public virtual IEnumerable <IntrospectedIndex> GetAllUniqueIndexes(NrdoConnection connection)
{
var uniqueIndexColumns = connection.ExecuteSql("select k.constraint_schema as k_schema, k.constraint_name as k_name, k.table_schema as t_schema, k.table_name as t_name, " +
"k.constraint_type as k_type, u.column_name as c_name, u.ordinal_position as c_index " +
"from information_schema.table_constraints k, information_schema.key_column_usage u " +
"where (k.constraint_type = 'PRIMARY KEY' or k.constraint_type = 'UNIQUE') " +
"and k.constraint_schema = u.constraint_schema and k.constraint_name = u.constraint_name " +
"and k.table_schema = u.table_schema and k.table_name = u.table_name ",
result => new
{
keySchema = result.GetString("k_schema"),
keyName = result.GetString("k_name"),
tableSchema = result.GetString("t_schema"),
tableName = result.GetString("t_name"),
keyType = result.GetString("k_type"),
columnName = result.GetString("c_name"),
columnIndex = result.GetInt("c_index"),
});
return(from col in uniqueIndexColumns
group col by new { col.keySchema, col.keyName, col.tableSchema, col.tableName, col.keyType } into cols
select new IntrospectedIndex(
new IntrospectedTable(cols.Key.tableSchema, cols.Key.tableName),
StringEquals(cols.Key.keyType, "PRIMARY KEY"), true, cols.Key.keyName,
from col in cols orderby col.columnIndex select col.columnName));
}
public virtual IEnumerable <string> GetAllFulltextCatalogs(NrdoConnection connection)
{
if (IsFulltextCatalogUsed)
{
throw new NotImplementedException("Getting existing fulltext catalogs not implemented on " + DisplayName);
}
yield break;
}
public virtual IEnumerable <IntrospectedFulltextIndex> GetAllFulltextIndexes(NrdoConnection connection)
{
if (IsFulltextIndexSupported)
{
throw new NotImplementedException("Getting existing fulltext indexes not implemented on " + DisplayName);
}
yield break;
}
public virtual void TryAcquireSchemaUpdateLock(NrdoConnection connection)
{
// FIXME: the semantics of this method are easy to achieve on SQL server but may not be as easy to do on other database systems.
// Finishing up support for other systems may require changing the API to something that's less convenient for the calling code.
// The semantics of the method currently are:
// - Only one connection can hold the lock at once
// - Once a connection is holding the lock, any other connection trying to take it will fail with a SchemaLockFailException
// - When the connection that's holding the lock is closed, the lock is automatically released
throw new NotImplementedException("Schema lock not implemented on " + DisplayName);
}
public override void TryAcquireSchemaUpdateLock(NrdoConnection connection)
{
try
{
connection.ExecuteSql(GetCreateTableSql("##nrdo_update_in_progress", new[] { new FieldCreation("nothing", "int", true, false, null) }));
}
catch (DbException)
{
throw new SchemaLockFailException();
}
}
public NrdoConnection GetConnection()
{
if (disposed)
{
throw new InvalidCastException("Cannot get a connection from a disposed scope");
}
if (conn == null)
{
NrdoStats.UpdateGlobalStats(stats => stats.WithConnectionStart());
conn = NrdoConnection.Create(dataBase);
}
return(conn);
}
public virtual IEnumerable <IntrospectedProc> GetAllStoredProcsAndFunctions(NrdoConnection connection)
{
var routines = connection.ExecuteSql("select r.specific_schema as s_schema, r.specific_name as s_name, " + // Doesn't seem to be any difference between specific_xyz and routine_xyz but just in case...
"r.routine_schema as r_schema, r.routine_name as r_name, " +
"r.routine_type as r_type, " +
"r.data_type as r_datatype, r.character_maximum_length as r_length, " +
"r.numeric_precision as r_precision, r.numeric_scale as r_scale, " +
"r.routine_definition as r_definition " +
"from information_schema.routines r " +
"where (r.routine_type = 'PROCEDURE' or r.routine_type = 'FUNCTION') " +
"and r.routine_body = 'SQL'",
result => new
{
specSchema = result.GetString("s_schema"),
specName = result.GetString("s_name"),
schema = result.GetString("r_schema"),
name = result.GetString("r_name"),
type = result.GetString("r_type"),
dataType = result.GetString("r_datatype") == null ? null :
GetDataTypeName(result.GetString("r_datatype"), result.GetInt("r_length"), result.GetByte("r_precision"), result.GetInt("r_scale")),
definition = result.GetString("r_definition"),
});
var parameters = connection.ExecuteSql("select p.specific_schema as s_schema, p.specific_name as s_name, p.ordinal_position as p_index, " +
"p.parameter_name as p_name, p.data_type as p_type, p.character_maximum_length as p_length, " +
"p.numeric_precision as p_precision, p.numeric_scale as p_scale " +
"from information_schema.parameters p " +
"where p.is_result = 'NO'",
result => new
{
specSchema = result.GetString("s_schema"),
specName = result.GetString("s_name"),
name = UnquoteParam(result.GetString("p_name")),
index = result.GetInt("p_index"),
dataType = GetDataTypeName(result.GetString("p_type"), result.GetInt("p_length"), result.GetByte("p_precision"), result.GetInt("p_scale")),
});
return(from r in routines
join p in parameters on new { r.specSchema, r.specName } equals new { p.specSchema, p.specName } into rparams
let procParams = from param in rparams orderby param.index select new ProcParam(param.name, param.dataType)
select StringEquals(r.type, "FUNCTION") ?
new IntrospectedFunction(r.schema, r.name, procParams, r.dataType, ExtractFunctionBody(r.definition)) :
new IntrospectedProc(r.schema, r.name, procParams, ExtractProcBody(r.definition)));
}
public override IEnumerable <IntrospectedTrigger> GetAllTriggers(NrdoConnection connection)
{
var triggers = connection.ExecuteSql(@"
select tr.object_id as o_id,
object_schema_name(tr.parent_id) as t_schema, object_name(tr.parent_id) as t_name,
object_schema_name(tr.object_id) as tr_schema, tr.name as tr_name,
object_definition(tr.object_id) as tr_definition, is_instead_of_trigger as tr_instead
from sys.triggers tr",
result => new
{
objectId = result.GetInt("o_id"),
tableSchema = result.GetString("t_schema"),
tableName = result.GetString("t_name"),
triggerSchema = result.GetString("tr_schema"),
triggerName = result.GetString("tr_name"),
triggerDef = result.GetString("tr_definition"),
timing = (bool)result.GetBool("tr_instead") ? TriggerTiming.InsteadOf : TriggerTiming.After,
});
var events = connection.ExecuteSql("select e.object_id as o_id, e.type_desc as tr_event from sys.trigger_events e",
result => new
{
objectId = result.GetInt("o_id"),
evt = (TriggerEvents)Enum.Parse(typeof(TriggerEvents), result.GetString("tr_event"), true),
});
var triggerEvents = triggers.ToDictionary(tr => tr.objectId, tr => default(TriggerEvents));
foreach (var triggerEvent in events)
{
triggerEvents[triggerEvent.objectId] |= triggerEvent.evt;
}
return(from trigger in triggers
select new IntrospectedTrigger(trigger.triggerSchema, trigger.triggerName,
new IntrospectedTable(trigger.tableSchema, trigger.tableName),
trigger.timing, triggerEvents[trigger.objectId], ExtractTriggerBody(trigger.triggerDef)));
}
public virtual IEnumerable <IntrospectedField> GetAllFields(NrdoConnection connection)
{
string isIdentitySelectSql;
string isIdentityFromSql;
string isIdentityWhereSql;
Func <NrdoResult, bool> getIdentity;
Func <NrdoResult, string> getSequenceName;
GetInformationSchemaFieldIsSequencedSql("c", out isIdentitySelectSql, out isIdentityFromSql, out isIdentityWhereSql, out getIdentity, out getSequenceName);
return(connection.ExecuteSql("select c.table_schema as t_schema, c.table_name as t_name, c.column_name as c_name, c.is_nullable as c_nullable, " +
"c.ordinal_position as c_pos, c.data_type as c_type, c.character_maximum_length as c_length, " +
"c.numeric_precision as c_precision, c.numeric_scale as c_scale" + isIdentitySelectSql +
" from information_schema.tables t, information_schema.columns c" + isIdentityFromSql +
" where t.table_schema = c.table_schema and t.table_name = c.table_name and t.table_type = 'BASE TABLE'" + isIdentityWhereSql,
result => new IntrospectedField(new IntrospectedTable(result.GetString("t_schema"), result.GetString("t_name")),
result.GetString("c_name"),
(int)result.GetInt("c_pos"),
GetDataTypeName(result.GetString("c_type"), result.GetInt("c_length"), result.GetByte("c_precision"), result.GetInt("c_scale")),
GetInformationSchemaFieldNullability(result.GetString("c_nullable")),
getIdentity(result),
getSequenceName(result))));
}
public override IEnumerable <IntrospectedIndex> GetAllNonUniqueIndexes(NrdoConnection connection)
{
var indexColumns = connection.ExecuteSql("select object_schema_name(i.object_id) as t_schema, object_name(i.object_id) as t_name, i.name as i_name, " +
"c.name as c_name, ic.key_ordinal as c_index " +
"from sys.indexes i, information_schema.tables t, sys.index_columns ic, sys.columns c " +
"where i.is_unique = 0 and t.table_schema = object_schema_name(i.object_id) and t.table_name = object_name(i.object_id) " +
"and ic.is_included_column = 0 " +
"and ic.object_id = i.object_id and ic.index_id = i.index_id and ic.column_id = c.column_id and ic.object_id = c.object_id",
result => new
{
tableSchema = result.GetString("t_schema"),
tableName = result.GetString("t_name"),
indexName = result.GetString("i_name"),
columnName = result.GetString("c_name"),
columnIndex = result.GetByte("c_index"),
});
return(from col in indexColumns
group col by new { col.tableSchema, col.tableName, col.indexName } into cols
select new IntrospectedIndex(
new IntrospectedTable(cols.Key.tableSchema, cols.Key.tableName),
false, false, cols.Key.indexName,
from col in cols orderby col.columnIndex select col.columnName));
}
public virtual IEnumerable <IntrospectedForeignKey> GetAllForeignKeys(NrdoConnection connection)
{
var fkeyJoins = connection.ExecuteSql("select from_k.table_schema as from_t_schema, from_k.table_name as from_t_name, " +
"to_k.table_schema as to_t_schema, to_k.table_name as to_t_name, " +
"f.constraint_name as f_name, f.delete_rule as f_rule, " +
"from_u.column_name as from_c_name, to_u.column_name as to_c_name, " +
"from_u.ordinal_position as c_index " +
"from information_schema.referential_constraints f, " +
"information_schema.table_constraints from_k, information_schema.table_constraints to_k, " +
"information_schema.key_column_usage from_u, information_schema.key_column_usage to_u " +
"where f.constraint_schema = from_k.constraint_schema and f.constraint_name = from_k.constraint_name " +
"and f.unique_constraint_schema = to_k.constraint_schema and f.unique_constraint_name = to_k.constraint_name " +
"and f.constraint_schema = from_u.constraint_schema and f.constraint_name = from_u.constraint_name " +
"and f.unique_constraint_schema = to_u.constraint_schema and f.unique_constraint_name = to_u.constraint_name " +
"and from_u.ordinal_position = to_u.ordinal_position",
result => new
{
fromSchema = result.GetString("from_t_schema"),
fromTableName = result.GetString("from_t_name"),
toSchema = result.GetString("to_t_schema"),
toTableName = result.GetString("to_t_name"),
fkeyName = result.GetString("f_name"),
deleteRule = result.GetString("f_rule"),
fromColumn = result.GetString("from_c_name"),
toColumn = result.GetString("to_c_name"),
columnIndex = result.GetInt("c_index"),
});
return(from j in fkeyJoins
group j by new { j.fromSchema, j.fromTableName, j.toSchema, j.toTableName, j.fkeyName, j.deleteRule } into joins
select new IntrospectedForeignKey(
new IntrospectedTable(joins.Key.fromSchema, joins.Key.fromTableName),
new IntrospectedTable(joins.Key.toSchema, joins.Key.toTableName),
joins.Key.fkeyName, StringEquals(joins.Key.deleteRule, "CASCADE"),
from fieldPair in joins orderby fieldPair.columnIndex select new FieldPair(fieldPair.fromColumn, fieldPair.toColumn)));
}
public override IEnumerable <string> GetAllFulltextCatalogs(NrdoConnection connection)
{
return(connection.ExecuteSql("select name from sys.fulltext_catalogs", result => result.GetString("name")));
}
public override IEnumerable <IntrospectedIndexCustomState> GetAllIndexCustomState(NrdoConnection connection)
{
var indexClustered = connection.ExecuteSql("select i.object_id as o_id, i.index_id as i_id, object_schema_name(i.object_id) as t_schema, object_name(i.object_id) as t_name, i.name as i_name, " +
"type_desc as i_type from sys.indexes i " +
"where i.type_desc = 'NONCLUSTERED' or i.type_desc = 'CLUSTERED'",
result => new
{
objectId = result.GetInt("o_id"),
indexId = result.GetInt("i_id"),
tableSchema = result.GetString("t_schema"),
tableName = result.GetString("t_name"),
indexName = result.GetString("i_name"),
isClustered = getIsClustered(result.GetString("i_type")),
});
var indexColumns = connection.ExecuteSql("select ic.object_id as o_id, ic.index_id as i_id, c.name as c_name " +
"from sys.index_columns ic, sys.columns c " +
"where ic.is_included_column = 1 and ic.column_id = c.column_id and ic.object_id = c.object_id",
result => new
{
objectId = result.GetInt("o_id"),
indexId = result.GetInt("i_id"),
columnName = result.GetString("c_name"),
});
return(from index in indexClustered
join col in indexColumns on new { index.objectId, index.indexId } equals new { col.objectId, col.indexId } into includedCols
select new IntrospectedIndexCustomState(
new IntrospectedTable(index.tableSchema, index.tableName),
index.indexName,
new SqlServerIndexCustomState(index.isClustered, from c in includedCols select c.columnName)));
}
// INFORMATION_SCHEMA does not include a way to get at non-unique indexes public abstract IEnumerable <IntrospectedIndex> GetAllNonUniqueIndexes(NrdoConnection connection);
public int GetSqlServerMajorVersion(NrdoConnection connection)
{
// This combination of ServerProperty and ParseName extracts the major number from the sql server version
return((int)connection.ExecuteSql("SELECT Cast(ParseName(Cast(ServerProperty('ProductVersion') As nvarchar), 4) As int) AS [sql_version]",
result => result.GetInt("sql_version")).Single());
}
public override string GetEnableFulltextSql(NrdoConnection connection)
{
return(GetSqlServerMajorVersion(connection) <= 9 ? "EXEC sp_fulltext_database 'enable'" : null);
}
// INFORMATION_SCHEMA does not provide a standard way to get triggers. public abstract IEnumerable <IntrospectedTrigger> GetAllTriggers(NrdoConnection connection);
// It's slightly hacky to have core nrdo support for SQL server specific features, but figuring out a general extensibility mechanism
// would be more work than there's time for for now, so these methods pick up clustering and included-field information about indexes
// on "any database that happens to support it" - which is to say, SQL server.
public virtual IEnumerable <IntrospectedIndexCustomState> GetAllIndexCustomState(NrdoConnection connection)
{
yield break;
}
public virtual IEnumerable <IntrospectedTable> GetAllTables(NrdoConnection connection)
{
return(connection.ExecuteSql("select t.table_schema as t_schema, t.table_name as t_name from information_schema.tables t where t.table_type = 'BASE TABLE'",
result => new IntrospectedTable(result.GetString("t_schema"), result.GetString("t_name"))));
}
public virtual string GetEnableFulltextSql(NrdoConnection connection)
{
return(null);
}
public override IEnumerable <IntrospectedIndex> GetAllNonUniqueIndexes(NrdoConnection connection)
{
throw new NotImplementedException("Determining existing non-unique indexes is not supported on " + DisplayName);
}
public virtual IEnumerable <IntrospectedView> GetAllViews(NrdoConnection connection)
{
return(connection.ExecuteSql("select t.table_schema as t_schema, t.table_name as t_name, t.view_definition as t_def from information_schema.views t",
result => new IntrospectedView(result.GetString("t_schema"), result.GetString("t_name"), ExtractViewBody(result.GetString("t_def")))));
}
public override IEnumerable <IntrospectedFulltextIndex> GetAllFulltextIndexes(NrdoConnection connection)
{
var fulltextIndexes = connection.ExecuteSql(@"
select f.object_id as o_id, object_schema_name(f.object_id) as t_schema, object_name(f.object_id) as t_name, i.name as i_name, c.name as c_name
from sys.fulltext_indexes f, sys.indexes i, sys.fulltext_catalogs c
where f.object_id = i.object_id and f.unique_index_id = i.index_id
and f.fulltext_catalog_id = c.fulltext_catalog_id" ,
result => new
{
objectId = result.GetInt("o_id"),
tableSchema = result.GetString("t_schema"),
tableName = result.GetString("t_name"),
keyName = result.GetString("i_name"),
catalogName = result.GetString("c_name"),
});
var columns = connection.ExecuteSql(@"
select f.object_id as o_id, c.name as c_name
from sys.fulltext_index_columns f, sys.columns c
where f.object_id = c.object_id and f.column_id = c.column_id" ,
result => new
{
objectId = result.GetInt("o_id"),
columnName = result.GetString("c_name"),
});
return(from i in fulltextIndexes
select new IntrospectedFulltextIndex(new IntrospectedTable(i.tableSchema, i.tableName), i.catalogName, i.keyName,
from c in columns where c.objectId == i.objectId select c.columnName));
}
public override bool IsFulltextSupported(NrdoConnection connection)
{
return(connection.ExecuteSql("SELECT fulltextserviceproperty('isfulltextinstalled') AS [fulltext_enabled]",
result => result.GetInt("fulltext_enabled") == 1).Single());
}
public virtual IEnumerable <IntrospectedSequence> GetAllSequences(NrdoConnection connection)
{
return(connection.ExecuteSql("select s.sequence_schema as s_schema, s.sequence_name as s_name from information_schema.sequences s",
result => new IntrospectedSequence(result.GetString("s_schema"), result.GetString("s_name"))));
}
public override IEnumerable <IntrospectedTrigger> GetAllTriggers(NrdoConnection connection)
{
throw new NotImplementedException("Determining existing triggers is not supported on " + DisplayName);
}
public virtual bool IsFulltextSupported(NrdoConnection connection)
{
return(IsFulltextIndexSupported);
}
