// get item and company name by itemId
        public TemporaryTable GetItemNameAndCompanyNameByItemId(int itemId)
        {
            Connection.Open();

            string query = "SELECT ItemName, CompanyName FROM Items, Company WHERE Items.CompanyId = Company.Id and Items.Id = @itemId";

            Command = new SqlCommand(query, Connection);
            Command.Parameters.Add("@itemId", itemId);

            Reader = Command.ExecuteReader();

            TemporaryTable tempTable = new TemporaryTable();


            while (Reader.Read())
            {
                tempTable.ItemName    = Reader["ItemName"].ToString();
                tempTable.CompanyName = Reader["CompanyName"].ToString();
            }

            Reader.Close();
            Connection.Close();

            return(tempTable);
        }
Beispiel #2
0
        internal Database(DatabaseSystem system, IDatabaseContext context)
        {
            System = system;
            Context = context;

            Name = Context.DatabaseName();

            DiscoverDataVersion();

            TableComposite = new TableSourceComposite(this);

            Context.RegisterInstance(this);
            Context.RegisterInstance<ITableSourceComposite>(TableComposite);

            Locker = new Locker(this);

            Sessions = new ActiveSessionList(this);

            // Create the single row table
            var t = new TemporaryTable(context, "SINGLE_ROW_TABLE", new ColumnInfo[0]);
            t.NewRow();
            SingleRowTable = t;

            TransactionFactory = new DatabaseTransactionFactory(this);
        }
Beispiel #3
0
        internal Database(DatabaseSystem system, IDatabaseContext context)
        {
            System  = system;
            Context = context;

            Name = Context.DatabaseName();

            DiscoverDataVersion();

            TableComposite = new TableSourceComposite(this);

            Context.RegisterInstance(this);
            Context.RegisterInstance <ITableSourceComposite>(TableComposite);

            Locker = new Locker(this);

            Sessions = new ActiveSessionList(this);

            // Create the single row table
            var t = new TemporaryTable(context, "SINGLE_ROW_TABLE", new ColumnInfo[0]);

            t.NewRow();
            SingleRowTable = t;

            TransactionFactory = new DatabaseTransactionFactory(this);
        }
Beispiel #4
0
        private void AddRow(TemporaryTable tmpTable, long id, string name, DateTimeOffset date)
        {
            var row = new Field[3];

            row[0] = Field.BigInt(id);
            row[1] = Field.String(name);
            row[2] = Field.Date(date);
            tmpTable.NewRow(row);
        }
Beispiel #5
0
        public InsertSearchTests()
        {
            var leftInfo = new TableInfo(ObjectName.Parse("tab1"));

            leftInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.Integer()));
            leftInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.Boolean()));
            leftInfo.Columns.Add(new ColumnInfo("c", PrimitiveTypes.Double()));

            left = new TemporaryTable(leftInfo);
            left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(5563.22) });
            left.AddRow(new[] { SqlObject.Integer(54), SqlObject.Boolean(null), SqlObject.Double(921.001) });
            left.AddRow(new[] { SqlObject.Integer(23), SqlObject.Boolean(true), SqlObject.Double(2010.221) });
        }
        public override string Translate(SqlCompilerContext context, SchemaNode node)
        {
            TemporaryTable tmp = node as TemporaryTable;

            //temporary tables need no schema qualifier
            if (tmp == null && node.Schema != null)
            {
                if (context == null)// extractor for some reason uses this method without context
                {
                    return(QuoteIdentifier(new[] { node.Schema.Name, node.Name }));
                }
                return(QuoteIdentifier(new[] { context.SqlNodeActualizer.Actualize(node.Schema), node.Name }));
            }
            return(QuoteIdentifier(new[] { node.Name }));
        }
        public TableIndexTests()
        {
            var tableInfo = new TableInfo(ObjectName.Parse("test_table1"));

            tableInfo.Columns.Add(new ColumnInfo("a", PrimitiveTypes.BigInt()));
            tableInfo.Columns.Add(new ColumnInfo("b", PrimitiveTypes.String()));

            var temp = new TemporaryTable(tableInfo);

            temp.AddRow(new [] { SqlObject.BigInt(22), SqlObject.String("o102") });
            temp.AddRow(new [] { SqlObject.BigInt(103), SqlObject.String("t-23") });
            temp.AddRow(new[] { SqlObject.BigInt(104), SqlObject.String("test22") });
            temp.BuildIndex();

            table = temp;
        }
