예제 #1
0
        /// <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;" });
        }
예제 #2
0
        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");
                }
            }
        }
예제 #3
0
 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));
         }
     }
 }
예제 #4
0
        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));
                }
            }
        }
예제 #5
0
        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);
        }