public void SetUp()
{
dbFileName = Path.GetTempFileName() + ".db";
var connStr = String.Format("Data Source={0};FailIfMissing=false;Pooling=False;", dbFileName);
Dalc = new DbDalc(new SQLiteDalcFactory(), connStr);
var usersViewSql = @"
select @SqlFields from users u
left join roles r on (u.role=r.id)
@SqlWhere[where {0}]
@SqlOrderBy[order by {0};order by u.id desc]
" .Trim();
Dalc.CommandGenerator = new DbCommandGenerator(Dalc.DbFactory)
{
Views = new[] {
new DbDalcView("users_view", usersViewSql, "u.*,r.role as role_name", "count(u.id)")
{
FieldMapping = new Dictionary <string, string>()
{
{ "role_name", "r.role" }
}
},
new DbDalcView("users2_view", usersViewSql, "u.*,r.role as role_name@customParam[, {0}]", "count(u.id)")
{
FieldMapping = new Dictionary <string, string>()
{
{ "role_name", "r.role" }
}
}
}
};
// create tables if not exist
Dalc.ExecuteNonQuery(@"
CREATE TABLE [users] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[name] TEXT,
[role] INTEGER
)
" );
Dalc.ExecuteNonQuery(@"
CREATE TABLE [roles] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[role] TEXT
)
" );
Dalc.Insert("users", new Hashtable {
{ "name", "Mike" }, { "role", 1 }
});
Dalc.Insert("users", new Hashtable {
{ "name", "Joe" }, { "role", 1 }
});
Dalc.Insert("users", new Hashtable {
{ "name", "Stas" }, { "role", 2 }
});
Dalc.Insert("users", new Hashtable {
{ "name", "WUserToDelete" }, { "role", 3 }
});
Dalc.Insert("roles", new Hashtable {
{ "role", "admin" }
});
Dalc.Insert("roles", new Hashtable {
{ "role", "user" }
});
Dalc.Insert("roles", new Hashtable {
{ "role", "toDelete" }
});
}
void InitDbSchema()
{
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [metadata_classes] (
[id] TEXT PRIMARY KEY,
[name] TEXT,
[predicate] INTEGER,
[compact_id] INTEGER,
[object_location] TEXT
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [metadata_properties] (
[id] TEXT PRIMARY KEY,
[name] TEXT,
[datatype] TEXT,
[compact_id] INTEGER,
[primary_key] INTEGER
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [metadata_class_relationships] (
[subject_class_id] TEXT,
[predicate_class_id] TEXT,
[object_class_id] TEXT,
[subject_multiplicity] INTEGER,
[object_multiplicity] INTEGER,
PRIMARY KEY (subject_class_id,predicate_class_id,object_class_id)
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [metadata_property_to_class] (
[property_id] TEXT,
[class_id] TEXT,
[value_location] TEXT,
[column_name] TEXT,
[derive_type] TEXT,
[derived_from_property_id] TEXT,
PRIMARY KEY (property_id, class_id)
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [objects] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[compact_class_id] INTEGER
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [objects_log] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[compact_class_id] INTEGER,
[object_id] INTEGER,
[account_id] INTEGER,
[timestamp] TEXT,
[action] TEXT
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [object_relations] (
[subject_id] INTEGER,
[predicate_class_compact_id] INTEGER,
[object_id] INTEGER,
PRIMARY KEY (subject_id,predicate_class_compact_id,object_id)
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [object_relations_log] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[subject_id] INTEGER,
[predicate_class_compact_id] INTEGER,
[object_id] INTEGER,
[account_id] INTEGER,
[timestamp] TEXT,
[deleted] INTEGER
)" );
var valueTableCreateSqlTemplate = @"
CREATE TABLE [{0}] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[object_id] INTEGER,
[property_compact_id] INTEGER,
[value] {1}
)
" ;
var valueLogTableCreateSqlTemplate = @"
CREATE TABLE [{0}] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[object_id] INTEGER,
[property_compact_id] INTEGER,
[value] {1},
[account_id] INTEGER,
[timestamp] TEXT,
[deleted] INTEGER
)
" ;
InternalDalc.ExecuteNonQuery(String.Format(valueTableCreateSqlTemplate, "object_datetime_values", "TEXT"));
InternalDalc.ExecuteNonQuery(String.Format(valueTableCreateSqlTemplate, "object_decimal_values", "REAL"));
InternalDalc.ExecuteNonQuery(String.Format(valueTableCreateSqlTemplate, "object_integer_values", "INTEGER"));
InternalDalc.ExecuteNonQuery(String.Format(valueTableCreateSqlTemplate, "object_string_values", "TEXT"));
InternalDalc.ExecuteNonQuery(String.Format(valueLogTableCreateSqlTemplate, "object_datetime_values_log", "TEXT"));
InternalDalc.ExecuteNonQuery(String.Format(valueLogTableCreateSqlTemplate, "object_decimal_values_log", "REAL"));
InternalDalc.ExecuteNonQuery(String.Format(valueLogTableCreateSqlTemplate, "object_integer_values_log", "INTEGER"));
InternalDalc.ExecuteNonQuery(String.Format(valueLogTableCreateSqlTemplate, "object_string_values_log", "TEXT"));
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [users] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[name] TEXT,
[age] INTEGER,
[group_id] INTEGER
)" );
InternalDalc.ExecuteNonQuery(@"
CREATE TABLE [user_groups] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[caption] TEXT
)" );
}