/** * Creates and returns SQL for creation of the view. * * @return string */ public static function get_creation_sql($node_schema, $node_view) { if (isset($node_view['description']) && strlen($node_view['description']) > 0) { $ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n"; } $ddl = "CREATE VIEW " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_view['name']); $ddl .= "\n\tAS " . mssql10_view::get_view_query($node_view) . ";\n"; // @IMPLEMENT: $node_view['owner'] ? return $ddl; }
/** * Creates and returns SQL for creation of the view. * * @return string */ public static function get_creation_sql($db_doc, $node_schema, $node_view) { // set replica set context for view if (pgsql8::set_context_replica_set_id($node_view) === -10) { // view doesn't specify one, set from for schema object pgsql8::set_context_replica_set_id($node_schema); } if (isset($node_view['description']) && strlen($node_view['description']) > 0) { $ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n"; } $view_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_view['name']); $ddl = "CREATE OR REPLACE VIEW " . $view_name; $ddl .= "\n\tAS " . pgsql8_view::get_view_query($node_view) . ";\n"; if (isset($node_view['owner']) && strlen($node_view['owner']) > 0) { $ddl .= "ALTER VIEW " . $view_name . "\n\tOWNER TO " . xml_parser::role_enum($db_doc, $node_view['owner']) . ";\n"; } return $ddl; }
public static function role_enum($db_doc, $role) { if (!is_object($db_doc)) { var_dump($db_doc); throw new exception("invalid db_doc passed"); } switch ($role) { // PUBLIC is accepted as a special placeholder for public case 'PUBLIC': if (strcasecmp(dbsteward::get_sql_format(), 'mysql5') == 0) { // MySQL doesn't have a "public" role, and will attempt to create the user "PUBLIC" // instead, warn and alias to ROLE_APPLICATION $role = dbsteward::string_cast($db_doc->database->role->application); dbsteward::warning("Warning: MySQL doesn't support the PUBLIC role, using ROLE_APPLICATION ('{$role}') instead."); } else { $role = 'PUBLIC'; } break; case 'ROLE_APPLICATION': $role = dbsteward::string_cast($db_doc->database->role->application); break; case 'ROLE_OWNER': $role = dbsteward::string_cast($db_doc->database->role->owner); break; case 'ROLE_SLONY': $role = dbsteward::string_cast($db_doc->database->role->replication); break; default: if (isset($db_doc->database->role->customRole)) { $custom_roles = preg_split("/[\\,\\s]+/", $db_doc->database->role->customRole, -1, PREG_SPLIT_NO_EMPTY); for ($i = 0; $i < count($custom_roles); $i++) { if (strcasecmp($role, $custom_roles[$i]) == 0) { return $custom_roles[$i]; } } if (!dbsteward::$ignore_custom_roles) { throw new exception("Failed to confirm custom role: " . $role); } else { // this is cleverville: // without having to modify the 450000 calls to role_enum // return role->owner when a role macro is not found and there is no custom role called $role $owner = $db_doc->database->role->owner; dbsteward::warning("Warning: Ignoring custom roles. Role '{$role}' is being overridden by ROLE_OWNER ('{$owner}')."); return $owner; } } throw new exception("Unknown role enumeration: " . $role); break; } return $role; }
/** * Creates and returns SQL for dropping the language. * * @return string */ public static function get_drop_sql($node_language) { $ddl = "DROP " . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . " LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . " ;"; return $ddl; }
/** * escape a column's value, or return the default value if none specified * * @NOTE: it is critical to note that colmn values should always be escaped with this function * as it also converts MSSQL specific values from postgresql ones * * @return string */ public static function column_value_default($node_schema, $node_table, $data_column_name, $node_col) { // if marked, make it null or default, depending on column options if (isset($node_col['null']) && strcasecmp('true', $node_col['null']) == 0) { return 'NULL'; } // columns that specify empty attribute are made empty strings if (isset($node_col['empty']) && strcasecmp('true', $node_col['empty']) == 0) { return "''"; } // don't esacape columns marked literal sql values if (isset($node_col['sql']) && strcasecmp($node_col['sql'], 'true') == 0) { return '(' . $node_col . ')'; } $node_column = dbx::get_table_column($node_table, $data_column_name); if ($node_column === NULL) { throw new exception("Failed to find table " . $node_table['name'] . " column " . $data_column_name . " for default value check"); } $value_type = mysql5_column::column_type(dbsteward::$new_database, $node_schema, $node_table, $node_column); // else if col is zero length, make it default, or DB NULL if (strlen($node_col) == 0) { // is there a default defined for the column? $dummy_data_column = new stdClass(); $column_default_value = xml_parser::column_default_value($node_table, $data_column_name, $dummy_data_column); if ($column_default_value != NULL) { // run default value through value_escape to allow data value conversions to happen $value = mysql5::value_escape($value_type, $column_default_value); } else { $value = 'NULL'; } } else { $value = mysql5::value_escape($value_type, dbsteward::string_cast($node_col)); } return $value; }
/** * Function for placing the slonyids into their Sets, or not if they have no set */ protected static function set_sequence_slony_ids(SimpleXMLElement $column, $db_doc) { if (isset($column['slonySetId']) && !is_null($column['slonySetId'])) { if (isset(self::$sequence_slony_ids[(int) $column['slonySetId']])) { self::$sequence_slony_ids[(int) $column['slonySetId']][] = dbsteward::string_cast($column['slonyId']); } else { self::$sequence_slony_ids[(int) $column['slonySetId']] = array(dbsteward::string_cast($column['slonyId'])); } } else { // if no slonySetId is defined, put it into the first natural order $first_replica_set = static::get_slony_replica_set_natural_first($db_doc); if ((int) $first_replica_set['id'] > 0) { if (isset(self::$sequence_slony_ids[(int) $first_replica_set['id']])) { self::$sequence_slony_ids[(int) $first_replica_set['id']][] = dbsteward::string_cast($column['slonyId']); } else { self::$sequence_slony_ids[(int) $first_replica_set['id']] = array(dbsteward::string_cast($column['slonyId'])); } } else { // only use if there is no default natural order replica set, // not a huge fan of magic values but don't want to let PHP default to 0 if (isset(self::$sequence_slony_ids['NoSlonySet'])) { self::$sequence_slony_ids['NoSlonySet'][] = dbsteward::string_cast($column['slonyId']); } else { self::$sequence_slony_ids['NoSlonySet'] = array(dbsteward::string_cast($column['slonyId'])); } } } }
public static function foreign_key($db_doc, $node_schema, $node_table, $column, &$foreign) { $fschema = strlen($column['foreignSchema']) == 0 ? (string) $node_schema['name'] : (string) $column['foreignSchema']; $foreign['schema'] = dbx::get_schema($db_doc, $fschema); if (!$foreign['schema']) { throw new exception("Failed to find foreign schema '" . dbsteward::string_cast($column['foreignSchema']) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name'])); } $foreign['table'] = dbx::get_table($foreign['schema'], $column['foreignTable']); if (!$foreign['table']) { throw new exception("Failed to find foreign table '" . $column['foreignTable'] . "' for " . $node_schema['name'] . "." . $node_table['name'] . "." . $column['name']); } // if foreignColumn is not set // the column is assumed to be the same name as the referring column if (isset($column['foreignColumn']) && strlen($column['foreignColumn'])) { $foreignColumn = $column['foreignColumn']; } else { $foreignColumn = $column['name']; } $foreign['column'] = dbx::get_table_column($foreign['table'], $foreignColumn); if (!$foreign['column']) { var_dump($foreign['column']); throw new exception("Failed to find foreign column '" . dbsteward::string_cast($foreignColumn) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name'])); } // column type is missing, and resolved foreign is also a foreign key? // recurse and find the cascading foreign key if (strlen($foreign['column']['type']) == 0 && isset($foreign['column']['foreignColumn'])) { dbsteward::trace("Seeking nested foreign key for " . dbsteward::string_cast($foreign['schema']['name']) . "." . dbsteward::string_cast($foreign['table']['name']) . "." . $foreign['column']['name']); $nested_fkey = array(); self::foreign_key($db_doc, $foreign['schema'], $foreign['table'], $foreign['column'], $nested_fkey); //var_dump($nested_fkey['column']); // make a separate clone of the column element because we are specifying the type only for foreign key type referencing $foreign['column'] = new SimpleXMLElement($foreign['column']->asXML()); $foreign['column']['type'] = $nested_fkey['column']['type']; } $foreign['name'] = pgsql8_index::index_name($node_table['name'], $column['name'], 'fkey'); $table_name = format::get_fully_qualified_table_name($foreign['schema']['name'], $foreign['table']['name']); $column_name = format::get_quoted_column_name($foreign['column']['name']); $foreign['references'] = "{$table_name}({$column_name})"; }
/** * escape a column's value, or return the default value if none specified * * @NOTE: it is critical to note that colmn values should always be escaped with this function * as it also converts MSSQL specific values from postgresql ones * * @return string */ public static function column_value_default($node_schema, $node_table, $data_column_name, $node_col) { // if marked, make it null or default, depending on column options if (isset($node_col['null']) && strcasecmp('true', $node_col['null']) == 0) { $value = 'NULL'; } else { if (isset($node_col['empty']) && strcasecmp('true', $node_col['empty']) == 0) { // string escape prefix needed? -- see pgsql8::E_ESCAPE usage $value = "''"; } else { if (isset($node_col['sql']) && strcasecmp($node_col['sql'], 'true') == 0) { $value = '(' . $node_col . ')'; } else { $node_column = dbx::get_table_column($node_table, $data_column_name); if ($node_column === NULL) { throw new exception("Failed to find table " . $node_table['name'] . " column " . $data_column_name . " for default value check"); } $value_type = mssql10_column::column_type(dbsteward::$new_database, $node_schema, $node_table, $node_column, $foreign); // else if col is zero length, make it default, or DB NULL if (strlen($node_col) == 0) { // is there a default defined for the column? $dummy_data_column = new stdClass(); $column_default_value = xml_parser::column_default_value($node_table, $data_column_name, $dummy_data_column); if ($column_default_value != NULL) { // run default value through value_escape to allow data value conversions to happen $value = mssql10::value_escape($value_type, $column_default_value); } else { $value = 'NULL'; } } else { $value = mssql10::value_escape($value_type, dbsteward::string_cast($node_col)); } } } } return $value; }
public static function get_primary_key_name($node_table) { if (!empty($node_table['primaryKeyName'])) { return dbsteward::string_cast($node_table['primaryKeyName']); } else { return format_index::index_name($node_table['name'], NULL, 'pkey'); } }