public Exporter(PathManager mgr, TreeNode<IDataPath> pt, Configuration cfg)
{
this.mgr = mgr;
this.cfg = cfg;
this.xml = new XmlDbFile { XmlDbFolder = cfg.XmlDbFolder };
this.fileName = cfg.OutputFile;
if (pt.Item is Locator)
{
this.tname = mgr.GetPathFrom<TableName>(pt);
this.dname = tname.DatabaseName;
this.sname = dname.ServerName;
}
else if (pt.Item is TableName)
{
this.tname = (TableName)pt.Item;
this.dname = tname.DatabaseName;
this.sname = dname.ServerName;
}
else if (pt.Item is DatabaseName)
{
this.tname = null;
this.dname = (DatabaseName)pt.Item;
this.sname = dname.ServerName;
}
else if (pt.Item is ServerName)
{
this.tname = null;
this.dname = null;
this.sname = (ServerName)pt.Item;
}
}
public ShellContext(Configuration cfg)
{
this.cfg = cfg;
this.mgr = new PathManager(cfg);
this.commandee = new Commandee(mgr);
string server = cfg.GetValue<string>(Configuration._SERVER0);
ConnectionProvider pvd = null;
if (!string.IsNullOrEmpty(server))
pvd = cfg.GetProvider(server);
if (pvd != null)
{
theSide = new Side(pvd);
ChangeSide(theSide);
}
else if (cfg.Providers.Count() > 0)
{
theSide = new Side(cfg.Providers.First());
ChangeSide(theSide);
}
else
{
stdio.ErrorFormat("database server not defined");
}
}
public PathManager(Configuration cfg)
{
tree = new Tree<IDataPath>();
current = RootNode;
this.cfg = cfg;
var snames = cfg.ServerNames;
foreach (var sname in snames)
{
var snode = new TreeNode<IDataPath>(sname);
tree.Nodes.Add(snode);
}
}
public Command(string line, Configuration cfg)
{
this.cfg = cfg;
this.HasDefinition = false;
this.IsVertical = false;
this.top = cfg.Limit_Top;
if (string.IsNullOrEmpty(line))
return;
int k = parseAction(line, out Action);
this.args = line.Substring(k);
string[] L;
this.badcommand = !parseArgument(this.args, out L);
if (L.Length > 0 && !L[0].StartsWith("/"))
this.arg1 = L[0];
if (L.Length > 1 && !L[1].StartsWith("/"))
this.arg2 = L[1];
for (int i = 0; i < L.Length; i++)
{
string a = L[i];
if (a.StartsWith("/"))
{
switch (a)
{
case "/def":
HasDefinition = true;
break;
case "/s":
IsSchema = true;
break;
case "/t":
IsVertical = true;
break;
case "/if":
HasIfExists = true;
break;
case "/p":
HasPage = true;
break;
case "/all":
top = 0;
break;
case "/r":
hasRowId = true;
break;
case "/refresh":
Refresh = true;
break;
case "/?":
HasHelp = true;
break;
case "/pk":
HasPrimaryKey = true;
break;
case "/fk":
HasForeignKey = true;
break;
case "/ik":
HasIdentityKey = true;
break;
case "/dep":
HasDependency = true;
break;
case "/ind":
HasIndex = true;
break;
case "/sto":
HasStorage = true;
break;
case "/json":
ToJson = true;
break;
case "/c#":
ToCSharp = true;
break;
default:
if (a.StartsWith("/top:"))
int.TryParse(a.Substring(5), out top);
else if (a.StartsWith("/col:"))
columns = a.Substring(5);
else
options.Add(a);
break;
}
}
else if (a.StartsWith("+") || a.StartsWith("-"))
{
options.Add(a);
}
else
{
if (paths.Count > 1)
this.badcommand = true;
paths.Add(a);
}
}
}
static void Main(string[] args)
{
Console.Title = "sqlcon";
Console.WriteLine("SQL Console [Version {0}]", SysExtension.ApplicationVerison);
Console.WriteLine("Copyright (c) 2014-2016 Datconn. All rights reserved.");
Console.WriteLine();
Tie.Constant.MAX_CPU_REG_NUM = 600;
string cfgFile = _USER_CFG;
cfgFile = PrepareCfgFile(cfgFile, false);
int i = 0;
while (i < args.Length)
{
switch (args[i++])
{
case "/cfg":
if (i < args.Length && !args[i].StartsWith("/"))
{
cfgFile = args[i++];
goto L1;
}
else
{
stdio.WriteLine("/cfg configuration file missing");
return;
}
case "/h":
case "/?":
Help();
return;
}
}
L1:
var cfg = new Configuration();
try
{
if (!cfg.Initialize(cfgFile))
return;
}
catch (Exception ex)
{
stdio.WriteLine("error on configuration file {0}, {1}:", cfgFile, ex.Message);
return;
}
#if DEBUG
var site = new CompareConsole(cfg);
site.Run(args);
#else
try
{
var site = new CompareConsole(cfg);
site.Run(args);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
//stdio.ShowError(ex.StackTrace);
stdio.ErrorFormat("fatal error, hit any key to exit");
stdio.ReadKey();
}
finally
{
stdio.Close();
}
#endif
}
public CompareConsole(Configuration cfg)
{
this.cfg = cfg;
}
public string Run(ActionType compareType, TableName[] N1, TableName[] N2, Configuration cfg, string[] exceptColumns)
{
DatabaseName dname1 = Side1.DatabaseName;
DatabaseName dname2 = Side2.DatabaseName;
stdio.WriteLine("server1: {0} default database:{1}", Side1.Provider.DataSource, dname1.Name);
stdio.WriteLine("server2: {0} default database:{1}", Side2.Provider.DataSource, dname2.Name);
if (!Exists(dname1) || !Exists(dname2))
return string.Empty;
StringBuilder builder = new StringBuilder();
builder.AppendFormat("-- sqlcon:", Side1.Provider.DataSource, dname1.Name).AppendLine();
builder.AppendFormat("-- compare server={0} db={1}", Side1.Provider.DataSource, dname1.Name).AppendLine();
builder.AppendFormat("-- server={0} db={1} @ {2}", Side2.Provider.DataSource, dname2.Name, DateTime.Now).AppendLine();
CancelableWork.CanCancel(cts =>
{
foreach (var tname1 in N1)
{
if (cts.IsCancellationRequested)
return;
TableName tname2 = N2.Where(t => t.ShortName == tname1.ShortName).FirstOrDefault();
if (tname2 == null)
{
tname2 = new TableName(dname2, tname1.SchemaName, tname1.ShortName);
}
if (compareType == ActionType.CompareData && cfg.compareExcludedTables.FirstOrDefault(t => t == tname1.Name) != null)
{
stdio.WriteLine("{0} is excluded", tname1);
continue;
}
if (tname2.Exists())
builder.Append(CompareTable(compareType, CompareSideType.compare, tname1, tname2, cfg.PK, exceptColumns));
else
{
if (compareType == ActionType.CompareSchema)
{
string sql = tname1.GenerateCluase();
stdio.WriteLine(sql);
builder.Append(sql);
}
else
{
stdio.WriteLine("{0} doesn't exist", tname2);
}
}
}
});
return builder.ToString();
}
public SqlShell(Configuration cfg)
: base(cfg)
{
}
public void open(Command cmd, Configuration cfg)
{
if (cmd.HasHelp)
{
stdio.WriteLine("open files in the editor");
stdio.WriteLine("open files");
stdio.WriteLine("options:");
stdio.WriteLine(" log : open log file");
stdio.WriteLine(" viewer : open GUI viewer to see the last data table retrieved");
stdio.WriteLine(" output : open output file");
stdio.WriteLine(" config [/s] : open user configure file, /s open system configurate");
stdio.WriteLine(" dpo : open table class output directory");
stdio.WriteLine(" dc : open data contract class output directory");
stdio.WriteLine(" l2s : open Linq to SQL class output directory");
stdio.WriteLine(" release : open release notes");
return;
}
string path;
switch (cmd.arg1)
{
case "output":
stdio.OpenEditor(cfg.OutputFile);
break;
case "log":
stdio.OpenEditor(Context.GetValue<string>("log"));
break;
case "config":
if (cmd.IsSchema)
stdio.OpenEditor("sqlcon.cfg");
else
stdio.OpenEditor(cfg.CfgFile);
break;
case "release":
stdio.OpenEditor("ReleaseNotes.txt");
break;
case "dpo":
path = cfg.GetValue<string>("dpo.path", $"{Configuration.MyDocuments}\\DataModel\\Dpo");
OpenDirectory(path, "table class");
break;
case "dc":
path = cfg.GetValue<string>("dc.path", $"{Configuration.MyDocuments}\\DataModel\\DataContracts");
OpenDirectory(path, "data contract class");
break;
case "l2s":
path = cfg.GetValue<string>("l2s.path", $"{Configuration.MyDocuments}\\DataModel\\L2s");
OpenDirectory(path, "data Linq to SQL class");
break;
case "viewer":
OpenEditor();
break;
default:
stdio.ErrorFormat("invalid arguments");
return;
}
}
public void mount(Command cmd, Configuration cfg)
{
if (cmd.HasHelp)
{
stdio.WriteLine("mount database server");
stdio.WriteLine("mount alias=server_name : alias must start with letter");
stdio.WriteLine("options:");
stdio.WriteLine(" /db:database : initial catalog, default is 'master'");
stdio.WriteLine(" /u:username : user id, default is 'sa'");
stdio.WriteLine(" /p:password : password, default is empty, use Windows Security when /u /p not setup");
stdio.WriteLine(" /pvd:provider : sqloledb,xmlfile, default is SQL client");
stdio.WriteLine("example:");
stdio.WriteLine(" mount ip100=192.168.0.100\\sqlexpress /u:sa /pwd:p@ss");
stdio.WriteLine(" mount web=http://192.168.0.100/db/northwind.xml /u:sa /pwd:p@ss");
stdio.WriteLine(" mount xml=file://c:\\db\\northwind.xml");
return;
}
if (cmd.arg1 == null)
{
stdio.ErrorFormat("invalid arguments");
return;
}
var items = cmd.arg1.Split('=');
if (items.Length != 2)
{
stdio.ErrorFormat("invalid arguments, correct format is alias=server_name");
return;
}
string serverName = items[0].Trim();
string dataSource = items[1].Trim();
StringBuilder builder = new StringBuilder();
string pvd = cmd.GetValue("pvd");
if (pvd != null)
{
if (pvd != "sqloledb" && pvd != "xmlfile")
{
stdio.ErrorFormat("provider={0} is not supported", pvd);
return;
}
builder.AppendFormat("provider={0};", pvd);
}
else
{
if (dataSource.StartsWith("file://") || dataSource.StartsWith("http://") || dataSource.StartsWith("ftp://"))
builder.Append("provider=xmlfile;");
}
builder.AppendFormat("data source={0};", dataSource);
string db = cmd.GetValue("db");
if (db != null)
builder.AppendFormat("initial catalog={0};", db);
else
builder.Append("initial catalog=master;");
string userId = cmd.GetValue("u");
string password = cmd.GetValue("p");
if (userId == null && password == null)
{
builder.Append("integrated security=SSPI;packet size=4096");
}
else
{
if (userId != null)
builder.AppendFormat("User Id={0};", userId);
else
builder.Append("User Id=sa;");
if (password != null)
builder.AppendFormat("Password={0}", password);
else
builder.Append("Password="******"database is offline or wrong parameter");
return;
}
var snode = new TreeNode<IDataPath>(provider.ServerName);
var result = cfg.Providers.FirstOrDefault(row => row.ServerName.Path == serverName);
if (result != null)
{
cfg.Providers.Remove(result);
var node = mgr.RootNode.Nodes.FirstOrDefault(row => row.Item.Path == serverName);
if (node != null)
mgr.RootNode.Nodes.Remove(node);
}
cfg.Providers.Add(provider);
mgr.RootNode.Nodes.Add(snode);
var xnode = mgr.Navigate(new PathName("\\" + serverName));
if (xnode != null)
{
mgr.current = xnode;
}
}
public void export(Command cmd, Configuration cfg, ShellContext context)
{
if (cmd.HasHelp)
{
stdio.WriteLine("export data, schema, class, and template on current selected server/db/table");
stdio.WriteLine("option:");
stdio.WriteLine(" /insert : export INSERT INTO script on current table/database");
stdio.WriteLine(" [/if] : option /if generate if exists row then UPDATE else INSERT");
stdio.WriteLine(" /create : generate CREATE TABLE script on current table/database");
stdio.WriteLine(" /select : generate SELECT FROM WHERE template");
stdio.WriteLine(" /update : generate UPDATE SET WHERE template");
stdio.WriteLine(" /save : generate IF EXISTS UPDATE ELSE INSERT template");
stdio.WriteLine(" /delete : generate DELETE FROM WHERE template, delete rows with foreign keys constraints");
stdio.WriteLine(" /schema : generate database schema xml file");
stdio.WriteLine(" /data : generate database/table data xml file");
stdio.WriteLine(" /dpo : generate C# table class");
stdio.WriteLine(" /enum : generate C# enum class");
stdio.WriteLine(" /l2s : generate C# Linq to SQL class");
stdio.WriteLine(" /dc1 : generate C# data contract class and extension class from last result");
stdio.WriteLine(" /dc2 : generate C# data contract class from last result");
stdio.WriteLine(" [/ns:name] default name space is defined on the .cfg");
stdio.WriteLine(" [/class:name] default class name is defined on the .cfg");
stdio.WriteLine(" [/method:foo] default convert method is defined on the .cfg");
stdio.WriteLine(" [/col:pk1,pk2] default primary key is the first column");
stdio.WriteLine(" [/out:path] output path");
stdio.WriteLine(" /entity : generate C# method copy/compare/clone for Entity framework");
stdio.WriteLine(" [/ns:name] default name space is defined on the .cfg");
stdio.WriteLine(" [/base:type] define base class or interface, use ~ to represent generic class itself, delimited by ;");
stdio.WriteLine(" [/using:assembly] allow the use of types in a namespace, delimited by ;");
stdio.WriteLine(" [/out:path] output path");
stdio.WriteLine(" /csv : generate table csv file");
stdio.WriteLine(" /json : generate json from last result");
stdio.WriteLine(" /c# : generate C# data from last result");
stdio.WriteLine(" [/ns:name] default name space is defined on the .cfg");
stdio.WriteLine(" [/class:name] default class name is defined on the .cfg");
stdio.WriteLine(" [/out:path] output path");
return;
}
if (!Navigate(cmd.Path1))
return;
if (pt.Item is TableName || pt.Item is Locator || pt.Item is DatabaseName || pt.Item is ServerName)
{
var exporter = new Exporter(mgr, pt, cfg);
if (cmd.Has("insert"))
exporter.ExportInsert(cmd);
else if (cmd.Has("create"))
exporter.ExportCreate(cmd);
else if (cmd.Has("select"))
exporter.ExportScud(SqlScriptType.SELECT);
else if (cmd.Has("delete"))
exporter.ExportScud(SqlScriptType.DELETE);
else if (cmd.Has("update"))
exporter.ExportScud(SqlScriptType.UPDATE);
else if (cmd.Has("save"))
exporter.ExportScud(SqlScriptType.INSERT_OR_UPDATE);
else if (cmd.Has("schema"))
exporter.ExportSchema();
else if (cmd.Has("data"))
exporter.ExportData(cmd);
else if (cmd.Has("dpo"))
exporter.ExportClass(cmd);
else if (cmd.Has("enum"))
exporter.ExportEnum(cmd);
else if (cmd.Has("csv"))
exporter.ExportCsvFile(cmd);
else if (cmd.Has("dc1"))
exporter.ExportDataContract(cmd, 1);
else if (cmd.Has("dc2"))
exporter.ExportDataContract(cmd, 2);
else if (cmd.Has("entity"))
exporter.ExportEntityClass(cmd);
else if (cmd.Has("l2s"))
exporter.ExportLinq2SQLClass(cmd);
else if (cmd.ToJson)
{
if (SqlShell.LastResult is DataTable)
{
var dt = SqlShell.LastResult as DataTable;
stdio.WriteLine(TableOut.ToJson(dt));
}
else
{
stdio.ErrorFormat("display data table first by sql clause or command [type]");
}
}
else if (cmd.ToCSharp)
exporter.ExportCSharpData(cmd);
else
stdio.ErrorFormat("invalid command options");
}
else
stdio.ErrorFormat("select server, database or table first");
}
public void clean(Command cmd, Configuration cfg)
{
if (cmd.HasHelp)
{
stdio.WriteLine("clean duplicated rows");
stdio.WriteLine("clean [path]|[pattern]| : clean current database or table, or search pattern");
stdio.WriteLine("options:");
stdio.WriteLine(" /col:c1,c2,.. : clean columns, compare column c1, c2, ...");
stdio.WriteLine(" /d : commit cleaning duplicated rows on database server, otherwise display # of duplicated rows");
stdio.WriteLine("example:");
stdio.WriteLine("clean match*s /col:c1,c2 : clean duplicated rows by comparing columns:c1 and c2");
stdio.WriteLine("clean : clean by comparing entire row");
return;
}
if (!Navigate(cmd.Path1))
return;
if (pt.Item is TableName)
{
var tname = (TableName)pt.Item;
var dup = new DuplicatedTable(tname, cmd.Columns);
if (cmd.Has("d"))
{
int count = dup.Clean();
stdio.WriteLine("completed to clean {0} #rows at {1}", count, tname);
}
else
{
int count = dup.DuplicatedRowCount();
if (count == 0)
stdio.WriteLine("no duplicated rows at {0}", tname);
else
stdio.WriteLine("{0} duplicated row(s) at {1}", count, tname);
}
return;
}
if (pt.Item is DatabaseName)
{
var dname = (DatabaseName)pt.Item;
var m = new MatchedDatabase(dname, cmd.wildcard, cfg.compareExcludedTables);
var T = m.MatchedTableNames;
CancelableWork.CanCancel(cts =>
{
foreach (var tn in T)
{
if (cts.IsCancellationRequested)
return;
if (cmd.Has("d"))
{
stdio.WriteLine("start to clean {0}", tn);
var dup = new DuplicatedTable(tn, cmd.Columns);
int count = dup.Clean();
stdio.WriteLine("cleaned {0} #rows", count);
}
else
{
stdio.WriteLine("start to query {0}", tn);
var dup = new DuplicatedTable(tn, cmd.Columns);
int count = dup.DuplicatedRowCount();
if (count == 0)
stdio.WriteLine("distinct rows");
else
stdio.WriteLine("{0} duplicated row(s)", count, tn);
}
}
});
return;
}
stdio.ErrorFormat("select database or table first");
}
public void umount(Command cmd, Configuration cfg)
{
if (cmd.HasHelp)
{
stdio.WriteLine("unmount database server");
stdio.WriteLine("unmount alias : alias must start with letter");
stdio.WriteLine("example:");
stdio.WriteLine(" umount ip100");
return;
}
if (cmd.arg1 == null)
{
stdio.ErrorFormat("invalid arguments");
return;
}
var items = cmd.arg1.Split('=');
string serverName = cmd.arg1;
var result = cfg.Providers.FirstOrDefault(row => row.ServerName.Path == serverName);
if (result != null)
{
cfg.Providers.Remove(result);
var node = mgr.RootNode.Nodes.FirstOrDefault(row => row.Item.Path == serverName);
if (node != null)
mgr.RootNode.Nodes.Remove(node);
}
var sname = mgr.GetCurrentPath<ServerName>();
if (sname != null && sname.Path == serverName)
{
var xnode = mgr.Navigate(new PathName("\\"));
if (xnode != null)
{
mgr.current = xnode;
}
}
}
