Exemplo n.º 1
0
        /// <summary>
        /// Gets the current rows from the table. Since this is accomplished with a trigger table its just a small querie.
        /// </summary>
        /// <param name="table">The table to get the current rows from.</param>
        /// <returns>Returns the number of rows in the given table</returns>
        public override int GetCurrentRowsFromTable(Table table)
        {
            try
            {
                SQLQueryBuilder sqb = new SQLQueryBuilder();
                sqb.Select().AddValue("rowCount").From().AddValue(table.TableName + "_count").Where().AddValue("id").Equal().AddValue("1");
                List <List <object> > result = ReadQuery(sqb.ToString(),
                                                         new List <KeyValuePair <int, Type> >()
                {
                    new KeyValuePair <int, Type>(0, typeof(int))
                });

                if (result == null)
                {
                    return(0);
                }
                else
                {
                    return((int)result[0][0]);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
Exemplo n.º 2
0
        /// <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>
        /// Gets the current rows from the table. There are no triggers in access so i had to do it with the count method.
        /// </summary>
        /// <param name="table">The table to get the current rows from.</param>
        /// <returns>Returns the number of rows in the given table</returns>
        public override int GetCurrentRowsFromTable(Table table)
        {
            try
            {
                SQLQueryBuilder sqb        = new SQLQueryBuilder();
                string          columnName = table.Columns.First().Key;
                sqb.Select().AddValue("Count(" + columnName + ")").From().AddValue(table.TableName);
                List <List <object> > result = ReadQuery(sqb.ToString(),
                                                         new List <KeyValuePair <int, Type> >()
                {
                    new KeyValuePair <int, Type>(0, typeof(int))
                });

                if (result == null)
                {
                    return(0);
                }
                else
                {
                    return((int)result[0][0]);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Exemplo n.º 4
0
        /// <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());
        }
Exemplo n.º 5
0
        /// <summary>
        /// Inserts the first and only row of this table. Bascially its set's the rowCOunt to 0.
        /// <para/>
        /// SQL query Example: <para/>
        /// insert into table1Count (rowCount) values (0);
        /// </summary>
        /// <param name="tableName">The name of the table.</param>
        /// <returns>The finished sql string.</returns>
        private string InsertStartValueTriggerTable(string tableName)
        {
            SQLQueryBuilder sqb   = new SQLQueryBuilder();
            string          param = sqb.AddValue("id").Comma().AddValue("rowCount").Flush();

            sqb.Insert().Or().Ignore().Into().AddValue(tableName + "_count").Brackets(param).Values().Brackets("1, 0");
            return(sqb.ToString());
        }
Exemplo n.º 6
0
        /// <summary>
        /// Get's the last n rows from the specified table.
        /// <para/>
        /// SQL query Example:<para/>
        /// SELECT * FROM table ORDER BY id DESC LIMIT 100
        /// </summary>
        /// <param name="rows">number of the rows to display.</param>
        /// <param name="table">The table to get the values from.</param>
        /// <returns></returns>
        public override List <List <object> > GetLastNRowsFromTable(Table table, int rows)
        {
            SQLQueryBuilder sqb = new SQLQueryBuilder();

            sqb.Select().ALL().From().AddValue(table.TableName).OrderBY().AddValue(table.Columns.First().Key).Desc().Limit().AddValue(rows.ToString());
            List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));

            return(results);
        }
Exemplo n.º 7
0
        /// <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());
        }
Exemplo n.º 8
0
 /// <summary>
 /// Drops the tables with the given table names.
 /// <para/>
 /// SQL query Example: <para/>
 /// DROP TABLE table1
 /// </summary>
 /// <param name="tableNames">A list of table names to delete.</param>
 public override void DropTables(List <string> tableNames)
 {
     foreach (var tableName in tableNames)
     {
         SQLQueryBuilder sqb = new SQLQueryBuilder();
         sqb.DropTable().AddValue(tableName);
         CommitQuery(sqb.ToString());
     }
 }
Exemplo n.º 9
0
        /// <summary>
        /// Deletes the last n rows of the given table.
        /// </summary>
        /// <param name="table">The table to delete the last n data from.</param>
        /// <param name="rows">The amount of data to delete.</param>
        public override void DeleteLastNRows(Table table, int rows)
        {
            SQLQueryBuilder sqb        = new SQLQueryBuilder();
            string          columnName = table.Columns.First().Key;
            string          param      = sqb.Select().AddValue(columnName).From().AddValue(table.TableName).OrderBY().
                                         AddValue(columnName).Asc().Limit().AddValue(rows.ToString()).Flush();

            sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);

            CommitQuery(sqb.ToString());
        }
Exemplo n.º 10
0
        /// <summary>
        /// Gets all rows in the given id slot.
        /// <para/>
        /// SQL query Example: <para/>
        /// SELECT * from table3 where id >= 0 and id -= 1
        /// </summary>
        /// <param name="table"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end)
        {
            SQLQueryBuilder sqb = new SQLQueryBuilder();

            sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
            sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());

            List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));

            return(results);
        }
