static DBTable CreateTable(string name) { DBTable table = new DBTable(name); table.AddColumn(new DBColumn("OID", true, null, 0, DBColumnType.Int32) { IsIdentity = true }); table.AddColumn(new DBColumn("OptimisticLockField", false, null, 0, DBColumnType.Int32)); table.AddColumn(new DBColumn("GCRecord", false, null, 0, DBColumnType.Int32)); table.PrimaryKey = new DBPrimaryKey(new StringCollection() { "OID" }); return(table); }
void GetColumns(DBTable table) { foreach (SelectStatementResultRow row in SelectData(new Query(string.Format(CultureInfo.InvariantCulture, "show columns from `{0}`", ComposeSafeTableName(table.Name)))).Rows) { int size; string rowValue1, rowValue5, rowValue0; if (row.Values[1].GetType() == typeof(Byte[])) { rowValue1 = System.Text.Encoding.Default.GetString((byte[])row.Values[1]); rowValue5 = System.Text.Encoding.Default.GetString((byte[])row.Values[5]); rowValue0 = System.Text.Encoding.Default.GetString((byte[])row.Values[0]); } else { rowValue1 = (string)row.Values[1]; rowValue5 = (string)row.Values[5]; rowValue0 = (string)row.Values[0]; } DBColumnType type = GetTypeFromString(rowValue1, out size); bool isAutoIncrement = false; string extraValue = rowValue5; if (!string.IsNullOrEmpty(extraValue) && extraValue.Contains("auto_increment")) { isAutoIncrement = true; } var column = new DBColumn(rowValue0, false, String.Empty, type == DBColumnType.String ? size : 0, type) { IsIdentity = isAutoIncrement }; table.AddColumn(column); } }
public static DBTable CreateDbTable(string table, params ColumnInfo[] columnInfos) { var dbTable = new DBTable(table); if (columnInfos != null) foreach (var columnInfo in columnInfos) { dbTable.AddColumn(new DBColumn { Name = columnInfo.Name, ColumnType = columnInfo.ColumnType }); } return dbTable; }
static IDictionary <string, DBTable> CreateSchema(IDataStore dataStore) { DBTable customer = CreateTable("Customer"); customer.AddColumn(new DBColumn("FirstName", false, null, SizeAttribute.DefaultStringMappingFieldSize, DBColumnType.String)); customer.AddColumn(new DBColumn("LastName", false, null, SizeAttribute.DefaultStringMappingFieldSize, DBColumnType.String)); DBTable order = CreateTable("Order"); order.AddColumn(new DBColumn("ProductName", false, null, 200, DBColumnType.String)); order.AddColumn(new DBColumn("OrderDate", false, null, 0, DBColumnType.DateTime)); order.AddColumn(new DBColumn("Freight", false, null, 0, DBColumnType.Decimal)); order.AddColumn(new DBColumn("Customer", false, null, 0, DBColumnType.Int32)); order.AddForeignKey(new DBForeignKey(new StringCollection() { "Customer" }, "Customer", new StringCollection() { "OID" })); DBTable objectType = new DBTable("XPObjectType"); objectType.AddColumn(new DBColumn("OID", true, null, 0, DBColumnType.Int32) { IsIdentity = true }); objectType.AddColumn(new DBColumn("TypeName", false, null, 254, DBColumnType.String)); objectType.AddColumn(new DBColumn("AssemblyName", false, null, 154, DBColumnType.String)); objectType.AddIndex(new DBIndex(new StringCollection() { "TypeName" }, true)); objectType.PrimaryKey = new DBPrimaryKey(new StringCollection() { "OID" }); dataStore.UpdateSchema(false, customer, order, objectType); return(new Dictionary <string, DBTable>() { { "Customer", customer }, { "Order", order } }); }
void GetColumns(DBTable table) { string schema = ComposeSafeSchemaName(table.Name); Query query = schema == string.Empty ? new Query("select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @p1", new QueryParameterCollection(new OperandValue(ComposeSafeTableName(table.Name))), new[] { "@p1" }) : new Query("select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @p1 and TABLE_SCHEMA = @p2", new QueryParameterCollection(new OperandValue(ComposeSafeTableName(table.Name)), new OperandValue(schema)), new[] { "@p1", "@p2" }); foreach (SelectStatementResultRow row in SelectData(query).Rows) { int size = row.Values[2] != DBNull.Value ? ((IConvertible)row.Values[2]).ToInt32(CultureInfo.InvariantCulture) : 0; DBColumnType type = GetTypeFromString((string)row.Values[1], size); table.AddColumn(new DBColumn((string)row.Values[0], false, String.Empty, type == DBColumnType.String ? size : 0, type)); } }
public static DBTable CreateDbTable(string table, params ColumnInfo[] columnInfos) { var dbTable = new DBTable(table); if (columnInfos != null) { foreach (var columnInfo in columnInfos) { dbTable.AddColumn(new DBColumn { Name = columnInfo.Name, ColumnType = columnInfo.ColumnType }); } } return(dbTable); }
void GetColumns(DBTable table) { string schema = ComposeSafeSchemaName(table.Name); string safeTableName = ComposeSafeTableName(table.Name); Query query = schema == string.Empty ? new Query("SELECT COLUMN_NAME, DATA_TYPE, CHAR_COL_DECL_LENGTH, DATA_PRECISION, DATA_SCALE from USER_TAB_COLUMNS where TABLE_NAME = :p0", new QueryParameterCollection(new OperandValue(safeTableName)), new[] { ":p0" }) : new Query("SELECT COLUMN_NAME, DATA_TYPE, CHAR_COL_DECL_LENGTH, DATA_PRECISION, DATA_SCALE from ALL_TAB_COLUMNS where OWNER = :p0 and TABLE_NAME = :p1", new QueryParameterCollection(new OperandValue(schema), new OperandValue(safeTableName)), new[] { ":p0", ":p1" }); foreach (SelectStatementResultRow row in SelectData(query).Rows) { int size = row.Values[2] != DBNull.Value ? ((IConvertible)row.Values[2]).ToInt32(CultureInfo.InvariantCulture) : 0; int precision = row.Values[3] != DBNull.Value ? ((IConvertible)row.Values[3]).ToInt32(CultureInfo.InvariantCulture) : 0; int scale = row.Values[4] != DBNull.Value ? ((IConvertible)row.Values[4]).ToInt32(CultureInfo.InvariantCulture) : 0; DBColumnType type = GetTypeFromString((string)row.Values[1], size, precision, scale); table.AddColumn(new DBColumn((string)row.Values[0], false, String.Empty, type == DBColumnType.String ? size : 0, type)); } }
void GetColumns(DBTable table) { foreach (SelectStatementResultRow row in SelectData(new Query(string.Format(CultureInfo.InvariantCulture, "show columns from `{0}`", ComposeSafeTableName(table.Name)))).Rows) { int size; string rowValue1, rowValue5, rowValue0; if (row.Values[1].GetType() == typeof(Byte[])) { rowValue1 = System.Text.Encoding.Default.GetString((byte[])row.Values[1]); rowValue5 = System.Text.Encoding.Default.GetString((byte[])row.Values[5]); rowValue0 = System.Text.Encoding.Default.GetString((byte[])row.Values[0]); } else { rowValue1 = (string)row.Values[1]; rowValue5 = (string)row.Values[5]; rowValue0 = (string)row.Values[0]; } DBColumnType type = GetTypeFromString(rowValue1, out size); bool isAutoIncrement = false; string extraValue = rowValue5; if (!string.IsNullOrEmpty(extraValue) && extraValue.Contains("auto_increment")) isAutoIncrement = true; var column = new DBColumn(rowValue0, false, String.Empty, type == DBColumnType.String ? size : 0, type) {IsIdentity = isAutoIncrement}; table.AddColumn(column); } }
private void GetColumns(DBTable table) { string schema = ComposeSafeSchemaName(table.Name); string tableName = ComposeSafeTableName(table.Name); const string selectFields = @"select column_name, data_type, char_col_decl_length, data_precision, data_scale, case when data_type='NUMBER' and data_precision is null and data_scale is null then data_type when data_type='NUMBER' and data_precision is null and data_scale = 0 then 'INT' when data_type='NUMBER' then data_type||'('||data_precision||','||data_scale||')' when data_type in ('CHAR','NCHAR','VARCHAR2','NVARCHAR2') then data_type||'('||char_col_decl_length||')' when data_type='RAW' then data_type||'('||data_length||')' else data_type end full_type, nullable"; Query query = string.IsNullOrEmpty(schema) ? new Query(selectFields + " from user_tab_columns where table_name = :p0", new QueryParameterCollection(new OperandValue(tableName)), new string[] { ":p0" }) : new Query(selectFields + " from all_tab_columns where owner = :p0 and table_name = :p1", new QueryParameterCollection(new OperandValue(schema), new OperandValue(tableName)), new string[] { ":p0", ":p1" }); foreach (SelectStatementResultRow row in SelectData(query).Rows) { string name = (string)row.Values[0]; int size = row.Values[2] != DBNull.Value ? ((IConvertible)row.Values[2]).ToInt32(CultureInfo.InvariantCulture) : 0; int precision = row.Values[3] != DBNull.Value ? ((IConvertible)row.Values[3]).ToInt32(CultureInfo.InvariantCulture) : 0; int? scale = row.Values[4] != DBNull.Value ? ((IConvertible)row.Values[4]).ToInt32(CultureInfo.InvariantCulture) : (int?)null; DBColumnType type = GetTypeFromString((string)row.Values[1], size, precision, scale); string dbType = (string)row.Values[5]; string nullable = (string)row.Values[6]; DBColumn column = new DBColumn(name, false, dbType, type == DBColumnType.String ? size : 0, type); table.AddColumn(column); if (nullable == "N" && table is DBTableEx) ((DBTableEx)table).AddConstraint(new DBNotNullConstraint(column)); } }
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(""); } }
private void TestThread(ManualResetEvent exitEvent, Object userData) { try { ISQL builder; ISQLExecuter executer; IConnectionInfo connInfo; switch ((int)userData) { // VistaDB case 0: { String path = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location); path = Path.Combine(path, "VistaDB"); if (Directory.Exists(path)) { Directory.Delete(path, true); } builder = new VistaDB_SQL(); executer = new VistaDB_SQLExecuter((VistaDB_SQL)builder, path); connInfo = new VistaDB_ConnectionInfo(true); break; } // DB2 case 1: { builder = new DB2_SQL(); executer = new DB2_SQLExecuter((DB2_SQL)builder); connInfo = new DB2_ConnectionInfo("sei-backend", "testdb", "eisst", "EISSTEISST"); break; } default: return; } // Create and start the runner DBRunner runner = new DBRunner(); try { // Show provider IProvider prov = executer.Provider; Output("Provider: " + prov.Name + " - Version: " + prov.Version); Output(""); // Create the database and test tables DBDatabase db = new DBDatabase("DBTest.MyTest"); DBTable table1 = db.AddTable("Table1"); table1.AddColumn("uiNoegle", ColumnType.Guid, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); table1.AddColumn("txTekst", ColumnType.String, 20, ColumnFlag.None); table1.AddColumn("iTal", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); table1.AddColumn("dtDato", ColumnType.DateTime, ColumnFlag.IndexAsc); table1.AddColumn("sLilleTal", ColumnType.Small, ColumnFlag.None); table1.AddColumn("lStortTal", ColumnType.Long, ColumnFlag.NotNull); table1.AddColumn("txStorTekst", ColumnType.Clob, 32 * 1024, ColumnFlag.None); table1.AddColumn("bValg", ColumnType.Boolean, ColumnFlag.NotNull); table1.AddColumn("biBillede", ColumnType.Blob, 10 * 1024 * 1024, ColumnFlag.Compressed); table1.AddColumn("iAutoTaeller", ColumnType.Int, ColumnFlag.NotNull | ColumnFlag.Identity); DBTable table2 = db.AddTable("Table2"); table2.AddColumn("uiNoegle", ColumnType.Guid, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); table2.AddColumn("txTekst", ColumnType.String, 20, ColumnFlag.None); table2.AddColumn("iTal", ColumnType.Int, ColumnFlag.PrimaryKey | ColumnFlag.NotNull); table2.AddColumn("dtDato", ColumnType.DateTime, ColumnFlag.IndexAsc); table2.AddColumn("sLilleTal", ColumnType.Small, ColumnFlag.None); table2.AddColumn("lStortTal", ColumnType.Long, ColumnFlag.NotNull); table2.AddColumn("txStorTekst", ColumnType.Clob, 32 * 1024, ColumnFlag.None); table2.AddColumn("bValg", ColumnType.Boolean, ColumnFlag.NotNull); table2.AddColumn("biBillede", ColumnType.Blob, 10 * 1024 * 1024, ColumnFlag.Compressed); TestDatabase(runner, executer, db); TestConnection(runner, executer, db, connInfo); TestTable(runner, executer, builder, db, table2, connInfo); { Output("Create table again for other tests"); DBConnection conn = runner.OpenConnection(executer, db, connInfo); try { Stmt_CreateTable stmtCreate = new Stmt_CreateTable(table1); runner.CreateTable(executer, conn, stmtCreate); stmtCreate = new Stmt_CreateTable(table2); runner.CreateTable(executer, conn, stmtCreate); } finally { conn.Close(); Output(""); } } TestTable2(runner, executer, db, "Table1", connInfo); TestSmallInsert(runner, executer, builder, db, table1, connInfo); TestSmallSelect(runner, executer, builder, db, table1, connInfo); TestSmallDelete(runner, executer, builder, db, table1, connInfo); TestTransactions(runner, executer, db, table2, connInfo); TestUpdate(runner, executer, builder, db, table2, connInfo); TestFunctions(runner, executer, builder, db, table2, connInfo); TestUnion(runner, executer, builder, db, table1, table2, connInfo); TestSQLBuild(builder, db, table1); { Output("Dropping testing tables"); DBConnection conn = runner.OpenConnection(executer, db, connInfo); try { Stmt_DropTable stmtDrop = new Stmt_DropTable(table1); runner.DropTable(executer, conn, stmtDrop); stmtDrop = new Stmt_DropTable(table2); runner.DropTable(executer, conn, stmtDrop); } finally { conn.Close(); Output("Done"); } } } finally { runner.Close(); } } catch (Exception ex) { Output("Whole test failed with an exception:"); Output(ex); } }
private static void FindDocuments(DBTable pluginsTable, DBTable gruppePostkasseTable, DBTable revisionssporTable, DBRunner runner, ISQLExecuter executer, DBConnection connection, String plugin, params String[] tables) { Output(""); Output("Find plugin ID for " + plugin); SQL_SelectStatement sqlSelect = new SQL_SelectStatement(); sqlSelect.AddColumn(pluginsTable, "uiPluginID"); sqlSelect.AddCriteria(new Crit_MatchCriteria(pluginsTable, "txNavn", MatchType.Equal, plugin)); DBRow row = runner.SelectAndReturnFirstRow(executer, connection, sqlSelect); if (row == null) { throw new Exception("Couldn't find plugin ID"); } Guid pluginID = (Guid)row["uiPluginID"]; // Find all the document IDs Dictionary <Guid, Object> documentIDs = new Dictionary <Guid, Object>(); Output("Find document IDs for " + gruppePostkasseTable.TableName); sqlSelect = new SQL_SelectStatement(); sqlSelect.Distinct = true; sqlSelect.AddColumn(gruppePostkasseTable, "uiSkemaID"); sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID)); AddIDs(sqlSelect, runner, executer, connection, documentIDs); foreach (String tableName in tables) { Output("Find document IDs for " + tableName); DBTable table = pluginsTable.Database.AddTable("EISST", tableName); table.AddColumn("uiSkemaID", ColumnType.Guid, ColumnFlag.None); sqlSelect = new SQL_SelectStatement(); sqlSelect.AddColumn(table, "uiSkemaID"); AddIDs(sqlSelect, runner, executer, connection, documentIDs); } Output("Check the revisionsspor to see which documents are missing"); sqlSelect = new SQL_SelectStatement(); sqlSelect.Distinct = true; sqlSelect.AddColumn(revisionssporTable, "uiSkemaID"); sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiPluginID", MatchType.Equal, pluginID)); sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "iType", MatchType.Equal, (short)0)); sqlSelect.AddCriteria(new Crit_MatchCriteria(revisionssporTable, "uiSkemaID", MatchType.IsNotNull)); sqlSelect.AddSort(revisionssporTable, "uiSkemaID", Order.Ascending); using (DBReader reader = runner.GetReader(executer, connection, sqlSelect)) { while ((row = reader.GetNextRow()) != null) { Guid id = (Guid)row["uiSkemaID"]; if (!documentIDs.ContainsKey(id)) { Output(id.ToString("B").ToUpper()); } } } }