public void RunSqlite()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE Test(Value);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES(1);";
cmd.ExecuteNonQuery();
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
System.Console.WriteLine(dr[2].ToString());
}
}
}
var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
var schema = databaseReader.ReadAll();
var tableList = databaseReader.TableList();
var tables = databaseReader.AllTables();
var views = databaseReader.AllViews();
Assert.NotEmpty(tableList);
}
}
private static void AddHistoricMessage(Message message)
{
var sql = string.Format("INSERT INTO {0} (MessageId, MessageType, Topic, Timestamp, HeaderBag, Body) VALUES (@MessageId, @MessageType, @Topic, @Timestamp, @HeaderBag, @Body)", _sqliteTestHelper.TableName_Messages);
var parameters = new[]
{
new SqliteParameter("MessageId", message.Id.ToString()),
new SqliteParameter("MessageType", message.Header.MessageType.ToString()),
new SqliteParameter("Topic", message.Header.Topic),
new SqliteParameter("Timestamp", SqliteType.Text) { Value =message.Header.TimeStamp.ToString("s")},
new SqliteParameter("HeaderBag",SqliteType.Text) { Value = JsonConvert.SerializeObject(message.Header.Bag)},
new SqliteParameter("Body", message.Body.Value),
};
using (var connection = new SqliteConnection(_sqliteTestHelper.ConnectionString))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = sql;
//command.Parameters.AddRange(parameters); used to work... but can't with current Sqlite lib. Iterator issue
for (var index = 0; index < parameters.Length; index++)
{
command.Parameters.Add(parameters[index]);
}
command.ExecuteNonQuery();
}
}
bool isTableExist(string tablename)
{
var sql = $"SELECT COUNT(*) FROM sqlite_master where type='table' and name='{tablename}'";
using (var cmd = _sqlCon.CreateCommand())
{
cmd.CommandText = sql;
return(Convert.ToInt32(cmd.ExecuteScalar()) > 0);
}
}
public void ExecuteReader_throws_when_connection_closed()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var ex = Assert.Throws<InvalidOperationException>(() => connection.CreateCommand().ExecuteReader());
Assert.Equal(Strings.FormatCallRequiresOpenConnection("ExecuteReader"), ex.Message);
}
}
private void CreateAndOpenConnection(bool sharedCache = false)
{
_connection = new SqliteConnection(CreateConnectionString(_name, sharedCache));
_connection.Open();
var command = _connection.CreateCommand();
command.CommandText = "PRAGMA foreign_keys=ON;";
command.ExecuteNonQuery();
}
public void CreateTable()
{
using (Microsoft.Data.Sqlite.SqliteConnection connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString))
{
connection.Open();
SqliteCommand cmd = connection.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS favorite_beers(name VARCHAR(50))";
cmd.ExecuteNonQuery();
}
}
public override IModel ConstructRelationalModel([NotNull] string connectionString)
{
Check.NotEmpty(connectionString, nameof(connectionString));
var modelBuilder = new ModelBuilder(new ConventionSet());
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
var tables = new Dictionary<string, string>();
var indexes = new List<SqliteIndexInfo>();
var master = connection.CreateCommand();
master.CommandText = "SELECT type, name, sql, tbl_name FROM sqlite_master";
using (var reader = master.ExecuteReader())
{
while (reader.Read())
{
var type = reader.GetString(0);
var name = reader.GetString(1);
var sql = reader.GetValue(2) as string; // can be null
var tableName = reader.GetString(3);
if (type == "table"
&& name != "sqlite_sequence"
&& _tableSelectionSet.Allows(TableSelection.Any, name))
{
tables.Add(name, sql);
}
else if (type == "index")
{
indexes.Add(new SqliteIndexInfo
{
Name = name,
TableName = tableName,
Sql = sql
});
}
}
}
LoadTablesAndColumns(connection, modelBuilder, tables.Keys);
LoadIndexes(connection, modelBuilder, indexes);
foreach (var item in tables)
{
SqliteDmlParser.ParseTableDefinition(modelBuilder, item.Key, item.Value);
}
AddAlternateKeys(modelBuilder);
LoadForeignKeys(connection, modelBuilder, tables.Keys);
}
return modelBuilder.Model;
}
public void DropTable()
{
using (Microsoft.Data.Sqlite.SqliteConnection connection = new Microsoft.Data.Sqlite.SqliteConnection(connectionString))
{
SqliteCommand cmd = connection.CreateCommand();
cmd.CommandText = "DROP TABLE IF EXISTS favorite_beers";
connection.Open();
cmd.ExecuteNonQuery();
}
}
private SqliteConnection CreateDatabaseWithTable(string dataSourceTestDb, string createTableScript)
{
_sqlConnection = new SqliteConnection(dataSourceTestDb);
_sqlConnection.Open();
using (var command = _sqlConnection.CreateCommand())
{
command.CommandText = createTableScript;
command.ExecuteNonQuery();
}
return _sqlConnection;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var path = FileSystem.Current.LocalStorage.Path;
var connection = new Microsoft.Data.Sqlite.SqliteConnection($"Data Source= {path}/{filename}");
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "PRAGMA key = 'password';";
command.ExecuteNonQuery();
optionsBuilder.UseSqlite(connection);
// optionsBuilder.UseSqlite($"Filename= {path}/{filename}");
base.OnConfiguring(optionsBuilder);
}
public void CharTest2()
{
using (var connection = new SqliteConnection(@"Data Source=Database\TestData.sqlite"))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT @p";
var p = command.CreateParameter();
p.Value = '1';
p.ParameterName = "@p";
command.Parameters.Add(p);
var r = command.ExecuteReader();
r.Read();
var value = r.GetValue(0);
Assert.AreNotEqual((long)49, value, $"{value.GetType().FullName}");
}
}
public void UInt32Test()
{
using (var connection = new SqliteConnection(@"Data Source=Database\TestData.sqlite"))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT @p";
var p = command.CreateParameter();
p.Value = UInt32.MaxValue;
p.DbType = System.Data.DbType.UInt32;
p.ParameterName = "@p";
command.Parameters.Add(p);
var r = command.ExecuteReader();
r.Read();
var value = r.GetValue(0);
Assert.AreEqual(UInt32.MaxValue, value, $"{value.GetType().FullName} {value}");
}
}
private void LoadIndexes(SqliteConnection connection, ModelBuilder modelBuilder, ICollection<SqliteIndexInfo> indexes)
{
foreach (var index in indexes)
{
var indexInfo = connection.CreateCommand();
indexInfo.CommandText = $"PRAGMA index_info(\"{index.Name.Replace("\"", "\"\"")}\");";
var indexProps = new List<string>();
using (var reader = indexInfo.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetValue((int)IndexInfo.Name) as string;
if (!string.IsNullOrEmpty(name))
{
indexProps.Add(name);
}
}
}
if (indexProps.Count > 0)
{
var indexBuilder = modelBuilder.Entity(index.TableName)
.Index(indexProps.ToArray())
.SqliteIndexName(index.Name);
if (!string.IsNullOrEmpty(index.Sql))
{
var uniqueKeyword = index.Sql.IndexOf("UNIQUE", StringComparison.OrdinalIgnoreCase);
var indexKeyword = index.Sql.IndexOf("INDEX", StringComparison.OrdinalIgnoreCase);
indexBuilder.Unique(uniqueKeyword > 0 && uniqueKeyword < indexKeyword);
}
}
}
}
private void LoadForeignKeys(SqliteConnection connection, ModelBuilder modelBuilder, ICollection<string> tables)
{
foreach (var tableName in tables)
{
var fkList = connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{tableName.Replace("\"", "\"\"")}\");";
var foreignKeys = new Dictionary<int, ForeignKeyInfo>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32((int)ForeignKeyList.Id);
var refTable = reader.GetString((int)ForeignKeyList.Table);
ForeignKeyInfo foreignKey;
if (!foreignKeys.TryGetValue(id, out foreignKey))
{
foreignKeys.Add(id, (foreignKey = new ForeignKeyInfo { Table = tableName, ReferencedTable = refTable }));
}
foreignKey.From.Add(reader.GetString((int)ForeignKeyList.From));
foreignKey.To.Add(reader.GetString((int)ForeignKeyList.To));
}
}
var dependentEntityType = modelBuilder.Entity(tableName).Metadata;
foreach (var fkInfo in foreignKeys.Values)
{
try
{
var principalEntityType = modelBuilder.Model.EntityTypes.First(e => e.Name.Equals(fkInfo.ReferencedTable, StringComparison.OrdinalIgnoreCase));
var principalProps = fkInfo.To
.Select(to => principalEntityType
.Properties
.First(p => p.Sqlite().ColumnName.Equals(to, StringComparison.OrdinalIgnoreCase))
)
.ToList()
.AsReadOnly();
var principalKey = principalEntityType.FindKey(principalProps);
if (principalKey == null)
{
var index = principalEntityType.FindIndex(principalProps);
if (index != null
&& index.IsUnique == true)
{
principalKey = principalEntityType.AddKey(principalProps);
}
else
{
LogFailedForeignKey(fkInfo);
continue;
}
}
var depProps = fkInfo.From
.Select(
@from => dependentEntityType
.Properties.
First(p => p.Sqlite().ColumnName.Equals(@from, StringComparison.OrdinalIgnoreCase))
)
.ToList()
.AsReadOnly();
var foreignKey = dependentEntityType.GetOrAddForeignKey(depProps, principalKey, principalEntityType);
if (dependentEntityType.FindIndex(depProps)?.IsUnique == true
|| dependentEntityType.GetKeys().Any(k => k.Properties.All(p => depProps.Contains(p))))
{
foreignKey.IsUnique = true;
}
}
catch (InvalidOperationException)
{
LogFailedForeignKey(fkInfo);
}
}
}
}
public void Bind_binds_string_values_without_embedded_nulls()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT @Text || 'ing';";
command.Parameters.AddWithValue("@Text", "test");
connection.Open();
var result = command.ExecuteScalar();
Assert.Equal("testing", result);
}
}
public void Bind_throws_when_unknown()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT @Parameter;";
command.Parameters.AddWithValue("@Parameter", new object());
connection.Open();
var ex = Assert.Throws<InvalidOperationException>(() => command.ExecuteScalar());
Assert.Equal(Strings.FormatUnknownDataType(typeof(object)), ex.Message);
}
}
public void Bind_works(object value, object coercedValue)
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT @Parameter;";
command.Parameters.AddWithValue("@Parameter", value);
connection.Open();
var result = command.ExecuteScalar();
Assert.Equal(coercedValue, result);
}
}
public void ExecuteReader_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 1;";
connection.Open();
using (var reader = command.ExecuteReader())
{
Assert.NotNull(reader);
}
}
}
public void DecimalTest()
{
using (var connection = new SqliteConnection(@"Data Source=Database\TestData.sqlite"))
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT MoneyValue FROM LinqDataTypes";
var r = command.ExecuteReader();
while (r.Read())
{
var value = r.GetValue(0);
Console.WriteLine($"{value.GetType().FullName} {value}, {r.GetFieldType(0)}");
Assert.AreNotEqual(typeof(long), r.GetFieldType(0), $"{value.GetType().FullName} {value}");
}
}
}
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 ExecuteNonQuery_throws_when_no_command_text()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var ex = Assert.Throws<InvalidOperationException>(() => connection.CreateCommand().ExecuteNonQuery());
Assert.Equal(Strings.FormatCallRequiresSetCommandText("ExecuteNonQuery"), ex.Message);
}
}
public void ExecuteReader_works_when_trailing_comments()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 0; -- My favorite number";
connection.Open();
using (var reader = command.ExecuteReader())
{
var hasResult = reader.NextResult();
Assert.False(hasResult);
}
}
}
public void ExecuteReader_works_when_comments()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "-- TODO: Write SQL";
connection.Open();
using (var reader = command.ExecuteReader())
{
Assert.False(reader.HasRows);
Assert.Equal(-1, reader.RecordsAffected);
}
}
}
public void ExecuteReader_skips_DML_statements()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Test(Value);");
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO Test VALUES(1);
SELECT 1;";
using (var reader = command.ExecuteReader())
{
var hasData = reader.Read();
Assert.True(hasData);
Assert.Equal(1L, reader.GetInt64(0));
}
}
}
private void LoadTablesAndColumns(SqliteConnection connection, ModelBuilder modelBuilder, ICollection<string> tables)
{
foreach (var tableName in tables)
{
modelBuilder.Entity(tableName, builder =>
{
builder.ToTable(tableName);
var tableInfo = connection.CreateCommand();
tableInfo.CommandText = $"PRAGMA table_info(\"{tableName.Replace("\"", "\"\"")}\");";
var keyProps = new List<string>();
using (var reader = tableInfo.ExecuteReader())
{
while (reader.Read())
{
var colName = reader.GetString((int)TableInfo.Name);
var typeName = reader.GetString((int)TableInfo.Type);
var isPk = reader.GetBoolean((int)TableInfo.Pk);
var notNull = isPk || reader.GetBoolean((int)TableInfo.NotNull);
var clrType = _typeMapper.GetClrType(typeName, nullable: !notNull);
var property = builder.Property(clrType, colName)
.HasColumnName(colName);
if (!string.IsNullOrEmpty(typeName))
{
property.HasColumnType(typeName);
}
var defaultVal = reader.GetValue((int)TableInfo.DefaultValue) as string;
if (!string.IsNullOrEmpty(defaultVal))
{
property.HasDefaultValueSql(defaultVal);
}
if (isPk)
{
keyProps.Add(colName);
}
else
{
property.Required(notNull);
}
}
}
if (keyProps.Count > 0)
{
builder.Key(keyProps.ToArray());
}
else
{
var errorMessage = Strings.MissingPrimaryKey(tableName);
builder.Metadata.AddAnnotation(AnnotationNameEntityTypeError, errorMessage);
Logger.LogWarning(errorMessage);
}
});
}
}
public void Mars_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var command1 = connection.CreateCommand();
command1.CommandText = "SELECT '1A' UNION SELECT '1B';";
using (var reader1 = command1.ExecuteReader())
{
reader1.Read();
Assert.Equal("1A", reader1.GetString(0));
var command2 = connection.CreateCommand();
command2.CommandText = "SELECT '2A' UNION SELECT '2B';";
using (var reader2 = command2.ExecuteReader())
{
reader2.Read();
Assert.Equal("2A", reader2.GetString(0));
reader1.Read();
Assert.Equal("1B", reader1.GetString(0));
reader2.Read();
Assert.Equal("2B", reader2.GetString(0));
}
}
}
}
private async Task CreateDatabaseAsync(SqliteConnection connection)
{
await connection.CreateCommand("BEGIN; SELECT InitSpatialMetaData(); COMMIT;").ExecuteNonQueryAsync();
await connection.ExecuteNonQueryAsync(@"
CREATE TABLE IF NOT EXISTS IPRangeProviders (
Id INTEGER NOT NULL PRIMARY KEY ASC,
Name TEXT NOT NULL
);
").ConfigureAwait(false);
await connection.ExecuteNonQueryAsync(@"
CREATE TABLE IF NOT EXISTS Areas (
Id INTEGER NOT NULL PRIMARY KEY ASC,
Name TEXT NOT NULL,
NamesByCultures TEXT,
Kind INTEGER NOT NULL,
Code TEXT,
AdminAreaLevel1Id INTEGER,
AdminAreaLevel2Id INTEGER,
CountryId INTEGER
);
CREATE INDEX IF NOT EXISTS IDX_Area_Kind ON Areas (Kind);
SELECT AddGeometryColumn('Areas', 'Coordinates', 4326, 'POINT', 'XY');
SELECT CreateSpatialIndex('Areas', 'Coordinates');
").ConfigureAwait(false);
await connection.ExecuteNonQueryAsync(@"
CREATE TABLE IF NOT EXISTS IPRangeInfos (
AreaId INTEGER NOT NULL,
ProviderId INTEGER NOT NULL,
StartLow INTEGER NOT NULL,
StartHigh INTEGER,
EndLow INTEGER NOT NULL,
EndHigh INTEGER
);
").ConfigureAwait(false);
}
public void ExecuteScalar_returns_long_when_multiple_rows()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 42 UNION SELECT 43;";
connection.Open();
Assert.Equal(42L, command.ExecuteScalar());
}
}
public void Bind_requires_set_value()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT @Parameter;";
command.Parameters.Add(new SqliteParameter { ParameterName = "@Parameter" });
connection.Open();
var ex = Assert.Throws<InvalidOperationException>(() => command.ExecuteNonQuery());
Assert.Equal(Strings.FormatRequiresSet("Value"), ex.Message);
}
}
public void ExecuteNonQuery_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 1;";
connection.Open();
Assert.Equal(-1, command.ExecuteNonQuery());
}
}
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 Bind_is_noop_on_unknown_parameter()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT 1;";
command.Parameters.AddWithValue("@Unknown", 1);
connection.Open();
command.ExecuteNonQuery();
}
}
private void MigrateDb0_9(IUnitOfWork uow)
{
var db = new SqliteConnection("Data Source=data/nadekobot.sqlite");
if (!File.Exists("data/nadekobot.sqlite"))
{
_log.Warn("No data from the old database will be migrated.");
return;
}
db.Open();
var com = db.CreateCommand();
com.CommandText = "SELECT * FROM Announcement";
var reader = com.ExecuteReader();
var i = 0;
while (reader.Read())
{
var gid = (ulong)(long)reader["ServerId"];
var greet = (long)reader["Greet"] == 1;
var greetDM = (long)reader["GreetPM"] == 1;
var greetChannel = (ulong)(long)reader["GreetChannelId"];
var greetMsg = (string)reader["GreetText"];
var bye = (long)reader["Bye"] == 1;
var byeDM = (long)reader["ByePM"] == 1;
var byeChannel = (ulong)(long)reader["ByeChannelId"];
var byeMsg = (string)reader["ByeText"];
var grdel = false;
var byedel = grdel;
var gc = uow.GuildConfigs.For(gid);
if (greetDM)
gc.SendDmGreetMessage = greet;
else
gc.SendChannelGreetMessage = greet;
gc.GreetMessageChannelId = greetChannel;
gc.ChannelGreetMessageText = greetMsg;
gc.SendChannelByeMessage = bye;
gc.ByeMessageChannelId = byeChannel;
gc.ChannelByeMessageText = byeMsg;
gc.AutoDeleteGreetMessagesTimer = gc.AutoDeleteByeMessagesTimer = grdel ? 30 : 0;
_log.Info(++i);
}
var com2 = db.CreateCommand();
com.CommandText = "SELECT * FROM CurrencyState GROUP BY UserId";
i = 0;
var reader2 = com.ExecuteReader();
while (reader2.Read())
{
_log.Info(++i);
var curr = new Currency()
{
Amount = (long)reader2["Value"],
UserId = (ulong)(long)reader2["UserId"]
};
uow.Currency.Add(curr);
}
db.Close();
try { File.Move("data/nadekobot.sqlite", "data/DELETE_ME_nadekobot.sqlite"); } catch { }
}
public void ExecuteReader_throws_when_parameter_unset()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
var command = connection.CreateCommand();
command.CommandText = "SELECT @Parameter;";
connection.Open();
var ex = Assert.Throws<InvalidOperationException>(() => command.ExecuteScalar());
Assert.Equal(Strings.FormatMissingParameters("@Parameter"), ex.Message);
}
}
