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);
