private function _testDbxForeignKey()
{
$doc = simplexml_load_string($this->xml);
$schema = $doc->schema;
$table1 = $schema->table[0];
$table1_t1id = $table1->column[0];
$table1_t2id = $table1->column[1];
$table2 = $schema->table[1];
$table2_t2id = $table2->column[0];
dbx::foreign_key($doc, $schema, $table1, $table1_t2id, $foreign);
$this->assertEquals($schema, $foreign['schema']);
$this->assertEquals($table2, $foreign['table']);
$this->assertEquals($table2_t2id, $foreign['column']);
}
public static function column_type($db_doc, $schema, $table, $column, &$foreign)
{
// if it is a foreign keyed column, solve for the foreignKey type
if (isset($column['foreignTable'])) {
dbx::foreign_key($db_doc, $schema, $table, $column, $foreign);
$column_type = $foreign['column']['type'];
// for foreign keys, translate serial types to their integer base
if (strcasecmp('serial', $column_type) == 0) {
$column_type = 'int';
} else {
if (strcasecmp('bigserial', $column_type) == 0) {
$column_type = 'bigint';
}
}
} else {
if (!isset($column['type']) || strlen($column['type']) == 0) {
throw new Exception("column missing type -- " . $schema['name'] . "." . $table['name'] . "." . $column['name']);
}
$column_type = $column['type'];
if (dbx::get_type($schema, $column_type) != NULL) {
// this is a user defined type or enum, enforce quoting if set
$column_type = pgsql8::get_quoted_object_name($column_type);
}
}
return $column_type;
}
/**
* 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());
}
public static function column_type($db_doc, $schema, $table, $column, &$foreign, $check_for_enum_types = TRUE)
{
// if it is a foreign keyed column, solve for the foreignKey type
if (isset($column['foreignTable'])) {
dbx::foreign_key($db_doc, $schema, $table, $column, $foreign);
$column_type = $foreign['column']['type'];
// for foreign keys, identity columns translate to integers
if (stripos($column_type, 'int identity') === 0) {
// starts with int identity?
$column_type = 'int';
} else {
if (stripos($column_type, 'bigint identity') === 0) {
// starts with bigint identity?
$column_type = 'bigint';
}
}
} else {
if (!isset($column['type']) || strlen($column['type']) == 0) {
throw new Exception("column missing type -- " . $schema['name'] . "." . $table['name'] . "." . $column['name']);
}
$column_type = (string) $column['type'];
if ($check_for_enum_types) {
if (mssql10_column::enum_type_check(dbsteward::$new_database, $schema, $table, $column, $drop_sql, $add_sql)) {
// enum types rewritten as varchar(255)
$column_type = 'varchar(255)';
}
}
}
return $column_type;
}
