static RoutinesTable() {
Table = new RealSqlTable("INFORMATION_SCHEMA.ROUTINES");
SpecificName = new SqlColumn("SPECIFIC_NAME", Table);
RoutineSchema = new SqlColumn("ROUTINE_SCHEMA", Table);
RoutineType = new SqlColumn("ROUTINE_TYPE", Table);
}
/// <summary>
/// Adds a <see cref="SqlCompareFilter"/> to the given collection.
/// </summary>
/// <param name="collection"></param>
/// <param name="table"></param>
/// <param name="columnName"></param>
/// <param name="oper"></param>
/// <param name="dataType"></param>
/// <param name="value"></param>
/// <returns></returns>
public static SqlCompareFilter Add(this ICollection <ISqlFilter> collection, ISqlTable table, string columnName, SqlOperator oper, System.Data.DbType dataType, object value)
{
var item = new SqlCompareFilter(table, columnName, oper, dataType, value);
collection.Add(item);
return(item);
}
public static bool IsPrimaryKey(this ISqlColumn column, ISqlTable table)
{
var primaryKey = table.Constraints.OfType<ISqlPrimaryKeyConstraint>().FirstOrDefault();
return primaryKey != null &&
primaryKey.ColumnSpecifications[0].Column.Name.Parts[2] == column.Name.Parts[2];
}
public static bool HasFoeignKeyAlsoDefinedOnPrimaryKeyColumns(this ISqlTable table)
{
var foreignKeyConstraints = table.Constraints.OfType <ISqlForeignKeyConstraint> ().ToList();
return(foreignKeyConstraints.Count != 0 &&
foreignKeyConstraints.Any(foreignKeyConstraint => foreignKeyConstraint.IsDefinedOnSameColumnsAsPrimaryKey()));
}
/// <summary>
/// Creates a set of columns referencing the given table.
/// </summary>
/// <param name="table"></param>
/// <param name="columnExpressions"></param>
/// <returns></returns>
public static IEnumerable <ISqlColumn> Columns(this ISqlTable table, params string[] columnExpressions)
{
foreach (var columnExpr in columnExpressions)
{
yield return(SqlColumn.Parse(table, columnExpr));
}
}
/// <summary>
/// Creates a new instance with the given table, column, operator, and value
/// </summary>
/// <param name="table"></param>
/// <param name="columnName"></param>
/// <param name="operator"></param>
/// <param name="value"></param>
public SqlCompareFilter(ISqlTable table, string columnName, SqlOperator @operator, TValue value) : this()
{
this.Table = table;
this.ColumnName = columnName;
this.Operator = @operator;
this.Value = value;
}
public SqlDateTimeFilter(ISqlTable table, string columnName, DateTime beginDate, DateTime endDate)
{
this.Table = table;
this.ColumnName = columnName;
this.BeginDate = beginDate;
this.EndDate = endDate;
}
public static bool IsPrimaryKey(this ISqlColumn column, ISqlTable table)
{
var primaryKey = table.Constraints.OfType <ISqlPrimaryKeyConstraint>().FirstOrDefault();
return(primaryKey != null &&
primaryKey.ColumnSpecifications[0].Column.Name.Parts[2] == column.Name.Parts[2]);
}
public SqlJoin(ISqlTable leftTable, SqlJoinType joinType, ISqlTable rightTable, IEnumerable <ISqlFilter> conditions)
{
this.LeftTable = leftTable;
this.JoinType = joinType;
this.RightTable = rightTable;
this.Conditions.AddRange(conditions);
}
static DbColumns() {
Table = new RealSqlTable("INFORMATION_SCHEMA.Columns");
TableName = new SqlColumn("TABLE_NAME", Table);
TableSchema = new SqlColumn("TABLE_SCHEMA", Table);
ColumnName = new SqlColumn("COLUMN_NAME", Table);
}
/// <summary>
/// Initializes a new instance with the given table and AND/OR logic.
/// </summary>
/// <param name="root"></param>
/// <param name="logic"></param>
public DbQueryWhereClause(ISqlTable root, SqlLogic logic)
{
_root = root;
this.Filters = new SqlFilterCollection()
{
Logic = logic
};
}
public static void Add(this ICollection <ISqlJoin> collection, ISqlTable leftTable, SqlJoinType joinType, ISqlTable rightTable, string key)
{
var cond = new List <ISqlFilter>();
//TODO: This sucks, Fix it
cond.Add(new SqlColumnCompareFilter(leftTable, key, SqlOperator.Equal, rightTable));
collection.Add(leftTable, joinType, rightTable, cond);
}
/// <summary>
/// Creates a new instance with the given table, column, operator, and value
/// </summary>
/// <param name="table"></param>
/// <param name="columnName"></param>
/// <param name="operator"></param>
/// <param name="value"></param>
public SqlCompareFilter(ISqlTable table, string columnName, SqlOperator @operator, TValue value) : this()
{
this.Table = table;
this.ColumnName = columnName;
this.Operator = @operator;
this.DataType = util.ConvertTypeCodeToDbType(Type.GetTypeCode(typeof(TValue)));
this.Value = value;
}
internal SqlSelectBuilder(ISqlTable table, IEnumerable <ISqlJoin> joins, IList <ISqlColumn> columns, IEnumerable <ISqlFilter> filters, IList <SqlOrderColumn> orderBy) : this()
{
this.Table = table;
_joins = joins.ToList();
_columns = columns;
_filters = filters.ToList();
_orderBy = orderBy;
}
public SqlCountFilter(ISqlTable table, ISqlTable subQueryTable, string subQueryColumn, SqlOperator @operator, int countValue)
{
this.Table = table;
this.SubQueryColumn = subQueryColumn;
this.SubQueryTable = subQueryTable;
this.CountOperator = @operator;
this.CountValue = countValue;
}
public SqlColumnCompareFilter(ISqlTable leftTable, string leftColumnName, SqlOperator @operator, ISqlTable rightTable, string rightColumnName)
{
this.LeftTable = leftTable;
this.LeftColumnName = leftColumnName;
this.RightColumnName = rightColumnName;
this.Operator = @operator;
this.RightTable = rightTable;
}
public void AssignAlias(ISqlTable table)
{
int index = _tables.IndexOf(table);
if (index < 0)
{
_tables.Add(table);
}
}
public string TableName(ISqlTable table)
{
int index = _tables.IndexOf(table);
if (index < 0)
{
throw new InvalidOperationException("The specified table is not associated with the query.");
}
return(string.Format(SqlTableNameFormat, index));
}
public override void SetTable(MappingSchema mappingSchema, ISqlTable table, MemberInfo member,
IEnumerable <Expression> expArgs, IEnumerable <IQueryExpression> sqlArgs)
{
var arg = sqlArgs.ElementAt(1);
var ed = mappingSchema.GetEntityDescriptor(table.ObjectType);
var sqlParameter = arg as ISqlParameter;
if (sqlParameter != null)
{
var exp = expArgs.ElementAt(1).Unwrap();
var constantExpression = exp as ConstantExpression;
if (constantExpression != null)
{
if (constantExpression.Value is Func <string> )
{
sqlParameter.ValueConverter = l => ((Func <string>)l)();
}
else
{
sqlParameter.ValueConverter = GetXmlConverter(mappingSchema, table);
}
}
else if (exp is LambdaExpression)
{
sqlParameter.ValueConverter = l => ((Func <string>)l)();
}
}
var columns = ed.Columns
.Select((c, i) => "{0} {1} path 'c{2}'".Args(
c.ColumnName,
string.IsNullOrEmpty(c.DbType)
? GetDataTypeText(
new SqlDataType(
c.DataType == DataType.Undefined
? SqlDataType.GetDataType(c.MemberType).DataType
: c.DataType,
c.MemberType,
c.Length,
c.Precision,
c.Scale))
: c.DbType,
i))
.Aggregate((s1, s2) => s1 + ", " + s2);
table.SqlTableType = ESqlTableType.Expression;
table.Name = "XmlTable('/t/r' PASSING XmlType({2}) COLUMNS " + columns + ") {1}";
table.TableArguments.Clear();
table.TableArguments.AddFirst(arg);
}
public ISqlTableSource FindTableSource(ISqlTable table)
{
foreach (var source in Tables)
{
var ts = FindTableSource(source, table);
if (ts != null)
{
return(ts);
}
}
return(null);
}
public QueryBuilder RightJoin(ISqlTable table, ISqlTable foreignTable, params LocalForeignKey[] foreignKeys)
{
var relation = new TableRelation
{
Table = table,
ForeignTable = foreignTable,
JoinType = TableJoinType.RightJoin,
LocalForeignKeys = foreignKeys.ToList()
};
query.JoinTables.Add(relation);
return(this);
}
public override IQueryExpression GetIdentityExpression(ISqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
{
return(new SqlExpression(attr.SequenceName + ".nextval", Precedence.Primary));
}
}
return(base.GetIdentityExpression(table));
}
public void RenderFrom(ISqlTable table, SqlBuildArguments args)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (args == null)
{
throw new ArgumentNullException("args");
}
this.WriteFrom();
table.Render(this, args);
this.WriteNewLine();
}
public QueryBuilder InnerJoin(ISqlTable table, ISqlTable foreignTable, ISqlField localKey, ISqlField foreignKey)
{
var relation = new TableRelation
{
Table = table,
ForeignTable = foreignTable,
JoinType = TableJoinType.InnerJoin
};
relation.LocalForeignKeys.Add(new LocalForeignKey(localKey, foreignKey));
query.JoinTables.Add(relation);
return(this);
}
/// <summary>
/// Returns the <see cref="SqlTable"/> instance for the specified <paramref name="metaType"/>.
/// </summary>
/// <param name="metaType">The <see cref="MetaType"/> of the entity.</param>
/// <returns>The <see cref="SqlTable"/> instance for <paramref name="metaType"/>.</returns>
protected internal SqlTable Table(MetaType metaType)
{
SqlTable table;
if (!this.tables.TryGetValue(metaType, out table))
{
ISqlTable genericTable = (ISqlTable)
tableMethod.MakeGenericMethod(metaType.Type).Invoke(this, null);
table = new SqlTable(this, metaType, genericTable);
this.tables.Add(metaType, table);
}
return(table);
}
public static ISqlForeignKeyConstraint GetForeignKeyForColumn(this ISqlTable table, ISqlColumn column)
{
var foreignKeys =
table.Constraints.OfType <ISqlForeignKeyConstraint>();
foreach (var foreignKey in foreignKeys)
{
var columns = foreignKey.Columns.Where(p => p.Name.Parts[2] == column.Name.Parts[2]);
if (columns.Count() == 1)
{
return(foreignKey);
}
}
return(null);
}
private static ITableSource FindTableSource(ITableSource source, ISqlTable table)
{
if (source.Source == table)
{
return(source);
}
source.Joins.ApplyUntilNonDefaultResult(
node =>
{
var join = node.Value;
var ts = FindTableSource(join.Table, table);
return(ts);
});
return(null);
}
/// <summary>
/// Creates a <see cref="SqlColumn"/> from the given string expression and table expression.
/// </summary>
/// <param name="table">The table expression that this column references.</param>
/// <param name="columnExpression">A column expression in the form of a single name, or name and alias.</param>
/// <returns></returns>
public static SqlColumn Parse(ISqlTable table, string columnExpression)
{
string[] parts = columnExpression.Split(new string[] { "AS" }, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length == 2)
{
return(new SqlColumn(table, parts[0].Trim(), parts[1].Trim()));
}
else if (parts.Length == 1)
{
return(new SqlColumn(table, parts[0].Trim()));
}
else
{
throw new FormatException("The format of the columnExpression is not valid.");
}
}
public virtual void RenderJoin(ISqlTable leftTable, SqlJoinType joinType, ISqlTable rightTable, IEnumerable <ISqlFilter> conditions, SqlBuildArguments args)
{
this.WriteSpace();
this.Write(joinType.ToString().ToUpper());
this.WriteSpace();
this.Write("JOIN");
this.WriteSpace();
//TODO: This is causing joined tables to render their conditions here, which is not right.
rightTable.Render(this, args);
if (joinType != SqlJoinType.Cross)
{
this.WriteSpace();
this.Write(SqlConstants.ON);
this.WriteSpace();
this.RenderAll <ISqlFilter>(conditions, args, string.Concat(SqlConstants.SPACE, ConvertSqlLogicToString(SqlLogic.And), SqlConstants.SPACE));
}
this.WriteNewLine();
}
public override IQueryExpression GetIdentityExpression(ISqlTable table)
{
if (!table.SequenceAttributes.IsNullOrEmpty())
{
var attr = GetSequenceNameAttribute(table, false);
if (attr != null)
{
var name = Convert(attr.SequenceName, ConvertType.NameToQueryTable).ToString();
var database = GetTableDatabaseName(table);
var owner = GetTableOwnerName(table);
var sb = BuildTableName(new StringBuilder(), database, owner, name);
return(new SqlExpression("nextval('" + sb + "')", Precedence.Primary));
}
}
return(base.GetIdentityExpression(table));
}
public override IList <DataRuleProblem> Analyze(DataRuleSetting ruleSetting, DataRuleExecutionContext context)
{
var foreignKeyConstraint = context.ModelElement as ISqlForeignKeyConstraint;
if (foreignKeyConstraint == null)
{
return(null);
}
ISqlSpecifiesTable definingTable = foreignKeyConstraint.DefiningTable;
ISqlTable foreignTable = foreignKeyConstraint.ForeignTable;
IList <DataRuleProblem> problems = new List <DataRuleProblem> ();
string prefix = definingTable.Name.Parts[1] + "_" + foreignTable.Name.Parts[1];
const string suffix = "_FK";
string foreignKeyPattern = @"^" + prefix + @"[a-zA-Z0-9_]*" + suffix;
if (!Regex.IsMatch(foreignKeyConstraint.Name.Parts[1], foreignKeyPattern))
{
string ruleProblemDescription = string.Format(CultureInfo.CurrentCulture,
"Defining Table [{0}].[{1}]: ForeignKey is named improperly. Expected to start with {2} and end with {3}. Found {4}",
definingTable.Name.Parts[0],
definingTable.Name.Parts[1],
prefix,
suffix,
foreignKeyConstraint.Name.Parts[1]);
var problem = new DataRuleProblem(this, ruleProblemDescription, foreignKeyConstraint)
{
FileName = foreignKeyConstraint.PrimarySource.SourceName,
StartLine = foreignKeyConstraint.PrimarySource.StartLine,
StartColumn = foreignKeyConstraint.PrimarySource.StartColumn
};
problems.Add(problem);
}
return(problems);
}
public override void SetTable(MappingSchema mappingSchema, ISqlTable table, MemberInfo member,
IEnumerable <Expression> arguments, IEnumerable <IQueryExpression> sqlArgs)
{
table.SqlTableType = ESqlTableType.Expression;
table.Name = Expression ?? member.Name;
var args = ConvertArgs(member, sqlArgs.ToArray());
for (var i = 0; i < args.Length; i++)
{
table.TableArguments.AddLast(args[i]);
}
if (Schema != null)
{
table.Owner = Schema;
}
if (Database != null)
{
table.Database = Database;
}
}
public SelectStatement Join(JoinType type, ISqlTable table, params Expression[] cond) {
SelectStatement clone = Clone();
clone.myJoins.Add(new JoinPart
{JoinType = type, Table = table, Conditions = new List<Expression>(cond)});
return clone;
}
public SelectStatement Join(ISqlTable table, params Expression[] cond) {
return Join(JoinType.InnerJoin, table, cond);
}
static DbTables() {
Table = new RealSqlTable("INFORMATION_SCHEMA.Tables");
TableName = new SqlColumn("TABLE_NAME", Table);
TableSchema = new SqlColumn("TABLE_SCHEMA", Table);
}
static Office() {
Table = new RealSqlTable("office");
Id = new SqlColumn("id", Table);
Name = new SqlColumn("name", Table);
}
public OfficeTable(string tableName) {
Table = new RealSqlTable("office", tableName);
Id = new SqlColumn("id", Table);
Name = new SqlColumn("name", Table);
}
private UsersGoodBalanceView() {
Table = Sql.SubQuery(Sql.Select(
Users.Id.Bind(out Id),
Users.Name.Bind(out Name)).Where(Users.Balance > Sql.Const(0)));
}
public static ISqlConstraint GetPrimaryKey(this ISqlTable table)
{
var primaryKey = table.Constraints.OfType <ISqlPrimaryKeyConstraint>().FirstOrDefault();
return(primaryKey);
}
public SqlColumn(string name, ISqlTable table) {
Name = name;
Table = table;
}
