public List <DbSequenceInfo> GetSequences()
{
var list = new List <DbSequenceInfo>();
string sql =
@"SELECT s1.nspname as tableschema, t1.relname AS tablename, a.attname columnname, s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
AND t2.relkind = 'S';
";
using (var r = db.ExecuteReader(sql))
{
while (r.Read())
{
var rec = new DbSequenceInfo();
rec.SchemaName = Conv.ToString(r["tableschema"]);
rec.TableName = Conv.ToString(r["TableName"]);
rec.SequenceName = Conv.ToString(r["SequenceName"]);
rec.ColumnName = Conv.ToString(r["ColumnName"]);
list.Add(rec);
}
}
return(list);
}
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence)
{
var start = (sequence.StartValue < 1) ? 1 : sequence.StartValue;
const string sqlTemplate = "CREATE Sequence {0} START WITH {1} INCREMENT BY {2};";
change.AddScript(DbScriptType.SequenceAdd, sqlTemplate, sequence.FullName, start, sequence.Increment);
}
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence)
{
var start = (sequence.StartValue < 1) ? 1 : sequence.StartValue;
change.AddScript(DbScriptType.SequenceAdd,
$"CREATE Sequence {sequence.FullName} START WITH {start} INCREMENT BY {sequence.Increment};");
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT NEXT VALUE FOR {0};";
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.FullName);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return(cmdInfo);
}
public override DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
const string SqlTemplate = "SELECT nextval('{0}.\"{1}\"');"; //note sequence name in double quotes inside single-quote argument
//Load by primary key
var cmdName = sequence.Name + "_GetNextValue";
var cmdInfo = new DbCommandInfo(DbModel, sequence.Schema, cmdName, null, null);
cmdInfo.Sql = string.Format(SqlTemplate, sequence.Schema, sequence.Name);
cmdInfo.ExecutionType = DbExecutionType.Scalar;
return(cmdInfo);
}
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence)
{
const string sqlCreateTemplate = "CREATE Sequence {0} AS {1} START WITH {2} INCREMENT BY {3};";
const string sqlGrantTemplate = "Grant UPDATE on {0} to {1};";
change.AddScript(DbScriptType.SequenceAdd, sqlCreateTemplate, sequence.FullName, sequence.DbType.SqlTypeSpec,
sequence.StartValue, sequence.Increment);
//Grant permission to UPDATE
var updateRole = this.Settings.GrantExecWriteToRole;
if (!string.IsNullOrWhiteSpace(updateRole))
{
change.AddScript(DbScriptType.Grant, sqlGrantTemplate, sequence.FullName, updateRole);
}
}
public override void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence)
{
const string sqlCreateTemplate = "CREATE Sequence {0} AS {1} START WITH {2} INCREMENT BY {3};";
const string sqlGrantTemplate = "Grant UPDATE on {0} to {1};";
var typeName = GetIntDbTypeName(sequence.Definition.DataType);
change.AddScript(DbScriptType.SequenceAdd, sqlCreateTemplate, sequence.FullName, typeName,
sequence.StartValue, sequence.Increment);
//Grant permission to UPDATE
var updateRole = this.Settings.GetCustomSetting(MsSqlDbDriver.SettingsKeyGrantExecWriteRole, "public");
if (!string.IsNullOrWhiteSpace(updateRole))
{
change.AddScript(DbScriptType.Grant, sqlGrantTemplate, sequence.FullName, updateRole);
}
}
public List <DbSequenceInfo> GetSequences_OLD()
{
var list = new List <DbSequenceInfo>();
string sql =
// @"select x.nspname as ''SchemaName'', x.relname as ''SequenceName'', x.attnum as ''#'', x.attname as ''Column'', x.''Type'', case x.attnotnull when true then ''NOT NULL'' else '''' end as ''NULL?''
@"select x.nspname as ''SchemaName'', x.relname as ''SequenceName'', x.attname as ''ColumnName'', r.conname as ''ConstraintName'', d.adsrc as ''Default''
from (
SELECT
c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as ''Type'', a.attnotnull
FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c
WHERE a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = c.oid
and c.relkind not in ('S','v')
and c.relnamespace = n.oid
and n.nspname not in ('pg_catalog','pg_toast','information_schema') and relkind = 'r'
) x
left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum
left join pg_class f on r.confrelid = f.oid
left join pg_namespace fn on f.relnamespace = fn.oid
where
r.contype = 'p'
order by 1,2,3
".Replace("''", "\"");
using (var r = db.ExecuteReader(sql))
{
while (r.Read())
{
var rec = new DbSequenceInfo();
rec.SchemaName = Conv.ToString(r["SchemaName"]);
rec.SequenceName = Conv.ToString(r["SequenceName"]);
rec.ColumnName = Conv.ToString(r["ColumnName"]);
//rec.ConstraintName = Conv.ToString(r["ConstraintName"]);
//rec.Default = Conv.ToString(r["Default"]);
list.Add(rec);
}
}
return(list);
}
} //method
protected virtual void LoadSequences()
{
if (!Settings.Driver.Supports(DbFeatures.Sequences))
{
return;
}
var data = GetSequences();
foreach (InfoRow row in data.Rows)
{
var schema = row.GetAsString("SEQUENCE_SCHEMA");
var name = row.GetAsString("SEQUENCE_NAME");
var dataTypeName = row.GetAsString("DATA_TYPE");
var startValue = row.GetAsLong("START_VALUE");
var incr = row.GetAsInt("INCREMENT");
// add sequence
var seq = new DbSequenceInfo(this.Model, name, schema, dataTypeName, startValue, incr);
Model.AddSequence(seq);
}
}
public List <DbSequenceInfo> GetSequences()
{
try
{
using (var r = db.ExecuteReader("SELECT schema_name(schema_id) schema_name, name FROM SYS.SEQUENCES"))
{
List <DbSequenceInfo> list = new List <DbSequenceInfo>();
while (r.Read())
{
DbSequenceInfo rec = new DbSequenceInfo();
rec.SchemaName = r.GetString(0);
rec.SequenceName = r.GetString(1);
list.Add(rec);
}
return(list);
}
}
catch (Exception ex)
{
return(new List <DbSequenceInfo>());
}
}
} //method
protected virtual void LoadSequences()
{
if (!Settings.Driver.Supports(DbFeatures.Sequences))
{
return;
}
var data = GetSequences();
foreach (DbRow row in data.Rows)
{
var schema = row.GetAsString("SEQUENCE_SCHEMA");
var name = row.GetAsString("SEQUENCE_NAME");
var dataType = row.GetAsString("DATA_TYPE");
var startValue = row.GetAsLong("START_VALUE");
var incr = row.GetAsInt("INCREMENT");
//find type def
var typeDef = Driver.TypeRegistry.FindVendorDbTypeInfo(dataType);
var typeSpec = typeDef.FormatTypeSpec(0, 0, 0);
var typeInfo = new DbTypeInfo(typeDef, dataType, false, 0, 0, 0, typeDef.DefaultColumnInit);
// add sequence
var seq = new DbSequenceInfo(this.Model, name, schema, typeInfo, startValue, incr);
Model.AddSequence(seq);
}
}
public virtual DbCommandInfo BuildSqlSequenceGetNextCommand(DbSequenceInfo sequence)
{
return(null);
}
}//method
public virtual void BuildSequenceDropSql(DbObjectChange change, DbSequenceInfo sequence)
{
change.AddScript(DbScriptType.SequenceDrop, $"DROP SEQUENCE {sequence.FullName}");
}//method
public virtual void BuildSequenceAddSql(DbObjectChange change, DbSequenceInfo sequence)
{
}
public override void BuildSequenceDropSql(DbObjectChange change, DbSequenceInfo sequence)
{
// PG creates sequences for identity columns, these should not be dropped explicitly;
// we do sequence drop after table drop, so we add check for existense
change.AddScript(DbScriptType.SequenceDrop, "DROP SEQUENCE IF EXISTS {0}", sequence.FullName);
}//method
