function image_retrieval($features, $max = 100)
{
$ret = '';
$query_args = array("url" => "http://cml10.csie.ntu.edu.tw:5000/", "post" => true, "params" => array("feature" => $features, "max" => $max));
$xml_ret = query_cbir_service($query_args);
if (!empty($xml_ret)) {
$parser = new xml_parser();
$ret = $parser->parse($xml_ret);
}
return $ret;
}
private function getParsedRow($tabrow, $delimiter)
{
$xml = <<<XML
<dbsteward>
<database>
<role>
<application>x</application>
<owner>x</owner>
<replication>x</replication>
<readonly>x</readonly>
</role>
</database>
<schema name="public" owner="ROLE_OWNER">
<table name="tabrowtable" owner="ROLE_OWNER" primaryKey="c1">
<column name="c1" type="int"/>
<column name="c2" type="int"/>
<column name="c3" type="int"/>
<rows columns="c1,c2,c3">
<tabrow>{$tabrow}</tabrow>
</rows>
</table>
</schema>
</dbsteward>
XML;
$doc = simplexml_load_string($xml);
if ($delimiter !== false) {
$doc->schema->table->rows['tabrowDelimiter'] = $delimiter;
}
$composite = xml_parser::composite_doc(null, $doc);
$cols = array();
foreach ($composite->schema->table->rows->row->col as $col) {
$cols[] = (string) $col;
}
return $cols;
}
private function _testXmlParserTableHasDep()
{
$doc = simplexml_load_string($this->xml);
$schema = $doc->schema;
$table1 = array('schema' => $schema, 'table' => $schema->table[0]);
$table2 = array('schema' => $schema, 'table' => $schema->table[1]);
$this->assertTrue(xml_parser::table_has_dependency($table1, $table2));
}
public function testXPathWorksWithQuotes()
{
$file_name = 'sqlXpathTest.xml';
$base = new SimpleXMLElement("<allyour>baseyourbaseyourbasebase</allyour>");
$over = new SimpleXMLElement("<allyour><sql>(1, 'version=\"1.0\"')</sql></allyour>");
$this->assertNotEquals($base, $over);
// ensure the sql is composited
xml_parser::xml_composite_children($base, $over, $file_name);
$this->assertEquals($base, $over);
}
function parseXmlFolder($folder)
{
//die($folder);
$complete_config = array();
require_once 'xml_parser.class.php';
$parser = new xml_parser();
// test if folder is found
if (file_exists($folder)) {
$ressource = opendir($folder);
// find files in this folder
while (($file = readdir($ressource)) !== false) {
// debug($file, 'xml_parser::parse_folder files');
if (is_file($folder . '/' . $file)) {
$path_parts = pathinfo($file);
// if it's an yaml file, parse it and store thge results in an array
if ($path_parts['extension'] == 'xml') {
$we_have_config_files = true;
$config = $parser->load($folder . '/' . $file);
if (!$config) {
trigger_error("we have a parsing error with {$file}");
}
if (is_array($config)) {
$complete_config = array_merge($complete_config, $config);
}
}
}
}
if (isset($we_have_config_files)) {
//echo '<pre>';
//print_r($complete_config);
return $complete_config['config'];
} else {
trigger_error("thinkedit::parseXmlFolder() no config files found - aborting");
die;
return false;
}
} else {
trigger_error("thinkedit::parseXmlFolder() : {$folder} is not found - aborting");
die;
return false;
}
}
/**
* @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");
}
public static function get_creation_sql($db_doc, $node_schema, $node_view)
{
if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
$ddl = "-- {$node_view['description']}\n";
}
$view_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']);
$definer = strlen($node_view['owner']) > 0 ? xml_parser::role_enum($db_doc, $node_view['owner']) : 'CURRENT_USER';
$ddl = "CREATE OR REPLACE DEFINER = {$definer} SQL SECURITY DEFINER VIEW {$view_name}\n";
$ddl .= " AS " . static::get_view_query($node_view) . ";";
return $ddl;
}
public function testDefineRows()
{
$schema_parent = simplexml_load_string($this->xml_parent);
$schema_child = simplexml_load_string($this->xml_child);
$schema_parent_child = simplexml_load_string($this->xml_parent_and_child);
$schema_grandchild = simplexml_load_string($this->xml_grandchild);
// xml_parser::composite_doc($schema_parent, $schema_child);
//xml_parser::composite_doc($schema_parent, $schema_grandchild);
xml_parser::composite_doc($schema_parent, $schema_child);
xml_parser::composite_doc($schema_parent_child, $schema_grandchild);
}
public static function convert($file)
{
$file_name = realpath($file);
$fp_slonik = fopen($file_name, 'r');
if (!$fp_slonik) {
throw new exception("failed to open slonik script " . $file . " for conversion");
}
$doc = new SimpleXMLElement('<dbsteward></dbsteward>');
$line = '';
while (($c = fgetc($fp_slonik)) !== FALSE) {
switch ($c) {
// catch lines starting with # comments and ignore them
case '#':
// only if line hasn't started yet
if (strlen(trim($line)) == 0) {
$comment = TRUE;
}
break;
// convert newlines to spaces
// convert newlines to spaces
case "\n":
$c = ' ';
// newline encountered, so comment is over
$comment = FALSE;
break;
// the statement terminated
// the statement terminated
case ';':
// not in comment state?
if (!$comment) {
self::parse_line($doc, $line);
$line = '';
}
break;
// by default, add character to the line
// by default, add character to the line
default:
// late-start # comment line
$trimmed_line = trim($line);
if ($trimmed_line[0] == '#') {
$comment = TRUE;
} else {
if (!$comment) {
$line .= $c;
}
}
break;
}
}
fclose($fp_slonik);
//xml_parser::validate_xml($doc->asXML());
return xml_parser::format_xml($doc->saveXML());
}
/**
* Creates and returns SQL for creation of trigger.
*
* @return created SQL
*/
public static function get_creation_sql($node_language)
{
/*
CREATE [ PROCEDURAL ] LANGUAGE name
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR valfunction ]
/**/
$ddl = "CREATE " . (strcasecmp(dbsteward::string_cast($node_language['trusted']), 'true') == 0 ? "TRUSTED " : "") . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . (strlen($node_language['handler']) > 0 ? "HANDLER " . pgsql8::get_quoted_function_name($node_language['handler']) : "") . (strlen($node_language['validator']) > 0 ? "VALIDATOR " . pgsql8::get_quoted_function_name($node_language['validator']) : "") . " ;\n";
if (strlen($node_language['owner']) > 0) {
$ddl .= "ALTER " . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_language['owner']) . " ;\n";
}
return $ddl;
}
/**
* Creates SQL diff of two DBSteward XML definition documents
*
* @return array output files list
*/
public static function diff_xml($old_xml_file, $new_xml_file, $upgrade_prefix)
{
$old_database = simplexml_load_file($old_xml_file);
if ($old_database === false) {
throw new Exception("failed to simplexml_load_file() " . $old_xml_file);
}
$old_database = xml_parser::sql_format_convert($old_database);
$new_database = simplexml_load_file($new_xml_file);
if ($new_database === false) {
throw new Exception("failed to simplexml_load_file() " . $new_xml_file);
}
$new_database = xml_parser::sql_format_convert($new_database);
pgsql8::diff_doc($old_xml_file, $new_xml_file, $old_database, $new_database, $upgrade_prefix);
}
public function testDependencyOfChild()
{
$parent = '<schema name="parentSchema" />';
$parent_table = '
<table name="parentTable">
</table>';
$child = '<schema name="childSchema" />';
$child_table = '
<table name="childTable" inheritsSchema="parentSchema" inheritsTable="parentTable">
</table>';
$parent_obj = array('schema' => simplexml_load_string($parent), 'table' => simplexml_load_string($parent_table));
$child_obj = array('schema' => simplexml_load_string($child), 'table' => simplexml_load_string($child_table));
$this->assertTrue(xml_parser::table_has_dependency($child_obj, $parent_obj));
$this->assertFalse(xml_parser::table_has_dependency($parent_obj, $child_obj));
}
/**
* 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;
}
/**
* @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);
}
/**
* Creates and returns SQL for creation of the schema.
*
* @return created SQL
*/
public static function get_creation_sql($node_schema)
{
if (strcasecmp('public', $node_schema['name']) == 0) {
// don't create the public schema
$ddl = '';
} else {
$schema_name = pgsql8::get_quoted_schema_name($node_schema['name']);
$ddl = "CREATE SCHEMA " . $schema_name . ";\n";
// schema ownership
if (isset($node_schema['owner']) && strlen($node_schema['owner']) > 0) {
// see dtd owner attribute enum: ROLE_OWNER, ROLE_APPLICATION, ROLE_SLONY
// map ROLE_ enums to database->role->owner etc
$ddl .= "ALTER SCHEMA " . $schema_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_schema['owner']) . ";\n";
}
// schema comment
if (isset($node_schema['description']) && strlen($node_schema['description']) > 0) {
$ddl .= "COMMENT ON SCHEMA " . $schema_name . " IS '" . pg_escape_string($node_schema['description']) . "';\n";
}
}
return $ddl;
}
private function diff($old, $new, $expected)
{
$ofs = new mock_output_file_segmenter();
$old = '<dbsteward><database/>' . $old . '</dbsteward>';
$new = '<dbsteward><database/>' . $new . '</dbsteward>';
$old_doc = simplexml_load_string($old);
$new_doc = simplexml_load_string($new);
dbsteward::$old_database = $old_doc;
dbsteward::$new_database = $new_doc;
pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order($old_doc);
pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order($new_doc);
pgsql8_diff_types::apply_changes($ofs, $old_doc->schema, $new_doc->schema);
$sql = trim(preg_replace('/\\n\\n+/', "\n", preg_replace('/^--.*$/m', '', $ofs->_get_output())));
$this->assertEquals($expected, $sql);
}
private function common_structure($old, $new)
{
dbsteward::$old_database = new SimpleXMLElement($old);
dbsteward::$new_database = new SimpleXMLElement($new);
mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
mysql5_diff::revoke_permissions($ofs1, $ofs3);
mysql5_diff::update_structure($ofs1, $ofs3);
mysql5_diff::update_permissions($ofs1, $ofs3);
// @TODO: assert expected = actual
// echo "\n\nofs 1:\n\n";
// echo $ofs1->_get_output();
// echo "\n\nofs 3:\n\n";
// echo $ofs3->_get_output();
}
public function xml_data_insert($def_file, $data_file)
{
dbsteward::info("Automatic insert data into " . $def_file . " from " . $data_file);
$def_doc = simplexml_load_file($def_file);
if (!$def_doc) {
throw new exception("Failed to load " . $def_file);
}
$data_doc = simplexml_load_file($data_file);
if (!$data_doc) {
throw new exception("Failed to load " . $data_file);
}
// for each of the tables defined, act on rows addColumns definitions
foreach ($data_doc->schema as $data_schema) {
$xpath = "schema[@name='" . $data_schema['name'] . "']";
$def_schema = $def_doc->xpath($xpath);
if (count($def_schema) == 0) {
throw new exception("definition " . $xpath . " not found");
}
if (count($def_schema) > 1) {
throw new exception("more than one " . $xpath . " found");
}
$def_schema = $def_schema[0];
foreach ($data_schema->table as $data_table) {
$xpath = "table[@name='" . $data_table['name'] . "']";
$def_table = $def_schema->xpath($xpath);
if (count($def_table) == 0) {
throw new exception("definition " . $xpath . " not found");
}
if (count($def_table) > 1) {
throw new exception("more than one " . $xpath . " found");
}
$def_table = $def_table[0];
if (!isset($data_table->rows) || !isset($data_table->rows->row)) {
throw new exception($xpath . " rows->row definition is incomplete");
}
if (count($data_table->rows->row) > 1) {
throw new exception("Unexpected: more than one rows->row found in " . $xpath . " definition");
}
$definition_columns = preg_split("/[\\,\\s]+/", $def_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$new_columns = preg_split("/[\\,\\s]+/", $data_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
for ($i = 0; $i < count($new_columns); $i++) {
$new_column = $new_columns[$i];
dbsteward::info("Adding rows column " . $new_column . " to definition table " . $def_table['name']);
if (in_array($new_column, $definition_columns)) {
throw new exception("new column " . $new_column . " is already defined in dbsteward definition file");
}
$def_table->rows['columns'] = $def_table->rows['columns'] . ", " . $new_column;
$col_value = $data_table->rows->row->col[$i];
// add the value to each row col set in def_table->rows
foreach ($def_table->rows->row as $row) {
$row->addChild('col', $col_value);
}
}
}
}
$def_file_modified = $def_file . '.xmldatainserted';
dbsteward::notice("Saving modified dbsteward definition as " . $def_file_modified);
return xml_parser::save_xml($def_file_modified, $def_doc->saveXML());
}
public static function get_sql($db_doc, $node_schema, $node_object, $node_permission)
{
$operations = mssql10_permission::get_permission_operations($node_permission);
$roles = preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY);
$object_type = strtoupper($node_object->getName());
$sql = '';
switch ($object_type) {
case 'SCHEMA':
$object_name = mssql10::get_quoted_schema_name($node_schema['name']);
for ($i = 0; $i < count($operations); $i++) {
// see http://msdn.microsoft.com/en-us/library/ms187940.aspx
if (strcasecmp($operations[$i], 'USAGE') == 0) {
// for schemas, translate USAGE into SELECT
$operations[$i] = 'SELECT';
}
if (strcasecmp($operations[$i], 'ALL') == 0) {
$operations[$i] = 'SELECT';
$operations[] = 'INSERT';
$operations[] = 'UPDATE';
$operations[] = 'DELETE';
}
// CREATE TABLE permission is database-wide
// so create it explicitly here in-line
// and then remove it from the list of operations to define
if (strcasecmp($operations[$i], 'CREATE TABLE') == 0) {
for ($j = 0; $j < count($roles); $j++) {
$sql .= "GRANT CREATE TABLE TO " . mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])) . ";\n";
}
unset($operations[$i]);
$operations = array_merge($operations);
$i--;
}
}
break;
case 'SEQUENCE':
for ($i = 0; $i < count($operations); $i++) {
// for sequences, translate USAGE into INSERT
if (strcasecmp($operations[$i], 'USAGE') == 0) {
$operations[$i] = 'INSERT';
}
}
// give explicit DELETE permission for pseudo sequences, implemented as mssql10_bit_table
if (!in_array('DELETE', $operations)) {
$operations[] = 'DELETE';
}
case 'TABLE':
case 'VIEW':
case 'FUNCTION':
$object_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_object['name']);
break;
default:
throw new exception("unknown object type encountered: " . $object_type);
}
for ($j = 0; $j < count($roles); $j++) {
$with = '';
if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
$with = "WITH " . $node_permission['with'] . " OPTION";
}
// treat sequences as tables, because that's how mssql10_bit_table created them
if (strcasecmp($object_type, 'SEQUENCE') == 0) {
$object_type = 'TABLE';
}
// translate pg to ms object type names that the GRANT ... CLASS :: specifier accepts
$ms_object_type = $object_type;
// in mssql a table is an object when doing grants
if (strcasecmp($object_type, 'TABLE') == 0) {
$ms_object_type = 'OBJECT';
}
// in mssql a view is an object when doing grants
if (strcasecmp($object_type, 'VIEW') == 0) {
$ms_object_type = 'OBJECT';
}
// in mssql a function and a procedure is an object when doing grants
if (strcasecmp($object_type, 'FUNCTION') == 0) {
$ms_object_type = 'OBJECT';
}
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $operations), $ms_object_type, $object_name, mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])), $with);
}
return $sql;
}
public static function add_data_row(&$node_table, $row)
{
// sanity check alpha keys this table expects
$data_columns = self::get_column_list($node_table);
$row_data_columns = array_keys($row);
$diff = array_diff($row_data_columns, $data_columns);
if (count($diff) > 0) {
throw new exception("table " . $node_table['name'] . " (" . implode(',', $data_columns) . ") does not contain all columns specified in row_data_columns (" . implode(',', $row_data_columns) . ") diff: (" . implode(',', $diff) . ")");
}
$pk_cols = self::primary_key_columns($node_table);
// only check if it has primary key columns if the table has a primary key
if (count($pk_cols) > 0) {
$diff = array_diff($pk_cols, $row_data_columns);
if (count($diff) > 0) {
throw new exception("table " . $node_table['name'] . " rows element missing primary key column. diff: (" . implode(',', $diff) . ")");
}
}
// collect rows to add in $new_rows, index by row_data_columns
// @TODO: the caller then data_rows_overlay()'s these all at once
$new_rows = array();
// $new_rows contains all of the variants of rows collections to add to the table
// it is expected that there is a low number of insert statement variants
if (!isset($new_rows[$row_data_columns])) {
$new_rows[$row_data_columns] = new SimpleXMLElement('<rows/>');
}
// have the xml_parser compositor add the row with it's overlay logic
// which compensates for additional columns, missing columns
// and all the rest of the variants for <rows> <row> elements
$node_new_row = $new_rows[$row_data_columns]->addChild('row');
foreach ($row as $column_name => $column_value) {
$new_rows[$row_data_columns]['columns'] .= $column_name . ',';
$node_new_row->addChild('col', xml_parser::ampersand_magic(sql_parser::quoted_value_strip($column_value)));
}
$new_rows[$row_data_columns]['columns'] = substr($new_rows[$row_data_columns]['columns'], 0, -1);
//var_dump($new_rows[$row_data_columns]->asXML());
//die('add_data_row tracer 115');
// see @TODO: above caller collation
xml_parser::data_rows_overlay($node_table, $new_rows[$row_data_columns]);
}
/**
* Creates and returns SQL command for creation of the sequence.
*
* @return created SQL command
*/
public static function get_creation_sql($node_schema, $node_sequence)
{
if (isset($node_sequence['start']) && !is_numeric((string) $node_sequence['start'])) {
throw new exception("start value is not numeric: " . $node_sequence['start']);
}
if (isset($node_sequence['inc']) && !is_numeric((string) $node_sequence['inc'])) {
throw new exception("increment by value is not numeric: " . $node_sequence['inc']);
}
if (isset($node_sequence['max']) && !is_numeric((string) $node_sequence['max'])) {
throw new exception("max value is not numeric: " . $node_sequence['max']);
}
if (isset($node_sequence['min']) && !is_numeric((string) $node_sequence['min'])) {
throw new exception("min value is not numeric: " . $node_sequence['min']);
}
format::set_context_replica_set_id($node_sequence);
$cache = 1;
if (isset($node_sequence['cache'])) {
if (!is_numeric((string) $node_sequence['cache'])) {
throw new exception($node_schema['name'] . '.' . $node_sequence['name'] . " cache parameter for is not numeric: " . $node_sequence['cache']);
}
$cache = (int) $node_sequence['cache'];
}
if (isset($node_sequence['start'])) {
$start = (int) $node_sequence['start'];
} else {
if (isset($node_sequence['min'])) {
$start = (int) $node_sequence['min'];
} else {
$start = '1';
}
}
if (isset($node_sequence['inc'])) {
$inc = (int) $node_sequence['inc'];
} else {
$inc = '1';
}
$max_value = (int) $node_sequence['max'];
if ($max_value > 0) {
$max_value = 'MAXVALUE ' . $max_value;
} else {
$max_value = 'NO MAXVALUE';
}
$min_value = (int) $node_sequence['min'];
if ($min_value > 0) {
$min_value = 'MINVALUE ' . $min_value;
} else {
$min_value = 'NO MINVALUE';
}
$cycle = 'NO CYCLE';
if (strlen($node_sequence['cycle']) > 0 && strcasecmp($node_sequence['cycle'], 'false') != 0) {
$cycle = 'CYCLE';
}
$owned_by = '';
if (strlen($node_sequence['ownedBy']) > 0) {
$owned_by = " OWNED BY " . $node_sequence['ownedBy'];
}
$sequence_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_sequence['name']);
$ddl = "CREATE SEQUENCE " . $sequence_name . "\n\tSTART WITH " . $start . "\n\tINCREMENT BY " . $inc . "\n\t" . $max_value . "\n\t" . $min_value . "\n\tCACHE " . $cache . "\n\t" . $cycle . $owned_by . ";\n";
// sequence ownership
if (isset($node_sequence['owner'])) {
$ddl .= "ALTER TABLE " . $sequence_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_sequence['owner']) . ";\n";
}
// sequence comment
if (isset($schema['description']) && strlen($schema['description']) > 0) {
$ddl .= "COMMENT ON SCHEMA " . $sequence_name . " IS '" . pg_escape_string($schema['description']) . "';\n";
}
return $ddl;
}
private function common($xml, $expected)
{
$dbs = new SimpleXMLElement($xml);
$ofs = new mock_output_file_segmenter();
dbsteward::$new_database = $dbs;
$table_dependency = xml_parser::table_dependency_order($dbs);
mysql5::build_data($dbs, $ofs, $table_dependency);
$actual = $ofs->_get_output();
// get rid of extra whitespace
$expected = preg_replace("/^ +/m", "", $expected);
$expected = trim(preg_replace("/\n+/", "\n", $expected));
// echo $actual;
// get rid of comments
$actual = preg_replace("/\\s*-- .*\$/m", '', $actual);
// get rid of extra whitespace
$actual = preg_replace("/^ +/m", "", $actual);
$actual = trim(preg_replace("/\n+/", "\n", $actual));
$this->assertEquals($expected, $actual);
}
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());
}
require_once 'inc/class.syncml_wbxml_response.inc.php';
require_once 'inc/class.syncml_response.inc.php';
require_once 'inc/class.syncml_message.inc.php';
$file_date = gettimeofday(true);
// this is a ugly, ugly hack
//$GLOBALS['phpgw']->db->query('TRUNCATE phpgw_access_log');
if (!isset($_SERVER['CONTENT_TYPE']) || $_SERVER['REQUEST_METHOD'] != 'POST') {
exit('I\'m a SyncML server (1)');
}
$post_input = implode("\r\n", file('php://input'));
file_put_contents('/tmp/' . $file_date . '-a.xml', $post_input);
switch ($_SERVER['CONTENT_TYPE']) {
case 'application/vnd.syncml+wbxml':
$post_input = wbxml_decode($post_input);
case 'application/vnd.syncml+xml':
$parser = new xml_parser();
$response = new syncml_response();
break;
default:
exit('I\'m a SyncML server (2)');
}
$message = new syncml_message();
// the header
$header = $parser->parse($post_input, new xml_offset_mapper(array('SYNCML', 'SYNCHDR')));
$message->process_header($header);
unset($header);
// the body
$body = $parser->parse($post_input, new xml_offset_mapper(array('SYNCML', 'SYNCBODY')));
$message->process_body($body);
unset($body, $GLOBALS['HTTP_RAW_POST_DATA']);
// execute and print everything
private function diff($old, $new, $expected1, $expected3, $message = '')
{
dbsteward::$old_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $old . '</dbsteward>'));
dbsteward::$new_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $new . '</dbsteward>'));
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
mysql5_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database);
mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
mysql5_diff::update_structure($ofs1, $ofs3);
$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}");
}
/**
* @group pgsql8
*/
public function testGoodSerialIdPGSQL8()
{
// reset options
$this->apply_options_pgsql8();
dbsteward::$require_slony_id = TRUE;
dbsteward::$generate_slonik = TRUE;
$this->set_xml_content_a($this->pgsql8_xml_good_serial_id);
// build the DDL first, incase dbsteward code wants to throw about something
pgsql8::build($this->output_prefix, xml_parser::xml_composite(array($this->xml_file_a)));
// if that worked, build a db with this definition
$this->pgsql8->create_db();
$this->assertStringNotEqualsFile($this->output_prefix . '_build.sql', '');
$this->pgsql8->run_file($this->output_prefix . '_build.sql');
}
/**
* if in_both = false, what rows in A are not in B ?
*
* if in_both = true, what rows are in A and B ?
* - when a is empty, all of b's are returned
* - a's row members are the ones returned when in_both rows are found
* this is important when comparing tables whose rows are the same but have added columns
*
* @return void
*/
public static function table_data_rows_compare($table_a, $table_b, $in_both, &$a_rows, &$b_rows, &$changes)
{
$a_rows = array();
$b_rows = array();
$changes = array();
$table_a_data_rows = dbx::get_table_rows($table_a);
$table_b_data_rows = dbx::get_table_rows($table_b);
// data_row_overlay_key_search() needs these to do the matching
$table_a_data_rows_columns = preg_split("/[\\,\\s]+/", $table_a_data_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$table_b_data_rows_columns = preg_split("/[\\,\\s]+/", $table_b_data_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
if ($table_a_data_rows == null || $table_a_data_rows_columns == null) {
// table_a has no data rows
if ($in_both) {
// what rows are in A and B? none in A to compare
} else {
// what rows in A are not in B? none to list
}
} else {
if ($table_b_data_rows == null || $table_b_data_rows_columns == null) {
// table_b has no data rows
if ($in_both) {
// what rows are in A and B? none in B to compare
} else {
// what rows in A are not in B? omg, all of them!
$a_rows = $table_a_data_rows->row;
}
} else {
$table_b_primary_keys = mysql5_table::primary_key_columns($table_b);
// @NOTICE: in the case of table row data comparising for DIFFING, the primary keys to use are table B
// but the table B and A columns are reversed compared to data_rows_overlay() as the comparison is B vs A not base vs overlay (AvB)
// so the columns to match as base and overlay are reversed, comared to other calls to data_row_overlay_primary_key_index()
$primary_key_index = xml_parser::data_row_overlay_primary_key_index($table_b_primary_keys, $table_b_data_rows_columns, $table_a_data_rows_columns);
$base_pklookup = array();
$i = 0;
if (count($primary_key_index['base'])) {
foreach ($table_b_data_rows->row as $base_row) {
$s = '';
foreach ($primary_key_index['base'] as $index) {
$s .= ':' . $base_row->col[$index];
}
$base_pklookup[$s] = $i++;
}
}
$table_b_index = 0;
foreach ($table_a_data_rows->row as $table_a_data_row) {
$s = '';
foreach ($primary_key_index['overlay'] as $index) {
$s .= ':' . $table_a_data_row->col[$index];
}
if (array_key_exists($s, $base_pklookup)) {
$match = TRUE;
$table_b_index = $base_pklookup[$s];
} else {
$match = FALSE;
}
if ($match) {
// we found a match
//echo "rows match\n"; var_dump($table_a_data_row); var_dump($table_b_data_row);
$table_b_data_row =& $table_b_data_rows->row[$table_b_index];
if ($in_both) {
// looking for rows in both
// is the row marked for delete in A?
if (self::table_data_row_deleted($table_a_data_row)) {
// don't return changes, we are looking for rows in_both
} else {
if (self::table_data_row_deleted($table_b_data_row)) {
// don't return changes, we are looking for rows in_both
} else {
// do table data row diff, add rows to return by reference rows for both A and B
$changed_columns = array();
static::table_data_row_diff($table_a_data_rows_columns, $table_a_data_row, $table_b_data_rows_columns, $table_b_data_row, $changed_columns);
$a_rows[] = $table_a_data_row;
$b_rows[] = $table_b_data_row;
$changes[] = $changed_columns;
}
}
} else {
// is the A row marked for delete?
if (self::table_data_row_deleted($table_a_data_row)) {
// there was a match, but we are looking for A not in B, A row is marked deleted, don't return it
} else {
if (self::table_data_row_deleted($table_b_data_row)) {
// there was a match
// A is not deleted
// we are looking for A not in B
// B is deleted
$a_rows[] = $table_a_data_row;
}
}
}
} else {
// echo "rows don't match\n"; var_dump($table_a_data_row); var_dump($table_b_data_row);
// no match
if (!$in_both) {
// looking for A not in B
if (self::table_data_row_deleted($table_a_data_row)) {
// but the A row is marked deleted, don't return it
} else {
$a_rows[] = $table_a_data_row;
}
}
}
}
}
}
}
/**
* Insert any missing slonyId and slonySetId as specifeid by dbsteward mode variables
*
* @param SimpleXML $indoc
* @return SimpleXML
*/
public static function slonyid_number($indoc)
{
$outdoc = clone $indoc;
// start with --slonyidsetvalue=
$slony_set_id = dbsteward::$slonyid_set_value;
// start slony ID at --slonyidstartvalue=
$slony_id = dbsteward::$slonyid_start_value;
foreach ($outdoc->schema as $schema) {
xml_parser::slonyid_number_set($schema, $slony_set_id);
foreach ($schema->table as $table) {
xml_parser::slonyid_number_set($table, $slony_set_id);
xml_parser::slonyid_number_id($table, $slony_id);
foreach ($table->column as $column) {
// make sure any serial columns have slonySetId
if (sql99_column::is_serial($column['type'])) {
xml_parser::slonyid_number_set($column, $slony_set_id);
xml_parser::slonyid_number_id($column, $slony_id);
}
}
}
foreach ($schema->trigger as $trigger) {
xml_parser::slonyid_number_set($trigger, $slony_set_id);
}
foreach ($schema->sequence as $sequence) {
xml_parser::slonyid_number_set($sequence, $slony_set_id);
xml_parser::slonyid_number_id($sequence, $slony_id);
}
foreach ($schema->function as $function) {
xml_parser::slonyid_number_set($function, $slony_set_id);
}
foreach ($schema->sql as $sql) {
xml_parser::slonyid_number_set($sql, $slony_set_id);
}
}
return $outdoc;
}
protected function upgrade_db($format, $ofs1, $ofs2, $ofs3, $ofs4)
{
dbsteward::set_sql_format($format);
$doc_a = new SimpleXMLElement($this->{$format . '_xml_a'});
$doc_b = new SimpleXMLElement($this->{$format . '_xml_b'});
dbsteward::$old_database = $doc_a;
dbsteward::$new_database = $doc_b;
$diff_class = $format . '_diff';
$diff_class::$old_table_dependency = xml_parser::table_dependency_order($doc_a);
$diff_class::$new_table_dependency = xml_parser::table_dependency_order($doc_b);
$diff_class::diff_doc_work($ofs1, $ofs2, $ofs3, $ofs4);
}
/**
* @group mysql5
*/
public function testUpgradeNewTableMysql5()
{
$this->apply_options_mysql5();
$this->setup_mysql5();
// upgrade from base
// to base + strict action table + new resolution table
// check null specificity
$base_db_doc = xml_parser::xml_composite(array($this->xml_file_a));
$newtable_db_doc = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_b, $this->xml_file_c));
mysql5::build_upgrade('', 'newtable_upgrade_test_pgsql8_base', $base_db_doc, array(), $this->output_prefix, 'newtable_upgrade_test_pgsql8_newtable', $newtable_db_doc, array());
$text = file_get_contents($this->output_prefix . '_upgrade_single_stage.sql');
// make sure NOT NULL is specified for description column
$this->assertContains('`description` character varying(200) NOT NULL', $text);
// make sure NOT NULL is specified for resolution column
$this->assertContains('`resolution` character varying(16) NOT NULL', $text);
// make sure NOT NULL is NOT specified for points column
$this->assertNotContains('`points` int NOT NULL', $text);
}