Exemplo n.º 1
0
        private void TestUpdate(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
        {
            Output("TestUpdate:");
            Output("");

            try
            {
                DBConnection conn = runner.OpenConnection(executer, db, connInfo);

                try
                {
                    Output("Insert more rows");
                    Stmt_Insert stmtInsert = new Stmt_Insert(table);
                    stmtInsert.AddAllColumns();
                    stmtInsert.AddValues(Guid.NewGuid(), "Dette er en tekst", 42, DateTime.Now, null, 6576547634);
                    stmtInsert.AddParameter("MEGET STOR TEKST");
                    stmtInsert.AddValue(true);
                    stmtInsert.AddParameter(new byte[432]);
                    Output(builder.ToSQL(stmtInsert));
                    runner.Insert(executer, conn, stmtInsert);
                    Output("Rows inserted");
                    Output("");

                    ShowContents(runner, executer, conn, table);
                    Output("");

                    Output("Update 1");
                    Stmt_Update stmtUpdate = new Stmt_Update(table);
                    stmtUpdate.AddColumns("txTekst", "iTal");
                    stmtUpdate.AddValues("En ny tekst", 534);
                    stmtUpdate.AddCriteria(new Crit_Match(table, "iTal", MatchType.Equal, 42));
                    Output(builder.ToSQL(stmtUpdate));
                    runner.Update(executer, conn, stmtUpdate);
                    Output("");

                    ShowContents(runner, executer, conn, table);
                    Output("");

                    Output("Update 2");
                    stmtUpdate = new Stmt_Update(table);
                    stmtUpdate.AddColumn("txStorTekst");
                    stmtUpdate.AddParameter("DETTE STÅR MED STORT!");
                    stmtUpdate.AddCriteria(new Crit_Match(table, "txStorTekst", MatchType.IsNull));
                    Output(builder.ToSQL(stmtUpdate));
                    runner.Update(executer, conn, stmtUpdate);
                    Output("");

                    ShowContents(runner, executer, conn, table);
                    Output("");

                    Stmt_Select stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    stmtSelect.AddFunction(new Func_SubString(table, "txTekst", 3, 8));
                    Output(builder.ToSQL(stmtSelect));
                    ShowContents(stmtSelect, runner, executer, conn);
                    Output("");

                    Output("Delete");
                    Stmt_Delete stmtDelete = new Stmt_Delete(table);
                    stmtDelete.AddCriteria(new Crit_Match(table, "bValg", MatchType.Equal, false));
                    Output(builder.ToSQL(stmtDelete));
                    runner.Delete(executer, conn, stmtDelete);
                    Output("");

                    ShowContents(runner, executer, conn, table);
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Output("TestUpdate failed with an exception:");
                Output(ex);
            }
            finally
            {
                Output("");
                Output("");
            }
        }
Exemplo n.º 2
0
        public void _06_CreateAndDropView()
        {
            DBDatabase db = ConnectDb();

            CreateCustomTable(db);

            //Wrap a try round this so we can definitely drop the table

            try
            {
                InsertARowIntoCustomTable(db, "Beta", "Alpha");
                InsertARowIntoCustomTable(db, "Alpha", "Beta");

                //Create the view
                DBQuery create = DBQuery.Create.View(ViewName)
                                 .As(DBQuery.SelectAll()
                                     .From(TableName)
                                     .WhereField("ColB", Compare.Like, DBConst.String("A%")));
                TestContext.WriteLine(create.ToSQLString(db));

                db.ExecuteNonQuery(create);
                TestContext.WriteLine("Created the view");

                DBQuery selectView = DBQuery.SelectAll().From(ViewName);
                int     totalcount = 0;
                db.ExecuteRead(selectView, reader =>
                {
                    while (reader.Read())
                    {
                        string colb = reader["ColB"] as string;
                        totalcount += 1;
                    }
                });
                TestContext.WriteLine("Total Number of rows in view = {0}", totalcount);
                DBDropQuery drop = DBQuery.Drop.View(ViewName);
                db.ExecuteNonQuery(drop);

                //Try another select to make sure it fails
                try
                {
                    db.ExecuteNonQuery(selectView);
                    throw new InvalidOperationException("The table was not dropped and the insert statement succeeded");
                }
                catch (InvalidOperationException)
                {
                    throw;
                }
                catch (System.Exception ex)
                {
                    TestContext.WriteLine("Expected failure after inserting on a dropped table:{0}", ex.Message);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                DropCustomTable(db);
            }
        }
Exemplo n.º 3
0
        public void _12_CreateSProc()
        {
            DBDatabase db = ConnectDb();

            this.CreateCustomTable(db);
            try
            {
                DBParam name = DBParam.Param("name", DbType.String, 50);
                DBParam desc = DBParam.Param("desc", DbType.String, 150);

                DBQuery ins = DBQuery.InsertInto(TableName).Fields(TblCol2, TblCol3).Values(name, desc);
                char    one = 'A';
                char    two = 'a';

                TestContext.WriteLine(ins.ToSQLString(db));

                for (int i = 0; i < 26; i++)
                {
                    int c = (int)one;
                    c += i;
                    string offset = ((char)c).ToString() + two.ToString();
                    name.Value = offset;
                    desc.Value = "Description of " + offset;
                    db.ExecuteNonQuery(ins);
                }
                int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
                Assert.AreEqual(26, count);

                DBQuery q = DBQuery.Create.StoredProcedure("finditemsincol2")
                            .WithParam("p1", DbType.String, 50, ParameterDirection.Input)
                            .As(
                    DBQuery.SelectAll().From(TableName)
                    .WhereField(TblCol2, Compare.Like, DBParam.Param("p1"))

                    );
                TestContext.WriteLine("Execute Procedure: " + q.ToSQLString(db));
                db.ExecuteNonQuery(q);
                TestContext.WriteLine("Created the new stored procedure");


                DBQuery exec = DBQuery.Exec("finditemsincol2").WithParamValue("p1", DbType.String, 50, "A%");
                count = 0;
                TestContext.WriteLine(exec.ToSQLString(db));
                db.ExecuteRead(exec, reader =>
                {
                    while (reader.Read())
                    {
                        count++;
                        Assert.IsTrue(reader[TblCol2].ToString().StartsWith("A"));
                    }
                    TestContext.WriteLine("Executed the stored procedure and read '" + count.ToString() + "' rows");
                });

                Assert.AreEqual(1, count);
            }
            finally
            {
                try
                {
                    DBQuery drop = DBQuery.Drop.StoredProcedure("finditemsincol2");
                    db.ExecuteNonQuery(drop);
                    TestContext.WriteLine("Sucessfully dropped the stored procedure");
                }
                catch
                {
                    TestContext.WriteLine("DROP PROCEDURE failed");
                }
                this.DropCustomTable(db);
            }
        }
Exemplo n.º 4
0
 protected override DBSchemaProvider CreateSchemaProvider(DBDatabase forDatabase, DBDatabaseProperties properties)
 {
     return(new DBSqLiteSchemaProvider(forDatabase, properties));
 }
 public DBOleDbStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties properties, System.IO.TextWriter tw, bool ownswriter)
     : base(forDatabase, properties, tw, ownswriter)
 {
 }
Exemplo n.º 6
0
        /// <summary>
        /// Takes the XML string and converts it back to a DBQuery.
        /// It then checks the generated sql statement from the DBQuery and compares to the provided sql string
        /// and makes sure they are the same (equal case sensitive).
        /// </summary>
        /// <param name="xml"></param>
        /// <param name="sql"></param>
        /// <param name="name"></param>
        private void ValidateXml(string xml, string sql, string name)
        {
            DBDatabase db = CreateDB();

            ValidateXml(db, xml, sql, name);
        }
Exemplo n.º 7
0
 public DBOracleSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
     : base(database, properties)
 {
 }
Exemplo n.º 8
0
 public SecurityPrivilege(string conn)
 {
     InitDbs(conn);
     db = base.Database;
 }
Exemplo n.º 9
0
 /// <summary>
 /// Creates a new instance of the DBMSAccessSchemaProvider
 /// </summary>
 /// <param name="database"></param>
 /// <param name="properties"></param>
 public DBMSAccessSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
     : base(database, properties)
 {
 }
Exemplo n.º 10
0
        public static void Main(String[] args)
        {
            try
            {
                DBRunner     runner   = new DBRunner();
                ISQLExecuter executer = new DB2_SQLExecuter(new DB2_SQL());
                DBDatabase   database = new DBDatabase("EISST");

                // Open connection to the database
                Output("Open connection to database");

                IConnectionInfo connInfo   = new DB2_ConnectionInfo("vagn", "EISST", "EISST", "EISSTEISST");
                DBConnection    connection = runner.OpenConnection(executer, database, connInfo);

                try
                {
                    // Extract meta data for all the tables we need
                    Output("Extract table metadata");

                    DBTable pluginsTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Plugins");
                    database.AddTable(pluginsTable);

                    DBTable grupperTable = runner.GetTableMetaData(executer, connection, database, "EISST", "Grupper");
                    database.AddTable(grupperTable);

                    DBTable gruppePostkasseTable = runner.GetTableMetaData(executer, connection, database, "EISST", "GruppePostkasse");
                    database.AddTable(gruppePostkasseTable);

                    DBTable side1Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side1");
                    database.AddTable(side1Table);

                    DBTable side2Table = runner.GetTableMetaData(executer, connection, database, "EISST", "Mortality_Side2");
                    database.AddTable(side2Table);

                    Output("Find needed ids");

                    // Find the plug-in id for mortality documents
                    Guid pluginID = FindPluginID(pluginsTable, runner, executer, connection);
                    if (pluginID.Equals(Guid.Empty))
                    {
                        Output("Couldn't find plug-in ID");
                        return;
                    }

                    Output("Plug-in ID: " + pluginID.ToString("B").ToUpper());

                    // Find the group id where the documents are stored
                    Guid groupID = FindGroupID(grupperTable, runner, executer, connection);
                    if (groupID.Equals(Guid.Empty))
                    {
                        Output("Couldn't find group ID");
                        return;
                    }

                    Output("Group ID: " + groupID.ToString("B").ToUpper());

                    Output("Delete documents that are stored in the database");
                    FixMailBox(pluginID, groupID, gruppePostkasseTable, side1Table, side2Table, runner, executer, connection);
                }
                finally
                {
                    Output("Close connection");
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Output("Exception failure: " + ex);
            }
        }
Exemplo n.º 11
0
 public DBOracleStatementBuilder(DBDatabase db, DBDatabaseProperties props,
                                 System.IO.TextWriter writer, bool ownsWriter)
     : base(db, props, writer, ownsWriter)
 {
 }
Exemplo n.º 12
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="conn"></param>
 public ImportTemplate(string conn)
 {
     InitDbs(conn);
     db = base.Database;
 }
Exemplo n.º 13
0
        private void TestSQLBuild(ISQL builder, DBDatabase db, DBTable table)
        {
            Output("TestSQLBuild:");
            Output("");

            try
            {
                Output("Criterias:");
                ICriteria crit1 = new Crit_Match(table, "lStortTal", MatchType.Equal, 6576547634);
                ICriteria crit2 = new Crit_Match(table, "txTekst", MatchType.Different, "Bent");
                ICriteria crit3 = new Crit_Match(table, "sLilleTal", MatchType.IsNull);

                Stmt_Select stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(crit1);
                stmtSelect.AddCriteria(crit2);
                stmtSelect.AddCriteria(crit3);
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(new Crit_Or(crit1, crit2));
                stmtSelect.AddCriteria(crit3);
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                ICriteria tempCrit = new Crit_And(crit2, crit3);
                stmtSelect.AddCriteria(new Crit_Or(crit1, tempCrit));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(new Crit_Or(new Crit_Or(crit1, crit2), crit3));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(crit1);
                stmtSelect.AddCriteria(crit2);
                stmtSelect.AddCriteria(new Crit_In(table, "iTal", true, 3, 5, 254, 31));
                Output(builder.ToSQL(stmtSelect));

                Stmt_Select stmtSelect1 = new Stmt_Select();
                stmtSelect1.AddColumn(table, "iTal");

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", stmtSelect1));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(table);
                stmtSelect.AddCriteria(new Crit_SubQuery(table, "iTal", true, stmtSelect1));
                Output(builder.ToSQL(stmtSelect));
                Output("");

                Output("Aggregates:");
                stmtSelect = new Stmt_Select();
                stmtSelect.AddColumn(table, "iTal");
                stmtSelect.Distinct = true;
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddTable(table);
                stmtSelect.AddAggregate(new Aggre_Count());
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddTable(table);
                stmtSelect.AddAggregate(new Aggre_Count(table, "iTal"));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddTable(table);
                stmtSelect.AddAggregate(new Aggre_Max(table, "iTal"));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddTable(table);
                stmtSelect.AddAggregate(new Aggre_Min(table, "iTal"));
                Output(builder.ToSQL(stmtSelect));
                Output("");

                Output("Create tables:");
                DBTable employees = db.AddTable("Employees");
                employees.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
                employees.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull);

                DBTable orders = db.AddTable("Orders");
                orders.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
                orders.AddColumn("Product", ColumnType.String, 50, ColumnFlag.NotNull | ColumnFlag.IndexUnique);
                orders.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.NotNull);

                DBTable storage = db.AddTable("Storage");
                storage.AddColumn("Storage_ID", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
                storage.AddColumn("Prod_ID", ColumnType.Int, ColumnFlag.NotNull);
                storage.AddColumn("Count", ColumnType.Int, ColumnFlag.NotNull | ColumnFlag.IndexDesc);

                Stmt_CreateTable stmtCreate = new Stmt_CreateTable(employees);
                Output(builder.ToSQL(stmtCreate));

                stmtCreate = new Stmt_CreateTable(orders);
                Output(builder.ToSQL(stmtCreate));

                stmtCreate = new Stmt_CreateTable(storage);
                Output(builder.ToSQL(stmtCreate));
                Output("");

                Output("Joins:");
                stmtSelect = new Stmt_Select();
                stmtSelect.AddColumn(employees, "Name");
                stmtSelect.AddColumn(orders, "Product");
                stmtSelect.AddJoin(new Join_Inner(employees, "Employee_ID", orders, "Employee_ID"));
                stmtSelect.AddColumn(storage, "Count");
                stmtSelect.AddJoin(new Join_Inner(orders, "Prod_ID", storage, "Prod_ID"));
                stmtSelect.AddCriteria(new Crit_Match(storage, "Count", MatchType.Bigger, 10));
                stmtSelect.AddSort(employees, "Name", Order.Ascending);
                stmtSelect.AddSort(orders, "Product", Order.Descending);
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddColumn(employees, "Name");
                stmtSelect.AddColumn(orders, "Product");
                stmtSelect.AddJoin(new Join_Left(employees, "Employee_ID", orders, "Employee_ID"));
                Output(builder.ToSQL(stmtSelect));

                stmtSelect = new Stmt_Select();
                stmtSelect.AddColumn(employees, "Name");
                stmtSelect.AddColumn(orders, "Product");
                stmtSelect.AddJoin(new Join_Right(employees, "Employee_ID", orders, "Employee_ID"));
                Output(builder.ToSQL(stmtSelect));
                Output("");

                Output("Misc");
                DBTable employees1 = db.AddTable("Employees1");
                employees1.AddColumn("Employee_ID", ColumnType.String, 2, ColumnFlag.PrimaryKey | ColumnFlag.NotNull);
                employees1.AddColumn("Name", ColumnType.String, 50, ColumnFlag.NotNull);

                stmtSelect = new Stmt_Select();
                stmtSelect.AddAllColumns(employees);
                Stmt_Insert stmtInsert = new Stmt_Insert(employees1);
                stmtInsert.InsertFromSelect = stmtSelect;
                Output(builder.ToSQL(stmtInsert));
            }
            catch (Exception ex)
            {
                Output("TestSQLBuild failed with an exception:");
                Output(ex);
            }
            finally
            {
                Output("");
                Output("");
            }
        }
Exemplo n.º 14
0
        private void TestFunctions(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
        {
            Output("TestFunctions:");
            Output("");

            try
            {
                DBConnection conn = runner.OpenConnection(executer, db, connInfo);

                try
                {
                    Output("Insert single row");
                    Stmt_Insert stmtInsert = new Stmt_Insert(table);
                    stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
                    stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
                    Output(builder.ToSQL(stmtInsert));
                    runner.Insert(executer, conn, stmtInsert);

                    stmtInsert = new Stmt_Insert(table);
                    stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
                    stmtInsert.AddValues(Guid.NewGuid(), "Blåbærgrød", 87, (long)2394287487, DateTime.Now, false);
                    Output(builder.ToSQL(stmtInsert));
                    runner.Insert(executer, conn, stmtInsert);
                    Output("Rows inserted");
                    Output("");

                    ShowContents(runner, executer, conn, table);
                    Output("");

                    Stmt_Select stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    IFunction func = new Func_SubString(table, "txTekst", 3, 8);
                    stmtSelect.AddFunction(new Func_SubString(func, 0, 2));
                    Output(builder.ToSQL(stmtSelect));
                    ShowContents(stmtSelect, runner, executer, conn);
                    Output("");

                    stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    stmtSelect.AddFunction(new Func_ToLower(table, "txTekst"));
                    Output(builder.ToSQL(stmtSelect));
                    ShowContents(stmtSelect, runner, executer, conn);
                    Output("");

                    stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    stmtSelect.AddFunction(new Func_ToUpper(table, "txTekst"));
                    Output(builder.ToSQL(stmtSelect));
                    ShowContents(stmtSelect, runner, executer, conn);
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Output("TestUpdate failed with an exception:");
                Output(ex);
            }
            finally
            {
                Output("");
                Output("");
            }
        }
Exemplo n.º 15
0
 public Table()
 {
     InitDbs("");
     db = base.Database;
 }
Exemplo n.º 16
0
        //
        // .ctor
        //

        #region public DBMySqlSchemaProvider(DBDatabase database, DBDatabaseProperties properties)

        public DBMySqlSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
            : base(database, properties)
        {
        }
Exemplo n.º 17
0
        /// <summary>
        /// Outputs the specified query as a SQL statement onto the console. User the DaBDatabase from CreateDB()
        /// </summary>
        /// <param name="query"></param>
        /// <param name="name"></param>
        private string OutputSql(DBQuery query, string name, bool output)
        {
            DBDatabase db = CreateDB();

            return(OutputSql(db, query, name, output));
        }
Exemplo n.º 18
0
        //
        // .ctor
        //

        #region public DBSQLClientSchemaProvider(DBDatabase database, DBDatabaseProperties properties)

        /// <summary>
        /// Creates a new instance of the schema provider for an SqlClient database
        /// </summary>
        /// <param name="database"></param>
        /// <param name="properties"></param>
        public DBSQLClientSchemaProvider(DBDatabase database, DBDatabaseProperties properties)
            : base(database, properties)
        {
        }
Exemplo n.º 19
0
 public SecurityGroup(string conn)
 {
     InitDbs(conn);
     db = base.Database;
 }
Exemplo n.º 20
0
        //
        // .ctor
        //

        internal DBSqLiteSchemaProvider(DBDatabase forDatabase, DBDatabaseProperties properties)
            : base(forDatabase, properties)
        {
        }
Exemplo n.º 21
0
 /// <summary>
 /// Constructor
 /// </summary>
 /// <param name="forDatabase"></param>
 /// <param name="properties"></param>
 /// <param name="tw"></param>
 /// <param name="ownswriter"></param>
 internal protected DBSQLClientStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties properties, System.IO.TextWriter tw, bool ownswriter)
     : base(forDatabase, properties, tw, ownswriter)
 {
 }
Exemplo n.º 22
0
        private List <MetricValue> readMetricValuesIntoEntity(DBEntityBase entity, JobTarget jobTarget, string metricName, AppDRESTMetric appDRESTMetric, int timerangeDuration)
        {
            List <MetricValue> metricValues = new List <MetricValue>(appDRESTMetric.metricValues.Count);

            foreach (AppDRESTMetricValue appDRESTMetricValue in appDRESTMetric.metricValues)
            {
                // Populate metrics into the list for output into CSV
                MetricValue metricValue = new MetricValue();

                metricValue.Controller      = jobTarget.Controller;
                metricValue.ApplicationID   = jobTarget.ApplicationID;
                metricValue.ApplicationName = jobTarget.Application;

                if (entity != null)
                {
                    if (entity is DBDatabase)
                    {
                        DBDatabase database = (DBDatabase)entity;

                        metricValue.EntityID   = database.DatabaseID;
                        metricValue.EntityName = String.Format("{0}\\{1}", database.CollectorName, database.DatabaseName);
                        metricValue.EntityType = database.CollectorType;
                    }
                    else if (entity is DBCollector)
                    {
                        DBCollector databaseCollector = (DBCollector)entity;

                        metricValue.EntityID   = databaseCollector.CollectorID;
                        metricValue.EntityName = databaseCollector.CollectorName;
                        metricValue.EntityType = databaseCollector.CollectorType;
                    }
                }

                metricValue.EventTimeStampUtc = UnixTimeHelper.ConvertFromUnixTimestamp(appDRESTMetricValue.startTimeInMillis);
                metricValue.EventTimeStamp    = metricValue.EventTimeStampUtc.ToLocalTime();
                metricValue.EventTime         = metricValue.EventTimeStamp;

                metricValue.MetricName = metricName;
                metricValue.MetricID   = appDRESTMetric.metricId;
                switch (appDRESTMetric.frequency)
                {
                case "SIXTY_MIN":
                {
                    metricValue.MetricResolution = 60;
                    break;
                }

                case "TEN_MIN":
                {
                    metricValue.MetricResolution = 10;
                    break;
                }

                case "ONE_MIN":
                {
                    metricValue.MetricResolution = 1;
                    break;
                }

                default:
                {
                    metricValue.MetricResolution = 1;
                    break;
                }
                }

                metricValue.Count       = appDRESTMetricValue.count;
                metricValue.Min         = appDRESTMetricValue.min;
                metricValue.Max         = appDRESTMetricValue.max;
                metricValue.Occurrences = appDRESTMetricValue.occurrences;
                metricValue.Sum         = appDRESTMetricValue.sum;
                metricValue.Value       = appDRESTMetricValue.value;

                metricValues.Add(metricValue);
            }

            return(metricValues);
        }
Exemplo n.º 23
0
 protected override DBStatementBuilder CreateStatementBuilder(DBDatabase forDatabase, DBDatabaseProperties withProperties, System.IO.TextWriter writer, bool ownsWriter)
 {
     return(new DBStatementSQLiteBuilder(forDatabase, withProperties, writer, ownsWriter));
 }
Exemplo n.º 24
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="conn"></param>
 public ComponentBuilder(string conn)
 {
     InitDbs(conn);
     db = base.Database;
 }
Exemplo n.º 25
0
        public void _04_JustDropTheTable()
        {
            DBDatabase db = ConnectDb();

            this.DropCustomTable(db);
        }
Exemplo n.º 26
0
        // no statement building
#else
        //
        // SQL String methods
        //

        #region public string ToSQLString(DBDatabase fordatabase)

        /// <summary>
        /// Generates the implementation specific sql statement for this query.
        /// </summary>
        /// <param name="fordatabase">The DBDatabase to use for generation of the SQL statement</param>
        /// <returns>This SQL statement as a string</returns>
        public string ToSQLString(DBDatabase fordatabase)
        {
            return(fordatabase.GetCommandText(this));
        }
Exemplo n.º 27
0
        public void _10_CreateTableWithForeignKeys()
        {
            DBDatabase db = ConnectDb();

            //Create the persons table
            DBQuery createPersons = DBQuery.Create.Table("DSQL_Persons")
                                    .Add("Person_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
                                    .Add("Person_Name", DbType.String, 50);

            TestContext.WriteLine(createPersons.ToSQLString(db));
            db.ExecuteNonQuery(createPersons);

            //Create the orders table
            DBQuery createOrders = DBQuery.Create.Table("DSQL_Orders")
                                   .Add("Order_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
                                   .Add("Ordered_By", DbType.Int32)
                                   .Add("Ordered_Date", DbType.DateTime)
                                   .Add("Signed_By", DbType.Int32, DBColumnFlags.Nullable)
                                   .Constraints(
                DBConstraint.ForeignKey().Column("Ordered_By")                                         //unnamed foreign key first
                .References("DSQL_Persons").Column("Person_ID"),

                DBConstraint.ForeignKey("Orders_Signed_By_2_Persons_PersonID").Column("Signed_By")
                .References("DSQL_Persons").Column("Person_ID")
                .OnDelete(DBFKAction.Cascade)
                .OnUpdate(DBFKAction.Cascade)
                );

            //Execute the Create Table statements
            TestContext.WriteLine(createOrders.ToSQLString(db));
            db.ExecuteNonQuery(createOrders);

            try
            {
                bool scripts = db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts);

                DBParam pname = DBParam.Param("name", DbType.String);

                DBScript insertperson = DBQuery.Script(
                    DBQuery.InsertInto("DSQL_Persons").Field("Person_Name").Value(pname),
                    DBQuery.Select(DBFunction.LastID())
                    );

                //Insert one row into the persons table
                pname.Value = "First Person";
                TestContext.WriteLine(insertperson.ToSQLString(db));
                int firstpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));

                //And another row
                pname.Value = "Second Person";
                int secondpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));


                //Create an order with orderedby = firstpid and signedby = secondpid
                DBParam  orderedby   = DBParam.ParamWithValue(DbType.Int32, firstpid);
                DBParam  signedby    = DBParam.ParamWithValue(DbType.Int32, secondpid);
                DBScript insertorder = DBQuery.Script(
                    DBQuery.InsertInto("DSQL_Orders")
                    .Field("Ordered_By").Value(orderedby)
                    .Field("Ordered_Date").Value(DBFunction.GetDate())
                    .Field("Signed_By").Value(signedby),
                    DBQuery.Select(DBFunction.LastID())
                    );
                TestContext.WriteLine(insertorder.ToSQLString(db));
                int orderid = Convert.ToInt32(this.ExecuteScalarScript(db, insertorder, scripts));

                //Now try to create an order that breaks referential integrity
                orderedby.Value = -100;
                try
                {
                    orderid = Convert.ToInt32(db.ExecuteScalar(insertorder));
                    throw new InvalidOperationException("We should not be able to insert these rows. FAILED test");
                }
                catch (InvalidOperationException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    TestContext.WriteLine("Sucessfully caught an exception that breaks referential integrity");
                }


                //Finally check the cascading deletes
                //check the Orders table count
                //delete a person row
                //ensure that the corresponding row for Signed By FK was deleted

                DBQuery getcount   = DBQuery.SelectCount().From("DSQL_ORDERS");
                int     ordercount = Convert.ToInt32(
                    db.ExecuteScalar(getcount));

                DBQuery del = DBQuery.DeleteFrom("DSQL_Persons")
                              .WhereField("Person_ID", Compare.Equals, DBParam.ParamWithValue(secondpid));
                int removed = db.ExecuteNonQuery(del);

                Assert.AreEqual(1, removed);
                TestContext.WriteLine("Removed a single row from the persons table");

                int newordercount = Convert.ToInt32(db.ExecuteScalar(getcount));
                //Make sure the orders row has been deleted
                Assert.AreEqual(newordercount, ordercount - 1);
                TestContext.WriteLine("Validated that the corresponding row in the orders table has been removed too");
            }
            finally
            {
                //Clean up tables in order
                db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
                db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
                TestContext.WriteLine("Database has been cleaned up");
            }
        }
