예제 #1
0
        public HashSet <DBIndex> Load(Tabloid tabloid)
        {
            HashSet <DBIndex> result = new HashSet <DBIndex>();

            using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_index(_db, tabloid.Name)))
            {
                while (reader.Read())
                {
                    result.Add(new DBIndex(_db)
                    {
                        Table    = (DBTable)tabloid,
                        Name     = (string)reader["name"],
                        isUnique = Convert.ToBoolean(reader["is_unique"])
                    });
                }
            }

            foreach (DBIndex index in result)
            {
                using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_COLUMNS_index(_db, tabloid.Name, index.Name)))
                {
                    while (reader.Read())
                    {
                        index.Columns.Add(tabloid.Columns.SingleOrDefault(c => c.Name == (string)reader["ColumnName"]).Name);
                    }
                }
            }

            return(result);
        }
예제 #2
0
 public ListJson <DBItem> ExecuteRead(IDbCommand command, Tabloid tabloid)
 {
     using (DBReader reader = ExecuteCommand(command))
     {
         ListJson <DBItem> result = Read(reader, tabloid);
         return(result);
     }
 }
예제 #3
0
        public DBItem(DBConnection db, Tabloid tabloid = null, Dictionary <string, object> dict = null)
        {
            _db          = db;
            _tabloid     = tabloid;
            _properties  = new Dictionary <string, object>();
            _foreignKeys = new Dictionary <string, object>();

            if (dict != null)
            {
                foreach (var pair in dict)
                {
                    this[pair.Key] = pair.Value;
                }
            }
        }
예제 #4
0
        public HashSet <DBColumn> Load(Tabloid Tabloid)
        {
            // in cache
            string tabloidRealName = _db.CommandSet.ToRealTableName(_db.Application, Tabloid.Name);

            if (Cache.ContainsKey(tabloidRealName))
            {
                return(Cache[tabloidRealName]);
            }

            HashSet <DBColumn> result = new HashSet <DBColumn>();

            using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_column(_db, Tabloid.Name)))
            {
                while (reader.Read())
                {
                    string defaultValue = null;
                    if (reader["default"] != DBNull.Value)
                    {
                        defaultValue = Convert.ToString(reader["default"]).Trim(new char[] { '(', ')' }); // removes ('value')
                        if ((!defaultValue.StartsWith("'") || !defaultValue.EndsWith("'")) && defaultValue.ToUpper() != "NULL")
                        {
                            defaultValue = $"'{defaultValue}'";
                        }
                    }

                    result.Add(new DBColumn(_db)
                    {
                        Tabloid      = Tabloid,
                        Name         = (string)reader["name"],
                        Type         = DataType.FromDBName((string)reader["typeName"], _db.Type),
                        MaxLength    = reader["max_length"] != DBNull.Value ? Convert.ToInt32(reader["max_length"]) : -1,
                        IsNullable   = Convert.ToBoolean(reader["is_nullable"]),
                        IsUnique     = Convert.ToBoolean(reader["is_unique"] != DBNull.Value ? reader["is_unique"] : false) || (string)reader["name"] == DBCommandSet.PrimaryKey,
                        DefaultValue = defaultValue
                                       //,Scale = Convert.ToInt32(reader["scale"])
                    });
                }
            }

            Cache.Add(tabloidRealName, result);

            return(result);
        }
예제 #5
0
        public ListJson <DBItem> Read(DBReader reader, Tabloid tabloid)
        {
            ListJson <DBItem> items = new ListJson <DBItem>();

            while (reader.Read())
            {
                DBItem newItem = new DBItem(this, tabloid);

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    string columnName = reader.GetName(i);
                    newItem[columnName] = reader[columnName];
                }

                items.Add(newItem);
            }

            return(items);
        }
