public override async Task <Database> GenerateDdl()
{
await Task.Run(() => {
connection.Open();
database.Name = Path.GetFileNameWithoutExtension(connection.DataSource);
// Get the tables.
using (var command = connection.CreateCommand()) {
command.CommandText = "SELECT tbl_name FROM sqlite_master WHERE type = 'table'";
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
var table = new Table();
table.Name = reader.GetString(0);
if (table.Name == "sqlite_sequence")
{
continue;
}
database.Table.Add(table);
}
}
}
// Get default Pragma configurations.
using (var command = connection.CreateCommand()) {
foreach (var config in SqlitePragmaConfigurations.Configurations)
{
string name = config.Name.Replace("sqlite.pragma.", "");
command.CommandText = "PRAGMA " + name;
using (var reader = command.ExecuteReader()) {
reader.Read();
try {
database.Configuration.Add(new Configuration()
{
Name = config.Name,
Value = reader[name].ToString(),
Description = config.Description
});
} catch { }
}
}
}
foreach (var table in database.Table)
{
// Get the columns
using (var command = connection.CreateCommand()) {
command.CommandText = "PRAGMA table_info(" + table.Name + ")";
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
var typ = reader["type"].ToString();
var column = new Column()
{
Name = reader["name"].ToString(),
IsPrimaryKey = Convert.ToBoolean(reader["pk"]),
Nullable = !Convert.ToBoolean(reader["notnull"]),
DbType = reader["type"].ToString(),
NetType = TypeTransformer.DbToNetType(reader["type"].ToString()),
};
if (column.IsPrimaryKey && column.DbType.ToLower() == "integer")
{
column.IsPrimaryKey = true;
}
table.Column.Add(column);
}
}
}
// Get the indexes.
using (var command = connection.CreateCommand()) {
command.CommandText = "SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND tbl_name = @TableName";
Utilities.addDbParameter(command, "@TableName", table.Name);
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
var index = new Index()
{
Name = reader.GetString(0),
Table = database.Table.First(t => t.Name == table.Name)
};
table.Index.Add(index);
}
}
}
if (table.Index.Count == 0)
{
continue;
}
using (var command = connection.CreateCommand()) {
var columns = new List <IndexColumn>();
foreach (var index in table.Index)
{
columns.Clear();
// Using a parameter here instead of a hard value seems to cause SQL syntax errors...
command.CommandText = "PRAGMA index_info ( " + index.Name + " )";
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
index.IndexColumn.Add(new IndexColumn()
{
Name = reader["name"].ToString()
});
}
}
}
}
}
//CREATE UNIQUE INDEX "main"."Test" ON "MangaTitles" ("Manga_id" ASC)
connection.Close();
});
return(database);
}
void TableColumn_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
Column column = sender as Column;
_DatabaseExplorer.SelectedDatabase._Modified = true;
// Temporarily remove this event so that we do not get stuck with a stack overflow.
column.PropertyChanged -= TableColumn_PropertyChanged;
switch (e.PropertyName)
{
case "Name":
ColumnReservedWords reserved_word_match;
if (Enum.TryParse(column.Name, true, out reserved_word_match))
{
column.Name = "FIX_COLUMN_NAME";
MessageBox.Show("Can not use reserved word '" + reserved_word_match.ToString() + "' for a column name.", "Column Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else
{
column.PostRename(_DatabaseExplorer.SelectedDatabase, previous_column.Name);
}
break;
case "DbType":
column.NetType = type_transformer.DbToNetType(column.DbType);
break;
case "NetType":
try {
var type = type_transformer.NetType(column.NetType);
if (type == null && NetTypes.Contains(column.NetType))
{
type = type_transformer.NetType("Int32");
}
column.DbLength = type.length;
column.DbType = type.db_type;
} catch (Exception) {
MessageBox.Show("Type is not allowed in the selected database type.", "Invalid Option");
}
break;
case "IsAutoIncrement":
if (column.IsAutoIncrement)
{
if (new string[] { "Int16", "Int32", "Int64" }.Contains(column.NetType) == false)
{
MessageBox.Show("An auto incremented column has to be an integer type.", "Invalid Option");
column.IsAutoIncrement = false;
}
else if (column.Nullable)
{
MessageBox.Show("Can not auto increment a column that is nullable.", "Invalid Option");
column.IsAutoIncrement = false;
}
}
break;
case "Nullable":
if (column.Nullable && column.IsAutoIncrement)
{
MessageBox.Show("Can not make an auto incremented value nullable.", "Invalid Option");
column.Nullable = false;
}
if (column.Nullable && DefaultNetTypes.Contains(column.NetType) == false && NetTypes.Contains(column.NetType))
{
MessageBox.Show("Can not make an enum value nullable.", "Invalid Option");
column.Nullable = false;
}
break;
case "DefaultValue":
if (column.IsAutoIncrement)
{
MessageBox.Show("An auto incremented value can not have a default value.", "Invalid Option");
column.DefaultValue = null;
}
break;
}
previous_column = column.Clone();
// Rebind this event to allow us to listen again.
column.PropertyChanged += TableColumn_PropertyChanged;
}
public override async Task <Database> GenerateDdl()
{
await Task.Run(() => {
var mwb_xml = new XmlDocument();
if (xml_format)
{
mwb_xml.Load(mwb_file_location);
}
else
{
using (var mwb_archive = ZipFile.OpenRead(mwb_file_location)) {
var zip_mwb = mwb_archive.GetEntry("document.mwb.xml");
if (zip_mwb == null)
{
throw new FileNotFoundException("Could not find the 'document.mwb.xml' inside the specified MWB file.");
}
using (var stream = zip_mwb.Open()) {
mwb_xml.Load(stream);
}
}
}
var xml_db_schema = mwb_xml.SelectSingleNode(@"/data
/value[@struct-name='workbench.Document']
/value[@content-struct-name='workbench.physical.Model']
/value[@struct-name='workbench.physical.Model']
/value[@struct-name='db.mysql.Catalog']
/value[@content-struct-name='db.mysql.Schema']
/value[@struct-name='db.mysql.Schema']" );
var xml_tables = xml_db_schema.SelectNodes(@"
value[@content-struct-name='db.mysql.Table']
/value" );
database.Name = xml_db_schema.SelectSingleNode("value[@key='name']").InnerText;
database.TargetDb = DbProvider.MySQL;
//database.Name = Path.GetFileNameWithoutExtension(connection.DataSource);
// Get the tables.
foreach (XmlNode xml_table in xml_tables)
{
var name_node = xml_table.SelectSingleNode("value[@key=\"name\"]");
var table = new Table()
{
Name = name_node.InnerText
};
var xml_columns = xml_table.SelectNodes(@"value[@key='columns']/value");
foreach (XmlNode xml_column in xml_columns)
{
var column = new Column()
{
Name = xml_column.SelectSingleNode(@"value[@key='name']").InnerText,
Description = xml_column.SelectSingleNode(@"value[@key='comment']").InnerText,
DefaultValue = xml_column.SelectSingleNode(@"value[@key='defaultValue']").InnerText,
};
var auto_increment = xml_column.SelectSingleNode(@"value[@key='autoIncrement']").InnerText;
column.IsAutoIncrement = (auto_increment == "0" || auto_increment == null) ? false : true;
var length = xml_column.SelectSingleNode(@"value[@key='length']").InnerText;
column.DbLength = (length == "-1" || length == null) ? 0 : Convert.ToInt32(length);
var nullable = xml_column.SelectSingleNode(@"value[@key='isNotNull']").InnerText;
column.Nullable = (nullable == "0" || nullable == null) ? true : false;
var db_type = xml_column.SelectSingleNode(@"link[@struct-name='db.SimpleDatatype']");
if (db_type != null)
{
column.DbType = db_type.InnerText.Replace("com.mysql.rdbms.mysql.datatype.", "").ToUpper();
}
else
{
db_type = xml_column.SelectSingleNode(@"link[@struct-name='db.UserDatatype']");
if (db_type != null)
{
column.DbType = db_type.InnerText.Replace("com.mysql.rdbms.mysql.userdatatype.", "").ToUpper();
}
else
{
throw new Exception("Unknown data type for column '" + column.Name + "' in table '" + table.Name + "'.");
}
}
try {
column.NetType = TypeTransformer.DbToNetType(column.DbType);
} catch {
throw new Exception("Unknown data type for column '" + column.Name + "' in table '" + table.Name + "'.");
}
column.IsPrimaryKey = column.IsAutoIncrement;
table.Column.Add(column);
}
database.Table.Add(table);
}
});
return(database);
}