Exemplo n.º 28
0
 public Field()
 {
     InitDbs("");
     db = base.Database;
 }
Exemplo n.º 29
0
        /* End SQL Server connections */

        /* MySql Connections
         * public const string DbConnection = "server=172.16.56.1;User Id=testaccount;Password=test;Persist Security Info=True;database=northwind;AllowUserVariables=true";
         * public const string DbProvider = "MySql.Data.MySqlClient";
         * /* End SQL Server connections */

        /* SQLite connections
         * public const string DbConnection = @"data source=C:\Development\DynaSQL\DynaSQLUnitTest.db;foreign keys=true;"; //we add the foreign keys=true here to enforce them
         * public const string DbProvider = "System.Data.SQLite";
         * /* End SQLite connections */

        /* MS Access connections
         * public const string DbConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Sample Databases\Northwind2007.accdb;Persist Security Info=False;";
         * public const string DbProvider = "System.Data.OleDb";
         * /* End MSAccess connections */

        #region private static DBDatabase CreateDb()

        /// <summary>
        /// Creates the database connection
        /// </summary>
        /// <returns></returns>
        private static DBDatabase ConnectDb()
        {
            return(DBDatabase.Create(DbConnection, DbProvider));
        }
Exemplo n.º 30
0
        private void TestTransactions(DBRunner runner, ISQLExecuter executer, DBDatabase db, DBTable table, IConnectionInfo connInfo)
        {
            Output("TestTransactions:");
            Output("");

            try
            {
                DBConnection conn = runner.OpenConnection(executer, db, connInfo);

                try
                {
                    Stmt_Insert stmtInsert;
                    Stmt_Select stmtSelect;
                    long        result;

                    Output("Begin transaction");
                    DBTransaction trans = runner.CreateTransaction(executer);
                    trans.Begin(conn);

                    try
                    {
                        Output("Insert row");
                        stmtInsert = new Stmt_Insert(table);
                        stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
                        stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
                        runner.Insert(executer, conn, stmtInsert);

                        stmtSelect = new Stmt_Select();
                        stmtSelect.AddTable(table);
                        stmtSelect.AddAggregate(new Aggre_Count());
                        result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
                        Output("Count: " + result + " / 1");

                        Output("Rollback");
                        trans.RollbackAll();
                    }
                    catch (Exception)
                    {
                        trans.RollbackAll();
                        throw;
                    }

                    stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    stmtSelect.AddAggregate(new Aggre_Count());
                    result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
                    Output("Count: " + result + " / 0");
                    Output("");

                    Output("Begin new transaction");
                    trans = runner.CreateTransaction(executer);
                    trans.Begin(conn);

                    try
                    {
                        Output("Insert row");
                        stmtInsert = new Stmt_Insert(table);
                        stmtInsert.AddColumns("uiNoegle", "iTal", "lStortTal", "dtDato", "bValg");
                        stmtInsert.AddValues(Guid.NewGuid(), 87, (long)2394287487, DateTime.Now, false);
                        runner.Insert(executer, conn, stmtInsert);

                        stmtSelect = new Stmt_Select();
                        stmtSelect.AddTable(table);
                        stmtSelect.AddAggregate(new Aggre_Count());
                        result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
                        Output("Count: " + result + " / 1");

                        Output("Commit");
                        trans.CommitAll();
                    }
                    catch (Exception)
                    {
                        trans.RollbackAll();
                        throw;
                    }

                    stmtSelect = new Stmt_Select();
                    stmtSelect.AddTable(table);
                    stmtSelect.AddAggregate(new Aggre_Count());
                    result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
                    Output("Count: " + result + " / 1");
                }
                finally
                {
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                Output("TestTransactions failed with an exception:");
                Output(ex);
            }
            finally
            {
                Output("");
                Output("");
            }
        }