public AddColumnAction(SqlTableColumn column)
{
if (column == null)
throw new ArgumentNullException("column");
Column = column;
}
private ColumnInfo CreateColumnInfo(IRequest context, string tableName, SqlTableColumn column, TableColumnChecker columnChecker)
{
var expression = column.DefaultExpression;
if (column.IsIdentity && expression != null)
{
throw new InvalidOperationException(String.Format("Identity column '{0}' cannot define a DEFAULT expression.", column.ColumnName));
}
if (expression != null)
{
expression = columnChecker.CheckExpression(expression);
}
var columnName = columnChecker.StripTableName(tableName, column.ColumnName);
var columnType = column.ColumnType.Resolve(context);
return(new ColumnInfo(columnName, columnType)
{
DefaultExpression = expression,
IsNotNull = column.IsNotNull,
IndexType = column.IndexType
});
}
public AddColumnAction(SqlTableColumn column)
{
if (column == null)
{
throw new ArgumentNullException("column");
}
Column = column;
}
object IPreparable.Prepare(IExpressionPreparer preparer)
{
var column = new SqlTableColumn(ColumnName, ColumnType);
if (DefaultExpression != null)
column.DefaultExpression = DefaultExpression.Prepare(preparer);
column.IsNotNull = IsNotNull;
return column;
}
object IPreparable.Prepare(IExpressionPreparer preparer)
{
var newColumn = new SqlTableColumn(Column.ColumnName, Column.ColumnType) {
IsNotNull = Column.IsNotNull
};
var defaultExp = Column.DefaultExpression;
if (defaultExp != null)
newColumn.DefaultExpression = defaultExp.Prepare(preparer);
return new AddColumnAction(newColumn);
}
public static void AddColumn()
{
var column = new SqlTableColumn("a", PrimitiveTypes.Integer());
var statement = new AlterTableStatement(ObjectName.Parse("APP.test_table"), new AddColumnAction(column));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE APP.test_table ");
expected.Append("ADD COLUMN a INTEGER");
Assert.AreEqual(expected.ToString(), sql);
}
object IPreparable.Prepare(IExpressionPreparer preparer)
{
var column = new SqlTableColumn(ColumnName, ColumnType);
if (DefaultExpression != null)
{
column.DefaultExpression = DefaultExpression.Prepare(preparer);
}
column.IsNotNull = IsNotNull;
return(column);
}
public static void AddColumn()
{
var column = new SqlTableColumn("a", PrimitiveTypes.Integer());
var statement = new AlterTableStatement(ObjectName.Parse("APP.test_table"), new AddColumnAction(column));
var sql = statement.ToString();
var expected = new StringBuilder();
expected.Append("ALTER TABLE APP.test_table ");
expected.Append("ADD COLUMN a INTEGER");
Assert.AreEqual(expected.ToString(), sql);
}
public void SimpleCreate()
{
var tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()),
};
var statement = new CreateTableStatement(tableName, columns);
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
}
public void SimpleCreate()
{
var tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()),
};
var statement = new CreateTableStatement(tableName, columns);
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
}
object IPreparable.Prepare(IExpressionPreparer preparer)
{
var newColumn = new SqlTableColumn(Column.ColumnName, Column.ColumnType)
{
IsNotNull = Column.IsNotNull
};
var defaultExp = Column.DefaultExpression;
if (defaultExp != null)
{
newColumn.DefaultExpression = defaultExp.Prepare(preparer);
}
return(new AddColumnAction(newColumn));
}
public void AlterTableAddColumn()
{
var tableName = ObjectName.Parse("APP.test_table");
var column = new SqlTableColumn("reserved", PrimitiveTypes.Boolean());
var statement = new AlterTableStatement(tableName, new AddColumnAction(column));
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
Assert.AreEqual(1, result.TableInfo.ColumnCount);
Assert.AreEqual(0, ((SqlNumber) result.GetValue(0,0).Value).ToInt32());
var testTable = Query.GetTable(new ObjectName("test_table"));
Assert.IsNotNull(testTable);
Assert.AreEqual(6, testTable.TableInfo.ColumnCount);
}
public SqlTableColumn BuildColumn(ITypeResolver typeResolver, string tableName, IList<SqlTableConstraint> constraints)
{
var dataType = DataTypeBuilder.Build(typeResolver, DataType);
var columnInfo = new SqlTableColumn(ColumnName, dataType);
if (Default != null)
columnInfo.DefaultExpression = ExpressionBuilder.Build(Default);
if (IsIdentity) {
columnInfo.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY",
new[] { SqlExpression.Constant(tableName) });
columnInfo.IsIdentity = true;
}
foreach (var constraint in Constraints) {
if (String.Equals(ConstraintTypeNames.Check, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var exp = ExpressionBuilder.Build(constraint.CheckExpression);
constraints.Add(SqlTableConstraint.Check(null, exp));
} else if (String.Equals(ConstraintTypeNames.ForeignKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var fTable = constraint.ReferencedTable.Name;
var fColumn = constraint.ReferencedColumn.Text;
var onDelete = ForeignKeyAction.NoAction;
var onUpdate = ForeignKeyAction.NoAction;
if (!String.IsNullOrEmpty(constraint.OnDeleteAction))
onDelete = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnDeleteAction);
if (!String.IsNullOrEmpty(constraint.OnUpdateAction))
onUpdate = SqlCodeObjectBuilder.GetForeignKeyAction(constraint.OnUpdateAction);
constraints.Add(SqlTableConstraint.ForeignKey(null, new[]{ColumnName}, fTable, new[]{fColumn}, onDelete, onUpdate));
} else if (String.Equals(ConstraintTypeNames.PrimaryKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(SqlTableConstraint.PrimaryKey(null, new[]{ColumnName}));
} else if (String.Equals(ConstraintTypeNames.UniqueKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(SqlTableConstraint.UniqueKey(null, new[]{ColumnName}));
} else if (String.Equals(ConstraintTypeNames.NotNull, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
columnInfo.IsNotNull = true;
} else if (String.Equals(ConstraintTypeNames.Null, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
columnInfo.IsNotNull = false;
}
}
return columnInfo;
}
public void AlterTableAddColumn()
{
var tableName = ObjectName.Parse("APP.test_table");
var column = new SqlTableColumn("reserved", PrimitiveTypes.Boolean());
var statement = new AlterTableStatement(tableName, new AddColumnAction(column));
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
Assert.AreEqual(1, result.TableInfo.ColumnCount);
Assert.AreEqual(0, ((SqlNumber)result.GetValue(0, 0).Value).ToInt32());
var testTable = Query.GetTable(new ObjectName("test_table"));
Assert.IsNotNull(testTable);
Assert.AreEqual(6, testTable.TableInfo.ColumnCount);
}
public void WithColumnDefault()
{
var tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()) {
DefaultExpression = SqlExpression.Parse("((67 * 90) + 22)")
},
new SqlTableColumn("date", PrimitiveTypes.TimeStamp()) {
DefaultExpression = SqlExpression.Parse("GetDate()")
}
};
var statement = new CreateTableStatement(tableName, columns);
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
}
private ColumnInfo CreateColumnInfo(IContext context, string tableName, SqlTableColumn column, TableColumnChecker columnChecker)
{
var expression = column.DefaultExpression;
if (column.IsIdentity && expression != null)
{
throw new InvalidOperationException($"Identity column '{column.ColumnName}' cannot define a DEFAULT expression.");
}
if (expression != null)
{
expression = columnChecker.CheckExpression(expression);
}
var columnName = columnChecker.StripTableName(tableName, column.ColumnName);
// TODO: support for dynamic types such as #ROW ?
var columnType = column.ColumnType;
return(new ColumnInfo(columnName, columnType, expression));
}
public void WithColumnDefault()
{
var tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar())
{
DefaultExpression = SqlExpression.Parse("((67 * 90) + 22)")
},
new SqlTableColumn("date", PrimitiveTypes.TimeStamp())
{
DefaultExpression = SqlExpression.Parse("GetDate()")
}
};
var statement = new CreateTableStatement(tableName, columns);
ITable result = null;
Assert.DoesNotThrow(() => result = statement.Execute(Query));
Assert.IsNotNull(result);
Assert.AreEqual(1, result.RowCount);
}
public void WithColumnIndex()
{
tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()) {
IndexType = "BLIST"
},
new SqlTableColumn("date", PrimitiveTypes.TimeStamp()) {
DefaultExpression = SqlExpression.Parse("GetDate()")
}
};
AdminQuery.CreateTable(tableName, columns);
}
public void WithIndexedColumn_CustomIndex()
{
tableName = ObjectName.Parse("APP.test");
var query = CreateQuery(CreateAdminSession(Database));
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()) {
IndexType = "foo"
},
};
query.CreateTable(tableName, columns);
query.Commit();
query = CreateQuery(CreateAdminSession(Database));
var table = query.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.TableInfo.ColumnCount);
}
private ColumnInfo CreateColumnInfo(string tableName, SqlTableColumn column, TableColumnChecker columnChecker)
{
var expression = column.DefaultExpression;
if (column.IsIdentity && expression != null)
throw new InvalidOperationException(String.Format("Identity column '{0}' cannot define a DEFAULT expression.", column.ColumnName));
if (expression != null)
expression = columnChecker.CheckExpression(expression);
var columnName = columnChecker.StripTableName(tableName, column.ColumnName);
return new ColumnInfo(columnName, column.ColumnType) {
DefaultExpression = expression,
IsNotNull = column.IsNotNull
};
}
public void WithColumnDefault()
{
tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()) {
DefaultExpression = SqlExpression.Parse("((67 * 90) + 22)")
},
new SqlTableColumn("date", PrimitiveTypes.TimeStamp()) {
DefaultExpression = SqlExpression.Parse("GetDate()")
}
};
AdminQuery.CreateTable(tableName, columns);
// TODO: Assert it exists and has the structure desired...
}
public void SimpleCreate()
{
tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()),
};
AdminQuery.CreateTable(tableName, columns);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.TableInfo.ColumnCount);
// TODO: Assert it exists and has the structure desired...
}
internal SqlTableColumn AsTableColumn()
{
var column = new SqlTableColumn(ColumnName, ColumnType) {
IsNotNull = !IsNullable
};
if (Default != null) {
SqlExpression defaultExpression;
if (DefaultIsExpression) {
defaultExpression = SqlExpression.Parse((string)Default);
} else {
defaultExpression = SqlExpression.Constant(Field.Create(Default));
}
column.DefaultExpression = defaultExpression;
}
return column;
}
public static void Serialize(SqlTableColumn column, BinaryWriter writer)
{
writer.Write(column.ColumnName);
TypeSerializer.SerializeTo(writer, column.ColumnType);
writer.Write(column.IsNotNull);
writer.Write(column.IsIdentity);
SqlExpression.Serialize(column.DefaultExpression, writer);
}
public static void Build(IQueryContext context, CreateTableNode node, ICollection<SqlStatement> statements)
{
string idColumn = null;
var dataTypeBuilder = new DataTypeBuilder();
var tableName = node.TableName;
var objTableName = ObjectName.Parse(tableName.Name);
var constraints = new List<ConstraintInfo>();
var columns = new List<SqlTableColumn>();
var expBuilder = new ExpressionBuilder();
foreach (var column in node.Columns) {
var dataType = dataTypeBuilder.Build(context.TypeResolver(), column.DataType);
var columnInfo = new SqlTableColumn(column.ColumnName.Text, dataType);
if (column.Default != null)
columnInfo.DefaultExpression = expBuilder.Build(column.Default);
if (column.IsIdentity) {
if (!String.IsNullOrEmpty(idColumn))
throw new InvalidOperationException(String.Format("Table {0} defines already {1} as identity column.",
node.TableName, idColumn));
if (column.Default != null)
throw new InvalidOperationException(String.Format("The identity column {0} cannot have a DEFAULT constraint.",
idColumn));
idColumn = column.ColumnName.Text;
columnInfo.DefaultExpression = SqlExpression.FunctionCall("UNIQUEKEY",
new[] {SqlExpression.Constant(node.TableName.Name)});
}
foreach (var constraint in column.Constraints) {
if (String.Equals(ConstraintTypeNames.Check, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var exp = expBuilder.Build(constraint.CheckExpression);
constraints.Add(ConstraintInfo.Check(objTableName, exp, column.ColumnName.Text));
} else if (String.Equals(ConstraintTypeNames.ForeignKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var fTable = ObjectName.Parse(constraint.ReferencedTable.Name);
var fColumn = constraint.ReferencedColumn.Text;
var fkey = ConstraintInfo.ForeignKey(objTableName, column.ColumnName.Text, fTable, fColumn);
if (!String.IsNullOrEmpty(constraint.OnDeleteAction))
fkey.OnDelete = GetForeignKeyAction(constraint.OnDeleteAction);
if (!String.IsNullOrEmpty(constraint.OnUpdateAction))
fkey.OnUpdate = GetForeignKeyAction(constraint.OnUpdateAction);
constraints.Add(fkey);
} else if (String.Equals(ConstraintTypeNames.PrimaryKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(ConstraintInfo.PrimaryKey(objTableName, column.ColumnName.Text));
} else if (String.Equals(ConstraintTypeNames.UniqueKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(ConstraintInfo.Unique(objTableName, column.ColumnName.Text));
} else if (String.Equals(ConstraintTypeNames.NotNull, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
columnInfo.IsNotNull = true;
} else if (String.Equals(ConstraintTypeNames.Null, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
columnInfo.IsNotNull = false;
}
}
columns.Add(columnInfo);
}
foreach (var constraint in node.Constraints) {
if (String.Equals(ConstraintTypeNames.Check, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var exp = expBuilder.Build(constraint.CheckExpression);
constraints.Add(ConstraintInfo.Check(constraint.ConstraintName, objTableName, exp, constraint.Columns.ToArray()));
} else if (String.Equals(ConstraintTypeNames.PrimaryKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(ConstraintInfo.PrimaryKey(constraint.ConstraintName, objTableName, constraint.Columns.ToArray()));
} else if (String.Equals(ConstraintTypeNames.UniqueKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
constraints.Add(ConstraintInfo.Unique(constraint.ConstraintName, objTableName, constraint.Columns.ToArray()));
} else if (String.Equals(ConstraintTypeNames.ForeignKey, constraint.ConstraintType, StringComparison.OrdinalIgnoreCase)) {
var fTable = ObjectName.Parse(constraint.ReferencedTableName.Name);
var fColumns = constraint.ReferencedColumns;
var fkey = ConstraintInfo.ForeignKey(constraint.ConstraintName, objTableName, constraint.Columns.ToArray(), fTable,
fColumns.ToArray());
if (!String.IsNullOrEmpty(constraint.OnDeleteAction))
fkey.OnDelete = GetForeignKeyAction(constraint.OnDeleteAction);
if (!String.IsNullOrEmpty(constraint.OnUpdateAction))
fkey.OnUpdate = GetForeignKeyAction(constraint.OnUpdateAction);
constraints.Add(fkey);
}
}
//TODO: Optimization: merge same constraints
statements.Add(MakeCreateTable(tableName.Name, columns, node.IfNotExists, node.Temporary));
foreach (var constraint in constraints) {
statements.Add(MakeAlterTableAddConstraint(tableName.Name, constraint));
}
}
public void SimpleCreateFromUnauthorized()
{
tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()),
};
var expected = Is.InstanceOf<SecurityException>();
Assert.Throws(expected, () => UserQuery.CreateTable(tableName, columns));
}
public void WithIndexedColumn_InsertSearch()
{
tableName = ObjectName.Parse("APP.test");
var columns = new SqlTableColumn[] {
new SqlTableColumn("id", PrimitiveTypes.Integer()),
new SqlTableColumn("name", PrimitiveTypes.VarChar()) {
IndexType = DefaultIndexTypes.InsertSearch
},
};
AdminQuery.CreateTable(tableName, columns);
var table = AdminQuery.Access().GetTable(tableName);
Assert.IsNotNull(table);
Assert.AreEqual(2, table.TableInfo.ColumnCount);
}