/// <summary>
/// Merge the user configuration with the default metadatas from the servers.
/// </summary>
/// <param name="metadatas">Metadatas to generate queries for.</param>
/// <param name="behavior">A behavior for cloning data</param>
/// <param name="variables">The compiled cascade variables</param>
private static void MergeForeignKey(this Metadatas metadatas, Behavior behavior, HashSet <SchemaVar> variables)
{
if (behavior == null)
{
return;
}
foreach (var dbSettings in behavior.DbSettings)
{
var dbSettingsVar = variables.First(v => v.Id == dbSettings.ForSchemaId);
if (!metadatas.ContainsKey(dbSettingsVar.Server))
{
continue;
}
var server = metadatas[dbSettingsVar.Server];
if (!server.ContainsKey(dbSettingsVar.Database))
{
continue;
}
var database = server[dbSettingsVar.Database];
if (!database.ContainsKey(dbSettingsVar.Schema))
{
continue;
}
var schema = database[dbSettingsVar.Schema];
MergeFkModifierSchema(schema, dbSettings, variables);
}
}
public static Metadatas Deserialize(BinaryReader stream, FastAccessList <object> referenceTracking = null)
{
var cTables = new Metadatas();
var nbServers = stream.ReadInt32();
for (var n = 0; n < nbServers; n++)
{
var serverId = stream.ReadString();
cTables.Add(serverId, new ServerMetadata());
var nbDatabases = stream.ReadInt32();
for (var j = 0; j < nbDatabases; j++)
{
var database = stream.ReadString();
cTables[serverId].Add(database, new DatabaseMetadata());
var nbSchemas = stream.ReadInt32();
for (var k = 0; k < nbSchemas; k++)
{
var schemaMetadata = new SchemaMetadata();
var schema = stream.ReadString();
var nbTablesFrom = stream.ReadInt32();
for (var l = 0; l < nbTablesFrom; l++)
{
TableMetadata table;
if (referenceTracking == null)
{
table = TableMetadata.Deserialize(stream);
}
else
{
var id = stream.ReadInt32();
table = (TableMetadata)referenceTracking[id];
}
schemaMetadata.Add(table);
}
cTables[serverId][database].Add(schema, schemaMetadata);
}
}
}
return(cTables);
}
/// <summary>
/// Generate derivative tables for the metadatas.
/// </summary>
private static void GenerateDerivativeTables(this Metadatas metadatas)
{
foreach (var server in metadatas)
{
foreach (var database in server.Value)
{
foreach (var schema in database.Value)
{
foreach (var table in schema.Value)
{
//On trouve les dérivées de la table
foreach (var databaseDeriv in server.Value)
{
foreach (var schemaDeriv in databaseDeriv.Value)
{
foreach (var tableDeriv in schemaDeriv.Value)
{
foreach (var fk in tableDeriv.ForeignKeys)
{
//Si correspondance
if (fk.ServerIdTo == server.Key && fk.DatabaseTo == database.Key &&
fk.SchemaTo == schema.Key && fk.TableTo == table.Name)
{
//Si non présente
if (!table.DerivativeTables.Any(t => t.ServerId == fk.ServerIdTo && t.Schema == fk.SchemaTo &&
t.Database == fk.DatabaseTo && t.Table == fk.TableTo))
{
table.DerivativeTables.Add(new DerivativeTable
{
ServerId = server.Key,
Database = databaseDeriv.Key,
Schema = schemaDeriv.Key,
Table = tableDeriv.Name
});
}
}
}
}
}
}
}
}
}
}
}
/// <summary>
/// Get the metadatas from the databases.
/// </summary>
/// <param name="queryProxy">The proxy used to fetch data</param>
/// <returns>The default metadatas from the databases.</returns>
private static Metadatas FetchMetadata(List <Connection> connections)
{
var metadatas = new Metadatas();
foreach (var connection in connections)
{
var dbConnection = DbProviderFactories.GetFactory(connection.ProviderName).CreateConnection();
dbConnection.ConnectionString = connection.ConnectionString;
var provider = MetadataProviderFactory.GetProvider(connection.ProviderName);
foreach (var database in provider.GetDatabasesName(dbConnection))
{
provider.LoadColumns(dbConnection, metadatas, connection.Id, database);
provider.LoadForeignKeys(dbConnection, metadatas, connection.Id, database);
provider.LoadUniqueKeys(dbConnection, metadatas, connection.Id, database);
}
}
return(metadatas);
}
/// <summary>
/// Generate INSERT and SELECT queries for the tables.
/// </summary>
/// <param name="metadatas">Metadatas to generate queries for.</param>
private static void GenerateCommands(this Metadatas metadatas)
{
foreach (var server in metadatas)
{
foreach (var database in server.Value)
{
foreach (var schema in database.Value)
{
foreach (var table in schema.Value)
{
var sbInsert = new StringBuilder("INSERT INTO ");
var sbSelect = new StringBuilder("SELECT ");
sbInsert.Append(database.Key);
if (!string.IsNullOrEmpty(schema.Key))
{
sbInsert.Append(".\"").Append(schema.Key).Append('"');
}
sbInsert.Append(".\"")
.Append(table.Name)
.Append("\" (");
//Nom des colonnes
var nbCols = table.ColumnsDefinition.Count;
for (var j = 0; j < nbCols; j++)
{
//Select
sbSelect.Append('"').Append(table.ColumnsDefinition[j].Name).Append('"');
if (j < nbCols - 1)
{
sbSelect.Append(",");
}
//Insert
if (!table.ColumnsDefinition[j].IsAutoIncrement)
{
sbInsert.Append('"').Append(table.ColumnsDefinition[j].Name).Append('"');
if (j < nbCols - 1)
{
sbInsert.Append(",");
}
}
}
sbInsert.Append(") VALUES(");
//Valeur des colonnes Insert
for (var j = 0; j < nbCols; j++)
{
if (!table.ColumnsDefinition[j].IsAutoIncrement)
{
sbInsert.Append("@").Append(table.ColumnsDefinition[j].Name);
if (j < nbCols - 1)
{
sbInsert.Append(",");
}
}
}
sbInsert.Append(");");
//Finalisation du select
sbSelect.Append(" FROM \"")
.Append(database.Key)
.Append('"');
if (!string.IsNullOrEmpty(schema.Key))
{
sbSelect.Append(".\"").Append(schema.Key).Append('"');
}
sbSelect.Append(".\"")
.Append(table.Name)
.Append("\"");
table.InsertCommand = sbInsert.ToString();
table.SelectCommand = sbSelect.ToString();
}
}
}
}
}
/// <summary>
/// Merge a user behavior with the default metadatas.
/// </summary>
/// <param name="metadatas">Metadatas to generate queries for.</param>
/// <param name="behavior">A behavior for cloning data</param>
/// <param name="variables">The compiled cascade variables</param>
private static void MergeBehaviour(this Metadatas metadatas, Behavior behavior, HashSet <SchemaVar> variables)
{
if (behavior == null)
{
return;
}
foreach (var dbSettings in behavior.DbSettings)
{
var dbSettingsVar = variables.First(v => v.Id == dbSettings.ForSchemaId);
if (!metadatas.ContainsKey(dbSettingsVar.Server))
{
continue;
}
var server = metadatas[dbSettingsVar.Server];
if (!server.ContainsKey(dbSettingsVar.Database))
{
continue;
}
var database = server[dbSettingsVar.Database];
if (!database.ContainsKey(dbSettingsVar.Schema))
{
continue;
}
var schema = database[dbSettingsVar.Schema];
foreach (var dbSettingsTable in dbSettings.Tables)
{
var table = schema.FirstOrDefault(t => t.Name.Equals(dbSettingsTable.Name, StringComparison.CurrentCultureIgnoreCase));
if (table == null)
{
continue;
}
//On affecte les changements de la configuration
table.IsStatic = dbSettingsTable.IsStatic == NullableBool.True;
//Derivative tables
var globalAccess = dbSettingsTable.DerativeTableGlobal.GlobalAccess;
var globalCascade = dbSettingsTable.DerativeTableGlobal.GlobalCascade;
//Default settings
foreach (var derivTbl in table.DerivativeTables)
{
derivTbl.Access = globalAccess;
derivTbl.Cascade = globalCascade == NullableBool.True;
}
//Override if modified
foreach (var dbSettingsDerivativeTable in dbSettingsTable.DerativeTableGlobal.DerivativeTables)
{
var destinationVar = variables.First(v => v.Id == dbSettingsDerivativeTable.DestinationSchema);
var derivativeTable = table.DerivativeTables.FirstOrDefault(t => t.ServerId == destinationVar.Server &&
t.Database.Equals(destinationVar.Database,
StringComparison.OrdinalIgnoreCase) &&
t.Schema.Equals(destinationVar.Schema,
StringComparison.OrdinalIgnoreCase) &&
t.Table.Equals(dbSettingsDerivativeTable.Name,
StringComparison.OrdinalIgnoreCase));
if (derivativeTable == null)
{
continue;
}
derivativeTable.Access = dbSettingsDerivativeTable.Access;
derivativeTable.Cascade = dbSettingsDerivativeTable.Cascade == NullableBool.True;
}
//Data builder
foreach (var builderCol in dbSettingsTable.DataBuilders)
{
var col = table.ColumnsDefinition.FirstOrDefault(c => c.Name.Equals(builderCol.Name, StringComparison.OrdinalIgnoreCase));
if (col != null)
{
col.BuilderName = builderCol.BuilderName;
}
}
}
}
}
/// <summary>
/// Load the result of an Sql query into the metadatas object.
/// </summary>
/// <param name="reader">Result of an Sql query defined in <see cref="MetadataProvider.SqlGetColumns"/></param>
/// <param name="metadata">Metadatas container</param>
/// <param name="serverId">ServerId loaded from</param>
/// <param name="database">Database loaded from</param>
/// <param name="typeConverter">Classe performing type conversion between database and .NET.</param>
internal static void LoadColumns(IDataReader reader, Metadatas metadata, string serverId, string database, ISqlTypeConverter typeConverter)
{
var schemaMetadata = new SchemaMetadata();
var lstSchemaColumn = new List <ColumnDefinition>();
string currentSchema;
if (!reader.Read())
{
return;
}
//Init first row
var previousSchema = reader.GetString(0);
var previousTable = new TableMetadata(reader.GetString(1));
//Pour chaque ligne
do
{
currentSchema = reader.GetString(0);
var currentTable = reader.GetString(1);
//Si on change de table
if (currentSchema != previousSchema || currentTable != previousTable.Name)
{
previousTable.ColumnsDefinition = lstSchemaColumn;
schemaMetadata.Add(previousTable);
lstSchemaColumn = new List <ColumnDefinition>();
previousTable = new TableMetadata(currentTable);
}
//Si on change de schema
if (currentSchema != previousSchema)
{
metadata[serverId, database, currentSchema] = schemaMetadata;
schemaMetadata = new SchemaMetadata();
}
//Ajoute la colonne
var col = new ColumnDefinition
{
Name = reader.GetString(2),
SqlType = new SqlType
{
DataType = reader.GetString(3),
Precision = reader.GetInt32(4),
Scale = reader.GetInt32(5),
IsUnsigned = reader.GetBoolean(6)
},
IsPrimary = reader.GetBoolean(7),
IsAutoIncrement = reader.GetBoolean(8)
};
col.DbType = typeConverter.ConvertFromSql(col.SqlType);
lstSchemaColumn.Add(col);
} while (reader.Read());
//Ajoute la dernière table / schema
if (lstSchemaColumn.Count > 0)
{
previousTable.ColumnsDefinition = lstSchemaColumn;
schemaMetadata.Add(previousTable);
metadata[serverId, database, currentSchema] = schemaMetadata;
}
}
/// <summary>
/// Load the result of an Sql query into the metadatas object.
/// </summary>
/// <param name="reader">Result of an Sql query defined in <see cref="MetadataProvider.SqlGetForeignKeys"/></param>
/// <param name="metadata">Metadatas container</param>
/// <param name="serverId">ServerId loaded from</param>
/// <param name="database">Database loaded from</param>
internal static void LoadForeignKeys(IDataReader reader, Metadatas metadata, string serverId, string database)
{
var lstForeignKeys = new List <ForeignKey>();
var lstForeignKeyColumns = new List <ForeignKeyColumn>();
if (!reader.Read())
{
return;
}
//Init first row
var currentSchema = reader.GetString(0);
var previousTable = metadata[serverId][database][currentSchema].First(t => t.Name.Equals(reader.GetString(1), StringComparison.OrdinalIgnoreCase));
var previousConstraintName = reader.GetString(2);
var previousConstraint = new ForeignKey
{
ServerIdTo = serverId,
DatabaseTo = database,
SchemaTo = currentSchema,
TableTo = reader.GetString(5)
};
//Pour chaque ligne
do
{
currentSchema = reader.GetString(0);
var currentTable = reader.GetString(1);
var currentConstraint = reader.GetString(2);
//Si on change de constraint
if (currentTable != previousTable.Name || currentConstraint != previousConstraintName)
{
previousConstraint.Columns = lstForeignKeyColumns;
lstForeignKeys.Add(previousConstraint);
lstForeignKeyColumns = new List <ForeignKeyColumn>();
previousConstraint = new ForeignKey
{
ServerIdTo = serverId,
DatabaseTo = database,
SchemaTo = currentSchema,
TableTo = reader.GetString(5)
};
previousConstraintName = currentConstraint;
}
//Si on change de table
if (currentTable != previousTable.Name)
{
previousTable.ForeignKeys = lstForeignKeys;
//Change de table
previousTable = metadata[serverId][database][currentSchema].First(t => t.Name.Equals(reader.GetString(1), StringComparison.OrdinalIgnoreCase));
lstForeignKeys = new List <ForeignKey>();
}
//Ajoute la colonne
var colName = reader.GetString(3);
lstForeignKeyColumns.Add(new ForeignKeyColumn
{
NameFrom = colName,
NameTo = reader.GetString(6)
});
//Affecte l'indicateur dans le schema
var col = previousTable.ColumnsDefinition.FirstOrDefault(c => c.Name.Equals(colName, StringComparison.OrdinalIgnoreCase));
if (col == null)
{
throw new Exception($"The column {colName} has not been found in the metadata for the table {previousTable.Name}.");
}
col.IsForeignKey = true;
} while (reader.Read());
//Ajoute la dernière table / schema
if (lstForeignKeyColumns.Count > 0)
{
previousConstraint.Columns = lstForeignKeyColumns;
lstForeignKeys.Add(previousConstraint);
previousTable.ForeignKeys = lstForeignKeys;
}
}