コード例 #1
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
        public async Task When_searching_for_all_records_in_a_table_not_matching_any_of_the_given_keywords()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await Given_a_logTtable_and_an_ftsTable(conn);

                var logs = new[]
                {
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "There is a Cat"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Info, Message = "There is a Dog"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "There is a Cat and a Dog"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "There is a Parrot"
                    }
                };

                var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
                await repo.Insert(logs);

                var term = Term <FTSContext.Log> .All
                           .AndNot(Match.Any, l => l.Message, "Cat", "Dog");

                var result = (await conn.Search(term)).ToArray();
                result.Length.ShouldBe(1);

                result[0].Level.ShouldBe(logs[3].Level);
                result[0].Message.ShouldBe(logs[3].Message);
            }
        }
コード例 #2
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
 public void When_searching_for_non_existing_table()
 {
     using (var conn = new SQLiteInMemoryConnection())
     {
         var selectAllTerm = Term <FTSContext.Log> .All;
         Should.Throw <SQLiteException>(async() => await conn.Search(selectAllTerm))
         .Message.ShouldBe("SQL logic error\r\nno such table: Log");
     }
 }
コード例 #3
0
 public void When_binding_function_to_a_closed_connection()
 {
     using (var conn = new SQLiteInMemoryConnection())
     {
         var func = new SQLiteScalarFunction("Foo", 1, args => null);
         Should.Throw <InvalidOperationException>(() => conn.BindFunction(func))
         .Message.ShouldBe("Cannot bind a function to a closed.");
     }
 }
コード例 #4
0
        public async Task When_checking_if_table_exists_overriding_model_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                (await conn.Exists("Person")).ShouldBeFalse();
                await conn.ExecuteAsync(TableQuery);

                (await conn.Exists("Person")).ShouldBeTrue();
            }
        }
コード例 #5
0
        public async Task When_checking_if_table_exists_aliased_models()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                (await conn.Exists <MyPerson>()).ShouldBeFalse();
                await conn.ExecuteAsync(TableQuery);

                (await conn.Exists <MyPerson>()).ShouldBeTrue();
            }
        }
コード例 #6
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
        public async Task When_searching_for_records_in_a_table_for_all_of_the_supplied_keywords()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await Given_a_logTtable_and_an_ftsTable(conn);

                var logs = new[]
                {
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "John is playing football"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Info, Message = "Mary is playing football"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "Someone is playing football"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "John with Mary are playing football"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "football is awesome"
                    }
                };

                var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
                await repo.Insert(logs);

                var term = Term <FTSContext.Log> .All.And(Match.All, l => l.Message, "John", "Mary", "football");

                var result1 = (await conn.Search(term)).ToArray();
                result1.Length.ShouldBe(1);

                result1[0].Level.ShouldBe(logs[3].Level);
                result1[0].Message.ShouldBe(logs[3].Message);

                term.Clear();
                term.And(Match.All, l => l.Message, "play*", "football");

                var result2 = (await conn.Search(term)).ToArray();
                result2.Length.ShouldBe(4);

                result2[0].Level.ShouldBe(logs[0].Level);
                result2[0].Message.ShouldBe(logs[0].Message);

                result2[1].Level.ShouldBe(logs[1].Level);
                result2[1].Message.ShouldBe(logs[1].Message);

                result2[2].Level.ShouldBe(logs[2].Level);
                result2[2].Message.ShouldBe(logs[2].Message);

                result2[3].Level.ShouldBe(logs[3].Level);
                result2[3].Message.ShouldBe(logs[3].Message);
            }
        }
コード例 #7
0
        public async Task When_checking_table_exists()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                (await conn.Exists <Person>()).ShouldBeFalse();
                conn.State.ShouldBe(ConnectionState.Open);
                await conn.ExecuteAsync(TableQuery);

                (await conn.Exists <Person>()).ShouldBeTrue();
            }
        }