예제 #6
0
        public virtual IDbCommand SELECT_count(DBConnection db, Tabloid table, Manager <Condition> conditions = null, Manager <Join> joins = null, GroupBy groupBy = null)
        {
            IDbCommand command = Command;

            string conditionString = conditions != null && conditions.Any()
                ? $"WHERE {conditions.ToSql(this, command)}"
                : null;
            string groupByString = groupBy != null
                ? ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, table.Name, groupBy.Columns), false, true)
                : null;

            string tableString = groupBy != null
                ? $"(SELECT {groupByString} FROM {ToRealTableName(db.Application, table.Name)} GROUP BY {groupByString}) _table1"
                : ToRealTableName(db.Application, table.Name);

            command.CommandText = groupBy != null
                ? $"SELECT Count(*) count FROM (SELECT {groupByString} FROM {ToRealTableName(db.Application, table.Name)} {joins.ToSql(this, command)} {conditionString} GROUP BY {groupByString}) a"
                : $"SELECT Count(*) count FROM {ToRealTableName(db.Application, table.Name)} {joins.ToSql(this, command)} {conditionString}";

            return(command);
        }
예제 #7
0
        public HashSet <DBForeignKey> Load(Tabloid tabloid)
        {
            HashSet <DBForeignKey> result = new HashSet <DBForeignKey>();

            using (DBReader reader = _db.ExecuteCommand(_db.CommandSet.LIST_foreignKey(_db, tabloid.Name, true, false)))
            {
                while (reader.Read())
                {
                    result.Add(new DBForeignKey(_db)
                    {
                        SourceTable  = (tabloid as DBTable),
                        SourceColumn = (string)reader["sourceColumn"],
                        TargetTable  = _db.Table(_db.CommandSet.FromRealTableName(_db.Application, (string)reader["targetTable"])),
                        TargetColumn = (string)reader["targetColumn"],
                        OnDelete     = (string)reader["onDelete"],
                        OnUpdate     = (string)reader["onUpdate"]
                    });
                }
            }

            return(result);
        }
