private function diff($old, $new, $expected1, $expected3, $message = '')
 {
     dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $old . '</dbsteward>');
     dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $new . '</dbsteward>');
     $ofs1 = new mock_output_file_segmenter();
     $ofs3 = new mock_output_file_segmenter();
     // same structure as mysql5_diff::update_structure
     foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
         $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
         mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE);
         mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE);
         mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema);
         mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema);
         // mysql5_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema);
         mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE);
     }
     foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
         $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
         mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE);
     }
     $actual1 = trim($ofs1->_get_output());
     $actual3 = trim($ofs3->_get_output());
     $this->assertEquals($expected1, $actual1, "during stage 1: {$message}");
     $this->assertEquals($expected3, $actual3, "during stage 3: {$message}");
 }
Ejemplo n.º 2
0
 /**
  * in MSSQL indexes must contain column references, value expressions are not allowed
  *
  */
 public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql)
 {
     $dimension_list = '';
     $add_column_sql = '';
     // in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql
     $i = 0;
     foreach ($node_index->indexDimension as $dimension) {
         $i++;
         $dimension_name = (string) $dimension;
         if (mssql10_table::contains_column($node_table, $dimension_name)) {
             // dimension is an explicit column
             // check unique index indexDimensions for nulled columns
             // mssql index constraint engine will not ignore null values for nullable columns
             if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
                 $node_column = dbx::get_table_column($node_table, $dimension_name);
                 if (mssql10_column::null_allowed($node_table, $node_column)) {
                     dbsteward::error("dimension_name = " . $dimension_name);
                     //var_dump($node_column);
                     throw new exception("nulled column index found");
                 }
             }
         } else {
             // not an explicit column, so create one
             $dimension_name = $node_index['name'] . '_' . $i;
             $add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . "  ADD " . $dimension_name . " AS " . (string) $dimension . ";\n";
         }
         $dimension_list .= $dimension_name . ', ';
     }
     $dimension_list = substr($dimension_list, 0, -2);
     return $dimension_list;
 }
Ejemplo n.º 3
0
 public function get_partition_info($table)
 {
     $parts = $this->query("SELECT partition_method, partition_name,\n                                  partition_expression, partition_description\n                           FROM partitions\n                           WHERE table_schema = ?\n                             AND table_name = ?\n                           ORDER BY partition_ordinal_position", array($this->dbname, $table->table_name));
     if (count($parts) === 0) {
         return null;
     }
     $method = strtoupper($parts[0]->partition_method);
     switch ($method) {
         case 'HASH':
         case 'LINEAR HASH':
             return (object) array('type' => $method, 'number' => count($parts), 'expression' => $parts[0]->partition_expression);
         case 'KEY':
         case 'LINEAR KEY':
             return (object) array('type' => $method, 'number' => count($parts), 'columns' => str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression));
         case 'LIST':
         case 'RANGE':
         case 'RANGE COLUMNS':
             return (object) array('type' => $method, 'expression' => $method == 'RANGE COLUMNS' ? str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression) : $parts[0]->partition_expression, 'segments' => array_map(function ($p) {
                 return (object) array('name' => $p->partition_name, 'value' => $p->partition_description);
             }, $parts));
         default:
             dbsteward::error("Unrecognized partition method {$method}!");
     }
     return null;
 }
 public function __call($m, $a)
 {
     $ignore_ofs_methods = array('__destruct');
     if (in_array($m, $ignore_ofs_methods)) {
         return 'IGNORE_OFS_COMMAND_COMPLETE';
     }
     // if the command is in the list of commands to run on all ofs objects, do so
     $all_ofs_methods = array('append_header', 'append_footer');
     if (in_array($m, $all_ofs_methods)) {
         foreach ($this->ofs as $set_id => $ofs) {
             call_user_func_array(array(&$ofs, $m), $a);
         }
         return 'ALL_OFS_COMMAND_COMPLETE';
     }
     $use_replica_set_id = format::get_context_replica_set_id();
     if ($use_replica_set_id == -10) {
         // context_replica_set_id -10 means object does not have slonySetId defined
         // use the natural first replica set as the replica context
         $first_replica_set = pgsql8::get_slony_replica_set_natural_first(dbsteward::$new_database);
         $use_replica_set_id = (int) $first_replica_set['id'];
     }
     // make sure replica set id to use is known
     if (!isset($this->ofs[$use_replica_set_id])) {
         if ($this->skip_unknown_set_ids) {
             dbsteward::notice("[OFS RSR] context replica set ID is " . $use_replica_set_id . ", but no replica set by that ID, skipping output");
             return FALSE;
         }
         throw new exception("context replica set ID " . $use_replica_set_id . " not defined");
     }
     $active_set_ofs = $this->ofs[$use_replica_set_id];
     dbsteward::debug("[OFS RSR] __call calling " . $use_replica_set_id . " ofs::" . $m);
     return call_user_func_array(array(&$active_set_ofs, $m), $a);
 }
    public function setUp()
    {
        dbsteward::set_sql_format('mysql5');
        dbsteward::$quote_schema_names = TRUE;
        dbsteward::$quote_table_names = TRUE;
        dbsteward::$quote_column_names = TRUE;
        dbsteward::$quote_function_names = TRUE;
        dbsteward::$quote_object_names = TRUE;
        mysql5::$use_auto_increment_table_options = FALSE;
        mysql5::$use_schema_name_prefix = FALSE;
        $xml = <<<XML
<dbsteward>
  <database>
    <host>db-host</host>
    <name>dbsteward</name>
    <role>
      <application>dbsteward_phpunit_app</application>
      <owner>deployment</owner>
      <replication/>
      <readonly/>
    </role>
  </database>
</dbsteward>
XML;
        $this->dbdoc = new SimpleXMLElement($xml);
    }
