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; } } }