Beispiel #8
0
        private void ReadAll()
        {
            if (Result.Type == StatementResultType.CursorRef)
            {
                var tableInfo = Result.Cursor.Source.TableInfo;
                localTable = new TemporaryTable("##LOCAL##", tableInfo);

                foreach (var row in Result.Cursor)
                {
                    var rowIndex = localTable.NewRow();
                    for (int i = 0; i < row.ColumnCount; i++)
                    {
                        localTable.SetValue(rowIndex, i, row.GetValue(i));
                    }
                }
            }
        }
        public static string BuildTemporaryTable(TemporaryTable table)
        {
            StringBuilder sb = new StringBuilder();

            sb.AppendLine($"CREATE TEMPORARY TABLE {table.Name}(");

            int i = 0;

            foreach (var column in table.Columns)
            {
                sb.AppendLine($"{column.Name} {column.DataType}{(i == table.Columns.Count - 1 ? "" : ",")}");
                i++;
            }

            sb.AppendLine(");");

            return(sb.ToString());
        }
        public void TestSetUp()
        {
            var tableInfo = new TableInfo(new ObjectName("test_table"));
            tableInfo.AddColumn("id", PrimitiveTypes.Numeric());
            tableInfo.AddColumn("name", PrimitiveTypes.String());
            tableInfo.AddColumn("date", PrimitiveTypes.DateTime());

            var cornerTime = DateTimeOffset.UtcNow;

            var tmpTable = new TemporaryTable(tableInfo);

            AddRow(tmpTable, 1, "test1", cornerTime);
            AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
            AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));

            tmpTable.BuildIndexes();

            table = tmpTable;
        }
Beispiel #11
0
        public void TestSetUp()
        {
            var tableInfo = new TableInfo(new ObjectName("test_table"));

            tableInfo.AddColumn("id", PrimitiveTypes.Numeric());
            tableInfo.AddColumn("name", PrimitiveTypes.String());
            tableInfo.AddColumn("date", PrimitiveTypes.DateTime());

            var cornerTime = DateTimeOffset.UtcNow;

            var tmpTable = new TemporaryTable(tableInfo);

            AddRow(tmpTable, 1, "test1", cornerTime);
            AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
            AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));

            tmpTable.BuildIndexes();

            table = tmpTable;
        }
Beispiel #12
0
        public void TestSetUp()
        {
            var tableName = ObjectName.Parse("APP.test_table");
            var tableInfo = new TableInfo(tableName);

            tableInfo.AddColumn("id", PrimitiveTypes.Numeric(), true);
            tableInfo.AddColumn("name", PrimitiveTypes.String(SqlTypeCode.VarChar));
            tableInfo.AddColumn("date", PrimitiveTypes.DateTime());

            cornerTime = DateTimeOffset.UtcNow;

            var tmpTable = new TemporaryTable(tableInfo);

            AddRow(tmpTable, 1, "test1", cornerTime);
            AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
            AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));

            tmpTable.BuildIndexes(DefaultIndexTypes.BlindSearch);

            table = tmpTable;
        }
Beispiel #13
0
        // get data for temporary table
        private List <TemporaryTable> GetDataForTemporaryTable(List <StockOut> stockOutList)
        {
            List <TemporaryTable> temporaryTableList = new List <TemporaryTable>();

            // for serial no
            int count = 0;

            foreach (StockOut stockOut in stockOutList)
            {
                TemporaryTable tempTable = itemManager.GetItemNameAndCompanyNameByItemId(stockOut.ItemId);

                count++;

                tempTable.SL       = count;
                tempTable.Quantity = stockOut.StockOutQuantity;


                temporaryTableList.Add(tempTable);
            }

            return(temporaryTableList);
        }
