public SqlInvocation(string sql, SqlName name, RawToken openParen, SqlArgumentList argumentList, RawToken closeParen)
: base(sql, CreateChildren(name, argumentList))
{
this.Name = name;
this.OpenParen = openParen.WithParent(this);
this.ArgumentList = argumentList;
this.CloseParen = closeParen.WithParent(this);
}
public void Add(SqlName name, ISqlCodeType type)
{
if (this.Content == null)
{
this.Content = new SqlScope();
}
this.Content.Add(name, type);
}
public bool InFilter(string filterText)
{
return(ModuleName.Contains(filterText) ||
PageName.Contains(filterText) ||
PageID.Contains(filterText) ||
ActionName.Contains(filterText) ||
ActionTitle.Contains(filterText) ||
SqlGroupName.Contains(filterText) ||
SqlTableName.Contains(filterText) ||
SqlName.Contains(filterText) ||
SqlDescription.Contains(filterText));
}
private SqlName GetSqlNameSys()
{
var sqlSysName = this._sqlSysName;
if (sqlSysName == null)
{
sqlSysName = SqlName.Schema("sys");
// think
// this._sqlSysName = this._DBScope.ModelDatabase.Schemas.GetValueOrDefault(sqlSysName)?.Name ?? sqlSysName;
// this.currentScope.ScopeNameResolverContext.Resolve(sqlSysName);
this._sqlSysName = this.currentScopeRef.Current.ScopeNameResolverContext.ModelDatabase.Schemas.GetValueOrDefault(sqlSysName)?.Name ?? sqlSysName;
}
return(sqlSysName);
}
public override void ExplicitVisit(DeclareVariableElement node)
{
var declarationScope = this.currentScopeRef.Current.GetDeclarationScope();
node.Analyse.SqlCodeScope = declarationScope;
var variableNameValue = new SqlName(null, node.VariableName.Value, ObjectLevel.Local);
var lazy = new SqlCodeTypeLazy(node.DataType);
node.DataType.Analyse.ResultType = lazy;
node.Analyse.ResultType = lazy;
node.Analyse.SqlCodeScope.Add(variableNameValue, lazy);
base.ExplicitVisit(node);
var resolved = node.Analyse.ResultType.GetResolvedCodeType();
if (resolved != null)
{
node.Analyse.ResultType = resolved;
}
}
/// <summary>
/// Resolve the name.
/// </summary>
/// <param name="name">the name to find the item thats called name</param>
/// <param name="context">the resolver context.</param>
/// <returns>the named object or null.</returns>
public object ResolveObject(SqlName name, IScopeNameResolverContext context)
{
if (this.Content != null)
{
var result = this.Content.ResolveObject(name, context ?? this.ScopeNameResolverContext);
if ((object)result != null)
{
return(result);
}
}
if (this.Previous != null)
{
return(this.Parent.ResolveObject(name, context));
}
if (this.IsDeclaration)
{
// if (this.ModelDatabase != null) {
// var modelType = this.ModelDatabase.ResolveObject(name, context ?? this.ScopeNameResolverContext);
var modelType = this.ScopeNameResolverContext.Resolve(name);
if ((object)modelType != null)
{
return(modelType);
}
// if (modelType != null) {
// if (modelType is ModelSqlType modelSqlType) {
// return modelSqlType.SqlCodeType ?? (modelSqlType.SqlCodeType = new SqlCodeType(modelSqlType));
// }
// if (modelType is ModelSqlObjectWithColumns modelSqlObjectWithColumns) {
// return modelSqlObjectWithColumns.SqlCodeType ?? (modelSqlObjectWithColumns.SqlCodeType = new SqlCodeTypeObjectWithColumns(modelSqlObjectWithColumns));
// }
// }
}
if (this.Parent != null)
{
return(this.Parent.ResolveObject(name, context));
}
return(null);
}
public SchemaImpl(string name, string sql, int expectedPartsInName)
{
Name = new SqlName(name, expectedPartsInName);
Sql = sql;
}
public override bool Exists(IDbConnection connection)
{
// check permissions by querying the permissions table
Match m = Regex.Match(Name.Original, String.Format(CultureInfo.InvariantCulture, @"(?<permission>\w+)\s+ON\s+(?<object>{0})\s+TO\s+(?<user>{0})", SqlParser.SqlNameExpression));
var userName = new SqlName(m.Groups["user"].Value, 1);
var o = m.Groups["object"].Value;
var objectName = new SqlName(o, o.Contains("TYPE::[dbo]") ? 3 : 2);
var permissions = connection.QuerySql(@"SELECT PermissionName=p.permission_name, ObjectType=ISNULL(o.type_desc, p.class_desc)
FROM sys.database_principals u
JOIN sys.database_permissions p ON (u.principal_id = p.grantee_principal_id)
LEFT JOIN sys.objects o ON (p.class_desc = 'OBJECT_OR_COLUMN' AND p.major_id = o.object_id)
LEFT JOIN sys.schemas os ON (o.schema_id = os.schema_id AND os.name = @SchemaName)
LEFT JOIN sys.types t ON (p.class_desc = 'TYPE' AND p.major_id = t.user_type_id)
LEFT JOIN sys.schemas s ON (p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id)
WHERE state_desc IN ('GRANT', 'GRANT_WITH_GRANT_OPTION')
AND u.name = @UserName
AND COALESCE(o.name, t.name, s.name) = @ObjectName" ,
new Dictionary <string, object>()
{
{ "UserName", userName.Object },
{ "SchemaName", objectName.Schema },
{ "ObjectName", objectName.Object }
});
string type = permissions.Select((dynamic p) => p.ObjectType).FirstOrDefault();
string permission = m.Groups["permission"].Value.ToUpperInvariant();
switch (permission)
{
case "EXEC":
return(permissions.Any((dynamic p) => p.PermissionName == "EXECUTE"));
case "ALL":
switch (type)
{
case null:
// this happens on initial install when there is no database
return(false);
case "SQL_STORED_PROCEDURE":
return(permissions.Any((dynamic p) => p.PermissionName == "EXECUTE"));
case "SQL_SCALAR_FUNCTION":
return(permissions.Any((dynamic p) => p.PermissionName == "EXECUTE") &&
permissions.Any((dynamic p) => p.PermissionName == "REFERENCES"));
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
case "SQL_TABLE_VALUED_FUNCTION":
case "USER_TABLE":
case "VIEW":
return(permissions.Any((dynamic p) => p.PermissionName == "REFERENCES") &&
permissions.Any((dynamic p) => p.PermissionName == "SELECT") &&
permissions.Any((dynamic p) => p.PermissionName == "INSERT") &&
permissions.Any((dynamic p) => p.PermissionName == "UPDATE") &&
permissions.Any((dynamic p) => p.PermissionName == "DELETE"));
case "DATABASE":
return(permissions.Any((dynamic p) => p.PermissionName == "BACKUP DATABASE") &&
permissions.Any((dynamic p) => p.PermissionName == "BACKUP LOG") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE DATABASE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE DEFAULT") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE FUNCTION") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE PROCEDURE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE RULE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE TABLE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE VIEW"));
default:
throw new SchemaException(String.Format(CultureInfo.InvariantCulture, "GRANT ALL is not supported for {0} objects", type));
}
default:
return(permissions.Any((dynamic p) => p.PermissionName == permission));
}
}
public BeginTransactionStatement(SqlName transactionIdentifier): base(transactionIdentifier) {}
public RangeVar(string sql, SqlName name, SqlName alias)
: base(sql, CreateChildren(name, alias))
{
this.Name = name;
this.Alias = alias;
}
public RollbackTransactionStatement(SqlName transactionIdentifier): base(transactionIdentifier) {}
public CommitTransactionStatement(SqlName transactionIdentifier): base(transactionIdentifier) {}
/// <summary>
/// Return the column definitions for a table.
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The definitions of the columns in the table.</returns>
public IList<ColumnDefinition> GetColumns(SqlName tableName)
{
List<ColumnDefinition> columns = new List<ColumnDefinition>();
// get the schema of the table and the primary key
string schemaSql = String.Format(CultureInfo.InvariantCulture, @"
SELECT c.name, type_name=t.name, c.max_length, c.precision, c.scale, is_identity, is_readonly = is_identity | is_computed, c.is_nullable,
is_key = CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END,
has_default = CASE WHEN c.default_object_id <> 0 THEN 1 ELSE 0 END
FROM sys.columns c
JOIN sys.types t ON (c.user_type_id = t.user_type_id AND t.is_user_defined = 0)
LEFT JOIN (
SELECT ic.object_id, ic.column_id
FROM sys.index_columns ic
JOIN sys.indexes i ON (ic.object_id = i.object_id AND ic.index_id = i.index_id)
WHERE i.is_primary_key = 1
) AS pk ON (c.object_id = pk.object_id AND c.column_id = pk.column_id)
WHERE c.object_id = OBJECT_ID('{0}')
", tableName.SchemaQualifiedTable);
using (IDataReader reader = _connection.GetReaderSql(schemaSql))
{
while (reader.Read())
{
ColumnDefinition column = new ColumnDefinition()
{
Name = reader["name"].ToString(),
SqlType = reader["type_name"].ToString(),
IsKey = Convert.ToBoolean(reader["is_key"], CultureInfo.InvariantCulture),
IsIdentity = Convert.ToBoolean(reader["is_identity"], CultureInfo.InvariantCulture),
IsReadOnly = Convert.ToBoolean(reader["is_readonly"], CultureInfo.InvariantCulture),
IsUpdateNullable = Convert.ToBoolean(reader["is_nullable"], CultureInfo.InvariantCulture),
HasDefault = Convert.ToBoolean(reader["has_default"], CultureInfo.InvariantCulture),
};
switch (column.SqlType)
{
case "char":
case "nchar":
case "varchar":
case "nvarchar":
case "binary":
case "varbinary":
column.SqlType += String.Format(CultureInfo.InvariantCulture, "({0})", GetColumnLength(column.SqlType, Int32.Parse(reader["max_length"].ToString(), CultureInfo.InvariantCulture)));
break;
case "decimal":
case "numeric":
column.SqlType += String.Format(CultureInfo.InvariantCulture, "({0}, {1})", reader["precision"].ToString(), reader["scale"].ToString());
break;
case "float":
column.SqlType += String.Format(CultureInfo.InvariantCulture, "({0})", reader["precision"].ToString());
break;
case "real":
break;
case "rowversion":
case "timestamp":
column.IsReadOnly = true;
column.IsRowVersion = true;
column.IsUpdateNullable = true;
break;
}
columns.Add(column);
}
}
return columns;
}
private void ScriptColumnsAndConstraints(InstallContext context, SchemaObject schemaObject, string oldTableName, string newTableName)
{
#region Detect Column Changes
Func<dynamic, dynamic, bool> compareColumns = (dynamic c1, dynamic c2) => (String.Compare(c1.Name, c2.Name, StringComparison.OrdinalIgnoreCase) == 0);
Func<dynamic, dynamic, bool> areColumnsEqual = (dynamic c1, dynamic c2) =>
c1.TypeName == c2.TypeName &&
c1.MaxLength == c2.MaxLength &&
c1.Precision == c2.Precision &&
c1.Scale == c2.Scale &&
c1.IsNullable == c2.IsNullable &&
c1.IsIdentity == c2.IsIdentity &&
c1.IdentitySeed == c2.IdentitySeed &&
c1.IdentityIncrement == c2.IdentityIncrement &&
c1.Definition == c2.Definition &&
c1.CollationName == c2.CollationName
;
Func<dynamic, dynamic, bool> areDefaultsEqual = (dynamic c1, dynamic c2) =>
((String.Compare(c1.DefaultName, c2.DefaultName, StringComparison.OrdinalIgnoreCase) == 0) || (c1.DefaultIsSystemNamed == true && c2.DefaultIsSystemNamed == true)) &&
c1.DefaultDefinition == c2.DefaultDefinition
;
Func<dynamic, string> getConstraintName = (dynamic c) => new SqlName(oldTableName, 2).Append(c.Name).FullName;
// get the columns for each of the tables
var oldColumns = GetColumnsForTable(oldTableName);
var newColumns = GetColumnsForTable(newTableName);
// if we are planning on dropping the constraint on a column, then clear it from the old column definition
foreach (dynamic oldColumn in oldColumns.Where(c => context.DropObjects.Any(d => String.Compare(d.ObjectName, getConstraintName(c), StringComparison.OrdinalIgnoreCase) == 0)))
{
oldColumn.DefaultName = null;
oldColumn.DefaultIsSystemNamed = false;
}
// calculate which columns added/dropped
var missingColumns = oldColumns.Except(newColumns, compareColumns).ToList();
var addColumns = newColumns.Except(oldColumns, compareColumns).ToList();
#endregion
#region Rename Columns
// find all of the renames
var renames = Regex.Matches(schemaObject.Sql, String.Format("(?<newname>{0}) WAS (?<oldname>{0})", SqlParser.SqlNameExpression));
foreach (Match rename in renames)
{
var oldName = new SqlName(rename.Groups["oldname"].Value, 1);
var newName = new SqlName(rename.Groups["newname"].Value, 1);
if (missingColumns.Any(c => c.Name == oldName.Object) && addColumns.Any(c => c.Name == newName.Object))
{
var oldColumn = missingColumns.Single(c => c.Name == oldName.Object);
// don't need to add/remove the column
missingColumns.Remove(oldColumn);
addColumns.RemoveAll(c => c.Name == newName.Object);
// script the column rename
StringBuilder sb = new StringBuilder();
sb.AppendFormat("sp_rename '{0}.{1}' , '{2}', 'COLUMN'", oldTableName, oldName.ObjectFormatted, newName.Object);
context.AddObjects.Add(new SchemaObject(SchemaObjectType.InternalPreScript, oldTableName, sb.ToString()));
// rename the old column in memory
oldColumn.Name = newName.Object;
}
}
#endregion
#region Change Columns
// find the changed columns
var changedColumns = newColumns.Where((dynamic cc) =>
{
dynamic oldColumn = oldColumns.FirstOrDefault(oc => compareColumns(cc, oc));
return (oldColumn != null) && (!areColumnsEqual(oldColumn, cc) || !areDefaultsEqual(oldColumn, cc));
}).ToList();
// if we want to modify a computed column, we have to drop/add it
var changedComputedColumns = changedColumns.Where(c => c.Definition != null).ToList();
foreach (var cc in changedComputedColumns)
{
missingColumns.Add(cc);
addColumns.Add(cc);
changedColumns.Remove(cc);
}
// delete old columns - this should be pretty free
if (missingColumns.Any())
{
// if the column has a default, drop it
foreach (dynamic oldColumn in missingColumns.Where(c => c.DefaultName != null))
{
// script the default drop
context.DropObjects.Add(new SchemaRegistryEntry() { Type = SchemaObjectType.Default, ObjectName = SqlParser.FormatSqlName(oldTableName, oldColumn.Name) });
}
// script the column drop
StringBuilder sb = new StringBuilder();
sb.AppendFormat("ALTER TABLE {0}", oldTableName);
sb.Append(" DROP");
sb.AppendLine(String.Join(",", missingColumns.Select((dynamic o) => String.Format(" COLUMN {0}", SqlParser.FormatSqlName(o.Name)))));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// add new columns - this is free when the columns are nullable and possibly with a default
if (addColumns.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("ALTER TABLE {0}", oldTableName);
sb.Append(" ADD ");
sb.AppendLine(String.Join(", ", addColumns.Select((dynamic o) => GetColumnDefinition(o) + GetDefaultDefinition(o))));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// alter columns - either the definition or the default
foreach (dynamic column in changedColumns)
{
// find any indexes that are on that column
ScriptIndexes(context, schemaObject, column.Name);
// find the old column
dynamic oldColumn = oldColumns.First(oc => compareColumns(column, oc));
// if the columns aren't equal then alter the column
if (!areColumnsEqual(column, oldColumn))
{
StringBuilder sb = new StringBuilder();
// if the old column is nullable and the new one is not, and there is a default, then convert the data
if (oldColumn.IsNullable && !column.IsNullable && column.DefaultName != null)
{
string defaultDefinition = column.DefaultDefinition.Substring(2, column.DefaultDefinition.Length - 4);
sb.AppendFormat("UPDATE {0} SET {1} = 2 WHERE {1} IS NULL\n", oldTableName, column.Name, defaultDefinition);
}
// alter the column
sb.AppendFormat("ALTER TABLE {0} ALTER COLUMN ", oldTableName);
sb.AppendFormat(GetColumnDefinition(column));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// modify the defaults
if (!areDefaultsEqual(column, oldColumn))
{
StringBuilder sb = new StringBuilder();
// delete the old default if it exists but it's not in the registry
if (oldColumn.DefaultName != null && !context.SchemaRegistry.Contains(getConstraintName(oldColumn)))
{
// script the default drop
sb.AppendFormat("ALTER TABLE {0} DROP CONSTRAINT {1}\nGO\n", oldTableName, SqlParser.FormatSqlName(oldColumn.DefaultName));
}
// add the new default if we want one
if (column.DefaultName != null)
{
// script the add
sb.AppendFormat("ALTER TABLE {0} ADD ", oldTableName);
sb.AppendFormat(GetDefaultDefinition(column));
sb.AppendFormat(" FOR {0}", SqlParser.FormatSqlName(column.Name));
}
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
}
#endregion
}
public SchemaImpl(string name, string sql, int expectedPartsInName)
{
Name = new SqlName(name, expectedPartsInName);
Sql = sql;
}
/// <summary>
/// Make a drop statement for a table type.
/// </summary>
/// <param name="tableName">The name of the table to drop.</param>
/// <returns>The Drop statement.</returns>
private string MakeTableDropStatment(string tableName)
{
var sqlName = new SqlName(MakeTableName(tableName), 2);
return String.Format(CultureInfo.InvariantCulture, @"
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE ss.name = N'{1}' AND st.name = N'{2}')
DROP TYPE {0}", sqlName.SchemaQualifiedObject, sqlName.Schema, sqlName.Object);
}
/// <summary>
/// Initializes an automatically generated procedure.
/// </summary>
/// <param name="installer">The installer to use when modifying the procedure.</param>
/// <param name="name">The name of the procedure, in in the format of the AutoProcRegex.</param>
/// <param name="objects">The list of objects that are in the schema. Used for change detection.</param>
public AutoProc(string name, IColumnDefinitionProvider columnProvider, IEnumerable<SchemaObject> objects)
{
// initialize dependencies
_columnProvider = columnProvider;
// break up the name into its components
var match = new Regex(AutoProcRegexString, RegexOptions.IgnoreCase).Match(name);
_type = (ProcTypes)Enum.Parse(typeof(ProcTypes), match.Groups["type"].Value);
_tableName = new SqlName(match.Groups["tablename"].Value, 2);
// generate the singular table name
if (!String.IsNullOrWhiteSpace(match.Groups["single"].Value))
_singularTableName = match.Groups["single"].Value;
else
_singularTableName = Singularizer.Singularize(_tableName.Table);
// generate the plural table name
if (!String.IsNullOrWhiteSpace(match.Groups["plural"].Value))
_pluralTableName = match.Groups["plural"].Value;
else
{
_pluralTableName = _tableName.Table;
if (String.Compare(_pluralTableName, _singularTableName, StringComparison.OrdinalIgnoreCase) == 0)
_pluralTableName = _tableName.Table + "s";
}
// get the specified name
string procName = match.Groups["name"].Value;
if (!String.IsNullOrWhiteSpace(procName))
{
if (MoreThanOneBitIsSet((int)_type))
throw new ArgumentException("Can only rename an autoproc if generating one at a time.");
Name = SqlParser.FormatSqlName(procName);
}
// check the exec as owner flag
if (!String.IsNullOrWhiteSpace(match.Groups["tvp"].Value))
_tvpName = match.Groups["tvp"].Value;
if (!String.IsNullOrWhiteSpace(match.Groups["idtvp"].Value))
_idtvpName = match.Groups["idtvp"].Value;
// check the exec as owner flag
if (!String.IsNullOrWhiteSpace(match.Groups["execasowner"].Value))
_executeAsOwner = Boolean.Parse(match.Groups["execasowner"].Value);
// if we received a set of objects, then we can calculate a signature
if (objects != null)
{
Regex optionalSqlName = new Regex(@"([\[\]])");
string escapedWildcardedName = optionalSqlName.Replace(Regex.Escape(_tableName.Table), @"$1?");
Regex regex = new Regex(String.Format(CultureInfo.InvariantCulture, @"(CREATE\s+TABLE\s+.*{0}[\]\s]\s*\()|(ALTER\s+TABLE\s+.*{0}.*PRIMARY\s+KEY)", escapedWildcardedName));
// calculate the signature based upon the TABLE definition, plus any PRIMARY KEY definition for the table
string sql = String.Join(" ", objects.Where(o => regex.Match(o.Sql).Success)
.Select(o => o.Sql)
.OrderBy(s => s));
// add a version signature so we can force updates if we need to
sql += VersionSignature;
Signature = SchemaObject.CalculateSignature(sql);
}
else
{
// we don't know what the schema is, so assume that the proc has changed
Signature = Guid.NewGuid().ToString();
}
}
public BeginTransactionWithMarkStatement(SqlName transactionIdentifier): this(transactionIdentifier, null) {}
protected TransactionStatement(SqlName transactionIdentifier) {
this.transactionIdentifier = transactionIdentifier;
}
private static bool IsLocalName(SqlName name)
{
TableName tableName = name as TableName;
if (tableName != null) {
return tableName.IsTempTable;
}
return name is VariableName;
}
public BeginTransactionWithMarkStatement(SqlName transactionIdentifier, StringLiteral markName): base(transactionIdentifier) {
this.markName = markName;
}
public override bool Exists(IDbConnection connection)
{
// check permissions by querying the permissions table
Match m = Regex.Match(Name.Original, String.Format(CultureInfo.InvariantCulture, @"(?<permission>\w+)\s+ON\s+(?<object>{0})\s+TO\s+(?<user>{0})", SqlParser.SqlNameExpression));
var userName = new SqlName(m.Groups["user"].Value, 1);
var objectName = new SqlName(m.Groups["object"].Value, 2);
var permissions = connection.QuerySql(@"SELECT PermissionName=p.permission_name, ObjectType=ISNULL(o.type_desc, p.class_desc)
FROM sys.database_principals u
JOIN sys.database_permissions p ON (u.principal_id = p.grantee_principal_id)
LEFT JOIN sys.objects o ON (p.class_desc = 'OBJECT_OR_COLUMN' AND p.major_id = o.object_id)
LEFT JOIN sys.schemas os ON (o.schema_id = os.schema_id AND os.name = @SchemaName)
LEFT JOIN sys.types t ON (p.class_desc = 'TYPE' AND p.major_id = t.user_type_id)
LEFT JOIN sys.schemas s ON (p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id)
WHERE state_desc IN ('GRANT', 'GRANT_WITH_GRANT_OPTION')
AND u.name = @UserName
AND COALESCE(o.name, t.name, s.name) = @ObjectName",
new Dictionary<string, object>()
{
{ "UserName", userName.Object },
{ "SchemaName", objectName.Schema },
{ "ObjectName", objectName.Object }
});
string type = permissions.Select((dynamic p) => p.ObjectType).FirstOrDefault();
string permission = m.Groups["permission"].Value.ToUpperInvariant();
switch (permission)
{
case "EXEC":
return permissions.Any((dynamic p) => p.PermissionName == "EXECUTE");
case "ALL":
switch (type)
{
case null:
// this happens on initial install when there is no database
return false;
case "SQL_STORED_PROCEDURE":
return permissions.Any((dynamic p) => p.PermissionName == "EXECUTE");
case "SQL_SCALAR_FUNCTION":
return permissions.Any((dynamic p) => p.PermissionName == "EXECUTE") &&
permissions.Any((dynamic p) => p.PermissionName == "REFERENCES");
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
case "SQL_TABLE_VALUED_FUNCTION":
case "USER_TABLE":
case "VIEW":
return permissions.Any((dynamic p) => p.PermissionName == "REFERENCES") &&
permissions.Any((dynamic p) => p.PermissionName == "SELECT") &&
permissions.Any((dynamic p) => p.PermissionName == "INSERT") &&
permissions.Any((dynamic p) => p.PermissionName == "UPDATE") &&
permissions.Any((dynamic p) => p.PermissionName == "DELETE");
case "DATABASE":
return permissions.Any((dynamic p) => p.PermissionName == "BACKUP DATABASE") &&
permissions.Any((dynamic p) => p.PermissionName == "BACKUP LOG") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE DATABASE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE DEFAULT") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE FUNCTION") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE PROCEDURE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE RULE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE TABLE") &&
permissions.Any((dynamic p) => p.PermissionName == "CREATE VIEW");
default:
throw new SchemaException(String.Format(CultureInfo.InvariantCulture, "GRANT ALL is not supported for {0} objects", type));
}
default:
return permissions.Any((dynamic p) => p.PermissionName == permission);
}
}
public void Utility_ReadAll2_Test()
{
var sut = new Utility()
{
ConnectionString = TestCfg.Get().ConnectionString
};
sut.ReadAll();
var db = sut.ModelDatabase;
/*
* SELECT [SCHEMA] = SCHEMA_NAME(schema_id),name,type,type_desc from sys.objects where is_ms_shipped=0
* dbo synonyma SN SYNONYM
* dbo ScalarFunctionNameA FN SQL_SCALAR_FUNCTION
* dbo TableFunctionNameA TF SQL_TABLE_VALUED_FUNCTION
* dbo Name U USER_TABLE
* dbo PK_Name PK PRIMARY_KEY_CONSTRAINT
* dbo NameA V VIEW
* dbo inlineFuncA IF SQL_INLINE_TABLE_VALUED_FUNCTION
* dbo NameValue U USER_TABLE
* dbo PK_NameValue PK PRIMARY_KEY_CONSTRAINT
* dbo p P SQL_STORED_PROCEDURE
* dbo proca P SQL_STORED_PROCEDURE
*/
{
var guest = db.Schemas.GetValueOrDefault(SqlName.Schema("guest"));
Assert.NotNull(guest);
}
{
var t = db.Tables.GetValueOrDefault(SqlName.Parse("dbo.NameValue", ObjectLevel.Object));
Assert.NotNull(t);
Assert.Equal(4, t.Columns.Count);
}
{
var t = db.Tables.GetValueOrDefault(SqlName.Parse("dbo.Name", ObjectLevel.Object));
Assert.NotNull(t);
Assert.Equal(3, t.Columns.Count);
Assert.Equal("idx", t.Columns[0].Name.Name);
Assert.Equal("name", t.Columns[1].Name.Name);
Assert.Equal("RowVersion", t.Columns[2].Name.Name);
}
{
var v = db.Views.GetValueOrDefault(SqlName.Parse("dbo.NameA", ObjectLevel.Object));
Assert.NotNull(v);
Assert.Equal(3, v.Columns.Count);
}
{
var p = db.Procedures.GetValueOrDefault(SqlName.Parse("dbo.proca", ObjectLevel.Object));
Assert.NotNull(p);
}
{
var p = db.Synonyms.GetValueOrDefault(SqlName.Parse("dbo.synonyma", ObjectLevel.Object));
Assert.NotNull(p);
}
{
#warning here TableTypes SQL
//var p = db.TableTypes.GetValueOrDefault(SqlName.Parse("[dbo].[TVP_Name]", ObjectLevel.Object));
//Assert.NotNull(p);
}
}
public override void ExplicitVisit(SqlDataTypeReference node)
{
var name = node.Name;
var parameters = node.Parameters;
SqlName typename = SqlName.From(node.Name);
var sqlDataTypeOption = node.SqlDataTypeOption;
short length = 0;
bool isMax = false;
byte precision = 0;
byte scale = 0;
string collationName = null;
foreach (var parameter in parameters)
{
{
if (parameter is MaxLiteral)
{
isMax = true;
continue;
}
}
{
if (parameter is IntegerLiteral lengthLiteral)
{
short.TryParse(lengthLiteral.Value, out length);
continue;
}
}
// TODO: float(1,2)
// TODO: collationName
}
if (sqlDataTypeOption != ModelSystemDataType.None)
{
// happyness
var scalarType = new ModelSematicScalarType();
scalarType.Name = typename;
scalarType.MaxLength = (isMax) ? ((short)-1) : length;
scalarType.Precision = precision;
scalarType.Scale = scale;
scalarType.SystemDataType = sqlDataTypeOption;
scalarType.CollationName = collationName;
node.Analyse.ResultType = new SqlCodeType(scalarType);
}
else
{
if (System.Diagnostics.Debugger.IsAttached)
{
System.Diagnostics.Debugger.Break();
}
var resolved = this.scope.ResolveObject(typename, null);
if (resolved != null)
{
if (resolved is ModelSematicType modelType)
{
node.Analyse.ResultType = new SqlCodeType(modelType);
}
}
/*
* var sqlName = SqlName.Parse(name.BaseIdentifier.Value, ObjectLevel.Object);
* var sqlSysName = SqlName.Root.Child("sys", ObjectLevel.Schema).Child(sqlName.Name, ObjectLevel.Object);
* var sqlCodeType = this.currentScopeRef.Current.ResolveObject(sqlSysName, null) as ISqlCodeType;
* SetAnalyseSqlCodeType(node.Analyse, sqlCodeType);
*/
}
}
public void BuildModelSqlDatabase(
ModelSchema modelSchema,
ModelSqlDatabase modelDatabase,
MetaModelBuilder metaModelBuilder,
ModelErrors errors)
{
foreach (var modelEntitySource in modelSchema.Entities)
{
var modelEntityTypeSource = modelEntitySource.EntityType;
if (modelEntityTypeSource is null)
{
#warning SOON add error
}
else
{
var tableNameTarget = modelEntitySource.ExternalName ?? modelEntitySource.Name;
var sqlTableNameTarget = SqlName.Parse(tableNameTarget, ObjectLevel.Object);
var tableTarget = ModelSqlTable.Ensure(modelDatabase, sqlTableNameTarget);
foreach (var property in modelEntityTypeSource.Properties)
{
var sqlColumnNameTarget = SqlName.Parse(property.ExternalName ?? property.Name, ObjectLevel.Object);
ModelSqlColumn column = ModelSqlColumn.Ensure(tableTarget, sqlColumnNameTarget.Name);
if (property.Type is ModelScalarType propertyScalarType)
{
var clrTypeSource = property.GetClrType();
var typeName = propertyScalarType.Name;
#warning TODO SOON better Scalar Type Handling this is ugly
if (!(clrTypeSource is null))
{
var innerNullableClrTypeSource = ((clrTypeSource.IsValueType) ? Nullable.GetUnderlyingType(clrTypeSource) : null) ?? clrTypeSource;
if (!(column.SqlType is null))
{
var clrScalarTypeTarget = column.SqlType.GetScalarType()?.GetClrType();
var innerNullableClrScalarTypeTarget = ((clrScalarTypeTarget.IsValueType) ? Nullable.GetUnderlyingType(clrScalarTypeTarget) : null) ?? clrScalarTypeTarget;
if (!(clrScalarTypeTarget is null) && (clrScalarTypeTarget.IsAssignableFrom(innerNullableClrScalarTypeTarget)))
{
// ok
}
else
{
column.SqlType = null;
}
}
}
if (column.SqlType is null)
{
var sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse(typeName, ObjectLevel.Object));
if (!(sqlTypeTarget is null))
{
column.SqlType = sqlTypeTarget;
}
else
{
if (!(clrTypeSource is null))
{
var innerNullableClrTypeSource = ((clrTypeSource.IsValueType) ? Nullable.GetUnderlyingType(clrTypeSource) : null) ?? clrTypeSource;
var lstTypes = new List <ModelSqlType>();
foreach (var type in modelDatabase.Types)
{
var clrScalarTypeTarget = type.GetScalarType()?.GetClrType();
if (clrScalarTypeTarget is null)
{
continue;
}
var innerNullableClrScalarTypeTarget = ((clrScalarTypeTarget.IsValueType) ? Nullable.GetUnderlyingType(clrScalarTypeTarget) : null) ?? clrScalarTypeTarget;
if (innerNullableClrTypeSource.Equals(innerNullableClrScalarTypeTarget))
{
lstTypes.Add(type);
}
else if (innerNullableClrTypeSource.IsAssignableFrom(innerNullableClrScalarTypeTarget))
{
lstTypes.Add(type);
}
}
if (lstTypes.Count == 1)
{
sqlTypeTarget = lstTypes[0];
column.SqlType = sqlTypeTarget;
}
else if (lstTypes.Count > 1)
{
sqlTypeTarget = null;
if (clrTypeSource == typeof(string))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[nvarchar]", ObjectLevel.Object));
}
else if (clrTypeSource == typeof(DateTime))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[datetime2]", ObjectLevel.Object));
}
else if (clrTypeSource == typeof(DateTime?))
{
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[datetime2]", ObjectLevel.Object));
}
if (sqlTypeTarget is null)
{
sqlTypeTarget = lstTypes[0];
}
column.SqlType = sqlTypeTarget;
}
else
{
errors.Add(new ModelErrorInfo($"Unknown mapping for {typeName} - {clrTypeSource}.", column.NameSql));
sqlTypeTarget = modelDatabase.Types.GetValueOrDefault(SqlName.Parse("[sys].[nvarchar]", ObjectLevel.Object));
column.SqlType = sqlTypeTarget;
}
}
}
sqlTypeTarget = column.SqlType;
if (sqlTypeTarget is null)
{
errors.Add(new ModelErrorInfo($"column.SqlType is null.", column.NameSql));
}
else
{
sqlTypeTarget.Nullable = propertyScalarType.Nullable.GetValueOrDefault(true);
if (propertyScalarType.MaxLength.HasValue)
{
sqlTypeTarget.MaxLength = propertyScalarType.MaxLength;
}
else
{
//if (typeof(string).Equals(sqlTypeTarget.GetScalarType().GetClrType())) {
// sqlTypeTarget.MaxLength = -1;
//}
}
}
} // if (column.SqlType is null)
}
