private SqliteTestStore CreateShared(Action initializeDatabase)
{
CreateShared(typeof(SqliteTestStore).Name + _name, initializeDatabase);
_connection = new SqliteConnection(CreateConnectionString(_name));
_connection.Open();
_transaction = _connection.BeginTransaction();
return this;
}
public void Ctor_unsets_read_uncommitted_when_serializable()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.Serializable))
{
Assert.Equal(0L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
}
}
}
public void Ctor_sets_read_uncommitted()
{
using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
Assert.Equal(1L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
}
}
}
public void IsolationLevel_throws_when_completed()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var transaction = connection.BeginTransaction();
transaction.Dispose();
var ex = Assert.Throws<InvalidOperationException>(() => transaction.IsolationLevel);
Assert.Equal(Strings.TransactionCompleted, ex.Message);
}
}
void CreateTable(Type keytype)
{
string type = "INTEGER";
if (keytype == typeof(long))
{
type = "BIGINT";
}
else if (keytype == typeof(string))
{
type = "TEXT";
}
string sql = $@"
CREATE TABLE [main](
[key] " + type + @",
[CreateTime] DATETIME,
[Content] TEXT);
";
string sql2 = @"
CREATE INDEX primary_index ON [main] (
key ASC
);
";
var tran = _sqlCon.BeginTransaction();
using (var cmd = _sqlCon.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
cmd.CommandText = sql2;
cmd.ExecuteNonQuery();
}
tran.Commit();
}
public void Ctor_throws_when_invalid_isolation_level_without_cache()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var ex = Assert.Throws<ArgumentException>(() => connection.BeginTransaction(IsolationLevel.ReadUncommitted));
Assert.Equal(Strings.FormatInvalidIsolationLevelForUnsharedCache(IsolationLevel.ReadUncommitted), ex.Message);
}
using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
{
connection.Open();
connection.BeginTransaction(IsolationLevel.ReadUncommitted);
}
}
public void Ctor_sets_values()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = new SqliteCommand("SELECT 1;", connection, transaction);
Assert.Equal("SELECT 1;", command.CommandText);
Assert.Same(connection, command.Connection);
Assert.Same(transaction, command.Transaction);
}
}
}
public void BeginTransaction_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
{
Assert.NotNull(transaction);
Assert.Equal(connection, transaction.Connection);
Assert.Equal(IsolationLevel.Serializable, transaction.IsolationLevel);
}
}
}
public void BeginTransaction_throws_when_parallel_transaction()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (connection.BeginTransaction())
{
var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction());
Assert.Equal(Strings.ParallelTransactionsNotSupported, ex.Message);
}
}
}
public void BeginTransaction_throws_when_closed()
{
var connection = new SqliteConnection();
var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction());
Assert.Equal(Strings.FormatCallRequiresOpenConnection("BeginTransaction"), ex.Message);
}
public void CreateCommand_returns_command()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = connection.CreateCommand();
Assert.NotNull(command);
Assert.Same(connection, command.Connection);
Assert.Same(transaction, command.Transaction);
}
}
}
public void ExecuteReader_throws_when_transaction_mismatched()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 1;";
connection.Open();
using (var otherConnection = new SqliteConnection("Data Source=:memory:"))
{
otherConnection.Open();
using (var transction = otherConnection.BeginTransaction())
{
command.Transaction = transction;
var ex = Assert.Throws<InvalidOperationException>(() => command.ExecuteReader());
Assert.Equal(Strings.TransactionConnectionMismatch, ex.Message);
}
}
}
}
public void Dispose_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
CreateTestTable(connection);
using (var transaction = connection.BeginTransaction())
{
connection.ExecuteNonQuery("INSERT INTO TestTable VALUES (1);");
transaction.Dispose();
Assert.Null(connection.Transaction);
Assert.Null(transaction.Connection);
}
Assert.Equal(0L, connection.ExecuteScalar<long>("SELECT COUNT(*) FROM TestTable;"));
}
}
public void IsolationLevel_is_infered_when_unspecified()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("PRAGMA read_uncommitted = 1;");
using (var transaction = connection.BeginTransaction())
{
Assert.Equal(IsolationLevel.ReadUncommitted, transaction.IsolationLevel);
}
}
}
public static void Main(string[] args)
{
if (!File.Exists(dbloc)) {
FileStream fs = File.Create(dbloc);
fs.Dispose();
}
SqliteConnection conn = new SqliteConnection("Data Source = " + dbloc);
SqliteCommand comm = conn.CreateCommand();
conn.Open();
comm.CommandText = @"CREATE TABLE IF NOT EXISTS [companies] (
[id] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[Title] string NOT NULL,
[Country] string NOT NULL,
[AddedDate] Date NOT NULL
);
";
comm.ExecuteNonQuery();
//
SqliteTransaction st2 = conn.BeginTransaction();
try
{
SqliteCommand comm2 = conn.CreateCommand();
comm2.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES
(@title, @country, @date);";
List<Company> companies = new List<Company>();
companies.Add(new Company("Roshen", "Ukraine", "10.10.2010"));
companies.Add(new Company("Sandora", "Ukraine", "11.09.2011"));
companies.Add(new Company("Svitoch","Ukraine","12.08.2012"));
companies.Add(new Company("Rosinka","Ukraine","13.07.2013"));
companies.Add(new Company("Korona","Ukraine","14.06.2014"));
companies.Add(new Company("Mircrosoft","USA","10.10.2009"));
companies.Add(new Company("Google","USA","10.10.2008"));
companies.Add(new Company("Facebook","USA","10.10.2007"));
companies.Add(new Company("Air France","France","10.10.2006"));
companies.Add(new Company("Koenisegg","Sweden","10.10.2005"));
comm2.Parameters.Add(new SqliteParameter("@title", SqliteType.Text));
comm2.Parameters.Add(new SqliteParameter("@country", SqliteType.Text));
comm2.Parameters.Add(new SqliteParameter("@date", SqliteType.Text));
foreach (Company comp in companies)
{
comm2.Parameters[0].Value = comp.Title;
comm2.Parameters[1].Value = comp.Country;
comm2.Parameters[2].Value = comp.AddedDate;
if (comm2.ExecuteNonQuery() != 1)
{
throw new InvalidProgramException();
}
}
st2.Commit();
}
catch (Exception ex)
{
st2.Rollback();
}
//
SqliteCommand comm3 = conn.CreateCommand();
comm3.CommandText = @"SELECT MAX(id),Title FROM companies";
var reader = comm3.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}|{1}", reader.GetInt32(0),
reader.GetString(1));
}
Console.WriteLine("------------------");
//
SqliteCommand comm4 = conn.CreateCommand();
comm4.CommandText = @"
UPDATE companies
SET Country= @country1
WHERE Country= @country2;
";
var country1Param = new SqliteParameter();
country1Param.ParameterName = "@country1";
country1Param.Value = "USA";
var country2Param = new SqliteParameter();
country2Param.ParameterName = "@country2";
country2Param.Value = "Ukraine";
comm4.Parameters.Add(country1Param);
comm4.Parameters.Add(country2Param);
comm4.ExecuteNonQuery();
//
SqliteCommand comm5 = conn.CreateCommand();
comm5.CommandText = @"
DELETE FROM companies
WHERE Country<>@country1;
";
comm5.Parameters.Add(country1Param);
comm5.ExecuteNonQuery();
//
SqliteCommand comm6 = conn.CreateCommand();
comm6.CommandText = @"
SELECT COUNT(*) FROM companies
";
Console.WriteLine(comm6.ExecuteScalar());
Console.WriteLine("------------------");
//
SqliteCommand comm7 = conn.CreateCommand();
comm7.CommandText = @"SELECT * FROM companies";
reader = comm7.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}|{1}|{2}|{3}", reader.GetInt32(0),
reader.GetString(1), reader.GetString(2), reader.GetString(3));
}
Console.WriteLine("------------------");
//
Console.WriteLine(@"input example:{'Title':'title','Country':'country','AddedDate':'20.10.2015'}");
Console.WriteLine("------------------");
List<Company> companiesToAdd = new List<Company>();
string str;
while ((str = Console.ReadLine()) != "q")
{
if (str.StartsWith("{"))
{
Company obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Company>(str);
companiesToAdd.Add(obj);
}
}
if (companiesToAdd.Count > 0)
{
SqliteTransaction st = conn.BeginTransaction();
try
{
SqliteCommand comm8 = conn.CreateCommand();
comm8.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES
(@title, @country, @date);";
comm8.Parameters.Add(new SqliteParameter("@title", SqliteType.Text));
comm8.Parameters.Add(new SqliteParameter("@country", SqliteType.Text));
comm8.Parameters.Add(new SqliteParameter("@date", SqliteType.Text));
foreach (Company comp in companiesToAdd)
{
comm8.Parameters[0].Value = comp.Title;
comm8.Parameters[1].Value = comp.Country;
comm8.Parameters[2].Value = comp.AddedDate;
if (comm8.ExecuteNonQuery() != 1)
{
throw new InvalidProgramException();
}
}
st.Commit();
}
catch (Exception ex)
{
st.Rollback();
}
}
conn.Close();
}
public void Serializable_locks()
{
using (var connectionA = new SqliteConnection("Data Source=testdb;Mode=Memory;Cache=Shared"))
{
connectionA.Open();
using (var transactionA = connectionA.BeginTransaction(IsolationLevel.Serializable))
{
using (var connectionB = new SqliteConnection("Data Source=testdb;Mode=Memory;Cache=Shared"))
{
connectionB.Open();
var ex = Assert.Throws<SqliteException>(() => new SqliteTransaction(connectionB, IsolationLevel.Serializable, 1));
Assert.Equal(SQLITE_LOCKED, ex.SqliteErrorCode);
}
}
}
}
public void Dispose_can_be_called_more_than_once()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var transaction = connection.BeginTransaction();
transaction.Dispose();
transaction.Dispose();
}
}
public void Ctor_throws_when_invalid_isolation_level()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var ex = Assert.Throws<ArgumentException>(() => connection.BeginTransaction(IsolationLevel.Snapshot));
Assert.Equal(Strings.FormatInvalidIsolationLevel(IsolationLevel.Snapshot), ex.Message);
}
}