コード例 #8
0
        public void When_getting_table_info_of_a_non_existing_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                var ex1 = Should.Throw <InvalidOperationException>(() => conn.GetTableInfo <Person>());
                ex1.Message.ShouldBe("Table: [Person] does not exist.");

                var ex2 = Should.Throw <InvalidOperationException>(() => conn.GetTableInfo("Bambolini"));
                ex2.Message.ShouldBe("Table: Bambolini does not exist.");
            }
        }
コード例 #9
0
 public void When_creating_connection_with_valid_connection_string()
 {
     using (var conn = new SQLiteInMemoryConnection(SQLiteConnectionStringProvider.GetInMemoryConnectionString()))
     {
         conn.ConnectionString.ShouldBe("Data Source=:memory:");
         conn.ConnectionTimeout.ShouldBe(15);
         conn.DataSource.ShouldBeNull();
         conn.Database.ShouldBe("main");
         conn.ServerVersion.ShouldBe("3.21.0");
         conn.State.ShouldBe(ConnectionState.Closed);
     }
 }
コード例 #10
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
        public async Task When_searching_for_all_records_in_empty_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await Given_a_logTtable_and_an_ftsTable(conn);

                var selectAllTerm = Term <FTSContext.Log> .All;
                var result        = await conn.Search(selectAllTerm);

                result.ShouldBeEmpty();
            }
        }
コード例 #11
0
        public async Task When_executing_some_sql()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await conn.ExecuteAsync(TableQuery);

                (await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM Person")).ShouldBe(0);

                var allRows = await conn.QueryAsync <dynamic>("SELECT * FROM Person");

                allRows.ShouldBeEmpty();

                var ex = Should.Throw <SQLiteException>(async() => await conn.ExecuteAsync("SELECT * FROM SomeTable;"));
                ex.Message.ShouldBe("SQL logic error\r\nno such table: SomeTable");
                ex.InnerException.ShouldBeNull();
            }
        }
コード例 #12
0
        public void When_changing_connection_state()
        {
            var conn = new SQLiteInMemoryConnection();

            conn.State.ShouldBe(ConnectionState.Closed);

            conn.Open();
            conn.State.ShouldBe(ConnectionState.Open);

            conn.Close();
            conn.State.ShouldBe(ConnectionState.Open);

            conn.Dispose();

            Should.Throw <ObjectDisposedException>(() => conn.State.ShouldBe(ConnectionState.Closed))
            .Message.ShouldBe("Cannot access a disposed object.\r\nObject name: 'SQLiteConnection'.");
        }
コード例 #13
0
        public async Task When_binding_a_collation_function_to_an_opened_connection_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                conn.Open();
                Func <string, string, int> compare = (one, two) => 1;
                conn.BindFunction(new SQLiteCollationFunction("FunkyCompare", compare));

                await conn.ExecuteAsync("CREATE TABLE Numbers(No TEXT NOT NULL COLLATE FunkyCompare);");

                await conn.ExecuteAsync("INSERT INTO Numbers VALUES (1), (2), (3), (4);");

                var defaultCollation = await conn.QueryAsync <string>("SELECT No FROM Numbers ORDER BY No ASC;");

                defaultCollation.ShouldBe(new[] { "4", "3", "2", "1" });
            }
        }
コード例 #14
0
        public async Task When_binding_a_scalar_function_to_an_opened_connection()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                string receivedArg = null;
                var    func        = new SQLiteScalarFunction("Funky", 1, args =>
                {
                    receivedArg = args[0].ToString();
                    return("bar");
                });

                conn.Open();
                conn.BindFunction(func);

                var foo = (await conn.QueryAsync <string>("SELECT \"foo\"")).First();
                foo.ShouldBe("foo");

                var bar = (await conn.QueryAsync <string>("SELECT Funky(\"foo\")")).First();
                bar.ShouldBe("bar");
                receivedArg.ShouldBe("foo");
            }
        }
