public string CreateDescription(Energy.Source.Structure.Table table)
{
List <string> script = new List <string>();
string schema = "dbo";
script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Caption' , @value=N'Klucz' , @level0type='SCHEMA' , @level0name='"
+ schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='id'");
foreach (Energy.Source.Structure.Column column in table.Columns)
{
script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Caption',@value=N'" + column.Label
+ "',@level0type='SCHEMA',@level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name
+ "',@level2type='COLUMN',@level2name='" + column.Name + "'");
}
script.Add("");
script.Add("GO");
script.Add("");
script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'Klucz',@level0type='SCHEMA',@level0name='"
+ schema + "',@level1type='TABLE',@level1name='" + table.Name + "',@level2type='COLUMN',@level2name='id'");
foreach (Energy.Source.Structure.Column column in table.Columns)
{
string description = String.IsNullOrEmpty(column.Description) ? column.Label : column.Description;
script.Add("EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'" + description
+ "',@level0type='SCHEMA',@level0name='" + schema + "',@level1type='TABLE',@level1name='" + table.Name
+ "',@level2type='COLUMN',@level2name='" + column.Name + "'");
}
script.Add("");
script.Add("GO");
return(String.Join(Energy.Base.Text.NL, script.ToArray()));
}
private static void Test1()
{
Energy.Source.Structure.Table table = Energy.Source.Structure.Table.Create(typeof(UserTableRecord));
string query;
Energy.Interface.IDialect script = new Energy.Query.Dialect.MYSQL();
query = script.DropTable(table.Name);
if (db.Execute(query) < 0)
{
Console.WriteLine(db.ErrorMessage);
}
Console.WriteLine(query);
Energy.Query.Script scriptBuilder = new Energy.Query.Script();
scriptBuilder.Dialect = Energy.Enumeration.SqlDialect.SQLSERVER;
Energy.Query.Format format = Energy.Enumeration.SqlDialect.MYSQL;
query = script.CreateTable(table);
if (db.Execute(query) < 0)
{
Console.WriteLine(db.ErrorMessage);
}
Console.WriteLine(query);
Console.ReadLine();
}
public string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration)
{
if (table == null || table.Columns.Count == 0)
{
return("");
}
if (configuration == null)
{
configuration = Energy.Query.Style.Global;
}
List <string> script = new List <string>();
string identity = configuration.IdentityName;
string tableName = "[" + table.Name + "]";
script.Add("IF ( OBJECT_ID('" + tableName + "') ) IS NULL");
script.Add("");
script.Add("CREATE TABLE " + tableName);
script.Add("(");
if (!String.IsNullOrEmpty(table.Identity))
{
Energy.Source.Structure.Column column = table.Columns.Get(table.Identity) ?? new Energy.Source.Structure.Column(table.Identity);
string type = column.Type;
if (type == null || type.Length == 0)
{
type = configuration.IdentityType;
}
script.Add("\t[" + column.Name + "] " + type + " IDENTITY(1,1) NOT NULL ,");
identity = table.Identity;
}
else if (table.Columns.Get(identity) == default(Energy.Source.Structure.Column))
{
script.Add("\t[" + identity + "] " + configuration.IdentityType + " IDENTITY(1,1) NOT NULL ,");
}
foreach (Energy.Source.Structure.Column column in table.Columns)
{
if (!column.Ignore && !String.IsNullOrEmpty(column.Name) && column.Name != table.Identity)
{
script.Add("\t[" + column.Name + "] " + column.Type + " NOT NULL ,");
}
}
script.Add("");
script.Add("\tCONSTRAINT [PK_" + table.Name + "] PRIMARY KEY NONCLUSTERED");
script.Add("\t(");
script.Add("\t\t[" + identity + "] ASC");
script.Add("\t)");
script.Add("\tWITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )");
script.Add(")");
script.Add("ON [PRIMARY]");
script.Add("");
script.Add("GO");
return(String.Join(Energy.Base.Text.NL, script.ToArray()));
}
public string Merge(Energy.Source.Structure.Table table, bool compact)
{
string name = Format.Object(table.Name);
StringBuilder script = new StringBuilder();
for (int i = 0; i < table.Rows.Count; i++)
{
List <string> where = new List <string>();
List <string> column = new List <string>();
List <string> insert = new List <string>();
List <string> update = new List <string>();
//for (int j = 0; j < table.Keys.Count; j++)
//{
// string key = table.Keys[j];
// string value = table.Rows[i][table.Keys[j]].ToString();
// key = "[" + key + "]";
// value = "'" + value + "'";
// where.Add(key + " = " + value);
// column.Add(key);
// insert.Add(value);
//}
//foreach (string key in table.Rows[i].Keys)
//{
// if (table.Keys.Contains(key))
// continue;
// string value = table.Rows[i][key].ToString();
// update.Add("[" + key + "] = '" + value + "'");
//}
script.Append("IF NOT EXISTS ( SELECT 1 FROM " + name + " WHERE " + String.Join(" AND ", where.ToArray()) + " )");
if (!compact)
{
script.Append(Energy.Base.Text.NL);
}
script.Append(compact ? " " : "\t");
script.Append("INSERT INTO " + name + " ( " + String.Join(" , ", column.ToArray())
+ " ) VALUES ( " + String.Join(" , ", insert.ToArray()) + " )");
if (!compact)
{
script.Append(Energy.Base.Text.NL);
}
if (compact)
{
script.Append(" ");
}
script.Append("UPDATE " + name + " SET " + String.Join(" , ", update.ToArray()));
if (!compact)
{
script.Append(Energy.Base.Text.NL);
}
script.Append(compact ? " " : "\t");
script.Append("WHERE " + String.Join(" AND ", where.ToArray()));
script.Append(Energy.Base.Text.NL);
}
return(script.ToString().Trim());
}
public string CreateTable(Energy.Source.Structure.Table table)
{
StringBuilder s = new StringBuilder();
s.Append("CREATE TABLE IF NOT EXISTS ");
s.Append(Format.Object(table.Schema, table.Name));
s.Append(Energy.Base.Text.NL);
s.Append("(");
s.Append(Energy.Base.Text.NL);
string identity = Energy.Base.Text.Select(table.Identity, table.Columns.GetPrimaryName());
List <string> list = new List <string>();
for (int i = 0; i < table.Columns.Count; i++)
{
Energy.Source.Structure.Column column = table.Columns[i];
if (column.Ignore || string.IsNullOrEmpty(column.Name))
{
continue;
}
string line = string.Concat(Format.Object(column.Name), " ", column.Type, " ", (column.NotNull ? "NOT " : ""), "NULL");
if (column.Name == identity)
{
line += " PRIMARY KEY";
if (column.Increment > 0)
{
line += " AUTOINCREMENT";
}
}
list.Add(line);
}
string join = string.Concat(" ,", Energy.Base.Text.NL, "\t");
s.Append(string.Concat("\t", string.Join(join, list.ToArray())));
s.Append(Energy.Base.Text.NL);
s.Append(")");
s.Append(Energy.Base.Text.NL);
s.Append(Energy.Base.Text.NL);
return(s.ToString());
}
static void Main(string[] args)
{
Energy.Core.Tilde.WriteLine("~y~MySQL ~w~connection example");
Energy.Core.Log.Default.Destination.Add(new Energy.Core.Log.Target.Console());
Energy.Source.Connection <MySql.Data.MySqlClient.MySqlConnection> db;
db = new Energy.Source.Connection <MySql.Data.MySqlClient.MySqlConnection>();
db.ConnectionString = @"Server=127.0.0.1;Database=test;Uid=test;Pwd=test;";
db.OnCreate += Db_OnCreate;
db.OnClose += Db_OnClose;
string query = "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST";
//Console.WriteLine(db.Read))
Console.WriteLine(db.Fetch(query).ToPlain());
Energy.Core.Tilde.WriteLine("~c~Using 3 threads to retreive current timestamp 3 times with delay of 1 second each");
Energy.Core.Tilde.WriteLine("~y~Starting in persistent mode (one connection only)");
db.Persistent = true;
for (int i = 0; i < 3; i++)
{
Thread thread = new Thread(Work);
thread.Start(new Tuple <int, Energy.Source.Connection>(i, db));
}
Energy.Core.Tilde.Pause();
Energy.Core.Tilde.WriteLine("~y~Now in not persistent mode (opening new connection per each statement)");
db.Persistent = false;
for (int i = 0; i < 3; i++)
{
Thread thread = new Thread(Work);
thread.Start(new Tuple <int, Energy.Source.Connection>(i, db));
}
Energy.Core.Tilde.Pause();
db.ConnectionString = @"Server=127.0.0.1;Database=platoon;Uid=platoon;Pwd=platoon;";
db.Open();
Console.WriteLine(db.Scalar("SELECT CURRENT_TIMESTAMP()"));
Energy.Source.Structure.Table table = Energy.Source.Structure.Table.Create(typeof(UserTableRecord));
Energy.Interface.IDialect script = new Energy.Query.Dialect.MYSQL();
query = script.CreateDescription(table);
Console.WriteLine(query);
query = script.DropTable(table.Name);
if (db.Execute(query) < 0)
{
Console.WriteLine(db.ErrorMessage);
}
Console.WriteLine(query);
query = script.CreateTable(table);
if (db.Execute(query) < 0)
{
Console.WriteLine(db.ErrorMessage);
}
Console.WriteLine(query);
Console.ReadLine();
}
public virtual string CreateIndex(Energy.Source.Structure.Table table)
{
throw new NotImplementedException();
}
public virtual string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration)
{
throw new NotImplementedException();
}
public string Merge(Energy.Source.Structure.Table table)
{
return(Merge(table, false));
}
public string DropTable(Energy.Source.Structure.Table table)
{
throw new NotImplementedException();
}
/// <summary>
/// Create table
/// </summary>
/// <param name="table"></param>
/// <param name="configuration"></param>
/// <returns></returns>
public string CreateTable(Energy.Source.Structure.Table table, Energy.Query.Style configuration)
{
if (table == null || table.Columns.Count == 0)
{
return("");
}
if (configuration == null)
{
configuration = Energy.Query.Style.Global;
}
StringBuilder script = new StringBuilder();
if (table == null || table.Columns.Count == 0)
{
return("");
}
if (configuration == null)
{
configuration = Energy.Query.Style.Global;
}
string identity = configuration.IdentityName;
string tableName = table.Name;
script.Append("CREATE TABLE IF NOT EXISTS " + Format.Object(tableName));
script.Append(Energy.Base.Text.NL);
script.Append("(");
script.Append(Energy.Base.Text.NL);
Energy.Source.Structure.Column primary = null;
if (!string.IsNullOrEmpty(table.Identity))
{
identity = table.Identity;
}
else
{
primary = table.Columns.GetPrimary();
if (primary != null)
{
identity = primary.Name;
}
}
if (primary == null)
{
primary = table.Columns.Get(identity) ?? new Energy.Source.Structure.Column(table.Identity);
}
List <string> list = new List <string>();
string line;
string type = primary.Type;
if (type == null || type.Length == 0)
{
type = configuration.IdentityType;
}
line = string.Concat(Format.Object(primary.Name), " ", type, " NOT NULL");
if (Energy.Query.Type.IsNumeric(type))
{
line += " AUTO_INCREMENT";
}
list.Add(line);
for (int i = 0; i < table.Columns.Count; i++)
{
Energy.Source.Structure.Column column = table.Columns[i];
if (column.Ignore || string.IsNullOrEmpty(column.Name) || column.IsName(identity))
{
continue;
}
line = string.Concat(Format.Object(column.Name), " ", column.Type, " ", (column.NotNull ? "NOT " : ""), "NULL");
list.Add(line);
}
script.Append(string.Concat("\t", string.Join(" ," + Energy.Base.Text.NL + "\t", list.ToArray()), Energy.Base.Text.NL));
script.Append(")");
script.Append(Energy.Base.Text.NL);
string engine = string.IsNullOrEmpty(table.Engine) ? DefaultEngine : table.Engine;
if (!string.IsNullOrEmpty(engine))
{
script.Append("ENGINE = " + engine);
}
script.Append(";");
script.Append(Energy.Base.Text.NL);
return(script.ToString());
}
