public SqliteTable <T> WithoutMissingColumns(SqliteDatabase database)
{
var columns = new List <string>();
using (var reader = database.Read($"PRAGMA table_info({TableName})"))
{
while (reader.TryRead())
{
var column = reader.ColumnText(1);
if (column != null)
{
columns.Add(column);
}
}
}
if (columns.Count < 1)
{
throw new MissingTableException($"Table '{TableName}' does not exist.");
}
var properties = ImmutableArray.CreateRange(_properties.Where(p => columns.Contains(p.ColumnName)));
var keyProperties = ImmutableArray.CreateRange(_keyProperties.Where(p => columns.Contains(p.ColumnName)));
if (properties.IsDefaultOrEmpty)
{
throw new MissingTableException($"Table '{TableName}' has the wrong schema.");
}
return(new SqliteTable <T>(TableName, properties, keyProperties));
}
public IEnumerable <KeyValuePair <long, T2> > ValuesWithRowId <T2>(
SqliteDatabase database,
Func <T2> generator,
Action <QueryBuilder>?extendQuery = null) where T2 : T
{
var builder = new QueryBuilder()
.From($"`{TableName}`")
.Select(_properties.Select(p => $"`{p.ColumnName}`"))
.Select("`rowid`");
extendQuery?.Invoke(builder);
using (var reader = database.Read(builder.ToString()))
{
while (reader.TryRead())
{
var item = generator();
int index = 0;
foreach (var property in _properties)
{
property.PropertyHandler.LoadRecord(reader, index++, item);
}
long rowId = reader.ColumnInt64(index++);
yield return(KeyValuePair.Create(rowId, item));
}
}
}
public IEnumerable <T2> Values <T2>(
SqliteDatabase database,
Func <T2> generator,
Action <QueryBuilder>?extendQuery = null) where T2 : T
{
var builder = new QueryBuilder()
.From($"`{TableName}`")
.Select(_properties.Select(p => $"`{p.ColumnName}`"));
extendQuery?.Invoke(builder);
using (var reader = database.Read(builder.ToString()))
{
while (reader.TryRead())
{
var item = generator();
int index = 0;
foreach (var property in _properties)
{
property.PropertyHandler.LoadRecord(reader, index++, item);
}
yield return(item);
}
}
}
public SqliteTableInsert(
SqliteTable <T> table,
SqliteDatabase database,
ConflictResolution conflictResolution = ConflictResolution.Default)
{
_table = table;
var sql = table.InsertSql(1, conflictResolution);
_statement = database.Prepare(sql);
}
public bool Exists(SqliteDatabase database)
{
string sql = "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?";
using (var statement = database.Prepare(sql))
{
statement.BindText(1, TableName);
using (var reader = statement.GetReader())
return(reader.TryRead());
}
}
public void Insert(
SqliteDatabase database,
IEnumerable <T> records,
ConflictResolution conflictResolution = ConflictResolution.Default)
{
int count = 0;
var recordBuffer = new T[database.VariableLimit / _properties.Length];
var sql = InsertSql(recordBuffer.Length, conflictResolution);
var statement = database.Prepare(sql);
try
{
foreach (var record in records)
{
recordBuffer[count++] = record;
if (count == recordBuffer.Length)
{
InsertAll();
count = 0;
}
}
if (count > 0)
{
statement.Dispose();
sql = InsertSql(count, conflictResolution);
statement = database.Prepare(sql);
InsertAll();
}
}
finally
{
statement?.Dispose();
}
void InsertAll()
{
int n = 0;
for (int i = 0; i < count; ++i)
{
foreach (var property in _properties)
{
property.PropertyHandler.BindRecord(statement, ++n, recordBuffer[i]);
}
}
statement.Execute();
}
}
public void Insert(
SqliteDatabase database,
T record,
ConflictResolution conflictResolution = ConflictResolution.Default)
{
var sql = InsertSql(1, conflictResolution);
using (var statement = database.Prepare(sql))
{
for (int i = 0; i < _properties.Length; ++i)
{
_properties[i].PropertyHandler.BindRecord(statement, i + 1, record);
}
statement.Execute();
}
}
public void AddMissingColumns(SqliteDatabase database)
{
CreateTable(database);
var currentFields = new HashSet <string>();
using (var reader = database.Read($"PRAGMA table_info({TableName})"))
{
while (reader.TryRead())
{
var column = reader.ColumnText(1);
if (column != null)
{
currentFields.Add(column);
}
}
}
var missingFields = new List <SqliteProperty <T> >(_properties);
missingFields.RemoveAll(p => currentFields.Contains(p.ColumnName));
var builder = new StringBuilder();
foreach (var field in missingFields)
{
builder.Length = 0;
builder
.Append("ALTER TABLE ")
.AppendField(TableName)
.Append(" ADD COLUMN ")
.AppendProperty(field);
database.Execute(builder.ToString());
}
}
public void InsertWithTransaction(
SqliteDatabase database,
IEnumerable <T> records,
ConflictResolution conflictResolution = ConflictResolution.Default)
{
using (var transaction = new SqliteTransaction(database))
{
var sql = InsertSql(1, conflictResolution);
using (var statement = database.Prepare(sql))
{
foreach (var record in records)
{
for (int i = 0; i < _properties.Length; ++i)
{
_properties[i].PropertyHandler.BindRecord(statement, i + 1, record);
}
statement.Execute();
}
}
transaction.Commit();
}
}
public SqliteTransaction(SqliteDatabase database)
{
_database = database;
_database.Execute("BEGIN TRANSACTION");
}
public void CreateTable(SqliteDatabase database)
{
var sql = CreateTableSql();
database.Execute(sql);
}
public static IEnumerable <T> Values <T>(this SqliteTable <T> table, SqliteDatabase database) where T : class, new()
{
return(table.Values(database, () => new T()));
}
public static IEnumerable <KeyValuePair <long, T> > ValuesWithRowId <T>(this SqliteTable <T> table, SqliteDatabase database) where T : class, new()
{
return(table.ValuesWithRowId(database, () => new T()));
}