public static IEnumerable <T> Query <T>(this MappedTable <T> table, string sqlWhereClause)
{
string sqlQuery = "SELECT * FROM " + SQLiteMapperHelpers.GetEscaped(table.TableName)
+ " " + sqlWhereClause;
return(table.Context.Mapper.Query <T>(sqlQuery));
}
public void InsertRecord <T>(MappedTable table, T record,
SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort,
Type recordType = null)
{
InsertRecords(table, new T[1] {
record
}, conflictResolution, recordType);
}
public void CreateTable(MappedTable mappedTable)
{
WrapDatabaseExceptions(() => {
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = SQLiteMapperHelpers.GetCreateTableStatement(mappedTable);
command.ExecuteNonQuery();
}
});
}
public bool DeleteRecordUsingPrimaryKey <T>(MappedTable <T> table, T recordToDelete)
{
var recordType = typeof(T);
var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(recordType);
var descriptors = SQLiteMapperHelpers.GetPropertyDescriptors(recordType, columnSet);
var primaryKey = columnSet.PrimaryKeyColumn;
if (primaryKey == null)
{
throw new InvalidOperationException("Cannot delete the record because there is no primary key");
}
string sqlQuery = string.Format(
"DELETE FROM [{0}] WHERE [{1}] = ?",
table.TableName,
primaryKey.Name);
int primaryKeyIndex = columnSet.Columns.IndexOf(columnSet.PrimaryKeyColumn);
object primaryKeyValue = descriptors[primaryKeyIndex].GetValue(recordToDelete);
return(ExecuteNonQuery(sqlQuery, primaryKeyValue) != 0);
}
public MappedRecordChangedEventArgs(T record, MappedTable <T> table)
{
this.Record = record;
this.Table = table;
}
public static long GetCount <T>(this MappedTable <T> table, string whereClause = "")
{
return(table.Context.Mapper.GetCount(table.TableName, whereClause));
}
public static bool DoesTableExist <T>(this MappedTable <T> table)
{
return(table.Context.Mapper.DoesTableExist(table.TableName));
}
public static IEnumerable <T> QueryAll <T>(this MappedTable <T> table)
{
return(Query <T>(table, ""));
}
public static int DeleteAllRecords <T>(this MappedTable <T> table)
{
return(table.Context.Mapper.DeleteRecords(table, ""));
}
public static bool DeleteRecordUsingPrimaryKey <T>(this MappedTable <T> table, T recordToDelete)
{
return(table.Context.Mapper.DeleteRecordUsingPrimaryKey(table, recordToDelete));
}
public static int DeleteRecords <T>(this MappedTable <T> table, string sqlWhereClause)
{
return(table.Context.Mapper.DeleteRecords(table, sqlWhereClause));
}
public static void InsertRecord <T>(this MappedTable <T> table, T record,
SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort)
{
table.Context.Mapper.InsertRecord(table, record, conflictResolution);
}
void CollectMappedTables()
{
mappedTables.Clear();
foreach (MemberInfo memberInfo in GetType().GetMembers(
BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance))
{
Type memberType;
PropertyInfo propertyInfo = memberInfo as PropertyInfo;
FieldInfo fieldInfo = memberInfo as FieldInfo;
if (propertyInfo != null)
{
memberType = propertyInfo.PropertyType;
}
else if (fieldInfo != null)
{
memberType = fieldInfo.FieldType;
}
else
{
continue;
}
SQLiteMapperTableAttribute tableAttribute = memberInfo
.GetCustomAttributes(typeof(SQLiteMapperTableAttribute), inherit: true)
.Cast <SQLiteMapperTableAttribute>()
.FirstOrDefault();
if (tableAttribute == null)
{
continue;
}
if (!memberType.IsGenericType ||
!typeof(MappedTable <>).IsAssignableFrom(memberType.GetGenericTypeDefinition()))
{
throw new ArgumentException("Invalid type for property " + memberInfo.Name
+ ": must be based on MappedTable<T>");
}
MappedTable mappedTable = (MappedTable)Activator.CreateInstance(memberType, nonPublic: true);
mappedTable.TableName = memberInfo.Name;
mappedTable.RecordType = memberType.GetGenericArguments()[0];
mappedTable.Context = this;
mappedTable.TrackChanges = typeof(IMappedRecordWithChangeTracking).IsAssignableFrom(mappedTable.RecordType);
var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(mappedTable.RecordType);
mappedTable.ColumnSet = columnSet;
string errorPrefix = "Problem with definition of table \"" + mappedTable.TableName + "\": ";
foreach (var column in mappedTable.ColumnSet.Columns)
{
if (StringComparer.OrdinalIgnoreCase.Equals(column.Name, SQLiteMapper.ChangeNumberColumnName))
{
if (!mappedTable.TrackChanges)
{
throw new InvalidOperationException(errorPrefix
+ "The reserved " + SQLiteMapper.ChangeNumberColumnName + " column cannot be used"
+ " unless the record implements the " + typeof(IMappedRecordWithChangeTracking).Name
+ " interface");
}
if (column.Nullable)
{
throw new InvalidOperationException(errorPrefix
+ "The " + SQLiteMapper.ChangeNumberColumnName + " column cannot be nullable");
}
bool defaultValueOkay = false;
try
{
long value = (long)SQLiteMapperHelpers.ConvertObjectToSql(typeof(long), column.SqlDefaultValue);
if (value == -1)
{
defaultValueOkay = true;
}
}
catch
{
}
if (!defaultValueOkay)
{
throw new InvalidOperationException(errorPrefix
+ "The " + SQLiteMapper.ChangeNumberColumnName + " column must have -1 as its default value");
}
}
}
mappedTable.Indexes.AddRange(
mappedTable.RecordType.GetCustomAttributes(typeof(MappedTableIndexAttribute), inherit: true)
.Cast <MappedTableIndexAttribute>()
// Ensure nondeterministic ordering
.OrderBy(x => x.ToString())
);
if (propertyInfo != null)
{
propertyInfo.SetValue(this, mappedTable, null);
}
else
{
fieldInfo.SetValue(this, mappedTable);
}
mappedTables.Add(mappedTable);
}
}
public static string GetCreateTableStatement(MappedTable mappedTable)
{
string tableName = mappedTable.TableName;
var columnSet = mappedTable.ColumnSet;
// --> Table definition
StringBuilder builder = new StringBuilder();
builder.AppendLine("DROP TABLE IF EXISTS " + GetEscaped(tableName) + ";");
builder.Append("CREATE TABLE " + GetEscaped(tableName) + " (");
bool needsComma = false;
foreach (MappedColumn column in columnSet.Columns)
{
if (needsComma)
{
builder.Append(",");
}
needsComma = true;
builder.AppendLine();
builder.Append(" " + GetEscaped(column.Name));
builder.Append(" " + GetSQLiteAffinityName(column.Affinity));
if (column.PrimaryKey)
{
builder.Append(" PRIMARY KEY");
}
if (!column.Nullable)
{
builder.Append(" NOT NULL");
}
if (column.SqlDefaultValue != null)
{
string literal = SQLiteMapperHelpers.GetSqlLiteralForValue(column.SqlDefaultValue, column);
builder.Append(" DEFAULT " + literal);
}
}
builder.AppendLine();
builder.AppendLine(");");
// --> Indexes
int generatedIndex = 0;
foreach (MappedTableIndexAttribute attribute in mappedTable.Indexes)
{
string indexName = attribute.IndexName;
if (string.IsNullOrEmpty(indexName))
{
indexName = tableName + "_Index" + generatedIndex++;
builder.Append("\r\nCREATE");
if (attribute.Unique)
{
builder.Append(" UNIQUE");
}
builder.Append(" INDEX " + GetEscaped(indexName) + " ON " + GetEscaped(tableName) + "(");
bool first = true;
foreach (string columnDefinition in attribute.ColumnDefinitions)
{
if (!first)
{
builder.Append(", ");
}
builder.Append(GetEscaped(columnDefinition));
first = false;
}
builder.Append(");");
}
}
// --> Change number feature (index and two triggers)
if (mappedTable.TrackChanges)
{
builder.AppendFormat("\r\nCREATE UNIQUE INDEX {0} ON {1} ({2});",
GetEscaped(tableName + "_Index" + generatedIndex++),
GetEscaped(tableName),
GetEscaped(SQLiteMapper.ChangeNumberColumnName));
string triggerFormat
= "\r\nCREATE TRIGGER {0} AFTER {1} ON {2}"
+ "\r\nBEGIN"
+ "\r\n UPDATE {2}"
+ "\r\n SET {3} = (SELECT MAX({3}) FROM {2} LIMIT 1)+1"
+ "\r\n WHERE ROWID = NEW.ROWID;"
+ "\r\nEND;";
builder.AppendFormat(triggerFormat,
GetEscaped(tableName + "_ChangeNumberTrigger0"),
"INSERT",
GetEscaped(tableName),
GetEscaped(SQLiteMapper.ChangeNumberColumnName));
builder.AppendFormat(triggerFormat,
GetEscaped(tableName + "_ChangeNumberTrigger1"),
"UPDATE",
GetEscaped(tableName),
GetEscaped(SQLiteMapper.ChangeNumberColumnName));
}
return(builder.ToString());
}
public int DeleteRecords <T>(MappedTable <T> table, string sqlWhereClause)
{
return(ExecuteNonQuery("DELETE FROM [" + table.TableName + "] " + sqlWhereClause));
}
public void InsertRecords <T>(MappedTable table, IEnumerable <T> records,
SQLiteConflictResolution conflictResolution = SQLiteConflictResolution.Abort,
Type recordType = null)
{
if (recordType == null)
{
recordType = typeof(T);
}
var columnSet = SQLiteMapperHelpers.CreateColumnSetForType(recordType);
var descriptors = SQLiteMapperHelpers.GetPropertyDescriptors(recordType, columnSet);
WrapDatabaseExceptions(() => {
using (SQLiteCommand command = connection.CreateCommand())
using (SQLiteCommand fixReplaceCommand = connection.CreateCommand())
{
command.CommandText = SQLiteMapperHelpers.GetInsertStatement(columnSet, table.TableName, conflictResolution);
foreach (var column in columnSet.Columns)
{
var parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
}
command.Prepare();
// SQLite implements "INSERT OR REPLACE" as a DELETE followed by an INSERT, which can cause
// trouble when our TrackChanges trigger tries to compute MAX(ChangeNumber). The workaround
// is to make sure an accurate ChangeNumber is in the C# record before we start
// the INSERT OR REPLACE. (Normally this is already true, but sometimes it's 0 or a
// little outdated so we do a SELECT to be sure.)
bool fixReplace = table.TrackChanges && conflictResolution == SQLiteConflictResolution.Replace;
SQLiteParameter fixReplaceParameter = null;
PropertyDescriptor fixReplacePrimaryKeyDescriptor = null;
if (fixReplace)
{
if (columnSet.PrimaryKeyColumn == null)
{
throw new InvalidOperationException(
"SQLiteConflictResolution.Replace cannot be used without including the primary key");
}
int primaryKeyIndex = columnSet.Columns.IndexOf(columnSet.PrimaryKeyColumn);
fixReplacePrimaryKeyDescriptor = descriptors[primaryKeyIndex];
fixReplaceCommand.CommandText = string.Format(
"SELECT [{0}] FROM [{1}] WHERE [{2}] = ?",
SQLiteMapper.ChangeNumberColumnName,
table.TableName,
columnSet.PrimaryKeyColumn.Name);
fixReplaceParameter = fixReplaceCommand.CreateParameter();
fixReplaceParameter.Direction = ParameterDirection.Input;
fixReplaceCommand.Parameters.Add(fixReplaceParameter);
}
using (var transaction = this.BeginTransaction())
{
foreach (var record in records)
{
if (fixReplace)
{
var castedRecord = record as IMappedRecordWithChangeTracking;
if (castedRecord == null)
{
throw new InvalidOperationException(
"SQLiteConflictResolution.Replace cannot be used without including the ChangeNumber column");
}
fixReplaceParameter.Value = fixReplacePrimaryKeyDescriptor.GetValue(record);
// NOTE: If the SELECT returns no rows, Convert.ToInt64() will convert NULL to 0
castedRecord.ChangeNumber = Convert.ToInt64(fixReplaceCommand.ExecuteScalar());
}
for (int i = 0; i < columnSet.Columns.Count; ++i)
{
var column = columnSet.Columns[i];
var parameter = command.Parameters[i];
var descriptor = descriptors[i];
object obj = descriptor.GetValue(record);
parameter.Value = SQLiteMapperHelpers.ConvertObjectToSql(descriptor.PropertyType, obj);
}
int recordsModified = command.ExecuteNonQuery();
if (SQLiteMapper.DefaultLogLevel >= SQLiteMapperLogLevel.Verbose)
{
Debug.WriteLine("Inserted " + recordsModified + " rows");
}
}
transaction.Commit();
}
}
});
}