Ejemplo n.º 6
0
 public static function get_view_query($node_view)
 {
     $q = '';
     foreach ($node_view->viewQuery as $query) {
         if (!isset($query['sqlFormat']) || strcasecmp($query['sqlFormat'], dbsteward::get_sql_format()) == 0) {
             // sanity check to make sure not more than one viewQuery is matching the sqlFormat scenario
             if (strlen($q) > 0) {
                 throw new exception("query already matched for sqlFormat -- extra viewQuery elements present?");
             }
             // sqlFormat is not present or
             // sqlFormat matches the current static run-time setting
             // use this viewQuery
             $q = (string) $query;
         }
     }
     if (strlen($q) == 0) {
         foreach ($node_view->viewQuery as $query) {
             var_dump($query);
         }
         throw new exception("view " . $node_view['name'] . " - failed to find viewQuery that matches active sql format " . dbsteward::get_sql_format());
     }
     // if last char is ;, prune it
     if (substr($q, -1) == ';') {
         $q = substr($q, 0, -1);
     }
     return $q;
 }
    public function setUp()
    {
        dbsteward::set_sql_format('pgsql8');
        dbsteward::$quote_schema_names = TRUE;
        dbsteward::$quote_table_names = TRUE;
        dbsteward::$quote_column_names = TRUE;
        dbsteward::$quote_function_names = TRUE;
        dbsteward::$quote_object_names = TRUE;
        $xml = <<<XML
<dbsteward>
  <database>
    <host>db-host</host>
    <name>dbsteward</name>
    <role>
      <application>dbsteward_phpunit_app</application>
      <owner>deployment</owner>
      <replication/>
      <readonly/>
    </role>
  </database>
</dbsteward>
XML;
        $db = new SimpleXMLElement($xml);
        dbsteward::$new_database = $db;
        dbsteward::$old_database = $db;
    }
 protected function quoteTestCommon($format, $additional_invalid = array())
 {
     dbsteward::set_sql_format($format);
     dbsteward::$quote_all_names = FALSE;
     dbsteward::$quote_illegal_identifiers = FALSE;
     $invalid_prefixes = array_merge(array('in$', '0in'), $additional_invalid);
     foreach (array('schema', 'table', 'column', 'object', 'function') as $object) {
         foreach (array(TRUE, FALSE) as $quoted) {
             dbsteward::${"quote_{$object}_names"} = $quoted;
             // attempt valid identifiers
             $valid_name = "valid_{$format}_{$object}_" . ($quoted ? 'quoted' : 'unquoted') . "_identifier123";
             $expected = $quoted ? $format::QUOTE_CHAR . $valid_name . $format::QUOTE_CHAR : $valid_name;
             $this->assertEquals($expected, call_user_func("{$format}::get_quoted_{$object}_name", $valid_name), "During call to {$format}::get_quoted_{$object}_name");
             // attempt invalid identifiers - expect exceptions
             $invalid_names = array_map(function ($prefix) use($valid_name) {
                 return $prefix . $valid_name;
             }, $invalid_prefixes);
             $invalid_names[] = $format::QUOTE_CHAR . $valid_name . $format::QUOTE_CHAR;
             foreach ($invalid_names as $invalid_name) {
                 if ($quoted) {
                     // only expect an exception if not quoted...
                 } else {
                     try {
                         call_user_func("{$format}::get_quoted_{$object}_name", $invalid_name);
                     } catch (Exception $ex) {
                         $this->assertContains('Illegal identifier', $ex->getMessage());
                         continue;
                     }
                     $this->fail("Expected 'Illegal identifier' exception, but no exception was thrown for identifier '{$invalid_name}'");
                 }
             }
         }
     }
 }
