protected async Task <TableFieldDef[]> GetFieldDefs(string tableName)
{
List <TableFieldDef> fieldDefs = new List <TableFieldDef>();
string commandText = $"pragma table_info({tableName})";
using (var connection = new SqliteConnection(this.ConnectionString)) {
await connection.OpenAsync();
var command = new SqliteCommand(commandText, connection);
using (var reader = command.ExecuteReader()) {
while (await reader.ReadAsync())
{
var fieldName = ConvertValue(reader[1])?.ToString();
var fieldType = ConvertValue(reader[2])?.ToString();
Type dataType;
if (fieldType == "TEXT")
{
dataType = typeof(string);
}
else if (fieldType == "INTEGER")
{
dataType = typeof(int);
}
else if (fieldType == "REAL")
{
dataType = typeof(float);
}
else
{
throw new Exception($"Unknown field type '{fieldType}'");
}
var notNull = ConvertValue(reader[3])?.ToString() == "1";
var isPrimaryKey = ConvertValue(reader[5])?.ToString() == "1";
var fieldDef = new TableFieldDef(fieldName, dataType, -1, !notNull, isPrimaryKey && dataType == typeof(int));
fieldDefs.Add(fieldDef);
}
}
}
return(fieldDefs.ToArray());
}
private async Task <TableFieldDef[]> GetFieldDefsAsync(string tableName)
{
var sql = $@"select c.name,
st.name as 'column_type',
c.max_length,
c.is_nullable,
c.is_identity
from sys.columns c
join sys.tables t on c.object_id = t.object_id
join sys.types st on c.system_type_id = st.system_type_id
where t.name = '{tableName}'" ;
var result = await ExecuteCommandAsync <List <TableFieldDef> >(async c =>
{
var fields = new List <TableFieldDef>();
using (var reader = await c.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var field = new TableFieldDef(
reader["name"].ToString(),
SqlTypeToType(reader["column_type"].ToString()),
int.Parse(reader["max_length"].ToString()),
bool.Parse(reader["is_nullable"].ToString()),
bool.Parse(reader["is_identity"].ToString())
);
fields.Add(field);
}
}
return(fields);
}, sql);
return(result.ToArray());
}
protected static string EvaluateWhereClauseReplace(string sql, Regex regex, Dict sqlParams, StatementFromRef[] tableRefs, Func <string, string> remapOp = null)
{
StringBuilder sb = new StringBuilder();
int lastIndex = 0;
foreach (Match match in regex.Matches(sql))
{
sb.Append(sql.Substring(lastIndex, match.Groups["op"].Index - lastIndex));
string op = match.Groups["op"].Value;
sb.Append(remapOp != null ? remapOp(op) : op);
lastIndex = match.Groups["op"].Index + match.Groups["op"].Length;
sb.Append(sql.Substring(lastIndex, match.Groups["param"].Index - lastIndex));
// Get table ref
string tableAlias = match.Groups["tableAliasWithDot"].Value.Replace(".", "");
StatementFromRef tableRef;
if (string.IsNullOrEmpty(tableAlias))
{
if (tableRefs.Length > 1)
{
throw new Exception("SELECT statements with more than one table reference must use table aliases for all where clause fields");
}
tableRef = tableRefs[0];
}
else
{
tableRef = Array.Find(tableRefs, x => x.tableAlias == tableAlias);
}
// Get field defs
string fieldName = match.Groups["fieldName"].Value;
if (fieldName.Equals("NOT", StringComparison.OrdinalIgnoreCase))
{
lastIndex = match.Groups["param"].Index;
}
else
{
TableFieldDef fieldDef = tableRef.table.FindFieldDef(fieldName);
// Get evaluated value
var paramNames = match.Groups["param"].Value.Split(',').Select(x => x.Replace("@", "").Trim());
bool isFirst = true;
foreach (var paramName in paramNames)
{
object replacementValue = sqlParams[paramName];
string evaluatedValue;
if (fieldDef.type == typeof(string))
{
evaluatedValue = $"'{replacementValue}'";
}
else if (fieldDef.type == typeof(DateTime))
{
evaluatedValue = $"#{replacementValue}#";
}
else
{
evaluatedValue = $"{replacementValue}";
}
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append(',');
}
sb.Append(evaluatedValue);
}
lastIndex = match.Groups["param"].Index + match.Groups["param"].Length;
}
}
sb.Append(sql.Substring(lastIndex));
return(sb.ToString());
}
