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