コード例 #15
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
        public async Task When_searching_for_all_records_in_a_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await Given_a_logTtable_and_an_ftsTable(conn);

                var logs = new[]
                {
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "There is a Cat"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "There is a Dog"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "There is a Cat and a Dog"
                    }
                };

                var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
                await repo.Insert(logs);

                var selectAllTerm = Term <FTSContext.Log> .All;
                var result        = (await conn.Search(selectAllTerm)).ToArray();
                result.Length.ShouldBe(3);
                result[0].Level.ShouldBe(logs[0].Level);
                result[0].Message.ShouldBe(logs[0].Message);

                result[1].Level.ShouldBe(logs[1].Level);
                result[1].Message.ShouldBe(logs[1].Message);

                result[1].Level.ShouldBe(logs[1].Level);
                result[1].Message.ShouldBe(logs[1].Message);

                result[2].Level.ShouldBe(logs[2].Level);
                result[2].Message.ShouldBe(logs[2].Message);
            }
        }
コード例 #16
0
        public async Task When_querying_multiple_single_rows()
        {
            using (var conn = new SQLiteInMemoryConnection())
                using (var reader = await conn.QueryMultipleAsync("SELECT 'Foo'; SELECT 666; SELECT 3.43;"))
                {
                    reader.IsConsumed.ShouldBeFalse();

                    var first = await reader.ReadFirstOrDefaultAsync <string>();

                    reader.IsConsumed.ShouldBeFalse();
                    first.ShouldBe("Foo");

                    var second = await reader.ReadFirstOrDefaultAsync <dynamic>();

                    reader.IsConsumed.ShouldBeFalse();
                    second.ShouldBe(100);

                    var three = await reader.ReadFirstOrDefaultAsync <double>();

                    reader.IsConsumed.ShouldBeTrue();
                    three.ShouldBe(3.43);
                }
        }
コード例 #17
0
        public async Task When_getting_table_info_of_aliased_model()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await conn.ExecuteAsync(TableQuery);

                var tableInfo = await conn.GetTableInfo <MyPerson>();

                tableInfo.ShouldNotBeNull();
                tableInfo.TableName.ShouldBe("Person");
                tableInfo.SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
                tableInfo.Columns.Length.ShouldBe(3);

                Array.TrueForAll(tableInfo.Columns, i => i.TableName == "Person").ShouldBeTrue();

                tableInfo.Columns[0].Id.ShouldBe(0);
                tableInfo.Columns[0].Name.ShouldBe("Id");
                tableInfo.Columns[0].Type.ShouldBe(SQLiteDataType.INTEGER);
                tableInfo.Columns[0].DefaultValue.ShouldBeNull();
                tableInfo.Columns[0].IsPrimaryKey.ShouldBeTrue();
                tableInfo.Columns[0].NotNull.ShouldBeTrue();

                tableInfo.Columns[1].Id.ShouldBe(1);
                tableInfo.Columns[1].Name.ShouldBe("Name");
                tableInfo.Columns[1].Type.ShouldBe(SQLiteDataType.TEXT);
                tableInfo.Columns[1].DefaultValue.ShouldBeNull();
                tableInfo.Columns[1].IsPrimaryKey.ShouldBeFalse();
                tableInfo.Columns[1].NotNull.ShouldBeTrue();

                tableInfo.Columns[2].Id.ShouldBe(2);
                tableInfo.Columns[2].Name.ShouldBe("Age");
                tableInfo.Columns[2].Type.ShouldBe(SQLiteDataType.INTEGER);
                tableInfo.Columns[2].DefaultValue.ShouldBeNull();
                tableInfo.Columns[2].IsPrimaryKey.ShouldBeFalse();
                tableInfo.Columns[2].NotNull.ShouldBeTrue();
            }
        }
コード例 #18
0
        public async Task When_binding_an_aggregate_function_to_an_opened_connection()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                conn.Open();
                await conn.ExecuteAsync("CREATE TABLE Numbers(No INTEGER NOT NULL);");

                await conn.ExecuteAsync("INSERT INTO Numbers VALUES (1), (2), (3), (4);");

                const int InitState = 0;
                Func <object[], int, object, object> step = (objects, i, state) =>
                {
                    var newNo = Convert.ToInt32(objects[0]);
                    return((int)state + newNo);
                };
                Func <object, object> final = finalState => finalState;

                conn.BindFunction(new SQLiteAggregateFunction("FunkySum", 1, InitState, step, final));

                var funkySum = await conn.ExecuteScalarAsync <long>("SELECT FunkySum(No) FROM Numbers;");

                funkySum.ShouldBe(10);
            }
        }
