/**
* Creates and returns SQL for dropping the trigger.
*
* @return created SQL
*/
public static function get_drop_sql($node_schema, $node_trigger)
{
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == NULL) {
throw new exception("Failed to find trigger table " . $node_trigger['table'] . " in schema node " . $node_schema['name']);
}
$table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']);
$ddl = "DROP TRIGGER " . pgsql8::get_quoted_object_name($node_trigger['name']) . " ON " . $table_name . ";\n";
return $ddl;
}
/**
* 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)
{
$column_type = mssql10_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign);
$definition = mssql10::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
if (strlen($node_column['default']) > 0) {
$definition .= " DEFAULT " . $node_column['default'];
} else {
if (!mssql10_column::null_allowed($node_table, $node_column) && $add_defaults) {
$default_col_value = mssql10_column::get_default_value($node_column['type']);
if ($default_col_value != NULL) {
$definition .= " DEFAULT " . $default_col_value;
}
}
}
if ($include_null_definition && !mssql10_column::null_allowed($node_table, $node_column)) {
$definition .= " NOT NULL";
}
return $definition;
}
/**
* Creates declaration string for the function. The string consists
* of function name, '(', list of argument types separated by ',' and ')'.
*
* @param $node_function
*/
public static function get_declaration($node_schema, $node_function)
{
$r = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_function_name($node_function['name']) . '(';
$parameters = dbx::get_function_parameters($node_function);
foreach ($parameters as $parameter) {
$arg = '';
if (strlen($parameter['name']) > 0) {
$arg .= '@' . $parameter['name'] . ' ';
}
$arg .= $parameter['type'];
if (isset($parameter['direction'])) {
$arg .= ' ' . $parameter['direction'];
}
$r .= $arg . ', ';
}
if (count($parameters) > 0) {
$r = substr($r, 0, -2);
}
$r .= ')';
return $r;
}
public static function get_enum_value_delete($node_schema, $node_type)
{
$ddl = '';
$reference_table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_type['name'] . '_enum_values');
$ddl = "DELETE FROM " . $reference_table_name . ";\n";
return $ddl;
}
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;
}
/**
* return SQL command for dropping the view
*
* @return string
*/
public function get_drop_sql($node_schema, $node_view)
{
$ddl = "DROP VIEW " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_view['name']) . ";\n";
return $ddl;
}
/**
* 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;
}
/**
* returns DDL to drop specified schema
*
* @return string
*/
public function get_drop_sql($node_schema)
{
$ddl = "DROP SCHEMA " . mssql10::get_quoted_schema_name($node_schema['name']) . ";\n";
return $ddl;
}
/**
* escape data types that need it
*
* @param mixed $value value to check for escaping
*
* @value mixed value, escaped as necessary
*/
public static function value_escape($type, $value, $db_doc = NULL)
{
if (strlen($value) > 0) {
// data types that should be quoted
$enum_regex = dbx::enum_regex($db_doc);
if (strlen($enum_regex) > 0) {
$enum_regex = '|' . $enum_regex;
}
$PATTERN_QUOTED_TYPES = "/^char.*|^string|^date.*|^time.*|^varchar.*|^interval|^money.*|^inet" . $enum_regex . "/i";
// strip quoting if it is a quoted type, it will be added after conditional conversion
if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
$value = mssql10::strip_single_quoting($value);
}
// complain when assholes use colon time notation instead of postgresql verbose for interval expressions
if (dbsteward::$require_verbose_interval_notation) {
if (preg_match('/interval/i', $type) > 0) {
if (substr($value, 0, 1) != '@') {
throw new exception("bad interval value: " . $value . " -- interval types must be postgresql verbose format: '@ 2 hours 30 minutes' etc for cfxn comparisons to work");
}
}
}
// mssql doesn't understand epoch
if (stripos('date', $type) !== FALSE && strcasecmp($value, 'epoch') == 0) {
$value = '1970-01-01';
}
// special case for postgresql type value conversion
// the boolean type for the column would have been translated to char(1) by xml_parser::mssql10_type_convert()
if (strcasecmp($type, 'char(1)') == 0) {
$value = mssql10::boolean_value_convert($value);
} else {
if (strcasecmp($type, 'datetimeoffset(7)') == 0) {
$value = date('c', strtotime($value));
// use date()'s ISO 8601 date format to be conformant
} else {
if (strcasecmp($type, 'datetime2') == 0) {
$value = date('Y-m-dTG:i:s', strtotime($value));
// use date() to make date format conformant
} else {
if (strcasecmp($type, 'time') == 0 && strlen($value) > 8) {
$value = substr($value, 0, 8);
}
}
}
}
if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
//@TODO: is there a better way to do mssql string escaping?
$value = "'" . str_replace("'", "''", $value) . "'";
}
} else {
// value is zero length, make it NULL
$value = "NULL";
}
return $value;
}
protected static function get_data_row_update($node_schema, $node_table, $old_data_row_columns, $old_data_row, $new_data_row_columns, $new_data_row, $changed_columns)
{
if (count($changed_columns) == 0) {
throw new exception("empty changed_columns passed");
}
// what columns from new_data_row are different in old_data_row?
// those are the ones to push through the update statement to make the database current
$old_columns = '';
$update_columns = '';
foreach ($changed_columns as $changed_column) {
if (!isset($changed_column['old_col'])) {
$old_columns .= 'NOTDEFINED, ';
} else {
$old_col_value = mssql10::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['old_col']);
$old_columns .= $changed_column['name'] . ' = ' . $old_col_value . ', ';
}
$update_col_name = mssql10::get_quoted_column_name($changed_column['name']);
$update_col_value = mssql10::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['new_col']);
$update_columns .= $update_col_name . ' = ' . $update_col_value . ', ';
}
// if the computed update_columns expression is < 5 chars, complain
if (strlen($update_columns) < 5) {
var_dump($update_columns);
throw new exception(sprintf("%s.%s update_columns is < 5 chars, unexpected", $node_schema['name'], $node_table['name']));
}
// kill trailing ', '
$update_columns = substr($update_columns, 0, -2);
$old_columns = substr($old_columns, 0, -2);
// use multiline comments here, so when data has newlines they can be preserved, but upgrade scripts don't catch on fire
$sql = sprintf("UPDATE %s.%s SET %s WHERE (%s); /* old values: %s */\n", mssql10::get_quoted_schema_name($node_schema['name']), mssql10::get_quoted_table_name($node_table['name']), $update_columns, dbx::primary_key_expression(dbsteward::$new_database, $node_schema, $node_table, $new_data_row_columns, $new_data_row), $old_columns);
return $sql;
}
/**
* Creates and returns SQL command for dropping the sequence.
*
* @return string
*/
public function get_drop_sql($node_schema, $node_sequence)
{
$ddl = "DROP TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_sequence['name']) . ";\n";
return $ddl;
}
public function get_constraint_drop_sql($constraint)
{
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");
}
$sql = "ALTER TABLE " . mssql10::get_quoted_schema_name($constraint['schema_name']) . '.' . mssql10::get_quoted_table_name($constraint['table_name']) . "\n\tDROP CONSTRAINT " . mssql10::get_quoted_object_name($constraint['name']) . ';';
return $sql;
}
protected function upgrade_db_mssql10()
{
$this->apply_options_mssql10();
// build the upgrade DDL first, incase dbsteward code wants to throw about something
mssql10::build_upgrade($this->xml_file_a, $this->xml_file_b);
// upgrade database to "B" with each stage file
$this->mssql10->run_file(__DIR__ . '/testdata/upgrade_stage1_schema1.sql');
$this->mssql10->run_file(__DIR__ . '/testdata/upgrade_stage2_data1.sql');
$this->mssql10->run_file(__DIR__ . '/testdata/upgrade_stage3_schema1.sql');
$this->mssql10->run_file(__DIR__ . '/testdata/upgrade_stage4_data1.sql');
$old_db_doc = xml_parser::xml_composite(array($this->xml_file_a));
$new_db_doc = xml_parser::xml_composite(array($this->xml_file_b));
mssql10::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
$this->mssql10->run_file($this->output_prefix . '_upgrade_stage1_schema1.sql');
$this->mssql10->run_file($this->output_prefix . '_upgrade_stage2_data1.sql');
$this->mssql10->run_file($this->output_prefix . '_upgrade_stage3_schema1.sql');
$this->mssql10->run_file($this->output_prefix . '_upgrade_stage4_data1.sql');
}