Ejemplo n.º 9
0
    public function testSlonikOutputIsCorrect()
    {
        $xml = <<<OUTXML
<?xml version="1.0" encoding="UTF-8"?>
<dbsteward>
  <database>
    <sqlformat>pgsql8</sqlformat>
    <role>
      <application>application</application>
      <owner>dba</owner>
      <replication>slony</replication>
      <readonly>readonly</readonly>
    </role>
    <slony clusterName="aim">
      <slonyNode id="1" comment="Master" dbPassword="******" dbUser="******" dbHost="db00" dbName="mrh"/>
      <slonyNode id="2" comment="Replica" dbPassword="******" dbUser="******" dbHost="db01" dbName="mrh"/>
      <slonyReplicaSet id="101" comment="only set" originNodeId="1" upgradeSetId="2">
        <slonyReplicaSetNode id="2" providerNodeId="1"/>
      </slonyReplicaSet>
      <slonyReplicaSet id="201" comment="only set" originNodeId="1" upgradeSetId="3">
        <slonyReplicaSetNode id="2" providerNodeId="1"/>
      </slonyReplicaSet>
    </slony>
  </database>
  <schema name="public" owner="ROLE_OWNER">
    <table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="101" slonyId="101">
      <column name="id" type="bigserial" slonySetId="101" slonyId="101"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="101" slonyId="102">
      <column name="id" type="bigserial" slonySetId="101" slonyId="102"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="201" slonyId="105">
      <column name="id" type="bigserial" slonySetId="201" slonyId="105"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="201" slonyId="106">
      <column name="id" type="bigserial" slonySetId="201" slonyId="106"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="1098">
      <column name="id" type="bigserial" slonyId="1098"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <!-- here for additional changes -->
  </schema>
</dbsteward>
OUTXML;
        $old_db_doc = simplexml_load_string($xml);
        dbsteward::$generate_slonik = TRUE;
        $output_prefix_path = dirname(__FILE__) . '/../testdata/' . 'slony_id_output';
        pgsql8::build($output_prefix_path, $old_db_doc);
        $this->assertLogged(Monolog\Logger::NOTICE, '/101:\\s101-102/');
        // before 1098 wasn't getting put into first natural order, now it should be
        $this->assertLogged(Monolog\Logger::NOTICE, '/101:\\s[\\d\\-]+,\\s*1098/', "SlonyIds without slonySetIds are not put into first natural order slonySet");
        $this->assertLogged(Monolog\Logger::NOTICE, '/201:\\s105-106/');
    }
Ejemplo n.º 10
0
 public function setUp()
 {
     dbsteward::set_sql_format('mysql5');
     dbsteward::$quote_schema_names = TRUE;
     dbsteward::$quote_table_names = TRUE;
     dbsteward::$quote_column_names = TRUE;
     mysql5::$use_auto_increment_table_options = FALSE;
     mysql5::$use_schema_name_prefix = FALSE;
 }
 public function setUp()
 {
     dbsteward::set_sql_format('pgsql8');
     dbsteward::$quote_schema_names = TRUE;
     dbsteward::$quote_table_names = TRUE;
     dbsteward::$quote_column_names = TRUE;
     dbsteward::$quote_function_names = TRUE;
     dbsteward::$quote_object_names = TRUE;
 }
Ejemplo n.º 12
0
 /**
  * Creates new schemas (not the objects inside the schemas)
  *
  * @param  object  $ofs output file pointer
  * @return void
  */
 protected static function create_new_schemas($ofs)
 {
     foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
         if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == null) {
             dbsteward::info("Create New Schema " . $new_schema['name']);
             pgsql8::set_context_replica_set_id($new_schema);
             $ofs->write(format_schema::get_creation_sql($new_schema));
         }
     }
 }
Ejemplo n.º 13
0
 /**
  * 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;
 }
 /**
  * @group pgsql8
  * @group mysql5
  * @group mssql10
  */
 public function testThrowWhenFalse()
 {
     dbsteward::$ignore_custom_roles = FALSE;
     try {
         xml_parser::role_enum($this->dbdoc, 'invalid');
     } catch (Exception $ex) {
         $this->assertEquals('Failed to confirm custom role: invalid', $ex->getMessage());
         return;
     }
     $this->fail("Expected exception when not ignoring custom roles");
 }
Ejemplo n.º 15
0
 public static function get_permission_options_sql($node_permission)
 {
     if (!empty($node_permission['with'])) {
         // @TODO: Support MAX_*_PER_HOUR grant options
         if (strcasecmp($node_permission['with'], 'grant') != 0) {
             dbsteward::warning("Ignoring WITH option '{$node_permission['with']}' because MySQL only supports WITH GRANT OPTION.");
         } else {
             return " WITH GRANT OPTION";
         }
     }
 }
