private void CopyMembers(DestinationTableParameters old)
 {
     this.table = old.table;
     this.operation = old.operation;
     this.bulkInsertBatchSize = old.bulkInsertBatchSize;
     this.bulkInsertTimeout = old.bulkInsertTimeout;
 }
 private void InitializeMembers()
 {
     this.table = null;
     this.operation = DestinationTableOperation.Create;
     this.bulkInsertBatchSize = 10000;
     this.bulkInsertTimeout = 1200;
 }
Esempio n. 3
0
        public DestinationTable(Table table)
        {
            InitializeMembers();

            this.dataset = (SqlServerDataset)table.Dataset;
            this.databaseName = table.DatabaseName;
            this.schemaName = table.SchemaName;
            this.tableName = table.TableName;
        }
        public void GenerateCreateDestinationTableQueryTest()
        {
            DataTable schema;

            using (var cn = new SqlConnection(Jhu.Graywulf.Test.AppSettings.IOTestConnectionString))
            {
                cn.Open();

                var sql = "SELECT * FROM SampleData";
                using (var cmd = new SqlCommand(sql, cn))
                {
                    using (var dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                    {
                        schema = dr.GetSchemaTable();
                    }
                }
            }

            var dest = new Table()
            {
                SchemaName = "dbo",
                TableName = "destination"
            };

            var cg = new SqlServerCodeGenerator();
            var res = cg.GenerateCreateDestinationTableQuery(schema, dest);

            Assert.AreEqual(@"CREATE TABLE [dbo].[destination] ([float] real  NULL,
            [double] float  NULL,
            [decimal] money  NULL,
            [nvarchar(50)] nvarchar(50)  NULL,
            [bigint] bigint  NULL,
            [int] int NOT NULL,
            [tinyint] tinyint  NULL,
            [smallint] smallint  NULL,
            [bit] bit  NULL,
            [ntext] nvarchar(max)  NULL,
            [char] char(1)  NULL,
            [datetime] datetime  NULL,
            [guid] uniqueidentifier  NULL)", res);
        }
Esempio n. 5
0
        private ICopyTable GetTableCopy(string tableName, bool remote)
        {
            ICopyTable q = null;

            if (remote)
            {
                q = RemoteServiceHelper.CreateObject <ICopyTable>(Test.Constants.Localhost);
            }
            else
            {
                q = new CopyTable();
            }

            var ds = new Jhu.Graywulf.Schema.SqlServer.SqlServerDataset(Jhu.Graywulf.Test.Constants.TestDatasetName, Jhu.Graywulf.Test.AppSettings.IOTestConnectionString)
            {
                IsMutable = true
            };

            var source = new SourceTableQuery()
            {
                Dataset = ds,
                Query   = "SELECT 1 AS one, 2 AS two, 3 AS three"
            };

            q.Source = new SourceTableQuery[] { source };


            var destination = new Jhu.Graywulf.Schema.Table()
            {
                Dataset    = ds,
                SchemaName = "dbo",
                TableName  = tableName
            };

            q.Destination = new Schema.Table[] { destination };

            q.Options = TableInitializationOptions.Create;

            return(q);
        }
Esempio n. 6
0
        private ICopyTable GetTableCopy(string tableName, bool remote)
        {
            ICopyTable q = null;
            if (remote)
            {
                q = RemoteServiceHelper.CreateObject<ICopyTable>(Test.Constants.Localhost);
            }
            else
            {
                q = new CopyTable();
            }

            var ds = new Jhu.Graywulf.Schema.SqlServer.SqlServerDataset(Jhu.Graywulf.Test.Constants.TestDatasetName, Jhu.Graywulf.Test.AppSettings.IOTestConnectionString)
            {
                IsMutable = true
            };

            var source = new SourceTableQuery()
            {
                Dataset = ds,
                Query = "SELECT 1 AS one, 2 AS two, 3 AS three"
            };

            q.Source = new SourceTableQuery[] { source };

            var destination = new Jhu.Graywulf.Schema.Table()
            {
                Dataset = ds,
                SchemaName = "dbo",
                TableName = tableName
            };

            q.Destination = new Schema.Table[] { destination };

            q.Options = TableInitializationOptions.Create;

            return q;
        }
Esempio n. 7
0
        protected Guid ScheduleExportTableJob(string schemaName, string tableName, string path, QueueType queueType)
        {
            var queue = String.Format("QueueInstance:Graywulf.Controller.Controller.{0}", queueType.ToString());

            using (var context = ContextManager.Instance.CreateContext(ConnectionMode.AutoOpen, TransactionMode.AutoCommit))
            {
                var user = SignInTestUser(context);

                var ef         = new EntityFactory(context);
                var federation = ef.LoadEntity <Federation>(Registry.AppSettings.FederationName);
                var format     = FileFormatFactory.Create(federation.FileFormatFactory).GetFileFormatDescription(typeof(Jhu.Graywulf.Format.DelimitedTextDataFile).FullName);

                var mydbds = federation.MyDBDatabaseVersion.GetUserDatabaseInstance(user).GetDataset();

                var source = new Jhu.Graywulf.Schema.Table()
                {
                    Dataset      = mydbds, // TODO: fix this
                    DatabaseName = mydbds.DatabaseName,
                    SchemaName   = schemaName,
                    TableName    = tableName
                };

                var etf = new ExportTablesFactory(context);
                var ji  = etf.ScheduleAsJob(
                    federation,
                    new TableOrView[] { source },
                    path,
                    format,
                    queue,
                    "");

                ji.Save();

                return(ji.Guid);
            }
        }
        /// <summary>
        /// Generates the SQL script to create the table
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <returns></returns>
        public string GenerateCreateDestinationTableQuery(DataTable schemaTable, Table destinationTable)
        {
            var columnlist = String.Empty;
            var keylist = String.Empty;
            //var nokey = false;

            int cidx = 0;
            //int kidx = 0;

            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                var column = Column.Create(schemaTable.Rows[i]);

                if (!column.IsHidden)
                {
                    if (cidx != 0)
                    {
                        columnlist += ",\r\n";
                    }

                    columnlist += String.Format(
                        "{0} {1} {2} NULL",
                        QuoteIdentifier(column.Name),
                        column.DataType.NameWithLength,
                        column.DataType.IsNullable ? "" : "NOT");

                    cidx++;
                }

                // This code can figure out key columns but it doesn't really work now
                /*
                if (column.IsKey)
                {
                    if (column.IsHidden)
                    {
                        // The key is not returned by the query, so no key can be specified on
                        // the final table
                        nokey = true;
                    }

                    if (kidx != 0)
                    {
                        keylist += ",\r\n";
                    }

                    keylist += String.Format("[{0}] ASC", column.Name);

                    kidx++;
                }
                 * */
            }

            // Key generation code removed, key cannot be figured out automatically for
            // join queries
            /*
            if (!String.IsNullOrEmpty(keylist) && !nokey)
            {
                columnlist += String.Format(
                    @",
            CONSTRAINT [{0}] PRIMARY KEY CLUSTERED ({1})",
                    String.Format("PK_{0}", destinationTable.TableName),
                    keylist);
            }
             * */

            return String.Format(
                "CREATE TABLE {0}.{1} ({2})",
                QuoteIdentifier(destinationTable.SchemaName),
                QuoteIdentifier(destinationTable.TableName),
                columnlist);
        }
