public void InquireSchema()
{
Dialect.MsSql2005Dialect dialect = new MsSql2005Dialect();
DbConnection conn = new SqlConnection(@"");
conn.Open();
DatabaseMetadata meta = new DatabaseMetadata(conn, dialect);
IDataBaseSchema schema = dialect.GetDataBaseSchema(conn);
var dt = schema.GetTables(null, null, null, new string[0]);
var cols = schema.GetColumns(null, null, null, null);
var keys = schema.GetForeignKeys(null, null, null);
foreach (DataRow r in dt.Rows)
{
var tableMeta = schema.GetTableMetadata(r, true);
Console.WriteLine(string.Format("Table {2}:[{0}].[{1}]",tableMeta.Schema,tableMeta.Name,tableMeta.Catalog));
ITableMetadata tm = meta.GetTableMetadata(tableMeta.Name, tableMeta.Schema, tableMeta.Catalog, false);
IColumnMetadata col = tm.GetColumnMetadata(cols.Rows[0].ItemArray[2] as string);
}
}
// Perform the validations.
public async Task ValidateAsync(CancellationToken cancellationToken = default(CancellationToken))
{
cancellationToken.ThrowIfCancellationRequested();
log.Info("Running schema validator");
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
await(connectionHelper.PrepareAsync(cancellationToken)).ConfigureAwait(false);
var connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect, false);
}
catch (OperationCanceledException) { throw; }
catch (Exception sqle)
{
log.Error(sqle, "could not get database metadata");
throw;
}
configuration.ValidateSchema(dialect, meta);
}
catch (OperationCanceledException) { throw; }
catch (Exception e)
{
log.Error(e, "could not complete schema validation");
throw;
}
finally
{
try
{
connectionHelper.Release();
}
catch (OperationCanceledException) { throw; }
catch (Exception e)
{
log.Error(e, "Error closing connection");
}
}
}
/**
* Perform the validations.
*/
public void Validate()
{
log.Info("Running schema validator");
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
DbConnection connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect, false);
}
catch (Exception sqle)
{
log.Error("could not get database metadata", sqle);
throw;
}
configuration.ValidateSchema(dialect, meta);
}
catch (Exception e)
{
log.Error("could not complete schema validation", e);
throw;
}
finally
{
try
{
connectionHelper.Release();
}
catch (Exception e)
{
log.Error("Error closing connection", e);
}
}
}
/// <summary>
/// Execute schema update script, determined by the Configuration object
/// used for creating the SessionFactory. A replacement for NHibernate's
/// SchemaUpdate class, for automatically executing schema update scripts
/// on application startup. Can also be invoked manually.
/// </summary>
/// <remarks>
/// Fetch the LocalSessionFactoryObject itself rather than the exposed
/// SessionFactory to be able to invoke this method, e.g. via
/// <code>LocalSessionFactoryObject lsfo = (LocalSessionFactoryObject) ctx.GetObject("mySessionFactory");</code>.
/// <p>
/// Uses the SessionFactory that this bean generates for accessing a ADO.NET
/// connection to perform the script.
/// </p>
/// </remarks>
public virtual void UpdateDatabaseSchema()
{
log.Info("Updating database schema for Hibernate SessionFactory");
HibernateTemplate hibernateTemplate = new HibernateTemplate(sessionFactory);
hibernateTemplate.TemplateFlushMode = TemplateFlushMode.Never;
hibernateTemplate.Execute(
new HibernateDelegate(delegate(ISession session)
{
IDbConnection con = session.Connection;
Dialect dialect = Dialect.GetDialect(Configuration.Properties);
DatabaseMetadata metadata = new DatabaseMetadata((DbConnection) con, dialect);
string[] sql = Configuration.GenerateSchemaUpdateScript(dialect, metadata);
ExecuteSchemaScript(con, sql);
return null;
}));
}
/// <summary>
/// Execute the schema updates
/// </summary>
public void Execute(bool script, bool doUpdate)
{
log.Info("Running hbm2ddl schema update");
DbConnection connection;
IDbCommand stmt = null;
exceptions.Clear();
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
connection = (DbConnection) connectionHelper.GetConnection();
meta = new DatabaseMetadata(connection, dialect);
stmt = connection.CreateCommand();
}
catch (Exception sqle)
{
exceptions.Add(sqle);
log.Error("could not get database metadata", sqle);
throw;
}
log.Info("updating schema");
String[] createSQL = configuration.GenerateSchemaUpdateScript(dialect, meta);
for (int j = 0; j < createSQL.Length; j++)
{
String sql = createSQL[j];
try
{
if (script)
{
Console.WriteLine(sql);
}
if (doUpdate)
{
log.Debug(sql);
stmt.CommandText = sql;
stmt.ExecuteNonQuery();
}
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Unsuccessful: " + sql, e);
}
}
log.Info("schema update complete");
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("could not complete schema update", e);
}
finally
{
try
{
if (stmt != null)
{
stmt.Dispose();
}
connectionHelper.Release();
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Error closing connection", e);
}
}
}
/// <summary>
/// Execute the schema updates
/// </summary>
/// <param name="scriptAction">The action to write the each schema line.</param>
/// <param name="doUpdate">Commit the script to DB</param>
public void Execute(Action<string> scriptAction, bool doUpdate)
{
log.Info("Running hbm2ddl schema update");
string autoKeyWordsImport = PropertiesHelper.GetString(Environment.Hbm2ddlKeyWords, configuration.Properties, "not-defined");
autoKeyWordsImport = autoKeyWordsImport.ToLowerInvariant();
if (autoKeyWordsImport == Hbm2DDLKeyWords.AutoQuote)
{
SchemaMetadataUpdater.QuoteTableAndColumns(configuration);
}
DbConnection connection;
IDbCommand stmt = null;
exceptions.Clear();
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect);
stmt = connection.CreateCommand();
}
catch (Exception sqle)
{
exceptions.Add(sqle);
log.Error("could not get database metadata", sqle);
throw;
}
log.Info("updating schema");
string[] createSQL = configuration.GenerateSchemaUpdateScript(dialect, meta);
for (int j = 0; j < createSQL.Length; j++)
{
string sql = createSQL[j];
string formatted = formatter.Format(sql);
try
{
if (scriptAction != null)
{
scriptAction(formatted);
}
if (doUpdate)
{
log.Debug(sql);
stmt.CommandText = sql;
stmt.ExecuteNonQuery();
}
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Unsuccessful: " + sql, e);
}
}
log.Info("schema update complete");
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("could not complete schema update", e);
}
finally
{
try
{
if (stmt != null)
{
stmt.Dispose();
}
connectionHelper.Release();
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Error closing connection", e);
}
}
}
/// <summary>
/// Execute the schema updates
/// </summary>
/// <param name="scriptAction">The action to write the each schema line.</param>
/// <param name="doUpdate">Commit the script to DB</param>
/// <param name="cancellationToken">A cancellation token that can be used to cancel the work</param>
public async Task ExecuteAsync(Action <string> scriptAction, bool doUpdate, CancellationToken cancellationToken = default(CancellationToken))
{
cancellationToken.ThrowIfCancellationRequested();
log.Info("Running hbm2ddl schema update");
await(InitializeAsync(cancellationToken)).ConfigureAwait(false);
DbConnection connection;
DbCommand stmt = null;
exceptions.Clear();
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
await(connectionHelper.PrepareAsync(cancellationToken)).ConfigureAwait(false);
connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect);
stmt = connection.CreateCommand();
}
catch (Exception sqle)
{
exceptions.Add(sqle);
log.Error("could not get database metadata", sqle);
throw;
}
log.Info("updating schema");
string[] createSQL = configuration.GenerateSchemaUpdateScript(dialect, meta);
for (int j = 0; j < createSQL.Length; j++)
{
string sql = createSQL[j];
string formatted = formatter.Format(sql);
try
{
if (scriptAction != null)
{
scriptAction(formatted);
}
if (doUpdate)
{
log.Debug(sql);
stmt.CommandText = sql;
await(stmt.ExecuteNonQueryAsync(cancellationToken)).ConfigureAwait(false);
}
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Unsuccessful: " + sql, e);
}
}
log.Info("schema update complete");
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("could not complete schema update", e);
}
finally
{
try
{
if (stmt != null)
{
stmt.Dispose();
}
connectionHelper.Release();
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Error closing connection", e);
}
}
}
public void ValidateSchema(Dialect dialect, DatabaseMetadata databaseMetadata);
public string[] GenerateSchemaUpdateScript(Dialect dialect, DatabaseMetadata databaseMetadata);
/// <summary>
/// Execute the schema updates
/// </summary>
/// <param name="scriptAction">The action to write the each schema line.</param>
/// <param name="doUpdate">Commit the script to DB</param>
public void Execute(Action <string> scriptAction, bool doUpdate)
{
log.Info("Running hbm2ddl schema update");
Initialize();
DbConnection connection;
DbCommand stmt = null;
exceptions.Clear();
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect);
stmt = connection.CreateCommand();
}
catch (Exception sqle)
{
exceptions.Add(sqle);
log.Error(sqle, "could not get database metadata");
throw;
}
log.Info("updating schema");
string[] createSQL = configuration.GenerateSchemaUpdateScript(dialect, meta);
for (int j = 0; j < createSQL.Length; j++)
{
string sql = createSQL[j];
string formatted = formatter.Format(sql);
try
{
if (scriptAction != null)
{
scriptAction(formatted);
}
if (doUpdate)
{
log.Debug(sql);
stmt.CommandText = sql;
stmt.ExecuteNonQuery();
}
}
catch (Exception e)
{
exceptions.Add(e);
log.Error(e, "Unsuccessful: {0}", sql);
}
}
log.Info("schema update complete");
}
catch (Exception e)
{
exceptions.Add(e);
log.Error(e, "could not complete schema update");
}
finally
{
try
{
if (stmt != null)
{
stmt.Dispose();
}
connectionHelper.Release();
}
catch (Exception e)
{
exceptions.Add(e);
log.Error(e, "Error closing connection");
}
}
}
public void Contribute(string alias, ISessionFactory factory, Configuration cfg)
{
Configuration = cfg;
SessionFactory = factory;
if (CreateDatabaseSchema)
{
using (var session = factory.OpenSession())
{
var dialect = Dialect.GetDialect(cfg.Properties);
var metadata = new DatabaseMetadata((DbConnection) session.Connection, dialect);
string[] lines = cfg.GenerateSchemaUpdateScript(dialect, metadata);
foreach (var line in lines)
{
var cmd = session.Connection.CreateCommand();
cmd.CommandText = line;
cmd.ExecuteNonQuery();
}
}
DatabaseSchemaCreated = true;
}
}
/// <summary>
/// Execute the schema updates
/// </summary>
/// <param name="scriptAction">The action to write the each schema line.</param>
/// <param name="doUpdate">Commit the script to DB</param>
public void Execute(Action <string> scriptAction, bool doUpdate)
{
log.Info("Running hbm2ddl schema update");
string autoKeyWordsImport = PropertiesHelper.GetString(Environment.Hbm2ddlKeyWords, configuration.Properties, "not-defined");
autoKeyWordsImport = autoKeyWordsImport.ToLowerInvariant();
if (autoKeyWordsImport == Hbm2DDLKeyWords.AutoQuote)
{
SchemaMetadataUpdater.QuoteTableAndColumns(configuration);
}
DbConnection connection;
IDbCommand stmt = null;
exceptions.Clear();
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect);
stmt = connection.CreateCommand();
}
catch (Exception sqle)
{
exceptions.Add(sqle);
log.Error("could not get database metadata", sqle);
throw;
}
log.Info("updating schema");
string[] createSQL = configuration.GenerateSchemaUpdateScript(dialect, meta);
for (int j = 0; j < createSQL.Length; j++)
{
string sql = createSQL[j];
string formatted = formatter.Format(sql);
try
{
if (scriptAction != null)
{
scriptAction(formatted);
}
if (doUpdate)
{
log.Debug(sql);
stmt.CommandText = sql;
stmt.ExecuteNonQuery();
}
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Unsuccessful: " + sql, e);
}
}
log.Info("schema update complete");
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("could not complete schema update", e);
}
finally
{
try
{
if (stmt != null)
{
stmt.Dispose();
}
connectionHelper.Release();
}
catch (Exception e)
{
exceptions.Add(e);
log.Error("Error closing connection", e);
}
}
}
///<summary>
/// Generate DDL for altering tables
///</summary>
/// <seealso cref="NHibernate.Tool.hbm2ddl.SchemaUpdate"/>
public String[] GenerateSchemaUpdateScript(Dialect.Dialect dialect, DatabaseMetadata databaseMetadata)
{
SecondPassCompile();
string defaultCatalog = PropertiesHelper.GetString(Environment.DefaultCatalog, properties, null);
string defaultSchema = PropertiesHelper.GetString(Environment.DefaultSchema, properties, null);
ArrayList script = new ArrayList(50);
foreach (Table table in tables.Values)
{
if (table.IsPhysicalTable)
{
TableMetadata tableInfo = databaseMetadata.GetTableMetadata(
table.Name,
table.Schema ?? defaultSchema
);
if (tableInfo == null)
{
script.Add(table.SqlCreateString(dialect, mapping, defaultCatalog, defaultSchema));
}
else
{
string[] alterDDL = table.SqlAlterStrings(
dialect,
mapping,
tableInfo,
defaultSchema
);
script.AddRange(alterDDL);
}
string[] comments = table.SqlCommentStrings(dialect, defaultCatalog, defaultSchema);
script.AddRange(comments);
}
}
foreach (Table table in tables.Values)
{
if (table.IsPhysicalTable)
{
TableMetadata tableInfo = databaseMetadata.GetTableMetadata(
table.Name,
table.Schema
);
if (dialect.HasAlterTable)
{
foreach (ForeignKey fk in table.ForeignKeyIterator)
{
if (fk.HasPhysicalConstraint)
{
bool create = tableInfo == null || (
tableInfo.GetForeignKeyMetadata(fk.Name) == null && (
//Icky workaround for MySQL bug:
!(dialect is MySQLDialect) ||
tableInfo.GetIndexMetadata(fk.Name) == null
)
);
if (create)
{
script.Add(fk.SqlCreateString(dialect, mapping, defaultCatalog, defaultSchema));
}
}
}
}
}
/*//broken, 'cos we don't generate these with names in SchemaExport
subIter = table.getIndexIterator();
while ( subIter.hasNext() ) {
Index index = (Index) subIter.next();
if ( !index.isForeignKey() || !dialect.hasImplicitIndexForForeignKey() ) {
if ( tableInfo==null || tableInfo.GetIndexMetadata( index.getFilterName() ) == null ) {
script.add( index.sqlCreateString(dialect, mapping) );
}
}
}
//broken, 'cos we don't generate these with names in SchemaExport
subIter = table.getUniqueKeyIterator();
while ( subIter.hasNext() ) {
UniqueKey uk = (UniqueKey) subIter.next();
if ( tableInfo==null || tableInfo.GetIndexMetadata( uk.getFilterName() ) == null ) {
script.add( uk.sqlCreateString(dialect, mapping) );
}
}*/
}
foreach (IPersistentIdentifierGenerator generator in IterateGenerators(dialect))
{
Object key = generator.GeneratorKey();
if (!databaseMetadata.IsSequence(key) && !databaseMetadata.IsTable(key))
{
String[] lines = generator.SqlCreateStrings(dialect);
for (int i = 0; i < lines.Length; i++)
{
script.Add(lines[i]);
}
}
}
return ArrayHelper.ToStringArray(script);
}
/**
* Perform the validations.
*/
public void Validate()
{
log.Info("Running schema validator");
try
{
DatabaseMetadata meta;
try
{
log.Info("fetching database metadata");
connectionHelper.Prepare();
DbConnection connection = connectionHelper.Connection;
meta = new DatabaseMetadata(connection, dialect, false);
}
catch (Exception sqle)
{
log.Error("could not get database metadata", sqle);
throw;
}
configuration.ValidateSchema(dialect, meta);
}
catch (Exception e)
{
log.Error("could not complete schema validation", e);
throw;
}
finally
{
try
{
connectionHelper.Release();
}
catch (Exception e)
{
log.Error("Error closing connection", e);
}
}
}
public static string[] ExportUpdateSchema(Dialect dialect, DatabaseMetadata databaseMetadata)
{
Configuration cfg = new Configuration();
cfg.Configure();
return cfg.GenerateSchemaUpdateScript(dialect, databaseMetadata);
}
