private ColumnStructure FindeColumnByName(string columnName, DataBaseStructure inDatabase)
{
ColumnStructure outColumn = new ColumnStructure("ERROR");
foreach (TableStructure inDatabaseTable in inDatabase.Tables)
{
foreach (ColumnStructure column in inDatabaseTable.Columns)
{
if (column.OldName == columnName)
{
outColumn = column;
}
else
{
if (column.Name == columnName)
{
outColumn = column;
}
}
}
}
if (outColumn.Name == "ERROR")
{
// MessageBox.Show(this.GetType().Name + "FindeColumnByName");
}
return(outColumn);
}
private static void HandleMultiTypes(MultiValueChildScaffold ObjectChildren, DataTable theseObjects, ref long?T_OBJECT_SETS_ID, out long?T_PREV_OBJECT_SETS_ID, ref int i, ref DataRow item, ref string PROPERTY_NAME, ref string PROPERTY_VALUE, ref string OBJECT_TYPE, out ColumnStructure thisColumn)
{
var _DataType = "Characters(MAX)";
thisColumn = new ColumnStructure {
_Name = PROPERTY_VALUE, _DataType = _DataType, _DefaultValue = "", _IsNull = true
};
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
//Increment i
i++;
for (int y = i; y < theseObjects.AsEnumerable().Count(); y++)
{
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID == T_OBJECT_SETS_ID)
{
item = theseObjects.Rows[y];
i++;
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
else
{
i--;
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
break;
}
}
}
//public bool IsBlob(int ordinal)
//{
// return m_fields[ordinal].IsBlob;
//}
//public bool IsKey(int ordinal)
//{
// return m_fields[ordinal].IsKey;
//}
//public string GetBaseTable(int ordinal)
//{
// return m_fields[ordinal].BaseTable;
//}
public ITableStructure GetStructure()
{
TableStructure res = new TableStructure();
if (m_fields == null)
{
return(res);
}
PrimaryKey pk = new PrimaryKey();
foreach (var fld in m_fields)
{
ColumnStructure col = new ColumnStructure();
col.ColumnName = fld.FieldName;
col.DataType = fld.IsBlob ? (DbTypeBase) new DbTypeBlob() : new DbTypeString();
if (fld.IsKey)
{
pk.Columns.Add(new ColumnReference(col.ColumnName));
}
res._Columns.Add(col);
}
if (m_fields.Length > 0)
{
res.FullName = new NameWithSchema(m_fields[0].BaseTable);
}
if (pk.Columns.Count > 0)
{
res._Constraints.Add(pk);
}
return(res);
}
public void SetUp()
{
try
{
DBTest.RemoveDBFiles();
DBStructure database = new DBStructure("", "MyPal", DatabaseMode.Create);
TableStructure tblPeople = database.CreateTable("People");
tblPeople.CreateColumn("Id", ColumnType.Integer, true);
ColumnStructure colName =
tblPeople.CreateColumn("Name", ColumnType.String, false);
colName = colName;
tblPeople.CreateColumn("Age", ColumnType.Integer, false);
tblPeople.CreateColumn("Type", ColumnType.Integer, true);
tblPeople.CreateColumn("DateTime", ColumnType.DateTime, true);
tblPeople.SetCompoundIndex("Id", "Name");
tblPeople.SetCompoundIndex("Name", "Type");
database.SaveStructure();
database.Shutdown();
database = new DBStructure("", "MyPal");
database.LoadStructure( );
m_database = database.Database;
}
catch (Exception exc)
{
Assert.Fail(exc.Message);
}
}
public override void RenameColumn(IColumnStructure column, string newcol)
{
var newcoldef = new ColumnStructure(column);
newcoldef.ColumnName = newcol;
this.ChangeColumn(column, newcoldef);
}
protected override void ProcessTableColumnsTable(TableStructure table, DataTable columns)
{
foreach (DataRow row in columns.Rows)
{
ColumnStructure col = table.AddColumn(row["COLUMN_NAME"].ToString(), AnalyseType(new DataRowAdapter(row), m_conn, false));
LoadTableColumn(col, row);
}
}
internal List <string> GenerateColumn(ColumnStructure columnStructure, long length)
{
if (generatorFunctions.ContainsKey(columnStructure.Type))
{
return(generatorFunctions[columnStructure.Type](columnStructure.Options, length));
}
throw new ArgumentException();
}
private static void HandleDateAndTimeTypes(DataTable theseObjects, ref long?T_OBJECT_SETS_ID, out long?T_PREV_OBJECT_SETS_ID, ref int i, ref DataRow item, ref string PROPERTY_NAME, ref string PROPERTY_VALUE, ref string OBJECT_TYPE, out ColumnStructure thisColumn)
{
var _DataType = "";
switch (OBJECT_TYPE.ToLower())
{
case "time":
case "times":
_DataType = "time(7)";
break;
case "date":
case "datetime":
//_DataType = "datetime";
_DataType = "datetime2";
break;
case "timestamp":
_DataType = "timestamp";
break;
}
thisColumn = new ColumnStructure {
_Name = PROPERTY_VALUE, _DataType = _DataType, _DefaultValue = "", _IsNull = true
};
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
//Increment i
i++;
for (int y = i; y < theseObjects.AsEnumerable().Count(); y++)
{
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID == T_OBJECT_SETS_ID)
{
item = theseObjects.Rows[y];
if (PROPERTY_NAME == "Required" && PROPERTY_VALUE == "true")
{
thisColumn._IsNull = false;
thisColumn._DefaultValue = DateTime.MinValue.ToString();
}
i++;
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
else
{
i--;
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
break;
}
}
}
/// <summary>
/// Creates a DataTable representing the information in a shape file.
/// </summary>
/// <param name="filename">The filename (minus the . and extension) to read.</param>
/// <param name="tableName">The name to give to the table.</param>
/// <param name="geometryFactory">The geometry factory to use when creating the objects.</param>
/// <returns>DataTable representing the data </returns>
public static DataTable CreateDataTable(string filename, string tableName, GeometryFactory geometryFactory)
{
if (filename == null)
{
throw new ArgumentNullException("filename");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
if (geometryFactory == null)
{
throw new ArgumentNullException("geometryFactory");
}
ShapefileDataReader shpfileDataReader = new ShapefileDataReader(filename, geometryFactory);
DataTable table = new DataTable(tableName);
// use ICustomTypeDescriptor to get the properies/ fields. This way we can get the
// length of the dbase char fields. Because the dbase char field is translated into a string
// property, we lost the length of the field. We need to know the length of the
// field when creating the table in the database.
IEnumerator enumerator = shpfileDataReader.GetEnumerator();
bool moreRecords = enumerator.MoveNext();
ICustomTypeDescriptor typeDescriptor = (ICustomTypeDescriptor)enumerator.Current;
foreach (PropertyDescriptor property in typeDescriptor.GetProperties())
{
ColumnStructure column = (ColumnStructure)property;
Type fieldType = column.PropertyType;
DataColumn datacolumn = new DataColumn(column.Name, fieldType);
if (fieldType == typeof(string))
{
// use MaxLength to pass the length of the field in the dbase file
datacolumn.MaxLength = column.Length;
}
table.Columns.Add(datacolumn);
}
// add the rows - need a do-while loop because we read one row in order to determine the fields
int iRecordCount = 0;
object[] values = new object[shpfileDataReader.FieldCount];
do
{
iRecordCount++;
shpfileDataReader.GetValues(values);
table.Rows.Add(values);
moreRecords = enumerator.MoveNext();
}while (moreRecords);
return(table);
}
public ITableStructure GetStructure()
{
var res = new TableStructure();
foreach (string fld in m_fieldNames)
{
var col = new ColumnStructure();
col.ColumnName = fld;
col.DataType = new DbTypeString();
res._Columns.Add(col);
}
return(res);
}
public Table GetTable(string tablename)
{
Table table = null;
List <ColumnStructure> information = new List <ColumnStructure>();
using (MySqlTransaction transaction = Connection.Instance.BeginTransaction()) {
//Structure
string query = "DESC " + tablename;
MySqlCommand command = Connection.Instance.GetCommand(query, transaction);
using (MySqlDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
ColumnStructure cs = new ColumnStructure();
cs.ColumnName = DbUtils.GetString(reader["Field"]);
cs.DataType = DbUtils.GetString(reader["Type"]);
cs.KeyType = DbUtils.GetString(reader["Key"]);
cs.IsNullable = DbUtils.GetString(reader["Null"]);
cs.Extra = DbUtils.GetString(reader["Extra"]);
information.Add(cs);
}
}
//Data
table = new Table(tablename, information);
query = "SELECT * from " + tablename;
MySqlCommand commandData = Connection.Instance.GetCommand(query, transaction);
using (MySqlDataReader reader = commandData.ExecuteReader()) {
while (reader.Read())
{
DataRow row = new DataRow();
row.ColumnStructure = information;
for (int i = 0; i < reader.FieldCount; i++)
{
row.Attributes.Add(new Flag(reader.GetName(i), reader[i].ToString(), reader.GetDataTypeName(i)));
}
table.DataRows.Add(row);
}
}
transaction.Commit();
}
return(table);
}
protected override void LoadTableColumn(ColumnStructure col, DataRow row)
{
base.LoadTableColumn(col, row);
try
{
if ((bool)row["PRIMARY_KEY"])
{
m_pkcols[new Tuple <string, string>(col.Table.FullName.Name, col.ColumnName)] = true;
}
}
catch (Exception err)
{
Logging.Warning("Error loading SQLite PK:" + err.ToString());
}
}
private static void HandleNumberTypes(DataTable theseObjects, ref long?T_OBJECT_SETS_ID, out long?T_PREV_OBJECT_SETS_ID, ref int i, ref DataRow item, ref string PROPERTY_NAME, ref string PROPERTY_VALUE, ref string OBJECT_TYPE, out ColumnStructure thisColumn)
{
var _DataType = "";
switch (OBJECT_TYPE.ToLower())
{
case "number":
_DataType = "bigint";
break;
case "currency":
case "decimal":
_DataType = "money";
break;
}
thisColumn = new ColumnStructure {
_Name = PROPERTY_VALUE, _DataType = _DataType, _DefaultValue = "", _IsNull = true
};
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
//Increment i
i++;
for (int y = i; y < theseObjects.AsEnumerable().Count(); y++)
{
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID == T_OBJECT_SETS_ID)
{
item = theseObjects.Rows[y];
if (PROPERTY_NAME.ToLower() == "required" && PROPERTY_VALUE.ToLower() == "true")
{
thisColumn._IsNull = false;
thisColumn._DefaultValue = "0";
}
i++;
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
else
{
i--;
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
break;
}
}
}
public static void CreateDB()
{
DBStructure database = new DBStructure("", "MyPal", DatabaseMode.Create);
database.Build = "Build";
TableStructure tblPeople = database.CreateTable("People");
tblPeople.CreateColumn("Id", ColumnType.Integer, true);
ColumnStructure colName =
tblPeople.CreateColumn("Name", ColumnType.String, true);
colName = colName;
ColumnStructure colAge =
tblPeople.CreateColumn("Age", ColumnType.Integer, false);
colAge = colAge;
tblPeople.CreateColumn("Birthday", ColumnType.DateTime, false);
tblPeople.SetCompoundIndex("Name", "Age");
TableStructure tblBooks = database.CreateTable("Books");
tblBooks.CreateColumn("Id", ColumnType.Integer, true);
ColumnStructure colBookName =
tblBooks.CreateColumn("Name", ColumnType.String, true);
colBookName = colBookName;
ColumnStructure colPrice =
tblBooks.CreateColumn("Price", ColumnType.Integer, false);
colPrice = colPrice;
TableStructure tblDate = database.CreateTable("Date");
tblDate.CreateColumn("Id", ColumnType.Integer, true);
tblDate.CreateColumn("Age", ColumnType.Integer, false);
tblDate.CreateColumn("Birthday", ColumnType.DateTime, true);
tblDate.SetCompoundIndexWithValue("Age", "Birthday", "Id");
TableStructure tblEndMarker = database.CreateTable("EndMarker");
tblEndMarker.CreateColumn("Id", ColumnType.Integer, true);
tblEndMarker.CreateColumn("Age", ColumnType.Integer, false);
tblEndMarker.CreateColumn("Name", ColumnType.String, true);
tblEndMarker.SetCompoundIndex("Name", "Age");
database.SaveStructure();
database.Shutdown();
}
public InMemoryTable GetTable(bool wantdata)
{
List <DataRecord> records = new List <DataRecord>();
foreach (string row in EnumRows())
{
records.Add(FieldAnalyser.AnalyseRecord(row));
}
var ts = new TableStructure();
Dictionary <string, int> colindexes = new Dictionary <string, int>();
// get column collection
foreach (var rec in records)
{
foreach (var fld in rec.Fields)
{
if (colindexes.ContainsKey(fld.Name))
{
continue;
}
var col = new ColumnStructure();
col.ColumnName = fld.Name;
col.DataType = new DbTypeString();
colindexes[fld.Name] = ts._Columns.Count;
ts._Columns.Add(col);
}
}
if (!wantdata)
{
return(new InMemoryTable(ts));
}
var recs = new List <ArrayDataRecord>();
foreach (var rec in records)
{
var row = new ArrayDataRecord(ts);
foreach (var fld in rec.Fields)
{
row.SeekValue(colindexes[fld.Name]);
row.SetString(fld.Value);
}
recs.Add(row);
}
return(InMemoryTable.FromEnumerable(ts, recs));
}
public void SetUp()
{
try
{
DBTest.RemoveDBFiles();
DBStructure database = new DBStructure("", "MyPal", DatabaseMode.Create);
TableStructure tblPeople = database.CreateTable("People");
tblPeople.CreateColumn("Id", ColumnType.Integer, true);
ColumnStructure colName =
tblPeople.CreateColumn("Name", ColumnType.String, false);
colName = colName;
tblPeople.CreateColumn("Age", ColumnType.Integer, false);
tblPeople.CreateColumn("Type", ColumnType.Integer, true);
tblPeople.CreateColumn("DateTime", ColumnType.DateTime, true);
tblPeople.SetCompoundIndex("Id", "Name");
tblPeople.SetCompoundIndex("Name", "Type");
TableStructure tblTest = database.CreateTable("Test");
tblTest.CreateColumn("Id", ColumnType.Integer, false);
tblTest.CreateColumn("Date", ColumnType.DateTime, false);
tblTest.SetCompoundIndex("Id", "Date");
database.SaveStructure();
database.Shutdown();
database = new DBStructure("", "MyPal", DatabaseMode.Create);
database.LoadStructure( );
_database = database.Database;
_peopleTable = _database.GetTable("People");
_testTable = _database.GetTable("Test");
//m_testTable.InputMode = InputMode.Batch;
for (int i = 0; i < 10; i++)
{
IRecord record = _peopleTable.NewRecord();
Assert.AreEqual(i, record.GetID(), "Id is wrong");
record.SetValue(1, (500 - i).ToString());
record.Commit();
Assert.AreEqual((i + 1), _peopleTable.Count, "Count is wrong");
}
}
catch (Exception exc)
{
Assert.Fail(exc.Message);
}
}
public override void ChangeColumn(IColumnStructure oldcol, IColumnStructure newcol, IEnumerable <IConstraint> constraints)
{
DropDefault(oldcol);
if (oldcol.ColumnName != newcol.ColumnName)
{
RenameColumn(oldcol, newcol.ColumnName);
}
Put("^alter ^table %f ^alter ^column %i ", newcol.Table, newcol.ColumnName);
// remove autoincrement flag
ColumnStructure newcol2 = new ColumnStructure(newcol);
newcol2.SetDummyTable(newcol.Table.FullName);
newcol2.DataType.SetAutoincrement(false);
ColumnDefinition(newcol2, false, true, true);
EndCommand();
CreateDefault(newcol);
this.CreateConstraints(constraints);
}
protected override void LoadTableColumns(TableStructure table)
{
OleDbConnection c = (OleDbConnection)GetDbConn();
Dictionary <int, string> typenames = GetTypeNames(m_conn);
DataTable cols = c.GetSchema("Columns", new string[] { null, null, table.FullName.Name }).SelectNewTable("1=1", "ORDINAL_POSITION ASC");
foreach (DataRow row in cols.Rows)
{
ColumnStructure col = new ColumnStructure();
col.ColumnName = row["COLUMN_NAME"].ToString();
col.IsNullable = (bool)row["IS_NULLABLE"];
col.DefaultValue = SqlExpression.ParseDefaultValue((row.ColumnDefault() ?? "").ToString(), m_dialect);
col.DataType = MakeSpecificAccessType(new DataRowAdapter(row), typenames).ToGenericType();
int colflags = Int32.Parse(row["COLUMN_FLAGS"].ToString());
//if ((colflags & 0x20) == 0) col.DataType.SetAutoincrement(true);
table._Columns.Add(col);
}
//table.FilledMembers |= TableStructureMembers.ColumnNames | TableStructureMembers.ColumnTypes;
}
public void FindeTable(DataBaseStructure db)
{//кривой цикл продумать лучше. Попробовать сделать через Column.Table
_asTables = new List <TableStructure>();
_asRightColumn = new ColumnStructure(_asRightName);
_asRightColumn.Size = -1;
foreach (var col in _asColumnList) //определение типа столбца
{
foreach (var tb in db.Tables)
{
foreach (var cl in tb.Columns)
{
if (col == cl.Name)
{
_asTables.Add(tb);
if (_asRightColumn.Size < cl.Size)
{
_asRightColumn.Size = cl.Size;
_asRightColumn.TypeID = cl.TypeID;
_asRightColumn.Type = cl.Type;
}
}
}
}
}
_asTables = _asTables.Distinct().ToList();
if (_asTables.Count == 1)
{
_isSelectPart = true;
_table = _asTables[0].Name; //переделать в структуру.
_asRightColumn.IsRenamed = true;
_asRightColumn.OldName = _asRightColumn.Name;
_asRightColumn.Name = _asTables[0].ShortName + _asRightName;
}
else
{
_isSelectPart = false;
_table = "Error! _asTables.Count";
}
}
private void Add()
{
//Add row
DTO.DataRow row = new DTO.DataRow();
row.ColumnStructure = this.table.ColumnStructure;
foreach (Control ctrl in this.pnlValues.Controls)
{
ColumnStructure cs = (ColumnStructure)ctrl.Tag;
if (ctrl is TextBox)
{
row.Attributes.Add(new Flag(cs.ColumnName, ctrl.Text, cs.DataType));
}
else if (ctrl is ComboBox)
{
string value = string.Empty;
if (!string.IsNullOrEmpty(ctrl.Text))
{
value = ctrl.Text.Split(';')[0];
}
row.Attributes.Add(new Flag(cs.ColumnName, value, cs.DataType));
}
}
this.table.DataRows.Add(row);
//Clear fields
if (this.ctrlToIncrement != null)
{
Int32.TryParse(this.ctrlToIncrement.Text, out int id);
this.ClearFields();
this.ctrlToIncrement.Text = (id + 1).ToString();
}
else
{
this.ClearFields();
}
//Generate query
this.txtQuery.Text = InsertStatement.GetQuery(table);
}
public override void ChangeColumn(IColumnStructure oldcol, IColumnStructure newcol, IEnumerable <IConstraint> constraints)
{
if (oldcol.DefaultValue != null)
{
PutCmd("^alter ^table %f ^alter ^column %i ^drop ^default", newcol.Table, newcol.ColumnName);
}
if (oldcol.ColumnName != newcol.ColumnName)
{
RenameColumn(oldcol, newcol.ColumnName);
}
// change data type
if (!DbDiffTool.EqualTypes(oldcol.DataType, newcol.DataType, new DbDiffOptions()))
{
Put("^alter ^table %f ^alter ^column %i ", newcol.Table, newcol.ColumnName);
// remove autoincrement flag
ColumnStructure newcol2 = new ColumnStructure(newcol);
newcol2.SetDummyTable(newcol.Table.FullName);
newcol2.DataType.SetAutoincrement(false);
ColumnDefinition(newcol2, false, false, false);
EndCommand();
}
if (oldcol.IsNullable != newcol.IsNullable)
{
PutCmd("^alter ^table %f ^alter ^column %i ^set %k ^null", oldcol.Table, newcol.ColumnName, newcol.IsNullable ? "" : "not");
}
CreateDefault(newcol);
if (oldcol.DataType.IsAutoIncrement() != newcol.DataType.IsAutoIncrement())
{
if (oldcol.DataType.IsAutoIncrement())
{
PutCmd("^alter ^table %f ^alter ^column %i ^drop ^generated", newcol.Table.FullName, newcol.ColumnName);
}
if (newcol.DataType.IsAutoIncrement())
{
PutCmd("^alter ^table %f ^alter ^column %i %s ^generated ^always ^as ^identity (^start ^with %s)",
newcol.Table.FullName, newcol.ColumnName, m_dialect.GenericTypeToSpecific(newcol.DataType), 1);
}
}
this.CreateConstraints(constraints);
}
private ComboBox GetValueComboBox(ColumnStructure cs)
{
ComboBox combobox = new ComboBox();
combobox.BeginUpdate();
combobox.DropDownStyle = ComboBoxStyle.DropDownList;
combobox.Width = this.pnlValues.Width - 3;
combobox.Font = new Font("Microsoft Sans Serif", 10);
Table table = this.dbInformation.GetTableOfForeignKey(this.table.TableName, cs.ColumnName);
if (table != null)
{
if (cs.IsNullable.ToLower() == "yes")
{
combobox.Items.Add("");
}
foreach (DTO.DataRow row in table.DataRows)
{
string item = string.Empty;
foreach (Flag flag in row.Attributes)
{
item += flag.Value + "; ";
}
combobox.Items.Add(item);
}
}
if (combobox.Items.Count > 0)
{
combobox.SelectedIndex = 0;
}
combobox.EndUpdate();
return(combobox);
}
private static void HandleVarCharTypes(DataTable theseObjects, ref long?T_OBJECT_SETS_ID, out long?T_PREV_OBJECT_SETS_ID, ref int i, ref DataRow item, ref string PROPERTY_NAME, ref string PROPERTY_VALUE, ref string OBJECT_TYPE, out ColumnStructure thisColumn)
{
thisColumn = new ColumnStructure {
_Name = PROPERTY_VALUE, _DataType = "Characters(MAX)", _DefaultValue = "", _IsNull = true
};
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
//Increment i
i++;
for (int y = i; y < theseObjects.AsEnumerable().Count(); y++)
{
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID == T_OBJECT_SETS_ID)
{
item = theseObjects.Rows[y];
if (PROPERTY_NAME == "Max")
{
thisColumn._DataType = "Characters(" + PROPERTY_VALUE + ")";
}
if (PROPERTY_NAME == "Required" && PROPERTY_VALUE == "true")
{
thisColumn._IsNull = false;
thisColumn._DefaultValue = " ";
}
i++;
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
else
{
i--;
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
break;
}
}
}
protected override void ReadColumnSpecific(ColumnStructure col)
{
while (!IsSymbol(")") && !IsSymbol(","))
{
if (SkipTokenIf("auto_increment"))
{
col.DataType.SetAutoincrement(true);
}
else if (SkipMultiIf("primary", "key"))
{
var pk = new PrimaryKey();
pk.Columns.Add(new ColumnReference(col.ColumnName));
((TableStructure)col.Table)._Constraints.Add(pk);
}
else if (SkipTokenIf("references"))
{
var fk = new ForeignKey();
fk.Columns.Add(new ColumnReference(col.ColumnName));
((TableStructure)col.Table)._Constraints.Add(fk);
ReadReferencesClause(fk);
}
}
}
private static void HandleByteTypes(DataTable theseObjects, ref long?T_OBJECT_SETS_ID, out long?T_PREV_OBJECT_SETS_ID, ref int i, ref DataRow item, ref string PROPERTY_NAME, ref string PROPERTY_VALUE, ref string OBJECT_TYPE, out ColumnStructure thisColumn)
{
var _DataType = "varbinary";
thisColumn = new ColumnStructure {
_Name = PROPERTY_VALUE, _DataType = _DataType, _DefaultValue = "", _IsNull = true
};
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
//Increment i
i++;
for (int y = i; y < theseObjects.AsEnumerable().Count(); y++)
{
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID == T_OBJECT_SETS_ID)
{
item = theseObjects.Rows[y];
if (PROPERTY_NAME == "Required" && PROPERTY_VALUE == "true")
{
thisColumn._IsNull = false;
thisColumn._DefaultValue = ER_Tools.ConvertObjectToString(new byte[0]);
}
i++;
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
else
{
i--;
item = theseObjects.Rows[i];
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
break;
}
}
}
private static List <CommandResult> CreateTablesCode(IConnectToDB _Connect, CreateTableDefinition thisDef, bool isChildCall)
{
List <CommandResult> results = new List <CommandResult>();
ScaffoldStructure thisAppStructure = new ScaffoldStructure();
foreach (var thisCurrentObject in thisDef.listOfObjects)
{
ER_DDL er_ddl = new ER_DDL();
List <ColumnStructure> ExistingColumnsList = new List <ColumnStructure>();
List <ColumnStructure> MetaColumnsList = new List <ColumnStructure>();
List <ColumnStructure> ScaffoldColumns = new List <ColumnStructure>();
List <ColumnStructure> ScaffoldColumnsForReOrder = new List <ColumnStructure>();
DataTable thisStageObjects = thisDef.theseObjects.AsEnumerable().Where(r => r.Field <string>(thisDef.BindRootFamilyOnObject).Replace(" ", "_").Replace("-", "_") == thisCurrentObject).CopyToDataTable();
DataColumnCollection ccthisStageObjects = thisStageObjects.Columns;
Scaffolds scaffolds = new Scaffolds();
ConnectToDB forCore = _Connect.Copy();
forCore.Schema = thisDef.desiredSchema;
string ObjectName = "";
string BaseObjectUUID = "";
string parentBaseColumnUUID = ""; //Unrelated to Outside
List <MultiValueChildScaffold> ObjectChildren = new List <MultiValueChildScaffold>();
if (ccthisStageObjects.Contains(thisDef.BindRootFamilyOnObject))
{
ObjectName = string.IsNullOrWhiteSpace(ObjectName) ? thisStageObjects.Rows[0].Field <string>(thisDef.BindRootFamilyOnObject).Replace(" ", "_") : ObjectName;
BaseObjectUUID = string.IsNullOrWhiteSpace(BaseObjectUUID) ? thisStageObjects.Rows[0].Field <Guid>(thisDef.BindRootColumnUUID).ToString().Replace("-", "_") : BaseObjectUUID;
string StageTableName = thisDef.rootTablePrefix + "_" + BaseObjectUUID;
string RootColumnName = StageTableName;
parentBaseColumnUUID = string.IsNullOrWhiteSpace(parentBaseColumnUUID) ? thisStageObjects.Rows[0].Field <Guid>(thisDef.ThisParent.BindParentRootColumnUUID).ToString().Replace("-", "_") : parentBaseColumnUUID;
string ParentTable = thisDef.ThisParent.ParentTablePrefix + "_" + parentBaseColumnUUID;
#region Create App Table
//if (!thisDef.sourceDatabase.Tables.Contains(ParentTable, forCore.Schema))
//{
thisDef.ThisParent.BindParentRootColumnFamilyName = ParentTable;
thisDef.ThisParent.FkColumnAvailableInBothTable = ParentTable + "_UUID";
//thisDef.ThisParent.BindParentRootColumnUUID = "BASE_" + ParentTable + "_UUID";
thisAppStructure = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = ParentTable,
ScaffoldType = Tools.Box.Clone <string>(thisDef.ThisParent.ParentObjectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(thisDef.ThisParent.BindParentRootColumnFamilyName)
};
ScaffoldColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, ParentTable, thisDef.ThisParent.ParentObjectLayer, ScaffoldColumns, true, thisDef.ThisParent.BindParentRootColumnFamilyName, true));
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
IConnectToDB Link2CSA = forCore.Copy();
Link2CSA.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA, "FK_" + ParentTable + "_" + "IDENTITIES", ParentTable, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, true));
// results.AddRange(ER_DDL._DROP_VIEW(Link2CSA, ParentTable));
results.AddRange(ER_Generate._GENERATE_VIEW(Link2CSA, ParentTable, "Generated"));
//}
//else
//{
// //thisDef.ThisParent.FkColumnAvailableInBothTable = ParentTable + "_UUID";
// thisDef.ThisParent.BuildParentScaffold = false;
//}
#endregion
#region Create Stage Table & Any Children
//if (!thisDef.sourceDatabase.Tables.Contains(TableName, forCore.Schema))
if (!thisDef.sourceDatabase.Tables.Contains(StageTableName, forCore.Schema) || true)
{
bool createdAppScaffold = false;
long? T_OBJECT_SETS_ID = 0;
long? @T_PREV_OBJECT_SETS_ID = 0;
if (!createdAppScaffold && thisDef.ThisParent.BuildParentScaffold)
{
createdAppScaffold = true;
forCore.Schema = thisDef.desiredSchema;
}
ScaffoldColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
ScaffoldColumns.Add(new ColumnStructure {
_Name = "STAGES_UUID", _DataType = "guid", _IsNull = false
});
#region Loop Through Every Column
for (int i = 0; i < thisStageObjects.AsEnumerable().Count(); i++)
{
thisDef.desiredSchema = string.IsNullOrWhiteSpace(thisDef.desiredSchema) ? thisStageObjects.Rows[i].Field <string>("CORE_NAME").Replace(" ", "_") : thisDef.desiredSchema;
DataRow item = thisStageObjects.Rows[i];
string PROPERTY_NAME, PROPERTY_VALUE, OBJECT_TYPE;
AssignValue(out T_OBJECT_SETS_ID, item, out PROPERTY_NAME, out PROPERTY_VALUE, out OBJECT_TYPE);
if (T_PREV_OBJECT_SETS_ID != T_OBJECT_SETS_ID)
{
if (PROPERTY_NAME == "ID")
{
ColumnStructure thisColumn = new ColumnStructure();
bool addScaffold = true;
if (Array.IndexOf(StringTypes, OBJECT_TYPE.ToLower()) > -1)
{
HandleVarCharTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if (Array.IndexOf(NumberTypes, OBJECT_TYPE.ToLower()) > -1)
{
HandleNumberTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(DateAndTimeTypes, OBJECT_TYPE.ToLower()) > -1))
{
HandleDateAndTimeTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(ByteTypes, OBJECT_TYPE.ToLower()) > -1))
{
HandleByteTypes(thisStageObjects, ref T_OBJECT_SETS_ID, out T_PREV_OBJECT_SETS_ID, ref i, ref item, ref PROPERTY_NAME, ref PROPERTY_VALUE, ref OBJECT_TYPE, out thisColumn);
}
else if ((Array.IndexOf(MultiTypes, OBJECT_TYPE.ToLower()) > -1))
{
#region Define Multi Object for post creation.
List <ColumnStructure> TheseChildColumns = new List <ColumnStructure>();
var objectName = thisStageObjects.Rows[i].Field <string>("PROPERTY_VALUE");
TheseChildColumns.Add(new ColumnStructure {
_Name = "CORES_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = "STAGES_UUID", _DataType = "guid", _IsNull = false
});
TheseChildColumns.Add(new ColumnStructure {
_Name = objectName, _DataType = "Characters(MAX)", _IsNull = false
});
ObjectChildren.Add(new MultiValueChildScaffold
{
SchemaName = thisDef.desiredSchema,
TableName = "O_" + thisStageObjects.Rows[i].Field <Guid>("BASE_OBJECT_SETS_UUID").ToString().Replace(" ", "_").Replace("-", "_"),
ObjectLayer = "Design",
RootColumnName = PROPERTY_VALUE,
ParentRootColumnName = RootColumnName,
ScaffoldColumns = TheseChildColumns
});
addScaffold = false;
#endregion
}
if (addScaffold)
{
ScaffoldColumns.Add(thisColumn);
}
}
}
T_PREV_OBJECT_SETS_ID = T_OBJECT_SETS_ID;
}
#endregion
forCore.Schema = thisDef.desiredSchema;
}
if (thisStageObjects.AsEnumerable().Count() > 0 && !string.IsNullOrWhiteSpace(BaseObjectUUID) && !string.IsNullOrWhiteSpace(parentBaseColumnUUID))
{
ScaffoldColumnsForReOrder = new List <ColumnStructure>();
ScaffoldColumnsForReOrder.Add(new ColumnStructure {
_Name = thisDef.ThisParent.FkColumnAvailableInBothTable, _DataType = thisDef.ThisParent.FkRelatedColumnType, _IsNull = false
});
ScaffoldColumnsForReOrder.AddRange(ScaffoldColumns);
ScaffoldColumns = ScaffoldColumnsForReOrder;
forCore.Schema = thisDef.desiredSchema;
ScaffoldStructure thisStage = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = StageTableName,
ScaffoldType = Tools.Box.Clone <string>(thisDef.objectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(RootColumnName)
};
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, StageTableName, thisDef.objectLayer, ScaffoldColumns, true, RootColumnName, true));
#region Add Foreign Key to Parent Application Table
forCore.Schema2 = thisDef.desiredSchema;
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = thisDef.ThisParent.FkColumnAvailableInBothTable, _DataType = thisDef.ThisParent.FkRelatedColumnType, _DefaultValue = "", _IsNull = false
});
results.AddRange(er_ddl.ADD_KEY_FOREIGN(forCore, "FK_" + StageTableName, StageTableName, ParentTable, ExistingColumnsList, ExistingColumnsList, true));
#endregion
#region Add Foreign Key to Identities
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
// IConnectToDB Link2CSA = forCore.Copy();
Link2CSA = forCore.Copy();
Link2CSA.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA, "FK_" + StageTableName + "_IDENTITIES", StageTableName, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, false));
#endregion
#region Create Each Multi Object Table
foreach (MultiValueChildScaffold item in ObjectChildren)
{
ScaffoldStructure thisMultiObject = new ScaffoldStructure
{
_Connect = forCore.Copy(),
Name = Tools.Box.Clone <string>(item.TableName),
ScaffoldType = Tools.Box.Clone <string>(item.ObjectLayer),
ColumnsList = Tools.Box.Clone <List <ColumnStructure> >(item.ScaffoldColumns),
useIdentityUUID = true,
RootColumn = Tools.Box.Clone <string>(item.TableName)
};
thisStage.children.Add(thisMultiObject);
List <ColumnStructure> itemScaffoldColumnsForReOrder = new List <ColumnStructure>();
itemScaffoldColumnsForReOrder.Add(new ColumnStructure {
_Name = StageTableName + "_UUID", _DataType = thisDef.ThisParent.FkRelatedColumnType, _IsNull = false
});
itemScaffoldColumnsForReOrder.AddRange(item.ScaffoldColumns);
item.ScaffoldColumns = itemScaffoldColumnsForReOrder;
results.AddRange(scaffolds.SYNC_SCAFFOLD(forCore, item.TableName, item.ObjectLayer, item.ScaffoldColumns, true, item.TableName, true));
#region Add Foreign to Parent Stage Table
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
// ExistingColumnsList.Add(new ColumnStructure { _Name = item.ParentRootColumnName, _DataType = "Guid", _DefaultValue = "", _IsNull = false });
ExistingColumnsList.Add(new ColumnStructure {
_Name = StageTableName + "_UUID", _DataType = thisDef.ThisParent.FkRelatedColumnType, _DefaultValue = "", _IsNull = false
});
results.AddRange(er_ddl.ADD_KEY_FOREIGN(forCore, "FK_" + item.TableName + "_" + item.RootColumnName, item.TableName, StageTableName, ExistingColumnsList, ExistingColumnsList, true));
#endregion
#region Add Foreign Key to Identities
ExistingColumnsList.Clear(); MetaColumnsList.Clear(); ScaffoldColumns.Clear();
ExistingColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_UUID", _DataType = "Guid", _DefaultValue = "", _IsNull = false
});
IConnectToDB Link2CSA2 = forCore.Copy();
Link2CSA2.Schema2 = "CSA";
results.AddRange(er_ddl.ADD_KEY_FOREIGN(Link2CSA2, "FK_" + item.TableName + "_" + "IDENTITIES", item.TableName, "IDENTITIES", ExistingColumnsList, ExistingColumnsList, false));
#endregion
//results.AddRange(ER_DDL._DROP_VIEW(forCore, item.TableName));
// results.AddRange(ER_Generate._GENERATE_VIEW(forCore, item.TableName, "Generated"));
}
thisAppStructure.children.Add(thisStage);
//results.AddRange(ER_DDL._DROP_VIEW(forCore, StageTableName));
results.AddRange(ER_Generate._GENERATE_VIEW_WITH_CHILDREN(forCore, StageTableName, "Generated"));
#endregion
}
#endregion
}
else
{
//IF CASTGOOP Not Present
//Update Logic
}
}
return(results);
}
private void PairTableContent(TableStructure tsrc, TableStructure tdst)
{
// parovani sloupcu podle jmen
foreach (ColumnStructure csrc in tsrc.Columns)
{
ColumnStructure cdst = (ColumnStructure)tdst.Columns.FirstOrDefault(c => c.ColumnName == csrc.ColumnName);
if (cdst != null)
{
PairObjects(csrc, cdst);
}
}
// parovani sloupcu dle indexu (jen ty nesparovane podle jmen)
foreach (ColumnStructure csrc in tsrc.Columns)
{
if (IsPaired(csrc))
{
continue;
}
int cindex = tsrc.Columns.GetIndex(csrc.ColumnName);
if (cindex < tdst.Columns.Count && !IsPaired((AbstractObjectStructure)tdst.Columns[cindex]))
{
PairObjects(csrc, (AbstractObjectStructure)tdst.Columns[cindex]);
}
}
// sparovani primarnich klicu
PrimaryKey psrc = tsrc.FindConstraint <PrimaryKey>();
PrimaryKey pdst = tdst.FindConstraint <PrimaryKey>();
if (psrc != null && pdst != null)
{
PairObjects(psrc, pdst);
}
// sparovani na zaklade jmen constraintu
foreach (Constraint csrc in tsrc.Constraints)
{
if (IsPaired(csrc))
{
continue;
}
Constraint cdst = tdst.Constraints.FirstOrDefault(c => c.Name != null && c.Name == csrc.Name) as Constraint;
if (cdst != null && !IsPaired(cdst) && csrc.GetType() == cdst.GetType())
{
PairObjects(csrc, cdst);
}
}
// sparovani na zaklade typu a obsahu constraintu (jmen sloupcu/vyrazu CHECK)
foreach (Constraint csrc in tsrc.Constraints)
{
if (IsPaired(csrc))
{
continue;
}
foreach (Constraint cdst in tdst.Constraints)
{
if (IsPaired(cdst))
{
continue;
}
if (csrc.GetType() != cdst.GetType())
{
continue;
}
if (csrc is ColumnsConstraint)
{
if (((ColumnsConstraint)csrc).Columns.EqualSequence(((ColumnsConstraint)cdst).Columns))
{
PairObjects(csrc, cdst);
break;
}
}
if (csrc is CheckConstraint)
{
if (((CheckConstraint)csrc).Expression == ((CheckConstraint)cdst).Expression)
{
PairObjects(csrc, cdst);
break;
}
}
}
}
}
public List <CommandResult> ADD_SCAFFOLD_DATA(IConnectToDB _Connect, string TableName)
{
List <CommandResult> HoldResult = new List <CommandResult>();
CommandResult _result = new CommandResult();
Tools.Box er_tools = new Tools.Box();
ER_Generate gen = new ER_Generate();
string newTableName = er_tools.MaxNameLength(TableName, (128 - 16));
string Char_Table = newTableName + "_Dat_Char";
string Date_Table = newTableName + "_Dat_Date";
string Number_Table = newTableName + "_Dat_Numb";
string File_Table = newTableName + "_Dat_File";
string Options_Table = newTableName + "_Dat_Opt";
string Decimal_Table = newTableName + "_Dat_Deci";
string Stages_Name = TableName + "_Stages";
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---Start Data Scaffold " + TableName + "",
_EndTime = DateTime.Now
});
List <ColumnStructure> ColumnsList = new List <ColumnStructure>();
string[] tablesList = { Char_Table, Number_Table, Date_Table, File_Table, Options_Table, Decimal_Table };
foreach (var t in tablesList)
{
HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, t, "Application Data"));
}
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
HoldResult.AddRange(ER_DDL._ADD_TABLE_CHAR(_Connect, TableName, Char_Table, ColumnsList));
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
HoldResult.AddRange(ER_DDL._ADD_TABLE_NUMB(_Connect, TableName, Number_Table, ColumnsList));
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
HoldResult.AddRange(ER_DDL._ADD_TABLE_DATE(_Connect, TableName, Date_Table, ColumnsList));
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
HoldResult.AddRange(ER_DDL._ADD_TABLE_FILE(_Connect, TableName, File_Table, ColumnsList));
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
//HoldResult.AddRange(ER_DDL._ADD_TABLE_OPT(_Connect, TableName, Options_Table, ColumnsList));
//ColumnsList.Add(new ColumnStructure { _Name = "RENDITION", _DataType = "bigint", _DefaultValue = "0", _IsNull = false });
HoldResult.AddRange(ER_DDL._ADD_TABLE_DECIMAL(_Connect, TableName, Decimal_Table, ColumnsList));
foreach (var t in tablesList)
{
ColumnsList.Clear();
var item = new ColumnStructure
{
_Name = t + "_UUID",
_DefaultValue = "",
_IsNull = false,
_DataType = "Guid" // t != Decimal_Table ? "bigint" : "Money";
};
ColumnsList.Add(item);
HoldResult.AddRange(ER_DDL._ADD_INDEX_NONE_CLUSTERED(_Connect, t + "_CI", t, "TABLE", ColumnsList));
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, t, ""));
}
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---End Data Scaffold " + TableName + "",
_EndTime = DateTime.Now
});
return(HoldResult);
}
