public IDbDataParameter CreateDbParameter(IDbCommand command, IDbAdapter dbAdapter)
{
var result = command.CreateParameter();
result.ParameterName = dbAdapter.FormatParameterName(Name);
result.Value = dbAdapter.ConvertToDbType(Value);
return result;
}
public DbConnectException(Exception InnerException, NameValueList Config)
: base(string.Empty, InnerException)
{
this.ConnectionString = Config.Get<string>("ConnectionString");
this.DbProviderFactory = Config.Get<DbProviderFactory>("DbProviderFactory");
this.DbAdapter = Config.Get<IDbAdapter>("DbAdapter");
}
public static void AppendSelect(Command cmd, Select select, IDbAdapter adapter)
{
if (select == null)
{
cmd.Append(" *");
return;
}
switch (select.Type)
{
case SelectType.All:
default:
cmd.Append(" *");
break;
case SelectType.Custom:
if (select.Fields.Count == 0)
{
return;
}
cmd.Append(" ");
foreach (string s in select.Fields)
{
cmd.AppendFormat("{0},", adapter.EnsureIdentifier(s));
}
cmd.RemoveLastChar();
break;
}
}
public DbConnectException(Exception InnerException, NameValueList Config)
: base(string.Empty, InnerException)
{
this.ConnectionString = Config.Get <string>("ConnectionString");
this.DbProviderFactory = Config.Get <DbProviderFactory>("DbProviderFactory");
this.DbAdapter = Config.Get <IDbAdapter>("DbAdapter");
}
/// <summary>
/// Indexes a set of columns defined by the dictionary that is passed.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table the index will be created on.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the index is created.</returns>
private static bool CreateIndexes(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error creating index: Table {0} does not exist", schema.TableName));
return(false);
}
foreach (var column in columns)
{
try
{
var indexName = column.Key + "_idx";
adapter.CreateIndexOnTable(schema.TableName, column.Key, indexName);
if (column.Value == true)
{
adapter.ClusterIndex(schema.TableName, indexName);
}
}
catch (Exception ex)
{
Log.Error(String.Format("Error creating index on column '{0}' for table '{1}': {2}", column.Key, schema.TableName, ex.Message));
return(false);
}
}
return(true);
}
/// <summary>
/// Creates a new Repository using an EntityMap to define the database mapping.
/// </summary>
/// <param name="dbAdapter">DbAdapter class for the database being used</param>
/// <param name="entityMap">Entity map class used to define the mapping for this repository</param>
public DbRepository(IDbAdapter dbAdapter, EntityMap <T> entityMap)
{
DbAdapter = dbAdapter;
ConnectionString = dbAdapter.ConnectionString;
TableName = entityMap.TableName;
Mappings = new MappingModel <T>(entityMap);
}
public SessionFactory(string connectionString, IDbAdapter dbAdapter, IDomainMap domainMap, Func<ISessionCache> sessionCacheProvider)
{
this.connectionString = connectionString;
this.dbAdapter = dbAdapter;
this.domainMap = domainMap;
this.sessionCacheProvider = sessionCacheProvider;
}
/// <summary>
/// Creates a new Repository with the table name and maps passed directly into the contructor.
/// </summary>
/// <param name="dbAdapter">DbAdapter class for the database being used</param>
/// <param name="tableName">Name of the table</param>
/// <param name="maps">List of maps for this tabe</param>
public DbRepository(IDbAdapter dbAdapter, string tableName, IEnumerable <IMap> maps)
{
DbAdapter = dbAdapter;
ConnectionString = dbAdapter.ConnectionString;
TableName = tableName;
Mappings = new MappingModel <T>(maps);
}
public Spid3r(string seed, int maxThreads, IDbAdapter adapter)
{
SeedUrl = seed;
Adapter = adapter;
Home = ExtractHome(seed);
MaxThreads = maxThreads;
}
public ResignAccountHanlderController(IResignAccountHanlderViewer viewer, IExecutioner executor, IDbAdapter adapter)
{
Viewer = viewer;
Executioner = executor;
Adapter = adapter;
Logic = new BussiessLogic(Adapter, ResignAccountHandlerUI.Properties.Settings.Default.DeleteAfter);
}
public SubtotalTest()
{
m_Adapter = Facade.Create(db: "accounting-test");
m_Adapter.DeleteVouchers(VoucherQueryUnconstrained.Instance);
m_Adapter.Upsert(
new Voucher
{
Date = new(2016, 12, 31, 0, 0, 0, DateTimeKind.Utc),
Remark = "xrmk1",
Details = new()
{
new()
{
User = "******",
Currency = "JPY",
Title = 1234,
SubTitle = 56,
Content = "cnt1",
Fund = 123.45,
Remark = "rmk1",
},
new()
{
User = "******",
Currency = "JPY",
Title = 6541,
SubTitle = 98,
Content = "cnt1",
Fund = -123.45,
Remark = "rmk2",
},
},
});
public static async ValueTask <IDbAdapter> UnVirtualize(IDbAdapter db)
{
var vir = db as Virtualizer ?? throw new InvalidOperationException("Not virtualized");
await vir.DisposeAsync();
return(vir.Db);
}
/// <summary>
/// 使用若干参数初始化数据连接参数。
/// </summary>
/// <param name="Parameters">参数，包括DbProviderFactory(String类型表示的完整限定名称或DbProviderFactory类型)、DbAdapter(String类型表示的类型完整限定名称或DbAdapter类型)，及连接字符串(ConnectionString)或当前IDbAdapter.BuildConnectionString所需参数。</param>
/// <returns>初始化成功时，返回DbProvider，否则，返回null。</returns>
public static DbProvider Initialize(NameValueList Parameters)
{
DbConnection _conn = null;
DbProvider res = new DbProvider();
try
{
IDbAdapter DbAdapter = _getInstance <IDbAdapter>(Parameters["DbAdapter"]);
if (DbAdapter != null)
{
res._config["DbAdapter"] = DbAdapter;
DbAdapter.InitializeOptions(res.Options);
}
DbProviderFactory DbProviderFactory = _getInstance <DbProviderFactory>(Parameters["DbProviderFactory"]);
if (DbProviderFactory != null)
{
res._config["DbProviderFactory"] = DbProviderFactory;
}
else if (res.Options["DefaultDbProviderFactory"] != null)
{
res._config["DbProviderFactory"] = _getInstance <DbProviderFactory>(res.Options["DefaultDbProviderFactory"]);
}
if (Parameters["ConnectionString"] is string)
{
res._config["ConnectionString"] = Parameters["ConnectionString"];
}
else
{
res._config["ConnectionString"] = res.Adapter.BuildConnectionString(Parameters);
}
_conn = res.Provider.CreateConnection();
_conn.ConnectionString = res.ConnectionString;
_conn.Open();
res._config["DbSchema"] = res.Adapter.GetSchema(_conn);
if (Current == null)
{
Current = res;
}
return(res);
}
catch (Exception e)
{
#if DEBUG
DbConnectException dce = new DbConnectException(e, res._config);
Debug.WriteLine(dce);
throw dce;
#endif
return(null);
}
finally
{
try
{
_conn.Close();
}
catch { }
}
}
/// <summary>
/// Creates an instance of Database.
/// </summary>
/// <param name="adapter"></param>
public Database(IDbAdapter adapter)
{
if (adapter == null)
{
throw new ArgumentNullException("adapter");
}
Adapter = adapter;
}
public DbTest()
{
m_Adapter = Facade.Create(db: "accounting-test");
m_Adapter.DeleteVouchers(VoucherQueryUnconstrained.Instance);
ClientUser.Set("b1");
}
public UsuarioService(IDbAdapter dbAdapter, ILoggerFactory loggerFactory)
{
this.dbAdapter = dbAdapter
?? throw new ArgumentNullException(nameof(dbAdapter));
logger = loggerFactory?.CreateLogger <UsuarioService>()
?? throw new ArgumentNullException(nameof(loggerFactory));
}
public IDbDataParameter CreateDbParameter(IDbCommand command, IDbAdapter dbAdapter)
{
var result = command.CreateParameter();
result.ParameterName = dbAdapter.FormatParameterName(Name);
result.Value = dbAdapter.ConvertToDbType(Value);
return(result);
}
public DapperStoredProcedure(string name, IReadOnlyList <IDbDataParameter> parameters, IDbConnection connection, IDbTransaction transaction, ILogger <DapperStoredProcedure> logger, IDbAdapter dbAdapter)
{
this.name = name;
this.parameters = parameters;
this.connection = connection;
this.transaction = transaction;
this.logger = logger;
this.dbAdapter = dbAdapter;
}
public DbManipulator(IDbAdapter dbAdapter,
Assembly migrationAssembly,
params string[] tags)
{
_dbAdapter = dbAdapter;
_migrationAssembly = migrationAssembly;
Tags = new List <string>(tags ?? Enumerable.Empty <string>());
SetServiceProvider();
}
public static void AppendOrders(Command cmd, IEnumerable<Order> orders, IDbAdapter adapter)
{
if (orders != null)
if (orders.Count() > 0)
{
cmd.Append(" order by ");
foreach (Order oo in orders)
cmd.AppendFormat("{0},", oo.ToString(adapter));
cmd.RemoveLastChar();
}
}
public IDbCommand CreateCommand(IDbAdapter dbAdapter, IDbConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = dbAdapter.FormatCommandText(CommandText);
foreach (var parameter in Parameters)
{
var p = parameter.CreateDbParameter(command, dbAdapter);
command.Parameters.Add(p);
}
return command;
}
private static void InsertResource(IDbAdapter db, object name, string description, Guid resourceGuid,
Guid sensor1Guid, Guid sensor2Guid)
{
db.SimpleData.Resource.Insert(name: name, description: description, guid: resourceGuid.ToString());
long resourceId = db.SimpleData.Resource.FindByGuid(resourceGuid.ToString()).Id;
db.SimpleData.Sensor.Insert(guid: sensor1Guid.ToString(), metric: "CPU", unit: "%", complex: 0,
resourceId: resourceId);
db.SimpleData.Sensor.Insert(guid: sensor2Guid.ToString(), metric: "Memory usage", unit: "MB", complex: 0,
resourceId: resourceId);
}
public IDbCommand CreateCommand(IDbAdapter dbAdapter, IDbConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = dbAdapter.FormatCommandText(CommandText);
foreach (var parameter in Parameters)
{
var p = parameter.CreateDbParameter(command, dbAdapter);
command.Parameters.Add(p);
}
return(command);
}
public StatsApi(IDbAdapter database, bool cacheOn)
{
_db = database;
_enableCache = cacheOn;
// Cache setup
_playerStatsCache = new WeakCache <string, string>(_db.MakePlayerStats);
_serverStatsCache = new WeakCache <string, string>(_db.MakeServerStats);
_recentMatchesReportCache = new WeakCache <int, string>(_db.MakeRecentMatchesReport);
_bestPlayersReportCache = new WeakCache <int, string>(_db.MakeBestPlayersReport);
_popularServersReportCache = new WeakCache <int, string>(_db.MakePopularServersReport);
}
/// <summary>
/// Initializing <see cref="SqlDataProvider{TEntity}"/>
/// </summary>
/// <param name="dbConnectionFactory">Factory providing database connection</param>
/// <exception cref="ArgumentNullException">Parameter dbConnectionFactory is null</exception>
/// <exception cref="ArgumentNullException">Parameter dbAdapter is null</exception>
public SqlDataProvider(IDbConnectionFactory dbConnectionFactory, IDbAdapter dbAdapter)
{
DbConnectionFactory = dbConnectionFactory ?? throw new ArgumentNullException(nameof(DbConnectionFactory));
DbAdapter = dbAdapter ?? throw new ArgumentNullException(nameof(dbAdapter));
TableName = typeof(TEntity).Name;
if (!TableName.EndsWith("s"))
{
TableName += "s";
}
EntityFields = GetEntityFields();
}
internal QueryBuilder(string tableName, IEnumerable <IMap> maps, IDbAdapter dbAdapter, Type domainType)
{
TableName = tableName;
Parameters = new Dictionary <string, object>();
List <string> columns = new List <string>();
foreach (IMap map in maps)
{
columns.Add(dbAdapter.GetSelectColumnCast(domainType, tableName, map));
}
SqlQuery = "SELECT " + string.Join(",", columns) + " FROM " + TableName + " ";
}
/// <summary>
/// Creates a new Repository using attributes to define the database mapping.
/// All non-virtual properties with public getters and setters will be mapped
/// unless overridden with an attribute.
/// </summary>
/// <param name="dbAdapter">DbAdapter class for the database being used</param>
/// <param name="overrideTableName">Overrides the table name generated from the object name or the table attribute.</param>
/// <param name="ignoreObjectMaps">Sets if properties that end with _Object or _Objects will be ignored by the automatic mapping</param>
public DbRepository(IDbAdapter dbAdapter, string overrideTableName = null, bool ignoreObjectMaps = true)
{
DbAdapter = dbAdapter;
ConnectionString = dbAdapter.ConnectionString;
if (string.IsNullOrWhiteSpace(overrideTableName))
{
TableName = GetTableName();
}
else
{
TableName = overrideTableName;
}
Mappings = new MappingModel <T>(ignoreObjectMaps);
}
public static void AppendOrders(Command cmd, IEnumerable <Order> orders, IDbAdapter adapter)
{
if (orders != null)
{
if (orders.Count() > 0)
{
cmd.Append(" order by ");
foreach (Order oo in orders)
{
cmd.AppendFormat("{0},", oo.ToString(adapter));
}
cmd.RemoveLastChar();
}
}
}
public LogParser(LogReader reader, IFileRepository fileService, IDbAdapter db, params ITransformation[] transformations)
{
if (reader == null)
throw new ArgumentNullException("reader");
if (fileService == null)
throw new ArgumentNullException("files");
if (db == null)
throw new ArgumentNullException("db");
this.reader = reader;
this.fileService = fileService;
this.db = db;
this.transformations = transformations;
}
/// <summary>
/// Updates whether or not indexes are clustered.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the clusters on indexes were properly updated.</returns>
private static bool UpdateIndexClusters(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating index cluster status: Table {0} does not exist", schema.TableName));
return(false);
}
try
{
Log.Debug(String.Format("Checking to see if clusters on indexes should be updated for table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName);
// For every column associated with an index in the DB, gather the Index Name from the DB, whether the DB index is clustered,
// the name of the indexed column in the config, and whether or not the index is clustered IF the column exists in both the config and DB.
var indexesToCheck = from dbIndex in dbIndexes
from indexedColumn in dbIndex.IndexedColumns
join columnName in columns
on indexedColumn equals columnName.Key
select new
{
dbIndexName = dbIndex.IndexName,
dbIsCluster = dbIndex.IsClustered,
indexedColumn = columnName.Key,
isClustered = columnName.Value
};
foreach (var index in indexesToCheck)
{
if (index.dbIsCluster == false && index.isClustered == true)
{
adapter.ClusterIndex(schema.TableName, index.dbIndexName);
}
else if (index.dbIsCluster == true && index.isClustered == false)
{
adapter.DropIndex(index.dbIndexName);
adapter.CreateIndexOnTable(schema.TableName, index.indexedColumn, index.dbIndexName);
}
}
return(true);
}
catch
{
Log.Error(String.Format("Unable to update index cluster status for table '{0}'.", schema.TableName));
return(false);
}
}
public ISessionFactory Build()
{
if (dbAdapter == null)
{
dbAdapter = new NullDbAdapter();
}
var domainMap = new DomainMap(dbAdapter);
if (domainConfig != null)
{
domainConfig(domainMap);
}
domainMap.Done();
if (sessionCacheProvider == null)
{
sessionCacheProvider = () => new SessionCache();
}
return new SessionFactory(connString, dbAdapter, domainMap, sessionCacheProvider);
}
/// <summary>
/// Creates a table on a remote database server.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema of the table to create.</param>
/// <returns>True if table is successfully created.</returns>
public static bool CreateTable(IDbAdapter adapter, DataTable schema)
{
try
{
if (!adapter.ExistsTable(schema.TableName))
{
adapter.CreateTable(schema);
}
else
{
Log.Debug(String.Format("Database table '{0}' already exists; skipping creation.", schema.TableName));
}
return true;
}
catch (DbException ex)
{
Log.Error(String.Format("Failed to create database table '{0}': {1}", schema.TableName, ex.Message));
return false;
}
}
/// <summary>
/// Register a named adapter. Allows a database to be created using Database.CreateDB(name).
/// </summary>
/// <param name="name"></param>
/// <param name="adapter"></param>
/// <remarks>Register adapters in the startup of your application so you don't need to pass connection strings around.</remarks>
public static void RegisterAdapter(string name, IDbAdapter adapter)
{
if (name.IsEmpty())
{
throw new ArgumentNullException("name");
}
if (adapter == null)
{
sAdapters.Remove(name);
}
else if (sAdapters.ContainsKey(name))
{
sAdapters[name] = adapter;
}
else
{
sAdapters.Add(name, adapter);
}
}
public static void AppendWhereByEnsuredKey(Command cmd, Where where, string keyFieldName, IDbAdapter adapter)
{
if (where == null)
return;
switch (where.Type)
{
case WhereType.Key:
cmd.AppendFormat(" where {0}=@0WhereKey", keyFieldName);
cmd.AddParameter("@0WhereKey", where.SingleObject);
break;
case WhereType.Custom:
if (!string.IsNullOrEmpty(where.QueryString))
{
cmd.AppendFormat(" where {0}", where.QueryString);
for (int i = 0; i < where.Parameters.Count; i++)
cmd.AddParameter("@" + i, where.Parameters[i]);
}
break;
}
AppendOrders(cmd, where.Orders, adapter);
}
/// <summary>
/// Uses an in-memory schema to initialize a database table according to a set of initialization options.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to use to initialize.</param>
/// <param name="tableInitializationOptions">A set of options to determine initialization behavior.</param>
public static void InitializeTable(IDbAdapter adapter, DataTable schema, DbTableInitializationOptions tableInitializationOptions)
{
if (tableInitializationOptions.CreateTableDynamically)
{
CreateTable(adapter, schema, tableInitializationOptions.IndexesToGenerate);
}
if (tableInitializationOptions.UpdateDbTableToMatchSchema)
{
UpdateTableToMatchSchema(adapter, schema);
}
if (tableInitializationOptions.UpdateSchemaToMatchDbTable)
{
UpdateSchemaToMatchTable(adapter, schema);
}
if (tableInitializationOptions.UpdateIndexes)
{
AddDbIndexesToMatch(adapter, schema, tableInitializationOptions.IndexesToGenerate);
RemoveDBIndexesToMatch(adapter, schema, tableInitializationOptions.IndexesToGenerate);
UpdateIndexClusters(adapter, schema, tableInitializationOptions.IndexesToGenerate);
}
}
/// <summary>
/// Uses an in-memory schema to initialize a database table according to a set of initialization options.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to use to initialize.</param>
/// <param name="tableInitializationOptions">A set of options to determine initialization behavior.</param>
public static void InitializeTable(IDbAdapter adapter, DataTable schema, DbTableInitializationOptions tableInitializationOptions)
{
if (tableInitializationOptions.CreateTableDynamically)
{
CreateTable(adapter, schema, tableInitializationOptions.IndexesToGenerate);
}
if (tableInitializationOptions.UpdateDbTableToMatchSchema)
{
UpdateTableToMatchSchema(adapter, schema);
}
if (tableInitializationOptions.UpdateSchemaToMatchDbTable)
{
UpdateSchemaToMatchTable(adapter, schema);
}
if (tableInitializationOptions.UpdateIndexes)
{
AddDbIndexesToMatch(adapter, schema, tableInitializationOptions.IndexesToGenerate);
RemoveDBIndexesToMatch(adapter, schema, tableInitializationOptions.IndexesToGenerate);
UpdateIndexClusters(adapter, schema, tableInitializationOptions.IndexesToGenerate);
}
}
/// <summary>
/// Creates a table on a remote database server and indexes the table.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema of the table to create.</param>
/// <param name="indexes">A dictionary that contains the indexes to create and whether the indexes are clustered.</param>
/// <returns>True if table is successfully created.</returns>
public static bool CreateTable(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> indexes)
{
try
{
if (!adapter.ExistsTable(schema.TableName))
{
adapter.CreateTable(schema);
CreateIndexes(adapter, schema, indexes);
}
else
{
Log.Debug(String.Format("Database table '{0}' already exists; skipping creation.", schema.TableName));
}
return(true);
}
catch (DbException ex)
{
Log.Error(String.Format("Failed to create database table '{0}': {1}", schema.TableName, ex.Message));
return(false);
}
}
/// <summary>
/// Updates an existing database table to 'match' a schema by adding any missing columns.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to use to update the database.</param>
/// <returns>True if table is successfully updated to match schema.</returns>
public static bool UpdateTableToMatchSchema(IDbAdapter adapter, DataTable schema)
{
// Check if we need to update the db schema and/or this result schema.
var dbTable = adapter.GetSchema(schema.TableName);
if (!schema.SchemaEquals(dbTable))
{
try
{
// Insert any missing columns into the database.
adapter.AddColumnsToTableToMatchSchema(dbTable.TableName, schema);
return true;
}
catch (DbException ex)
{
Log.Error("Failed to initialize database writer: " + ex.Message);
return false;
}
}
Log.Debug("Database table already matches schema; nothing to update.");
return true;
}
static async Task Index(Queue <ChangeInfo> changeQueue, IDbAdapter dbAdapter)
{
while (true)
{
if (changeQueue.Count > 0)
{
var change = changeQueue.Dequeue();
System.Console.WriteLine(change.EventType + " " + change.Path);
switch (change.EventType)
{
case "Renamed":
await dbAdapter.UpdateIndexes(IndexUtils.CreateUpdateIndex(change.Path.ToLower(), change.NewPath.ToLower()));
break;
case "Created":
var newIdx = new Index
{
Name = Path.GetFileNameWithoutExtension(change.Path),
Extention = Path.GetExtension(change.Path),
Folder = Path.GetDirectoryName(change.Path),
SearchKey = change.Path,
};
dbAdapter.SetIndexes(new List <Index> {
newIdx
}, false);
break;
case "Deleted":
await dbAdapter.UpdateIndexes(IndexUtils.CreateUpdateIndex(change.Path.ToLower(), null));
break;
default:
break;
}
}
await Task.Delay(100);
}
}
/// <summary>
/// Adds indexes to the database if the existing indexes do not match the dictionary passed in.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if we were successfully able to update the indexes.</returns>
private static bool AddDbIndexesToMatch(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating indexes: table {0} does not exist", schema.TableName));
return(false);
}
try
{
Log.Debug(String.Format("Checking to see if indexes should be added to table '{0}'..", schema.TableName));
var dbIndexList = adapter.GetIndexes(schema.TableName.ToString());
var indexesToCreate = new Dictionary <string, bool>();
var existingIndexes = new List <string>();
foreach (var listItem in dbIndexList)
{
existingIndexes.Add(listItem.IndexName);
}
foreach (var configEntry in columns)
{
var indexName = BuildIndexName(schema.TableName, configEntry.Key);
if (!existingIndexes.Contains(indexName))
{
indexesToCreate.Add(configEntry.Key, configEntry.Value);
}
}
CreateIndexes(adapter, schema, indexesToCreate);
return(true);
}
catch
{
Log.Error(String.Format("Unable to update indexes for table '{0}'.", schema.TableName));
return(false);
}
}
/// <summary>
/// Updates an existing database table to 'match' a schema by adding any missing columns.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to use to update the database.</param>
/// <returns>True if table is successfully updated to match schema.</returns>
public static bool UpdateTableToMatchSchema(IDbAdapter adapter, DataTable schema)
{
// Check if we need to update the db schema and/or this result schema.
var dbTable = adapter.GetSchema(schema.TableName);
if (!schema.SchemaEquals(dbTable))
{
try
{
// Insert any missing columns into the database.
adapter.AddColumnsToTableToMatchSchema(dbTable.TableName, schema);
return(true);
}
catch (DbException ex)
{
Log.Error("Failed to initialize database writer: " + ex.Message);
return(false);
}
}
Log.Debug("Database table already matches schema; nothing to update.");
return(true);
}
private static ISessionFactory Bootstrap(IDatabaseMigration createSchema, IDbAdapter dbAdapter, Action<string> createDatabaseFunc)
{
Log.Level = LogLevel.Off;
const string dbFileName = "main.db";
const string connectionString = "Data source=" + dbFileName;
File.Delete(dbFileName);
if (createDatabaseFunc != null)
{
createDatabaseFunc(connectionString);
}
var sessionFactory = Fluently.Configure
.ConnectionString(connectionString)
.DatabaseAdapter(dbAdapter)
.Domain(DomainMapping.Get())
.Build();
UnitOfWork.Initialize(sessionFactory);
using (var s = sessionFactory.Create())
{
s.Open();
new DatabaseMigratorUtility(s).Migrate(createSchema);
}
return sessionFactory;
}
public static void AppendSelect(Command cmd, Select select, IDbAdapter adapter)
{
if (select == null)
{
cmd.Append(" *");
return;
}
switch (select.Type)
{
case SelectType.All:
default:
cmd.Append(" *");
break;
case SelectType.Custom:
if (select.Fields.Count == 0)
return;
cmd.Append(" ");
foreach (string s in select.Fields)
cmd.AppendFormat("{0},", adapter.EnsureIdentifier(s));
cmd.RemoveLastChar();
break;
}
}
/// <summary>
/// Updates a schema to 'match' an existing database table by copying it.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to update.</param>
/// <returns></returns>
public static bool UpdateSchemaToMatchTable(IDbAdapter adapter, DataTable schema)
{
// Copy the DB table's schema back over the top of our in-memory schema to ensure parity.
try
{
// Check to see if overwriting the schema will yield inconsistencies.
var existingTableSchema = adapter.GetSchema(schema.TableName);
foreach (DataColumn column in schema.Columns)
{
if (!existingTableSchema.Columns.Contains(column.ColumnName))
{
Log.Error(String.Format("Cannot update local schema to match database table; column '{0}' exists in the schema, but not the database table.", column.ColumnName));
return false;
}
if (existingTableSchema.Columns[column.ColumnName].DataType != column.DataType)
{
Log.Error(String.Format("Cannot update local schema to match database table; data types are inconsistent. [Schema='{0}', DbTable='{1}']", column.DataType, existingTableSchema.Columns[column.ColumnName].DataType));
return false;
}
if (existingTableSchema.Columns[column.ColumnName].AllowDBNull != column.AllowDBNull)
{
Log.Error(String.Format("Cannot update local schema to match database table; inconsistencies in 'AllowDBNull' parameter. [Schema='{0}', DbTable='{1}']", column.AllowDBNull, existingTableSchema.Columns[column.ColumnName].AllowDBNull));
return false;
}
}
// Copy the remote database's schema over the top of our in-memory schema.
schema = existingTableSchema.Copy();
Log.Debug(String.Format("Updated internal schema to match database table '{0}'.", schema.TableName));
return true;
}
catch (DbException ex)
{
Log.Error(String.Format("Error updating schema '{0}' from database: {1}", schema.TableName, ex.Message));
return false;
}
}
public DbModelerLoader(IDbAdapter adapter)
{
this.adapter = adapter;
}
public string ToString(IDbAdapter adapter)
{
return string.Format("{0} {1}", adapter.EnsureIdentifier(Field), sql_type);
}
public IConfigurator DatabaseAdapter(IDbAdapter adapter)
{
dbAdapter = adapter;
return this;
}
/// <summary>
/// Indexes a set of columns defined by the dictionary that is passed.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table the index will be created on.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the index is created.</returns>
private static bool CreateIndexes(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error creating index: Table {0} does not exist", schema.TableName));
return false;
}
foreach (var column in columns)
{
try
{
var indexName = column.Key + "_idx";
adapter.CreateIndexOnTable(schema.TableName, column.Key, indexName);
if (column.Value == true)
{
adapter.ClusterIndex(schema.TableName, indexName);
}
}
catch (Exception ex)
{
Log.Error(String.Format("Error creating index on column '{0}' for table '{1}': {2}", column.Key, schema.TableName, ex.Message));
return false;
}
}
return true;
}
/// <summary>
/// Uses an in-memory schema to initialize a database table according to a set of initialization options.
/// </summary>
/// <param name="adapter">Open adapter to a database.</param>
/// <param name="schema">The schema to use to initialize.</param>
/// <param name="tableInitializationOptions">A set of options to determine initialization behavior.</param>
public static void InitializeTable(IDbAdapter adapter, DataTable schema, DbTableInitializationOptions tableInitializationOptions)
{
if (tableInitializationOptions.CreateTableDynamically)
{
CreateTable(adapter, schema);
}
if (tableInitializationOptions.UpdateDbTableToMatchSchema)
{
UpdateTableToMatchSchema(adapter, schema);
}
if (tableInitializationOptions.UpdateSchemaToMatchDbTable)
{
UpdateSchemaToMatchTable(adapter, schema);
}
}
public static void Initialize(string connectionString, IDbAdapter dbAdapter)
{
ConnectionString = connectionString;
DbAdapter = dbAdapter;
}
public DbAdapterSwitch(IDbAdapter db)
{
this.db = db;
}
/// <summary>
/// Adds indexes to the database if the existing indexes do not match the dictionary passed in.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if we were successfully able to update the indexes.</returns>
private static bool AddDbIndexesToMatch(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating indexes: Table {0} does not exist", schema.TableName));
return false;
}
try
{
Log.Debug(String.Format("Checking to see if indexes should be added to table '{0}'..", schema.TableName));
var dbIndexList = adapter.GetIndexes(schema.TableName.ToString());
var indexesToCreate = new Dictionary<string, bool>();
var existingIndexes = new List<string>();
foreach (var listItem in dbIndexList)
{
existingIndexes.Add(listItem.IndexName);
}
foreach (var configEntry in columns)
{
var indexName = configEntry.Key + "_idx";
if (!existingIndexes.Contains(indexName))
{
indexesToCreate.Add(configEntry.Key, configEntry.Value);
}
}
CreateIndexes(adapter, schema, indexesToCreate);
return true;
}
catch
{
Log.Error(String.Format("Unable to update indexes for table '{0}'.", schema.TableName));
return false;
}
}
public static void AppendPage(Command cmd, string keyFieldName, string tableName, Page page, Where where, IDbAdapter adapter)
{
if (page == null)
return;
adapter.AppendPage(cmd, keyFieldName, tableName, page, where);
}
public DbCommandFactory(IDbAdapter dbAdapter, IDbConnection connection)
{
this.dbAdapter = dbAdapter;
this.connection = connection;
}
/// <summary>
/// Removes indexes from database if indexes are not in the dictionary.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the index(es) were properly removed.</returns>
private static bool RemoveDBIndexesToMatch(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating indexes: Table {0} does not exist", schema.TableName));
return false;
}
try
{
Log.Debug(String.Format("Checking to see if indexes should be removed from table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName.ToString());
var indexesToDrop = new HashSet<string>();
foreach (var dbIndex in dbIndexes)
{
foreach (var indexedColumn in dbIndex.IndexedColumns)
{
if (!columns.Keys.Contains(indexedColumn))
{
indexesToDrop.Add(dbIndex.IndexName);
}
}
}
foreach (var indexToDrop in indexesToDrop)
{
adapter.DropIndex(indexToDrop);
}
return true;
}
catch (Exception ex)
{
Log.Error(String.Format("Unable to remove indexes from table '{0}': {1}", schema.TableName, ex.Message));
return false;
}
}
/// <summary>
/// Updates whether or not indexes are clustered.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the clusters on indexes were properly updated.</returns>
private static bool UpdateIndexClusters(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating index cluster status: Table {0} does not exist", schema.TableName));
return false;
}
try
{
Log.Debug(String.Format("Checking to see if clusters on indexes should be updated for table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName);
// For every column associated with an index in the DB, gather the Index Name from the DB, whether the DB index is clustered,
// the name of the indexed column in the config, and whether or not the index is clustered IF the column exists in both the config and DB.
var indexesToCheck = from dbIndex in dbIndexes
from indexedColumn in dbIndex.IndexedColumns
join columnName in columns
on indexedColumn equals columnName.Key
select new
{
dbIndexName = dbIndex.IndexName,
dbIsCluster = dbIndex.IsClustered,
indexedColumn = columnName.Key,
isClustered = columnName.Value
};
foreach (var index in indexesToCheck)
{
if (index.dbIsCluster == false && index.isClustered == true)
{
adapter.ClusterIndex(schema.TableName, index.dbIndexName);
}
else if (index.dbIsCluster == true && index.isClustered == false)
{
adapter.DropIndex(index.dbIndexName);
adapter.CreateIndexOnTable(schema.TableName, index.indexedColumn, index.dbIndexName);
}
}
return true;
}
catch
{
Log.Error(String.Format("Unable to update index cluster status for table '{0}'.", schema.TableName));
return false;
}
}
public static Where ToWhere(this IEnumerable<Compare> cos, IDbAdapter adapter)
{
StringBuilder where = new StringBuilder();
List<Object> pms = new List<Object>();
foreach (Compare _co in cos)
{
Compare co = _co.Clone() as Compare;
switch (co.Type)
{
default:
case CompareType.Custom:
where.AppendFormat("{0} and ", co.CustomCondition);
pms.AddRange(co.CompareValues);
break;
case CompareType.Equals:
if (_compareGetSingleValue(co))
{
where.AppendFormat("{0}=@{1} and ", adapter.EnsureIdentifier(co.Field), pms.Count);
pms.Add(co.CompareValues[0]);
}
break;
case CompareType.Unequals:
if (_compareGetSingleValue(co))
{
where.AppendFormat("{0}<>@{1} and ", adapter.EnsureIdentifier(co.Field), pms.Count);
pms.Add(co.CompareValues[0]);
}
break;
case CompareType.GreaterThan:
if (_compareGetSingleValue(co))
{
where.AppendFormat("{0}>@{1} and ", adapter.EnsureIdentifier(co.Field), pms.Count);
pms.Add(co.CompareValues[0]);
}
break;
case CompareType.LessThan:
if (_compareGetSingleValue(co))
{
where.AppendFormat("{0}<@{1} and ", adapter.EnsureIdentifier(co.Field), pms.Count);
pms.Add(co.CompareValues[0]);
}
break;
case CompareType.Like:
if (_compareGetSingleValue(co))
{
where.AppendFormat("{0} like {1} and ", adapter.EnsureIdentifier(co.Field), adapter.Concat("'%'", "@" + pms.Count, "'%'"));
pms.Add(adapter.LikePattern(co.CompareValues[0].ToString()));
}
break;
case CompareType.Between:
if (_compareGetBetweenValue(co))
{
where.AppendFormat("{0} between @{1} and @{2} and ", adapter.EnsureIdentifier(co.Field), pms.Count, pms.Count + 1);
pms.Add(co.CompareValues[0]);
pms.Add(co.CompareValues[1]);
}
break;
case CompareType.In:
if (_compareGetInValue(co))
{
where.AppendFormat("{0} in (", adapter.EnsureIdentifier(co.Field));
foreach (var o in co.CompareValues)
{
where.AppendFormat("@{0},", pms.Count);
pms.Add(o);
}
where.Remove(where.Length - 1, 1);
where.Append(") and ");
}
break;
}
}
if (where.Length == 0)
return null;
where.Remove(where.Length - 5, 5);
return new Where(where.ToString(), pms.ToArray());
}
public static Where ToWhere(this Compare _co, IDbAdapter adapter)
{
Compare co = _co.Clone() as Compare;
switch (co.Type)
{
default:
case CompareType.Custom:
return new Where(co.CustomCondition, co.CompareValues);
case CompareType.Equals:
if (_compareGetSingleValue(co))
return new Where(string.Format("{0}=@0", adapter.EnsureIdentifier(co.Field)), co.CompareValues[0]);
return null;
case CompareType.Unequals:
if (_compareGetSingleValue(co))
return new Where(string.Format("{0}<>@0", adapter.EnsureIdentifier(co.Field)), co.CompareValues[0]);
return null;
case CompareType.GreaterThan:
if (_compareGetSingleValue(co))
return new Where(string.Format("{0}>@0", adapter.EnsureIdentifier(co.Field)), co.CompareValues[0]);
return null;
case CompareType.LessThan:
if (_compareGetSingleValue(co))
return new Where(string.Format("{0}<@0", adapter.EnsureIdentifier(co.Field)), co.CompareValues[0]);
return null;
case CompareType.Like:
if (_compareGetSingleValue(co))
return new Where(string.Format("{0} like {1}",
adapter.EnsureIdentifier(co.Field), adapter.Concat("'%'", "@0", "'%'")), adapter.LikePattern(co.CompareValues[0].ToString()));
return null;
case CompareType.Between:
if (_compareGetBetweenValue(co))
return new Where(string.Format("{0} between @0 and @1", adapter.EnsureIdentifier(co.Field)), co.CompareValues[0], co.CompareValues[1]);
return null;
case CompareType.In:
if (_compareGetInValue(co))
{
List<object> in_pms = new List<object>();
StringBuilder in_query = new StringBuilder();
foreach (var o in co.CompareValues)
{
in_query.AppendFormat("@{0},", in_pms.Count);
in_pms.Add(o);
}
in_query.Remove(in_query.Length - 1, 1);
return new Where(string.Format("{0} in ({1})", adapter.EnsureIdentifier(co.Field), in_query), in_pms.ToArray());
}
return null;
}
}
public DataTableDbWriter(DbDriverType driverType, IDbConnectionInfo connectionInfo, DbTableInitializationOptions tableInitializationOptions = default(DbTableInitializationOptions))
{
Adapter = new DbAdapter(driverType, connectionInfo);
this.tableInitializationOptions = tableInitializationOptions;
}
public static void RegisterQueryAdapter(string provider, IDbAdapter adapter)
{
if (provider == null) throw new ArgumentNullException("provider");
Adapters[provider.ToLowerInvariant()] = adapter;
}