public static TableInfo GetInstance(DataConnectionSpec dcs)
{
if (dcs == null)
{
return(new MySqlTableInfo(null));
}
DataVendor vendor = DataManager.GetVendor(dcs);
switch (vendor)
{
case DataVendor.MySql:
return(new MySqlTableInfo(dcs));
case DataVendor.SqlServer:
return(new SqlServerTableInfo(dcs));
case DataVendor.Oracle:
return(new OracleTableInfo(dcs));
case DataVendor.PostgreSql:
return(new PostgreSqlTableInfo(dcs));
}
throw new NotImplementedException(getDisplayMember("GetInstance", "TableInfo class for {0} is not mapped through TableInfo.GetInstance().", vendor.ToString()));
}
private void btnTest_Click(object sender, EventArgs e)
{
string providerName = rbFromDatabaseMySQL.Checked ? "mysql" :
rbFromDatabaseOracle.Checked ? "oracle" :
rbFromDatabasePostgreSQL.Checked ? "postgresql" :
"sqlserver";
_dcs = new DataConnectionSpec {
ConnectionString = cboFromConnectionString.Text, ProviderName = providerName
};
using (var dm = DataManager.Create(_dcs)) {
var output = dm.TestLogin();
if (String.IsNullOrEmpty(output))
{
MessageBox.Show(this, "Connection Succeeded.", "Success", MessageBoxButtons.OK, MessageBoxIcon.None);
btnMunge.Enabled = true;
}
else
{
MessageBox.Show(this, "Failed: " + output, "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
btnMunge.Enabled = false;
}
}
}
public override void RebuildIndexes(List <TableInfo> tables)
{
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
DataConnectionSpec dsc = DataConnectionSpec.Clone(dm);
// MySQL creates indexes orders of magnitude faster (in InnoDB engine at least)
// if we issue a single ALTER TABLE statement with multiple indexes on it.
ProcessTables(tables, ti => {
if (ti.IsSelected)
{
string toRun = null;
try {
toRun = ((MySqlTableInfo)ti).GenerateRebuildAllIndexesForTable();
showProgress(getDisplayMember("RebuildIndexes{progress}", "Rebuilding all indexes for {0}...", ti.TableName), 0, 0);
dm.DataConnectionSpec.CommandTimeout = 3600; // allow one hour for each table index recreation (!!!)
if (!String.IsNullOrEmpty(toRun))
{
dm.Write(toRun);
}
} catch (Exception ex) {
throw new InvalidOperationException(ex.Message + ". SQL=" + toRun, ex);
}
}
});
}
}
private Creator getToCreator(bool checkObjectType)
{
string providerName = rbToDatabaseMySQL.Checked ? "mysql" :
rbToDatabaseOracle.Checked ? "oracle" :
rbToDatabasePostgreSQL.Checked ? "postgresql" :
"sqlserver";
DataConnectionSpec dcs = DataConnectionSpec.Parse(providerName, cboToConnectionString.Text);
Creator c = Creator.GetInstance(dcs);
c.OnProgress += new ProgressEventHandler(c_OnProgress);
if (checkObjectType)
{
if (_tables != null && _tables.Count > 0)
{
// make sure the table objects we have are of the appropriate database type
bool ok = false;
switch (providerName.ToLower())
{
case "mysql":
ok = (_tables[0] is MySqlTableInfo);
break;
case "oracle":
case "oracleclient":
case "ora":
ok = (_tables[0] is OracleTableInfo);
break;
case "mssql":
case "sql":
case "sqlclient":
case "sqlserver":
ok = (_tables[0] is SqlServerTableInfo);
break;
case "pgsql":
case "postgresql":
ok = (_tables[0] is PostgreSqlTableInfo);
break;
default:
ok = false;
break;
}
if (!ok)
{
MessageBox.Show("The table objects used to fill the list are of a different type than your destination database.\nPlease have the From: database type match the To: (even if you're pulling from CSV or XML)");
return(null);
}
}
}
return(c);
}
public TableInfo(DataConnectionSpec dcs)
: base(dcs)
{
Fields = new List <FieldInfo>();
Indexes = new List <IndexInfo>();
Constraints = new List <ConstraintInfo>();
_primaryKeyValues = new Dictionary <string, object>();
SyncAction = SyncAction.Create;
}
protected override void mungeConnectionSpec(DataConnectionSpec dcs)
{
//// make it a privileged connection if need be
//if (("" + dcs.UserName).ToUpper() == "SYS"){
// if (!dcs.ConnectionString.ToLower().Contains("dba privilege=")){
// dcs.ConnectionString += "; DBA Privilege=SYSDBA;";
// }
//}
}
// protected override void initConnectionString(DataConnectionSpec dcs, string serverName, string databaseName) {
// string connString = "Data Source=" + serverName + ";";
// if (!String.IsNullOrEmpty(databaseName)) {
// connString += "Initial Catalog=" + databaseName + ";";
// }
// if (dcs.UseWindowsAuthentication) {
//// if (dcs.UseWindowsAuthentication || String.IsNullOrEmpty(dcs.UserName)) {
//// connString += "Integrated Security=SSPI;";
// connString += "Trusted_Connection=True;";
// } else {
// connString += dcs.UserNameMoniker + "=;" + dcs.PasswordMoniker + "=;";
// connString = base.replaceNameValuePair(connString, dcs.UserNameMoniker, dcs.UserName);
// connString = base.replaceNameValuePair(connString, dcs.PasswordMoniker, dcs.Password);
// }
// dcs.ConnectionString = connString;
// }
protected override void mungeConnectionSpec(DataConnectionSpec dcs)
{
if (String.IsNullOrEmpty(dcs.UserName) && !String.IsNullOrEmpty(dcs.ConnectionString))
{
//if (!dcs.ConnectionString.ToLower().Contains("integrated security=sspi")) {
// dcs.ConnectionString = dcs.ConnectionString += ";Integrated Security=SSPI";
//}
if (!dcs.ConnectionString.ToLower().Contains("trusted_connection=true"))
{
dcs.ConnectionString = dcs.ConnectionString += ";Trusted_Connection=True";
}
}
}
private Creator getFromCreator()
{
string providerName = rbFromDatabaseMySQL.Checked ? "mysql" :
rbFromDatabaseOracle.Checked ? "oracle" :
rbFromDatabasePostgreSQL.Checked ? "postgresql" :
"sqlserver";
DataConnectionSpec dcs = new DataConnectionSpec {
ConnectionString = cboFromConnectionString.Text, ProviderName = providerName
};
Creator c = Creator.GetInstance(dcs);
c.OnProgress += new ProgressEventHandler(c_OnProgress);
return(c);
}
public DataConnectionSpec GenerateDataConnectionSpec()
{
var dcs = new DataConnectionSpec {
ProviderName = DatabaseEngineProviderName,
ServerName = DatabaseEngineServerName,
DatabaseName = DatabaseEngineDatabaseName,
UserName = DatabaseEngineUserName,
Password = DatabaseEnginePassword,
UseWindowsAuthentication = UseWindowsAuthentication,
SID = DatabaseEngineSID
};
return(dcs);
}
private DataConnectionSpec getDataConnectionSpec()
{
// use it as a direct dll reference (aka connect via database only, no web services involved)
var dcs = new DataConnectionSpec {
ProviderName = _conn.DatabaseEngineProviderName,
ServerName = _conn.DatabaseEngineServerName,
DatabaseName = _conn.DatabaseEngineDatabaseName,
UserName = _conn.DatabaseEngineUserName,
Password = _conn.DatabaseEnginePassword,
UseWindowsAuthentication = _conn.UseWindowsAuthentication
};
//string token = SecureData.Login(_conn.GrinGlobalUserName, _conn.GrinGlobalPassword, dcs);
return(dcs);
}
private void button1_Click(object sender, EventArgs e)
{
var dsc = new DataConnectionSpec(ddlProvider.Text, null, null, txtValue.Text);
var f = new frmDatabaseLoginPrompt();
var util = DatabaseEngineUtil.CreateInstance(dsc.ProviderName, Toolkit.ResolveFilePath(@".\gguac.exe", false), "SQLExpress");
var dr = f.ShowDialog(this, util, false, false, false, null, null, false, false);
if (dr == DialogResult.OK)
{
txtValue.Text = f.txtConnectionstring.Text;
ddlProvider.SelectedIndex = ddlProvider.FindString(f.DatabaseEngineUtil.EngineName);
}
}
public override DataConnectionSpec GetDataConnectionSpec(string databaseName, string userName, string password)
{
DataConnectionSpec dcs = new DataConnectionSpec {
ProviderName = EngineName,
DatabaseName = databaseName,
InstanceName = "",
ServerName = ServerName,
ServiceName = ServiceName,
Port = Port,
UseWindowsAuthentication = UseWindowsAuthentication,
UserName = userName,
Password = password,
};
return(dcs);
}
public override DataConnectionSpec GetDataConnectionSpec(string databaseName, string userName, string password)
{
DataConnectionSpec dcs = new DataConnectionSpec {
ProviderName = EngineName,
DatabaseName = databaseName,
InstanceName = _instanceName,
ServerName = ServerName,
Port = Port,
UseWindowsAuthentication = UseWindowsAuthentication,
UserName = userName,
Password = password
};
return(dcs);
// dcs.ProviderName = EngineName;
// string initialCatalog = (String.IsNullOrEmpty(databaseName) ? "" : "Initial Catalog=" + databaseName + ";");
// var instanceName = GetPartialInstanceName(_instanceName);
// string separator = null;
// if (String.IsNullOrEmpty(instanceName)) {
// // default instance, no separator
// separator = "";
// } else {
// if (Toolkit.ToInt32(instanceName, -1) > -1) {
// // using port, need a comma
// separator = ", ";
// } else {
// // using instance name, need a backslash
// separator = @"\";
// }
// }
// dcs.UseWindowsAuthentication = UseWindowsAuthentication;
// if (UseWindowsAuthentication) {
// // assume integrated security -- either null user or specified SA and empty password.
//// dcs.ConnectionString = String.Format(@"Data Source={0}" + separator + @"{1};{2}Integrated Security=SSPI;", ServerName, instanceName, initialCatalog);
// dcs.ConnectionString = String.Format(@"Data Source={0}" + separator + @"{1};{2}Trusted_Connection=True;", ServerName, instanceName, initialCatalog);
// } else {
// dcs.ConnectionString = String.Format(@"Data Source={0}" + separator + @"{1};{2}User Id='{3}';Password='******';", ServerName, instanceName, initialCatalog, ("" + userName).Replace("'", "''"), ("" + password).Replace("'", "''"));
// }
// return dcs;
}
static void export(string[] args)
{
// /export "target folder here" "provider_name_here" "connection_string_here" db_name_here
string folder = args[1].Replace("\"", "");
string during = "Connecting to database";
try {
// parse the dsc into a DataConnectionSpec object
DataConnectionSpec dsc = new DataConnectionSpec {
ProviderName = args[2].Replace("\"", ""),
ConnectionString = args[3].Replace("\"", ""),
};
__frmProgress = new frmProgress();
__frmProgress.Show();
__frmProgress.Text = "Exporting Data from " + args[4] + " Database...";
// read table info from database
Creator c = Creator.GetInstance(dsc);
c.OnProgress += new ProgressEventHandler(c_OnProgress);
updateProgress("Loading table information...");
during = "loading table information";
List <TableInfo> tables = c.LoadTableInfo(args[4], null, false, int.MaxValue);
// save table schema to file
c.SaveTableInfoToXml((folder + @"\__schema.xml").Replace(@"\\", @"\"), tables);
// copy the data from the database
during = "copying data";
c.CopyDataFromDatabase(folder, args[4], tables);
updateProgress("Done");
} catch (Exception ex) {
updateProgress("Error while " + during + ": " + ex.Message);
// MessageBox.Show("Error while " + during + ": " + ex.Message);
throw;
}
__frmProgress.btnDone.Enabled = true;
__frmProgress.Close();
//while (__frmProgress.Visible) {
// Thread.Sleep(1000);
// Application.DoEvents();
//}
}
private void btnCopyDataFrom_Click(object sender, EventArgs e)
{
clearWorker();
List <string> tableNames = new List <string>();
for (int i = 0; i < lvFromTables.Items.Count; i++)
{
ListViewItem lvi = lvFromTables.Items[i];
if (lvi.Checked)
{
tableNames.Add(lvi.Text);
}
}
DataConnectionSpec dcs = new DataConnectionSpec {
ConnectionString = cboConnectionStringFrom.Text, ProviderName = getFromProviderName(), CommandTimeout = COMMAND_TIMEOUT
};
if (dcs.ConnectionString.Contains("Driver="))
{
// assume odbc
dcs.ProviderName = "ODBC";
}
Creator c = Creator.GetInstance(dcs);
string schemaName = txtFromDatabaseOwner.Text;
string dest = txtDestinationFolder.Text;
backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker1_ProgressChanged);
backgroundWorker1.DoWork += new DoWorkEventHandler(delegate(object sender2, DoWorkEventArgs evt2) {
_timer = new HighPrecisionTimer("timer", true);
c.OnProgress += new ProgressEventHandler(c_OnProgress);
backgroundWorker1.ReportProgress(0, new ProgressEventArgs("Gathering schema information from database...", 0, 0));
List <TableInfo> tables = c.LoadTableInfo(schemaName, tableNames, false);
c.CopyDataFromDatabase(dest, schemaName, tables);
});
backgroundWorker1.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker1_RunWorkerCompleted);
backgroundWorker1.RunWorkerAsync();
}
public override void CreateConstraints(List <TableInfo> tables, bool createForeignConstraints, bool createSelfReferentialConstraints)
{
using (DataManager dm = DataManager.Create(DataConnectionSpec)) {
DataConnectionSpec dsc = DataConnectionSpec.Clone(dm);
// MySQL creates constraints orders of magnitude faster (in InnoDB engine at least)
// if we issue a single ALTER TABLE statement with multiple constraints on it.
ProcessTables(tables, ti => {
if (ti.IsSelected)
{
string toRun = ((MySqlTableInfo)ti).GenerateCreateAllConstraintsForTable(createForeignConstraints, createSelfReferentialConstraints);
showProgress(getDisplayMember("CreateConstraints{progress}", "Creating all constraints for {0}...", ti.TableName), 0, 0);
dm.DataConnectionSpec.CommandTimeout = 3600; // allow one hour for each table index recreation (!!!)
if (!String.IsNullOrEmpty(toRun))
{
dm.Write(toRun);
}
}
});
}
}
private void btnActionSearchGo_Click(object sender, EventArgs e)
{
Creator c = getToCreator(true);
if (c == null)
{
return;
}
// first make sure checkboxes line up with our 'selected' tables
selectTables();
DataConnectionSpec dcs = new DataConnectionSpec {
ConnectionString = cboActionGenerateSearch.Text, ProviderName = "MySQL.Data"
};
background((sentBy, args) => {
string output = c.GenerateIndexSelectFromTableInfo(_tables, dcs);
StringBuilder sb = new StringBuilder();
foreach (TableInfo ti in _tables)
{
string pkName = ti.PrimaryKey == null ? null : ti.PrimaryKey.Name.ToLower();
if (!String.IsNullOrEmpty(pkName))
{
// not all tables are indexed for searching -- only certain ones.
// for ones that are not, just add a comment
try {
// sb.AppendLine(ti.GenerateIndexSelectScript(sit));
} catch {
sb.AppendLine("\r\n####### Table '" + ti.TableName + "' is not indexed for searching ######\r\n");
}
}
}
showProgress("SCHEMA:" + output);
});
}
private void btnListTables_Click(object sender, EventArgs e)
{
DataConnectionSpec dcs = new DataConnectionSpec {
ConnectionString = cboConnectionStringFrom.Text, ProviderName = getFromProviderName(), CommandTimeout = COMMAND_TIMEOUT
};
if (dcs.ConnectionString.Contains("Driver="))
{
// assume odbc
dcs.ProviderName = "ODBC";
}
Creator c = Creator.GetInstance(dcs);
lvFromTables.Items.Clear();
List <string> names = c.ListTableNames(txtFromDatabaseOwner.Text);
foreach (string nm in names)
{
ListViewItem lvi = new ListViewItem(nm);
lvi.Checked = chkFromList.Checked;
lvFromTables.Items.Add(lvi);
}
toggleCopyFrom(names.Count > 0);
}
/// <summary>
/// Connects to the given database and returns a table object based on data in sys_table / sys_table_field / sys_table_field_lang tables for the given tableName. Returns an empty definition if not found.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dcs"></param>
/// <param name="languageID"></param>
/// <param name="loadFriendlyNamesForAllLanguages"></param>
/// <returns></returns>
public static ITable Map(string tableName, DataConnectionSpec dcs, int languageID, bool loadFriendlyNamesForAllLanguages)
{
using (DataManager dm = (dcs == null ? DataManager.Create() : DataManager.Create(dcs))){
return(Map(tableName, dm, languageID, loadFriendlyNamesForAllLanguages));
}
}
public static string GetDisplayMember(this Page pg, string pageName, DataConnectionSpec dcs, int languageId, string resourceName, string defaultValue, params string[] substitutes)
{
return(ResourceHelper.GetDisplayMember(dcs, "WebTool", pageName, resourceName, languageId, defaultValue, substitutes));
}
internal SqliteTableInfo(DataConnectionSpec dcs)
: base(dcs)
{
}
internal MySqlCreator(DataConnectionSpec dcs)
: base(dcs)
{
}
public Query(string sql, DataConnectionSpec dcs, int languageID, bool errorOnMissingRelationship, bool errorOnMultipleRelationships)
: base(sql, dcs, languageID, errorOnMissingRelationship, errorOnMultipleRelationships)
{
}
internal PostgreSqlTableInfo(DataConnectionSpec dcs)
: base(dcs)
{
_delimiter = "";
}
public Generator(DataConnectionSpec dcs)
{
_dataConnectionSpec = dcs;
}
internal SqlServerTableInfo(DataConnectionSpec dcs)
: base(dcs)
{
}
public BaseInfo(DataConnectionSpec dcs)
{
DataConnectionSpec = dcs;
SyncAction = SyncAction.Create;
_delimiter = "`";
}
internal SqlServerCreator(DataConnectionSpec dcs)
: base(dcs)
{
}
internal OracleCreator(DataConnectionSpec dcs)
: base(dcs)
{
}
internal SqlDataManager(DataConnectionSpec dcs)
: base(dcs, "User Id", "Password", DataVendor.SqlServer)
{
}
