private void CreateTestTables(IQueryContext context) { var tn1 = ObjectName.Parse("APP.test_table1"); var tableInfo1 = new TableInfo(tn1); tableInfo1.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer())); tableInfo1.AddColumn(new ColumnInfo("name", PrimitiveTypes.String())); tableInfo1.AddColumn(new ColumnInfo("date", PrimitiveTypes.DateTime())); context.CreateTable(tableInfo1); context.AddPrimaryKey(tn1, "id"); var tn2 = ObjectName.Parse("APP.test_table2"); var tableInfo2 = new TableInfo(tn2); tableInfo2.AddColumn(new ColumnInfo("id", PrimitiveTypes.Integer())); tableInfo2.AddColumn(new ColumnInfo("other_id", PrimitiveTypes.Integer())); tableInfo2.AddColumn(new ColumnInfo("count", PrimitiveTypes.Integer())); context.CreateTable(tableInfo2); context.AddPrimaryKey(tn2, "id"); context.AddForeignKey(tn2, new []{"other_id"}, tn1, new []{"id"}, ForeignKeyAction.Cascade, ForeignKeyAction.Cascade, null); }
private void CreateTestTable(IQueryContext context) { var tableInfo = new TableInfo(ObjectName.Parse("APP.test_table")); var idColumn = tableInfo.AddColumn("id", PrimitiveTypes.Integer()); idColumn.DefaultExpression = SqlExpression.FunctionCall("UNIQUE_KEY", new SqlExpression[] { SqlExpression.Reference(tableInfo.TableName) }); tableInfo.AddColumn("first_name", PrimitiveTypes.String()); tableInfo.AddColumn("last_name", PrimitiveTypes.String()); tableInfo.AddColumn("birth_date", PrimitiveTypes.DateTime()); tableInfo.AddColumn("active", PrimitiveTypes.Boolean()); context.CreateTable(tableInfo); context.AddPrimaryKey(tableInfo.TableName, "id", "PK_TEST_TABLE"); }
private static void CreateSecurityTables(IQueryContext context) { var tableInfo = new TableInfo(UserTableName); tableInfo.AddColumn("name", PrimitiveTypes.String()); // TODO: User table must be completed ... tableInfo = tableInfo.AsReadOnly(); context.CreateTable(tableInfo); context.AddPrimaryKey(UserTableName, new []{"name"}, "SYSTEM_USER_PK"); tableInfo = new TableInfo(PasswordTableName); tableInfo.AddColumn("user", PrimitiveTypes.String()); tableInfo.AddColumn("type", PrimitiveTypes.Numeric()); tableInfo.AddColumn("hash", PrimitiveTypes.String()); tableInfo.AddColumn("salt", PrimitiveTypes.String()); tableInfo.AddColumn("hash_algorithm", PrimitiveTypes.String()); tableInfo = tableInfo.AsReadOnly(); context.CreateTable(tableInfo); tableInfo = new TableInfo(UserPrivilegesTableName); tableInfo.AddColumn("user", PrimitiveTypes.String()); tableInfo.AddColumn("group", PrimitiveTypes.String()); tableInfo = tableInfo.AsReadOnly(); context.CreateTable(tableInfo); tableInfo = new TableInfo(UserConnectPrivilegesTableName); tableInfo.AddColumn("user", PrimitiveTypes.String()); tableInfo.AddColumn("protocol", PrimitiveTypes.String()); tableInfo.AddColumn("host", PrimitiveTypes.String()); tableInfo.AddColumn("access", PrimitiveTypes.Boolean()); tableInfo = tableInfo.AsReadOnly(); context.CreateTable(tableInfo); tableInfo = new TableInfo(UserGrantsTableName); tableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric()); tableInfo.AddColumn("object", PrimitiveTypes.Numeric()); tableInfo.AddColumn("name", PrimitiveTypes.String()); tableInfo.AddColumn("user", PrimitiveTypes.String()); tableInfo.AddColumn("grant_option", PrimitiveTypes.Boolean()); tableInfo.AddColumn("granter", PrimitiveTypes.String()); tableInfo = tableInfo.AsReadOnly(); context.CreateTable(tableInfo); var fkCol = new[] {"user"}; var refCol = new[] {"name"}; const ForeignKeyAction onUpdate = ForeignKeyAction.NoAction; const ForeignKeyAction onDelete = ForeignKeyAction.Cascade; context.AddForeignKey(PasswordTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_PASSWORD_FK"); context.AddForeignKey(UserPrivilegesTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_PRIV_FK"); context.AddForeignKey(UserConnectPrivilegesTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_CONNPRIV_FK"); context.AddForeignKey(UserGrantsTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_GRANTS_FK"); }