private void FormatCountQuery()
{
string tableName = SqlHelpers.FormatTableName(this.query.TableName);
this.sql.AppendFormat("SELECT COUNT(1) AS [count] FROM {0}", tableName);
if (this.query.Filter != null)
{
this.FormatWhereClause(this.query.Filter);
}
}
private void BatchDelete(string tableName, IEnumerable <string> ids)
{
var parameters = ids
.Select((value, index) => (value, index))
.ToDictionary(pair => "@id" + pair.index, pair => (object)pair.value);
string sql = string.Format("DELETE FROM {0} WHERE {1} IN ({2})",
SqlHelpers.FormatTableName(tableName),
MobileServiceSystemColumns.Id,
string.Join(",", parameters.Keys));
this.ExecuteNonQueryInternal(sql, parameters);
}
public string FormatDelete()
{
var delQuery = this.query.Clone(); // create a copy to avoid modifying the original
delQuery.Selection.Clear();
delQuery.Selection.Add(MobileServiceSystemColumns.Id);
delQuery.IncludeTotalCount = false;
var formatter = new SqlQueryFormatter(delQuery);
string selectIdQuery = formatter.FormatSelect();
string idMemberName = SqlHelpers.FormatMember(MobileServiceSystemColumns.Id);
string tableName = SqlHelpers.FormatTableName(delQuery.TableName);
string command = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", tableName, idMemberName, selectIdQuery);
this.Parameters = formatter.Parameters;
return(command);
}
/// <summary>
/// Deletes items from local table with the given list of ids
/// </summary>
/// <param name="tableName">Name of the local table.</param>
/// <param name="ids">A list of ids of the items to be deleted</param>
/// <returns>A task that completes when delete query has executed.</returns>
public override Task DeleteAsync(string tableName, IEnumerable <string> ids)
{
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (ids == null)
{
throw new ArgumentNullException("ids");
}
this.EnsureInitialized();
string idRange = String.Join(",", ids.Select((_, i) => "@id" + i));
string sql = string.Format("DELETE FROM {0} WHERE {1} IN ({2})",
SqlHelpers.FormatTableName(tableName),
MobileServiceSystemColumns.Id,
idRange);
var parameters = new Dictionary <string, object>();
int j = 0;
foreach (string id in ids)
{
parameters.Add("@id" + (j++), id);
}
return(this.operationSemaphore.WaitAsync()
.ContinueWith(t =>
{
try
{
this.ExecuteNonQueryInternal(sql, parameters);
}
finally
{
this.operationSemaphore.Release();
}
}));
}
private JObject ReadRow(TableDefinition table, SqliteDataReader reader)
{
JObject row = new JObject();
for (int i = 0; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
string name = reader.GetName(i);
object value = new object();
if (reader.GetDataTypeName(i) == "DATETIME")
{
try
{
value = reader.GetDouble(reader.GetOrdinal(name));
}
catch (FormatException ex)
{
value = reader.GetInt64(reader.GetOrdinal(name));
}
}
else
{
value = reader.GetValue(i);
}
ColumnDefinition column;
if (table.TryGetValue(name, out column))
{
JToken jVal = SqlHelpers.DeserializeValue(value, column.StoreType, column.JsonType);
row[name] = jVal;
}
else
{
row[name] = value == null ? null : JToken.FromObject(value);
}
}
}
return(row);
}
public override QueryNode Visit(ConvertNode nodeIn)
{
this.sql.Append("CAST(");
QueryNode source = nodeIn.Source.Accept(this);
this.sql.Append(" AS ");
string sqlType = SqlHelpers.GetStoreCastType(nodeIn.TargetType);
this.sql.Append(sqlType);
this.sql.Append(")");
if (source != nodeIn.Source)
{
return(new ConvertNode(source, nodeIn.TargetType));
}
return(nodeIn);
}
private JObject ReadRow(TableDefinition table, sqlite3_stmt statement)
{
var row = new JObject();
for (int i = 0; i < raw.sqlite3_column_count(statement); i++)
{
string name = raw.sqlite3_column_name(statement, i).utf8_to_string();
object value = SQLitePCLRawHelpers.GetValue(statement, i);
if (table.TryGetValue(name, out ColumnDefinition column))
{
JToken jVal = SqlHelpers.DeserializeValue(value, column.StoreType, column.JsonType);
row[name] = jVal;
}
else
{
row[name] = value == null ? null : JToken.FromObject(value);
}
}
return(row);
}
private void BatchUpdate(string tableName, IEnumerable <JObject> items, List <ColumnDefinition> columns)
{
if (columns.Count <= 1)
{
return; // For update to work there has to be at least once column besides Id that needs to be updated
}
ValidateParameterCount(columns.Count);
string sqlBase = String.Format("UPDATE {0} SET ", SqlHelpers.FormatTableName(tableName));
foreach (JObject item in items)
{
var sql = new StringBuilder(sqlBase);
var parameters = new Dictionary <string, object>();
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
if (idColumn == null)
{
continue;
}
foreach (var column in columns.Where(c => c != idColumn))
{
string paramName = AddParameter(item, parameters, column);
sql.AppendFormat("{0} = {1}", SqlHelpers.FormatMember(column.Name), paramName);
sql.Append(",");
}
if (parameters.Any())
{
sql.Remove(sql.Length - 1, 1); // remove the trailing comma
}
sql.AppendFormat(" WHERE {0} = {1}", SqlHelpers.FormatMember(MobileServiceSystemColumns.Id), AddParameter(item, parameters, idColumn));
this.ExecuteNonQuery(sql.ToString(), parameters);
}
}
private JObject ReadRow(TableDefinition table, ISQLiteStatement statement)
{
var row = new JObject();
for (int i = 0; i < statement.ColumnCount; i++)
{
string name = statement.ColumnName(i);
object value = statement[i];
ColumnDefinition column;
if (table.TryGetValue(name, out column))
{
JToken jVal = SqlHelpers.DeserializeValue(value, column.StoreType, column.JsonType);
row[name] = jVal;
}
else
{
row[name] = value == null ? null : JToken.FromObject(value);
}
}
return(row);
}
public static JToken DeserializeValue(object value, string storeType, JTokenType columnType)
{
if (value == null)
{
return(null);
}
if (IsTextType(storeType))
{
return(SqlHelpers.ParseText(columnType, value));
}
if (IsRealType(storeType))
{
return(SqlHelpers.ParseReal(columnType, value));
}
if (IsNumberType(storeType))
{
return(SqlHelpers.ParseNumber(columnType, value));
}
return(null);
}
/// <summary>
/// Executes a lookup against a local table.
/// </summary>
/// <param name="tableName">Name of the local table.</param>
/// <param name="id">The id of the item to lookup.</param>
/// <returns>A task that will return with a result when the lookup finishes.</returns>
public override Task <JObject> LookupAsync(string tableName, string id)
{
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (id == null)
{
throw new ArgumentNullException("id");
}
this.EnsureInitialized();
string sql = string.Format("SELECT * FROM {0} WHERE {1} = @id", SqlHelpers.FormatTableName(tableName), MobileServiceSystemColumns.Id);
var parameters = new Dictionary <string, object>
{
{ "@id", id }
};
IList <JObject> results = this.ExecuteQuery(tableName, sql, parameters);
return(Task.FromResult(results.FirstOrDefault()));
}
internal virtual void CreateTableFromObject(string tableName, IEnumerable <ColumnDefinition> columns)
{
ColumnDefinition idColumn = columns.FirstOrDefault(c => c.Name.Equals(MobileServiceSystemColumns.Id));
var colDefinitions = columns.Where(c => c != idColumn).Select(c => String.Format("{0} {1}", SqlHelpers.FormatMember(c.Name), c.StoreType)).ToList();
if (idColumn != null)
{
colDefinitions.Insert(0, String.Format("{0} {1} PRIMARY KEY", SqlHelpers.FormatMember(idColumn.Name), idColumn.StoreType));
}
String tblSql = string.Format("CREATE TABLE IF NOT EXISTS {0} ({1})", SqlHelpers.FormatTableName(tableName), String.Join(", ", colDefinitions));
this.ExecuteNonQuery(tblSql, parameters: null);
string infoSql = string.Format("PRAGMA table_info({0});", SqlHelpers.FormatTableName(tableName));
IDictionary <string, JObject> existingColumns = this.ExecuteQuery((TableDefinition)null, infoSql, parameters: null)
.ToDictionary(c => c.Value <string>("name"), StringComparer.OrdinalIgnoreCase);
// new columns that do not exist in existing columns
var columnsToCreate = columns.Where(c => !existingColumns.ContainsKey(c.Name));
foreach (ColumnDefinition column in columnsToCreate)
{
string createSql = string.Format("ALTER TABLE {0} ADD COLUMN {1} {2}",
SqlHelpers.FormatTableName(tableName),
SqlHelpers.FormatMember(column.Name),
column.StoreType);
this.ExecuteNonQuery(createSql, parameters: null);
}
// NOTE: In SQLite you cannot drop columns, only add them.
}
public string FormatSelect()
{
var command = new StringBuilder("SELECT ");
if (this.query.Selection.Any())
{
string columnNames = String.Join(", ", this.query.Selection.Select(c => SqlHelpers.FormatMember(c)));
command.Append(columnNames);
}
else
{
command.Append("*");
}
return(FormatQuery(command.ToString()));
}
public static object SerializeValue(JValue value, bool allowNull)
{
string storeType = SqlHelpers.GetStoreType(value.Type, allowNull);
return(SerializeValue(value, storeType, value.Type));
}