/// <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; } }