string GetSql(DBTable table, ConnectionProviderSql sqlDataStore, DBColumn column) {
return string.Format(CultureInfo.InvariantCulture,
"alter table {0} alter column {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column));
}
private void UpdateColumnSize(DBTable[] tables) {
// HACK: (FN) This is a temporary solution for http://www.devexpress.com/issue=S132075
foreach (var table in tables) {
DBTable actualTable = null;
foreach (var column in from col in table.Columns where col.ColumnType == DBColumnType.String select col) {
if (actualTable == null) {
actualTable = new DBTable(table.Name);
GetTableSchema(actualTable, false, false);
}
DBColumn dbColumn = column;
var actualColumn = actualTable.Columns.Find(col => string.Compare(col.Name, ComposeSafeColumnName(dbColumn.Name), true) == 0);
if ((actualColumn != null) &&
(actualColumn.ColumnType == DBColumnType.String) &&
(actualColumn.Size != column.Size) &&
(column.DBTypeName != string.Format("varchar({0})", actualColumn.Size))) {
if ((actualColumn.Size < column.Size) || (column.Size == DevExpress.Xpo.SizeAttribute.Unlimited)) {
ExecuteSqlSchemaUpdate("Column",
column.Name,
table.Name,
string.Format(CultureInfo.InvariantCulture,
"alter table {0} alter column {1} {2}",
FormatTableSafe(table),
FormatColumnSafe(column.Name),
GetSqlCreateColumnFullAttributes(table, column)));
} else
System.Diagnostics.Debug.Fail("The size of a DB column will not be decreased." +
" So changing the SizeAttribute of a column to have a smaller size than previously specified will have no effect.");
}
}
}
}
public MemberAttributeGenerator(MemberGeneratorInfo memberGeneratorInfo, ClassGeneratorInfo classGeneratorInfo) {
_objectSpace = XPObjectSpace.FindObjectSpaceByObject(memberGeneratorInfo.PersistentMemberInfo);
_persistentMemberInfo = memberGeneratorInfo.PersistentMemberInfo;
_column = memberGeneratorInfo.DbColumn;
_isPrimaryKey = CalculatePrimaryKey(memberGeneratorInfo, classGeneratorInfo);
_dbTable = classGeneratorInfo.DbTable;
}
string GetSql(DBTable table, ConnectionProviderSql sqlDataStore, DBColumn column) {
return string.Format(CultureInfo.InvariantCulture,
"alter table {0} {3} {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column),
(sqlDataStore is BaseOracleConnectionProvider || sqlDataStore is MySqlConnectionProvider) ? "modify" : "alter 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;
}
public void _Connects_To_Sample_Db()
{
_model = new DBTable(_connectionStringName, _testTableName, _tablePkColumn);
var cn = _model.OpenConnection();
Assert.True(cn != null && cn.State == System.Data.ConnectionState.Open);
cn.Close();
cn.Dispose();
}
public static DBTable GetTable(string tableName)
{
if (!DBTables.ContainsKey(tableName))
{
DBTables[tableName] = new DBTable(tableName);
}
return DBTables[tableName];
}
public override void GetTableSchema(DBTable table, bool checkIndexes, bool checkForeignKeys) {
base.GetTableSchema(table, checkIndexes, checkForeignKeys);
table.Columns.Clear();
GetColumns(table);
this.CallMethod("GetPrimaryKey",table);
if (checkIndexes)
this.CallMethod("GetIndexes", table);
if (checkForeignKeys)
this.CallMethod("GetForeignKeys", table);
}
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));
}
}
void CreateTransctionTable()
{
string sql = ""
+ "CREATE TABLE Transactions "
+ "(TransactionId int IDENTITY(1,1) PRIMARY KEY NOT NULL, "
+ "Amount Money NOT NULL, "
+ "Comment Text NOT NULL, "
+ "Identifier Text NOT NULL)";
var Model = new DBTable(CONNECTION_STRING_NAME);
Model.Execute(sql);
}
public bool TransactionTableExists()
{
bool exists = false;
string sql = ""
+ "SELECT * FROM INFORMATION_SCHEMA.TABLES "
+ "WHERE TABLE_SCHEMA = 'dbo' "
+ "AND TABLE_NAME = 'Transactions'";
var Model = new DBTable(CONNECTION_STRING_NAME);
var query = Model.Query(sql);
if (query.Count() > 0)
{
exists = true;
}
return exists;
}
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));
}
}
public void _Deletes_Typed_Record()
{
_setup.CheckSetUp();
var model = new DBTable(_connectionStringName, _testTableName, _tablePkColumn);
var newRecord = new Transaction()
{
Amount = 100,
Comment = "I Overspent!",
Identifier = "XXX"
};
model.Insert(newRecord);
int recordPk = newRecord.TransactionId;
newRecord = model.Find<Transaction>(recordPk);
int deleted = model.Delete(newRecord.TransactionId);
newRecord = model.Find(recordPk);
Assert.True(deleted > 0 && newRecord == null);
}
private void UpdateColumnSize(IEnumerable<DBTable> tables, ConnectionProviderSql sqlDataStore) {
foreach (var table in tables) {
DBTable actualTable = null;
foreach (var column in table.Columns.Where(col => col.ColumnType == DBColumnType.String)) {
if (actualTable == null) {
actualTable = new DBTable(table.Name);
sqlDataStore.GetTableSchema(actualTable, false, false);
}
DBColumn dbColumn = column;
var actualColumn = actualTable.Columns.Find(col => string.Compare(col.Name, sqlDataStore.ComposeSafeColumnName(dbColumn.Name), true) == 0);
if (NeedsAltering(column, actualColumn)) {
if ((actualColumn.Size < column.Size) || (column.Size == DevExpress.Xpo.SizeAttribute.Unlimited)) {
var sql = GetSql(table, sqlDataStore, column);
System.Diagnostics.Trace.WriteLineIf(new System.Diagnostics.TraceSwitch("XPO", "").TraceInfo, sql);
sqlDataStore.ExecSql(new Query(sql));
}
}
}
}
}
public void _Deletes_Anonymous_Record()
{
_setup.CheckSetUp();
var model = new DBTable(_connectionStringName, _testTableName, _tablePkColumn);
var newRecord = new
{
Amount = 100,
Comment = "I Anonymously Overspent!",
Identifier = "YYZ" // Bah da-bah-bah-bah da bah-bah-bah-bah
};
var result = model.Insert(newRecord);
int recordPk = result.TransactionId;
// Retrieve the updated item from the Db:
var recordToDelete = model.Find(recordPk);
int deleted = model.Delete(recordToDelete.TransactionId);
recordToDelete = model.Find(recordPk);
Assert.True(deleted > 0 && recordToDelete == null);
}
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);
}
}
/// <summary>
/// Создание ссылки на другую таблицу
/// </summary>
/// <param name="table">Таблица со ссылкой</param>
/// <param name="fk">Ссылка на другую таблицу</param>
public override void CreateForeignKey(DBTable table, DBForeignKey fk)
{
// Если ссылка на таблицу с настраиваемой модификацией, то пропускаем
DBTableEx foreignTableEx = XPDictionaryInformer.Schema.GetTable(fk.PrimaryKeyTable);
if (foreignTableEx.IsCustom) return;
// Если ссылка на таблицу из другой схемы, то необходимо дать разрешение
string tableSchema = FormatRealSchemaName(table.Name);
string foreignSchema = FormatRealSchemaName(fk.PrimaryKeyTable);
if (tableSchema != foreignSchema)
{
string foreignTable = FormatTable(foreignSchema, ComposeSafeTableName(fk.PrimaryKeyTable));
ExecuteSqlSchemaUpdate("ForeignKey", GetForeignKeyName(fk, table), table.Name,
String.Format(CultureInfo.InvariantCulture, "grant references on {0} to {1}", foreignTable, tableSchema));
}
base.CreateForeignKey(table, fk);
}
private void frmPartnersEdit_Load(object sender, EventArgs e)
{
bool bResult = true;
oPartner.ID = nPartnerID;
// классификаторы
bResult = cboZone_Restore() && cboPermitLevel_Restore(); /*&& cboPartnerRoot_Restore();*/
if (bResult)
{
cboPartnerRoot.SelectedIndex =
cboZone.SelectedIndex =
cboPermitLevel.SelectedIndex =
-1;
if (nPartnerID.HasValue && (int)nPartnerID != 0)
{
// существующий клиент
bResult = oPartner.ReFillOne((int)nPartnerID) && (oPartner.ErrorNumber == 0);
if (bResult)
{
Text += " (код " + nPartnerID.ToString() + ")";
txtNаme.Text = oPartner.Name;
chkActual.Checked = oPartner.Actual;
chkIsCustomer.Checked = oPartner.IsCustomer;
chkIsSupplier.Checked = oPartner.IsSupplier;
chkIsTransport.Checked = oPartner.IsTransport;
chkIsOwner.Checked = oPartner.IsOwner;
chkSeparatePicking.Checked = oPartner.SeparatePicking;
chkIsOwner_CheckedChanged(null, null);
txtContactsInfo.Text = oPartner.ContactsInfo;
if (oPartner.PartnerRootID.HasValue)
{
cboPartnerRoot.SelectedValue = oPartner.PartnerRootID;
}
if (oPartner.ZoneID.HasValue)
{
cboZone.SelectedValue = oPartner.ZoneID;
}
if (oPartner.PermitLevelID.HasValue)
{
cboPermitLevel.SelectedValue = oPartner.PermitLevelID;
}
txtDeliveryAddress.Text = oPartner.DeliveryAddress;
txtDeliveryPassage.Text = oPartner.DeliveryPassage;
txtDeliveryRestriction.Text = oPartner.DeliveryRestriction;
txtGravity.Text = oPartner.Gravity;
txtNote.Text = oPartner.Note;
// CoordinatesX, CoordinatesY
txtLatitude.Text = (oPartner.Latitude.HasValue ? oPartner.Latitude.ToString() : "");
txtLongitude.Text = (oPartner.Longitude.HasValue ? oPartner.Longitude.ToString() : "");
numDistance.Value = oPartner.Distance;
chkPassengerCarOnly.Checked = oPartner.PassengerCarOnly;
chkPosternOnly.Checked = oPartner.PosternOnly;
chkLoadLiftOnly.Checked = oPartner.LoadLiftOnly;
// покажем верхнюю запись из PartnersDetail
oPartner.FillTablePartnersDetails((int)oPartner.ID);
if (oPartner.TablePartnersDetails.Rows.Count > 0)
{
DataRow p = oPartner.TablePartnersDetails.Rows[0];
txtLegalName.Text = p["LegalName"].ToString();
txtLegalAddress.Text = p["LegalAddress"].ToString();
txtFactName.Text = p["FactName"].ToString();
txtFactAddress.Text = p["FactAddress"].ToString();
txtPhone.Text = p["Phone"].ToString();
txtInn.Text = p["Inn"].ToString();
txtKpp.Text = p["Kpp"].ToString();
txtContractNumber.Text = p["ContractNumber"].ToString();
if (!Convert.IsDBNull(p["ContractDate"]))
{
dtpContractDate.Value = Convert.ToDateTime(p["ContractDate"]);
}
else
{
dtpContractDate.HideControl(false);
}
txtBankName.Text = p["BankName"].ToString();
txtBankAddress.Text = p["BankAddress"].ToString();
txtPAccount.Text = p["PAccount"].ToString();
txtCAccount.Text = p["CAccount"].ToString();
txtBik.Text = p["Bik"].ToString();
txtOkonh.Text = p["Okonh"].ToString();
txtOkpo.Text = p["Okpo"].ToString();
txtJobTitleDirector.Text = p["JobTitleDirector"].ToString();
txtFioDirector.Text = p["FioDirector"].ToString();
txtJobTitleAccountant.Text = p["JobTitleAccountant"].ToString();
txtFioAccountant.Text = p["FioAccountant"].ToString();
}
//
// стр.Дополнительно
chkDateValidControl.Checked = oPartner.DateValidControl;
chkPalletsAgreement.Checked = oPartner.PalletsAgreement;
chkFactPaymentOnly.Checked = oPartner.FactPaymentOnly;
txtTimeWork.Text = oPartner.TimeWork;
txtTimeReceipt.Text = oPartner.TimeReceipt;
txtTimePay.Text = oPartner.TimePay;
txtTimeRest.Text = oPartner.TimeRest;
numStayMinutes.Value = oPartner.StayMinutes;
// стр.Шаблоны
if (oPartner.PF_BillName != null && oPartner.PF_BillName.Length > 0)
{
txtPF_BillName.Text = oPartner.PF_BillName;
}
numPF_BillCopiesCount.Value = oPartner.PF_BillCopiesCount;
chkPF_Bill.Checked = txtPF_BillName.Text.Trim().Length > 0 || numPF_BillCopiesCount.Value > 0;
if (oPartner.PF_FactureName != null && oPartner.PF_FactureName.Length > 0)
{
txtPF_FactureName.Text = oPartner.PF_FactureName;
}
numPF_FactureCopiesCount.Value = oPartner.PF_FactureCopiesCount;
chkPF_Facture.Checked = txtPF_FactureName.Text.Trim().Length > 0 || numPF_FactureCopiesCount.Value > 0;
if (oPartner.PF_PayBillName != null && oPartner.PF_PayBillName.Length > 0)
{
txtPF_PayBillName.Text = oPartner.PF_PayBillName;
}
numPF_PayBillCopiesCount.Value = oPartner.PF_PayBillCopiesCount;
chkPF_PayBill.Checked = txtPF_PayBillName.Text.Trim().Length > 0 || numPF_PayBillCopiesCount.Value > 0;
txtShopCode.Text = oPartner.ShopCode;
// стр.Ветеринария
txtVeterinaryPartnerName.Text = oPartner.VeterinaryPartnerName;
txtVeterinaryPermission.Text = oPartner.VeterinaryPermission;
// not used
/*
* chkFactureNeed.Checked = oPartner.FactureNeed;
* chkWarrantNeed.Checked = oPartner.WarrantNeed;
* chkPaymentOrderNeed.Checked = oPartner.PaymentOrderNeed;
* chkOtherDocumentsNeed.Checked = oPartner.OtherDocumentsNeed;
* chkQualityLicenceNeed.Checked = oPartner.QualityLicenceNeed;
* chkVeterinaryLicenceNeed.Checked = oPartner.VeterinaryLicenceNeed;
*/
}
if (oPartner.DrivingScheme != null && !Convert.IsDBNull(oPartner.DrivingScheme))
{
byte[] bsPhoto = oPartner.DrivingScheme;
MemoryStream ms = new MemoryStream(bsPhoto);
try
{
picDrivingScheme.Image = new Bitmap(ms);
}
catch (Exception ex)
{
RFMMessage.MessageBoxError("Ошибка загрузки схемы проезда: " + ex.Message);
}
}
}
else
{
// новый клиент
chkActual.Checked = true;
}
// только просмотр
if (bNoEdit)
{
RFMPanelGlass pnlGlass = new RFMPanelGlass();
pnlGlass.Top = pnlData.Top;
pnlGlass.Left = pnlData.Left;
pnlGlass.Width = pnlData.Width;
pnlGlass.Height = pnlData.Height;
Controls.Add(pnlGlass);
pnlGlass.BringToFront();
btnSave.Select();
btnSave.Enabled = false;
Text += " - просмотр";
}
else
{
// доступность полей
DBTable dbTable = new DBTable();
foreach (Control oControlTemp in Controls)
{
ControlEnable(dbTable, oControlTemp);
}
}
chkPF_Bill_CheckedChanged(null, null);
chkPF_Facture_CheckedChanged(null, null);
chkPF_PayBill_CheckedChanged(null, null);
}
if (!bResult)
{
Dispose();
}
bLoaded = true;
}
public static void Delete <T>(Expression <Func <T, bool> > predicate, DBTable collectionName)
{
db.GetCollection <T>(collectionName.ToString()).DeleteMany(predicate);
}
protected override JsonObjectContract CreateObjectContract(Type objectType)
{
if (TypeHelper.IsBaseType(objectType, typeof(DBItem)))
{
var table = DBTable.GetTable(objectType);
if (table != null)
{
var result = new JsonObjectContract(objectType)
{
Converter = DBItemConverter
};
foreach (var column in table.Columns.Where(p => TypeHelper.IsBaseType(p.PropertyInfo?.DeclaringType, objectType)))
// && (p.Attribute.Keys & DBColumnKeys.System) != DBColumnKeys.System
{
if (column.PropertyInfo.PropertyType == typeof(AccessValue))
{
var accessProperty = new JsonProperty
{
DeclaringType = objectType,
DefaultValue = null,
Order = column.Order,
PropertyName = column.Property,
PropertyType = typeof(AccessType?),
ValueProvider = column.PropertyInvoker,
Ignored = true
};
result.Properties.Add(accessProperty);
continue;
}
var jsonProperty = new JsonProperty
{
DeclaringType = objectType,
DefaultValue = column.DefaultValues != null && column.DefaultValues.TryGetValue(objectType, out var defaultValue)
? defaultValue : null,
Order = column.Order,
PropertyName = column.Property,
PropertyType = column.PropertyInfo.PropertyType,
ValueProvider = column.PropertyInvoker,
Ignored = column.ColumnType != DBColumnTypes.Default || (column.Keys & DBColumnKeys.Access) == DBColumnKeys.Access
};
result.Properties.Add(jsonProperty);
if (column.ReferencePropertyInfo != null)
{
jsonProperty = base.CreateProperty(column.ReferencePropertyInfo, MemberSerialization.OptIn);
jsonProperty.IsReference = true;
jsonProperty.ValueProvider = column.ReferencePropertyInvoker;
jsonProperty.NullValueHandling = NullValueHandling.Ignore;
result.Properties.Add(jsonProperty);
}
}
return(result);
}
}
var contract = base.CreateObjectContract(objectType);
if (objectType.IsEnum)
{
contract.Converter = StringConverter;
}
return(contract);
}
}
public Dictionary<IDataStore, List<DBTable>> GetDataStores(DBTable[] dbTables){
var dictionary = _simpleDataLayers.Select(pair =>
pair.Value.ConnectionProvider).ToDictionary(dataStore => dataStore, dataStore => new List<DBTable>());
foreach (var dbTable in dbTables) {
if (dbTable.Name == "XPObjectType")
foreach (var simpleDataLayer in _simpleDataLayers) {
dictionary[simpleDataLayer.Value.ConnectionProvider].Add(dbTable);
}
else
try {
dictionary[_simpleDataLayers[GetKey(dbTable.Name)].ConnectionProvider].Add(dbTable);
}
catch (Exception e) {
Console.WriteLine(e);
}
}
return dictionary;
}
/// <summary>
/// Создает указанную таблицу
/// </summary>
/// <param name="table">Таблица</param>
public override void CreateTable(DBTable table)
{
ExecuteSqlSchemaUpdate("Table", table.Name, string.Empty,
new OracleTemplater(this, table).CreateTableCommand(col => GetSqlCreateColumnFullAttributes(table, col)));
}
private void TestTable(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestTable:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
String name = table.TableName;
bool result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
Output("");
Output("Create table " + name);
Stmt_CreateTable stmtCreate = new Stmt_CreateTable(table);
Output(builder.ToSQL(stmtCreate));
runner.CreateTable(executer, conn, stmtCreate);
Output("Table created");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / True");
Output("");
if (result)
{
Output("Drop table " + name);
Stmt_DropTable stmtDrop = new Stmt_DropTable(table);
Output(builder.ToSQL(stmtDrop));
runner.DropTable(executer, conn, stmtDrop);
Output("Table dropped");
Output("");
result = runner.TableExists(executer, conn, name);
Output("Table " + name + " exists: " + result + " / False");
}
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestTable 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);
}
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message)
{
// init
DBConnection db = COREobject.i.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
if (!vars.ContainsKey("TableName"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: TableName is required");
}
if (!vars.ContainsKey("BaseName"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: BaseName is required");
}
if (!vars.ContainsKey("Data"))
{
throw new Exception("Tapestry action JSON 2 DBItemList: Data is required");
}
JToken data = (JToken)vars["Data"];
string tableName = (string)vars["TableName"];
string baseName = (string)vars["BaseName"];
string itemName = vars.ContainsKey("ItemName") ? (string)vars["ItemName"] : "item";
/****************************************************************************************
** MOCKUP DATA **
*****************************************************************************************
* string jsonText;
* try {
* XmlDocument xml = new XmlDocument();
* xml.Load("c:/users/mnvk8/Downloads/response.xml");
* jsonText = JsonConvert.SerializeXmlNode(xml);
* }
* catch (Exception e) {
* if (e is ArgumentNullException || e is XmlException) {
* jsonText = "";// JsonConvert.SerializeObject(response);
* }
* else {
* throw e;
* }
* }
* JToken data = JToken.Parse(jsonText);
****************************************************************************************/
DBTable table = db.Table(tableName, searchInShared);
Dictionary <string, DBColumn> columnExists = new Dictionary <string, DBColumn>();
Dictionary <string, DbType> columnType = new Dictionary <string, DbType>();
var items = data.SelectToken($"$..{baseName}.{itemName}");
foreach (JToken item in items)
{
DBItem entity = new DBItem(db, table);
foreach (JProperty pair in item)
{
// Zjistíme, jestli ten slupec v tabulce vůbec existuje
string columnName = pair.Name.ToLowerInvariant();
if (!columnExists.ContainsKey(columnName))
{
DBColumn column = table.Columns.Where(c => c.Name.ToLowerInvariant() == columnName).FirstOrDefault();
columnExists.Add(columnName, column);
if (column != null)
{
columnType.Add(columnName, column.Type);
}
}
if (columnExists[columnName] != null)
{
var columnInfo = columnExists[columnName];
entity[columnInfo.Name] = DataType.ConvertTo(columnType[columnName], pair);
}
}
table.Add(entity);
}
db.SaveChanges();
// return
outputVars["Result"] = true;
}
public DbTable(DBTable table, IDbDataStore store)
: base(store)
{
DBTable = table;
}
private void generateChart()
{
DataTable[] rawDataTables;
double?yMin = null, yMax = null, y2Min = null, y2Max = null;
imageMapAreas = new StringBuilder();
preSetup();
if (_rcp.Datasources.Length == 0)
{
drawMessage(_noDatasourcesMessage);
finalizeChart();
return;
}
if (_start == _end)
{
drawMessage("There is no data available for selected options.");
finalizeChart();
return;
}
//Get the data from the database
rawDataTables = retrieveData(_rcp.Datasources, _start, _end);
//Set the scale to be measured in days
//Remove the auto-generated labels
_chart.xAxis().setLinearScale(Chart.CTime(_timeZone.ToLocalTime(_start).Date),
Chart.CTime(_timeZone.ToLocalTime(_end).Date.AddDays(1)), null);
addTimestampLabels(_start, _end);
for (int i = 0; i < rawDataTables.Length; i++)
{
DBTable tableHelper;
DateTime[] timestamps;
double[] values;
string[] tooltips;
DisplayDatasourceItem currDataItem;
ConfiguredDatasource currConfiguredDatasource;
Color currLineColor;
string currDataName;
currDataItem = _rcp.Datasources[i];
currConfiguredDatasource = _db.ORManager.Get <ConfiguredDatasource>(currDataItem.ConfiguredDatasourceId);
//If the datasource doesn't exist, don't process it
if (currConfiguredDatasource == null)
{
continue;
}
currDataName = currConfiguredDatasource.Name;
if (rawDataTables[i].ExtendedProperties.Contains("SubTypeId"))
{
DatasourceSubType currSubType;
int subTypeId;
subTypeId = (int)rawDataTables[i].ExtendedProperties["SubTypeId"];
currSubType = _db.ORManager.Get <DatasourceSubType>(subTypeId);
currDataName += " - " + currSubType.Name;
}
tableHelper = new DBTable(rawDataTables[i]);
timestamps = tableHelper.getColAsDateTime(0);
values = tableHelper.getCol(1);
//Adjust the timestamps for the users time zone
for (int j = 0; j < timestamps.Length; j++)
{
timestamps[j] = _timeZone.ToLocalTime(timestamps[j]);
}
//Create an array of strings that represent the tooltips
tooltips = new string[timestamps.Length];
for (int j = 0; j < timestamps.Length; j++)
{
tooltips[j] = getDatapointTooltip(currConfiguredDatasource, timestamps[j], values[j]);
}
//Determine the color to use
if (currDataItem.Color == null)
{
currLineColor = AutoLineColors[i % AutoLineColors.Length];
}
else
{
currLineColor = (Color)currDataItem.Color;
}
bool useYAxis2;
useYAxis2 = (rawDataTables.Length == 2 && i == 1);
//Hide the axis labels when displaying 3 or more data sources
if (rawDataTables.Length > 2)
{
_chart.yAxis().setLabelFormat("");
}
Axis axis = null;
//If there are more than 2 data sources, display them on different axis
if (rawDataTables.Length > 2)
{
axis = _chart.addAxis(Chart.Left, 0);
axis.setLabelFormat("");
}
//Keep a running tab of the overall min and max values
if (rawDataTables.Length <= 2)
{
if (!useYAxis2)
{
double?currYMin, currYMax;
ChartMath.GetDataBounds(values, out currYMin, out currYMax);
if (yMin == null && currYMin != null)
{
yMin = currYMin;
}
else if (currYMin != null)
{
yMin = Math.Min(currYMin.Value, yMin.Value);
}
if (yMax == null && currYMax != null)
{
yMax = currYMax;
}
else if (currYMax != null)
{
yMax = Math.Min(currYMax.Value, yMax.Value);
}
}
else
{
double?currYMin, currYMax;
ChartMath.GetDataBounds(values, out currYMin, out currYMax);
if (y2Min == null && currYMin != null)
{
y2Min = currYMin;
}
else if (currYMin != null)
{
y2Min = Math.Min(currYMin.Value, y2Min.Value);
}
if (y2Max == null && currYMax != null)
{
y2Max = currYMax;
}
else if (currYMax != null)
{
y2Max = Math.Min(currYMax.Value, y2Max.Value);
}
}
}
if (currDataItem.ShowRaw)
{
LineLayer lineLayer;
lineLayer = _chart.addLineLayer2();
lineLayer.addDataSet(values, Chart.CColor(currLineColor), fixLayerNameWidth(currDataName)).setDataSymbol(
Chart.DiamondSymbol, 4, 0xffff80);
lineLayer.setXData(timestamps);
lineLayer.addExtraField(tooltips); //field0
lineLayer.setLineWidth(currDataItem.LineThickness);
if (axis == null)
{
lineLayer.setUseYAxis2(useYAxis2);
}
else
{
lineLayer.setUseYAxis(axis);
}
}
if (currDataItem.ShowTrendLine)
{
TrendLayer tl;
tl =
_chart.addTrendLayer(values, _chart.dashLineColor(Chart.CColor(currLineColor), Chart.DashLine),
fixLayerNameWidth(currConfiguredDatasource.Name + " Trend"));
tl.setLineWidth(currDataItem.LineThickness + 1);
tl.setXData(timestamps);
tl.addExtraField(new string[timestamps.Length]); //field0
if (axis == null)
{
tl.setUseYAxis2(useYAxis2);
}
else
{
tl.setUseYAxis(axis);
}
}
if (currDataItem.ShowLowess)
{
SplineLayer sl;
sl =
_chart.addSplineLayer(new ArrayMath(values).lowess().result(),
_chart.dashLineColor(Chart.CColor(currLineColor), Chart.DashLine),
fixLayerNameWidth(currDataName));
sl.setLineWidth(currDataItem.LineThickness + 1);
sl.setXData(timestamps);
sl.addExtraField(new string[timestamps.Length]); //field0
if (axis == null)
{
sl.setUseYAxis2(useYAxis2);
}
else
{
sl.setUseYAxis(axis);
}
}
}
optimizePlotArea(yMin, yMax, y2Min, y2Max);
//Calculate the scale of the chart based on the plotted data,
//this needs to be called before drawEvents, so the tool tip
//locations for the events can be calculated.
_chart.layout();
drawEvents();
drawLegend();
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
COREobject core = COREobject.i;
db = core.Entitron;
object diffObject = vars["Diff"];
string tableNames = vars.ContainsKey("TablesNames") ? (string)vars["TablesNames"] : "";
extIdColumnName = vars.ContainsKey("ExtIdColumnName") ? (string)vars["ExtIdColumnName"] : "ext_id";
diffIdColumnName = vars.ContainsKey("DiffIdColumnName") ? (string)vars["DiffIdColumnName"] : "id";
isDeletedColumnName = vars.ContainsKey("IsDeletedColumnName") ? (string)vars["IsDeletedColumnName"] : "";
if (diffObject == null)
{
throw new Exception($"{Name}: Diff must not be null");
}
if (!(diffObject is string) && !(diffObject is JToken))
{
throw new Exception($"{Name}: Diff must be string or JToken");
}
try {
JToken diff = diffObject is JToken ? (JToken)diffObject : JToken.Parse((string)diffObject);
List <string> tableNamesList = tableNames.Split(new char[] { ',', ';' }).ToList();
string tableName = (string)diff["table"];
if (string.IsNullOrEmpty(tableNames) || tableNamesList.Contains(tableName)) // Zjistíme, jestli synchronizujeme všechny tabulky, nebo jen vybrané
{
table = db.Table(tableName);
if (table == null)
{
throw new Exception($"{Name}: table {tableName} was not found");
}
RethinkDiffType changeType = GetDiffType(diff["old_val"], diff["new_val"]); // Zjistíme typ změny
switch (changeType)
{
case RethinkDiffType.INSERT:
Insert(diff["new_val"]);
break;
case RethinkDiffType.UPDATE:
Update(diff["old_val"], diff["new_val"]);
break;
case RethinkDiffType.DELETE:
Delete(diff["old_val"]);
break;
}
try {
db.SaveChanges();
outputVars["Result"] = 1;
outputVars["Error"] = "";
}
catch (Exception e) {
outputVars["Result"] = 0;
outputVars["Error"] = e.Message;
}
}
else // Pokud synchronizujeme jen vybrané tabulky a tato v nich není, vracíme -1 = ignorovaná tabulka
{
outputVars["Result"] = -1;
outputVars["Error"] = "";
}
}
catch (Exception e) {
throw new Exception($"{Name}: Fatal error occured ({e.Message})");
}
}
public void StatementCreatorOrderTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List <DBColumn>()
{
col1, col2, col3
};
Database db = new Database("TestDB", new List <DBTable>()
{
table
});
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col2, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ErrorHandling errorHandling = new ErrorHandling();
ImportConfiguration config = new ImportConfiguration(new TableMapping[] { tableMapping }, null, "TestDB", errorHandling);
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(config, dt);
ImportStatement statement = statementCreator.CreateStatement(0);
ImportStatement[] statements = statementCreator.CreateStatements();
Assert.AreEqual(1, statements.Length);
Assert.AreEqual(statement.RowReference, statements[0].RowReference);
Assert.AreEqual(statement.SqlStatement, statements[0].SqlStatement);
string[] lines = statement.SqlStatement
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
StatementSetupPart setupPart = new StatementSetupPart(config);
Assert.AreEqual(setupPart.GetDatabasePart(), lines[0]);
Assert.AreEqual(setupPart.GetWarningsPart(), lines[1]);
StatementTransactionPart transPart = new StatementTransactionPart(config);
string[] transStartPart = transPart.GetTransactionStartPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();;
Assert.AreEqual(2, transStartPart.Length);
Assert.AreEqual(transStartPart[0], lines[2]);
Assert.AreEqual(transStartPart[1], lines[3]);
StatementTableMappingPart tmParts = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string variablePart = tmParts.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual(variablePart, lines[4]);
string[] bodyParts = tmParts.GetStatementBodyPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual(bodyParts[0], lines[5]);
Assert.AreEqual(bodyParts[1], lines[6]);
Assert.AreEqual(bodyParts[2], lines[7]);
Assert.AreEqual(bodyParts[3], lines[8]);
string[] transEndPart = transPart.GetTransactionEndPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(12, transEndPart.Length);
Assert.AreEqual(transEndPart[0], lines[9]);
Assert.AreEqual(transEndPart[1], lines[10]);
Assert.AreEqual(transEndPart[2], lines[11]);
Assert.AreEqual(transEndPart[3], lines[12]);
Assert.AreEqual(transEndPart[4], lines[13]);
Assert.AreEqual(transEndPart[5], lines[14]);
Assert.AreEqual(transEndPart[6], lines[15]);
Assert.AreEqual(transEndPart[7], lines[16]);
Assert.AreEqual(transEndPart[8], lines[17]);
Assert.AreEqual(transEndPart[9], lines[18]);
Assert.AreEqual(transEndPart[10], lines[19]);
Assert.AreEqual(transEndPart[11], lines[20]);
}
/// <summary>
/// Создание или пересоздание пакета процедур на основе указанной таблицы
/// </summary>
/// <param name="table">Таблица, на основе которой создается пакет процедур</param>
public virtual void CreateOrReplacePackage(DBTable table)
{
string package = FormatPackage(table);
string script = new OracleTemplater(this, table).CreateStandartPackageCommand(package,
col => GetSqlProcedureParameterType(table, col));
// Проверка изменений
bool skip = false;
if ((updateOptions & UpdateSchemaOptions.CheckSafeContent) == UpdateSchemaOptions.CheckSafeContent)
{
string body = null;
foreach (string command in ParseScript(script))
if (command != null && command.ToUpper().Contains("PACKAGE BODY")) { body = command; break; }
skip = CompareSafeContent(body, GetPackageBody(table), true);
}
// Выполнение скрипта
if (!skip)
{
if (scriptMode)
ExecuteSqlSchemaUpdate("Package", package, table.Name, script);
else
foreach (string command in ParseScript(script))
ExecuteSqlSchemaUpdate("Package and procedures", package, table.Name, command);
}
}
/// <summary>
/// Создание первичного ключа для указанной таблицы
/// </summary>
/// <param name="table">Таблица, для которой создается первичный ключ</param>
public override void CreatePrimaryKey(DBTable table)
{
if (table.PrimaryKey == null) return;
base.CreatePrimaryKey(table);
}
private void TestSmallInsert(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallInsert:");
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);
Output("Row inserted");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallInsert failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
/// <summary>Получить название представления для sql-запроса для указанной таблицы</summary>
/// <param name="table">Таблица</param>
/// <returns>Название представления таблицы <paramref name="table"/> в формате sql-запроса</returns>
public string FormatView(DBTable table)
{
return FormatView(ComposeSafeSchemaName(table.Name), ComposeSafeTableName(table.Name));
}
private void TestSmallDelete(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table, IConnectionInfo connInfo)
{
Output("TestSmallDelete:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Delete all rows");
Stmt_Delete stmtDelete = new Stmt_Delete(table);
Output(builder.ToSQL(stmtDelete));
runner.Delete(executer, conn, stmtDelete);
Output("Rows deleted");
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddTable(table);
stmtSelect.AddAggregate(new Aggre_Count());
long result = runner.SelectWithSingleAggregate(executer, conn, stmtSelect);
Output("Count: " + result + " / 0");
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestSmallDelete failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
public static CriteriaOperator Transform(CriteriaOperator criteria, DBTable table, string alias)
{
return(new SimpleDataStoreCriteriaVisitor(table, alias).Process(criteria));
}
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("");
}
}
public ClassGeneratorInfo(IPersistentClassInfo persistentClassInfo, DBTable dbTable)
: this()
{
PersistentClassInfo = persistentClassInfo;
DbTable = dbTable;
}
private void ShowContents(DBRunner runner, ISQLExecuter executer, DBConnection conn, DBTable table)
{
Output("Display contents of table " + table.TableName);
Stmt_Select stmtSelect = new Stmt_Select();
stmtSelect.AddAllColumns(table);
ShowContents(stmtSelect, runner, executer, conn);
}
/// <summary>
/// Возвращает функциональное тело триггера для таблицы.
/// </summary>
/// <param name="table">Таблица.</param>
/// <returns></returns>
protected internal virtual string GetBody(DBTable table)
{
throw new NotSupportedException();
}
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("");
}
}
/// <summary>
/// Возвращает функциональное тело триггера для таблицы основных данных.
/// </summary>
/// <param name="principalTable">Таблица основных данных.</param>
/// <returns></returns>
protected internal virtual string GetBody(DBPrincipalTable principalTable)
{
DBTable baseTable = principalTable;
return(this.GetBody(baseTable));
}
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("");
}
}
public MemberGenerator(DBTable dbTable, Dictionary <string, ClassGeneratorInfo> classInfos)
{
_dbTable = dbTable;
_classInfos = classInfos;
_objectSpace = XPObjectSpace.FindObjectSpaceByObject(classInfos[classInfos.Keys.First()].PersistentClassInfo);
}
private void TestUnion(DBRunner runner, ISQLExecuter executer, ISQL builder, DBDatabase db, DBTable table1, DBTable table2, IConnectionInfo connInfo)
{
Output("TestUnion:");
Output("");
try
{
DBConnection conn = runner.OpenConnection(executer, db, connInfo);
try
{
Output("Insert more test rows");
Stmt_Insert stmtInsert = new Stmt_Insert(table1);
stmtInsert.AddColumns("uiNoegle", "txTekst", "iTal", "lStortTal", "dtDato", "bValg");
stmtInsert.AddValues(Guid.NewGuid(), "Giv mig en bog!", 123, (long)213142566, DateTime.Now, true);
Output(builder.ToSQL(stmtInsert));
runner.Insert(executer, conn, stmtInsert);
Output("Rows inserted");
Output("");
ShowContents(runner, executer, conn, table1);
Output("");
ShowContents(runner, executer, conn, table2);
Output("");
Stmt_Select stmtSelect1 = new Stmt_Select();
stmtSelect1.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtSelect2 = new Stmt_Select();
stmtSelect2.AddColumns(table2, "uiNoegle", "txTekst", "iTal", "lStortTal");
Stmt_Select stmtUnion = new Stmt_Select();
stmtUnion.AddColumns(table1, "uiNoegle", "txTekst", "iTal", "lStortTal");
stmtUnion.AddUnion(stmtSelect1);
stmtUnion.AddUnion(stmtSelect2);
stmtUnion.AddSort(table1, "iTal", Order.Ascending);
Output(builder.ToSQL(stmtUnion));
ShowContents(stmtUnion, runner, executer, conn);
}
finally
{
conn.Close();
}
}
catch (Exception ex)
{
Output("TestUpdate failed with an exception:");
Output(ex);
}
finally
{
Output("");
Output("");
}
}
/// <summary>
/// Создание индекса для таблицы
/// </summary>
/// <param name="table">Таблица, для которой создается индекс</param>
/// <param name="index">Создаваемый индекс</param>
public override void CreateIndex(DBTable table, DBIndex index)
{
StringBuilder indexColumns = new StringBuilder();
foreach (string indexColumn in index.Columns)
{
if (indexColumns.Length > 0) indexColumns.Append(", ");
indexColumns.Append(FormatColumnSafe(indexColumn));
}
string schemaName = ComposeSafeSchemaName(table.Name);
string tableFullName = FormatTableSafe(table);
string indexName = GetIndexName(index, table);
string indexFullName = ComposeSafeConstraintName(indexName);
indexFullName = (string.IsNullOrEmpty(schemaName) ? indexFullName : string.Concat(schemaName, ".", indexFullName)).ToLowerInvariant();
ExecuteSqlSchemaUpdate("Index", indexName, table.Name,
String.Format(CultureInfo.InvariantCulture, CreateIndexTemplate,
index.IsUnique ? "unique" : string.Empty, indexFullName, tableFullName, indexColumns,
string.IsNullOrEmpty(indexTablespace) ? string.Empty : "tablespace " + indexTablespace));
}
void DropTransctionTable()
{
string sql = ""
+ "DROP TABLE Transactions ";
var Model = new DBTable(_connectionString);
Model.Execute(sql);
}
/// <summary>
/// Создание или пересоздание представления на основе указанной таблицы
/// </summary>
/// <param name="table">Таблица, на основе которой создается представление</param>
public virtual void CreateOrReplaceView(DBTable table)
{
string view = FormatView(table);
string script = new OracleTemplater(this, table).CreateViewCommand();
// Проверка изменений
bool skip = false;
if ((updateOptions & UpdateSchemaOptions.CheckSafeContent) == UpdateSchemaOptions.CheckSafeContent)
skip = CompareSafeContent(script, GetViewText(table), false);
// Выполнение скрипта
if (!skip)
ExecuteSqlSchemaUpdate("View", view, table.Name, script);
}
public MemberMapper(DBTable dbTable) {
_dbTable = dbTable;
}
/// <summary>
/// Создание последовательности для первичного ключа указанной таблицы
/// </summary>
/// <param name="table">Таблица, для которой создается последовательность</param>
public virtual void CreateSequence(DBTable table)
{
DBColumn key = GetTableSequenceColumn(table);
if (key != null && !GetSequence(table))
{
string sequence = GetSeqName(table.Name);
ExecuteSqlSchemaUpdate("Sequence", sequence, table.Name,
new OracleTemplater(this, table).CreateSequenceCommand(sequence));
}
}
public static ILiteQueryable <T> Query <T>(DBTable collectionName)
{
return(db.GetCollection <T>(collectionName.ToString()).Query());
}
/// <summary>
/// Удаление колонки
/// </summary>
/// <param name="table">Таблица</param>
/// <param name="column">Колонка таблицы, которая подлежит удалению</param>
public virtual void DeleteColumn(DBTable table, DBColumn column)
{
string command = String.Format(CultureInfo.InvariantCulture, "alter table {0} drop column {1};",
FormatTableSafe(table), FormatColumnSafe(column.Name));
UnsafeSchemaUpdate("Old column", column.Name, table.Name, command);
}
private static void FixMailBox(Guid pluginID, Guid groupID, DBTable gruppePostkasseTable, DBTable side1Table, DBTable side2Table, DBRunner runner, ISQLExecuter executer, DBConnection connection)
{
SQL_SelectStatement sqlSelect = new SQL_SelectStatement();
sqlSelect.AddColumns(gruppePostkasseTable, "uiBrevID", "uiSkemaID", "txCPRNr", "txBesked");
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiGruppeID", MatchType.Equal, groupID));
sqlSelect.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiPluginID", MatchType.Equal, pluginID));
using (DBReader reader = runner.GetReader(executer, connection, sqlSelect))
{
DBRow row;
int count = 0;
try
{
while ((row = reader.GetNextRow()) != null)
{
Guid documentID = (Guid)row["uiSkemaID"];
bool deleteIt = false;
int option = 0;
if (HasID(side1Table, documentID, runner, executer, connection))
{
if (HasID(side2Table, documentID, runner, executer, connection))
{
option = 1;
deleteIt = true;
}
else if (OnlyOnePage((String)row["txBesked"]))
{
option = 2;
deleteIt = true;
}
if (deleteIt)
{
// Delete the document
Output(option + "," + row["txCPRNr"] + "," + documentID.ToString("B").ToUpper());
SQL_DeleteStatement sqlDelete = new SQL_DeleteStatement(gruppePostkasseTable);
sqlDelete.AddCriteria(new Crit_MatchCriteria(gruppePostkasseTable, "uiBrevID", MatchType.Equal, row["uiBrevID"]));
runner.Delete(executer, connection, sqlDelete);
count++;
}
}
}
}
finally
{
Output("Deleted " + count + " documents");
}
}
}
/// <inheritdoc/>
public override string GetSqlCreateColumnFullAttributes(DBTable table, DBColumn column)
{
string result = GetSqlCreateColumnType(table, column);
bool notNull = table is DBTableEx ? ((DBTableEx)table).ColumnIsNotNull(column) : column.IsKey;
return notNull ? string.Concat(result, OracleTemplater.Space, OracleTemplater.NotNull) : result;
}
private IEnumerable <DBData> GetData(SqlConnection connection, DBTable table)
{
var sqlCommandStr = $"SELECT {string.Join(", ", table.Columns.Select(x => string.Format($"[{x.Name}]")))} FROM {table.Schema}.{table.Name}";
var data = new List <DBData>();
using (SqlCommand command = new SqlCommand(sqlCommandStr, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var tempData = new DBData();
tempData.NameValue = new Dictionary <string, string>();
tempData.TableName = table.Name;
tempData.TableSchema = table.Schema;
int counter = 0;
foreach (var column in table.Columns)
{
if (reader[counter] is DBNull)
{
tempData.NameValue.Add(column.Name, "NULL");
continue;
}
switch (column.DataType)
{
case "binary":
case "varbinary":
case "image":
case "rowversion":
case "timestamp":
tempData.NameValue.Add(column.Name, $"'0x{BitConverter.ToString((byte[])reader[counter]).Replace("-", string.Empty)}'");
break;
case "bigint":
case "bit":
case "float":
case "int":
case "real":
case "smallint":
case "smallmoney":
case "tinyint":
tempData.NameValue.Add(column.Name, reader[counter].ToString());
break;
case "decimal":
case "money":
case "numeric":
var result = (reader[counter] as decimal?)?.ToString(CultureInfo.InvariantCulture);
tempData.NameValue.Add(column.Name, result);
break;
case "nchar":
case "ntext":
case "nvarchar":
tempData.NameValue.Add(column.Name, $"N'{reader[counter].ToString()}'");
break;
case "char":
case "text":
case "varchar":
tempData.NameValue.Add(column.Name, $"'{reader[counter].ToString()}'");
break;
case "date":
tempData.NameValue.Add(column.Name, $"'{reader.GetDateTime(counter).ToShortDateString()}'");
break;
case "datetime":
tempData.NameValue.Add(column.Name, $"'{reader.GetDateTime(counter).ToString("yyyy-MM-dd HH:mm:ss")}'");
break;
case "datetime2":
tempData.NameValue.Add(column.Name, $"'{reader.GetDateTime(counter).ToString("yyyy-MM-dd-HH:mm:ss.fffffff")}'");
break;
case "datetimeoffset":
tempData.NameValue.Add(column.Name, $"'{reader.GetDateTime(counter).ToString("yyyy-MM-dd HH:mm:ss.fffffff zzz")}'");
break;
case "time":
tempData.NameValue.Add(column.Name, $"'{reader.GetTimeSpan(counter)}'");
break;
case "uniqueidentifier":
tempData.NameValue.Add(column.Name, $"'{reader.GetGuid(counter).ToString()}'");
break;
default:
break;
}
counter++;
}
data.Add(tempData);
}
}
}
return(data);
}
private void SendData()
{
while (!runEvent.WaitOne(timer))
{
try
{
if (buffer.Count == 0)
{
continue;
}
var list = new NotifyMessageItem[buffer.Count > 200 ? 200 : buffer.Count];
for (int i = 0; i < list.Length; i++)
{
if (buffer.TryTake(out var item))
{
list[i] = item;
}
}
Array.Sort(list, (x, y) =>
{
var res = x.Table.CompareTo(y.Table);
//res = res != 0 ? res : string.Compare(x.Item.GetType().Name, y.Item.GetType().Name, StringComparison.Ordinal);
res = res != 0 ? res : ListHelper.Compare(x.ItemId, y.ItemId, null, false);
return(res != 0 ? res : x.Type.CompareTo(y.Type));
});
var stream = new MemoryStream();
using (var writer = new BinaryWriter(stream))
{
DBTable table = null;
object id = null;
foreach (var log in list)
{
if (log.Table != table)
{
id = null;
table = log.Table;
writer.Write((char)1);
writer.Write(table.Name);
}
if (!log.ItemId.Equals(id))
{
id = log.ItemId;
writer.Write((char)2);
writer.Write((int)log.Type);
writer.Write((int)log.UserId);
Helper.WriteBinary(writer, id, true);
}
}
writer.Flush();
Send(stream.ToArray());
}
OnSendChanges(list);
}
catch (Exception e)
{
Helper.OnException(e);
}
}
}
public SimpleDataStoreCriteriaVisitor(DBTable table, string alias)
{
this.table = table;
this.alias = alias;
}
public DictionaryMapper(DBTable[] dbTables) {
_dbTables = dbTables;
}
private void TableClick(object sender, EventArgs e)
{
Table = ((ToolMenuItem)sender).Tag as DBTable;
}
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("");
}
}
public static List <T> GetCollectionList <T>(DBTable collectionName)
{
return(db.GetCollection <T>(collectionName.ToString()).FindAll().ToList());
}
