using System;
using System.Collections.Generic;
using System.Text;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;

namespace buildsql
{
    class buildsql
    {
        static void Main(string[] args)
        {
             //BuildBasicElement();
            // BuildCreateTable();
            // BuildCreateTableMySQL();
            // BuildDelete();
            // BuildField();
            // BuildFieldConstraint();
            // BuildInsert();
            BuildSimpleSelect();
            // BuildSelect();
            // BuildTable();
            // BuildTableConstraint();
           //   BuildUpdate();
          //   BuildJoin();
           // BuildExpr1();
          //  add2sqls s = new add2sqls();
          //  Console.WriteLine(s.addsql());
        }

        public static void BuildExpr1()
        {
            TLzCustomExpression expr = new TLzCustomExpression(null);

            expr.DBVendor = TDbVendor.DbVMssql;

            expr.oper = TLzOpType.Expr_OR;

            expr.lexpr = new TLzCustomExpression(null, TDbVendor.DbVMssql, "a is null");

            expr.rexpr = new TLzCustomExpression(null, TDbVendor.DbVMssql, "b=3");

            Console.WriteLine(expr.AsText);
        }

        public static void BuildJoin()
        {
            TLzJoinItem item1 = new TLzJoinItem(null);
            item1.JoinItemTableType = TJoinTableType.jttTable;
            item1.JoinType = TSelectJoinType.sjtleft;
            item1.JoinItemTable = new TLzTable(null);
            item1.JoinItemTable.TableName = "dept";
            item1.JoinQual = new TLzCustomExpression(null);
            item1.JoinQualType = TSelectJoinQual.sjqOn;
            item1.JoinQual.AsText = "e.deptid = d.id";
            Console.WriteLine("Join Item: {0}", item1.AsText);

        }

        public static void BuildBasicElement()
        {
            TSourceToken st = new TSourceToken(null);
            st.AsText = "scott";
            Console.WriteLine("Source Token: {0}",st.AsText);

            TLz_Attr attr1 = new TLz_Attr(null);
            attr1.relname = new TSourceToken(attr1);
            attr1.relname.AsText = "scott.emp";
            Console.WriteLine("Qualified name: {0}", attr1.AsText);

            TLzCustomExpression expression1 = new TLzCustomExpression(null,TDbVendor.DbVMssql, "(x+y-z)+1");
            Console.WriteLine("Expression: {0}", expression1.AsText);

            //Change expression x+y>z to x+y>fx(z)

            TLzCustomExpression expression3 = new TLzCustomExpression(null, TDbVendor.DbVMssql, "x+y>z");
            TLzCustomExpression expression4 = new TLzCustomExpression(null, TDbVendor.DbVMssql, "fx(z)");
            expression3.rexpr = expression4;
            Console.WriteLine("Expression: {0}", expression3.AsText);
        }

        public static void BuildCreateTable()
        {
			TCreateTableSqlStatement statement1 = new TCreateTableSqlStatement(TDbVendor.DbVMssql);

                statement1.Table = new TLzTable(statement1);
                statement1.Table.TableName = "tn";
                TLzField field1 = new TLzField(statement1);
                field1.FieldType = TLzFieldType.lftColumn;
                field1.ColumnName = new TSourceToken(statement1);
                field1.ColumnName.AsText = "f1";
                field1.FieldDataType = new TLz_TypeName(statement1);
                field1.FieldDataType.AsText = "float";
                statement1.Table.Fields.Add(field1);
                field1 = new TLzField(statement1);
                field1.FieldType = TLzFieldType.lftColumn; 
                field1.ColumnName = new TSourceToken(statement1);
                field1.ColumnName.AsText = "f2";
                field1.FieldDataType = new TLz_TypeName(statement1);
                field1.FieldDataType.AsText = "decimal";
                statement1.Table.Fields.Add(field1);
                Console.WriteLine("create table:\n {0}", statement1.AsText);    
            

                field1 = new TLzField(statement1);
                field1.FieldType = TLzFieldType.lftColumn;
                field1.ColumnName = new TSourceToken(statement1);
                field1.ColumnName.AsText = "f3";
                field1.FieldDataType = new TLz_TypeName(statement1);
                field1.FieldDataType.AsText = "char(2)";
                TLzConstraint constraint1 = new TLzConstraint(statement1);
                constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
                constraint1.ConstraintType = TLzConstraintType.ctNotnull;
                field1.ColumnConstraints.Add(constraint1);
                statement1.Table.Fields.Add(field1);
                Console.WriteLine("create table:\n {0}", statement1.AsText);
            

                constraint1 = new TLzConstraint(statement1);
                constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
                constraint1.ConstraintType = TLzConstraintType.ctCheck;
                constraint1.ColExpr = new TLzCustomExpression(constraint1);
                constraint1.ColExpr.AsText = "f3>10";
                field1.ColumnConstraints.Add(constraint1);
                Console.WriteLine("create table:\n {0}", statement1.AsText);

                constraint1 = new TLzConstraint(statement1);
                constraint1.ConstraintLevel = TLzConstraintLevel.clTable;
                constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;

                using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
                {
                    lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                    lcConstraintColumn.ColumnName.AsText = "f1";
                    constraint1.ConstraintColumnList.Add(lcConstraintColumn);
                }

                using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
                {
                    lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                    lcConstraintColumn.ColumnName.AsText = "f2";
                    constraint1.ConstraintColumnList.Add(lcConstraintColumn);
                }

                
                statement1.Table.TableConstraints.Add(constraint1);

                Console.WriteLine("create table:\n {0}", statement1.AsText);

        }

