private static void CreateTables(sqlite3 handle, List <string> tableNames, LiteWriter writer)
{
foreach (var tableName in tableNames)
{
var stmt = SQLite3.Prepare2(handle, "pragma table_info(" + tableName + ")");
try {
int offset = 0;
List <Column> columns = new List <Column>();
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
string name = SQLite3.ColumnString(stmt, 1);
string type = SQLite3.ColumnString(stmt, 2);
int notNull = SQLite3.ColumnInt(stmt, 3);
var ct = GetType(type);
var column = new Column(name, ct, offset);
columns.Add(column);
offset += LiteData.GetLength(ct);
}
var columnArray = new Column[columns.Count];
columns.CopyTo(columnArray);
var table = writer.AddTable(tableName, columnArray, offset);
AddRows(handle, table);
} finally {
SQLite3.Finalize(stmt);
}
}
}
public object GetValue(int columnIndex, Type propertyType)
{
object value = null;
if (propertyType == typeof(String) || propertyType == typeof(string))
{
value = SQLite3.ColumnString(statement, columnIndex);
}
else if (propertyType == typeof(long))
{
value = SQLite3.ColumnInt64(statement, columnIndex);
}
else if (propertyType == typeof(Single) || propertyType == typeof(Double) || propertyType == typeof(Decimal))
{
value = SQLite3.ColumnDouble(statement, columnIndex);
}
else if (propertyType == typeof(int))
{
value = SQLite3.ColumnInt(statement, columnIndex);
}
else if (propertyType == typeof(bool) || propertyType == typeof(Boolean))
{
value = (SQLite3.ColumnInt(statement, columnIndex) != 0);
}
else if (propertyType == typeof(byte[]))
{
value = SQLite3.ColumnByteArray(statement, columnIndex);
} /* else if (column.isSingleRelationship) {
* value = (V)(object)SQLite3.ColumnString(statement, columnIndex);
* }*/
return(value);
}
/// <summary>
/// execute a query</summary>
/// <returns>
/// return a JArray which contains all objects </returns>
/// <param name="query"> contains the query to execute</param>
/// <param name="param"> contains the parameters needed to execute the query</param>
public JArray execute(String query, object[] param)
{
JArray array = new JArray();
if (query != null && query.Length > 0)
{
stmt = SQLite3.Prepare2(this.Handle, query);
if (stmt != null)
{
if (param != null && param.Length > 0)
{
for (int i = 1; i <= param.Length; i++)
{
bindValue(param.GetValue(i - 1), i);
}
}
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
int count = SQLite3.ColumnCount(stmt);
JObject obj = new JObject();
for (int i = 0; i < count; i++)
{
string name = SQLite3.ColumnName(stmt, i);
SQLite3.ColType colType = SQLite3.ColumnType(stmt, i);
switch (colType)
{
case SQLite3.ColType.Blob:
byte[] bytes = SQLite3.ColumnByteArray(stmt, i);
obj.Add(name, bytes);
break;
case SQLite3.ColType.Integer:
int intValue = SQLite3.ColumnInt(stmt, i);
obj.Add(name, intValue);
break;
case SQLite3.ColType.Float:
double doubleValue = SQLite3.ColumnDouble(stmt, i);
obj.Add(name, doubleValue);
break;
case SQLite3.ColType.Text:
string text = SQLite3.ColumnString(stmt, i);
obj.Add(name, text);
break;
case SQLite3.ColType.Null:
default:
obj.Add(name, null);
break;
}
}
array.Add(obj);
}
}
SQLite3.Finalize(stmt);
}
return(array);
}
private static void AddRows(sqlite3 handle, LiteTableBuilder table)
{
var columns = table.Columns;
string query = GetTableQuery(table.Name, Array.ConvertAll(columns, c => c.Name));
var stmt = SQLite3.Prepare2(handle, query);
try {
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
var row = table.AddRow();
for (int i = 0; i < columns.Length; i++)
{
var column = columns[i];
switch (column.Type)
{
case ColumnType.Int32:
row.Write(i, SQLite3.ColumnInt(stmt, i));
break;
case ColumnType.Single:
row.Write(i, (float)SQLite3.ColumnDouble(stmt, i));
break;
case ColumnType.String:
row.Write(i, SQLite3.ColumnString(stmt, i));
break;
}
}
}
} finally {
SQLite3.Finalize(stmt);
}
}
public NSMutableDictionary GetConfigSettings()
{
NSMutableDictionary returnDictionary = new NSMutableDictionary();
IntPtr statement = IntPtr.Zero;
statement = SQLite3.Prepare2(_database, SQL_GET_SETTINGS);
int detailedLogOn = -100;
if (statement == IntPtr.Zero)
{
return(null);
}
var result = SQLite3.Step(statement);
if (result == SQLite3.Result.Row)
{
IntPtr serverHostPtr = SQLite3.ColumnText(statement, 0);
string serverHost = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(serverHostPtr);
int port = SQLite3.ColumnInt(statement, 1);
int priority = SQLite3.ColumnInt(statement, 2);
IntPtr configPtr = SQLite3.ColumnText(statement, 3);
string config = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(configPtr);
int copyPasteOn = SQLite3.ColumnInt(statement, 4);
detailedLogOn = SQLite3.ColumnInt(statement, 5);
IntPtr userEmailPtr = SQLite3.ColumnText(statement, 6);
string userEmail = System.Runtime.InteropServices.Marshal.PtrToStringAnsi(userEmailPtr);
if (!String.IsNullOrWhiteSpace(serverHost))
{
GDAppServer firstServer = new GDAppServer(serverHost, new NSNumber(port), new NSNumber(priority));
returnDictionary.Add(GDiOS.GDAppConfigKeyServers, firstServer);
}
if (!String.IsNullOrWhiteSpace(config))
{
returnDictionary.Add(GDiOS.GDAppConfigKeyConfig, new NSString(config));
}
returnDictionary.Add(GDiOS.GDAppConfigKeyCopyPasteOn, new NSNumber(copyPasteOn));
if (detailedLogOn != -100)
{
returnDictionary.Add(GDiOS.GDAppConfigKeyDetailedLogsOn, new NSNumber(detailedLogOn));
}
if (!string.IsNullOrWhiteSpace(userEmail))
{
returnDictionary.Add(GDiOS.GDAppConfigKeyUserId, new NSString(userEmail));
}
}
SQLite3.Finalize(statement);
return(returnDictionary);
}
private object[] RunSql(string sqlString)
{
SQLitePCL.sqlite3_stmt stQuery = null;
try
{
stQuery = SQLite3.Prepare2(database.DatabaseNotAsync.Handle, sqlString);
var colLenght = SQLite3.ColumnCount(stQuery);
while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
{
var obj = new object[colLenght];
for (int i = 0; i < colLenght; i++)
{
var colType = SQLite3.ColumnType(stQuery, i);
switch (colType)
{
case SQLite3.ColType.Blob:
obj[i] = SQLite3.ColumnBlob(stQuery, i);
break;
case SQLite3.ColType.Float:
obj[i] = SQLite3.ColumnDouble(stQuery, i);
break;
case SQLite3.ColType.Integer:
obj[i] = SQLite3.ColumnInt(stQuery, i);
break;
case SQLite3.ColType.Null:
obj[i] = null;
break;
case SQLite3.ColType.Text:
obj[i] = SQLite3.ColumnString(stQuery, i);
break;
}
}
return(obj);
}
return(null);
}
catch (Exception)
{
return(null);
}
finally
{
if (stQuery != null)
{
SQLite3.Finalize(stQuery);
}
}
}
public List <object[]> ExecuteCustomQuery()
{
if (SQLiteConnection.Trace)
{
Debug.WriteLine("Executing Query: " + this);
}
var stmt = Prepare();
try
{
var colLenght = SQLite3.ColumnCount(stmt);
var lstRes = new List <object[]>();
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
var colType = SQLite3.ColumnType(stmt, i);
switch (colType)
{
case SQLite3.ColType.Blob:
obj[i] = SQLite3.ColumnBlob(stmt, i);
break;
case SQLite3.ColType.Float:
obj[i] = SQLite3.ColumnDouble(stmt, i);
break;
case SQLite3.ColType.Integer:
obj[i] = SQLite3.ColumnInt(stmt, i);
break;
case SQLite3.ColType.Null:
obj[i] = null;
break;
case SQLite3.ColType.Text:
obj[i] = SQLite3.ColumnString(stmt, i);
break;
}
}
}
return(lstRes);
}
finally
{
SQLite3.Finalize(stmt);
}
}
public int Count()
{
IntPtr stmt = SQLite3.Prepare2(db, "SELECT COUNT (" + fieldlist + ") FROM " + tablename + join + " WHERE " + condition +
(limit != null ? (" LIMIT " + limit) : ""));
BindConditions(stmt);
var res = SQLite3.Step(stmt);
if (res != SQLite3.Result.Row)
{
throw new Exception("no count!");
}
return(SQLite3.ColumnInt(stmt, 1));
}
public static async Task <List <Item> > GetAllItems()
{
InitializeConnection();
return(await _connection.QueryAsyncWithPopulateAction <Item>("SELECT _id,name,type,icon_name FROM items", (row) => {
return new Item()
{
_id = SQLite3.ColumnInt(row, 0),
name = SQLite3.ColumnString(row, 1),
type = SQLite3.ColumnString(row, 2),
icon_name = SQLite3.ColumnString(row, 3),
//sub_type = SQLite3.ColumnString(row, 4)
};
}));
}
public object ReadCol(IntPtr stmt, int index, Type clrType)
{
var type = SQLite3.ColumnType(stmt, index);
if (type == SQLite3.ColType.Null)
{
return(null);
}
if (clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
{
return(Convert.ChangeType(SQLite3.ColumnInt(stmt, index), clrType));
}
if (clrType == typeof(UInt32) || clrType == typeof(Int64))
{
return(Convert.ChangeType(SQLite3.ColumnInt64(stmt, index), clrType));
}
if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal))
{
return(Convert.ChangeType(SQLite3.ColumnDouble(stmt, index), clrType));
}
if (clrType == typeof(String))
{
return(Convert.ChangeType(SQLite3.ColumnString(stmt, index), clrType));
}
if (clrType == typeof(byte[]))
{
return(SQLite3.ColumnByteArray(stmt, index));
}
if (clrType == typeof(DateTime))
{
return(ToDateTime(SQLite3.ColumnString(stmt, index)));
}
if (clrType == typeof(Boolean))
{
return(ToBoolean(SQLite3.ColumnString(stmt, index)));
}
throw new NotSupportedException("Don't know how to read " + clrType);
}
public bool SettingsAlreadyStored()
{
IntPtr statement = IntPtr.Zero;
int count = 0;
statement = SQLite3.Prepare2(_database, SQL_COUNT);
if (statement == IntPtr.Zero)
{
return(false);
}
var result = SQLite3.Step(statement);
if (result == SQLite3.Result.Row)
{
count = SQLite3.ColumnInt(statement, 0);
}
SQLite3.Finalize(statement);
statement = IntPtr.Zero;
return(count > 0);
}
private List <object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
{
var lstRes = new List <object[]>();
SQLitePCL.sqlite3_stmt stQuery = null;
try
{
stQuery = SQLite3.Prepare2(App.Database.DatabaseNotAsync.Handle, sqlString);
var colLenght = SQLite3.ColumnCount(stQuery);
if (includeColumnNamesAsFirstRow)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
obj[i] = SQLite3.ColumnName(stQuery, i);
}
}
while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
var colType = SQLite3.ColumnType(stQuery, i);
switch (colType)
{
case SQLite3.ColType.Blob:
obj[i] = SQLite3.ColumnBlob(stQuery, i);
break;
case SQLite3.ColType.Float:
obj[i] = SQLite3.ColumnDouble(stQuery, i);
break;
case SQLite3.ColType.Integer:
obj[i] = SQLite3.ColumnInt(stQuery, i);
break;
case SQLite3.ColType.Null:
obj[i] = null;
break;
case SQLite3.ColType.Text:
obj[i] = SQLite3.ColumnString(stQuery, i);
break;
}
}
}
return(lstRes);
}
catch (Exception)
{
return(null);
}
finally
{
if (stQuery != null)
{
SQLite3.Finalize(stQuery);
}
}
}
public int GetInt32(int i)
{
return(SQLite3.ColumnInt(_vd, i));
}
private object ReadCol(Sqlite3Statement stmt, int index, SQLite3.ColType type, Type clrType)
{
if (type == SQLite3.ColType.Null)
{
return(null);
}
if (clrType == typeof(string))
{
return(SQLite3.ColumnString(stmt, index));
}
if (clrType == typeof(int))
{
return(SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(bool))
{
return(SQLite3.ColumnInt(stmt, index) == 1);
}
if (clrType == typeof(double))
{
return(SQLite3.ColumnDouble(stmt, index));
}
if (clrType == typeof(float))
{
return((float)SQLite3.ColumnDouble(stmt, index));
}
if (clrType == typeof(TimeSpan))
{
return(new TimeSpan(SQLite3.ColumnInt64(stmt, index)));
}
if (clrType == typeof(DateTime))
{
if (_conn.StoreDateTimeAsTicks)
{
return(new DateTime(SQLite3.ColumnInt64(stmt, index)));
}
string text = SQLite3.ColumnString(stmt, index);
return(DateTime.Parse(text));
}
if (clrType == typeof(DateTimeOffset))
{
return(new DateTimeOffset(SQLite3.ColumnInt64(stmt, index), TimeSpan.Zero));
}
if (clrType.IsEnum)
{
return(SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(long))
{
return(SQLite3.ColumnInt64(stmt, index));
}
if (clrType == typeof(uint))
{
return((uint)SQLite3.ColumnInt64(stmt, index));
}
if (clrType == typeof(decimal))
{
return((decimal)SQLite3.ColumnDouble(stmt, index));
}
if (clrType == typeof(byte))
{
return((byte)SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(ushort))
{
return((ushort)SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(short))
{
return((short)SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(sbyte))
{
return((sbyte)SQLite3.ColumnInt(stmt, index));
}
if (clrType == typeof(byte[]))
{
return(SQLite3.ColumnByteArray(stmt, index));
}
if (clrType == typeof(Guid))
{
string text = SQLite3.ColumnString(stmt, index);
return(new Guid(text));
}
throw new NotSupportedException("Don't know how to read " + clrType);
}
private static object ReadCol(SQLiteConnection connection, sqlite3_stmt stmt, int index, SQLite3.ColType type, Type clrType)
{
if (type == SQLite3.ColType.Null)
{
return(null);
}
else
{
if (clrType == typeof(String))
{
return(SQLite3.ColumnString(stmt, index));
}
else if (clrType == typeof(Int32))
{
return((int)SQLite3.ColumnInt(stmt, index));
}
else if (clrType == typeof(Boolean))
{
return(SQLite3.ColumnInt(stmt, index) == 1);
}
else if (clrType == typeof(double))
{
return(SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(float))
{
return((float)SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(TimeSpan))
{
return(new TimeSpan(SQLite3.ColumnInt64(stmt, index)));
}
else if (clrType == typeof(DateTime))
{
if (connection.StoreDateTimeAsTicks)
{
return(new DateTime(SQLite3.ColumnInt64(stmt, index)));
}
else
{
string text = SQLite3.ColumnString(stmt, index);
DateTime resultDate;
if (!DateTime.TryParseExact(text, DateTimeExactStoreFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out resultDate))
{
resultDate = DateTime.Parse(text);
}
return(resultDate);
}
}
// else if (clrType == typeof(DateTimeOffset))
// {
// return new DateTimeOffset(SQLite3.ColumnInt64(stmt, index), TimeSpan.Zero);
//#if !USE_NEW_REFLECTION_API
// }
// else if (clrType.IsEnum)
// {
//#else
// } else if (clrType.GetTypeInfo().IsEnum) {
//#endif
// if (type == SQLite3.ColType.Text)
// {
// var value = SQLite3.ColumnString(stmt, index);
// return Enum.Parse(clrType, value.ToString(), true);
// }
// else
// return SQLite3.ColumnInt(stmt, index);
// }
else if (clrType == typeof(Int64))
{
return(SQLite3.ColumnInt64(stmt, index));
}
else if (clrType == typeof(UInt32))
{
return((uint)SQLite3.ColumnInt64(stmt, index));
}
else if (clrType == typeof(decimal))
{
return((decimal)SQLite3.ColumnDouble(stmt, index));
}
else if (clrType == typeof(Byte))
{
return((byte)SQLite3.ColumnInt(stmt, index));
}
else if (clrType == typeof(UInt16))
{
return((ushort)SQLite3.ColumnInt(stmt, index));
}
else if (clrType == typeof(Int16))
{
return((short)SQLite3.ColumnInt(stmt, index));
}
else if (clrType == typeof(sbyte))
{
return((sbyte)SQLite3.ColumnInt(stmt, index));
}
else if (clrType == typeof(byte[]))
{
return(SQLite3.ColumnByteArray(stmt, index));
}
else if (clrType == typeof(Guid))
{
string text = SQLite3.ColumnString(stmt, index);
return(new Guid(text));
}
else
{
throw new NotSupportedException("Don't know how to read " + clrType);
}
}
}
