/// <summary>
/// Return a list of table names
/// </summary>
public string[] GetTableNames()
{
string tableSQL = "SELECT name FROM sqlite_master WHERE type='table'";
List <string> tables = new List <string>();
var statement = SQLite3.Prepare2(connection.Handle, tableSQL);
try
{
bool done = false;
while (!done)
{
SQLite3.Result result = SQLite3.Step(statement);
if (result == SQLite3.Result.Row)
{
var tableName = SQLite3.ColumnString(statement, 0);
tables.Add(tableName);
}
else if (result == SQLite3.Result.Done)
{
done = true;
}
else
{
throw SQLiteException.New(result, SQLite3.GetErrmsg(connection.Handle));
}
}
}
finally
{
SQLite3.Finalize(statement);
}
return(tables.ToArray());
}
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);
}
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 static List <string> Tables(this SQLiteConnection connection)
{
const string GET_TABLES_QUERY = "SELECT NAME from sqlite_master";
List <string> tables = new List <string> ();
var statement = SQLite3.Prepare2(connection.Handle, GET_TABLES_QUERY);
try {
bool done = false;
while (!done)
{
SQLite3.Result result = SQLite3.Step(statement);
if (result == SQLite3.Result.Row)
{
var tableName = SQLite3.ColumnString(statement, 0);
tables.Add(tableName);
}
else if (result == SQLite3.Result.Done)
{
done = true;
}
else
{
throw SQLiteException.New(result, SQLite3.GetErrmsg(connection.Handle));
}
}
}
finally {
SQLite3.Finalize(statement);
}
return(tables);
}
/// <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);
}
public static IDictionary <string, object> ParseSQLiteLinha(this SQLitePCL.sqlite3_stmt stQuery, string[] colunas)
{
var linha = new Dictionary <string, object>();
for (int i = 0; i < colunas.Length; i++)
{
var coluna = colunas[i];
var tipoColuna = SQLite3.ColumnType(stQuery, i);
switch (tipoColuna)
{
case SQLite3.ColType.Blob:
linha.Add(coluna, SQLite3.ColumnBlob(stQuery, i));
break;
case SQLite3.ColType.Float:
linha.Add(coluna, SQLite3.ColumnDouble(stQuery, i));
break;
case SQLite3.ColType.Integer:
linha.Add(coluna, SQLite3.ColumnInt(stQuery, i));
break;
case SQLite3.ColType.Null:
linha.Add(coluna, null);
break;
case SQLite3.ColType.Text:
linha.Add(coluna, SQLite3.ColumnString(stQuery, i));
break;
}
}
return(linha);
}
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 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)
};
}));
}
private static void GetTableNames(sqlite3 handle, List <string> tableNames)
{
var stmt = SQLite3.Prepare2(handle, "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name");
try {
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
string name = SQLite3.ColumnString(stmt, 0);
if (name != "sqlite_sequence")
{
tableNames.Add(name);
}
}
} finally {
SQLite3.Finalize(stmt);
}
}
public static IEnumerable <string> GetTableNames()
{
lock (_locker)
{
var names = new List <string>();
var query = string.Format("select name from sqlite_master where type='table' and name not like 'sqlite_%'"); // get all table names except sqlite system tables
var statement = SQLite3.Prepare2(_db.Handle, query);
while (SQLite3.Step(statement) == SQLite3.Result.Row)
{
var name = SQLite3.ColumnString(statement, 0);
names.Add(name);
}
SQLite3.Finalize(statement);
return(names);
}
}
private object ReadCol(SQLitePCL.sqlite3_stmt stmt, int index, SQLite3.ColType type)
{
switch (type)
{
case SQLite3.ColType.Blob:
return(SQLite3.ColumnByteArray(stmt, index));
case SQLite3.ColType.Float:
return(SQLite3.ColumnDouble(stmt, index));
case SQLite3.ColType.Integer:
return(SQLite3.ColumnInt64(stmt, index));
case SQLite3.ColType.Null:
return(null);
case SQLite3.ColType.Text:
return(SQLite3.ColumnString(stmt, index));
}
return(null);
}
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 Object ExecuteScalar()
{
Object retObj = null;
var stmt = Prepare();
var cols = new System.Reflection.PropertyInfo[SQLite3.ColumnCount(stmt)];
if (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
if (cols.Length > 0)
{
var type = SQLite3.ColumnType(stmt, 0);
switch (type)
{
case SQLite3.ColType.Integer:
retObj = SQLite3.ColumnInt64(stmt, 0);
break;
case SQLite3.ColType.Float:
retObj = SQLite3.ColumnDouble(stmt, 0);
break;
case SQLite3.ColType.Blob:
retObj = SQLite3.ColumnByteArray(stmt, 0);
break;
case SQLite3.ColType.Text:
retObj = SQLite3.ColumnString(stmt, 0);
break;
case SQLite3.ColType.Null:
break;
}
}
}
SQLite3.Finalize(stmt);
return(retObj);
}
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);
}
public string GetString(int i)
{
return(SQLite3.ColumnString(_vd, i));
}
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);
}
}
}
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 bool selectAllInto(JArray results, string query)
{
if (_connection != null)
{
try
{
var cmd = _connection.CreateCommand(query);
var stmt = SQLite3.Prepare2(_connection.Handle, cmd.CommandText);
try
{
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
int colCount = SQLite3.ColumnCount(stmt);
if (colCount <= 0)
{
return(false);
}
JObject jsonObj = new JObject();
for (int i = 0; i < colCount; i++)
{
string columnName = SQLite3.ColumnName16(stmt, i);
if (columnName.Equals(FIELD_JSON))
{
MemoryStream ms = new MemoryStream(SQLite3.ColumnByteArray(stmt, i));
using (BsonReader reader = new BsonReader(ms))
{
jsonObj.Add(FIELD_JSON, JToken.ReadFrom(reader));
}
}
else if (columnName.Equals(FIELD_ID))
{
jsonObj.Add(FIELD_ID, Int32.Parse(SQLite3.ColumnString(stmt, i)));
}
else
{
if (isJSONCreatedColumn(columnName))
{
jsonObj.Add(columnName, SQLite3.ColumnString(stmt, i));
}
else
{
jsonObj.Add(columnName.Replace('_', '.'), SQLite3.ColumnString(stmt, i));
}
}
}
results.Add(jsonObj);
}
}
finally
{
SQLite3.Finalize(stmt);
}
return(true);
}
catch (Exception e)
{
lastErrorMsg = e.ToString();
}
}
return(false);
}