        public static void BuildCreateTableMySQL()
        {
            TCreateTableSqlStatement statement1 = new TCreateTableSqlStatement(TDbVendor.DbVMysql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableName = "tn";
            TLzField field1 = new TLzField(statement1);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(statement1);
            field1.ColumnName.AsText = "f1";
            field1.FieldDataType = new TLz_TypeName(statement1);
            field1.FieldDataType.AsText = "float";
            statement1.Table.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(statement1);
            field1.ColumnName.AsText = "f2";
            field1.FieldDataType = new TLz_TypeName(statement1);
            field1.FieldDataType.AsText = "decimal";
            statement1.Table.Fields.Add(field1);
            Console.WriteLine("create table:\n {0}", statement1.AsText); 

            field1 = new TLzField(statement1);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(statement1);
            field1.ColumnName.AsText = "f3";
            field1.FieldDataType = new TLz_TypeName(statement1);
            field1.FieldDataType.AsText = "char(2)";
            TLzConstraint constraint1 = new TLzConstraint(statement1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
            constraint1.ConstraintType = TLzConstraintType.ctNotnull;
            field1.ColumnConstraints.Add(constraint1);
            statement1.Table.Fields.Add(field1);
            Console.WriteLine("create table:\n {0}", statement1.AsText); 


            constraint1 = new TLzConstraint(statement1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clTable;
            constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;


            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                lcConstraintColumn.ColumnName.AsText = "f1";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                lcConstraintColumn.ColumnName.AsText = "f2";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            statement1.Table.TableConstraints.Add(constraint1);
            Console.WriteLine("create table:\n {0}", statement1.AsText); 


            constraint1 = new TLzConstraint(statement1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clTable;
            constraint1.ConstraintType = TLzConstraintType.ctForeignkey;

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                lcConstraintColumn.ColumnName.AsText = "f3";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }


            constraint1.RefClause = new TLzConstraintRefClause(constraint1);
            constraint1.RefClause.q_name = new TLz_Attr(constraint1);
            constraint1.RefClause.q_name.relname = new TSourceToken(constraint1);
            constraint1.RefClause.q_name.relname.AsText = "scott.dept";
            constraint1.RefClause._column_listnode = new TLz_DummyNode(constraint1);
            

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(statement1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(statement1);
                lcConstraintColumn.ColumnName.AsText = "f4";
                constraint1.RefClause.ConstraintColumnList.Add(lcConstraintColumn);
            }

            constraint1.RefClause.KeyMatch = TCRKeyMatchType.ckmFull;
            constraint1.RefClause._keymatchnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keymatchnode.token1 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token1.AsText = "match";
            constraint1.RefClause._keymatchnode.token2 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token2.AsText = "full";
            constraint1.RefClause.KeyAction = TCRKeyActionType.catUpdate;
            TSourceToken token1 = new TSourceToken(constraint1);
            token1.AsText = "on";
            TSourceToken token2 = new TSourceToken(constraint1);
            token2.AsText = "update";
            TSourceToken token3 = new TSourceToken(constraint1);
            token3.AsText = "restrict";
            constraint1.RefClause._keyactionnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keyactionnode.token1 = token1;
            constraint1.RefClause._keyactionnode.token2 = token2;
            constraint1.RefClause._keyactionnode.token3 = token3;
            statement1.Table.TableConstraints.Add(constraint1);
            Console.WriteLine("create table:\n {0}", statement1.AsText); 

            statement1 = new TCreateTableSqlStatement(TDbVendor.DbVMssql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableName = "tn";
            field1 = new TLzField(statement1);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(statement1);
            field1.ColumnName.AsText = "f1";
            field1.FieldDataType = new TLz_TypeName(statement1);
            field1.FieldDataType.AsText = "float";
            statement1.Table.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(statement1);
            field1.ColumnName.AsText = "f2";
            field1.FieldDataType = new TLz_TypeName(statement1);
            field1.FieldDataType.AsText = "decimal";
            statement1.Table.Fields.Add(field1);
            statement1.SelectStmt = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement1.SelectStmt.IsParsed = true;
            field1 = new TLzField(statement1);
            field1.FieldName = "f1";
            statement1.SelectStmt.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "f2";
            statement1.SelectStmt.Fields.Add(field1);
            TLzJoin join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "t2";
            statement1.SelectStmt.JoinTables.Add(join1);
            Console.WriteLine("create table:\n {0}", statement1.AsText); 
        }

        public static void BuildDelete()
        {
            TDeleteSqlStatement statement1 = new TDeleteSqlStatement(TDbVendor.DbVMssql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableName = "table1";
            statement1.WhereClauseText = "f1=1";
            Console.WriteLine("\ndelete statement:\n {0}", statement1.AsText); 
            statement1.IsHasFrom = false;
            Console.WriteLine("\ndelete statement:\n {0}", statement1.AsText); 

            statement1.ReturningClause = new TLzReturningClause(statement1);
            statement1.ReturningClause.RowList = new  TLz_List(statement1);
            statement1.ReturningClause.NameList = new TLz_List(statement1);
            TLzCustomExpression expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr1";
            statement1.ReturningClause.RowList.Add(expression1);
            expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr2";
            statement1.ReturningClause.RowList.Add(expression1);

            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql,"name1"));
            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql,"name2"));
            
            Console.WriteLine("\ndelete statement:\n {0}", statement1.AsText); 

            statement1.ReturningClause.IntoType = 2;
            Console.WriteLine("\ndelete statement:\n {0}", statement1.AsText); 
            TLzJoin join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "table2";
            statement1.JoinTables.Add(join1);
            Console.WriteLine("\ndelete statement:\n {0}", statement1.AsText); 

        }

        public static void BuildField(){

            TLzField field1 = new TLzField(null);
            field1.FieldName = "field1";
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldName = "field1";
            field1.aliasclause = new TLz_AliasClause(field1);
            field1.aliasclause._aliastoken = new TSourceToken(field1);
            field1.aliasclause._aliastoken.AsText = "aliasname";
            
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldName = "field1";
            field1.FieldPrefix = "user1";
            field1.aliasclause = new TLz_AliasClause(field1);
            field1.aliasclause._aliastoken = new TSourceToken(field1);
            field1.aliasclause._aliastoken.AsText = "aliasname";
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldName = "field1";
            field1.aliasclause = new TLz_AliasClause(field1);
            field1.aliasclause._aliastoken = new TSourceToken(field1);
            field1.aliasclause._aliastoken.AsText = "aliasname";
            field1.aliasclause._withas = true;
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(field1);
            field1.ColumnName.AsText = "field1";
            field1.FieldDataType = new TLz_TypeName(field1);
            field1.FieldDataType.AsText = "char(1)";
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(field1);
            field1.ColumnName.AsText = "field1";
            field1.FieldDataType = new TLz_TypeName(field1);
            field1.FieldDataType.AsText = "char(1)";
            TLzConstraint constraint1 = new TLzConstraint(field1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
            constraint1.ConstraintType = TLzConstraintType.ctNotnull;
            field1.ColumnConstraints.Add(constraint1);
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

            field1 = new TLzField(null);
            field1.FieldType = TLzFieldType.lftColumn;
            field1.ColumnName = new TSourceToken(field1);
            field1.ColumnName.AsText = "field1";
            field1.FieldDataType = new TLz_TypeName(field1);
            field1.FieldDataType.AsText = "char(1)";
            constraint1 = new TLzConstraint(field1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
            constraint1.ConstraintType = TLzConstraintType.ctNotnull;
            field1.ColumnConstraints.Add(constraint1);
            constraint1 = new TLzConstraint(field1);
            constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
            constraint1.ConstraintType = TLzConstraintType.ctDefault;
            constraint1.ColExpr = new TLzCustomExpression(constraint1);
            constraint1.ColExpr.AsText = "10";
            field1.ColumnConstraints.Add(constraint1);
            Console.WriteLine("\nfield:\n {0}", field1.AsText);

        }

        public static void BuildFieldConstraint(){
            TLzConstraint constraint1 = new TLzConstraint(null);
            constraint1._keyword.AsText = "";
            constraint1._name.AsText = "";
            constraint1.ConstraintLevel = TLzConstraintLevel.clColumn;
            constraint1.ConstraintType = TLzConstraintType.ctNotnull;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctNull;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctUnique;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctCheck;
            constraint1.ColExpr = new TLzCustomExpression(constraint1);
            constraint1.ColExpr.AsText = "col1>10";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctDefault;
            constraint1.ColExpr = new TLzCustomExpression(constraint1);
            constraint1.ColExpr.AsText = "30";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctReference;
            constraint1.RefClause = new TLzConstraintRefClause(constraint1);
            constraint1.RefClause.q_name = new TLz_Attr(constraint1);
            constraint1.RefClause.q_name.relname = new TSourceToken(constraint1);
            constraint1.RefClause.q_name.relname.AsText = "ab";
            constraint1.RefClause._column_listnode = new TLz_DummyNode(constraint1);
            TSourceToken token1 = new TSourceToken(constraint1);
            token1.AsText = "(";
            TSourceToken token2 = new TSourceToken(constraint1);
            token2.AsText = ")";
            constraint1.RefClause._column_listnode.RawTokens.Add(token1);
            constraint1.RefClause._column_listnode.RawTokens.Add(token2);
            TLz_Attr attr1 = new TLz_Attr(constraint1);
            attr1.relname = new TSourceToken(constraint1);
            attr1.relname.AsText = "col1";
            TLz_Attr attr2 = new TLz_Attr(constraint1);
            attr2.relname = new TSourceToken(constraint1);
            attr2.relname.AsText = "col2";
            constraint1.RefClause._column_listnode.list1 = new TLz_List(constraint1);
            constraint1.RefClause._column_listnode.list1.Add(attr1);
            constraint1.RefClause._column_listnode.list1.Add(attr2);
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.RefClause.KeyAction = TCRKeyActionType.catDelete;
            token1 = new TSourceToken(constraint1);
            token1.AsText = "on";
            token2 = new TSourceToken(constraint1);
            token2.AsText = "delete";
            TSourceToken token3 = new TSourceToken(constraint1);
            token3.AsText = "cascade";
            constraint1.RefClause._keyactionnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keyactionnode.token1 = token1;
            constraint1.RefClause._keyactionnode.token2 = token2;
            constraint1.RefClause._keyactionnode.token3 = token3;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctMySQLAutoIncrement;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctUnique;
            constraint1._stCluster = new TSourceToken(constraint1);
            constraint1._stCluster.AsText = "clustered";
            constraint1._opt_onfilegroup = new TLz_MssqlOnFileGroupClause(constraint1);
            constraint1._opt_onfilegroup.AsText = "on default";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;
            constraint1._stCluster = new TSourceToken(constraint1);
            constraint1._stCluster.AsText = "nonclustered";
            constraint1._opt_onfilegroup = new TLz_MssqlOnFileGroupClause(constraint1);
            constraint1._opt_onfilegroup.AsText = "on colname";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctmssqlcollate;
            constraint1._collatename = new TSourceToken(constraint1);
            constraint1._collatename.AsText = "colname";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctmssqlIdentity;
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1._ndIdentitySeed = new TLzCustomExpression(constraint1);
            constraint1._ndIdentitySeed.AsText = "expr1";
            constraint1._ndIdentityIncrement = new TLzCustomExpression(constraint1);
            constraint1._ndIdentityIncrement.AsText = "expr2";

            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctDefault;
            constraint1.ColExpr = new TLzCustomExpression(constraint1);
            constraint1.ColExpr.AsText = "expr1";
            constraint1._stWith = new TSourceToken(constraint1);
            constraint1._stWith.AsText = "with";
            constraint1._stValues = new TSourceToken(constraint1);
            constraint1._stValues.AsText = "values";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctReference;
            constraint1.RefClause = new TLzConstraintRefClause(constraint1);
            constraint1.RefClause.q_name = new TLz_Attr(constraint1);
            constraint1.RefClause.q_name.relname = new TSourceToken(constraint1);
            constraint1.RefClause.q_name.relname.AsText = "scott.emp";
            constraint1.RefClause._column_listnode = new TLz_DummyNode(constraint1);
            token1 = new TSourceToken(constraint1);
            token1.AsText = "(";
            token2 = new TSourceToken(constraint1);
            token2.AsText = ")";
            constraint1.RefClause._column_listnode.RawTokens.Add(token1);
            constraint1.RefClause._column_listnode.RawTokens.Add(token2);
            attr1 = new TLz_Attr(constraint1);
            attr1.relname = new TSourceToken(constraint1);
            attr1.relname.AsText = "col1";
            attr2 = new TLz_Attr(constraint1);
            attr2.relname = new TSourceToken(constraint1);
            attr2.relname.AsText = "col2";
            constraint1.RefClause._column_listnode.list1 = new TLz_List(constraint1);
            constraint1.RefClause._column_listnode.list1.Add(attr1);
            constraint1.RefClause._column_listnode.list1.Add(attr2);
            constraint1.RefClause.KeyMatch = TCRKeyMatchType.ckmPartial;
            constraint1.RefClause._keymatchnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keymatchnode.token1 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token1.AsText = "match";
            constraint1.RefClause._keymatchnode.token2 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token2.AsText = "partial";
            constraint1.RefClause.KeyAction = TCRKeyActionType.catUpdate;
            constraint1.RefClause._keyactionnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keyactionnode.token1 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token1.AsText = "on";
            constraint1.RefClause._keyactionnode.token2 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token2.AsText = "delete";
            constraint1.RefClause._keyactionnode.token3 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token3.AsText = "no";
            constraint1.RefClause._keyactionnode.token4 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token4.AsText = "action";
            constraint1.RefClause._keyactionnode.token5 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token5.AsText = "on";
            constraint1.RefClause._keyactionnode.token6 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token6.AsText = "update";
            constraint1.RefClause._keyactionnode.token7 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token7.AsText = "set";
            constraint1.RefClause._keyactionnode.token8 = new TSourceToken(constraint1);
            constraint1.RefClause._keyactionnode.token8.AsText = "null";
            Console.WriteLine("\nConstraint:\n {0}", constraint1.AsText);

        }

        public static void BuildInsert()
        {
            TInsertSqlStatement statement1 = new TInsertSqlStatement(TDbVendor.DbVMssql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableName = "table1";

            TLzFieldList fieldlist = new TLzFieldList(false);
            TLzField field1 = new TLzField(statement1);
            field1.FieldName = "v1";
            fieldlist.Add(field1);

            field1 = new TLzField(statement1);
            field1.FieldName = "v2";
            fieldlist.Add(field1);

            statement1.MultiValues.Add(fieldlist);
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);


            field1 = new TLzField(statement1);
            field1.FieldName = "col1";
            statement1.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "col2";
            statement1.Fields.Add(field1);
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);

            //multivalue insert of sql server 2008
            TLzFieldList fieldlist2 = new TLzFieldList(false);
            TLzField field11 = new TLzField(statement1);
            field11.FieldName = "v3";
            fieldlist2.Add(field11);

            field11 = new TLzField(statement1);
            field11.FieldName = "v4";
            fieldlist2.Add(field11);

            statement1.MultiValues.Add(fieldlist2);
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);


            statement1 = new TInsertSqlStatement(TDbVendor.DbVMssql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableType = TLzTableType.lttSubquery;
            statement1.Table.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement1.Table.SubQuery.IsParsed = true;
            (statement1.Table.SubQuery as TSelectSqlStatement).WithParenthesis = 1;
            field1 = new TLzField(statement1);
            field1.FieldName = "f1";
            statement1.Table.SubQuery.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "f2";
            statement1.Table.SubQuery.Fields.Add(field1);
            TLzJoin join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "t2";
            (statement1.Table.SubQuery as TSelectSqlStatement).JoinTables.Add(join1);

            TLzFieldList fieldlist1 = new TLzFieldList(false);

            field1 = new TLzField(statement1);
            field1.FieldName = "v1";
            fieldlist1.Add(field1);
            
            
            field1 = new TLzField(statement1);
            field1.FieldName = "v2";
            fieldlist1.Add(field1);

            statement1.MultiValues.Add(fieldlist1);
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);

            statement1.Table.AliasClause = new TLz_AliasClause(statement1);
            statement1.Table.AliasClause._aliastoken = new TSourceToken(statement1);
            statement1.Table.AliasClause._aliastoken.AsText = "t2alias";
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);

            statement1 = new TInsertSqlStatement(TDbVendor.DbVMssql);
            statement1.Table = new TLzTable(statement1);
            statement1.Table.TableName = "table1";
            field1 = new TLzField(statement1);
            field1.FieldName = "col1";
            statement1.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "col2";
            statement1.Fields.Add(field1);
            statement1.ValueType = TInsertValueType.ivtSubquery;
            statement1.subquery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement1.subquery.IsParsed = true;
            field1 = new TLzField(statement1);
            field1.FieldName = "f1";
            statement1.subquery.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "f2";
            statement1.subquery.Fields.Add(field1);
            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "t2";
            statement1.subquery.JoinTables.Add(join1);
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);

