private IEnumerable <string> CreateAdditionalPrimaryKeyIndexForCIAI_IfNeeded(ITableSourceInfo existingTable, IPlatformTableSourceColumnInfo column, bool ciai_IndexExists) { var dbConfig = DatabaseServices.DatabaseConfiguration as RuntimeDatabaseConfiguration; if (dbConfig == null) { return(Enumerable.Empty <string>()); } if (column != null && column.DataType.Type == DBDataType.TEXT && dbConfig.CI_AI && !ciai_IndexExists) { // adding the PRIMARY KEY constraint creates an index // but if we're using CI/AI and the PK is a text field, we should create something else too... IPlatformDMLIdentifiers dmlIdentifiers = DatabaseServices.DMLService.Identifiers; var primaryKeyName = GetGeneratedPrimaryKeyConstraintNameForTable(existingTable); string idxName = primaryKeyName.Replace(PlatformDatabaseObjectConstants.PrimaryConstPrefix, PrimaryConstInvariantPrefix).Replace( PlatformDatabaseObjectConstants.PrimarySysConstPrefix, PrimarySysConstInvariantPrefix); return(string.Format("CREATE UNIQUE INDEX {0} ON {1} (NLSSORT({2}, 'NLS_SORT=BINARY_AI'))", dmlIdentifiers.EscapeAndQualifyIdentifierForLocalDatabase(idxName), dmlIdentifiers.EscapeAndQualifyIdentifierForLocalDatabase(existingTable.Name), dmlIdentifiers.EscapeIdentifier(column.Name)).ToEnumerable()); } return(Enumerable.Empty <string>()); }
public PGDMLEntityActions(IDMLService service, ITableSourceInfo tableSourceInfo) : base(service, tableSourceInfo) { getforupdateplaceholder = new Dictionary <SelectPlaceholder, string>() { { SelectPlaceholder.AfterStatement, "FOR UPDATE" } }; }
public override bool Equals(ITableSourceInfo other) { TableSourceInfo tsi = other as TableSourceInfo; return(ReferenceEquals(this, other) || (tsi != null && Database.Equals(tsi.Database) && Schema.EqualsIgnoreCase(tsi.Schema) && Name.EqualsIgnoreCase(tsi.Name))); }
public override IEnumerable <ITableSourceColumnInfo> GetTableSourceColumns(ITableSourceInfo tableSource) { PGTableSource source = tableSource as PGTableSource; if (source == null) { return(null); } List <ITableSourceColumnInfo> res = new List <ITableSourceColumnInfo>(); using (IDbConnection connection = GetConnection()) { IDbCommand cmd = CreateCommand(connection, GetTableSourceColumnsQuery()); CreateParameter(cmd, "schema", DbType.String, source.Database.Identifier); CreateParameter(cmd, "tableName", DbType.String, source.Name); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { res.Add(new PGColumnInfo(tableSource, reader)); } } return(res); } }
public override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable) { var sql = new StringBuilder(); string triggerName = ((newTrigger.Name.ToUpperInvariant().EndsWith("_I") || newTrigger.Name.ToUpperInvariant().EndsWith("_U")) ? newTrigger.Name.Substring(0, newTrigger.Name.Length - 2) : newTrigger.Name).Right(Identifiers.MaxLength - 2); ITableSourceInfo table = newTrigger.TableSource; sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_I")); sql.Append(" AFTER INSERT ON " + Identifiers.EscapeIdentifier(table.Name)); sql.Append(" FOR EACH ROW "); sql.Append(" BEGIN "); sql.Append(" DECLARE isUpdating integer default 0;"); FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating"); sql.AppendLine(" END;"); sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_U")); sql.Append(" AFTER UPDATE ON " + Identifiers.EscapeIdentifier(table.Name)); sql.Append(" FOR EACH ROW "); sql.Append(" BEGIN "); sql.Append(" DECLARE isUpdating integer default 1;"); FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating"); sql.AppendLine(" END;"); return(sql.ToString().ToEnumerable()); }
public static IEnumerable <IPlatformTableSourceColumnInfo> GetPlatformTableSourceColumns( this IPlatformIntrospectionService platformIntrospectionService, ITableSourceInfo tableSource) { IDictionary <ITableSourceInfo, IPlatformTableSourceInfo> result = platformIntrospectionService.GetTableSourcesDetails(tableSource); return(result.IsEmpty()? null: result.First().Value.Columns); }
public override IEnumerable <string> DropIndex(IPlatformTableSourceIndexInfo existingIndex) { ITableSourceInfo table = existingIndex.TableSource; yield return(GetSpecialSchemaStatement(table.Database, String.Format("DROP INDEX {0}.{1}", Identifiers.EscapeIdentifier(table.Name), Identifiers.EscapeIdentifier(existingIndex.Name)))); }
/// <summary> /// This method generates the SQL to grant permissions on a table source to a user. /// This implementation returns "GRANT permissions ON FullyQualifiedTableName TO userName" /// </summary> /// <param name="existingTableSource">Info about the table or view which we want to grant permissions on.</param> /// <param name="username">User to grant permissions.</param> /// <param name="permissions">Permissions to grant to the user.</param> /// <returns>SQL statements to grant permissions.</returns> public virtual IEnumerable <string> GrantPermissions(ITableSourceInfo existingTableSource, string username, Permissions permissions) { IList <string> permissionList = new List <string>(); if (permissions.HasPermissions(Permissions.Select)) { permissionList.Add("SELECT"); } if (permissions.HasPermissions(Permissions.Insert)) { permissionList.Add("INSERT"); } if (permissions.HasPermissions(Permissions.Update)) { permissionList.Add("UPDATE"); } if (permissions.HasPermissions(Permissions.Delete)) { permissionList.Add("DELETE"); } string statement = String.Format("GRANT {0} ON {1} TO {2}", permissionList.StrCat(","), existingTableSource.QualifiedName, Identifiers.EscapeIdentifier(username)); return(statement.ToEnumerable()); }
public override IEnumerable <string> CreateTable(ITableSourceInfo newTable, params ColumnDetails[] columns) { var dbConfig = DatabaseServices.DatabaseConfiguration as MySQLRuntimeDatabaseConfiguration; if (dbConfig == null) { return(Enumerable.Empty <string>()); } bool hasAutoGeneratedColumns = columns.Any(c => c.Column.IsAutoGenerated); string createTableStatement = base.CreateTable(newTable, columns).Single(); IList <string> statements = new List <string>(); statements.Add(createTableStatement); if (hasAutoGeneratedColumns) { foreach (ColumnDetails column in columns.Where(col => col.Column.IsAutoGenerated)) { statements.AddRange(SetColumnToAutonumber(column.Column)); } } return(statements); }
public TableSourceColumnInfo(ITableSourceInfo tableSource, string name, IDataTypeInfo dataType, bool isMandatory, bool isPrimaryKey, AutoNumberColumnInfo autoNumberInfo) : this(tableSource, name, dataType, isMandatory, isPrimaryKey) { this.autoNumberInfo = autoNumberInfo; // The auto-number is only valid if all fields are filled IsAutoGenerated = autoNumberInfo != null && !autoNumberInfo.SequenceName.IsNullOrEmpty() && !autoNumberInfo.TriggerName.IsNullOrEmpty(); }
public PlatformTableSourceIndexInfo(ITableSourceInfo tableSource, string name, bool isUnique, bool isPrimaryKey) { TableSource = tableSource; Name = name; IsUnique = isUnique; IsPrimaryKey = isPrimaryKey; }
public ITableSourceForeignKeyInfo CreateForeignKeyInfo(ITableSourceInfo tableSource, string foreignKeyName, string columnName, ITableSourceInfo referencedTableSource, string referencedColumnName, bool isCascadeDelete) { foreignKeyName = String.IsNullOrEmpty(foreignKeyName)? this.GetNewForeignKeyName(databaseServices, tableSource.Name, referencedTableSource.Name, columnName): foreignKeyName; return(new TableSourceForeignKeyInfo(tableSource, foreignKeyName, columnName, referencedTableSource, referencedColumnName, isCascadeDelete)); }
/// <summary> /// Returns the list of columns of the table source (e.g. table, view) /// </summary> /// <param name="tableSource">Table source from which we want to fetch the list of columns</param> /// <returns>The columns of the table</returns> /// <exception cref="System.Data.Common.DbException">if an error occurs while accessing the database</exception> public override IEnumerable <ITableSourceColumnInfo> GetTableSourceColumns(ITableSourceInfo tableSource) { var ts = tableSource as CacheTableSourceInfo; /* TODO: Remove */ System.Diagnostics.Trace.WriteLine(ts == null ? "ts == null" : "go ahead get columns"); return((ts == null) ? null : GetColumns(ts.ToEnumerable(), GetDataTypeInfo, GetColumnInfo)); }
private TableSourceColumnInfo(ITableSourceInfo tableSource, string name, IDataTypeInfo dataType, bool isMandatory, bool isPrimaryKey) { TableSource = tableSource; Name = name; DataType = dataType; IsMandatory = isMandatory; IsPrimaryKey = isPrimaryKey; }
/// <summary> /// This method generates the SQL to create a new primary key. /// This implementation returns "ALTER TABLE FullyQualifiedTableName ADD primaryKeyStatement" /// </summary> /// <param name="existingTable">Info about the table to create a new primary key. This info is obtained through the IIntrospectionServiceAPI</param> /// <param name="column">Info about the column that composes the primary key.</param> /// <returns>SQL statements to create the primary key.</returns> protected virtual IEnumerable <string> CreatePrimaryKey(ITableSourceInfo existingTable, IPlatformTableSourceColumnInfo column) { string constraintName = GetNewPrimaryKeyName(existingTable.Name); string alterTableStatement = String.Format("ALTER TABLE {0} ADD {1}", existingTable.QualifiedName, GetPrimaryKeyTableConstraint(constraintName, column)); return(alterTableStatement.ToEnumerable()); }
public TableSourceForeignKeyInfo(ITableSourceInfo tableSource, string name, string columnName, ITableSourceInfo referencedTableSource, string referencedColumnName, bool isCascadeDelete) { TableSource = tableSource; Name = name; ColumnName = columnName; ReferencedTableSource = referencedTableSource; ReferencedColumnName = referencedColumnName; IsCascadeDelete = isCascadeDelete; }
public DB2LUWTableSourceColumnInfo(ITableSourceInfo tableSource, string name, IDataTypeInfo dataType, bool isMandatory, bool isPrimaryKey, bool isAutoGenerated) { TableSource = tableSource; Name = name; DataType = dataType; IsMandatory = isMandatory; IsPrimaryKey = isPrimaryKey; IsAutoGenerated = isAutoGenerated; }
public ExpectedForeignKeyInfo(ITableSourceInfo tableSource, string name, string columnName, ITableSourceInfo referencedTableSource, string referencedColumnName) { TableSource = tableSource; Name = name; ColumnName = columnName; ReferencedTableSource = referencedTableSource; ReferencedColumnName = referencedColumnName; }
public PGFKInfo(IDatabaseServices dbServices, ITableSourceInfo source, IDataReader reader) { TableSource = source; Name = (string)reader["constraint_name"]; ColumnName = (string)reader["source_column_name"]; IsCascadeDelete = "DELETE".Equals(((string)reader["delete_rule"]).ToUpper()); ReferencedColumnName = (string)reader["dest_column_name"]; ReferencedTableSource = new PGTableSource(dbServices, source.Database, (string)reader["dest_table_name"]); }
public override IEnumerable <string> CreateIndex(IPlatformTableSourceIndexInfo newIndex) { var createStatement = new StringBuilder(); ITableSourceInfo table = newIndex.TableSource; createStatement.AppendFormat("CREATE {0}INDEX {1} ON dbo.{2} ({3})", newIndex.IsUnique ? "UNIQUE " : "", Identifiers.EscapeIdentifier(newIndex.Name), Identifiers.EscapeIdentifier(table.Name), newIndex.Columns.Select(col => Identifiers.EscapeIdentifier(col.Name)).StrCat(",")); yield return(GetSpecialSchemaStatement(table.Database, createStatement.ToString())); }
/// <summary> /// This method generates the query that will be used in the event trigger. /// This assumes that the underlying database has the NULLIF and COALESCE functions. /// </summary> /// <param name="sql">StringBuilder that will receive the query SQL.</param> /// <param name="triggerTablePrimaryKeyColumn">Primary key column of the table associated with the trigger.</param> /// <param name="triggerTableEventColumns">Columns of the table associated with the trigger that fire events.</param> /// <param name="triggerTableForeignKeys">Foreign keys of the table associated with the trigger.</param> /// <param name="eventTable">Table source that stores the events for the table associated with the trigger. This table resides in the same database as the table where the trigger is defined.</param> /// <param name="eventQueueTable">Table source that stores the events to be fired by the platform.</param> /// <param name="lightEventQueueTable">Table source that stores the light events to be fired by the platform.</param> /// <param name="triggerDataAccessor">SQL snippet to access the newly triggered data (new or updated line in trigger table).</param> /// <param name="needsTriggerDataAccessorInFrom">True if we need to include the <paramref name="triggerDataAccessor"/> in a from clause to access it in a query.</param> /// <param name="isUpdateVariableAccessor">SQL snippet to access the variable that is true if this trigger is an update.</param> protected void FillEventTriggerQuery(StringBuilder sql, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable, string triggerDataAccessor, bool needsTriggerDataAccessorInFrom, string isUpdateVariableAccessor) { FillEventTriggerQueryInternal(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, triggerDataAccessor, needsTriggerDataAccessorInFrom, isUpdateVariableAccessor, /*isLight*/ false); FillEventTriggerQueryInternal(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, lightEventQueueTable, triggerDataAccessor, needsTriggerDataAccessorInFrom, isUpdateVariableAccessor, /*isLight*/ true); }
/// <summary> /// This method generates the SQL to create a new table. /// This implementation returns the statement "CREATE TABLE FullyQualifiedTableName (columnsDefinitionStatements, primaryKeyStatement)" /// </summary> /// <param name="newTable">Info about the table to create.</param> /// <param name="columns">The columns information for the table to create along with the default values. /// Note that some of them may be primary keys, as indicated on the IsPrimaryKey property. /// This will lead to the creation of Primary Key Constraints. /// Also note that a column could be an autonumber column, there's no need to call the AlterColumnChangeAutoNumber after.</param> /// <returns>SQL statements to create the table.</returns> public virtual IEnumerable <string> CreateTable(ITableSourceInfo newTable, params ColumnDetails[] columns) { IEnumerable <string> columnDefinitions = columns.Select(col => GetColumnDefinition(col.Column, col.DefaultValue)); IPlatformTableSourceColumnInfo primaryKey = columns.Select(col => col.Column).SingleOrDefault(col => col.IsPrimaryKey); string primaryKeyStatement = primaryKey == null ? string.Empty: GetPrimaryKeyTableConstraint(GetNewPrimaryKeyName(newTable.Name), primaryKey); yield return(String.Format("CREATE TABLE {0} ({1}{2})", newTable.QualifiedName, columnDefinitions.StrCat(", "), primaryKeyStatement.IsNullOrEmpty() ? "" : ", " + primaryKeyStatement)); }
/// <summary> /// This method returns the previously generated primary key constraint for a table. /// </summary> /// <param name="tableSource">Info about the table to which we want to return the previously generated primary key constraint name.</param> /// <returns>The primary key constraint name previously generated. Or a new valid name if none was previously generated</returns> protected string GetGeneratedPrimaryKeyConstraintNameForTable(ITableSourceInfo tableSource) { if (generatedPrimaryKeyConstraintNames.ContainsKey(tableSource.Name)) { return(generatedPrimaryKeyConstraintNames[tableSource.Name]); } else { return(GetNewPrimaryKeyName(tableSource.Name)); } }
public override bool CanCreateTable(ITableSourceInfo newTable, ColumnDetails[] columns, out string errorMessage) { errorMessage = ""; IPlatformTableSourceColumnInfo badColumn = columns.Select(c => c.Column).Where(c => c.IsAutoGenerated).FirstOrDefault(c => c.IsPrimaryKey == false); if (badColumn != null) { errorMessage = string.Format("Column {0} of table {1} can't be Auto Number if it's not the identifier of the entity.", badColumn.Name, badColumn.TableSource.Name); return(false); } return(true); }
private IEnumerable <string> SetColumnToAutonumber(IPlatformTableSourceColumnInfo existingColumn) { IList <string> statements = new List <string>(); ITableSourceInfo tableSourceInfo = existingColumn.TableSource; string sequenceName = ObjectFactory.GetNewUniqueIdentifer(DatabaseServices, AUTO_NUMBER_SEQUENCE_PREFIX + tableSourceInfo.Name); string triggerName = ObjectFactory.GetNewUniqueIdentifer(DatabaseServices, AUTO_NUMBER_TRIGGER_PREFIX + tableSourceInfo.Name); statements.Add(GetCreateAutoNumberSequenceStatement(tableSourceInfo.Database, sequenceName)); statements.Add(GetCreateAutoNumberTriggerStatement(existingColumn, triggerName, sequenceName)); return(statements); }
public IPlatformTableSourceIndexInfo CreateIndexInfo(ITableSourceInfo tableSource, string indexName, IEnumerable <IPlatformTableSourceColumnInfo> columns, IEnumerable <ITableSourceForeignKeyInfo> foreignKeys, bool isUnique, bool isPrimaryKey) { indexName = string.IsNullOrEmpty(indexName) ? this.GetNewIndexName(databaseServices, tableSource.Name, columns) : indexName; var index = new MySQLPlatformTableSourceIndexInfo(tableSource, indexName, isUnique, isPrimaryKey); foreach (var column in columns) { index.AddColumn(column); } return(index); }
internal static ITableSourceInfo GetTableSourceInfo(IDatabaseServices databaseServices, string tableName, bool assertExists) { IDatabaseInfo db = databaseServices.ObjectFactory.CreateDatabaseInfo(GetDatabaseIdentifier(databaseServices)); var tables = databaseServices.IntrospectionService.ListTableSourcesWithoutFilter(db); ITableSourceInfo result = tables.FirstOrDefault(ts => ts.Name.EqualsIgnoreCase(tableName)); if (assertExists) { Assert.IsNotNull(result, "Table source named '" + tableName + "' not found in the database"); } return(result); }
private string GetDropDefaultContraintStatement(IPlatformTableSourceColumnInfo existingColumn) { const string sqlFormat = "DECLARE @ObjectName NVARCHAR(100);" + " SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM sys.columns" + " WHERE [object_id] = OBJECT_ID('{0}') AND [name] = '{1}';" + " IF @ObjectName IS NOT NULL" + " EXEC('ALTER TABLE {0} DROP CONSTRAINT ' + @ObjectName)"; ITableSourceInfo tableSourceInfo = existingColumn.TableSource; return(GetSpecialSchemaStatement(tableSourceInfo.Database, String.Format(sqlFormat, Identifiers.EscapeIdentifier(tableSourceInfo.Name), existingColumn.Name))); }
public void TestCreateTableSourceInfoFromQualifiedName(DatabaseProviderTestCase tc) { var databaseServices = tc.Services; IDatabaseInfo db = databaseServices.ObjectFactory.CreateDatabaseInfo(GetDatabaseIdentifier(databaseServices)); IEnumerable <ITableSourceInfo> tableSources = databaseServices.IntrospectionService.ListTableSourcesWithoutFilter(db); foreach (var tableSource in tableSources) { if (tableSource.QualifiedName.ToLowerInvariant().EndsWith(MachineName.ToLowerInvariant())) { ITableSourceInfo inferredTableSource = databaseServices.ObjectFactory.CreateTableSourceInfo(tableSource.QualifiedName); bool equals = tableSource.Equals(inferredTableSource); Assert.IsTrue(equals, "The inferred ITableSourceInfo is not equal to the original ITableSourceInfo for table source with qualified name: " + tableSource.QualifiedName); } } }
public override IEnumerable <string> CreateOrReplaceView(ITableSourceInfo newView, string viewSQL, bool withCheckOption) { var result = new StringBuilder(); var dbInfo = (DatabaseInfo)newView.Database; result.Append( String.Format("IF EXISTS (SELECT * FROM {0}.sys.views WHERE object_id = OBJECT_ID(N'{1}')) ", Identifiers.EscapeIdentifier(dbInfo.Catalog), newView.QualifiedName)); string viewName = Identifiers.EscapeIdentifier(newView.Name); string withCheckOptionSQL = withCheckOption ? " WITH CHECK OPTION" : ""; result.Append(GetSpecialSchemaStatement(dbInfo, String.Format("ALTER VIEW [dbo].{0} AS {1}{2}", viewName, viewSQL, withCheckOptionSQL))); result.Append(" ELSE "); result.Append(GetSpecialSchemaStatement(dbInfo, String.Format("CREATE VIEW [dbo].{0} AS {1}{2}", viewName, viewSQL, withCheckOptionSQL))); yield return(result.ToString()); }