/// <summary>
/// Add column to table, create Check and defaults
/// </summary>
private void AddColumn(DBTable table, DbTable designerTable, DbColumn designerColumn)
{
// column
DbType type = DataType.FromDesignerName(designerColumn.Type);
DBColumn col = new DBColumn(_db)
{
Tabloid = table,
Name = designerColumn.Name,
IsNullable = designerColumn.AllowNull,
MaxLength = designerColumn.ColumnLengthIsMax ? DataType.MaxLength(type) : designerColumn.ColumnLength,
Type = type,
DefaultValue = designerColumn.RealDefaultValue(type),
IsUnique = designerColumn.Unique || designerColumn.Name == DBCommandSet.PrimaryKey
};
table.Columns.Add(col);
// metadata
ColumnMetadata cm = _app.ColumnMetadata.SingleOrDefault(c => c.TableName == designerTable.Name && c.ColumnName == designerColumn.Name);
if (cm == null)
{
cm = new ColumnMetadata()
{
TableName = designerTable.Name,
ColumnName = designerColumn.Name
};
_app.ColumnMetadata.Add(cm);
}
cm.ColumnDisplayName = designerColumn.DisplayName ?? designerColumn.Name;
}
public void SetColumn(DBColumn column)
{
if (column != null)
{
SetValue(column.ID, column.Value);
}
}
public ActionResult AddColumn(string appName, string tableName, DBColumn column)
{
DBApp app = new DBApp()
{
Name = appName,
ConnectionString = (new Entities()).Database.Connection.ConnectionString
};
DBTable table = app.GetTable(tableName);
if (table.Select().ToList().Count != 0 && column.canBeNull == false)//condition for not null column, which can be created only in empty table
{
TempData["message-error"] = "Table " + tableName + " must be empty if you want to add NOT NULL column.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
else if (table.columns.SingleOrDefault(x => x.Name == column.Name) != null)//condition for column name, column name can not be equal with other names in table
{
TempData["message-error"] = "Table " + tableName + " has already column with name " + column.Name + ".";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
table.columns.AddToDB(column);
app.SaveChanges();
TempData["message-success"] = "Column " + column.Name + " was successfully added.";
return RedirectToAction("Details", new { @appName = appName, @tableName = tableName });
}
bool IsOneToOneOnTheKey(DBColumn dbColumn)
{
IEnumerable <DBForeignKey> foreignPKeys = _dbTable.ForeignKeys.Where(key => _dbTable.PrimaryKey.Columns.Contains(dbColumn.Name) && key.PrimaryKeyTable != _dbTable.Name && key.Columns.Contains(dbColumn.Name));
var keies = foreignPKeys.Select(key => new { FK = key, PrimaryTable = _classInfos[ClassGenerator.GetTableName(key.PrimaryKeyTable)].DbTable });
return(keies.Any(arg => arg.PrimaryTable.PrimaryKey.Columns.OfType <string>().All(s => arg.FK.PrimaryKeyTableKeyColumns.OfType <string>().Contains(s))));
}
public void LoadColumns(SqlDataConnection connection, params DBTable[] tables)
{
foreach (DBTable table in tables)
{
if (table.Name == "Categories" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID", ColumnType = DBColumnType.Int32
};
table.AddColumn(categoryIdColumn);
DBColumn categoryNameColumn = new DBColumn {
Name = "CategoryName", ColumnType = DBColumnType.String
};
table.AddColumn(categoryNameColumn);
}
if (table.Name == "Products" && table.Columns.Count == 0)
{
DBColumn categoryIdColumn = new DBColumn {
Name = "CategoryID", ColumnType = DBColumnType.Int32
};
table.AddColumn(categoryIdColumn);
DBColumn productNameColumn = new DBColumn {
Name = "ProductName", ColumnType = DBColumnType.String
};
table.AddColumn(productNameColumn);
DBForeignKey foreignKey = new DBForeignKey(
new[] { categoryIdColumn },
"Categories",
CustomDBSchemaProvider.CreatePrimaryKeys("CategoryID"));
table.ForeignKeys.Add(foreignKey);
}
}
}
public TableColumnMapping(TableMapping sourceTableMapping, DBColumn sourceColumn,
DBColumn destinationColumn, ColumnUse columnUse)
: base(destinationColumn, columnUse)
{
this.sourceTableMapping = sourceTableMapping;
this.sourceColumn = sourceColumn;
}
public void TableBodyPartInsertTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List <DBColumn>()
{
col1, col2
};
ColumnMapping colMap1 = new NullColumnMapping(col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(3, bodyParts.Length);
Assert.AreEqual("INSERT INTO dbo.TestTable (TestCol2)", bodyParts[0]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2 INTO @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") +
"(TestCol1, TestCol2)", bodyParts[1]);
Assert.AreEqual("VALUES ('2')", bodyParts[2]);
}
public void TestDBColumn()
{
DBColumn dbc = new DBColumn("admin(NOT_NULL,)");
Assert.AreEqual(false, dbc.Nullable);
Assert.AreEqual(ColumnDateType.None, dbc.DateType);
Assert.AreEqual("admin", dbc.Name);
dbc.ID = 10;
Assert.AreEqual(10, dbc.ID);
dbc.ID = 2;
Assert.AreEqual(10, dbc.ID);
dbc = new DBColumn("admin(NULL,datetime)");
Assert.AreEqual(true, dbc.Nullable);
Assert.AreEqual(ColumnDateType.DateTime, dbc.DateType);
dbc = new DBColumn("admin(NULL,date)");
Assert.AreEqual(true, dbc.Nullable);
Assert.AreEqual(ColumnDateType.Date, dbc.DateType);
dbc = new DBColumn("admin", false, ColumnDateType.None);
Assert.AreEqual(false, dbc.Nullable);
Assert.AreEqual(ColumnDateType.None, dbc.DateType);
Assert.AreEqual("admin", dbc.Name);
dbc = new DBColumn("admin", true, ColumnDateType.DateTime);
Assert.AreEqual(true, dbc.Nullable);
Assert.AreEqual(ColumnDateType.DateTime, dbc.DateType);
Assert.AreEqual("admin", dbc.Name);
}
protected virtual void FillFields(DBTable table)
{
OleDbDataReader rd = OleDbHelper.ExecuteReader(this.OleDbConnStr, string.Format(this.Format_GET_ALL_FIELDS, table.TableName));
List <DBColumn> fields = new List <DBColumn>();
while (rd.Read())
{
try
{
DBColumn column = new DBColumn(rd["fieldName"].ToString());
string fieldType = rd["fieldType"].ToString();
if (this.DataBaseType == DataBaseType.MSSQL)
{
column.DbType = ((SqlDbType)Enum.Parse(typeof(SqlDbType), fieldType, true)).ToJFielType();
}
else
{
column.DbType = ((OracleType)Enum.Parse(typeof(OracleType), fieldType, true)).ToJFielType();
}
column.Length = int.Parse(rd["fieldLength"].ToString());
column.AllowNull = rd["allowNull"].ToString() == "1" ? true : false;
fields.Add(column);
}
catch (Exception ex)
{
}
}
rd.Close();
rd.Dispose();
table.Columns = fields;
}
private void UpdateColumnSize(IEnumerable <DBTable> tables, ConnectionProviderSql sqlDataStore)
{
foreach (var table in tables.Where(table => !HashSet.Contains(table.Name)))
{
HashSet.Add(table.Name);
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), StringComparison.OrdinalIgnoreCase) == 0);
if (NeedsAltering(column, actualColumn))
{
if ((actualColumn.Size < column.Size) || (column.Size == SizeAttribute.Unlimited))
{
var sql = GetSql(table, sqlDataStore, column);
Trace.WriteLineIf(new TraceSwitch("XPO", "").TraceInfo, sql);
sqlDataStore.ExecSql(new Query(sql));
}
}
}
}
}
/// <summary>
/// 设置映射对象相应属性值
/// </summary>
/// <param name="column">对应数据库中的列</param>
/// <param name="persistent">映射对象</param>
/// <param name="value">设置的值</param>
protected void SetValue(DBColumn column, object persistent, object value)
{
if (column.SetHandler != null)
{
if (column.Type == typeof(bool) && !(value is bool))
{
value = (int)value == 1;
}
else if (column.ColumnType == DBColumnType.TimeSpan)
{
value = new TimeSpan((long)value);
}
else if (column.ColumnType == DBColumnType.Guid && !(value is Guid))
{
value = new Guid((string)value);
}
else if (column.ColumnType == DBColumnType.Xml && !(value is XmlDocument))
{
var xml = (new XmlDocument());
xml.LoadXml((string)value);
value = xml;
}
column.SetHandler(persistent, value);
}
}
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));
}
}
}
}
}
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);
}
}
bool NeedsAltering(DBColumn column, DBColumn actualColumn)
{
return((actualColumn != null) &&
(actualColumn.ColumnType == DBColumnType.String) &&
(actualColumn.Size != column.Size) &&
(column.DBTypeName != string.Format("varchar({0})", actualColumn.Size)));
}
public virtual ActionResult Update(DBColumn entity)
{
entity.LastUpdatedTime = DateTime.Now;
OperationResult or = DBColumnService.Update(entity);
return(this.JsonFormat(or));
}
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} alter column {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column));
}
public void Initialize(DBTable table, DBItem row, DBColumn ownColumn, TableEditorMode openmode, bool readOnly)
{
if (this.table == null)
{
this.table = table;
}
if (Name == "")
{
Name = table.Name + ownColumn?.Name;
}
TableExplorerNode node = null;
if (openmode == TableEditorMode.Item)
{
node = SelectRow(null, table, row, ownColumn, readOnly);
}
else
{
node = InitToolTable(table, row, ownColumn, openmode, readOnly);
Tree.Nodes.Add(node);
}
Current = node;
}
private void BtnSave_Click(object sender, EventArgs e)
{
DBColumn dbColumn = new DBColumn()
{
ColumnName = txtColumnName.Text,
AllowNull = checkBoxAllowNull.Checked,
DbType = JFieldType.String,
};
int length = 0;
if (!string.IsNullOrEmpty(txtLength.Text))
{
int.TryParse(txtLength.Text, out length);
dbColumn.Length = length;
}
if (!string.IsNullOrEmpty(comboBoxSQLDBType.Text))
{
JFieldType dbType;
if (Enum.TryParse <JFieldType>(comboBoxSQLDBType.Text, out dbType))
{
dbColumn.DbType = dbType;
}
}
JField field = new JField(dbColumn);
if (AddFieldDelegate != null)
{
AddFieldDelegate(field);
}
this.Close();
}
public void TableVariablePartTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List <DBColumn>()
{
col1, col2
};
ColumnMapping colMap1 = new LiteralColumnMapping("1", LiteralType.Integer, col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, null);
string partStatement = part.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual("DECLARE @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") + " TABLE (TestCol1 integer, TestCol2 nvarchar(max))",
partStatement);
}
bool NeedsAltering(DBColumn column, DBColumn actualColumn)
{
return((actualColumn != null) &&
(actualColumn.ColumnType == DBColumnType.String) &&
(actualColumn.Size != column.Size) &&
(column.DBTypeName != $"varchar({actualColumn.Size})"));
}
public void CycleTestTwoTable()
{
DBTable table1 = new DBTable("dbo", "1");
DBColumn table1ID = new DBColumn(table1, "1_id", true, DBDatatype.integer);
table1.Columns = new List <DBColumn>()
{
table1ID
};
DBTable table2 = new DBTable("dbo", "2");
DBColumn table2ID = new DBColumn(table2, "2_id", true, DBDatatype.integer);
table2.Columns = new List <DBColumn>()
{
table2ID
};
TableMapping t1Mapping = new TableMapping(table1, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t2Mapping = new TableMapping(table2, TableMappingImportType.Insert, new ColumnMapping[0]);
TableColumnMapping t1ColMaping = new TableColumnMapping(t2Mapping, table2ID, table1ID, ColumnUse.Insert);
TableColumnMapping t2ColMaping = new TableColumnMapping(t1Mapping, table1ID, table2ID, ColumnUse.Insert);
t1Mapping.ColumnMappings = new ColumnMapping[] { t1ColMaping };
t2Mapping.ColumnMappings = new ColumnMapping[] { t2ColMaping };
TableMapping[] mappingArray = new TableMapping[] { t1Mapping, t2Mapping };
TableMappingOrderer tableMappingOrderer = new TableMappingOrderer(mappingArray);
TableMapping[] order = tableMappingOrderer.OrderTableMappings();
}
private void ToolReferencesClick(object sender, EventArgs e)
{
var tool = (MenuItemRelation)sender;
toolReference.Text = tool.Text;
if (tool.View == null)
{
tool.View = tool.Relation.Table.CreateItemsView("", DBViewKeys.Empty, DBStatus.Actual | DBStatus.Edit | DBStatus.New | DBStatus.Error);
}
tool.View.DefaultParam = new QParam(LogicType.And, tool.Relation.Column, CompareType.Equal, OwnerRow.PrimaryId);
baseColumn = tool.Relation.Column;
TableView = tool.View;
loader.LoadAsync(tool.View.Query);
//if (ReferenceClick != null)
// ReferenceClick(this, new TableEditReferenceEventArgs(relation));
//else
//{
// TableEditor te = new TableEditor();
// te.Initialize(relation.Table.CreateRowView(DBViewInitMode.None, DBStatus.Current), Selected, relation.Column, TableFormMode.RefingTable, _access);
// Form f = DataCtrlService.WrapControl(te);
// f.ShowDialog(this);
//}
}
public override void CreateColumn(DBTable table, DBColumn column)
{
table.Name = table.Name.ToLower();
column.Name = column.Name.ToLower();
base.CreateColumn(table, column);
}
public bool IsSerializeableColumn(DBColumn column)
{
return(column.PropertyInvoker != null
//&& (column.Attribute.Keys & DBColumnKeys.Access) != DBColumnKeys.Access
&& (column.Keys & DBColumnKeys.Password) != DBColumnKeys.Password &&
(column.Keys & DBColumnKeys.File) != DBColumnKeys.File);
}
public override DbParameter CreateParameter(DBColumn column)
{
if (column == null)
{
return(null);
}
return(new OracleParameter(PREFIX_PARM + column.ID, column.Value));
}
public ActionResult DeleteConfirmed(int id)
{
DBColumn dBColumn = db.DbColumns.Find(id);
db.DbColumns.Remove(dBColumn);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public override DbParameter CreateParameter(DBColumn column)
{
if (column == null)
{
return(null);
}
return(CreateParameter(column.ID, column.Value));
}
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 DBColumn GetColumn()
{
int index = ParamCode.IndexOf(' ');
string code = ParamCode.Substring(0, index < 0 ? ParamCode.Length : index);
DBColumn column = DBService.Schems.ParseColumn(code, Table.Schema);
return(column);
}
public static void CreateColumn(this ConnectionProviderSql connectionProviderSql, XPMemberInfo xpMemberInfo, bool throwUnableToCreateDBObjectException = false)
{
var dbColumnType = GetDbColumnType(xpMemberInfo);
var column = new DBColumn(xpMemberInfo.Name, false, null, xpMemberInfo.MappingFieldSize, dbColumnType);
CallSchemaUpdateMethod(connectionProviderSql, sql => CreateColumnCore(xpMemberInfo, throwUnableToCreateDBObjectException, sql, column), throwUnableToCreateDBObjectException);
connectionProviderSql.CreateForeignKey(xpMemberInfo, throwUnableToCreateDBObjectException);
}
public override void OnPropertyChanged(string property, DBColumn column = null, object value = null)
{
base.OnPropertyChanged(property, column, value);
if (Attached)
{
GetReference <Document>(DocumentKey, ref document, DBLoadParam.None)?.OnReferenceChanged(this);
}
}
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, true),
(sqlDataStore is BaseOracleConnectionProvider || sqlDataStore is MySqlConnectionProvider) ? "modify" : "alter column"));
}
static void CreateColumnCore(XPMemberInfo xpMemberInfo, bool throwUnableToCreateDBObjectException, ConnectionProviderSql sql,
DBColumn column) {
try {
sql.CreateColumn(xpMemberInfo.Owner.Table, column);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
throw;
}
}
public CompoundPKMemberGeneratorHelper(IObjectSpace XPObjectSpace)
: base(XPObjectSpace) {
_dbColumn1 = new DBColumn { Name = "DBColumn1", ColumnType = DBColumnType.Int32 };
_dbColumn2 = new DBColumn { Name = "DBColumn2", ColumnType = DBColumnType.Int32 };
var dbColumns = new[] { _dbColumn1, _dbColumn2 };
DbTable.Columns.AddRange(dbColumns);
DbTable.PrimaryKey = new DBPrimaryKey(dbColumns);
_structPersistentClassInfo = XPObjectSpace.CreateObject<PersistentClassInfo>();
_structPersistentClassInfo.Name = "MainTable" + TableMapper.KeyStruct;
_structPersistentClassInfo.PersistentAssemblyInfo = (PersistentAssemblyInfo)PersistentAssemblyInfo;
_structPersistentClassInfo.SetDefaultTemplate(TemplateType.Struct);
ClassGeneratorInfos.Add(_structPersistentClassInfo.Name, new ClassGeneratorInfo(StructPersistentClassInfo, DbTable));
}
void GetColumns(DBTable table) {
foreach (SelectStatementResultRow row in SelectData(new Query(string.Format(CultureInfo.InvariantCulture, "show columns from `{0}`", ComposeSafeTableName(table.Name)))).Rows) {
int size;
string rowValue1, rowValue5, rowValue0;
if (row.Values[1].GetType() == typeof(Byte[])) {
rowValue1 = System.Text.Encoding.Default.GetString((byte[])row.Values[1]);
rowValue5 = System.Text.Encoding.Default.GetString((byte[])row.Values[5]);
rowValue0 = System.Text.Encoding.Default.GetString((byte[])row.Values[0]);
} else {
rowValue1 = (string)row.Values[1];
rowValue5 = (string)row.Values[5];
rowValue0 = (string)row.Values[0];
}
DBColumnType type = GetTypeFromString(rowValue1, out size);
bool isAutoIncrement = false;
string extraValue = rowValue5;
if (!string.IsNullOrEmpty(extraValue) && extraValue.Contains("auto_increment")) isAutoIncrement = true;
var column = new DBColumn(rowValue0, false, String.Empty, type == DBColumnType.String ? size : 0, type)
{IsIdentity = isAutoIncrement};
table.AddColumn(column);
}
}
public RefMemberGeneratorHelper(IObjectSpace XPObjectSpace)
: base(XPObjectSpace) {
var column = new DBColumn("Oid", false, "int", 0, DBColumnType.Int32);
DbTable.PrimaryKey = new DBPrimaryKey(new[] { column });
var dbColumn = new DBColumn { Name = "DBColumn", ColumnType = DBColumnType.Int32 };
DbTable.Columns.Add(dbColumn);
DbTable.Columns.Add(column);
var refDbColumn = new DBColumn { Name = "refDbColumn", ColumnType = DBColumnType.Int32, IsKey = true };
DbTable.Columns.Add(refDbColumn);
var primaryKeyTableKeyColumns = new StringCollection { "refDbColumn" };
DbTable.ForeignKeys.Add(new DBForeignKey(new List<DBColumn> { refDbColumn }, _refDbTable.Name, primaryKeyTableKeyColumns));
var item = new DBColumn("Oid", true, "int", 0, DBColumnType.Int32);
_refDbTable.Columns.Add(item);
_refDbTable.PrimaryKey = new DBPrimaryKey(new[] { item });
_refPersistentClassInfo = XPObjectSpace.CreateObject<PersistentClassInfo>();
_refPersistentClassInfo.Name = RefDbTable.Name;
PersistentAssemblyInfo.PersistentClassInfos.Add(_refPersistentClassInfo);
_refPersistentClassInfo.SetDefaultTemplate(TemplateType.Class);
ClassGeneratorInfos.Add(_refPersistentClassInfo.Name, new ClassGeneratorInfo(_refPersistentClassInfo, RefDbTable));
}
/// <summary>Возвращает тип данных колонки в формате, хранимом в структуре таблицы БД</summary>
/// <param name="table">Таблица, которой принадлежит колонка</param>
/// <param name="column">Колонка, для которой нужно определить тип данных</param>
/// <returns>Тип данных колонки в формате, хранимом в БД</returns>
protected string GetDBTableColumnType(DBTable table, DBColumn column)
{
string result = GetSqlCreateColumnType(table, column).ToUpper()
.Replace(" ", string.Empty)
.Replace("INTEGER", "INT")
.Replace("NUMERIC", "NUMBER")
.Replace("DOUBLEPRECISION", "FLOAT");
if (result == "CHAR" || result == "NCHAR") result = result + "(1)";
if (result.StartsWith("NUMBER(") && result.IndexOf(',') < 0)
result = result.Substring(0, result.Length - 1) + ",0)";
return result;
}
/// <summary>
/// Определяет, является ли изменение типа колонки безопасным
/// </summary>
/// <param name="column">Колонка таблицы, которая подлежит изменению</param>
/// <param name="newColumn">Колонка, которая является шаблоном для изменений</param>
/// <returns>True, если изменение типа колонки безопасное, иначе false</returns>
public virtual bool IsColumnChangeSafe(DBColumn column, DBColumn newColumn)
{
return newColumn.ColumnType == column.ColumnType && newColumn.Size > column.Size && column.Size > 0;
}
/// <summary>
/// Выполняет указанную команду администрирования безопасности
/// </summary>
/// <param name="statement">Команда администрирования безопасности</param>
/// <returns>Объекты безопасности в результате выполнения указанной команды в зависимости от типа команды <see cref="AdminSecurityOperations"/></returns>
protected virtual IEnumerable<SecurityObject> AdminSecurity(SecurityStatement statement)
{
// Команда запроса объектов администрирования
if (statement.Operation == AdminSecurityOperations.GetRolePrivileges ||
statement.Operation == AdminSecurityOperations.GetCurrentPrivileges ||
statement.Operation == AdminSecurityOperations.GetTable)
{
List<SecurityObject> result = new List<SecurityObject>();
SecurityObjectTypes objectType = statement.LeftOperand != null ? statement.LeftOperand.ObjectType : SecurityObjectTypes.UserInfo;
string objectName = statement.LeftOperand != null ? statement.LeftOperand.ObjectName : null;
SecurityObject tableSecurity = null;
switch (statement.Operation)
{
// Запрос привилегий роли
case AdminSecurityOperations.GetRolePrivileges:
foreach (SelectStatementResultRow row in SelectSimple(
GetUserPrivilege("dba_role_privs") ?
"select owner||'.'||table_name table_name from dba_tab_privs where grantee = :pRole" :
"select owner||'.'||table_name table_name from role_tab_privs where role = :pRole",
":pRole", objectName, "table_name").Rows)
if (GetTablePrivilege((string)row.Values[0], out tableSecurity)) result.Add(tableSecurity);
break;
// Запрос текущих привилегий пользователя
case AdminSecurityOperations.GetCurrentPrivileges:
foreach (SelectStatementResultRow row in SelectData(new Query(
"select owner||'.'||view_name from all_views where owner <> 'SYS' and owner <> 'SYSTEM' and view_name like 'VW:_%' escape ':' union all " +
"select owner||'.'||object_name from all_procedures where owner <> 'SYS' and owner <> 'SYSTEM' and object_name like 'P:_%' escape ':'")).Rows)
if (GetTablePrivilege((string)row.Values[0], out tableSecurity)) result.Add(tableSecurity);
break;
// Запрос таблицы для объектов
case AdminSecurityOperations.GetTable:
// Таблица пользователей (Id, IsActive, IsExpired, Locked, Created)
if (objectType == SecurityObjectTypes.User && statement.RightOperand == null)
{
DBColumn username = new DBColumn("username", true, null, 30, DBColumnType.String);
DBColumn isActive = new DBColumn("is_active", false, null, 0, DBColumnType.Boolean);
DBColumn isExpired = new DBColumn("is_expired", false, null, 0, DBColumnType.Boolean);
DBColumn locked = new DBColumn("lock_date", false, null, 0, DBColumnType.DateTime);
DBColumn created = new DBColumn("created", false, null, 0, DBColumnType.DateTime);
DBColumn nullValue = new DBCriteriaColumn("null", false, null, 0, DBColumnType.String, new OperandValue(null));
tableSecurity =
GetSecurityTable("(select username, " +
"cast(decode(instr(account_status,'LOCKED'),0,1,0) as number(1)) is_active, " +
"cast(decode(instr(account_status,'EXPIRED'),0,0,1) as number(1)) is_expired, " +
"lock_date, created from dba_users)",
new string[] { "dba_users" }, username, isActive, isExpired, locked, created) ??
// isActive, isExpired не имеет смысла брать из user_users
GetSecurityTable("all_users", null, username, nullValue, nullValue, nullValue, created) ??
GetSecurityTable("user_users", null, username, nullValue, nullValue, locked, created);
}
// Таблица ролей (Id)
else if (objectType == SecurityObjectTypes.Role && statement.RightOperand == null)
{
tableSecurity =
GetSecurityTable("dba_roles", "role") ??
// Выделена в запрос, чтобы не путать с использованием в таблице ролей пользователей с тремя полями
GetSecurityTable("(select granted_role from user_role_privs)", "granted_role", new string[] { "user_role_privs" });
}
// Таблица иерархии ролей (Parent, Child)
else if (objectType == SecurityObjectTypes.Role && statement.RightOperand != null &&
statement.RightOperand.ObjectType == SecurityObjectTypes.Role)
{
tableSecurity =
GetSecurityTable("(select grantee, granted_role from dba_role_privs, dba_roles where grantee = role)",
"grantee", "granted_role", new string[] { "dba_role_privs", "dba_roles" }) ??
GetSecurityTable("role_role_privs", "role", "granted_role");
}
// Таблица ролей пользователей (Parent, Child)
else if (statement.Operation == AdminSecurityOperations.GetTable && statement.RightOperand != null && (
(statement.LeftOperand.ObjectType == SecurityObjectTypes.Role && statement.RightOperand.ObjectType == SecurityObjectTypes.User) ||
(statement.LeftOperand.ObjectType == SecurityObjectTypes.User && statement.RightOperand.ObjectType == SecurityObjectTypes.Role)))
{
tableSecurity =
GetSecurityTable("(select grantee, granted_role from dba_role_privs, dba_users where grantee = username)",
"grantee", "granted_role", new string[] { "dba_role_privs", "dba_users" }) ??
GetSecurityTable("user_role_privs", "username", "granted_role");
}
if (tableSecurity != null) result.Add(tableSecurity);
break;
}
return result;
}
// Команда модификации объектов администрирования
foreach (string sql in new AdminSecurityGenerator(statement, this).GenerateSqlCommands())
{
IDbCommand command = CreateCommand();
command.CommandText = sql;
try
{
command.ExecuteNonQuery();
Trace.WriteLineIf(xpoSwitch.TraceInfo, sql, "AdminSecurity");
}
catch (Exception e)
{
throw WrapException(e, command);
}
}
return null;
}
/// <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);
}
/// <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;
}
/// <summary>
/// Изменение типа колонки
/// </summary>
/// <param name="table">Таблица</param>
/// <param name="column">Колонка таблицы, которая подлежит изменению</param>
/// <param name="newColumn">Колонка, которая является шаблоном для изменений</param>
public virtual void ChangeColumn(DBTable table, DBColumn column, DBColumn newColumn)
{
string command = String.Format(CultureInfo.InvariantCulture, "alter table {0} modify ({1} {2})",
FormatTableSafe(table), FormatColumnSafe(column.Name), GetSqlCreateColumnType(table, newColumn));
if (IsColumnChangeSafe(column, newColumn))
ExecuteSqlSchemaUpdate("Column", column.Name, table.Name, command);
else
UnsafeSchemaUpdate("Column type", column.Name, table.Name, command + ";");
column.ColumnType = newColumn.ColumnType;
column.Size = newColumn.Size;
}
/// <summary>
/// Изменение констрейнта обязательности колонки (not null)
/// </summary>
/// <param name="table">Таблица</param>
/// <param name="column">Колонка таблицы, которая подлежит изменению</param>
/// <param name="notNull">Значение констрейнта обязательности: true - обязательные значения, false - необязательные значения</param>
public virtual void ChangeColumnNullable(DBTable table, DBColumn column, bool notNull)
{
string command = String.Format(CultureInfo.InvariantCulture, "alter table {0} modify ({1} {2})",
FormatTableSafe(table), FormatColumnSafe(column.Name), notNull ? "not null" : "null");
if (!notNull)
ExecuteSqlSchemaUpdate("Column", column.Name, table.Name, command);
else
UnsafeSchemaUpdate("Column mandatory", column.Name, table.Name, command + ";");
}
private void GetColumns(DBTable table)
{
string schema = ComposeSafeSchemaName(table.Name);
string tableName = ComposeSafeTableName(table.Name);
const string selectFields =
@"select column_name, data_type, char_col_decl_length, data_precision, data_scale,
case when data_type='NUMBER' and data_precision is null and data_scale is null then data_type
when data_type='NUMBER' and data_precision is null and data_scale = 0 then 'INT'
when data_type='NUMBER' then data_type||'('||data_precision||','||data_scale||')'
when data_type in ('CHAR','NCHAR','VARCHAR2','NVARCHAR2') then data_type||'('||char_col_decl_length||')'
when data_type='RAW' then data_type||'('||data_length||')'
else data_type end full_type, nullable";
Query query = string.IsNullOrEmpty(schema) ?
new Query(selectFields + " from user_tab_columns where table_name = :p0",
new QueryParameterCollection(new OperandValue(tableName)), new string[] { ":p0" }) :
new Query(selectFields + " from all_tab_columns where owner = :p0 and table_name = :p1",
new QueryParameterCollection(new OperandValue(schema), new OperandValue(tableName)), new string[] { ":p0", ":p1" });
foreach (SelectStatementResultRow row in SelectData(query).Rows)
{
string name = (string)row.Values[0];
int size = row.Values[2] != DBNull.Value ? ((IConvertible)row.Values[2]).ToInt32(CultureInfo.InvariantCulture) : 0;
int precision = row.Values[3] != DBNull.Value ? ((IConvertible)row.Values[3]).ToInt32(CultureInfo.InvariantCulture) : 0;
int? scale = row.Values[4] != DBNull.Value ? ((IConvertible)row.Values[4]).ToInt32(CultureInfo.InvariantCulture) : (int?)null;
DBColumnType type = GetTypeFromString((string)row.Values[1], size, precision, scale);
string dbType = (string)row.Values[5];
string nullable = (string)row.Values[6];
DBColumn column = new DBColumn(name, false, dbType, type == DBColumnType.String ? size : 0, type);
table.AddColumn(column);
if (nullable == "N" && table is DBTableEx)
((DBTableEx)table).AddConstraint(new DBNotNullConstraint(column));
}
}
private bool IsColumnExists(DBTable table, DBColumn column)
{
return FindColumnByName(table, column) != null;
}
bool IsSelfRefOnTheKey(DBColumn dbColumn, bool isPrimaryKey) {
if (!isPrimaryKey)
return false;
return _dbTable.ForeignKeys.FirstOrDefault(
key => key.PrimaryKeyTable == _dbTable.Name && key.Columns.Contains(dbColumn.Name)) != null;
}
IPersistentCoreTypeMemberInfo CreatePersistentCoreTypeMemberInfo(DBColumn column, IPersistentClassInfo persistentClassInfo, TemplateType templateType) {
var persistentCoreTypeMemberInfo = _objectSpace.CreateWCObject<IPersistentCoreTypeMemberInfo>();
persistentCoreTypeMemberInfo.Name = column.Name;
persistentCoreTypeMemberInfo.DataType = column.ColumnType;
if (persistentClassInfo == null) {
var tableName = ClassGenerator.GetTableName(_dbTable.Name);
persistentClassInfo = _classInfos[tableName].PersistentClassInfo;
}
persistentClassInfo.OwnMembers.Add(persistentCoreTypeMemberInfo);
persistentCoreTypeMemberInfo.SetDefaultTemplate(templateType);
return persistentCoreTypeMemberInfo;
}
public MemberGeneratorInfo(IPersistentMemberInfo persistentMemberInfo, DBColumn dbColumn)
: this() {
_persistentMemberInfo = persistentMemberInfo;
_dbColumn = dbColumn;
}
static bool IsCoreColumn(DBColumn dbColumn) {
return !dbColumn.IsIdentity && !dbColumn.IsKey;
}
bool IsFKey(DBColumn dbColumn) {
return _dbTable.ForeignKeys.FirstOrDefault(key => key.Columns.Contains(dbColumn.Name)) != null;
}
static void Setup(DBTable refTable, DBTable mainTable) {
var dbColumns = new List<DBColumn>();
var dbColumn = new DBColumn { Name = "Oid2" };
dbColumns.Add(dbColumn);
dbColumns.Add(mainTable.GetColumn("Oid"));
mainTable.PrimaryKey = new DBPrimaryKey(dbColumns);
mainTable.Columns.Add(dbColumn);
mainTable.AddForeignKey(new DBForeignKey(dbColumns, refTable.Name, new StringCollection { "Oid", "Oid2" }));
}
bool IsPrimaryKey(DBColumn dbColumn) {
return _dbTable.PrimaryKey.Columns.Contains(dbColumn.Name);
}
bool NeedsAltering(DBColumn column, DBColumn actualColumn) {
return (actualColumn != null) &&
(actualColumn.ColumnType == DBColumnType.String) &&
(actualColumn.Size != column.Size) &&
(column.DBTypeName != string.Format("varchar({0})", actualColumn.Size));
}
MemberGeneratorInfo CreateMember(DBColumn dbColumn, IPersistentClassInfo persistentClassInfo = null, TemplateType coreTemplateType = TemplateType.XPReadWritePropertyMember, TemplateType refTemplateType = TemplateType.XPReadWritePropertyMember) {
return CreateMemberCore(dbColumn, persistentClassInfo, coreTemplateType, refTemplateType);
}
/// <summary>
/// Получить для указанной колонки таблицы тип параметра процедуры в формате sql-команды
/// </summary>
/// <param name="table">Таблица</param>
/// <param name="column">Колонка</param>
/// <returns>Тип параметра процедуры в формате sql-команды</returns>
/// <remarks>По умолчанию удаляет из типа колонки (см. <see cref="ConnectionProviderSql.GetSqlCreateColumnType"/>) значения, указанные в скобках</remarks>
protected virtual string GetSqlProcedureParameterType(DBTable table, DBColumn column)
{
if (column == OracleTemplater.ColumnRowid) return OracleTemplater.Rowid;
string result = GetSqlCreateColumnType(table, column);
int par = result.IndexOf('(');
return par > 0 ? result.Remove(par) : result;
}
bool IsOneToOneOnTheKey(DBColumn dbColumn) {
IEnumerable<DBForeignKey> foreignPKeys = _dbTable.ForeignKeys.Where(key => _dbTable.PrimaryKey.Columns.Contains(dbColumn.Name) && key.PrimaryKeyTable != _dbTable.Name && key.Columns.Contains(dbColumn.Name));
var keies = foreignPKeys.Select(key => new { FK = key, PrimaryTable = _classInfos[ClassGenerator.GetTableName(key.PrimaryKeyTable)].DbTable });
return keies.Any(arg => arg.PrimaryTable.PrimaryKey.Columns.OfType<string>().All(s => arg.FK.PrimaryKeyTableKeyColumns.OfType<string>().Contains(s)));
}
private DBColumn FindColumnByName(DBTable table, DBColumn column)
{
string columnName = ComposeSafeColumnName(column.Name);
for (int i = 0; i < table.Columns.Count; i++)
if (string.Compare(table.Columns[i].Name, columnName, true) == 0)
return table.Columns[i];
return null;
}
MemberGeneratorInfo CreateMemberCore(DBColumn dbColumn, IPersistentClassInfo persistentClassInfo,
TemplateType coreTemplateType, TemplateType refTemplateType) {
bool isPrimaryKey = IsPrimaryKey(dbColumn);
bool isFkColumn = IsFKey(dbColumn);
var isOneToOneOnTheKey = IsOneToOneOnTheKey(dbColumn);
var b = ((!isFkColumn) && (IsCoreColumn(dbColumn) || isPrimaryKey)) || (isOneToOneOnTheKey);
if (b || IsSelfRefOnTheKey(dbColumn, isPrimaryKey)) {
return new MemberGeneratorInfo(CreatePersistentCoreTypeMemberInfo(dbColumn, persistentClassInfo, coreTemplateType), dbColumn);
}
if (isFkColumn) {
return CreateFkMember(dbColumn, persistentClassInfo, coreTemplateType, refTemplateType);
}
throw new NotImplementedException(dbColumn.Name);
}