            statement1.ReturningClause = new TLzReturningClause(statement1);
            statement1.ReturningClause.RowList = new TLz_List(statement1);
            statement1.ReturningClause.NameList = new TLz_List(statement1);
            TLzCustomExpression expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr1";
            statement1.ReturningClause.RowList.Add(expression1);
            expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr2";
            statement1.ReturningClause.RowList.Add(expression1);
            
            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql,"f1"));
            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql, "f2"));
            Console.WriteLine("\ninsert statement:\n {0}", statement1.AsText);

        }

        public static void BuildSimpleSelect()
        {
            TSelectSqlStatement statement = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement.IsParsed = true;

            TLzField column = null;
            TLzJoin join = null;

            column = new TLzField(statement);
            column.FieldName = "ename";
            column.FieldPrefix = "e";
            column.aliasclause = new TLz_AliasClause(statement);
            column.aliasclause._aliastoken = new TSourceToken(statement);
            column.aliasclause._aliastoken.AsText = "empname";
            statement.Fields.Add(column);

            column = new TLzField(statement);
            column.FieldName = "id";
            column.FieldPrefix = "d";
            column.aliasclause = new TLz_AliasClause(statement);
            column.aliasclause._aliastoken = new TSourceToken(statement);
            column.aliasclause._aliastoken.AsText = "deptid";
            column.aliasclause._withas = true;
            statement.Fields.Add(column);
            
            join = new TLzJoin(statement);
            join.JoinTable = new TLzTable(statement);
            join.JoinTable.TableName = "emp";
            join.JoinTable.AliasClause = new TLz_AliasClause(statement);
            join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);
            join.JoinTable.AliasClause._aliastoken.AsText = "e";
            statement.JoinTables.Add(join);
            
            join = new TLzJoin(statement);
            join.JoinTable = new TLzTable(statement);
            join.JoinTable.TableName = "dept";
            join.JoinTable.AliasClause = new TLz_AliasClause(statement);
            join.JoinTable.AliasClause._aliastoken = new TSourceToken(statement);
            join.JoinTable.AliasClause._aliastoken.AsText = "d";
            statement.JoinTables.Add(join);
            
            statement.WhereClauseText = "e.deptid = d.id";
            Console.WriteLine("\n{0}", statement.AsText);
        }

        public static void BuildSelect(){
            TSelectSqlStatement statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            
            statement2.IsParsed = true;
            TLzField field1 = new TLzField(statement2);
            field1.FieldName = "varname";
            statement2.Fields.Add(field1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "empno";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "no";
            statement2.Fields.Add(field1);
            field1 = new TLzField(statement2);
            field1.FieldName = "empname";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "ename";
            field1.aliasclause._withas = true;
            statement2.Fields.Add(field1);
            TLzJoin join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);
            

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "ename";
            field1.FieldPrefix = "e";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "empname";
            statement2.Fields.Add(field1);
            field1 = new TLzField(statement2);
            field1.FieldName = "id";
            field1.FieldPrefix = "d";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "deptid";
            field1.aliasclause._withas = true;
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            join1.JoinTable.AliasClause = new TLz_AliasClause(statement2);
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(statement2);
            join1.JoinTable.AliasClause._aliastoken.AsText = "e";
            statement2.JoinTables.Add(join1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "dept";
            join1.JoinTable.AliasClause = new TLz_AliasClause(statement2);
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(statement2);
            join1.JoinTable.AliasClause._aliastoken.AsText = "d";
            statement2.JoinTables.Add(join1);
            statement2.WhereClauseText = "e.deptid = d.id";
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "ename";
            field1.FieldPrefix = "e";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "empname";
            statement2.Fields.Add(field1);
            field1 = new TLzField(statement2);
            field1.FieldName = "id";
            field1.FieldPrefix = "d";
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "deptid";
            field1.aliasclause._withas = true;
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            join1.JoinTable.AliasClause = new TLz_AliasClause(statement2);
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(statement2);
            join1.JoinTable.AliasClause._aliastoken.AsText = "e";
            TLzJoinItem item1 = new TLzJoinItem(statement2);
            item1.JoinItemTableType = TJoinTableType.jttTable;
            TSourceToken token1 = new TSourceToken(statement2);
            token1.AsText = "join";
            item1.RawTokens.Add(token1);
            item1.JoinItemTable = new TLzTable(statement2);
            item1.JoinItemTable.TableName = "dept";
            item1.JoinItemTable.AliasClause = new TLz_AliasClause(statement2);
            item1.JoinItemTable.AliasClause._aliastoken = new TSourceToken(statement2);
            item1.JoinItemTable.AliasClause._aliastoken.AsText = "d";
            item1.JoinQual = new TLzCustomExpression(statement2);
            item1.JoinQualType = TSelectJoinQual.sjqOn;
            item1.JoinQual.AsText = "e.deptid = d.id";
            join1.JoinItems.Add(item1);
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "f1";
            field1.FieldPrefix = "t1";
            field1.aliasclause = new TLz_AliasClause(statement2);
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "table1";
            join1.JoinTable.TablePrefix = "my";
            join1.JoinTable.AliasClause = new TLz_AliasClause(statement2);
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(statement2);
            join1.JoinTable.AliasClause._aliastoken.AsText = "t1";
            item1 = new TLzJoinItem(statement2);
            item1.JoinItemTableType = TJoinTableType.jttJoin;
            token1 = new TSourceToken(statement2);
            token1.AsText = "join";
            item1.RawTokens.Add(token1);
            item1.JoinQual = new TLzCustomExpression(statement2);
            item1.JoinQualType = TSelectJoinQual.sjqOn;
            item1.JoinQual.AsText = "t1.f1 = t2.f1";
            item1.JoinItemJoin = new TLzJoin(statement2);
            item1.JoinItemJoin.NestedLevel = 1;
            item1.JoinItemJoin.newalias = new TLz_AliasClause(statement2);
            item1.JoinItemJoin.newalias._aliastoken = new TSourceToken(statement2);
            item1.JoinItemJoin.newalias._aliastoken.AsText = "joinalias1";
            item1.JoinItemJoin.newalias._withas = true;
            item1.JoinItemJoin.JoinTable = new TLzTable(statement2);
            item1.JoinItemJoin.JoinTable.TableName = "table2";
            item1.JoinItemJoin.JoinTable.TablePrefix = "my";
            item1.JoinItemJoin.JoinTable.AliasClause = new TLz_AliasClause(statement2);
            item1.JoinItemJoin.JoinTable.AliasClause._aliastoken = new TSourceToken(statement2);
            item1.JoinItemJoin.JoinTable.AliasClause._aliastoken.AsText = "t2";
            TLzJoinItem item2 = new TLzJoinItem(statement2);
            item2.JoinItemTableType = TJoinTableType.jttTable;
            token1 = new TSourceToken(statement2);
            token1.AsText = "left";
            item2.RawTokens.Add(token1);
            token1 = new TSourceToken(statement2);
            token1.AsText = "join";
            item2.RawTokens.Add(token1);
            item2.JoinItemTable = new TLzTable(statement2);
            item2.JoinItemTable.TableName = "table3";
            item2.JoinItemTable.TablePrefix = "my";
            item2.JoinItemTable.AliasClause = new TLz_AliasClause(statement2);
            item2.JoinItemTable.AliasClause._aliastoken = new TSourceToken(statement2);
            item2.JoinItemTable.AliasClause._aliastoken.AsText = "t3";
            item2.JoinQual = new TLzCustomExpression(statement2);
            item2.JoinQualType = TSelectJoinQual.sjqOn;
            item2.JoinQual.AsText = "t2.f1 = t3.f1";
            item1.JoinItemJoin.JoinItems.Add(item2);
            join1.JoinItems.Add(item1);
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "f1";
            statement2.Fields.Add(field1);
            field1 = new TLzField(statement2);
            field1.FieldType = TLzFieldType.lftSubquery;
            field1.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            field1.SubQuery.IsParsed = true;
            (field1.SubQuery as TSelectSqlStatement).WithParenthesis = 1;
            TLzField field2 = new TLzField(field1.SubQuery);
            field2.FieldName = "f2";
            field1.SubQuery.Fields.Add(field2);
            TLzJoin join2 = new TLzJoin(field1.SubQuery);
            join2.JoinTable = new TLzTable(field1.SubQuery);
            join2.JoinTable.TableName = "t2";
            (field1.SubQuery as TSelectSqlStatement).JoinTables.Add(join2);
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            field1 = new TLzField(statement2);
            field1.FieldName = "f1";
            statement2.Fields.Add(field1);
            field1 = new TLzField(statement2);
            field1.FieldType = TLzFieldType.lftSubquery;
            field1.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            field1.SubQuery.IsParsed = true;
            (field1.SubQuery as TSelectSqlStatement).WithParenthesis = 1;
            field2 = new TLzField(field1.SubQuery);
            field2.FieldName = "f2";
            field1.SubQuery.Fields.Add(field2);
            join2 = new TLzJoin(field1.SubQuery);
            join2.JoinTable = new TLzTable(field1.SubQuery);
            join2.JoinTable.TableName = "t2";
            (field1.SubQuery as TSelectSqlStatement).JoinTables.Add(join2);
            field1.aliasclause = new TLz_AliasClause(statement2);
            field1.aliasclause._aliastoken = new TSourceToken(statement2);
            field1.aliasclause._aliastoken.AsText = "f2";
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            join1.JoinTable.AliasClause = new TLz_AliasClause(join1);
            join1.JoinTable.AliasClause._withas = true;
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(join1);
            join1.JoinTable.AliasClause._aliastoken.AsText = "t1";
            statement2.JoinTables.Add(join1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableType = TLzTableType.lttSubquery;
            join1.JoinTable.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            join1.JoinTable.SubQuery.IsParsed = true;
            (join1.JoinTable.SubQuery as TSelectSqlStatement).WithParenthesis = 1;
            join1.JoinTable.AliasClause = new TLz_AliasClause(join1);
            join1.JoinTable.AliasClause._withas = true;
            join1.JoinTable.AliasClause._aliastoken = new TSourceToken(join1);
            join1.JoinTable.AliasClause._aliastoken.AsText = "t2";
            TLzField field3 = new TLzField(join1.JoinTable.SubQuery);
            field3.FieldName = "f3";
            join1.JoinTable.SubQuery.Fields.Add(field3);
            join2 = new TLzJoin(join1.JoinTable.SubQuery);
            join2.JoinTable = new TLzTable(join1.JoinTable.SubQuery);
            join2.JoinTable.TableName = "t3";
            (join1.JoinTable.SubQuery as TSelectSqlStatement).JoinTables.Add(join2);
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            statement2.SelectDistinctText = "distinct";
            field1 = new TLzField(statement2);
            field1.FieldName = "empname";
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            statement2.JoinTables.Add(join1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            statement2.topclauseText = "top 10";
            statement2.IntoClauseAsText = "@var";
            field1 = new TLzField(statement2);
            field1.FieldName = "empname";
            statement2.Fields.Add(field1);
            join1 = new TLzJoin(statement2);
            join1.JoinTable = new TLzTable(statement2);
            join1.JoinTable.TableName = "emp";
            statement2.JoinTables.Add(join1);
            statement2.WhereClause = new TLzCustomExpression(statement2);
            statement2.WhereClause.AsText = "f1>10";
            statement2.HavingClause = new TLzCustomExpression(statement2);
            statement2.HavingClause.AsText = "f2 = 1";
            
            //TLzCustomExpression expression1 = new TLzCustomExpression(statement2);
            //expression1.AsText = "f1";
            //expression1 = new TLzCustomExpression(statement2);
            //expression1.AsText = "f2";

            statement2.GroupbyClause = new TLzGroupBy(statement2);
            statement2.GroupbyClause.AsText = "group by f1,f2";
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);
            statement2.GroupbyClause.AsText = ""; //set astext to empty string 

            // GroupItems used to contain 
            statement2.GroupbyClause.GroupItems = new TLz_List(statement2);
            TLzGroupByItem gi1 = new TLzGroupByItem(statement2);
            gi1._ndExpr = new TLzCustomExpression(statement2);
            gi1._ndExpr.AsText = "f3";
            statement2.GroupbyClause.GroupItems.Add(gi1);

            TLzGroupByItem gi2 = new TLzGroupByItem(statement2);
            gi2._ndExpr = new TLzCustomExpression(statement2);
            gi2._ndExpr.AsText = "f4";
            statement2.GroupbyClause.GroupItems.Add(gi2);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);
            
            
            statement2.SortClause = new TLzOrderByList(false);
            TLzOrderBy by1 = new TLzOrderBy(statement2);
            by1.SortItemType = TLzSortItemType.sitExpression;
            by1.SortExpr = new TLzCustomExpression(statement2);
            by1.SortExpr.AsText = "f1";
            by1.SortType = TLzSortType.srtAsc;
            statement2.SortClause.Add(by1);
            by1 = new TLzOrderBy(statement2);
            by1.SortItemType = TLzSortItemType.sitExpression;
            by1.SortExpr = new TLzCustomExpression(statement2);
            by1.SortExpr.AsText = "f2";
            by1.SortType = TLzSortType.srtDesc;
            statement2.SortClause.Add(by1);
            by1 = new TLzOrderBy(statement2);
            by1.SortItemType = TLzSortItemType.sitExpression;
            by1.SortExpr = new TLzCustomExpression(statement2);
            by1.SortExpr.AsText = "f3";
            statement2.SortClause.Add(by1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);


            statement2 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement2.IsParsed = true;
            TSelectSqlStatement statement1 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement1.IsParsed = true;
            field1 = new TLzField(statement1);
            field1.FieldName = "empname";
            statement1.Fields.Add(field1);
            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "emp";
            statement1.JoinTables.Add(join1);
            TSelectSqlStatement statement3 = new TSelectSqlStatement(TDbVendor.DbVMssql);
            statement3.IsParsed = true;
            field1 = new TLzField(statement3);
            field1.FieldName = "deptname";
            statement3.Fields.Add(field1);
            join1 = new TLzJoin(statement3);
            join1.JoinTable = new TLzTable(statement3);
            join1.JoinTable.TableName = "dept";
            statement3.JoinTables.Add(join1);
            statement2.LeftStmt = statement1;
            statement2.RightStmt = statement3;
            statement2.SelectSetType = TSelectSetType.sltUnion;
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2.SelectSetType = TSelectSetType.sltUnionAll;
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2.SelectSetType = TSelectSetType.sltMinus;
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2.SelectSetType = TSelectSetType.sltIntersect;
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

            statement2.SortClause = new TLzOrderByList(false);
            by1 = new TLzOrderBy(statement2);
            by1.AsText = "f1";
            statement2.SortClause.Add(by1);
            Console.WriteLine("\nSelect statement:\n {0}", statement2.AsText);

        }

        public static void BuildTable(){
            TLzTable table1 = new TLzTable(null);
            table1.TableName = "table1";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableName = "table1";
            table1.AliasClause = new TLz_AliasClause(table1);
            table1.AliasClause._aliastoken = new TSourceToken(table1);
            table1.AliasClause._aliastoken.AsText = "aliasname";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableName = "table1";
            table1.TablePrefix = "user1";
            table1.AliasClause = new TLz_AliasClause(table1);
            table1.AliasClause._aliastoken = new TSourceToken(table1);
            table1.AliasClause._aliastoken.AsText = "aliasname";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableName = "table1";
            table1.AliasClause = new TLz_AliasClause(table1);
            table1.AliasClause._aliastoken = new TSourceToken(table1);
            table1.AliasClause._aliastoken.AsText = "aliasname";
            table1.AliasClause._withas = true;
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableName = "func(p1,p2)";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableName = "(select f1,f2 from subtable1)";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);

            table1 = new TLzTable(null);
            table1.TableType = TLzTableType.lttSubquery;
            table1.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            table1.SubQuery.AsText = "(select f1,f2 from subtable2)";
            Console.WriteLine("\nTable:\n {0}", table1.AsText);


        }

        public static void BuildTableConstraint(){
            TLzConstraint constraint1 = new TLzConstraint(null);
            constraint1._keyword.AsText = "";
            constraint1._name.AsText = "";
            constraint1.ConstraintLevel = TLzConstraintLevel.clTable;
            constraint1.ConstraintType = TLzConstraintType.ctCheck;
            constraint1.ColExpr = new TLzCustomExpression(constraint1);
            constraint1.ColExpr.AsText = "col1=1";
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctUnique;


            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col1";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col2";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }
            
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctForeignkey;
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctForeignkey;
            constraint1._stMySQLIndexName = new TSourceToken(constraint1);
            constraint1._stMySQLIndexName.AsText = "keyname";


            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col1";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col2";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }
            
            constraint1.RefClause = new TLzConstraintRefClause(constraint1);
            constraint1.RefClause.q_name = new TLz_Attr(constraint1);
            constraint1.RefClause.q_name.relname = new TSourceToken(constraint1);
            constraint1.RefClause.q_name.relname.AsText = "scott.dept";
            

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col3";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col4";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            constraint1.RefClause.KeyMatch = TCRKeyMatchType.ckmFull;
            constraint1.RefClause._keymatchnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keymatchnode.token1 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token1.AsText = "match";
            constraint1.RefClause._keymatchnode.token2 = new TSourceToken(constraint1);
            constraint1.RefClause._keymatchnode.token2.AsText = "full";
            constraint1.RefClause.KeyAction = TCRKeyActionType.catUpdate;
            TSourceToken token1 = new TSourceToken(constraint1);
            token1.AsText = "on";
            TSourceToken token2 = new TSourceToken(constraint1);
            token2.AsText = "update";
            TSourceToken token3 = new TSourceToken(constraint1);
            token3.AsText = "restrict";
            constraint1.RefClause._keyactionnode = new TLz_DummyNode(constraint1);
            constraint1.RefClause._keyactionnode.token1 = token1;
            constraint1.RefClause._keyactionnode.token2 = token2;
            constraint1.RefClause._keyactionnode.token3 = token3;
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctMySQLKey;
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctMySQLIndex;
            constraint1._stMySQLIndexName = new TSourceToken(constraint1);
            constraint1._stMySQLIndexName.AsText = "indexname";
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctUnique;
            constraint1._stCluster = new TSourceToken(constraint1);
            constraint1._stCluster.AsText = "clustered";
            constraint1._opt_onfilegroup = new TLz_MssqlOnFileGroupClause(constraint1);
            constraint1._opt_onfilegroup.AsText = "on default";
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctPrimarykey;
            constraint1._stCluster = new TSourceToken(constraint1);
            constraint1._stCluster.AsText = "nonclustered";
            constraint1._opt_onfilegroup = new TLz_MssqlOnFileGroupClause(constraint1);
            constraint1._opt_onfilegroup.AsText = "on colname";
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

            constraint1.ConstraintType = TLzConstraintType.ctForeignkey;
            constraint1.RefClause = null;
            constraint1._stMySQLIndexName = null;
            
            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col1";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }

            using (TLzConstraintColumn lcConstraintColumn = new TLzConstraintColumn(constraint1))
            {
                lcConstraintColumn.ColumnName = new TLz_Attr(constraint1);
                lcConstraintColumn.ColumnName.AsText = "col2";
                constraint1.ConstraintColumnList.Add(lcConstraintColumn);
            }
            
            constraint1.RefClause = new TLzConstraintRefClause(constraint1);
            constraint1.RefClause.q_name = new TLz_Attr(constraint1);
            constraint1.RefClause.q_name.relname = new TSourceToken(constraint1);
            constraint1.RefClause.q_name.relname.AsText = "scott.emp";
            constraint1.RefClause.ConstraintColumnList.Clear();
            Console.WriteLine("\nTable constraint:\n {0}", constraint1.AsText);

        }

        public static void BuildUpdate()
        {
            TUpdateSqlStatement statement1 = new TUpdateSqlStatement(TDbVendor.DbVMssql);
            TLzJoin join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "table1";
            statement1.UpdateTables.Add(join1);
            TLzField field1 = new TLzField(statement1);
            field1.FieldExpr = new TLzCustomExpression(statement1);
            field1.AsText = "f1 = 1";
            statement1.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldExpr = new TLzCustomExpression(statement1);
            field1.AsText = "f2 = 2";
            statement1.Fields.Add(field1);
            Console.WriteLine("\nUpdate statement:\n {0}", statement1.AsText);

            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "table2";
            statement1.UpdateTables.Add(join1);
            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "table3";
            statement1.JoinTables.Add(join1);
            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableName = "table4";
            statement1.JoinTables.Add(join1);
            Console.WriteLine("\nUpdate statement:\n {0}", statement1.AsText);

            statement1.WhereClauseText = "f3=1";
            statement1.ReturningClause = new TLzReturningClause(statement1);
            statement1.ReturningClause.RowList = new TLz_List(statement1);
            statement1.ReturningClause.NameList = new TLz_List(statement1);
            TLzCustomExpression expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr1";
            statement1.ReturningClause.RowList.Add(expression1);
            expression1 = new TLzCustomExpression(statement1);
            expression1.AsText = "expr2";
            statement1.ReturningClause.RowList.Add(expression1);

            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql,"f1"));
            statement1.ReturningClause.NameList.Add(new TLzCustomExpression(statement1, TDbVendor.DbVMssql, "f2"));
            Console.WriteLine("\nUpdate statement:\n {0}", statement1.AsText);

            statement1 = new TUpdateSqlStatement(TDbVendor.DbVMssql);
            join1 = new TLzJoin(statement1);
            join1.JoinTable = new TLzTable(statement1);
            join1.JoinTable.TableType = TLzTableType.lttSubquery;
            join1.JoinTable.SubQuery = new TSelectSqlStatement(TDbVendor.DbVMssql);
            join1.JoinTable.SubQuery.IsParsed = true;
            (join1.JoinTable.SubQuery as TSelectSqlStatement).WithParenthesis = 1;
            field1 = new TLzField(statement1);
            field1.FieldName = "f1";
            join1.JoinTable.SubQuery.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldName = "f2";
            join1.JoinTable.SubQuery.Fields.Add(field1);
            TLzJoin join2 = new TLzJoin(statement1);
            join2.JoinTable = new TLzTable(statement1);
            join2.JoinTable.TableName = "t1";
            (join1.JoinTable.SubQuery as TSelectSqlStatement).JoinTables.Add(join2);
            join1.newalias = new TLz_AliasClause(statement1);
            join1.newalias._withas = true;
            join1.newalias._aliastoken = new TSourceToken(statement1);
            join1.newalias._aliastoken.AsText = "b";
            statement1.UpdateTables.Add(join1);
            field1 = new TLzField(statement1);
            field1.FieldExpr = new TLzCustomExpression(statement1);
            field1.AsText = "f1 = 1";
            statement1.Fields.Add(field1);
            field1 = new TLzField(statement1);
            field1.FieldExpr = new TLzCustomExpression(statement1);
            field1.AsText = "f2 = 2";
            statement1.Fields.Add(field1);
            Console.WriteLine("\nUpdate statement:\n {0}", statement1.AsText);
        }
    }
}