Ejemplo n.º 16
0
 private function common_diff($xml_a, $xml_b, $expected1, $expected3, $message = '')
 {
     dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $xml_a . '</dbsteward>');
     dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $xml_b . '</dbsteward>');
     $ofs1 = new mock_output_file_segmenter();
     $ofs3 = new mock_output_file_segmenter();
     pgsql8_diff_tables::diff_tables($ofs1, $ofs3, dbsteward::$old_database->schema, dbsteward::$new_database->schema);
     $actual1 = trim($ofs1->_get_output());
     $actual3 = trim($ofs3->_get_output());
     $this->assertEquals($expected1, $actual1, "during stage 1: {$message}");
     $this->assertEquals($expected3, $actual3, "during stage 3: {$message}");
 }
    /**
     * Tests that functions referenced as default values for columns
     * 1) do not result in build failures
     * 2) generate sane SQL on build
     */
    public function testBuildColumnSQL()
    {
        $xml = <<<XML
<dbsteward>
  <database>
    <role>
      <application>dbsteward_phpunit_app</application>
      <owner>deployment</owner>
      <replication/>
      <readonly/>
    </role>
  </database>

  <schema name="dbsteward" owner="ROLE_OWNER">
    <function name="test" returns="integer" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="always returns 5, is a test function">
      <functionDefinition language="plpgsql" sqlFormat="pgsql8">
        BEGIN
          RETURN 5;
        END
      </functionDefinition>
    </function>
  </schema>
  <schema name="hotel" owner="ROLE_OWNER">
    <table name="rate" owner="ROLE_OWNER" primaryKey="rate_id" primaryKeyName="rate_pkey">
      <tableOption sqlFormat="pgsql8" name="with" value="(oids=false)"/>
      <column name="rate_id" type="integer" null="false"/>
      <column name="rate_group_id" null="false" foreignSchema="hotel" foreignTable="rate_group" foreignColumn="rate_group_id" foreignKeyName="rate_rate_group_id_fkey" foreignOnUpdate="NO_ACTION" foreignOnDelete="NO_ACTION"/>
      <column name="rate_name" type="character varying(120)"/>
      <column name="rate_value" type="numeric"/>
    </table>
    <table name="rate_group" owner="ROLE_OWNER" primaryKey="rate_group_id" primaryKeyName="rate_group_pkey">
      <tableOption sqlFormat="pgsql8" name="with" value="(oids=false)"/>
      <column name="rate_group_id" type="integer" null="false" default="dbsteward.test()"/>
      <column name="rate_group_name" type="character varying(100)"/>
      <column name="rate_group_enabled" type="boolean" null="false" default="true"/>
    </table>
  </schema>
</dbsteward>
XML;
        $expected = <<<EXP
ALTER TABLE "hotel"."rate" ALTER COLUMN "rate_id" SET NOT NULL;
ALTER TABLE "hotel"."rate" ALTER COLUMN "rate_group_id" SET NOT NULL;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_id" SET DEFAULT dbsteward.test();
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_id" SET NOT NULL;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_enabled" SET DEFAULT true;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_enabled" SET NOT NULL;
EXP;
        dbsteward::$quote_all_names = TRUE;
        $this->set_xml_content_a($xml);
        $this->build_db('pgsql8');
        $actual = file_get_contents(dirname(__FILE__) . '/../testdata/unit_test_xml_a_build.sql');
        $this->assertContains($expected, $actual);
    }
Ejemplo n.º 18
0
    /**
     * There was a bug in streaker where it wasn't counting the entire first streak, output used to be for below: 1, 5-6, 98-98
     */
    public function testSlonikStreakerIsGood()
    {
        $xml = <<<SLONXML
<?xml version="1.0" encoding="UTF-8"?>
<dbsteward>
  <database>
    <sqlformat>pgsql8</sqlformat>
    <role>
      <application>application</application>
      <owner>dba</owner>
      <replication>slony</replication>
      <readonly>readonly</readonly>
    </role>
    <slony clusterName="aim">
      <slonyNode id="1" comment="Master" dbPassword="******" dbUser="******" dbHost="db00" dbName="mrh"/>
      <slonyNode id="2" comment="Replica" dbPassword="******" dbUser="******" dbHost="db01" dbName="mrh"/>
      <slonyReplicaSet id="1" comment="only set" originNodeId="1" upgradeSetId="2">
        <slonyReplicaSetNode id="2" providerNodeId="1"/>
      </slonyReplicaSet>
    </slony>
  </database>
  <schema name="public" owner="ROLE_OWNER">
    <table name="log_sl" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="1">
      <column name="id" type="bigserial" slonyId="1"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log_sl2" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="2">
      <column name="id" type="bigserial" slonyId="2"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log_sl3" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="5">
      <column name="id" type="bigserial" slonyId="5"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log_sl4" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="6">
      <column name="id" type="bigserial" slonyId="6"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <table name="log_sl5" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="98">
      <column name="id" type="bigserial" slonyId="98"/>
      <grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
    </table>
    <!-- here for additional changes -->
  </schema>
</dbsteward>
SLONXML;
        $old_db_doc = simplexml_load_string($xml);
        dbsteward::$generate_slonik = TRUE;
        $output_prefix_path = dirname(__FILE__) . '/../testdata/' . 'slony_id_streak';
        pgsql8::build($output_prefix_path, $old_db_doc);
        $this->assertLogged(Monolog\Logger::NOTICE, '/sequence ID segments.*:\\s1-2, 5-6, 98/');
    }
