public static string ColumnsValue(this DataSet.Define define)
{
string result;
if (ColumnssValueCache.TryGetValue(define, out result))
{
return(result);
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < define.Columns.Count; ++i)
{
var column = define.Columns[i];
if (column.Value == DataSet.Type.String || column.Value == DataSet.Type.Blob)
{
builder.Append(i == 0 ? "?" : ", ?");
}
else
{
builder.AppendFormat(i == 0 ? "<{0}>" : ", <{0}>", column.Key);
}
}
result = builder.ToString();
try
{
ColumnssValueCache.Add(define, result);
}
catch (Exception)
{
}
return(result);
}
public static string Where(this DataSet.Define define)
{
string result;
if (WhereCache.TryGetValue(define, out result))
{
return(result);
}
StringBuilder builder = new StringBuilder();
builder.Append(" WHERE 1 = 1");
for (int i = 0; i < define.Keys.Count; ++i)
{
builder.AppendFormat(" AND Key{0} = @{0}", i);
DataSet.Type type = define.Keys[i];
if (type == DataSet.Type.String || type == DataSet.Type.Blob)
{
builder.AppendFormat(" AND RealKey{0} = ?", i);
}
}
result = builder.ToString();
try
{
WhereCache.Add(define, result);
}
catch (Exception)
{
}
return(result);
}
public static string KeysValue(this DataSet.Define define)
{
string result;
if (KeysValueCache.TryGetValue(define, out result))
{
return(result);
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < define.Keys.Count; ++i)
{
builder.AppendFormat(i == 0 ? "@{0}" : ", @{0}", i);
DataSet.Type type = define.Keys[i];
if (type == DataSet.Type.String || type == DataSet.Type.Blob)
{
builder.Append(", ?");
}
}
result = builder.ToString();
try
{
KeysValueCache.Add(define, result);
}
catch (Exception)
{
}
return(result);
}
public override Task <DataSet.Reader> Scan(DataSet.Define define)
{
TaskCompletionSource <DataSet.Reader> source = new TaskCompletionSource <DataSet.Reader>();
try
{
string sql = string.Format("SELECT {0}, {1} FROM '{2}';", define.KeysName(), define.ColumnsName(), define.Name);
SQLiteCommand command = Pool <SQLiteCommand> .Default.Acquire();
command.Connection = connect;
command.CommandText = sql;
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.Add(parameters[i]);
}
parameters.Clear();
commands.Add(new KeyValuePair <SQLiteCommand, DataSet.Define>(command, define));
source.TrySetResult(new SimpleReader(define, command));
}
catch (Exception e)
{
source.SetException(e);
}
return(source.Task);
}
protected override async Task VerifyTable(DataSet.Define define)
{
SQLiteCommand select = new SQLiteCommand(
string.Format("SELECT count(name) as result FROM 'sqlite_master' WHERE type = 'table' AND name = '{0}';",
define.Name), connect);
using (select)
{
var reader = await select.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
if (reader.GetInt32(0) != 0)
{
return;
}
}
}
SQLiteCommand create = new SQLiteCommand(define.CreateTable(datatypenames), connect);
using (create)
{
await create.ExecuteNonQueryAsync();
}
}
public override async Task <bool> Select(DataSet.Define define, DataSet.Key key, DataSet.Column column)
{
string sql = string.Format("SELECT {0} FROM '{1}'{2};", define.ColumnsName(), define.Name, define.Where());
sql = this.BindKey(define, key, sql);
SQLiteCommand command = Pool <SQLiteCommand> .Default.Acquire();
command.Connection = connect;
command.CommandText = sql;
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.Add(parameters[i]);
}
parameters.Clear();
using (var reader = await command.ExecuteReaderAsync())
{
if (!await reader.ReadAsync())
{
Pool <SQLiteCommand> .Default.Release(command);
return(false);
}
reader.ReadColumn(define, column);
}
Pool <SQLiteCommand> .Default.Release(command);
return(true);
}
public override void Remove(DataSet.Define define, DataSet.Key key)
{
string sql = string.Format("DELETE FROM '{0}'{1};", define.Name, define.Where());
sql = this.BindKey(define, key, sql);
SQLiteCommand command = Pool <SQLiteCommand> .Default.Acquire();
command.Connection = connect;
command.CommandText = sql;
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.Add(parameters[i]);
}
parameters.Clear();
commands.Add(command);
}
public override void Select(DataSet.Define define, DataSet.Key key)
{
string sql = string.Format("SELECT {0}, {1} FROM '{2}'{3};", define.KeysName(), define.ColumnsName(), define.Name, define.Where());
sql = this.BindKey(define, key, sql);
SQLiteCommand command = Pool <SQLiteCommand> .Default.Acquire();
command.Connection = connect;
command.CommandText = sql;
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.Add(parameters[i]);
}
parameters.Clear();
commands.Add(new KeyValuePair <SQLiteCommand, DataSet.Define>(command, define));
}
public override void Write(DataSet.Define define, DataSet.Key key, DataSet.Column column)
{
Remove(define, key);
string sql = string.Format("INSERT INTO '{0}' ({1}, {2}) VALUES ({3}, {4});", define.Name, define.KeysName(),
define.ColumnsName(), define.KeysValue(), define.ColumnsValue());
sql = this.BindKeyColumn(define, key, column, sql);
SQLiteCommand command = Pool <SQLiteCommand> .Default.Acquire();
command.Connection = connect;
command.CommandText = sql;
for (int i = 0; i < parameters.Count; ++i)
{
command.Parameters.Add(parameters[i]);
}
parameters.Clear();
commands.Add(command);
}
public static string BindColumn(this Command command, DataSet.Define define, DataSet.Column column, string sql)
{
ColumnReplacement replacement = Pool <ColumnReplacement> .Default.Acquire();
replacement.define = define;
replacement.column = column;
string result = ColumnPattern.Replace(sql, replacement.Execute);
replacement.define = null;
replacement.column = null;
Pool <ColumnReplacement> .Default.Release(replacement);
var columns = define.Columns;
for (int i = 0; i < columns.Count; ++i)
{
var c = columns[i];
if (c.Value == DataSet.Type.String)
{
string str = null;
if (!column.Read(c.Key, ref str))
{
throw new DataSet.TypeMismatchException();
}
str = str ?? "";
command.AddParam(str);
}
else if (c.Value == DataSet.Type.Blob)
{
byte[] bytes = null;
if (!column.Read(c.Key, ref bytes))
{
throw new DataSet.TypeMismatchException();
}
command.AddParam(bytes);
}
}
return(result);
}
public static string BindKey(this Command command, DataSet.Define define, DataSet.Key key, string sql)
{
KeyReplacement replacement = Pool <KeyReplacement> .Default.Acquire();
replacement.define = define;
replacement.key = key;
string result = KeyPattern.Replace(sql, replacement.Execute);
replacement.define = null;
replacement.key = null;
Pool <KeyReplacement> .Default.Release(replacement);
var keys = define.Keys;
for (int i = 0; i < keys.Count; ++i)
{
var type = keys[i];
if (type == DataSet.Type.String)
{
string str = null;
if (!key.Read(i, ref str))
{
throw new DataSet.TypeMismatchException();
}
str = str ?? "";
command.AddParam(str);
}
else if (type == DataSet.Type.Blob)
{
byte[] bytes = null;
if (!key.Read(i, ref bytes))
{
throw new DataSet.TypeMismatchException();
}
command.AddParam(bytes);
}
}
return(result);
}
public static string CreateTable(this DataSet.Define define, IDictionary <DataSet.Type, string> datatypenames)
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat("CREATE TABLE '{0}' (", define.Name);
for (int i = 0; i < define.Keys.Count; ++i)
{
DataSet.Type type = define.Keys[i];
switch (type)
{
case DataSet.Type.String:
case DataSet.Type.Blob:
builder.AppendFormat(i == 0 ? "Key{0} {1} NOT NULL" : ", Key{0} {1} NOT NULL", i, datatypenames[DataSet.Type.Integer]);
builder.AppendFormat(", RealKey{0} {1} NOT NULL", i, datatypenames[type]);
break;
case DataSet.Type.Bool:
builder.AppendFormat(i == 0 ? "Key{0} {1} NOT NULL" : ", Key{0} {1} NOT NULL", i, datatypenames[DataSet.Type.Integer]);
break;
default:
builder.AppendFormat(i == 0 ? "Key{0} {1} NOT NULL" : ", Key{0} {1} NOT NULL", i, datatypenames[type]);
break;
}
}
foreach (var column in define.Columns)
{
builder.AppendFormat(", _{0}_ {1}", column.Key, datatypenames[column.Value]);
}
builder.AppendFormat(");CREATE INDEX '{0}#Index' ON '{0}' (", define.Name);
for (int i = 0; i < define.Keys.Count; ++i)
{
builder.AppendFormat(i == 0 ? "Key{0}" : ", Key{0}", i);
}
builder.Append(");");
return(builder.ToString());
}
public override async Task <bool> Read(DataSet.Key key, DataSet.Column column)
{
if (reader == null)
{
reader = await commands[index].Key.ExecuteReaderAsync() as SQLiteDataReader;
}
while (true)
{
if (await reader.ReadAsync())
{
break;
}
++index;
if (index >= commands.Count)
{
return(false);
}
reader.Close();
reader = await commands[index].Key.ExecuteReaderAsync() as SQLiteDataReader;
}
DataSet.Define define = commands[index].Value;
reader.ReadColumn(define, column, reader.ReadKey(define, key, 0));
return(true);
}
public static string ColumnsName(this DataSet.Define define)
{
string result;
if (ColumnsNameCache.TryGetValue(define, out result))
{
return(result);
}
StringBuilder builder = new StringBuilder();
for (int i = 0; i < define.Columns.Count; ++i)
{
builder.AppendFormat(i == 0 ? "_{0}_" : ", _{0}_", define.Columns[i].Key);
}
result = builder.ToString();
try
{
ColumnsNameCache.Add(define, result);
}
catch (Exception)
{
}
return(result);
}
public static int ReadKey(this DbDataReader reader, DataSet.Define define, DataSet.Key key, int start = 0)
{
int index = start;
for (int i = 0; i < define.Keys.Count; ++i)
{
switch (define.Keys[i])
{
case DataSet.Type.Bool:
{
int result = reader.GetInt32(index);
if (!key.Write(i, result != 0))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Integer:
{
int result = reader.GetInt32(index);
if (!key.Write(i, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Long:
{
long result = reader.GetInt64(index);
if (!key.Write(i, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Double:
{
double result = reader.GetDouble(index);
if (!key.Write(i, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.String:
{
++index;
string result = reader.GetString(index);
if (!key.Write(i, result ?? ""))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Blob:
{
++index;
using (Stream stream = reader.GetStream(index))
{
int length = (int)stream.Length;
byte[] result = new byte[length];
stream.Read(result, 0, length);
if (!key.Write(i, result))
{
throw new DataSet.TypeMismatchException();
}
}
}
break;
}
++index;
}
return(index);
}
public SimpleReader(DataSet.Define define, SQLiteCommand command)
{
this.define = define;
this.command = command;
}
public static int ReadColumn(this DbDataReader reader, DataSet.Define define, DataSet.Column column, int start = 0)
{
int index = start;
foreach (var kv in define.Columns)
{
switch (kv.Value)
{
case DataSet.Type.Bool:
{
int result = reader.GetInt32(index);
if (!column.Write(kv.Key, result != 0))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Integer:
{
int result = reader.GetInt32(index);
if (!column.Write(kv.Key, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Long:
{
long result = reader.GetInt64(index);
if (!column.Write(kv.Key, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Double:
{
double result = reader.GetDouble(index);
if (!column.Write(kv.Key, result))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.String:
{
string result = reader.GetString(index);
if (!column.Write(kv.Key, result ?? ""))
{
throw new DataSet.TypeMismatchException();
}
}
break;
case DataSet.Type.Blob:
{
using (Stream stream = reader.GetStream(index))
{
int length = (int)stream.Length;
byte[] result = new byte[length];
stream.Read(result, 0, length);
if (!column.Write(kv.Key, result))
{
throw new DataSet.TypeMismatchException();
}
}
}
break;
}
++index;
}
return(index);
}