コード例 #19
0
        public async Task When_querying_multiple_multiple_rows()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelOne>());

                await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelTwo>());

                var repoOne = conn.GetDBContext <ModelOne>(SQLiteDialect.Instance);
                var repoTwo = conn.GetDBContext <ModelTwo>(SQLiteDialect.Instance, "ModelTwo");

                await repoOne.Insert(new[]
                {
                    new ModelOne {
                        Name = "M1-A"
                    },
                    new ModelOne {
                        Name = "M1-B"
                    },
                    new ModelOne {
                        Name = "M1-C"
                    }
                });

                await repoTwo.Insert(new[]
                {
                    new ModelTwo {
                        Category = "M2-C-A", Number = 1
                    },
                    new ModelTwo {
                        Category = "M2-C-B", Number = 2
                    },
                    new ModelTwo {
                        Category = "M2-C-C", Number = 3
                    },
                    new ModelTwo {
                        Category = "M2-C-D", Number = 4
                    }
                });

                using (var reader = await conn.QueryMultipleAsync("SELECT Id, Name FROM ModelOne; SELECT Id, Number, Category FROM ModelTwo;"))
                {
                    reader.IsConsumed.ShouldBeFalse();

                    var modelOnes = (await reader.ReadAsync <ModelOne>(false)).ToArray();
                    reader.IsConsumed.ShouldBeFalse();

                    modelOnes.Length.ShouldBe(3);
                    modelOnes[0].Name.ShouldBe("M1-A");
                    modelOnes[1].Name.ShouldBe("M1-B");
                    modelOnes[2].Name.ShouldBe("M1-C");

                    var modelTwos = (await reader.ReadAsync <ModelTwo>()).ToArray();
                    reader.IsConsumed.ShouldBeTrue();

                    modelTwos.Length.ShouldBe(4);
                    modelTwos[0].Number.ShouldBe(1);
                    modelTwos[0].Category.ShouldBe("M2-C-A");

                    modelTwos[1].Number.ShouldBe(2);
                    modelTwos[1].Category.ShouldBe("M2-C-B");

                    modelTwos[2].Number.ShouldBe(3);
                    modelTwos[2].Category.ShouldBe("M2-C-C");

                    modelTwos[3].Number.ShouldBe(4);
                    modelTwos[3].Category.ShouldBe("M2-C-D");
                }
            }
        }