Ejemplo n.º 19
0
 /**
  * Returns full definition of the column.
  *
  * @param add_defaults whether default value should be added in case NOT
  *        NULL constraint is specified but no default value is set
  *
  * @return full definition of the column
  */
 public static function get_full_definition($db_doc, $node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_auto_increment = false)
 {
     // ignore AUTO_INCREMENT flags for now
     $is_auto_increment = static::is_auto_increment($node_column['type']);
     $orig_type = (string) $node_column['type'];
     $node_column['type'] = static::un_auto_increment($node_column['type']);
     $column_type = static::column_type($db_doc, $node_schema, $node_table, $node_column);
     $definition = mysql5::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
     $nullable = static::null_allowed($node_table, $node_column);
     $is_timestamp = static::is_timestamp($node_column);
     if ($include_null_definition) {
         if ($nullable) {
             if ($is_timestamp) {
                 $definition .= " NULL";
             }
         } else {
             $definition .= " NOT NULL";
         }
     }
     if ($include_auto_increment && $is_auto_increment) {
         $definition .= " AUTO_INCREMENT";
     }
     if (strlen($node_column['default']) > 0) {
         if (static::is_serial($node_column['type'])) {
             $note = "Ignoring default '{$node_column['default']}' on {$node_schema['name']}.{$node_table['name']}.{$node_column['name']} because it is a serial type";
             dbsteward::warning($note . "\n");
         } else {
             $definition .= " DEFAULT " . $node_column['default'];
         }
     } else {
         if ($add_defaults && $is_timestamp) {
             if ($nullable) {
                 $definition .= " DEFAULT NULL";
             } else {
                 $definition .= " DEFAULT CURRENT_TIMESTAMP";
             }
         } else {
             if (!$nullable && $add_defaults) {
                 $default_col_value = self::get_default_value($node_column['type']);
                 if ($default_col_value != null) {
                     $definition .= " DEFAULT " . $default_col_value;
                 }
             }
         }
     }
     if (strlen($node_column['description']) > 0) {
         $definition .= " COMMENT " . mysql5::quote_string_value($node_column['description']);
     }
     // restore the original type of the column
     $node_column['type'] = $orig_type;
     return $definition;
 }
 public function setUp()
 {
     dbsteward::set_sql_format('mysql5');
     dbsteward::$quote_schema_names = TRUE;
     dbsteward::$quote_table_names = TRUE;
     dbsteward::$quote_column_names = TRUE;
     mysql5::$swap_function_delimiters = FALSE;
     mysql5::$use_auto_increment_table_options = FALSE;
     mysql5::$use_schema_name_prefix = FALSE;
     $this->config = $GLOBALS['db_config']->mysql5_config;
     $this->connect();
     $this->setup_shim();
 }
    private function common($format, $expected)
    {
        $xml = <<<XML
<schema name="public" owner="ROLE_OWNER">
  <table name="table" owner="ROLE_OWNER">
    <column name="column" type="serial" serialStart="5"/>
  </table>
</schema>
XML;
        $schema = new SimpleXMLElement($xml);
        dbsteward::set_sql_format($format);
        $colclass = $format . '_column';
        $expected = "-- serialStart 5 specified for public.table.column\n{$expected}\n";
        $actual = $colclass::get_serial_start_dml($schema, $schema->table, $schema->table->column);
        $this->assertEquals($expected, $actual);
    }