Esempio n. 9
0
        /// <summary>
        /// Executest bulk copy to ingest data from the DataReader
        /// </summary>
        /// <param name="dr"></param>
        protected void ExecuteBulkCopy(IDataReader dr, Table destination)
        {
            // TODO: it can only import the first resultset from dr
            var cg = new SqlServerCodeGenerator();

            isBulkCopyCanceled = false;
            bulkCopyFinishedEvent = new AutoResetEvent(false);

            // Initialize bulk copy
            var sbc = new System.Data.SqlClient.SqlBulkCopy(destination.Dataset.ConnectionString);
            sbc.DestinationTableName = cg.GetResolvedTableName(destination);
            sbc.BatchSize = batchSize;
            sbc.BulkCopyTimeout = timeout;

            // Initialize events
            sbc.NotifyAfter = batchSize;
            sbc.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e)
            {
                //RowsAffected = e.RowsCopied;  // TODO: delete if not used
                e.Abort = isBulkCopyCanceled;
            };

            try
            {
                sbc.WriteToServer(dr);
            }
            finally
            {
                bulkCopyFinishedEvent.Set();
            }
        }
Esempio n. 10
0
 internal abstract void TruncateTable(Table table);
Esempio n. 11
0
 protected void DropTable(Table table)
 {
     DropTable(table.Dataset.ConnectionString, table.SchemaName, table.TableName);
 }
