public static IFunctionConfiguration WithBooleanParameter(this IFunctionConfiguration configuration, string name)
{
return(configuration.WithParameter(name, PrimitiveTypes.Boolean()));
}
public static IFunctionConfiguration ReturnsBoolean(this IFunctionConfiguration configuration)
{
return(configuration.ReturnsType(PrimitiveTypes.Boolean()));
}
static TransactionTableContainer()
{
// SYSTEM.TABLE_INFO
TableInfoTableInfo = new TableInfo(SystemSchema.TableInfoTableName);
TableInfoTableInfo.AddColumn("catalog", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("schema", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("name", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("type", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("other", PrimitiveTypes.String());
TableInfoTableInfo = TableInfoTableInfo.AsReadOnly();
// SYSTEM.TABLE_COLUMNS
TableColumnsTableInfo = new TableInfo(SystemSchema.TableColumnsTableName);
TableColumnsTableInfo.AddColumn("schema", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("table", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("column", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("sql_type", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("type_desc", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("size", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("scale", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("not_null", PrimitiveTypes.Boolean());
TableColumnsTableInfo.AddColumn("default", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("index_str", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("seq_no", PrimitiveTypes.Numeric());
TableColumnsTableInfo = TableColumnsTableInfo.AsReadOnly();
// SYSTEM.VARIABLES
VariablesTableInfo = new TableInfo(SystemSchema.VariablesTableName);
VariablesTableInfo.AddColumn("var", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("type", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("value", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("constant", PrimitiveTypes.Boolean());
VariablesTableInfo.AddColumn("not_null", PrimitiveTypes.Boolean());
VariablesTableInfo.AddColumn("is_set", PrimitiveTypes.Boolean());
VariablesTableInfo = VariablesTableInfo.AsReadOnly();
// SYSTEM.PRODUCT_INFO
ProductInfoTableInfo = new TableInfo(SystemSchema.ProductInfoTableName);
ProductInfoTableInfo.AddColumn("var", PrimitiveTypes.String());
ProductInfoTableInfo.AddColumn("value", PrimitiveTypes.String());
ProductInfoTableInfo = ProductInfoTableInfo.AsReadOnly();
// SYSTEM.SQL_TYPES
SqlTypesTableInfo = new TableInfo(SystemSchema.SqlTypesTableName);
SqlTypesTableInfo.AddColumn("TYPE_NAME", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("DATA_TYPE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("PRECISION", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("LITERAL_PREFIX", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("LITERAL_SUFFIX", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("CREATE_PARAMS", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("NULLABLE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("CASE_SENSITIVE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("SEARCHABLE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("UNSIGNED_ATTRIBUTE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("FIXED_PREC_SCALE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("AUTO_INCREMENT", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("LOCAL_TYPE_NAME", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("MINIMUM_SCALE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("MAXIMUM_SCALE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("SQL_DATA_TYPE", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("SQL_DATETIME_SUB", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("NUM_PREC_RADIX", PrimitiveTypes.Numeric());
SqlTypesTableInfo = SqlTypesTableInfo.AsReadOnly();
// SYSTEM.PRIVS
PrivilegesTableInfo = new TableInfo(SystemSchema.PrivilegesTableName);
PrivilegesTableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric());
PrivilegesTableInfo.AddColumn("description", PrimitiveTypes.String());
PrivilegesTableInfo = PrivilegesTableInfo.AsReadOnly();
IntTableInfo = new TableInfo[6];
IntTableInfo[0] = TableInfoTableInfo;
IntTableInfo[1] = TableColumnsTableInfo;
IntTableInfo[2] = ProductInfoTableInfo;
IntTableInfo[3] = VariablesTableInfo;
IntTableInfo[4] = SqlTypesTableInfo;
IntTableInfo[5] = PrivilegesTableInfo;
}
public void CastStringToBooleanFalse()
{
var exp = SqlExpression.Cast(SqlExpression.Constant(Field.String("false")), PrimitiveTypes.Boolean());
SqlExpression casted = null;
Assert.DoesNotThrow(() => casted = exp.Evaluate());
Assert.IsNotNull(casted);
Assert.IsInstanceOf <SqlConstantExpression>(casted);
var value = ((SqlConstantExpression)casted).Value;
Assert.IsNotNull(value.Value);
Assert.IsInstanceOf <BooleanType>(value.Type);
Assert.AreEqual(SqlTypeCode.Boolean, value.Type.TypeCode);
Assert.AreEqual(SqlBoolean.False, value.Value);
}
public override SqlType GetSqlType(IContext context)
{
return(PrimitiveTypes.Boolean());
}
public static DataObject Boolean(bool value)
{
return(new DataObject(PrimitiveTypes.Boolean(), value));
}
private static void CreateTestTable(string testName, IQuery context)
{
var tableName1 = ObjectName.Parse("APP.test_table");
context.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()));
context.Session.Access().AddPrimaryKey(tableName1, "id", "PK_TEST_TABLE");
if (testName.EndsWith("ConstraintCheck") ||
testName.EndsWith("Violation"))
{
var tableName2 = ObjectName.Parse("APP.test_table2");
context.Access().CreateTable(table => {
table
.Named(tableName2)
.WithColumn(column => column
.Named("id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.FunctionCall("UNIQUEKEY",
new SqlExpression[] { SqlExpression.Constant(tableName2.FullName) })));
if (testName.StartsWith("SetDefault"))
{
table.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.WithDefault(SqlExpression.Constant(0)));
}
else
{
table.WithColumn(column => column
.Named("person_id")
.HavingType(PrimitiveTypes.Integer())
.NotNull(testName.EndsWith("Violation")));
}
table.WithColumn("dept_no", PrimitiveTypes.Integer());
});
context.Access().AddPrimaryKey(tableName2, "id", "PK_TEST_TABLE2");
ForeignKeyAction?onUpdate = null;
if (testName.StartsWith("SetNull"))
{
onUpdate = ForeignKeyAction.SetNull;
}
else if (testName.StartsWith("SetDefault"))
{
onUpdate = ForeignKeyAction.SetDefault;
}
else if (testName.StartsWith("Cascade"))
{
onUpdate = ForeignKeyAction.Cascade;
}
if (onUpdate != null)
{
context.Access()
.AddForeignKey(tableName2, new[] { "person_id" }, tableName1, new[] { "id" }, ForeignKeyAction.NoAction,
onUpdate.Value, "FKEY_TEST_TABLE2");
}
}
}
static SystemSchema()
{
// SYSTEM.TABLE_INFO
TableInfoTableInfo = new TableInfo(TableInfoTableName);
TableInfoTableInfo.AddColumn("catalog", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("schema", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("name", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("type", PrimitiveTypes.String());
TableInfoTableInfo.AddColumn("other", PrimitiveTypes.String());
TableInfoTableInfo = TableInfoTableInfo.AsReadOnly();
// SYSTEM.TABLE_COLUMNS
TableColumnsTableInfo = new TableInfo(TableColumnsTableName);
TableColumnsTableInfo.AddColumn("schema", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("table", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("column", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("sql_type", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("type_desc", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("size", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("scale", PrimitiveTypes.Numeric());
TableColumnsTableInfo.AddColumn("not_null", PrimitiveTypes.Boolean());
TableColumnsTableInfo.AddColumn("default", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("index_str", PrimitiveTypes.String());
TableColumnsTableInfo.AddColumn("seq_no", PrimitiveTypes.Numeric());
TableColumnsTableInfo = TableColumnsTableInfo.AsReadOnly();
// SYSTEM.VARIABLES
VariablesTableInfo = new TableInfo(VariablesTableName);
VariablesTableInfo.AddColumn("var", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("type", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("value", PrimitiveTypes.String());
VariablesTableInfo.AddColumn("constant", PrimitiveTypes.Boolean());
VariablesTableInfo.AddColumn("not_null", PrimitiveTypes.Boolean());
VariablesTableInfo.AddColumn("is_set", PrimitiveTypes.Boolean());
VariablesTableInfo = VariablesTableInfo.AsReadOnly();
// SYSTEM.PRODUCT_INFO
ProductInfoTableInfo = new TableInfo(ProductInfoTableName);
ProductInfoTableInfo.AddColumn("var", PrimitiveTypes.String());
ProductInfoTableInfo.AddColumn("value", PrimitiveTypes.String());
ProductInfoTableInfo = ProductInfoTableInfo.AsReadOnly();
// SYSTEM.STATS
StatisticsTableInfo = new TableInfo(StatisticsTableName);
StatisticsTableInfo.AddColumn("stat_name", PrimitiveTypes.String());
StatisticsTableInfo.AddColumn("value", PrimitiveTypes.String());
StatisticsTableInfo = StatisticsTableInfo.AsReadOnly();
// SYSTEM.SQL_TYPES
SqlTypesTableInfo = new TableInfo(SqlTypesTableName);
SqlTypesTableInfo.AddColumn("TYPE_NAME", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("DATA_TYPE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("PRECISION", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("LITERAL_PREFIX", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("LITERAL_SUFFIX", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("CREATE_PARAMS", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("NULLABLE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("CASE_SENSITIVE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("SEARCHABLE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("UNSIGNED_ATTRIBUTE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("FIXED_PREC_SCALE", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("AUTO_INCREMENT", PrimitiveTypes.Boolean());
SqlTypesTableInfo.AddColumn("LOCAL_TYPE_NAME", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("MINIMUM_SCALE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("MAXIMUM_SCALE", PrimitiveTypes.Numeric());
SqlTypesTableInfo.AddColumn("SQL_DATA_TYPE", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("SQL_DATETIME_SUB", PrimitiveTypes.String());
SqlTypesTableInfo.AddColumn("NUM_PREC_RADIX", PrimitiveTypes.Numeric());
SqlTypesTableInfo = SqlTypesTableInfo.AsReadOnly();
// SYSTEM.OPEN_SESSIONS
OpenSessionsTableInfo = new TableInfo(OpenSessionsTableName);
OpenSessionsTableInfo.AddColumn("username", PrimitiveTypes.String());
OpenSessionsTableInfo.AddColumn("host_string", PrimitiveTypes.String());
OpenSessionsTableInfo.AddColumn("last_command", PrimitiveTypes.DateTime());
OpenSessionsTableInfo.AddColumn("time_connected", PrimitiveTypes.DateTime());
OpenSessionsTableInfo = OpenSessionsTableInfo.AsReadOnly();
// SYSTEM.SESSION_INFO
SessionInfoTableInfo = new TableInfo(SessionInfoTableName);
SessionInfoTableInfo.AddColumn("var", PrimitiveTypes.String());
SessionInfoTableInfo.AddColumn("value", PrimitiveTypes.String());
SessionInfoTableInfo = SessionInfoTableInfo.AsReadOnly();
// SYSTEM.PRIVS
PrivilegesTableInfo = new TableInfo(PrivilegesTableName);
PrivilegesTableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric());
PrivilegesTableInfo.AddColumn("description", PrimitiveTypes.String());
PrivilegesTableInfo = PrivilegesTableInfo.AsReadOnly();
}
private static void CreateSecurityTables(IQuery context)
{
var tableInfo = new TableInfo(UserTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String());
// TODO: User table must be completed ...
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
context.AddPrimaryKey(UserTableName, new [] { "name" }, "SYSTEM_USER_PK");
tableInfo = new TableInfo(PasswordTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("method", PrimitiveTypes.String());
tableInfo.AddColumn("method_args", PrimitiveTypes.Binary());
tableInfo.AddColumn("identifier", PrimitiveTypes.String());
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(UserGroupTableName);
tableInfo.AddColumn("user", PrimitiveTypes.String());
tableInfo.AddColumn("group", PrimitiveTypes.String());
tableInfo.AddColumn("admin", PrimitiveTypes.Boolean());
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(GroupsTableName);
tableInfo.AddColumn("name", PrimitiveTypes.String(), true);
tableInfo = tableInfo.AsReadOnly();
context.CreateSystemTable(tableInfo);
context.AddPrimaryKey(GroupsTableName, new[] { "name" }, "SYSTEM_GROUP_PK");
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.CreateSystemTable(tableInfo);
tableInfo = new TableInfo(GroupGrantsTable);
tableInfo.AddColumn("priv_bit", PrimitiveTypes.Numeric());
tableInfo.AddColumn("object", PrimitiveTypes.Numeric());
tableInfo.AddColumn("name", PrimitiveTypes.String());
tableInfo.AddColumn("group", PrimitiveTypes.String());
tableInfo.AddColumn("grant_option", PrimitiveTypes.Boolean());
tableInfo.AddColumn("granter", PrimitiveTypes.String());
var fkCol = new[] { "user" };
var gfkCol = new[] { "group" };
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(UserGroupTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_PRIV_FK");
context.AddForeignKey(UserGroupTableName, gfkCol, GroupsTableName, refCol, onDelete, onUpdate, "USER_GROUP_FK");
context.AddForeignKey(UserGrantsTableName, fkCol, UserTableName, refCol, onDelete, onUpdate, "USER_GRANTS_FK");
context.AddForeignKey(GroupGrantsTable, gfkCol, GroupsTableName, refCol, onDelete, onUpdate, "GROUP_GRANTS_FK");
}