/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// SQL query Example: <para/>
/// CREATE TABLE table1 (id INTEGER PRIMARY KEY, firstColumn TEXT NOT NULL, secondColumn REAL NOT NULL)
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(Dictionary <string, Table> tables)
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeText();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
if (iter.Equals(0))
{
sqb.ParamPrimaryKey();
}
else
{
sqb.ParamNot().Null();
}
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().IfNotExists().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
tableQueries.Add(CreateTriggerTable(table.Value.TableName));
tableQueries.Add(InsertStartValueTriggerTable(table.Value.TableName));
tableQueries.Add(CreateCounterAddTriger(table.Value.TableName));
tableQueries.Add(CreateCounterSubTriger(table.Value.TableName));
}
return(tableQueries);
}
/// <summary>
/// Creates the trigger table where the row count is automatically update through a custom insert and delete trigger.
/// <para/>
/// SQL query Example: <para/>
/// create table if not exists tableName_count (id integer primary key, number int);
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateTriggerTable(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string param = sqb.Brackets(sqb.AddValue("id").TypeInteger().ParamPrimaryKey().Comma().AddValue("rowCount").TypeInteger().Flush()).Flush();
sqb.Create().Table().IfNotExists().AddValue(tableName + "_count").AddValue(param);
return(sqb.ToString());
}
/// <summary>
/// Creates the delete trigger to keep track of the row count.
/// Basically every delete command subtracts +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_sub after delete on table BEGIN update table_count set rowCount = rowCount - 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterSubTriger(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_sub").After().Delete().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount - 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
/// <summary>
/// Creates the insert trigger to keep track of the row count.
/// Basically every insert command adds +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_add after insert on table BEGIN update table_count set rowCount = rowCount + 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterAddTriger(string tableName)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_add").After().Insert().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount + 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(ConcurrentDictionary <string, Table> tables)
{
try
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (iter.Equals(0))
{
sqb.AddValue("AUTOINCREMENT").ParamPrimaryKey();
paramList.Add(sqb.Flush());
iter++;
continue;
}
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeDateTime();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
sqb.ParamNot().Null();
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
}
return(tableQueries);
}
catch (Exception e)
{
throw e;
}
}