void createMigrationsTable(SQLiteDatabaseConnection db)
{
var ceate_sql = "CREATE TABLE __migrations ( id INT NOT NULL )";
db.Execute(ceate_sql);
var insert_sql = "INSERT INTO __migrations ( id ) VALUES ( 0 )";
db.Execute(insert_sql);
}
/// <summary>
/// Executes a SQL Script that doesn't retun rows
/// </summary>
/// <param name="command">
/// A list of SQL scripts to be executed
/// </param>
/// <returns>
/// An int indicating the number of affected rows
/// </returns>
public override IEnumerable <int> Execute(IEnumerable <Command> commands)
{
//Local Vars
Command current = new Command();
//Validating that exists at least one script
if (commands == null)
{
throw new ArgumentNullException("commands");
}
lock (Locker)
{
try
{
//Initializing command and connection
OpenConnection();
//Crossing the array of scripts
foreach (Command command in commands)
{
//raising events
CommandEventArgs e = new CommandEventArgs(command);
OnBeforeExecute(e);
current = command;
//Executing the code only if have authorization
if (!e.Cancel)
{
object[] parameters = new object[command.Parameters.Count];
for (int i = 0; i < command.Parameters.Count; i++)
{
parameters[i] = command.Parameters[i].Value;
}
//Executing command and getting the number of affected rows
Connection.Execute(command.Script, parameters);
}
//calling events
OnAfterExecute(e);
}
}
catch (System.Exception ex)
{
//Re throwing exception to the caller
throw new SqlException(current, "Error on executing command against the database (" + ex.Message + ")", ex);
}
finally
{
//Closing the connection
CloseConnection();
}
yield return(-1);
}
}
protected ManagedConnection GetConnection(bool _ = false)
{
WriteLock.Wait();
if (WriteConnection != null)
{
return(new ManagedConnection(WriteConnection, WriteLock));
}
WriteConnection = SQLite3.Open(
DbFilePath,
DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite,
null);
if (CacheSize.HasValue)
{
WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value);
}
if (!string.IsNullOrWhiteSpace(JournalMode))
{
WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode);
}
if (Synchronous.HasValue)
{
WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value);
}
if (PageSize.HasValue)
{
WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value);
}
WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore);
return(new ManagedConnection(WriteConnection, WriteLock));
}
public void Execute(string sql, params object[] values)
{
db.Execute(sql, values);
}
public static void ClassInit(TestContext testContext)
{
SQLitePCL.Batteries.Init();
SQLiteDatabaseConnectionBuilder dbbuilder = SQLiteDatabaseConnectionBuilder.InMemory;
db = PrettyFn.Init(dbbuilder);
db.Execute("CREATE TABLE test (vals text, type text);");
db.Execute("INSERT INTO test VALUES ('" + int.MaxValue + "', 'int');");
db.Execute("INSERT INTO test VALUES ('" + uint.MaxValue + "', 'uint');");
db.Execute("INSERT INTO test VALUES ('" + long.MaxValue + "', 'long');");
db.Execute("INSERT INTO test VALUES ('" + ulong.MaxValue + "', 'ulong');");
db.Execute("INSERT INTO test VALUES ('" + byte.MaxValue + "', 'byte');");
db.Execute("INSERT INTO test VALUES ('" + sbyte.MaxValue + "', 'sbyte');");
db.Execute("INSERT INTO test VALUES ('" + short.MaxValue + "', 'short');");
db.Execute("INSERT INTO test VALUES ('" + ushort.MaxValue + "', 'ushort');");
db.Execute("INSERT INTO test VALUES ('" + float.MaxValue + "', 'float');");
db.Execute("INSERT INTO test VALUES ('1.7976931348623157E+308', 'double');");
db.Execute("INSERT INTO test VALUES ('" + decimal.MaxValue + "', 'decimal');");
db.Execute("INSERT INTO test VALUES ('" + char.MaxValue + "', 'char');");
db.Execute("INSERT INTO test VALUES ('P1Y2MT2H', 'isotimespan');");
db.Execute("INSERT INTO test VALUES ('02:14:18', 'dotnettimespan');");
db.Execute("INSERT INTO test VALUES (UUID(), 'guid');");
db.Execute("INSERT INTO test VALUES ('0', 'nonpositiveint');");
db.Execute("INSERT INTO test VALUES ('0', 'nonnegativeint');");
db.Execute("INSERT INTO test VALUES ('1', 'positiveint');");
db.Execute("INSERT INTO test VALUES ('-1', 'negativeint');");
db.Execute("INSERT INTO test VALUES ('true', 'bool');");
db.Execute("INSERT INTO test VALUES ('https', 'uri');");
db.Execute("INSERT INTO test VALUES ('a', 'rownum');");
db.Execute("INSERT INTO test VALUES ('b', 'rownum');");
db.Execute("INSERT INTO test VALUES ('c', 'rownum');");
}
/// <summary>
/// Create a OfflineEntity Table with metadata information added
/// </summary>
internal void CreateTable(Type ty)
{
using (SQLiteDatabaseConnection connection = SQLitePCL.pretty.SQLite3.Open(localFilePath))
{
try
{
// Get mapping from my type
var map = manager.GetMapping(ty);
// Create 2 tables : One for datas, one for tracking
var query = SQLiteConstants.CreateTable;
var queryTracking = SQLiteConstants.CreateTrackingTable;
var columnsDcl = new List <String>();
var columnsDclTracking = new List <String>();
var columnsPk = new List <String>();
// Foreach columns, create the tsql command to execute
foreach (var c in map.Columns)
{
string dec = "\"" + c.Name + "\" " + Orm.SqlType(c) + " ";
columnsDcl.Add(dec);
// If it's the PK, add it to tracking
if (!c.IsPK)
{
continue;
}
columnsDclTracking.Add(dec);
columnsPk.Add(c.Name + " ");
}
var pkTracking = string.Join(",\n", columnsPk.ToArray());
// Adding metadatas to tracking table
columnsDclTracking.AddRange(GetOfflineEntityMetadataSQlDecl());
var decl = string.Join(",\n", columnsDcl.ToArray());
var declTracking = string.Join(",\n", columnsDclTracking.ToArray());
string pKeyDecl = String.Empty;
if (columnsDclTracking.Count > 0)
{
pKeyDecl = String.Format(",\n PRIMARY KEY ({0})", pkTracking);
}
query = String.Format(query, map.TableName, decl, pKeyDecl);
queryTracking = String.Format(queryTracking, map.TableName, declTracking, pKeyDecl);
connection.Execute(query);
connection.Execute(queryTracking);
var indexes = new Dictionary <string, IndexInfo>();
foreach (var c in map.Columns)
{
foreach (var i in c.Indices)
{
var iname = i.Name ?? map.TableName + "_" + c.Name;
IndexInfo iinfo;
if (!indexes.TryGetValue(iname, out iinfo))
{
iinfo = new IndexInfo
{
//IndexName = iname,
TableName = map.TableName,
Unique = i.Unique,
Columns = new List <IndexedColumn>()
};
indexes.Add(iname, iinfo);
}
if (i.Unique != iinfo.Unique)
{
throw new Exception(
"All the columns in an index must have the same value for their Unique property");
}
iinfo.Columns.Add(new IndexedColumn
{
Order = i.Order,
ColumnName = c.Name
});
}
}
foreach (var indexName in indexes.Keys)
{
var index = indexes[indexName];
const string sqlFormat = "create {3} index if not exists \"{0}\" on \"{1}\"(\"{2}\")";
var columns = String.Join("\",\"",
index.Columns.OrderBy(i => i.Order).Select(i => i.ColumnName).ToArray());
var sql = String.Format(sqlFormat, indexName, index.TableName, columns,
index.Unique ? "unique" : "");
connection.Execute(sql);
}
// Create Triggers
this.CreateTriggers(ty, connection);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
}