예제 #8
0
        public override IDbCommand SELECT(DBConnection db, Tabloid tabloid, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null)
        {
            MySqlCommand  command   = new MySqlCommand();
            List <string> withTable = new List <string>();

            /// get columns to select
            List <Tuple <string, string> > columns = new List <Tuple <string, string> >();

            // column are set
            if (columnNames != null && columnNames.Any())
            {
                columns.AddRange(ColumnsToTuple(db.Application, tabloid.Name, columnNames));
            }
            // all columns
            else
            {
                // origin table
                columns.AddRange(tabloid.Columns.Select(c => new Tuple <string, string>(tabloid.Name, c.Name)));
                // table not found - every table have some column
                if (columns.Count == 0)
                {
                    throw new Exception($"Tabulka/View [{tabloid.Name}]({tabloid.RealName}) nenalezena!");
                }
                // joined tables
                foreach (Join join in joins)
                {
                    columns.AddRange(new Tabloid(db)
                    {
                        Name = join.joinTableName
                    }.Columns.Select(c => new Tuple <string, string>(join.joinTableName, c.Name)));
                }
            }
            List <Tuple <string, string> > whereColumns = new List <Tuple <string, string> >(columns);

            foreach (Condition condition in conditions)
            {
                Tuple <string, string> tuple = ColumnsToTuple(db.Application, tabloid.Name, new List <string> {
                    condition.column
                }).First();
                if (!whereColumns.Any(wc => wc.Item1.ToLower() == tuple.Item1.ToLower() && wc.Item2.ToLower() == tuple.Item2.ToLower()))
                {
                    whereColumns.Add(tuple);
                }
            }

            /// SELECT FROM, JOIN
            command.CommandText =
                $"SELECT {ColumnTuplesToString(db.Application, whereColumns)} FROM {ToRealTableName(db.Application, tabloid.Name)} {joins.ToSql(this, command)}";

            /// WHERE, GROUP, HAVING
            string conditionString = conditions.ToSql(this, command);

            if (!string.IsNullOrEmpty(conditionString) || groupBy != null)
            {
                withTable.Add($"__table1 as ({command.CommandText})");

                // WHERE
                if (!string.IsNullOrEmpty(conditionString))
                {
                    conditionString =
                        $"WHERE {conditionString}";

                    if (groupBy == null)
                    {
                        command.CommandText =
                            $"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString}";
                    }
                }

                // GROUP BY
                if (groupBy != null)
                {
                    string havingString = groupBy.Having.ToSql(this, command);
                    if (!string.IsNullOrEmpty(havingString))
                    {
                        havingString = $"HAVING {havingString}";
                    }

                    if (groupBy.Function == ESqlFunction.none)
                    {
                        columns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);

                        command.CommandText =
                            $"SELECT {ColumnTuplesToString(db.Application, columns, true, false)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, columns, true, false)} {havingString}";
                    }
                    // first, last -> inner query
                    else if (groupBy.Function.NeedsInnerQuery())
                    {
                        command.CommandText =
                            $"SELECT * FROM __table1 WHERE {conditionString} {(string.IsNullOrEmpty(conditionString) ? "" : "AND")} {FullPrimaryKey(tabloid.Name)} IN (SELECT {(groupBy.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM __table1 GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString})";
                    }
                    // other functions
                    else
                    {
                        // get only numeric columns
                        if (groupBy.Function.RequireNumeric())
                        {
                            List <Tuple <string, string> > currentColumns = ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns);
                            foreach (string tabloidName in columns.Select(c => c.Item1).Distinct())
                            {
                                Tabloid currentTabloid = tabloidName == tabloid.Name
                                    ? tabloid
                                    : new Tabloid(db)
                                {
                                    Name = tabloidName
                                };

                                currentColumns = currentColumns.Concat(ColumnsToTuple(db.Application, currentTabloid.Name, currentTabloid.Columns.Where(c => columns.Contains(new Tuple <string, string>(currentTabloid.Name, c.Name)) && DataType.IsNumeric(c.Type)).Select(c => c.Name))).ToList();
                            }

                            columns = currentColumns;
                        }

                        command.CommandText =
                            $"SELECT {ColumnTuplesToString(db.Application, columns, true, false, groupBy.Function.ToString(), groupBy.Columns)} FROM __table1 {conditionString} GROUP BY {ColumnTuplesToString(db.Application, ColumnsToTuple(db.Application, tabloid.Name, groupBy.Columns), true, false)} {havingString}";
                    }
                }
            }

            /// Drop step
            if (dropStep != null)
            {
                if (dropStep.Order == null)
                {
                    throw new ArgumentException("Order needs to be filled");
                }

                withTable.Add($"__table2 as ({command.CommandText})");
                string rowNum = $"FLOOR((@__ROWNUM:=@__ROWNUM+1)*{dropStep.FinalCount}/(SELECT Count(*) FROM __table2)) _rowNum";

                if (dropStep.Function == ESqlFunction.none)
                {
                    throw new ArgumentException("DropStep with no function return no data!");
                }

                // first, last -> inner query
                else if (dropStep.Function.NeedsInnerQuery())
                {
                    command.CommandText =
                        $"SELECT * FROM __table2 WHERE {FullPrimaryKey(tabloid.Name)} IN (SELECT {(dropStep.Function == ESqlFunction.FIRST ? "MIN" : "MAX")}({FullPrimaryKey(tabloid.Name)}) FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum)";
                }
                // other functions
                else
                {
                    command.CommandText =
                        $"SELECT {ColumnTuplesToString(db.Application, columns, true, false, dropStep.Function.ToString())} FROM (SELECT {rowNum}, __table2.* FROM __table2, (SELECT @__ROWNUM:=-1) __fooo) __t GROUP BY _rowNum";
                }
            }

            string limitString = page != null
                ? $"LIMIT {page.RowsPerPage} OFFSET {page.PageIndex * page.RowsPerPage}"
                : limit != null
                    ? $"LIMIT {limit}"
                    : "";

            command.CommandText =
                $"{(withTable.Any() ? "with " : "")}{string.Join(",", withTable)} {command.CommandText} {order?.ToSql(this, command)} {limitString}";

            return(command);
        }
예제 #9
0
 public EntityManager(DBConnection db, Tabloid tabloid)
 {
     _db      = db;
     _tabloid = tabloid;
 }
예제 #10
0
 public abstract IDbCommand SELECT(DBConnection db, Tabloid table, IEnumerable <string> columnNames = null, Manager <Condition> conditions = null, Manager <Join> joins = null, Order order = null, GroupBy groupBy = null, int?limit = null, Page page = null, DropStep dropStep = null);