Пример #1
0
 /**
  * Creates and returns DDL for creation of the type
  *
  * @return string
  */
 public static function get_creation_sql($node_schema, $node_type)
 {
     if (strcasecmp($node_type['type'], 'enum') == 0) {
         if (!isset($node_type->enum)) {
             throw new exception("enum type contains no enum children");
         }
         $values = '';
         for ($i = 0; $i < count($node_type->enum); $i++) {
             $value = $node_type->enum[$i]['name'];
             $values .= "'" . pg_escape_string($value) . "'";
             if ($i < count($node_type->enum) - 1) {
                 $values .= ",";
             }
         }
         $type_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_type['name']);
         $ddl = "CREATE TYPE " . $type_name . " AS ENUM (" . $values . ");";
     } else {
         if (strcasecmp($node_type['type'], 'composite') == 0) {
             if (!isset($node_type->typeCompositeElement)) {
                 throw new exception("composite type contains no typeCompositeElement children");
             }
             $type_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_type['name']);
             $ddl = "CREATE TYPE " . $type_name . " AS (\n";
             for ($i = 0; $i < count($node_type->typeCompositeElement); $i++) {
                 $tce_name = $node_type->typeCompositeElement[$i]['name'];
                 $tce_value = $node_type->typeCompositeElement[$i]['type'];
                 $ddl .= "  " . $tce_name . " " . $tce_value;
                 if ($i < count($node_type->typeCompositeElement) - 1) {
                     $ddl .= ",";
                 }
                 $ddl .= "\n";
             }
             $ddl .= ");";
         } else {
             if (strcasecmp($node_type['type'], 'domain') == 0) {
                 $type_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_type['name']);
                 if (!isset($node_type->domainType)) {
                     throw new exception("domain type {$type_name} contains no domainType element");
                 }
                 $info_node = $node_type->domainType;
                 $base_type = trim($info_node['baseType']);
                 if ($base_type === '') {
                     throw new exception("No baseType was given for domain type {$name}");
                 }
                 $ddl = "CREATE DOMAIN {$type_name} AS {$base_type}";
                 if (isset($info_node['default'])) {
                     $ddl .= "\n  DEFAULT " . pgsql8::value_escape($base_type, (string) $info_node['default']);
                 }
                 $null = strtolower($info_node['null']);
                 // NULL is the default, must match exactly "false" to be NOT NULL
                 if (strcasecmp($null, "false") === 0) {
                     $ddl .= "\n  NOT NULL";
                 }
                 foreach ($node_type->domainConstraint as $domainConstraint) {
                     $constraint_name = trim($domainConstraint['name']);
                     if ($constraint_name === '') {
                         throw new exception("Empty domain constraint name for {$type_name}");
                     }
                     $check = trim($domainConstraint);
                     if ($check === '') {
                         throw new exception("Empty domain constraint for {$type_name}");
                     }
                     $check = self::normalize_domain_constraint($check);
                     $ddl .= "\n  CONSTRAINT " . pgsql8::get_quoted_object_name($constraint_name) . " CHECK({$check})";
                 }
                 $ddl .= ';';
             } else {
                 throw new exception("unknown type " . $node_type['name'] . ' type ' . $node_type['type']);
             }
         }
     }
     return $ddl;
 }