Ejemplo n.º 22
0
 public static function diff_triggers_table($ofs, $old_schema, $old_table, $new_schema, $new_table)
 {
     // drop triggers that no longer exist or are modified
     foreach (static::get_drop_triggers($old_schema, $old_table, $new_schema, $new_table) as $old_trigger) {
         // only do triggers set to the current sql_format
         if (strcasecmp($old_trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
             $ofs->write(format_trigger::get_drop_sql($old_schema, $old_trigger) . "\n");
         }
     }
     // add new triggers
     foreach (static::get_new_triggers($old_schema, $old_table, $new_schema, $new_table) as $new_trigger) {
         // only do triggers set to the current sql format
         if (strcasecmp($new_trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
             $ofs->write(format_trigger::get_creation_sql($new_schema, $new_trigger) . "\n");
         }
     }
 }
Ejemplo n.º 23
0
 /**
  * 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;
 }
Ejemplo n.º 24
0
 public static function get_drop_sql($node_schema, $node_trigger)
 {
     if (strcasecmp($node_trigger['sqlFormat'], dbsteward::get_sql_format())) {
         $note = "Ignoring {$node_trigger['sqlFormat']} trigger '{$node_trigger['name']}'";
         dbsteward::warning($note);
         return "-- {$note}\n";
     }
     $events = self::get_events($node_trigger);
     if (count($events) == 1) {
         return "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name']) . ";\n";
     } else {
         $ddl = "";
         foreach ($events as $event) {
             if ($event = self::validate_event($event)) {
                 $ddl .= "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . "_{$event}") . ";\n";
             }
         }
         return $ddl;
     }
 }
Ejemplo n.º 25
0
 /**
  * @group pgsql8
  */
 public function testTableColumnTypeQuotingPgsql8()
 {
     dbsteward::set_sql_format('pgsql8');
     dbsteward::$quote_all_names = TRUE;
     dbsteward::$single_stage_upgrade = TRUE;
     $doc_empty = simplexml_load_string($this->xml_empty);
     $doc_empty = xml_parser::composite_doc(FALSE, $doc_empty);
     dbsteward::$old_database = $doc_empty;
     $doc = simplexml_load_string($this->xml);
     $doc = xml_parser::composite_doc(FALSE, $doc);
     dbsteward::$new_database = $doc;
     $table_dependency = xml_parser::table_dependency_order($doc);
     //var_dump(xml_parser::format_xml($doc_empty->saveXML()));
     //var_dump(xml_parser::format_xml($doc->saveXML()));
     $schema = $doc->schema;
     $table = $schema->table;
     // make sure the type is named with quoting as part of a definition build
     $expected = "CREATE TYPE \"schema1\".\"enumCamelCaseType\" AS ENUM ('Read','Write','Delete');";
     $mofs = new mock_output_file_segmenter();
     pgsql8::build_schema($doc, $mofs, $table_dependency);
     $actual = trim($mofs->_get_output());
     $this->assertContains($expected, $actual);
     // make sure the type is referred to with quoting in a table creation as part of a definition build
     $expected_column = '"table_shable_mode" "enumCamelCaseType"';
     $this->assertContains($expected_column, $actual);
     // make sure the type is referred to with quoting when generating table create statements
     $expected = '"table_shable_mode" "enumCamelCaseType"';
     $sql = pgsql8_table::get_creation_sql($schema, $table);
     $this->assertContains($expected, $sql);
     // make sure create table quotes the type name
     $expected = '"table_shable_mode" "enumCamelCaseType"';
     $mofs = new mock_output_file_segmenter();
     var_dump(dbx::get_tables($schema));
     pgsql8_diff_tables::diff_tables($mofs, $mofs, NULL, $schema);
     $actual = trim($mofs->_get_output());
     $this->assertContains($expected, $actual);
     // make sure insert statements are made that match the XML definition
     $expected = "INSERT INTO \"schema1\".\"table_shable\" (\"table_shable_id\", \"table_shable_value\", \"table_shable_mode\") VALUES (1, E'shim sham', BETA);";
     $actual = trim(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE));
     $this->assertContains($expected, $actual);
 }
    public function setUp()
    {
        dbsteward::set_sql_format('mysql5');
        dbsteward::$quote_schema_names = TRUE;
        dbsteward::$quote_table_names = TRUE;
        dbsteward::$quote_column_names = TRUE;
        dbsteward::$quote_function_names = TRUE;
        mysql5::$swap_function_delimiters = FALSE;
        mysql5::$use_auto_increment_table_options = FALSE;
        mysql5::$use_schema_name_prefix = FALSE;
        $db_doc_xml = <<<XML
<dbsteward>
  <database>
    <role>
      <owner>the_owner</owner>
      <customRole>SOMEBODY</customRole>
    </role>
  </database>
</dbsteward>
XML;
        dbsteward::$new_database = new SimpleXMLElement($db_doc_xml);
    }
Ejemplo n.º 27
0
 public static function get_definition($node_function)
 {
     $definition = null;
     foreach ($node_function->functionDefinition as $def) {
         if (empty($def['sqlFormat']) || empty($def['language'])) {
             throw new Exception("Attributes sqlFormat and language are required on functionDefinitions, in function '{$node_function['name']}'");
         }
         if ($def['sqlFormat'] == dbsteward::get_sql_format() && static::supported_language($def['language'])) {
             if ($definition !== null) {
                 throw new Exception("duplicate function definition for {$def['sqlFormat']}/{$def['language']} in function '{$node_function['name']}'");
             }
             $definition = $def;
         }
     }
     if ($definition === null) {
         foreach ($node_function->functionDefinition as $def) {
             var_dump($def);
         }
         $format = dbsteward::get_sql_format();
         throw new Exception("no function definitions in a known language for format {$format} in function '{$node_function['name']}'");
     }
     return $definition;
 }
Ejemplo n.º 28
0
 /**
  * Updates objects in schemas.
  *
  * @param $ofs1  stage1 output file segmenter
  * @param $ofs3  stage3 output file segmenter
  */
 public static function update_structure($ofs1, $ofs3)
 {
     if (!mysql5::$use_schema_name_prefix) {
         if (count(dbsteward::$new_database->schema) > 1) {
             throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
         }
         if (count(dbsteward::$old_database->schema) > 1) {
             throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
         }
     } else {
         dbsteward::info("Drop Old Schemas");
         self::drop_old_schemas($ofs3);
     }
     mysql5_diff_views::drop_views_ordered($ofs1, dbsteward::$old_database, dbsteward::$new_database);
     //@TODO: implement mysql5_language ? no relevant conversion exists see other TODO's stating this
     //mysql5_diff_languages::diff_languages($ofs1);
     // if the table dependency order is unknown, bang them in natural order
     if (!is_array(mysql5_diff::$new_table_dependency)) {
         foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
             //@NOTICE: @TODO: this does not honor old*Name attributes, does it matter?
             $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
             mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
             mysql5_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
             mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $old_schema, $new_schema);
             // remove old constraints before table contraints, so the SQL statements succeed
             mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE);
             mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE);
             mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema);
             mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema);
             // mysql5_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema);
             mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE);
             mysql5_diff_triggers::diff_triggers($ofs1, $old_schema, $new_schema);
         }
         // non-primary key constraints may be inter-schema dependant, and dependant on other's primary keys
         // and therefore should be done after object creation sections
         foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
             $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
             mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE);
         }
     } else {
         $processed_schemas = array();
         for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
             // find the necessary pointers
             $item = mysql5_diff::$new_table_dependency[$i];
             // @NOTICE: dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME is NOT checked here because these are schema operations
             $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
             $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
             // do all types and functions on their own before table creation
             // see next loop for other once per schema work
             if (!in_array(trim($new_schema['name']), $processed_schemas)) {
                 mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
                 mysql5_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
                 $processed_schemas[] = trim($new_schema['name']);
             }
         }
         // remove all old constraints before new contraints, in reverse dependency order
         for ($i = count(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
             // find the necessary pointers
             $item = mysql5_diff::$old_table_dependency[$i];
             if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
                 // don't do anything with this table, it is a magic internal DBSteward value
                 continue;
             }
             $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
             $new_table = NULL;
             if ($new_schema != NULL) {
                 $new_table = dbx::get_table($new_schema, $item['table']['name']);
             }
             $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
             $old_table = NULL;
             if ($old_schema != NULL) {
                 $old_table = dbx::get_table($old_schema, $item['table']['name']);
             }
             if ($old_table == NULL) {
                 throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
             }
             // @NOTICE: when dropping constraints, dbx::renamed_table_check_pointer() is not called for $old_table
             // as mysql5_diff_tables::diff_constraints_table() will do rename checking when recreating constraints for renamed tables
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', TRUE);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', TRUE);
         }
         $processed_schemas = array();
         for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
             // find the necessary pointers
             $item = mysql5_diff::$new_table_dependency[$i];
             $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
             $new_table = NULL;
             if ($new_schema != NULL) {
                 $new_table = dbx::get_table($new_schema, $item['table']['name']);
             }
             $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
             // schema level stuff should only be done once, keep track of which ones we have done
             // see above for pre table creation stuff
             // see below for post table creation stuff
             if (!in_array($new_schema['name'], $processed_schemas)) {
                 mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $old_schema, $new_schema);
                 $processed_schemas[] = $new_schema['name'];
             }
             if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
                 // don't do anything with this table, it is a magic internal DBSteward value
                 continue;
             }
             $old_table = NULL;
             if ($old_schema != NULL) {
                 $old_table = dbx::get_table($old_schema, $item['table']['name']);
             }
             dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table, $new_table);
             // mysql5_diff_indexes::diff_indexes_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', FALSE);
             mysql5_diff_triggers::diff_triggers_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
             mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', FALSE);
         }
         // drop old tables in reverse dependency order
         for ($i = count(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
             // find the necessary pointers
             $item = mysql5_diff::$old_table_dependency[$i];
             if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
                 // don't do anything with this table, it is a magic internal DBSteward value
                 continue;
             }
             $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
             $new_table = NULL;
             if ($new_schema != NULL) {
                 $new_table = dbx::get_table($new_schema, $item['table']['name']);
             }
             $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
             $old_table = NULL;
             if ($old_schema != NULL) {
                 $old_table = dbx::get_table($old_schema, $item['table']['name']);
             }
             if ($old_table == NULL) {
                 throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
             }
             mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema, $old_table, $new_table);
         }
     }
     mysql5_diff_views::create_views_ordered($ofs3, dbsteward::$old_database, dbsteward::$new_database);
 }
