public static string SqlType(TableMapping.Column p)
{
var clrType = p.ColumnType;
if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
{
return("integer");
}
if (clrType == typeof(UInt32) || clrType == typeof(Int64))
{
return("bigint");
}
if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
{
return("float");
}
if (clrType == typeof(String))
{
int len = p.MaxStringLength;
return("varchar(" + len + ")");
}
if (clrType == typeof(DateTimeOffset))
{
return("datetime");
}
if (clrType == typeof(DateTime))
{
return("datetime");
#if !NETFX_CORE
}
if (clrType.IsEnum)
{
#else
}
if (clrType.GetTypeInfo().IsEnum)
{
#endif
return("integer");
}
if (clrType == typeof(TimeSpan))
{
return("bigint");
}
if (clrType == typeof(byte[]))
{
return("blob");
}
if (clrType == typeof(Guid))
{
return("varchar(36)");
}
throw new NotSupportedException("Don't know about " + clrType);
}
public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks)
{
string decl = "\"" + p.Name + "\" " + SqlType(p) + " ";
if (p.IsPK)
{
decl += "primary key ";
}
if (p.IsAutoInc)
{
decl += "autoincrement ";
}
if (!p.IsNullable)
{
decl += "not null ";
}
if (!string.IsNullOrEmpty(p.Collation))
{
decl += "collate " + p.Collation + " ";
}
return(decl);
}
/// <summary>
/// Get all changes fro SQLite Database
/// </summary>
/// <param name="schema">All Tables</param>
/// <param name="lastModifiedDate">Changes since this date</param>
internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate)
{
List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>();
using (SQLiteConnection connection = new SQLiteConnection(localFilePath))
{
try
{
foreach (var ty in schema.Collections)
{
// Get mapping from my type
var map = manager.GetMapping(ty);
// Create query to select changes
var querySelect = SQLiteConstants.SelectChanges;
var columnsDcl = new List <String>();
var columnsPK = new List <String>();
// Foreach columns, create the tsql command to execute
foreach (var c in map.Columns)
{
if (!c.IsPK)
{
columnsDcl.Add("[s].[" + c.Name + "]");
}
// If it's the PK, add it from Tracking (because of deleted items not in real table
if (c.IsPK)
{
columnsDcl.Add("[t].[" + c.Name + "]");
columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]");
}
}
var decl = string.Join(",\n", columnsDcl.ToArray());
var pk = string.Join(" \nAND ", columnsPK.ToArray());
querySelect = String.Format(querySelect, map.TableName, pk, decl);
// Prepare command
using (var stmt = connection.Prepare(querySelect))
{
try
{
// Set Values
BindParameter(stmt, 1, lastModifiedDate);
// Get mapping form the statement
var cols = new TableMapping.Column[map.Columns.Length];
// Foreach column, get the property in my object
for (int i = 0; i < cols.Length; i++)
{
var name = stmt.ColumnName(i);
var c = map.FindColumn(name);
if (c != null)
{
cols[i] = map.FindColumn(name);
}
}
// While row is available
//while (await stmt.StepAsync().AsTask().ConfigureAwait(false))
while (stmt.Step() == SQLiteResult.ROW)
{
// Create the object
SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType);
for (int i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
{
continue;
}
// Read the column
var val = ReadCol(stmt, i, cols[i].ColumnType);
// Set the value
cols[i].SetValue(obj, val);
}
// Read the Oem Properties
var newIndex = map.Columns.Count();
obj.ServiceMetadata = new OfflineEntityMetadata();
obj.ServiceMetadata.IsTombstone = (Boolean)ReadCol(stmt, newIndex, typeof(Boolean));
obj.ServiceMetadata.Id = (String)ReadCol(stmt, newIndex + 1, typeof(String));
obj.ServiceMetadata.ETag = (String)ReadCol(stmt, newIndex + 2, typeof(String));
String absoluteUri = (String)ReadCol(stmt, newIndex + 3, typeof(String));
obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri);
lstChanges.Add(obj);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
stmt.Reset();
stmt.ClearBindings();
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
return(lstChanges);
}
/// <summary>
/// Get all changes fro SQLite Database
/// </summary>
/// <param name="schema">All Tables</param>
/// <param name="lastModifiedDate">Changes since this date</param>
/// <param name="uploadBatchSize">Maximum number of rows to upload</param>
internal IEnumerable <SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate, int uploadBatchSize)
{
List <SQLiteOfflineEntity> lstChanges = new List <SQLiteOfflineEntity>();
using (SQLiteDatabaseConnection connection = SQLitePCL.pretty.SQLite3.Open(localFilePath))
{
try
{
foreach (var ty in schema.Collections)
{
// Get mapping from my type
var map = manager.GetMapping(ty);
// Create query to select changes
var querySelect = SQLiteConstants.SelectChanges;
var columnsDcl = new List <String>();
var columnsPK = new List <String>();
// Foreach columns, create the tsql command to execute
foreach (var c in map.Columns)
{
if (!c.IsPK)
{
columnsDcl.Add("[s].[" + c.Name + "]");
}
// If it's the PK, add it from Tracking (because of deleted items not in real table
if (c.IsPK)
{
columnsDcl.Add("[t].[" + c.Name + "]");
columnsPK.Add("[s].[" + c.Name + "] = [t].[" + c.Name + "]");
}
}
var decl = string.Join(",\n", columnsDcl.ToArray());
var pk = string.Join(" \nAND ", columnsPK.ToArray());
querySelect = String.Format(querySelect, map.TableName, pk, decl);
// add limit if specified
if (uploadBatchSize > 0)
{
querySelect += $" LIMIT {uploadBatchSize}";
}
try
{
// Get mapping form the statement
var cols = new TableMapping.Column[map.Columns.Length];
bool firstRow = true;
// While row is available
foreach (var row in connection.Query(querySelect, P(lastModifiedDate)))
{
if (firstRow)
{
// Foreach column, get the property in my object
for (int i = 0; i < cols.Length; i++)
{
var name = row[i].ColumnInfo.Name;
var c = map.FindColumn(name);
if (c != null)
{
cols[i] = map.FindColumn(name);
}
}
firstRow = false;
}
// Create the object
SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType);
for (int i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
{
continue;
}
// Read the column
var val = ReadCol(row, i, cols[i].ColumnType);
// Set the value
cols[i].SetValue(obj, val);
}
// Read the Oem Properties
var newIndex = map.Columns.Count();
obj.ServiceMetadata = new OfflineEntityMetadata();
obj.ServiceMetadata.IsTombstone = (bool)ReadCol(row, newIndex, typeof(Boolean));
obj.ServiceMetadata.Id = (String)ReadCol(row, newIndex + 1, typeof(String));
obj.ServiceMetadata.ETag = (String)ReadCol(row, newIndex + 2, typeof(String));
String absoluteUri = (String)ReadCol(row, newIndex + 3, typeof(String));
obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri);
lstChanges.Add(obj);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
// if we are batching uploads and the upload rowcount has been reached, skip
if (uploadBatchSize > 0 && lstChanges.Count >= uploadBatchSize)
{
break;
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
// if we are batching uploads, limit the in-memory result set as well
if (uploadBatchSize > 0)
{
return(lstChanges.Take(uploadBatchSize));
}
return(lstChanges);
}