Пример #2
0
 /**
  * Outputs commands for ALTER DOMAIN
  * @param $ofs          output file pointer
  * @param $old_schema   original schema
  * @param $old_type     original type
  * @param $new_schema   new schema
  * @param $new_type     new type
  */
 private static function apply_domain_changes($ofs, $old_schema, $old_type, $new_schema, $new_type)
 {
     // http://www.postgresql.org/docs/8.1/static/sql-alterdomain.html
     $domain = pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_type['name']);
     $old_domain = $old_type->domainType;
     $new_domain = $new_type->domainType;
     // if base type changes, we need to drop and re-add
     if (strcasecmp($old_domain['baseType'], $new_domain['baseType']) !== 0) {
         $ofs->write("-- domain base type changed from {$old_domain['baseType']} to {$new_domain['baseType']} - recreating\n");
         $ofs->write(pgsql8_type::get_drop_sql($old_schema, $old_type) . "\n");
         $ofs->write(pgsql8_type::get_creation_sql($new_schema, $new_type) . "\n");
         return;
     }
     $base_type = strtolower($new_domain['baseType']);
     // default is dropped
     if (isset($old_domain['default']) && !isset($new_domain['default'])) {
         $ofs->write("-- domain default dropped\n");
         $ofs->write("ALTER DOMAIN {$domain} DROP DEFAULT;\n");
     } elseif (strcmp($old_domain['default'], $new_domain['default']) !== 0) {
         $old_default = pgsql8::value_escape($base_type, (string) $old_domain['default']);
         $new_default = pgsql8::value_escape($base_type, (string) $new_domain['default']);
         $ofs->write("-- domain default changed from {$old_default}\n");
         $ofs->write("ALTER DOMAIN {$domain} SET DEFAULT {$new_default};\n");
     }
     $old_null = strcasecmp($old_domain['null'], 'false') !== 0;
     $new_null = strcasecmp($new_domain['null'], 'false') !== 0;
     // NULL -> NOT NULL
     if ($old_null && !$new_null) {
         $ofs->write("-- domain changed from NULL to NOT NULL\n");
         $ofs->write("ALTER DOMAIN {$domain} SET NOT NULL;\n");
     } elseif (!$old_null && $new_null) {
         $ofs->write("-- domain changed from NOT NULL to NULL\n");
         $ofs->write("ALTER DOMAIN {$domain} DROP NOT NULL;\n");
     }
     // diff constraints
     $old_constraints = array();
     foreach ($old_type->domainConstraint as $old_constraint) {
         $old_constraints[(string) $old_constraint['name']] = pgsql8_type::normalize_domain_constraint($old_constraint);
     }
     foreach ($new_type->domainConstraint as $new_constraint) {
         $name = (string) $new_constraint['name'];
         $constraint = pgsql8_type::normalize_domain_constraint($new_constraint);
         if (array_key_exists($name, $old_constraints)) {
             if (strcmp($constraint, $old_constraints[$name]) !== 0) {
                 $ofs->write("-- domain constraint {$name} changed from {$old_constraints[$name]}\n");
                 $ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n");
                 $ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n");
             }
             unset($old_constraints[$name]);
         } else {
             $ofs->write("-- domain constraint {$name} added\n");
             $ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n");
         }
     }
     foreach ($old_constraints as $name => $constraint) {
         $ofs->write("-- domain constraint {$name} removed\n");
         $ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n");
     }
 }