Ejemplo n.º 29
0
 public static function get_constraint_drop_sql($constraint, $with_alter_table = TRUE)
 {
     if (!is_array($constraint)) {
         throw new exception("constraint is not an array?");
     }
     if (strlen($constraint['table_name']) == 0) {
         var_dump(array_keys($constraint));
         throw new exception("table_name is blank");
     }
     // because MySQL refuses to have consistent syntax
     switch (strtoupper($constraint['type'])) {
         case 'CHECK':
             // @TODO: Implement compatibility
             dbsteward::warning("Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint");
             return "-- Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint";
             break;
         case 'UNIQUE':
             $drop = "INDEX " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         case 'PRIMARY KEY':
             $drop = "PRIMARY KEY";
             break;
         case 'FOREIGN KEY':
             $drop = "FOREIGN KEY " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         case 'KEY':
             $drop = "KEY " . mysql5::get_quoted_object_name($constraint['name']);
             break;
         default:
             // we shouldn't actually ever get here.
             throw new Exception("Unimplemented MySQL constraint {$constraint['type']}");
     }
     $sql = '';
     if ($with_alter_table) {
         $sql .= "ALTER TABLE " . mysql5::get_fully_qualified_table_name($constraint['schema_name'], $constraint['table_name']) . " ";
     }
     $sql .= "DROP {$drop}";
     if ($with_alter_table) {
         $sql .= ";";
     }
     return $sql;
 }
    protected function setup_mysql5()
    {
        $xml = <<<XML
<dbsteward>
  <database>
    <sqlformat>mysql5</sqlformat>
    <role>
      <application>app_application</application>
      <owner>postgres</owner>
      <replication>app_slony</replication>
      <readonly>app_readonly</readonly>
    </role>
  </database>
  <schema name="app" owner="ROLE_OWNER">
    <table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk">
      <column name="id" type="character varying(32)" null="false"/>
      <column name="action" type="character varying(32)"/>
      <column name="description" type="character varying(200)"/>
      <rows columns="id, action, description">
        <row>
          <col>1</col>
          <col>Row 1</col>
          <col>Action 1 Description</col>
        </row>
        <row>
          <col>2</col>
          <col>Row 2</col>
          <col>Action 2 Description</col>
        </row>
        <row>
          <col>3</col>
          <col>Row 3</col>
          <col>Action 3 Description</col>
        </row>
        <row>
          <col>4</col>
          <col>Row 4</col>
          <col>Action 4 Description</col>
        </row>
        <row>
          <col>5</col>
          <col>Row 5</col>
          <col>Action 5 Description</col>
        </row>
      </rows>
    </table>
  </schema>
</dbsteward>
XML;
        $xml_data_overlay = <<<XML
<dbsteward>
  <database>
    <role>
      <application>client_app_application</application>
      <owner>postgres</owner>
      <replication>client_app_slony</replication>
      <readonly>client_app_readonly</readonly>
    </role>
  </database>
  <schema name="app" owner="ROLE_OWNER">
    <table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk">
      <rows columns="id, description">
        <row>
          <col>2</col>
          <col>Action 2 Alternate Description</col>
        </row>
        <row>
          <col>3</col>
          <col>Action 3 Alternate Description</col>
        </row>
        <row>
          <col>5</col>
          <col>Action 5 Alternate Description</col>
        </row>          
      </rows>
    </table>
  </schema>
</dbsteward>
XML;
        $this->xml_file_a = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_a.xml';
        $this->xml_file_b = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_b.xml';
        $this->xml_file_c = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_c.xml';
        $this->set_xml_content_a($xml);
        $this->set_xml_content_b($xml);
        $this->set_xml_content_c($xml_data_overlay);
        $this->output_prefix = dirname(__FILE__) . '/../testdata/mysql5_unit_test_identical';
        dbsteward::$single_stage_upgrade = TRUE;
        $old_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_c));
        $new_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_b, $this->xml_file_c));
        mysql5::build_upgrade('', 'identical_diff_test_mysql5_old', $old_db_doc_comp, array(), $this->output_prefix, 'identical_diff_test_mysql5_new', $new_db_doc_comp, array());
    }