internal static string Where(GXDBSettings Settings, LambdaExpression value, bool removebrackets)
{
if (value != null)
{
string str;
string post = null;
if (Settings.UseQuotationWhereColumns)
{
str = GXDbHelpers.GetMembers(Settings, value.Body, Settings.ColumnQuotation, true, ref post)[0];
}
else
{
str = GXDbHelpers.GetMembers(Settings, value.Body, '\'', true, ref post)[0];
}
//Remove brackets.
if (removebrackets && str.Length > 2)
{
return(str.Substring(1, str.Length - 2));
}
else
{
return(str);
}
}
return(null);
}
internal void UpdateTables(System.Type table1, System.Type table2)
{
Table1Type = table1;
Table2Type = table2;
Table1 = GXDbHelpers.GetTableName(table1, false, null);
Table2 = GXDbHelpers.GetTableName(table2, false, null);
}
public static GXDeleteArgs Delete <T>(T item)
{
if (item == null)
{
throw new ArgumentNullException("Deleted item can't be null.");
}
if (item is GXTableBase tb)
{
tb.BeforeRemove();
}
GXDeleteArgs arg;
if (item is IEnumerable)
{
arg = Delete(GXInternal.GetPropertyType(typeof(T)));
foreach (var it in item as IEnumerable)
{
arg.Where.Or <T>(q => it);
}
return(arg);
}
GXSerializedItem si = GXSqlBuilder.FindUnique(typeof(T));
if (si == null)
{
throw new ArgumentException("Delete by ID failed. Target class must be derived from IUnique.");
}
string name = GXDbHelpers.GetColumnName(si.Target as PropertyInfo, '\0');
arg = DeleteAll <T>();
arg.Where.Or <IUnique <T> >(_ => name.Equals(item));
return(arg);
}
public override string ToString()
{
if (Parent.Updated)
{
List <string> queries = new List <string>();
GXDbHelpers.GetQueries(true, Parent.Settings, Values, queries);
sql = string.Join(" ", queries.ToArray());
}
return(sql);
}
public string ToString(bool addExecutionTime)
{
if (Parent.Updated)
{
List <string> queries = new List <string>();
GXDbHelpers.GetQueries(true, Parent.Settings, Values, Excluded, queries);
sql = string.Join(" ", queries.ToArray());
}
return(sql);
}
private void UpdateMaps(GXDBSettings settings, GXMapCollection list, List <GXJoin> joins)
{
Select.Columns.Maps.Clear();
foreach (BinaryExpression it in list.List)
{
var e = (MemberExpression)it.Right;
string[] target = GXDbHelpers.GetMembers(settings, it.Left, settings.TableQuotation, false, false);
string[] source = GXDbHelpers.GetMembers(settings, it.Right, settings.TableQuotation, false, false);
Select.Columns.Maps.Add(source[0], target[0]);
}
}
public override string ToString()
{
UpdateMaps(Settings, Maps, null);
StringBuilder sb = new StringBuilder();
sb.Append("Create View ");
sb.Append(GXDbHelpers.GetTableName(type, true, Parent.Settings.TableQuotation, null));
sb.Append(" AS ");
sb.Append(Select.ToString());
return(sb.ToString());
}
/// <summary>
/// Order values by.
/// </summary>
/// <param name="sourceColumn">Columns order by.</param>
internal static void OrderBy(GXDBSettings settings, LambdaExpression sourceColumn, List <GXOrder> OrderList)
{
string[] list = GXDbHelpers.GetMembers(settings, sourceColumn.Body, '\0', false);
foreach (string it in list)
{
GXOrder o = new GXOrder();
o.Table = sourceColumn.Parameters[0].Type;
o.Column = it;
OrderList.Add(o);
}
}
private static GXSelectArgs SelectByIdInternal <T, IDTYPE>(IDTYPE id, Expression <Func <T, object> > columns)
{
GXSelectArgs arg = GXSelectArgs.Select <T>(columns);
GXSerializedItem si = GXSqlBuilder.FindUnique(typeof(T));
if (si == null)
{
throw new ArgumentException("Select by ID failed. Target class must be derived from IUnique.");
}
string name = GXDbHelpers.GetColumnName(si.Target as PropertyInfo, '\0');
arg.Where.Or <IUnique <T> >(q => name.Equals(id));
return(arg);
}
/// <inheritdoc cref="GXDBSettings.CreateAutoIncrement"/>
public override string[] CreateAutoIncrement(string tableName, string columnName)
{
//IDENTITY don't work with multiple insert at the same query.
//Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:
//https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns002.htm#SQLRF50946
string trigger = GetTriggerName(tableName, columnName);
tableName = GXDbHelpers.AddQuotes(tableName, this.TableQuotation);
columnName = GXDbHelpers.AddQuotes(columnName, this.ColumnQuotation);
//Create sequence.
return(new string[] { "DECLARE\n C NUMBER;\nBEGIN\nSELECT COUNT(*) INTO C FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '" + trigger + "';\n" +
"IF (C = 0) THEN\n EXECUTE IMMEDIATE 'CREATE SEQUENCE " + trigger + "';\nEND IF;END;",
//Create or replace trigger.
"CREATE OR REPLACE TRIGGER " + trigger + " BEFORE INSERT ON " + tableName + " FOR EACH ROW\n" +
"BEGIN\n SELECT " + trigger + ".NEXTVAL\n INTO\n :new." + columnName + "\n \nFROM dual;\nEND;" });
}
public string ToString(bool addExecutionTime)
{
if (Parent.Updated)
{
List <string> queries = new List <string>();
if (Where.List.Count == 0)
{
//Get inserted items.
GXDbHelpers.GetQueries(true, Parent.Settings, Values, Excluded, queries, Where, null);
}
//Get updated items.
GXDbHelpers.GetQueries(false, Parent.Settings, Values, Excluded, queries, Where, null);
sql = string.Join(" ", queries.ToArray());
}
return(sql);
}
public string ToString(bool addExecutionTime)
{
if (Parent.Updated || Updated)
{
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM ");
sb.Append(GXDbHelpers.GetTableName(Table, true, Parent.Settings.TableQuotation, Parent.Settings.TablePrefix));
string str = Where.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(" ");
sb.Append(str);
}
Updated = false;
return(sb.ToString());
}
return(string.Empty);
}
public static GXDeleteArgs DeleteById <T>(object id)
{
if (id == null)
{
throw new ArgumentNullException("Invalid Id.");
}
GXDeleteArgs arg = DeleteAll <T>();
GXSerializedItem si = GXSqlBuilder.FindUnique(typeof(T));
if (si == null)
{
throw new Exception("DeleteById failed. Class is not derived from IUnique.");
}
string name = GXDbHelpers.GetColumnName(si.Target as PropertyInfo, '\0');
arg.Where.Or <IUnique <T> >(q => name.Equals(id));
return(arg);
}
/// <summary>
/// Get as name.
/// </summary>
/// <param name="joinList"></param>
/// <param name="type"></param>
/// <returns></returns>
/// <remarks>
/// As name is used if:
/// 1. Data from different classes is saved to same table.
/// 2. Several classes are using same class.
/// 3. User is defined it using Alias attribute.
/// </remarks>
internal static string GetAsName(List <GXJoin> joinList, Type type, bool selectUsingAs)
{
//Data from different classes is saved to same table.
if (selectUsingAs || GXDbHelpers.IsSharedTable(type))
{
string name = GXDbHelpers.OriginalTableName(type);
int cnt = 0;
GXJoin target = null;
foreach (GXJoin it in joinList)
{
if (type == it.Table1Type)
{
target = it;
}
else if (it.Alias2 == name || it.Table2 == name)
{
++cnt;
//If we have more than one class that are sharing same table.
if (cnt != 1)
{
break;
}
}
}
if (cnt > 1)
{
name = GXDbHelpers.GetTableName(type, false, '\0', null, false);
if (target != null)
{
target.Alias2 = name;
}
return(name);
}
if (selectUsingAs)
{
return(name);
}
}
if (GXDbHelpers.IsAliasName(type))
{
return(GXDbHelpers.OriginalTableName(type));
}
return(null);
}
internal static void UpdateJoins(GXDBSettings settings, GXJoinCollection list, List <GXJoin> joins)
{
char separtor = settings.ColumnQuotation;
bool allowNull;
MemberExpression me;
foreach (KeyValuePair <JoinType, BinaryExpression> it in list.List)
{
GXJoin join = new GXJoin();
join.Type = it.Key;
me = GetMemberExpression(it.Value.Left, out allowNull);
MemberInfo m = me.Member;
Expression e = me.Expression;
join.Column1 = GXDbHelpers.GetColumnName(m, separtor);
join.AllowNull1 = allowNull;
m = GetMemberExpression(it.Value.Right, out allowNull).Member;
join.Column2 = GXDbHelpers.GetColumnName(m, separtor);
join.AllowNull2 = allowNull;
join.UpdateTables(e.Type, m.DeclaringType);
joins.Add(join);
}
}
internal static void OrderByToString(GXSelectArgs parent, StringBuilder sb, List <GXOrder> OrderList, List <GXJoin> joinList)
{
bool first = true;
if (OrderList.Count != 0)
{
sb.Append(" ORDER BY ");
first = true;
foreach (GXOrder it in OrderList)
{
if (first)
{
first = false;
}
else
{
sb.Append(", ");
}
if (parent.Settings.Type == DatabaseType.MSSQL && parent.Count != 0)
{
string table = GXDbHelpers.GetTableName(it.Table, true, '\0', parent.Settings.TablePrefix);
sb.Append(GXDbHelpers.AddQuotes(table + '.' + it.Column, parent.Settings.ColumnQuotation));
continue;
}
//Add table name always until there is a way to check are multiple tables used. if (joinList.Count != 0)
{
string table = GXDbHelpers.GetTableName(it.Table, true, parent.Settings.TableQuotation, parent.Settings.TablePrefix);
sb.Append(table);
sb.Append('.');
}
sb.Append(GXDbHelpers.AddQuotes(it.Column, parent.Settings.ColumnQuotation));
}
if (parent.Descending)
{
sb.Append(" DESC");
}
}
}
internal static void GroupByToString(GXSelectArgs parent, StringBuilder sb, List <GXOrder> groupList, List <GXJoin> joinList)
{
if (groupList.Count != 0)
{
sb.Append(" GROUP BY ");
bool first = true;
foreach (GXOrder it in groupList)
{
if (first)
{
first = false;
}
else
{
sb.Append(", ");
}
//Table name is not added if there is only one table.
if (joinList.Count != 0)
{
if (parent.Settings.Type == DatabaseType.MSSQL && parent.Count != 0)
{
string table = GXDbHelpers.GetTableName(it.Table, true, '\0', parent.Settings.TablePrefix);
sb.Append(table);
sb.Append('.');
}
else
{
string table = GXDbHelpers.GetTableName(it.Table, true, parent.Settings.TableQuotation, parent.Settings.TablePrefix);
sb.Append(table);
sb.Append('.');
}
}
sb.Append(GXDbHelpers.AddQuotes(it.Column, parent.Settings.ColumnQuotation));
}
}
}
public override string ToString()
{
if (Parent.Updated || Updated)
{
ColumnList.Clear();
List <GXJoin> joinList = new List <GXJoin>();
GXOrderByCollection.UpdateJoins(Parent.Settings, Joins, joinList);
string[] list;
StringBuilder sb = new StringBuilder();
//Get columns.
Dictionary <string, GXSerializedItem> properties;
Dictionary <Type, GXSerializedItem> neededTables = new Dictionary <Type, GXSerializedItem>();
foreach (var it in List)
{
//No relations.
if (!neededTables.ContainsKey(it.Parameters[0].Type))
{
neededTables.Add(it.Parameters[0].Type, null);
}
list = GXDbHelpers.GetMembers(Parent.Settings, it, '\0', false);
foreach (var it2 in list)
{
properties = GXSqlBuilder.GetProperties(it.Parameters[0].Type);
if (it2 != "*" && ColumnList.ContainsKey(it.Parameters[0].Type))
{
GXSerializedItem si = properties[it2];
if (si.Relation != null)
{
//Get properties.
GetColumns(si.Relation.ForeignTable, ColumnList, neededTables);
}
if (si.Relation == null || si.Relation.RelationType != RelationType.ManyToMany)
{
ColumnList[it.Parameters[0].Type].Add(it2);
}
}
else
{
if (it2 == "*")
{
GetColumns(it.Parameters[0].Type, ColumnList, neededTables);
}
else
{
if (neededTables.ContainsKey(it.Parameters[0].Type))
{
neededTables.Remove(it.Parameters[0].Type);
}
List <string> columns2 = new List <string>();
columns2.Add(it2);
ColumnList.Add(it.Parameters[0].Type, columns2);
if (properties.ContainsKey(it2))
{
GXSerializedItem si = properties[it2];
if (si.Relation != null)
{
//Get properties.
GetColumns(si.Relation.ForeignTable, ColumnList, neededTables);
}
}
}
}
}
}
foreach (var it in ColumnList)
{
foreach (KeyValuePair <Type, LambdaExpression> x in Excluded)
{
if (x.Key == it.Key)
{
string[] removed = GXDbHelpers.GetMembers(null, x.Value, '\0', false);
foreach (string col in removed)
{
it.Value.Remove(col);
}
}
}
}
SelectToString(Parent.Settings, sb, Parent.Distinct, ColumnList, joinList, Parent.Index, Parent.Count);
sql = sb.ToString();
Updated = false;
}
return(sql);
}
/// <summary>
/// Get table name.
/// </summary>
/// <param name="type">Table type.</param>
/// <param name="addQuoteSeparator">Is quote separator added.</param>
/// <param name="allowSharedTables"></param>
/// <returns>Table name.</returns>
internal string GetTableName(Type type, bool addQuoteSeparator, bool allowSharedTables)
{
return(GXDbHelpers.GetTableName(type, addQuoteSeparator, Settings.TableQuotation, Settings.TablePrefix, allowSharedTables));
}
internal string ToString(ref string post)
{
if (Parent.Updated || Updated)
{
ColumnList.Clear();
List <GXJoin> joinList = new List <GXJoin>();
GXOrderByCollection.UpdateJoins(Parent.Settings, Joins, joinList);
string[] list;
StringBuilder sb = new StringBuilder();
//Get columns.
Dictionary <string, GXSerializedItem> properties;
Dictionary <Type, GXSerializedItem> neededTables = new Dictionary <Type, GXSerializedItem>();
foreach (var it in List)
{
//No relations.
if (!neededTables.ContainsKey(it.Key.Parameters[0].Type))
{
neededTables.Add(it.Key.Parameters[0].Type, null);
}
list = GXDbHelpers.GetMembers(Parent.Settings, it.Key, '\0', false, ref post);
foreach (var it2 in list)
{
properties = GXSqlBuilder.GetProperties(it.Key.Parameters[0].Type);
if (it2 != "*" && ColumnList.ContainsKey(it.Key.Parameters[0].Type))
{
if (properties.ContainsKey(it2))
{
GXSerializedItem si = properties[it2];
if (si.Relation != null)
{
//Get properties.
GetColumns(si.Relation.ForeignTable, ColumnList, neededTables);
}
if (si.Relation == null || si.Relation.RelationType != RelationType.ManyToMany)
{
ColumnList[it.Key.Parameters[0].Type].Add(it2);
}
}
else
{
string str = it2;
if (it.Value != null)
{
string[] tmp = GXDbHelpers.GetMembers(Parent.Settings, it.Value, '\0', false, ref post);
str += " AS " + tmp[0];
}
ColumnList[it.Key.Parameters[0].Type].Add(str);
}
}
else
{
if (it2 == "*")
{
GetColumns(it.Key.Parameters[0].Type, ColumnList, neededTables);
}
else
{
if (neededTables.ContainsKey(it.Key.Parameters[0].Type))
{
neededTables.Remove(it.Key.Parameters[0].Type);
}
List <string> columns2 = new List <string>();
columns2.Add(it2);
ColumnList.Add(it.Key.Parameters[0].Type, columns2);
if (properties.ContainsKey(it2))
{
GXSerializedItem si = properties[it2];
if (si.Relation != null)
{
//Get properties.
GetColumns(si.Relation.ForeignTable, ColumnList, neededTables);
}
}
}
}
}
}
foreach (var it in ColumnList)
{
foreach (KeyValuePair <Type, LambdaExpression> x in Excluded)
{
if (x.Key == it.Key)
{
string[] removed = GXDbHelpers.GetMembers(null, x.Value, '\0', false, ref post);
foreach (string col in removed)
{
bool includeQuery = false;
string col2 = GXDbHelpers.AddQuotes(col, Parent.Settings.ColumnQuotation);
//Joins are not removed from the qyery or 1:1 doesn't work.
foreach (var j in joinList)
{
if ((it.Key == j.Table1Type && j.Column1 == col2) ||
(it.Key == j.Table2Type && j.Column2 == col2))
{
includeQuery = true;
break;
}
}
if (!includeQuery)
{
it.Value.Remove(col);
}
}
}
}
}
SelectToString(Parent.Settings, sb, Parent.Distinct, ColumnList, joinList, Parent.Index, Parent.Count, post);
sql = sb.ToString();
Updated = false;
}
return(sql);
}
private void SelectToString(GXDBSettings settings, StringBuilder sb, bool distinct,
Dictionary <Type, List <string> > columnList, List <GXJoin> joinList, UInt32 index, UInt32 count)
{
Dictionary <Type, string> asTable = new Dictionary <Type, string>();
string name;
foreach (var it in columnList)
{
name = GetAsName(joinList, it.Key, settings.SelectUsingAs);
if (name != null)
{
asTable.Add(it.Key, name);
}
}
sb.Append("SELECT ");
if (distinct)
{
sb.Append("DISTINCT ");
}
if (index != 0 || count != 0)
{
if (index != 0 && count == 0)
{
throw new ArgumentOutOfRangeException("Count can't be zero if index is given.");
}
if (index != 0)
{
if (settings.LimitType == LimitType.Top)
{
sb.Length = 0;
sb.Append("SELECT * FROM (SELECT TOP ");
sb.Append(count);
sb.Append(" GX.* FROM (");
sb.Append("SELECT ");
if (distinct)
{
sb.Append("DISTINCT ");
}
sb.Append("TOP ");
sb.Append(index + count);
sb.Append(" ");
}
}
else
{
if (settings.LimitType == LimitType.Top)
{
sb.Append("TOP ");
sb.Append(count);
sb.Append(" ");
}
}
}
string tableAs, table;
bool first = true;
foreach (var it in columnList)
{
if (asTable.ContainsKey(it.Key))
{
tableAs = asTable[it.Key];
}
else
{
tableAs = null;
}
if (GXDbHelpers.IsSharedTable(it.Key) || GXDbHelpers.IsAliasName(it.Key))
{
table = GXDbHelpers.AddQuotes(GXDbHelpers.OriginalTableName(it.Key), settings.TableQuotation);
}
else
{
table = GXDbHelpers.GetTableName(it.Key, true, settings.TableQuotation, settings.TablePrefix);
}
foreach (var col in it.Value)
{
name = null;
if (first)
{
first = false;
}
else
{
sb.Append(", ");
}
//Check is this method.
int pos = col.LastIndexOf('(');
//Table name is not added if only one table.
if (pos == -1 && joinList.Count != 0)
{
if (asTable.Count > 1 && tableAs != null)
{
sb.Append(tableAs);
}
else
{
sb.Append(table);
}
sb.Append(".");
}
if (pos == -1) //If table field.
{
if (col == "*")
{
sb.Append(col);
}
else
{
name = GXDbHelpers.AddQuotes(col, settings.ColumnQuotation);
sb.Append(name);
}
if (tableAs != null)
{
sb.Append(" AS ");
name = GXDbHelpers.AddQuotes(tableAs + "." + col, settings.ColumnQuotation);
sb.Append(name);
}
else if (settings.SelectUsingAs && index == 0)
{
sb.Append(" AS ");
name = GXDbHelpers.AddQuotes(tableAs + "." + col, settings.ColumnQuotation);
sb.Append(name);
}
}
else //If method like COUNT(*)
{
sb.Append(col);
if (settings.SelectUsingAs && index == 0)
{
sb.Append(" AS ");
name = GXDbHelpers.AddQuotes(tableAs + "." + col.Substring(0, pos), settings.ColumnQuotation);
sb.Append(name);
}
}
if (Maps.ContainsKey(col))
{
sb.Append(" AS ");
sb.Append(GXDbHelpers.AddQuotes(Maps[col], settings.ColumnQuotation));
}
}
}
if (columnList.Count == 0)
{
sb.Append("*");
}
if (joinList.Count == 0)
{
sb.Append(" FROM ");
first = true;
foreach (var it in columnList)
{
if (first)
{
first = false;
}
else
{
sb.Append(", ");
}
sb.Append(GXDbHelpers.GetTableName(it.Key, settings.UseQuotationWhereColumns, settings.TableQuotation, settings.TablePrefix));
if (asTable.ContainsKey(it.Key))
{
sb.Append(" ");
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Key], settings.TableQuotation));
}
}
}
else
{
//If we are adding relation to same table more than once.
Dictionary <string, int> list = new Dictionary <string, int>();
sb.Append(" FROM ");
first = true;
if (joinList.Count != 1)
{
for (int pos = 0; pos < joinList.Count; ++pos)
{
sb.Append("(");
}
}
foreach (var it in joinList)
{
if (first)
{
sb.Append(GXDbHelpers.AddQuotes(it.Table1, settings.TableQuotation));
if (asTable.ContainsKey(it.Table1Type))
{
sb.Append(" ");
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table1Type], settings.TableQuotation));
}
else
{
if (it.Alias1 != null)
{
sb.Append(" ");
sb.Append(it.Alias1);
}
}
first = false;
}
switch (it.Type)
{
case JoinType.Inner:
sb.Append(" INNER JOIN ");
break;
case JoinType.Left:
sb.Append(" LEFT OUTER JOIN ");
break;
case JoinType.Right:
sb.Append(" RIGHT OUTER JOIN ");
break;
default:
throw new ArgumentOutOfRangeException("Invalid join type.");
}
sb.Append(GXDbHelpers.AddQuotes(it.Table2, settings.TableQuotation));
//Add alias if used and it is not same as table name.
if (asTable.ContainsKey(it.Table2Type))
{
sb.Append(" ");
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table2Type], settings.TableQuotation));
}
sb.Append(" ON ");
if (asTable.ContainsKey(it.Table1Type))
{
sb.Append(" ");
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table1Type], settings.TableQuotation));
}
else
{
sb.Append(GXDbHelpers.AddQuotes(it.Table1, settings.TableQuotation));
}
sb.Append('.');
sb.Append(it.Column1);
sb.Append('=');
//Add alias if used.
if (asTable.ContainsKey(it.Table2Type))
{
//sb.Append(it.Table2);
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table2Type], settings.TableQuotation));
}
else
{
sb.Append(GXDbHelpers.AddQuotes(it.Table2, settings.TableQuotation));
if (it.Alias2 == null && it.Index != 0)
{
sb.Append(it.Index);
}
}
sb.Append('.');
sb.Append(it.Column2);
if (it.AllowNull1)
{
sb.Append(" OR ");
//Add alias if used.
if (asTable.ContainsKey(it.Table1Type))
{
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table1Type], settings.TableQuotation));
}
else
{
sb.Append(GXDbHelpers.AddQuotes(it.Table1, settings.TableQuotation));
}
sb.Append('.');
sb.Append(it.Column1);
sb.Append(" IS NULL");
}
if (it.AllowNull2)
{
sb.Append(" OR ");
//Add alias if used.
if (asTable.ContainsKey(it.Table2Type))
{
sb.Append(GXDbHelpers.AddQuotes(asTable[it.Table2Type], settings.TableQuotation));
}
else
{
sb.Append(GXDbHelpers.AddQuotes(it.Table2, settings.TableQuotation));
}
sb.Append('.');
sb.Append(it.Column2);
sb.Append(" IS NULL");
}
if (joinList.Count != 1)
{
sb.Append(")");
}
}
}
if (index != 0)
{
if (settings.LimitType == LimitType.Top)
{
List <Type> tables = new List <Type>();
foreach (var it in columnList)
{
if (!tables.Contains(it.Key))
{
tables.Add(it.Key);
}
}
GXSerializedItem si = null;
foreach (var it in tables)
{
if ((si = GXSqlBuilder.FindUnique(it)) != null)
{
break;
}
}
string id;
//Add alias if used.
if (asTable.ContainsKey((si.Target as PropertyInfo).ReflectedType))
{
id = GXDbHelpers.AddQuotes(asTable[(si.Target as PropertyInfo).ReflectedType] + "." + GXDbHelpers.GetColumnName(si.Target as PropertyInfo, '\0'), settings.TableQuotation);
}
else
{
id = GXDbHelpers.GetColumnName(si.Target as PropertyInfo, '\0');
}
sb.Append(string.Format(" ORDER BY {0}) AS GX ORDER BY GX.{0} DESC) AS GX2", id));
}
}
}
private void GetRelations(Type type, List <GXJoin> joinList, List <Type> tables,
Dictionary <Type, List <string> > columns, bool getRelations)
{
if (!tables.Contains(type))
{
//TODO: lisää shared table attribute.
bool ignorebaseType = false;
foreach (var it in tables)
{
if (GXDbHelpers.IsSharedTable(it) && type.IsAssignableFrom(it.BaseType))
{
ignorebaseType = true;
break;
}
}
if (ignorebaseType)
{
return;
}
tables.Add(type);
Type tp;
bool added;
List <string> excluded = null;
List <string> cols = null;
string tableName = null;
if (columns != null && columns.ContainsKey(type))
{
cols = columns[type];
if (cols.Contains("*"))
{
cols = null;
}
}
Dictionary <string, GXSerializedItem> properties = GXSqlBuilder.GetProperties(type);
foreach (var it in properties)
{
if (cols != null && !cols.Contains(it.Key))
{
continue;
}
if (!IsExcluded(it.Key, excluded))
{
if (it.Value.Relation != null && getRelations)
{
GXJoin j = new GXJoin();
tp = it.Value.Relation.PrimaryTable;
j.Table1Type = tp;
bool shared = GXDbHelpers.IsSharedTable(tp);
if (shared || GXDbHelpers.IsAliasName(tp))
{
j.Alias1 = GXDbHelpers.OriginalTableName(tp);
}
j.Table2Type = it.Value.Relation.ForeignTable;
j.UpdateTables(tp, it.Value.Relation.ForeignTable);
//If nullable.
Type tp2 = it.Value.Relation.ForeignId.Type;
j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
if (!j.AllowNull1)
{
tp2 = it.Value.Relation.PrimaryId.Type;
j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) &&
tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
}
if (GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) ||
GXDbHelpers.IsAliasName(it.Value.Relation.ForeignTable))
{
j.Alias2 = GXDbHelpers.OriginalTableName(it.Value.Relation.ForeignTable);
}
if (it.Value.Relation.RelationType == RelationType.OneToOne ||
it.Value.Relation.RelationType == RelationType.Relation)
{
j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
if (tables.Contains(it.Value.Relation.ForeignTable))
{
continue;
}
//If nullable.
tp2 = it.Value.Relation.PrimaryId.Relation.ForeignId.Type;
j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
if (!j.AllowNull1)
{
tp2 = it.Value.Relation.PrimaryId.Relation.PrimaryId.Type;
j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) &&
tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
}
}
else if (it.Value.Relation.RelationType == RelationType.OneToMany)
{
j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.PrimaryId.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
//If nullable.
tp2 = it.Value.Relation.ForeignId.Type;
j.AllowNull1 = !shared && tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
if (!j.AllowNull1)
{
tp2 = it.Value.Relation.PrimaryId.Type;
j.AllowNull2 = !GXDbHelpers.IsSharedTable(it.Value.Relation.ForeignTable) &&
tp2.IsGenericType && tp2.GetGenericTypeDefinition() == typeof(Nullable <>);
}
}
else if (it.Value.Relation.RelationType == RelationType.ManyToMany)
{
j.Table2Type = it.Value.Relation.RelationMapTable.Relation.PrimaryTable;
j.UpdateTables(tp, it.Value.Relation.RelationMapTable.Relation.PrimaryTable);
j.Column2 = GXDbHelpers.GetColumnName(GXSqlBuilder.FindRelation(it.Value.Relation.RelationMapTable.Relation.PrimaryTable, tp).Target as PropertyInfo, Parent.Settings.ColumnQuotation);
j.Column1 = GXDbHelpers.GetColumnName(GXSqlBuilder.FindUnique(tp).Target as PropertyInfo, Parent.Settings.ColumnQuotation);
added = false;
//Check that join is not added already.
string j1 = j.Table1;
string j2 = j.Table2;
foreach (var it4 in joinList)
{
string t1 = it4.Table1;
string t2 = it4.Table2;
if ((j1 == t1 && j2 == t2) || (j2 == t1 && j1 == t2))
{
added = true;
break;
}
}
if (!added)
{
joinList.Add(j);
j = new GXJoin();
Type tmp = it.Value.Type;
j.AllowNull1 = !!GXDbHelpers.IsSharedTable(tmp) && tmp.IsGenericType && tmp.GetGenericTypeDefinition() == typeof(Nullable <>);
j.UpdateTables(it.Value.Relation.RelationMapTable.Relation.PrimaryTable, it.Value.Relation.ForeignTable);
j.Column1 = GXDbHelpers.GetColumnName(it.Value.Relation.RelationMapTable.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
j.Column2 = GXDbHelpers.GetColumnName(it.Value.Relation.ForeignId.Target as PropertyInfo, Parent.Settings.ColumnQuotation);
joinList.Add(j);
tables.Add(it.Value.Relation.RelationMapTable.Relation.PrimaryTable);
if (cols != null)
{
tables.Add(it.Value.Relation.ForeignTable);
}
}
j = null;
}
if (j != null)
{
//Check that join is not added already.
added = false;
string j1 = j.Table1;
string j2 = j.Table2;
int index = -1;
foreach (var it4 in joinList)
{
string t1 = it4.Table1;
string t2 = it4.Table2;
if ((j1 == t1 && j2 == t2) || (j2 == t1 && j1 == t2))
{
added = true;
break;
}
if (j.Alias2 != null && it4.Alias2 == j.Alias2)
{
++index;
}
}
//If we have multiple referenced to same table.
if (index != -1)
{
j.Index = index + 1;
}
if (!added)
{
joinList.Add(j);
}
}
if (cols == null || cols.Contains(it.Key))
{
GetRelations(it.Value.Relation.ForeignTable, joinList, tables, columns, getRelations);
}
}
}
else
{
System.Diagnostics.Debug.WriteLine("Column " + tableName + "." + it.Key + " Skipped.");
}
}
}
}
/// <summary>
/// Update DB relations.
/// </summary>
/// <param name="mainType"></param>
/// <param name="s"></param>
private static void UpdateRelations(Type mainType, GXSerializedItem s, bool primaryData, Dictionary <Type, GXRelationTable> relationTable)
{
Type type;
if (primaryData)
{
s.Relation = new GXRelationTable();
s.Relation.Column = s;
s.Relation.PrimaryTable = mainType;
s.Relation.PrimaryId = s;
if ((s.Attributes & Attributes.ForeignKey) != 0)
{
ForeignKeyAttribute[] fks = ((ForeignKeyAttribute[])(s.Target as PropertyInfo).GetCustomAttributes(typeof(ForeignKeyAttribute), false));
ForeignKeyAttribute fk;
if (fks.Length == 0)
{
fk = null;
type = null;
}
else
{
fk = fks[0];
type = fk.Type;
}
//If type is not give in ForeignKeyAttribute.
if (type == null)
{
type = s.Type;
if (typeof(IEnumerable).IsAssignableFrom(type))
{
type = GXInternal.GetPropertyType(type);
}
}
s.Relation.ForeignTable = type;
if (fk != null && fk.MapTable != null)
{
s.Relation.RelationType = RelationType.ManyToMany;
}
else if (s.Type != typeof(string) && typeof(System.Collections.IEnumerable).IsAssignableFrom(s.Type))
{
s.Relation.RelationType = RelationType.OneToMany;
s.Relation.PrimaryId = GXSqlBuilder.FindRelation(type, mainType);
if (s.Relation.PrimaryId == null)
{
throw new Exception(string.Format("Relation create failed. Foreign table '{0}' do not have relation to table '{1}'.",
GXDbHelpers.GetTableName(type, false, null),
GXDbHelpers.OriginalTableName(mainType)));
}
}
else
{
s.Relation.RelationType = RelationType.OneToOne;
}
}
else if ((s.Attributes & Attributes.Relation) != 0)
{
RelationAttribute ra = ((RelationAttribute[])(s.Target as PropertyInfo).GetCustomAttributes(typeof(RelationAttribute), false))[0];
type = ra.Target;
if (type == null)
{
type = s.Type;
}
if (typeof(IEnumerable).IsAssignableFrom(type))
{
type = GXInternal.GetPropertyType(type);
}
s.Relation.ForeignTable = type;
}
}
else
{
if ((s.Attributes & Attributes.ForeignKey) != 0)
{
ForeignKeyAttribute[] fks = ((ForeignKeyAttribute[])(s.Target as PropertyInfo).GetCustomAttributes(typeof(ForeignKeyAttribute), false));
ForeignKeyAttribute fk;
if (fks.Length == 0)
{
fk = null;
type = null;
}
else
{
fk = fks[0];
type = fk.Type;
}
//If type is not give in ForeignKeyAttribute.
if (type == null)
{
type = s.Type;
if (typeof(IEnumerable).IsAssignableFrom(type))
{
type = GXInternal.GetPropertyType(type);
}
}
GXSerializedItem secondary = FindUnique(type);
if (secondary == null)
{
throw new Exception(string.Format("Table {0} Relation create failed. Class must be derived from IUnique or target type must set in ForeignKey or Relation attribute.",
GXDbHelpers.GetTableName(mainType, true, '\'', null)));
}
s.Relation.ForeignId = secondary;
//Update relation map fields.
if (fk != null && fk.MapTable != null)
{
foreach (var it in GetProperties(fk.MapTable))
{
if ((it.Value.Attributes & Attributes.ForeignKey) != 0 &&
s.Relation.ForeignTable == it.Value.Relation.ForeignTable)
{
s.Relation.RelationMapTable = it.Value;
break;
}
}
}
}
else if ((s.Attributes & Attributes.Relation) != 0)
{
RelationAttribute ra = ((RelationAttribute[])(s.Target as PropertyInfo).GetCustomAttributes(typeof(RelationAttribute), false))[0];
type = ra.Target;
if (type == null)
{
type = s.Type;
}
if (typeof(IEnumerable).IsAssignableFrom(type))
{
type = GXInternal.GetPropertyType(type);
}
GXSerializedItem secondary = FindUnique(type);
if (secondary == null)
{
throw new Exception(string.Format("Table {0} Relation create failed. Class must be derived from IUnique or target type must set in ForeignKey or Relation attribute.",
GXDbHelpers.GetTableName(mainType, true, '\'', null)));
}
s.Relation.ForeignId = secondary;
}
}
}
