protected override bool OnSetUp(string testName, IQuery query)
{
procName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Statements.Add(new CallStatement(ObjectName.Parse("proc2")));
var procInfo = new PlSqlProcedureInfo(procName, new RoutineParameter[0], body);
query.Access().CreateObject(procInfo);
funcName = ObjectName.Parse("APP.func1");
body = new PlSqlBlockStatement();
body.Statements.Add(new ReturnStatement(SqlExpression.Constant(22)));
var funcInfo = new PlSqlFunctionInfo(funcName, new RoutineParameter[0], PrimitiveTypes.Integer(), body);
query.Access().CreateObject(funcInfo);
return(true);
}
private void CreateProcedureTrigger(IQuery query, ObjectName tableName)
{
var procedurName = ObjectName.Parse("APP.proc1");
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.Constant(33)));
var procedureInfo = new PlSqlProcedureInfo(procedurName, new RoutineParameter[0], body);
query.Access().CreateObject(procedureInfo);
var triggerName = ObjectName.Parse("APP.trigger2");
var eventTime = TriggerEventTime.After;
var eventType = TriggerEventType.Insert | TriggerEventType.Update;
var triggerInfo = new ProcedureTriggerInfo(triggerName, tableName, eventTime, eventType, procedurName);
query.Access().CreateObject(triggerInfo);
}
protected override bool OnTearDown(string testName, IQuery query)
{
var tn1 = ObjectName.Parse("APP.test_table1");
var tn2 = ObjectName.Parse("APP.test_table2");
if (query.Access().TableExists(tn2))
{
query.Access().DropAllTableConstraints(tn2);
}
if (query.Access().TableExists(tn1))
{
query.Access().DropAllTableConstraints(tn1);
}
query.Access().DropObject(DbObjectType.Table, tn2);
query.Access().DropObject(DbObjectType.Table, tn1);
return(true);
}
private void CreateProcedure3(IQuery query)
{
var procName = ObjectName.Parse("APP.proc3");
var args = new[] {
new RoutineParameter("a", PrimitiveTypes.String()),
new RoutineParameter("b", PrimitiveTypes.String(), ParameterDirection.Output)
};
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("c", PrimitiveTypes.String()));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("c"),
SqlExpression.VariableReference("a")));
body.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("b"),
SqlExpression.VariableReference("c")));
var procInfo = new PlSqlProcedureInfo(procName, args, body);
query.Access().CreateObject(procInfo);
}
public void FromMap()
{
AdminQuery.CreateTable <TestClass>();
tableName = ObjectName.Parse("APP.test_table");
var tableInfo = AdminQuery.Access().GetTableInfo(tableName);
Assert.IsNotNull(tableInfo);
Assert.AreEqual(3, tableInfo.ColumnCount);
var idColumn = tableInfo[0];
Assert.IsNotNull(idColumn);
Assert.AreEqual("Id", idColumn.ColumnName);
Assert.IsNotNull(idColumn.DefaultExpression);
var primaryKey = AdminQuery.Access().QueryTablePrimaryKey(tableName);
Assert.IsNotNull(primaryKey);
}
public void SimpleCallFunction()
{
var block = new PlSqlBlockStatement();
block.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.String()));
block.Statements.Add(new AssignVariableStatement(SqlExpression.VariableReference("a"), SqlExpression.FunctionCall("user")));
var procName = ObjectName.Parse("APP.proc1");
AdminQuery.CreateProcedure(procName, block);
var exists = AdminQuery.Access().RoutineExists(procName);
Assert.IsTrue(exists);
var procedure = AdminQuery.Access().GetObject(DbObjectType.Routine, procName);
Assert.IsNotNull(procedure);
Assert.IsInstanceOf <Procedure>(procedure);
}
private void CreateTestTable()
{
var tableName = ObjectName.Parse("APP.table1");
var tableInfo = new TableInfo(tableName);
tableInfo.AddColumn("a", PrimitiveTypes.Integer());
tableInfo.AddColumn("b", PrimitiveTypes.String());
AdminQuery.Access().CreateObject(tableInfo);
var table = AdminQuery.Access().GetMutableTable(tableName);
for (int i = 0; i < 50; i++)
{
var row = table.NewRow();
row["a"] = Field.Integer(i);
row["b"] = Field.String(String.Format("b_{0}", i));
table.AddRow(row);
}
}
public void SetDefaultOnDeleteConstraintCheck()
{
var query = CreateQuery(CreateAdminSession(Database));
var tableName = ObjectName.Parse("APP.test_table");
var expr = SqlExpression.Parse("first_name = 'Sebastiano' AND last_name = 'Provenzano'");
var count = query.Delete(tableName, expr);
query.Commit();
Assert.AreEqual(1, count);
query = CreateQuery(CreateAdminSession(Database));
var linkedTable = query.Access().GetTable(ObjectName.Parse("APP.test_table2"));
var rows = linkedTable.GetIndex(0).SelectEqual(Field.Integer(1));
var value = linkedTable.GetValue(rows.First(), 1);
Assert.AreEqual(1, ((SqlNumber)value.Value).ToInt32());
}
public void SetNullOnDeleteConstraintCheck()
{
var query = CreateQuery(CreateAdminSession(Database));
var tableName = ObjectName.Parse("APP.test_table");
var expr = SqlExpression.Parse("last_name = 'Provenzano'");
var count = query.Delete(tableName, expr);
query.Commit();
Assert.AreEqual(2, count);
query = CreateQuery(CreateAdminSession(Database));
var linkedTable = query.Access().GetTable(ObjectName.Parse("APP.test_table2"));
var rows = linkedTable.GetIndex(0).SelectEqual(Field.Integer(1));
var value = linkedTable.GetValue(rows.First(), 1);
Assert.IsTrue(Field.IsNullField(value));
}
public void BeforeInsert()
{
var tableName = ObjectName.Parse("APP.test_table");
var reset = new AutoResetEvent(false);
TriggerEvent firedEvent = null;
AdminQuery.Context.RouteImmediate <TriggerEvent>(e => {
firedEvent = e;
reset.Set();
}, e => e.TriggerType == TriggerType.Procedural &&
e.TriggerName.FullName.Equals("APP.trigger1"));
AdminQuery.Insert(tableName, new[] { "id", "name" },
new SqlExpression[] { SqlExpression.Constant(2), SqlExpression.Constant("The Name") });
reset.WaitOne(500);
Assert.IsNotNull(firedEvent);
}
private static void AddTestData(string testName, IQuery context)
{
var table = context.Access().GetMutableTable(ObjectName.Parse("APP.test_table"));
var row = table.NewRow();
row.SetValue("id", Field.Integer(0));
row.SetValue("first_name", Field.String("John"));
row.SetValue("last_name", Field.String("Doe"));
row.SetValue("birth_date", Field.Date(new SqlDateTime(1977, 01, 01)));
row.SetValue("active", Field.Boolean(false));
table.AddRow(row);
row = table.NewRow();
row.SetValue("id", Field.Integer(1));
row.SetValue("first_name", Field.String("Jane"));
row.SetValue("last_name", Field.String("Doe"));
row.SetValue("birth_date", Field.Date(new SqlDateTime(1978, 11, 01)));
row.SetValue("active", Field.Boolean(true));
table.AddRow(row);
row = table.NewRow();
row.SetValue("id", Field.Integer(2));
row.SetValue("first_name", Field.String("Roger"));
row.SetValue("last_name", Field.String("Rabbit"));
row.SetValue("birth_date", Field.Date(new SqlDateTime(1985, 05, 05)));
row.SetValue("active", Field.Boolean(true));
table.AddRow(row);
if (testName.EndsWith("Violation") ||
testName.EndsWith("ConstraintCheck"))
{
table = context.Access().GetMutableTable(ObjectName.Parse("APP.test_table2"));
row = table.NewRow();
row.SetValue("person_id", Field.Integer(1));
row.SetValue("dept_no", Field.Integer(45));
row.SetDefault(context);
table.AddRow(row);
}
}
private void InsertTestData(string testName, IQuery query)
{
var tableName = ObjectName.Parse("APP.test_table");
var table = query.Access().GetMutableTable(tableName);
var row = table.NewRow();
row.SetValue("first_name", Field.String("Antonello"));
row.SetValue("last_name", Field.String("Provenzano"));
row.SetValue("birth_date", Field.Date(new SqlDateTime(1980, 06, 04)));
row.SetValue("active", Field.BooleanTrue);
if (testName.EndsWith("WithLob"))
{
row.SetValue("bio", Field.Clob(CreateClobData(query)));
}
row.SetDefault(query);
table.AddRow(row);
row = table.NewRow();
row.SetValue("first_name", Field.String("Sebastiano"));
row.SetValue("last_name", Field.String("Provenzano"));
row.SetValue("birth_date", Field.Date(new SqlDateTime(1981, 08, 26)));
row.SetValue("active", Field.BooleanFalse);
row.SetDefault(query);
table.AddRow(row);
if (testName.EndsWith("Violation") ||
testName.EndsWith("ConstraintCheck"))
{
table = query.Access().GetMutableTable(ObjectName.Parse("APP.test_table2"));
row = table.NewRow();
row.SetValue("person_id", Field.Integer(2));
row.SetValue("dept_no", Field.Integer(45));
row.SetDefault(query);
table.AddRow(row);
}
}
protected override void OnAfterSetup(string testName)
{
var tableName = ObjectName.Parse("APP.persons");
var tableInfo = new TableInfo(tableName);
tableInfo.AddColumn("first_name", PrimitiveTypes.String());
tableInfo.AddColumn("last_name", PrimitiveTypes.String());
tableInfo.AddColumn("age", PrimitiveTypes.Integer());
AdminQuery.Access().CreateObject(tableInfo);
var table = AdminQuery.Access().GetMutableTable(tableName);
var row = table.NewRow();
row.SetValue(0, "Antonello");
row.SetValue(1, "Provenzano");
row.SetValue(2, 33);
table.AddRow(row);
row = table.NewRow();
row.SetValue(0, "Mark");
row.SetValue(1, "Smith");
row.SetValue(2, 42);
table.AddRow(row);
row = table.NewRow();
row.SetValue(0, "Maggie");
row.SetValue(1, "Smith");
row.SetValue(2, 65);
table.AddRow(row);
row = table.NewRow();
row.SetValue(0, "Antonello");
row.SetValue(1, "Capone");
row.SetValue(2, 33);
table.AddRow(row);
}
public void SetNullOnUpdateConstraintCheck()
{
var query = CreateQuery(CreateAdminSession(Database));
var tableName = ObjectName.Parse("APP.test_table");
var expr = SqlExpression.Parse("first_name = 'Jane' AND last_name = 'Doe'");
var count = query.Update(tableName, expr, new [] {
new SqlColumnAssignment("id", SqlExpression.Constant(10))
});
query.Commit();
Assert.AreEqual(1, count);
query = CreateQuery(CreateAdminSession(Database));
var linkedTable = query.Access().GetTable(ObjectName.Parse("APP.test_table2"));
var rows = linkedTable.GetIndex(0).SelectEqual(Field.Integer(1));
var value = linkedTable.GetValue(rows.First(), 1);
Assert.IsTrue(Field.IsNullField(value));
}
private static void CreateTestTable(IQuery query)
{
var tableName1 = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => table
.Named(tableName1)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName1.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
query.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
query.Access().CreateTable(table => table
.Named("APP.test_table2")
.WithColumn("person_id", PrimitiveTypes.Integer())
.WithColumn("value", PrimitiveTypes.Boolean()));
}
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);
}
protected override bool OnSetUp(string testName, IQuery query)
{
var tableName1 = ObjectName.Parse("APP.test_table");
query.Access().CreateTable(table => table
.Named(tableName1)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName1.FullName) })))
.WithColumn("first_name", PrimitiveTypes.String())
.WithColumn("last_name", PrimitiveTypes.String())
.WithColumn("birth_date", PrimitiveTypes.DateTime())
.WithColumn("active", PrimitiveTypes.Boolean()));
query.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
var tableName2 = ObjectName.Parse("APP.test_table2");
query.Access().CreateTable(table => table
.Named(tableName2)
.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.NotNull(testName.Equals("SetNullOnDeleteViolation")))
.WithColumn("value", PrimitiveTypes.Boolean()));
if (testName == "DropConstraint" ||
testName == "DropReferencedColumn")
{
query.Session.Access().AddForeignKey(tableName2, new string[] { "person_id" }, tableName1,
new[] { "id" }, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, "FK_1");
}
return(true);
}
public void SimpleReturn()
{
var body = new PlSqlBlockStatement();
body.Declarations.Add(new DeclareVariableStatement("a", PrimitiveTypes.Integer()));
body.Statements.Add(new ReturnStatement(SqlExpression.VariableReference("a")));
var funName = ObjectName.Parse("APP.fun1");
AdminQuery.CreateFunction(funName, PrimitiveTypes.Numeric(), body);
var exists = AdminQuery.Access().RoutineExists(funName);
Assert.IsTrue(exists);
var function = AdminQuery.Access().GetObject(DbObjectType.Routine, funName);
Assert.IsNotNull(function);
Assert.IsInstanceOf <PlSqlFunction>(function);
var userFunction = (PlSqlFunction)function;
Assert.IsNotNull(userFunction.Body);
}
private static void CreateTestTables(IQuery context)
{
var tn1 = ObjectName.Parse("APP.test_table1");
context.Access().CreateTable(table => table
.Named(tn1)
.WithColumn("id", PrimitiveTypes.Integer())
.WithColumn("name", PrimitiveTypes.String())
.WithColumn("date", PrimitiveTypes.DateTime()));
context.Session.Access().AddPrimaryKey(tn1, "id");
var tn2 = ObjectName.Parse("APP.test_table2");
context.Access().CreateTable(table => table
.Named(tn2)
.WithColumn("id", PrimitiveTypes.Integer())
.WithColumn("other_id", PrimitiveTypes.Integer())
.WithColumn("count", PrimitiveTypes.Integer()));
context.Session.Access().AddPrimaryKey(tn2, "id");
context.Session.Access().AddForeignKey(tn2, new[] { "other_id" }, tn1, new[] { "id" }, ForeignKeyAction.Cascade,
ForeignKeyAction.Cascade, null);
}
public void AddForeignKeyConstraint()
{
var tableName = ObjectName.Parse("APP.test_table2");
var constraint = new SqlTableConstraint("FK_1", ConstraintType.ForeignKey, new[] { "person_id" })
{
ReferenceTable = "APP.test_table",
ReferenceColumns = new[] { "id" }
};
AdminQuery.AddConstraint(tableName, constraint);
var fkeys = AdminQuery.Session.Access().QueryTableForeignKeys(tableName);
Assert.IsNotNull(fkeys);
Assert.IsNotEmpty(fkeys);
var fkey = fkeys.FirstOrDefault(x => x.ConstraintName == "FK_1");
Assert.IsNotNull(fkey);
Assert.IsNotNull(fkey.ForeignTable);
Assert.AreEqual("APP.test_table", fkey.ForeignTable.FullName);
Assert.IsNotNull(fkey.ForeignColumnNames);
Assert.IsNotEmpty(fkey.ForeignColumnNames);
}
public SqlType ResolveUserType(string typeName)
{
return(ResolveUserType(ObjectName.Parse(typeName)));
}
public void SetNullOnDeleteViolation()
{
var expected = Is.InstanceOf <ConstraintViolationException>()
.And.TypeOf <NotNullColumnViolationException>()
.And.Property("TableName").EqualTo(ObjectName.Parse("APP.test_table2"))
.And.Property("ColumnName").EqualTo("person_id");
Assert.Throws(expected, () => AdminQuery.AddForeignKey(ObjectName.Parse("test_table2"), new[] { "person_id" }, ObjectName.Parse("test_table"),
new[] { "id" }, ForeignKeyAction.SetNull, ForeignKeyAction.NoAction));
}
public void ParseEmptyName()
{
Assert.Throws <ArgumentNullException>(() => ObjectName.Parse(""));
}
public void ParseInvalidName(string s)
{
Assert.Throws <FormatException>(() => ObjectName.Parse(s));
}
protected override bool OnSetUp(string testName, IQuery query)
{
triggerName = ObjectName.Parse("APP.trigger1");
query.Access().CreateTrigger(new ProcedureTriggerInfo(triggerName, ObjectName.Parse("APP.table1"),
TriggerEventTime.After, TriggerEventType.Delete, ObjectName.Parse("APP.proc1")));
return(true);
}
public void Existing_UserDefined()
{
var seqName = ObjectName.Parse("APP.seq1");
Assert.Throws <StatementException>(() => AdminQuery.CreateSequence(seqName));
}
private void InsertTestData(IQuery query)
{
query.Insert(ObjectName.Parse("APP.test_table"), new[] { "name" }, new SqlExpression[] {
SqlExpression.Constant("Antonello")
});
}
protected override void OnAfterSetup(string testName)
{
triggerName = "trigger1";
AdminQuery.Access().CreateCallbackTrigger(triggerName, ObjectName.Parse("APP.table1"),
TriggerEventTime.After, TriggerEventType.Delete);
}
public void NotExisting_IfExistsClause()
{
var procName = ObjectName.Parse("APP.proc2");
Assert.DoesNotThrow(() => AdminQuery.DropProcedure(procName, true));
}
public void NotExisting()
{
var procName = ObjectName.Parse("APP.proc2");
Assert.Throws <ObjectNotFoundException>(() => AdminQuery.DropProcedure(procName));
}