public TableSizes GetTableSizes(DbConnection conn, LinkedDatabaseInfo linkedInfo)
{
var res = new TableSizes();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("tablesizes.sql"), linkedInfo);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string table = reader.SafeString("Table");
string schema = reader.SafeString("Schema");
var resItem = new TableSizesItem
{
RowCount = Int32.Parse(reader.SafeString("RowCount") ?? "0"),
TotalSpaceKB = Int32.Parse(reader.SafeString("TotalSpaceKB") ?? "0"),
UsedSpaceKB = Int32.Parse(reader.SafeString("UsedSpaceKB") ?? "0"),
UnusedSpaceKB = Int32.Parse(reader.SafeString("UnusedSpaceKB") ?? "0"),
};
res.Items[new NameWithSchema(schema, table)] = resItem;
}
}
}
return(res);
}
public TableSizes GetTableSizes(DbConnection conn, LinkedDatabaseInfo linkedInfo)
{
var res = new TableSizes();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "show table status";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string table = reader.SafeString("Name");
string engine = reader.SafeString("Engine");
var resItem = new TableSizesItem
{
RowCount = reader.SafeString("Rows").SafeIntParse(),
DataLengthKB = reader.SafeString("Data_length").SafeIntParse() / 1024,
IndexLengthKB = reader.SafeString("Index_length").SafeIntParse() / 1024,
};
resItem.TotalSpaceKB = resItem.IndexLengthKB + resItem.DataLengthKB;
res.Items[new NameWithSchema(null, table)] = resItem;
}
}
}
return(res);
}
public TableWriter(IShellContext context, IConnectionProvider connection, NameWithSchema name, TableInfo inputRowFormat, CopyTableTargetOptions options, TableInfo destinationTableOverride = null, LinkedDatabaseInfo linkedInfo = null, DataFormatSettings sourceDataFormat = null)
{
_connectionProvider = connection;
_linkedInfo = linkedInfo;
_name = name;
_inputRowFormat = inputRowFormat;
_queue = new CdlDataQueue(inputRowFormat);
_context = context;
_inserter = connection.Factory.CreateBulkInserter();
_inserter.SourceDataFormat = sourceDataFormat;
_connection = _connectionProvider.Connect();
_inserter.Connection = _connection;
_inserter.Factory = connection.Factory;
_inserter.LinkedInfo = _linkedInfo;
var db = context.GetDatabaseStructure(connection.ProviderString);
_inserter.DestinationTable = destinationTableOverride ?? db.FindTableLike(_name.Schema, _name.Name);
_inserter.CopyOptions = options;
_inserter.MessageLogger = _context;
_inserter.ServiceProvider = context.ServiceProvider;
_thread = new Thread(Run);
_thread.Start();
}
public void UpdateData(MultiTableUpdateScript script, LinkedDatabaseInfo linkedInfo)
{
if (script == null)
{
return;
}
string linkedInfoStr = linkedInfo != null?linkedInfo.ToString() : "";
int updrows = 0, updflds = 0;
foreach (var upd in script.Updates)
{
Put("^update %s%f ^set ", linkedInfoStr, upd.Table);
for (int i = 0; i < upd.Columns.Length; i++)
{
if (i > 0)
{
Put(", ");
}
Put("%i=%v", upd.Columns[i], upd.Values[i]);
}
Where(upd.Table, upd.CondCols, upd.CondValues);
Put(";&n");
updrows++;
updflds += upd.Values.Length;
}
}
public static string ReplaceLinkedServer(string sql, LinkedDatabaseInfo linkedInfo)
{
if (sql == null)
{
return(null);
}
string linkedServerSpec = "";
if (linkedInfo != null && linkedInfo.LinkedServerName != null)
{
linkedServerSpec = String.Format("[{0}].[{1}].", linkedInfo.LinkedServerName, linkedInfo.LinkedDatabaseName);
}
return(sql.Replace("[SERVER].", linkedServerSpec));
}
public override List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null)
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "SELECT datname FROM pg_database WHERE datistemplate = false";
using (var reader = cmd.ExecuteReader())
{
var res = new List <DatabaseOverviewInfo>();
while (reader.Read())
{
var item = new DatabaseOverviewInfo();
item.Name = reader["datname"].SafeToString();
res.Add(item);
}
return(res);
}
}
}
public void UpdateData(MultiTableUpdateScript script, LinkedDatabaseInfo linkedInfo)
{
}
public void UpdateData(TableInfo table, SingleTableDataScript script, LinkedDatabaseInfo linkedInfo)
{
}
public void DropTable(TableInfo obj, bool testIfExists, LinkedDatabaseInfo linkedInfo)
{
DropTable(obj, testIfExists);
}
public void CreateTable(TableInfo obj, LinkedDatabaseInfo linkedInfo)
{
CreateTable(obj);
}
public virtual List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null)
{
return(new List <DatabaseOverviewInfo>());
}
public override List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null)
{
if (includeDetails)
{
try
{
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("databasesizes.sql"), linkedInfo);
using (var reader = cmd.ExecuteReader())
{
var res = new List <DatabaseOverviewInfo>();
while (reader.Read())
{
var item = new DatabaseOverviewInfo();
item.Name = reader["DatabaseName"].SafeToString();
item.RowSizeKB = long.Parse(reader["RowSizeKB"].SafeToString());
item.LogSizeKB = long.Parse(reader["LogSizeKB"].SafeToString());
item.Collation = reader["Collation"].SafeToString();
item.RecoveryModel = reader["RecoveryModel"].SafeToString();
bool isSnapshot = reader["SnapshotIsolation"].SafeToString() == "1";
bool isReadCommitedSnapshot = reader["IsReadCommitedSnapshot"].SafeToString()?.ToLower() == "true";
if (isSnapshot)
{
if (isReadCommitedSnapshot)
{
item.Concurrency = "High";
}
else
{
item.Concurrency = "Middle";
}
}
else
{
item.Concurrency = "Low";
}
res.Add(item);
}
return(res);
}
}
}
catch (Exception err)
{
// use variant without details
ServiceProvider.LogError <SqlServerInterface>(err, "Error fetching database details");
}
}
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer("SELECT name FROM [SERVER].sys.databases order by name", linkedInfo);
using (var reader = cmd.ExecuteReader())
{
var res = new List <DatabaseOverviewInfo>();
while (reader.Read())
{
var item = new DatabaseOverviewInfo();
item.Name = reader["name"].SafeToString();
res.Add(item);
}
return(res);
}
}
}
public void UpdateData(TableInfo table, SingleTableDataScript script, LinkedDatabaseInfo linkedInfo)
{
if (script == null)
{
return;
}
int delcnt = 0, inscnt = 0, updrows = 0, updflds = 0;
string linkedInfoStr = linkedInfo != null?linkedInfo.ToString() : "";
foreach (var del in script.Deletes)
{
Put("^delete ^from %s%f", linkedInfoStr, table.FullName);
Where(table.FullName, del.CondCols, del.CondValues);
Put(";&n");
delcnt++;
}
foreach (var upd in script.Updates)
{
Put("^update %s%f ^set ", linkedInfoStr, table.FullName);
for (int i = 0; i < upd.Columns.Length; i++)
{
if (i > 0)
{
Put(", ");
}
Put("%i=%v", upd.Columns[i], new ValueTypeHolder(upd.Values[i], table.ColumnByName(upd.Columns[i]).CommonType));
}
Where(table.FullName, upd.CondCols, upd.CondValues);
Put(";&n");
updrows++;
updflds += upd.Values.Length;
}
ColumnInfo autoinc = null;
if (table != null)
{
autoinc = table.FindAutoIncrementColumn();
}
bool isIdentityInsert = false;
foreach (var ins in script.Inserts)
{
if (autoinc != null)
{
if (Array.IndexOf(ins.Columns, autoinc.Name) >= 0)
{
if (!isIdentityInsert)
{
AllowIdentityInsert(table.FullName, true);
}
isIdentityInsert = true;
}
else
{
if (isIdentityInsert)
{
AllowIdentityInsert(table.FullName, false);
}
isIdentityInsert = false;
}
}
var vals = new List <ValueTypeHolder>();
var insColumns = new List <string>();
for (int i = 0; i < ins.Columns.Length; i++)
{
var col = table.ColumnByName(ins.Columns[i]);
if (col != null)
{
insColumns.Add(ins.Columns[i]);
vals.Add(new ValueTypeHolder(ins.Values[i], col.CommonType));
}
}
if (insColumns.Count > 0)
{
Put("^insert ^into %s%f (%,i) ^values (%,v);&n", linkedInfoStr, table.FullName, insColumns, vals);
}
inscnt++;
}
if (isIdentityInsert)
{
AllowIdentityInsert(table.FullName, false);
}
}