Beispiel #14
0
        public static IEnumerable <SchemaObject> CollectLocalSchema(this TSqlStatement statement, ILogger logger, SchemaFile file)
        {
            switch (statement)
            {
            case BeginEndBlockStatement beginEndBlockStatement:
                return(beginEndBlockStatement
                       .StatementList
                       .Statements
                       .CollectLocalSchema(logger, file)
                       .ToList());

            case DeclareVariableStatement declareVariableStatement:
            {
                foreach (var declaration in declareVariableStatement.Declarations)
                {
                    var name       = declaration.VariableName.Value;
                    var isNullable = false;         // TODO : how to determine this?
                    var variable   = declaration.DataType.GetField(name, isNullable, logger, file);
                    variable.Origin = OriginType.Variable;

                    file.FileContext.Variables.Peek().Add(variable);
                }

                // TODO : what should I return here?
                break;
            }

            case DeclareTableVariableStatement declareTableVariableStatement:
            {
                var columns = declareTableVariableStatement
                              .Body
                              .Definition
                              .ColumnDefinitions
                              .GetFields(logger, file)
                              .ToList();

                var tableReference = new Table()
                {
                    Columns    = columns,
                    File       = file,
                    Database   = SchemaObject.TempDb,
                    Schema     = SchemaObject.DefaultSchema,
                    Identifier = declareTableVariableStatement.Body.VariableName.Value,
                };

                var field = new TableReferenceField()
                {
                    Name       = declareTableVariableStatement.Body.VariableName.Value,
                    Type       = FieldType.Table,
                    Origin     = OriginType.Variable,
                    IsNullable = false,
                    Reference  = tableReference,
                };

                file.FileContext.Variables.Peek().Add(field);

                // TODO : what should I return here?
                break;
            }

            // TODO : this could be an actual create table statement and not just a temp table
            case CreateTableStatement createTableStatement:
            {
                if (!createTableStatement.SchemaObjectName.BaseIdentifier.Value.StartsWith("#"))
                {
                    break;         // not a temp table
                }

                var columns = createTableStatement
                              .Definition
                              .ColumnDefinitions
                              .GetFields(logger, file)
                              .ToList();

                columns.ForEach(c => c.Origin = OriginType.Table);

                var tempTable = new TemporaryTable()
                {
                    Database   = createTableStatement.SchemaObjectName.DatabaseIdentifier?.Value ?? SchemaObject.TempDb,
                    Schema     = createTableStatement.SchemaObjectName.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema,
                    Identifier = createTableStatement.SchemaObjectName.BaseIdentifier.Value,
                    File       = file,
                    Columns    = columns,
                };

                file
                .LocalSchema
                .Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable));

                break;
            }

            case IfStatement ifStatement:
            {
                // TODO : conditional output? which data set to return? we don't know till runtime
                var thenReferences = ifStatement.ThenStatement.CollectLocalSchema(logger, file).ToList();

                if (ifStatement.ElseStatement != null)
                {
                    var elseReferences = ifStatement.ElseStatement.CollectLocalSchema(logger, file).ToList();
                    return(thenReferences.Concat(elseReferences));
                }

                return(thenReferences);
            }

            case SelectStatement selectStatement:
            {
                var columns = selectStatement.GetFields(logger, file);

                if (!columns.Any())
                {
                    // if there are no columns there's no data set to return..
                    // this happens for SELECT statement that assigns values to variables
                    break;
                }

                if (selectStatement.Into != null &&
                    selectStatement.Into.BaseIdentifier.Value.StartsWith("#") &&
                    !file.LocalSchema.ContainsKey(selectStatement.Into.GetTemporaryQualifiedIdentfier()))
                {
                    var tempTableColumns = selectStatement.GetFields(logger, file);;
                    var tempTable        = new TemporaryTable()
                    {
                        Columns    = tempTableColumns,
                        File       = file,
                        Database   = SchemaObject.TempDb,
                        Schema     = SchemaObject.DefaultSchema,
                        Identifier = selectStatement.Into.BaseIdentifier.Value,
                    };

                    file
                    .LocalSchema
                    .Add(new KeyValuePair <string, SchemaObject>(tempTable.GetQualifiedIdentfier(), tempTable));
                }

                var dataSet = new DerivedTable()
                {
                    Columns    = columns,
                    File       = file,
                    Identifier = selectStatement.GetTokenText(),
                };

                return(new List <SchemaObject>()
                    {
                        dataSet
                    });
            }

            case WhileStatement whileStatement:
                return(whileStatement.Statement.CollectLocalSchema(logger, file));

            case TryCatchStatement tryCatchStatement:
            {
                var tryReferences   = tryCatchStatement.TryStatements.Statements.CollectLocalSchema(logger, file);
                var catchReferences = tryCatchStatement.CatchStatements.Statements.CollectLocalSchema(logger, file);
                return(tryReferences.Concat(catchReferences).ToList());
            }

            case ReturnStatement x:
            {
                // TODO : check this statement, do I want to stop collecting data sets now?
                // what if it is conditinal return statement?
                break;
            }

            case MergeStatement mergeStatement:
                break;     // TODO : what to do with this one?

            // NOTE : I don't care about these statements yet
            case PredicateSetStatement x: break;

            case SetVariableStatement x: break;

            case SetCommandStatement x: break;

            case SetRowCountStatement x: break;

            case UseStatement x: break;

            case DenyStatement x: break;

            case RevokeStatement x: break;

            case SetIdentityInsertStatement x: break;

            case SetTransactionIsolationLevelStatement x: break;

            case BeginTransactionStatement x: break;

            case RollbackTransactionStatement x: break;

            case CommitTransactionStatement x: break;

            case RaiseErrorStatement x: break;

            case ThrowStatement x: break;

            case BreakStatement x: break;

            case ContinueStatement x: break;

            case SaveTransactionStatement x: break;

            case UpdateStatisticsStatement x: break;

            case InsertStatement x: break;

            case UpdateStatement x: break;

            case DeleteStatement x: break;

            case ExecuteStatement x: break;

            case GrantStatement x: break;

            case CreateIndexStatement x: break;

            case GoToStatement x: break;

            case LabelStatement x: break;

            case PrintStatement x: break;

            case DeclareCursorStatement x: break;

            case OpenCursorStatement x: break;

            case FetchCursorStatement x: break;

            case CloseCursorStatement x: break;

            case DeallocateCursorStatement x: break;

            case WaitForStatement x: break;

            case BeginDialogStatement x: break;

            case SendStatement x: break;

            case EndConversationStatement x: break;

            // TODO : statements to generate schema.. might be useful for sql in the test project
            case TruncateTableStatement x: break;

            case DropTableStatement x: break;

            case DropViewStatement x: break;

            case CreateFunctionStatement x: break;

            case AlterFunctionStatement x: break;

            case CreateOrAlterFunctionStatement x: break;

            case DropFunctionStatement x: break;

            case AlterTableAddTableElementStatement x: break;

            case AlterTableConstraintModificationStatement x: break;

            case CreateTypeTableStatement x: break;

            case CreateViewStatement x: break;

            case AlterViewStatement x: break;

            case DropProcedureStatement x: break;

            case CreateProcedureStatement x: break;

            case CreateOrAlterProcedureStatement x: break;

            case CreateOrAlterViewStatement x: break;

            case AlterTableSetStatement x: break;

            case AlterProcedureStatement x: break;

            case CreateTypeUddtStatement x: break;

            default:
            {
                logger.Log(LogLevel.Warning,
                           LogType.NotSupportedYet,
                           file.Path,
                           $"\"{statement.GetType()}\" Tsql statement is not supported yet. " +
                           $"Fragment: \"{statement.GetTokenText()}\"");
                break;
            }
            }

            return(new List <SchemaObject>());
        }
 /// <inheritdoc/>
 /// <exception cref="NotSupportedException">Method is not supported.</exception>
 protected override IPathNode VisitTemporaryTable(TemporaryTable temporaryTable)
 {
     // http://support.x-tensive.com/question/3643/oracle-database-domainbuild-throws-notsupportedexception
     return(null);
 }