Esempio n. 12
0
        protected Guid ScheduleExportTableJob(string schemaName, string tableName, string path, QueueType queueType)
        {
            var queue = String.Format("QueueInstance:Graywulf.Controller.Controller.{0}", queueType.ToString());

            using (var context = ContextManager.Instance.CreateContext(ConnectionMode.AutoOpen, TransactionMode.AutoCommit))
            {
                var user = SignInTestUser(context);

                var ef = new EntityFactory(context);
                var federation = ef.LoadEntity<Federation>(Registry.AppSettings.FederationName);
                var format = FileFormatFactory.Create(federation.FileFormatFactory).GetFileFormatDescription(typeof(Jhu.Graywulf.Format.DelimitedTextDataFile).FullName);

                var mydbds = federation.MyDBDatabaseVersion.GetUserDatabaseInstance(user).GetDataset();

                var source = new Jhu.Graywulf.Schema.Table()
                {
                    Dataset = mydbds,  // TODO: fix this
                    DatabaseName = mydbds.DatabaseName,
                    SchemaName = schemaName,
                    TableName = tableName
                };

                var etf = new ExportTablesFactory(context);
                var ji = etf.ScheduleAsJob(
                    federation,
                    new TableOrView[] {source },
                    path,
                    format,
                    queue,
                    "");

                ji.Save();

                return ji.Guid;
            }
        }
Esempio n. 13
0
        protected void TruncateTable(Table table)
        {
            // TODO: move this to schema eventually
            string sql = String.Format("TRUNCATE TABLE [{0}].[{1}]", table.SchemaName, table.TableName);

            ExecuteCommandNonQuery(sql, table.Dataset.ConnectionString);
        }
Esempio n. 14
0
        protected bool IsTableExisting(Table table)
        {
            // TODO: rewrite this and move function to schema

            string sql = String.Format(
                "SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'U')",
                table.SchemaName,
                table.TableName);

            return (int)ExecuteCommandScalar(sql, table.Dataset.ConnectionString) == 1;
        }
Esempio n. 15
0
 /// <summary>
 /// Copies member variables
 /// </summary>
 /// <param name="old"></param>
 private void CopyMembers(Table old)
 {
     this.ObjectType = old.ObjectType;
 }
Esempio n. 16
0
 /// <summary>
 /// Copy constructor
 /// </summary>
 /// <param name="old"></param>
 public Table(Table old)
     : base(old)
 {
     CopyMembers(old);
 }
Esempio n. 17
0
 protected bool IsTableExisting(Table table)
 {
     return IsTableExisting(table.Dataset.ConnectionString, table.SchemaName, table.TableName);
 }
        /// <summary>
        /// Generates the SQL script to create the table
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <returns></returns>
        public string GenerateCreateDestinationTableQuery(DataTable schemaTable, Table destinationTable)
        {
            var sql = "CREATE TABLE [{0}].[{1}] ({2})";
            var columnlist = String.Empty;
            var keylist = String.Empty;
            var nokey = false;

            int cidx = 0;
            int kidx = 0;

            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                var column = new Column();
                TypeUtil.CopyColumnFromSchemaTableRow(column, schemaTable.Rows[i]);

                if (!column.IsHidden)
                {
                    if (cidx != 0)
                    {
                        columnlist += ",\r\n";
                    }

                    columnlist += String.Format(
                        "{0} {1} {2} NULL",
                        column.Name,
                        column.DataType.NameWithLength,
                        column.IsNullable ? "" : "NOT");

                    cidx++;
                }

                /*
                if (column.IsKey)
                {
                    if (column.IsHidden)
                    {
                        // The key is not returned by the query, so no key can be specified on
                        // the final table
                        nokey = true;
                    }

                    if (kidx != 0)
                    {
                        keylist += ",\r\n";
                    }

                    keylist += String.Format("[{0}] ASC", column.Name);

                    kidx++;
                }
                 * */
            }

            // Key generation code removed, key cannot be figured out automatically for
            // join queries
            /*
            if (!String.IsNullOrEmpty(keylist) && !nokey)
            {
                columnlist += String.Format(
                    @",
            CONSTRAINT [{0}] PRIMARY KEY CLUSTERED ({1})",
                    String.Format("PK_{0}", destinationTable.TableName),
                    keylist);
            }
             * */

            sql = String.Format(sql, destinationTable.SchemaName, destinationTable.TableName, columnlist);

            return sql;
        }
Esempio n. 19
0
 internal abstract void CreateTable(Table table);