/// <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)); }
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); }