Пример #3
0
 /**
  * compare composite db doc to specified database
  *
  * @return string XML
  */
 public static function compare_db_data($db_doc, $host, $port, $database, $user, $password)
 {
     dbsteward::notice("Connecting to pgsql8 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
     // if not supplied, ask for the password
     if ($password === FALSE) {
         // @TODO: mask the password somehow without requiring a PHP extension
         echo "Password: "******"host={$host} port={$port} dbname={$database} user={$user} password={$password}");
     dbsteward::info("Comparing composited dbsteward definition data rows to postgresql database connection table contents");
     // compare the composited dbsteward document to the established database connection
     // effectively looking to see if rows are found that match primary keys, and if their contents are the same
     foreach ($db_doc->schema as $schema) {
         foreach ($schema->table as $table) {
             if (isset($table->rows)) {
                 $table_name = dbsteward::string_cast($schema['name']) . '.' . dbsteward::string_cast($table['name']);
                 $primary_key_cols = self::primary_key_split($table['primaryKey']);
                 $cols = preg_split("/[\\,\\s]+/", $table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
                 $col_types = array();
                 foreach ($table->column as $table_column) {
                     $type = '';
                     // foreign keyed columns inherit their foreign reference type
                     if (isset($table_column['foreignTable']) && isset($table_column['foreignColumn'])) {
                         if (strlen($type) > 0) {
                             throw new exception("type of " . $type . " was found for " . dbsteward::string_cast($cols[$j]) . " in table " . dbsteward::string_cast($table['name']) . " but it is foreign keyed!");
                         }
                         $foreign = array();
                         dbx::foreign_key($db_doc, $schema, $table, $table_column, $foreign);
                         // don't need to error-check, foreign_key() is self-checking if it doesnt find the fkey col it will complain
                         $type = $foreign['column']['type'];
                     } else {
                         $type = dbsteward::string_cast($table_column['type']);
                     }
                     if (strlen($type) == 0) {
                         throw new exception($table_name . " column " . $table_column['name'] . " type not found!");
                     }
                     $col_types[dbsteward::string_cast($table_column['name'])] = $type;
                 }
                 foreach ($table->rows->row as $row) {
                     // glue the primary key expression together for the where
                     $primary_key_expression = '';
                     for ($k = 0; $k < count($primary_key_cols); $k++) {
                         $column_name = pgsql8::get_quoted_column_name($primary_key_cols[$k]);
                         $pk_index = array_search($primary_key_cols[$k], $cols);
                         if ($pk_index === FALSE) {
                             throw new exception("failed to find " . $schema['name'] . "." . $table['name'] . " primary key column " . $primary_key_cols[$k] . " in cols list (" . implode(", ", $cols) . ")");
                         }
                         $primary_key_expression .= $column_name . " = " . pgsql8::value_escape($col_types[$primary_key_cols[$k]], $row->col[$pk_index], $db_doc);
                         if ($k < count($primary_key_cols) - 1) {
                             $primary_key_expression .= ' AND ';
                         }
                     }
                     $sql = "SELECT *\n              FROM " . $table_name . "\n              WHERE " . $primary_key_expression;
                     $rs = pgsql8_db::query($sql);
                     // is the row supposed to be deleted?
                     if (strcasecmp('true', $row['delete']) == 0) {
                         if (pg_num_rows($rs) > 0) {
                             dbsteward::notice($table_name . " row marked for DELETE found WHERE " . $primary_key_expression);
                         }
                     } else {
                         if (pg_num_rows($rs) == 0) {
                             dbsteward::notice($table_name . " does not contain row WHERE " . $primary_key_expression);
                         } else {
                             if (pg_num_rows($rs) > 1) {
                                 dbsteward::notice($table_name . " contains more than one row WHERE " . $primary_key_expression);
                                 while (($db_row = pg_fetch($rs)) !== FALSE) {
                                     dbsteward::notice("\t" . implode(', ', $db_row));
                                 }
                             } else {
                                 $db_row = pg_fetch_assoc($rs);
                                 // make sure any aspects of the $row are present in the $db_row
                                 for ($i = 0; $i < count($cols); $i++) {
                                     $xml_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($row->col[$i]));
                                     $db_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($db_row[$cols[$i]]));
                                     $values_match = FALSE;
                                     // evaluate if they are equal
                                     $values_match = $xml_value == $db_value;
                                     // if they are not PHP equal, and are alternate expressionable, ask the database
                                     if (!$values_match && preg_match('/^time.*|^date.*|^interval/i', $col_types[$cols[$i]]) > 0) {
                                         // do both describe atleast some value (greater than zero len?)
                                         if (strlen($xml_value) > 0 && strlen($db_value) > 0) {
                                             $sql = "SELECT '{$xml_value}'::" . $col_types[$cols[$i]] . " = '{$db_value}'::" . $col_types[$cols[$i]] . " AS equal_eval";
                                             $values_match = pgsql8_db::query_str($sql) == 't';
                                         }
                                     }
                                     if (!$values_match) {
                                         dbsteward::warning($table_name . " row column WHERE (" . $primary_key_expression . ") " . $cols[$i] . " data does not match database row column: '" . $xml_value . "' VS '" . $db_value . "'");
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
         }
     }
     //xml_parser::validate_xml($db_doc->asXML());
     return xml_parser::format_xml($db_doc->saveXML());
 }