Beispiel #16
0
        public void TestSetUp()
        {
            var tableName = ObjectName.Parse("APP.test_table");
            var tableInfo = new TableInfo(tableName);
            tableInfo.AddColumn("id", PrimitiveTypes.Numeric(), true);
            tableInfo.AddColumn("name", PrimitiveTypes.String(SqlTypeCode.VarChar));
            tableInfo.AddColumn("date", PrimitiveTypes.DateTime());

            cornerTime = DateTimeOffset.UtcNow;

            var tmpTable = new TemporaryTable(tableInfo);

            AddRow(tmpTable, 1, "test1", cornerTime);
            AddRow(tmpTable, 2, "test2", cornerTime.AddSeconds(2));
            AddRow(tmpTable, 3, "test3", cornerTime.AddSeconds(5));

            tmpTable.BuildIndexes(DefaultIndexTypes.BlindSearch);

            table = tmpTable;
        }
Beispiel #17
0
 private void AddRow(TemporaryTable tmpTable, long id, string name, DateTimeOffset date)
 {
     var row = new Field[3];
     row[0] = Field.BigInt(id);
     row[1] = Field.String(name);
     row[2] = Field.Date(date);
     tmpTable.NewRow(row);
 }
Beispiel #18
0
 public void Dispose()
 {
     left = null;
 }
