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