コード例 #20
0
        public async Task When_getting_objects_of_a_table()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                var snapshot1 = (await conn.GetDatabaseObjects()).ToArray();
                snapshot1.ShouldNotBeNull();
                snapshot1.ShouldBeEmpty();

                await conn.ExecuteAsync(TableQuery);

                var snapshot2 = (await conn.GetDatabaseObjects()).ToArray();
                snapshot2.ShouldNotBeNull();
                snapshot2.Length.ShouldBe(1);

                snapshot2[0].Type.ShouldBe(SQLiteObjectType.Table);
                snapshot2[0].Name.ShouldBe("Person");
                snapshot2[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                await conn.ExecuteAsync(ViewQuery);

                var snapshot3 = (await conn.GetDatabaseObjects()).ToArray();
                snapshot3.ShouldNotBeNull();
                snapshot3.Length.ShouldBe(2);

                snapshot3[0].Type.ShouldBe(SQLiteObjectType.Table);
                snapshot3[0].Name.ShouldBe("Person");
                snapshot3[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                snapshot3[1].Type.ShouldBe(SQLiteObjectType.View);
                snapshot3[1].Name.ShouldBe("Person_view");
                snapshot3[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                await conn.ExecuteAsync(TriggerQuery);

                var snapshot4 = (await conn.GetDatabaseObjects()).ToArray();
                snapshot4.ShouldNotBeNull();
                snapshot4.Length.ShouldBe(3);

                snapshot4[0].Type.ShouldBe(SQLiteObjectType.Table);
                snapshot4[0].Name.ShouldBe("Person");
                snapshot4[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                snapshot4[1].Type.ShouldBe(SQLiteObjectType.View);
                snapshot4[1].Name.ShouldBe("Person_view");
                snapshot4[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                snapshot4[2].Type.ShouldBe(SQLiteObjectType.Trigger);
                snapshot4[2].Name.ShouldBe("Person_bu");
                snapshot4[2].SQL.ShouldBe(TriggerQuery.Replace("IF NOT EXISTS ", string.Empty));

                await conn.ExecuteAsync(IndexQuery);

                var snapshot5 = (await conn.GetDatabaseObjects()).ToArray();
                snapshot5.ShouldNotBeNull();
                snapshot5.Length.ShouldBe(4);

                snapshot5[0].Type.ShouldBe(SQLiteObjectType.Table);
                snapshot5[0].Name.ShouldBe("Person");
                snapshot5[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                snapshot5[1].Type.ShouldBe(SQLiteObjectType.View);
                snapshot5[1].Name.ShouldBe("Person_view");
                snapshot5[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));

                snapshot5[2].Type.ShouldBe(SQLiteObjectType.Trigger);
                snapshot5[2].Name.ShouldBe("Person_bu");
                snapshot5[2].SQL.ShouldBe(TriggerQuery.Replace("IF NOT EXISTS ", string.Empty));

                snapshot5[3].Type.ShouldBe(SQLiteObjectType.Index);
                snapshot5[3].Name.ShouldBe("Person_idx");
                snapshot5[3].SQL.ShouldBe(IndexQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
            }
        }
コード例 #21
0
ファイル: FtsTests.cs プロジェクト: manhnguyenv/Easy.Storage
        public async Task When_searching_for_all_records_in_a_table_matching_multiple_keywords()
        {
            using (var conn = new SQLiteInMemoryConnection())
            {
                await Given_a_logTtable_and_an_ftsTable(conn);

                var logs = new[]
                {
                    new FTSContext.Log {
                        Level = FTSContext.Level.Debug, Message = "Linux can be fun"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Info, Message = "Windows and linux are two operating systems"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Warn, Message = "linux is a Unix like operating system"
                    },
                    new FTSContext.Log {
                        Level = FTSContext.Level.Fatal, Message = "software development in Mac OS operating system may be fun!"
                    }
                };

                var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
                await repo.Insert(logs);

                var term = Term <FTSContext.Log> .All;

                var result = (await conn.Search(term)).ToArray();
                result.Length.ShouldBe(4);

                term.AndNot(Match.Any, l => l.Message, "Mac Os");

                result = (await conn.Search(term)).ToArray();
                result.Length.ShouldBe(3);

                result[0].Level.ShouldBe(logs[0].Level);
                result[0].Message.ShouldBe(logs[0].Message);

                result[1].Level.ShouldBe(logs[1].Level);
                result[1].Message.ShouldBe(logs[1].Message);

                result[2].Level.ShouldBe(logs[2].Level);
                result[2].Message.ShouldBe(logs[2].Message);

                term.Clear();

                term
                .AndNot(Match.Any, l => l.Message, "Mac Os")
                .And(Match.Any, l => l.Message, "operating system", "fun");
                result = (await conn.Search(term)).ToArray();
                result.Length.ShouldBe(2);

                result[0].Level.ShouldBe(logs[0].Level);
                result[0].Message.ShouldBe(logs[0].Message);

                result[1].Level.ShouldBe(logs[2].Level);
                result[1].Message.ShouldBe(logs[2].Message);

                term.Clear();

                term
                .AndNot(Match.Any, l => l.Message, "Mac Os")
                .And(Match.Any, l => l.Message, "operating system*", "fun");
                result = (await conn.Search(term)).ToArray();
                result.Length.ShouldBe(3);

                result[0].Level.ShouldBe(logs[0].Level);
                result[0].Message.ShouldBe(logs[0].Message);

                result[1].Level.ShouldBe(logs[1].Level);
                result[1].Message.ShouldBe(logs[1].Message);

                result[2].Level.ShouldBe(logs[2].Level);
                result[2].Message.ShouldBe(logs[2].Message);
            }
        }