Exemplo n.º 11
0
        /// <summary>
        /// Gets all rows in the given DateTime slot.
        /// <para/>
        /// SQL query Example: <para/>
        /// select * from table3 where Date >= "2018-12-06 11:10:32.632" and Date -= "2018-12-06 12:05:57.526";
        /// </summary>
        /// <param name="table">The name of the table to get the data from.</param>
        /// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
        /// <param name="from">A DateTime object with the beginning of the timeslot.</param>
        /// <param name="until">A DateTime object with the end of the timeslot.</param>
        /// <returns></returns>
        public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until)
        {
            SQLQueryBuilder sqb         = new SQLQueryBuilder();
            string          stringFrom  = sqb.Apostrophe(from.ToString(_stringFormat)).Flush();
            string          stringUntil = sqb.Apostrophe(until.ToString(_stringFormat)).Flush();

            sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
            sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);

            List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));

            return(results);
        }
        /// <summary>
        /// If the current rowCount value is higher then the MaxRows value the deletion will be started.
        /// Now the size of the table will be shrinked to 70% of the MaxRows value. The oldest values will be deleted.
        /// If the amount of the rows to be deleted is higher than the global _maxDeleteRowSize then only every 5 seconds the highest amount possible
        /// will be deleted until the initial calculated amountToDelete is 0.
        ///  <para/>
        /// Example: <para/>
        /// If you have 50 000 000 as MaxRows defined. Then 70% would be 35 000 000 rows. So 15 000 000 would have to be deleted.
        /// If youre _maxDeleteRowSize is 100 000 this would mean 150 turns every 5 seconds. This would take then round about 12,5 minutes to delete all of them.
        /// If you calculated with 500 entries per second. This would only mean 375 000 entries in these 12,5 minutes.
        /// So this should be enough time to delete all entries with plenty of time in between for other sources to write to the database.
        /// </summary>
        /// <param name="tables">All saved tables.</param>
        public override void CheckDeleteTables(ConcurrentDictionary <string, Table> tables)
        {
            try
            {
                foreach (Table table in tables.Values)
                {
                    // Get the current row value
                    long result = GetCurrentRowsFromTable(table);

                    if (result >= table.MaxRows)
                    {
                        // Calculate 70% and the amount to delete
                        double seventyPercent = (double)table.MaxRows * (double)0.7;
                        long   amountToDelete = result - (long)Math.Round(seventyPercent);

                        string text = "Started to delete entries on table: " + table.TableName + Environment.NewLine;
                        text += "Current Rows: " + result + " | Max table rows: " + table.MaxRows + " | Amount to delete: " + amountToDelete;
                        OnDeleteEvent(text);

                        while (amountToDelete > 0)
                        {
                            long rows;
                            if (amountToDelete > MaxDeleteRowSize)
                            {
                                rows = MaxDeleteRowSize;
                            }
                            else
                            {
                                rows = amountToDelete;
                            }

                            amountToDelete -= rows;

                            SQLQueryBuilder sqb        = new SQLQueryBuilder();
                            string          columnName = table.Columns.First().Key;
                            string          param      = sqb.Select().Top().AddValue(rows.ToString()).AddValue(columnName).From().AddValue(table.TableName)
                                                         .OrderBY().AddValue(columnName).Desc().Flush();
                            sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);

                            CommitQuery(sqb.ToString());
                            Thread.Sleep(5000);
                        }
                        OnDeleteEvent("Finished the Deletion of the table: " + table.TableName);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Exemplo n.º 13
0
 /// <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>
        /// Deletes the last n rows of the given table.
        /// </summary>
        /// <param name="table">The table to delete the last n data from.</param>
        /// <param name="rows">The amount of data to delete.</param>
        public override void DeleteLastNRows(Table table, int rows)
        {
            // TODO: Check Query
            try
            {
                SQLQueryBuilder sqb        = new SQLQueryBuilder();
                string          columnName = table.Columns.First().Key;
                string          param      = sqb.Select().Top().AddValue(rows.ToString()).AddValue(columnName).From().AddValue(table.TableName)
                                             .OrderBY().AddValue(columnName).Desc().Flush();
                sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);

                CommitQuery(sqb.ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
        }
 /// <summary>
 /// Drops the tables with the given table names.
 /// DROP TABLE table1
 /// </summary>
 /// <param name="tableNames">A list of table names to delete.</param>
 public override void DropTables(List <string> tableNames)
 {
     try
     {
         List <string> tableQueries = new List <string>();
         foreach (var tableName in tableNames)
         {
             SQLQueryBuilder sqb = new SQLQueryBuilder();
             sqb.DropTable().AddValue(tableName);
             tableQueries.Add(sqb.ToString());
         }
         CommitBatchQuery(tableQueries);
     }
     catch (Exception e)
     {
         throw e;
     }
 }
        /// <summary>
        /// Gets all rows in the given id slot.
        /// <para/>
        /// </summary>
        /// <param name="table"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="ascending">Ascending or descending by first param.</param>
        /// <returns></returns>
        public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end, bool ascending = true)
        {
            try
            {
                SQLQueryBuilder sqb = new SQLQueryBuilder();
                sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
                sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());

                if (!ascending)
                {
                    sqb.OrderBY().AddValue(table.Columns.First().Key).Desc();
                }

                List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
                return(results);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Exemplo n.º 17
0
        /// <summary>
        /// Get's the last n rows from the specified table.
        /// <para/>
        /// SQL query Example:<para/>
        /// SELECT * FROM (SELECT * FROM name.table3 ORDER BY id DESC LIMIT 1) ORDER BY id ASC;
        /// </summary>
        /// <param name="rows">number of the rows to display.</param>
        /// <param name="table">The table to get the values from.</param>
        /// <param name="ascending">Ascending or descending by first param.</param>
        /// <returns></returns>
        public override List <List <object> > GetLastNRowsFromTable(Table table, int rows, bool ascending = true)
        {
            try
            {
                SQLQueryBuilder sqb = new SQLQueryBuilder();
                sqb.Select().ALL().From().AddValue(table.TableName).OrderBY().AddValue(table.Columns.First().Key).Desc().Limit().AddValue(rows.ToString());

                if (ascending)
                {
                    string value = sqb.Flush();
                    sqb.Select().ALL().From().Brackets(value).OrderBY().AddValue(table.Columns.First().Key).Asc();
                }

                List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
                return(results);
            }
            catch (Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// Gets all rows in the given DateTime slot.
        /// <para/>
        /// </summary>
        /// <param name="table">The name of the table to get the data from.</param>
        /// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
        /// <param name="from">A DateTime object with the beginning of the timeslot.</param>
        /// <param name="until">A DateTime object with the end of the timeslot.</param>
        /// <param name="ascending">Ascending or descending by DateTimeColumn param.</param>
        /// <returns></returns>
        public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until, bool ascending = true)
        {
            try
            {
                SQLQueryBuilder sqb         = new SQLQueryBuilder();
                string          stringFrom  = sqb.Tags(from.ToString(_stringFormat)).Flush();
                string          stringUntil = sqb.Tags(until.ToString(_stringFormat)).Flush();

                sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
                sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);

                if (!ascending)
                {
                    sqb.OrderBY().AddValue(DateTimeColumnName).Desc();
                }

                List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
                return(results);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Exemplo n.º 19
0
        /// <summary>
        /// Updates the given columns with the given id in the first column.
        /// Each row of rowsToUpdate must have the same size as rowsData.
        /// </summary>
        /// <param name="tableName">The table where rows should be updated.</param>
        /// <param name="rowsToUpdate">The rows with the name and data type to update.</param>
        /// <param name="rowsData">The rows with all column data which should be updated.</param>
        public override void UpdateTable(string tableName, List <Dictionary <string, Type> > rowsToUpdate, List <List <object> > rowsData)
        {
            int           rowIter   = 0;
            List <string> queryList = new List <string>();

            foreach (Dictionary <string, Type> row in rowsToUpdate)
            {
                SQLQueryBuilder sqb        = new SQLQueryBuilder();
                List <object>   columnData = rowsData[rowIter];
                List <string>   columns    = new List <string>();

                if (!row.Count.Equals(columnData.Count))
                {
                    throw new InvalidDataException(rowIter.ToString() + ". row size from rowsToUpdate doesn't match current row size from rowsData");
                }

                int columnIter = 0;
                foreach (KeyValuePair <string, Type> column in row)
                {
                    if (columnIter.Equals(0))
                    {
                        columnIter++;
                        continue;
                    }

                    if (!columnData[columnIter].GetType().Equals(column.Value))
                    {
                        throw new TypeLoadException("Type of the data doesn't match the columns type!");
                    }

                    if (column.Value == typeof(int))
                    {
                        sqb.AddValue(columnData[columnIter].ToString());
                    }
                    else if (column.Value == typeof(string))
                    {
                        sqb.Apostrophe(sqb.AddValue(columnData[columnIter].ToString()).Flush());
                    }
                    else if (column.Value == typeof(double))
                    {
                        sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
                    }
                    else if (column.Value == typeof(float))
                    {
                        sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
                    }
                    else if (column.Value == typeof(DateTime))
                    {
                        DateTime convertTime = (DateTime)columnData[columnIter];
                        sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
                    }
                    else
                    {
                        throw new NotSupportedException(column.Value.Name + " Datatype not supported");
                    }

                    string value = sqb.Flush();

                    sqb.AddValue(column.Key).Equal().AddValue(value);

                    columns.Add(sqb.Flush());
                    columnIter++;
                }
                sqb.Update().AddValue(tableName).Set().AddValues(columns).Where().AddValue(row.First().Key).Equal().AddValue(columnData[0].ToString());
                queryList.Add(sqb.ToString());
                rowIter++;
            }
            CommitBatchQuery(queryList);
        }
Exemplo n.º 20
0
        /// <summary>
        /// Inserts the given objects into the given table. The first object "id" is ignored due to the auto increment,
        /// <para/>
        /// SQL query Example: <para/>
        /// INSERT INTO table3 (Name, Date, value) VALUES ('John Doe', '2018-12-06 12:01:16.767', 22.5);
        /// </summary>
        /// <param name="tableName">The name of the table to insert rows to.</param>
        /// <param name="rows">A list of rows with all column objects to insert.</param>
        /// <param name="tables">All saved tables.</param>
        public override void InsertIntoTable(string tableName, Dictionary <string, Table> tables, List <List <object> > rows)
        {
            List <string> queryList = new List <string>();

            foreach (List <object> row in rows)
            {
                SQLQueryBuilder sqb           = new SQLQueryBuilder();
                List <string>   firstBracket  = new List <string>();
                List <string>   secondBracket = new List <string>();
                int             listIter      = 0;
                foreach (KeyValuePair <string, Type> column in tables[tableName].Columns)
                {
                    if (listIter.Equals(0))
                    {
                        listIter++;
                        continue;
                    }

                    if (!row[listIter].GetType().Equals(column.Value))
                    {
                        throw new TypeLoadException("Type of the data doesn't match the columns type!");
                    }

                    firstBracket.Add(sqb.AddValue(column.Key).Flush());

                    if (column.Value == typeof(int))
                    {
                        sqb.AddValue(row[listIter].ToString());
                    }
                    else if (column.Value == typeof(string))
                    {
                        sqb.Apostrophe(sqb.AddValue(row[listIter].ToString()).Flush());
                    }
                    else if (column.Value == typeof(double))
                    {
                        sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
                    }
                    else if (column.Value == typeof(float))
                    {
                        sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
                    }
                    else if (column.Value == typeof(DateTime))
                    {
                        DateTime convertTime = (DateTime)row[listIter];
                        sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
                    }
                    else
                    {
                        throw new NotSupportedException(column.Value.Name + " Datatype not supported");
                    }

                    secondBracket.Add(sqb.Flush());
                    listIter++;
                }
                string columnNames  = sqb.Brackets_Multiple(firstBracket, false).Flush();
                string columnValues = sqb.Brackets_Multiple(secondBracket, false).Flush();
                sqb.InsertInto().AddValue(tableName).AddValue(columnNames).Values().AddValue(columnValues);
                queryList.Add(sqb.ToString());
            }
            CommitBatchQuery(queryList);
        }
        /// <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;
            }
        }
Exemplo n.º 22
0
        /// <summary>
        /// Gets called if the Timer.Elapsed event is raised.
        /// If the current rowCount value is higher then the MaxRows value in each table a deleteThread will be started.
        /// Now the size of the table will be shrinked to 70% of the MaxRows value. The oldest values will be deleted.
        /// If the amount of the rows to be deleted is higher than the global _maxDeleteRowSize then only every 5 seconds the highest amount possible
        /// will be deleted until the the initial calculated RowsToDelete, which is stored in the Table object, is 0.
        /// The thread is also stored in a global Dictionary so it can be aborted.
        /// Every table can only have one DeleteThread active which is marked with the DeleteThreadActive variable.
        ///  <para/>
        /// Example: <para/>
        /// If you have 50 000 000 as MaxRows defined. Then 70% would be 35 000 000 rows. So 15 000 000 would have to be deleted.
        /// If youre _maxDeleteRowSize is 100 000 this would mean 150 turns every 5 seconds. This would take then round about 12,5 minutes to delete all of them.
        /// If you calculated with 500 entries per second. This would only mean 375 000 entries in these 12,5 minutes.
        /// So this should be enough time to delete all entries with plenty of time in between for other sources to write to the database.
        /// </summary>
        /// <param name="tables">All saved tables.</param>
        public override void OnDeleteTimer(Dictionary <string, Table> tables)
        {
            foreach (Table table in tables.Values)
            {
                long result = GetCurrentRowsFromTable(table);

                if (result == 0)
                {
                    continue;
                }

                if (result >= table.MaxRows)
                {
                    // Calculate 70% and the amount to delete
                    double seventyPercent = (double)table.MaxRows * (double)0.7;
                    long   amountToDelete = result - (long)Math.Round(seventyPercent);

                    Console.WriteLine(result + ">= " + table.MaxRows + " -> Clear the table: " + table.TableName);

                    // Start the Thread if it isn't active allready
                    if (!table.DeleteThreadActive)
                    {
                        table.RowsToDelete       = amountToDelete;
                        table.DeleteThreadActive = true;

                        Thread deleteThread = new Thread(() =>
                        {
                            try
                            {
                                while (table.RowsToDelete > 0)
                                {
                                    long rows;
                                    if (table.RowsToDelete > MaxDeleteRowSize)
                                    {
                                        rows = MaxDeleteRowSize;
                                    }
                                    else
                                    {
                                        rows = table.RowsToDelete;
                                    }

                                    table.RowsToDelete -= rows;

                                    SQLQueryBuilder deleteQuery = new SQLQueryBuilder();
                                    string columnName           = table.Columns.First().Key;
                                    string param = deleteQuery.Select().AddValue(columnName).From().AddValue(table.TableName).OrderBY().
                                                   AddValue(columnName).Asc().Limit().AddValue(rows.ToString()).Flush();
                                    deleteQuery.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);

                                    CommitQuery(deleteQuery.ToString());
                                    Console.WriteLine("Delete Thread deleted {0} rows!", rows);
                                    Thread.Sleep(5000);
                                }
                                table.DeleteThreadActive = false;
                                _tableDeleteThreads.Remove(table.TableName);
                            }
                            catch (Exception)
                            {
                            }
                        });

                        _tableDeleteThreads.Add(table.TableName, deleteThread);
                        deleteThread.Start();
                        Console.WriteLine("Started Thread on: " + table.TableName);
                    }
                }
            }
        }