Beispiel #19
0
        public ActionResult GetAnsweredQuestion()
        {
            IEnumerable <int> getIds = b.UserPostedQuestions.Select(m => m.Id);

            try
            {
                foreach (int id in getIds)
                {
                    UserPostedQuestion del = b.UserPostedQuestions.Where(m => m.Id.Equals(id)).Single();
                    b.UserPostedQuestions.Remove(del);
                }
                b.SaveChanges();


                TemporaryTable tmp = b.TemporaryTables.SingleOrDefault(m => m.Id.Equals(1));


                UserPostedQuestion ui = new UserPostedQuestion();


                var ansQuesList = (from q in _db.Questions

                                   join u in _db.ForumUser on q.UserId equals u.UserId
                                   join a in _db.Answers on q.QuestionId equals a.QuestionId
                                   join c in _db.Category on q.CategoryId equals c.CategoryId
                                   orderby q.PostedDate

                                   select
                                   new AnsweredQuestionModel()
                {
                    Question = q.Question,
                    PostedDate = q.PostedDate,
                    Username = u.UserName,
                    Category = c.CategoryName,
                    Views = q.views,
                    Replies = q.answers,
                    Id = q.QuestionId,
                    Image = u.Image
                });

                foreach (var h in ansQuesList)
                {
                    string g = h.Question;
                    var    v = b.UserPostedQuestions.FirstOrDefault(
                        m => m.Question.Equals(g));

                    if (v == null)
                    {
                        ui.Question = h.Question;



                        ui.Answer = h.Question;

                        ui.ReplyUser    = h.Username;
                        ui.PostedDate   = h.PostedDate;
                        ui.Image        = h.Image;
                        ui.QuesPostDate = h.PostedDate;
                        ui.QuestionId   = h.Id;
                        ui.Category     = h.Category;
                        ui.TotalAnswer  = h.Views;
                        ui.TotalReplies = h.Replies;
                        b.UserPostedQuestions.Add(ui);

                        b.SaveChanges();
                    }
                }
                ViewData["AnsweredQuesList"] = (from a in b.UserPostedQuestions

                                                select new QuestionList()
                {
                    Question = a.Question,
                    PostedBy = a.ReplyUser,
                    Image = a.Image,
                    Id = a.QuestionId.Value,
                    Category = a.Category,
                    Views = a.TotalAnswer.Value,
                    Answers = a.TotalReplies.Value,
                    Posteddate = a.QuesPostDate.Value
                }).OrderByDescending(x => x.Posteddate).ToList();
            }
            catch (Exception ex)
            {
            }
            ViewData["FQAC"] = 2;


            return(View());
        }