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)); }