示例#1
0
        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" }
            });
        }
示例#2
0
        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
				)"                );
        }