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; }
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); }
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); }
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; }
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); }
/// <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(); } }
internal abstract void TruncateTable(Table table);
protected void DropTable(Table table) { DropTable(table.Dataset.ConnectionString, table.SchemaName, table.TableName); }
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; } }
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); }
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; }
/// <summary> /// Copies member variables /// </summary> /// <param name="old"></param> private void CopyMembers(Table old) { this.ObjectType = old.ObjectType; }
/// <summary> /// Copy constructor /// </summary> /// <param name="old"></param> public Table(Table old) : base(old) { CopyMembers(old); }
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; }
internal abstract void CreateTable(Table table);