/** * 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()); }