private static void Helper(string actualName, string searchName)
{
var target = new TableCollection { new Table(actualName, "", TableType.Table) };
var actual = target.Find(searchName);
Assert.IsNotNull(actual);
Assert.AreEqual(actualName, actual.ActualName);
}
/// <summary>
/// Returns List of tables ordered to allow inserts
/// without violating constraints
/// </summary>
/// <param name="unorderedTables"></param>
/// <returns></returns>
public static SortedList OrderTablesByName( TableCollection unorderedTables , bool Ascending )
{
ArrayList tableList = new ArrayList();
SortedList sortedTables = new SortedList();
int counter = 0;
foreach(Table tableObject in unorderedTables )
{
if(tableObject.IsSystemObject) continue;
tableList.Add( tableObject.Name);
}
tableList.Sort();
foreach(string tableName in tableList)
{
if(Ascending)
{
sortedTables.Add(counter + 1 ,tableName );
}
else
{
sortedTables.Add( ((tableList.Count - 1) - counter ) + 1 ,tableName );
}
counter++;
}
return sortedTables;
}
public DataSchema()
{
TargetSystems = new TargetSystemCollection(this);
ColumnTypes = new ColumnTypeCollection(this);
Tables = new TableCollection(this);
NameFormats = new NameFormats();
}
public SelectQuery(Dialect dialect, TableCollection tables, string whereClause, ISortQueryBuilder orderBy, bool useAltName)
{
Dialect = dialect;
Tables = tables;
WhereClause = whereClause;
OrderBy = orderBy;
UseAltName = useAltName;
}
public TableCollection List(IEnumerable<IFilter> filters)
{
var tables = new TableCollection();
var rows = Discover(filters);
foreach (var row in rows)
tables.AddOrIgnore(row.Name);
return tables;
}
/// <summary>
/// Finalize decompilation.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void FinalizeDecompile(TableCollection tables)
{
this.FinalizeIIsMimeMapTable(tables);
this.FinalizeIIsHttpHeaderTable(tables);
this.FinalizeIIsWebApplicationTable(tables);
this.FinalizeIIsWebErrorTable(tables);
this.FinalizeIIsWebVirtualDirTable(tables);
this.FinalizeIIsWebSiteCertificatesTable(tables);
this.FinalizeWebAddressTable(tables);
}
// SchemaReader.ReadSchema
public override TableCollection ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result=new TableCollection();
var cmd=factory.CreateCommand();
cmd.Connection=connection;
cmd.CommandText=TABLE_SQL;
//pull the TableCollection in a reader
using(cmd)
{
using (var rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
Table tbl=new Table();
tbl.Name=rdr["TABLE_NAME"].ToString();
tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
tbl.CleanName=CleanUp(tbl.Name);
tbl.ClassName=Inflector.Instance.MakeSingular(tbl.CleanName);
result.Add(tbl);
}
}
}
//this will return everything for the DB
var schema = connection.GetSchema("COLUMNS");
//loop again - but this time pull by table name
foreach (var item in result)
{
item.Columns=new List<Column>();
//pull the columns from the schema
var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
foreach (var row in columns)
{
Column col=new Column();
col.Name=row["COLUMN_NAME"].ToString();
col.PropertyName=CleanUp(col.Name);
col.PropertyType=GetPropertyType(row);
col.IsNullable=row["IS_NULLABLE"].ToString()=="YES";
col.IsPrimaryKey=row["COLUMN_KEY"].ToString()=="PRI";
col.IsAutoIncrement=row["extra"].ToString().ToLower().IndexOf("auto_increment")>=0;
item.Columns.Add(col);
}
}
return result;
}
/// <summary>
/// Initializes a new instance of the <see cref="Database" /> class with the specified database engine factory..
/// </summary>
/// <param name="databaseComponentFactory">The database component factory.</param>
/// <exception cref="System.ArgumentNullException"><paramref name="databaseComponentFactory"/> is null.</exception>
public Database(IDatabaseComponentFactory databaseComponentFactory)
{
if (databaseComponentFactory == null)
{
throw new ArgumentNullException("databaseComponentFactory");
}
this.databaseEngine = new DefaultDatabaseEngine(databaseComponentFactory, this);
this.storedProcedures = new StoredProcedureCollection(this);
this.tables = new TableCollection(this);
}
public override TableCollection ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result = new TableCollection();
_connection = connection;
_factory = factory;
var cmd = _factory.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = TABLE_SQL;
//pull the TableCollection in a reader
using (cmd)
{
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var tbl = new Table();
tbl.Name = rdr["TABLE_NAME"].ToString();
tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
tbl.IsView =
String.Compare(rdr["TABLE_TYPE"].ToString(), "View", StringComparison.OrdinalIgnoreCase) ==
0;
tbl.CleanName = CleanUp(tbl.Name);
tbl.ClassName = Inflector.Instance.MakeSingular(tbl.CleanName);
result.Add(tbl);
}
}
}
foreach (var tbl in result)
{
tbl.Columns = LoadColumns(tbl);
// Mark the primary key
var primaryKey = GetPrimaryKey(tbl.Name);
var pkColumn = tbl.Columns.SingleOrDefault(x => x.Name.ToLower().Trim() == primaryKey.ToLower().Trim());
if (pkColumn != null)
{
pkColumn.IsPrimaryKey = true;
}
}
return result;
}
/// <summary>
/// Carga el arbol con todas las tablas .-
/// </summary>
/// <param name="pTreeView">Nodo</param>
/// <param name="pTables">Tablas</param>
public static void LoadTreeView(TreeView pTreeView, TableCollection pTables)
{
TreeNode wTreeNode;
pTreeView.Nodes.Clear();
foreach (Table wTable in pTables)
{
wTreeNode = new TreeNode();
wTreeNode.Checked = false;
wTreeNode.Text = wTable.Name;
wTreeNode.Tag = wTable;
pTreeView.Nodes.Add(wTreeNode);
//LoadColumnsNodes(wTreeNode, wTable);
}
}
public void SqlServerUpdateQuery_ShouldGenQuery()
{
// Arrange
var command = new System.Data.SqlClient.SqlCommand();
var db = MockRepository.GenerateStub<IDataMapper>();
db.Expect(d => d.Command).Return(command);
ColumnMapCollection columns = MapRepository.Instance.GetColumns(typeof(Person));
MappingHelper mappingHelper = new MappingHelper(db);
Person person = new Person();
person.ID = 1;
person.Name = "Jordan";
person.Age = 33;
person.IsHappy = true;
person.BirthDate = new DateTime(1977, 1, 22);
mappingHelper.CreateParameters<Person>(person, columns, true);
int idValue = 7;
TableCollection tables = new TableCollection { new Table(typeof(Person)) };
Expression<Func<Person, bool>> filter = p => p.ID == person.ID || p.ID == idValue || p.Name == person.Name && p.Name == "Bob";
var where = new WhereBuilder<Person>(command, new SqlServerDialect(), filter, tables, false, true);
IQuery query = new UpdateQuery(new SqlServerDialect(), columns, command, "dbo.People", where.ToString());
// Act
string queryText = query.Generate();
// Assert
Assert.IsNotNull(queryText);
Assert.IsTrue(queryText.Contains("UPDATE [dbo].[People]"));
Assert.IsTrue(queryText.Contains("[Name]"));
Assert.IsTrue(queryText.Contains("[Age]"));
Assert.IsTrue(queryText.Contains("[IsHappy]"));
Assert.IsTrue(queryText.Contains("[BirthDate]"));
Assert.IsTrue(queryText.Contains("[ID] = @P4"));
Assert.IsTrue(queryText.Contains("[ID] = @P5"));
Assert.IsTrue(queryText.Contains("[Name] = @P6"));
Assert.IsTrue(queryText.Contains("[Name] = @P7"));
Assert.AreEqual(command.Parameters["@P4"].Value, 1);
Assert.AreEqual(command.Parameters["@P5"].Value, 7);
Assert.AreEqual(command.Parameters["@P6"].Value, "Jordan");
Assert.AreEqual(command.Parameters["@P7"].Value, "Bob");
}
public static IQuery CreatePagingSelectQuery(TableCollection tables, IDataMapper dataMapper, string where, ISortQueryBuilder orderBy, bool useAltName, int skip, int take)
{
SelectQuery innerQuery = (SelectQuery)CreateSelectQuery(tables, dataMapper, where, orderBy, useAltName);
string providerString = dataMapper.ProviderFactory.ToString();
switch (providerString)
{
case DB_SqlClient:
return new PagingQueryDecorator(innerQuery, skip, take);
case DB_SqlCeClient:
return new PagingQueryDecorator(innerQuery, skip, take);
case DB_SQLiteClient:
return new SqlitePagingQueryDecorator(innerQuery, skip, take);
default:
throw new NotImplementedException("Paging has not yet been implemented for this provider.");
}
}
public static IQuery CreateRowCountSelectQuery(TableCollection tables, IDataMapper dataMapper, string where, ISortQueryBuilder orderBy, bool useAltName)
{
SelectQuery innerQuery = (SelectQuery)CreateSelectQuery(tables, dataMapper, where, orderBy, useAltName);
string providerString = dataMapper.ProviderFactory.ToString();
switch (providerString)
{
case DB_SqlClient:
return new RowCountQueryDecorator(innerQuery);
case DB_SqlCeClient:
return new RowCountQueryDecorator(innerQuery);
case DB_SQLiteClient:
return new SqliteRowCountQueryDecorator(innerQuery);
default:
throw new NotSupportedException("Row count is not currently supported for this provider.");
}
}
public TableCodeFacadeUpdateForm(TableCollection tableCollection, FieldSettingConstants fieldSetting)
: this()
{
_tableCollection = tableCollection;
_fieldSetting = fieldSetting;
if (_fieldSetting == FieldSettingConstants.Name)
{
this.Text = "Update Entity";
}
else if (_fieldSetting == FieldSettingConstants.CodeFacade)
{
this.Text = "Update CodeFacade";
cmdRemoveAll.Visible = true;
}
try
{
var cacheFile = new ModelCacheFile(((ModelRoot)tableCollection.Root).GeneratorProject);
if (!string.IsNullOrEmpty(cacheFile.TableFacadeSettings))
{
var document = new XmlDocument();
document.LoadXml(cacheFile.TableFacadeSettings);
var containerNode = document.DocumentElement.ChildNodes[0];
optPrefix.Checked = XmlHelper.GetAttributeValue(containerNode, "prefixChecked", optPrefix.Checked);
txtPrefix.Text = XmlHelper.GetAttributeValue(containerNode, "prefix", txtPrefix.Text);
chkReplaceText.Checked = XmlHelper.GetAttributeValue(containerNode, "replaceText", chkReplaceText.Checked);
txtReplaceSource.Text = XmlHelper.GetAttributeValue(containerNode, "replaceSource", txtReplaceSource.Text);
txtReplaceTarget.Text = XmlHelper.GetAttributeValue(containerNode, "replaceTarget", txtReplaceTarget.Text);
optUpcase.Checked = XmlHelper.GetAttributeValue(containerNode, "upcase", optUpcase.Checked);
optUnderscore.Checked = XmlHelper.GetAttributeValue(containerNode, "underscore", optUnderscore.Checked);
chkSkip2Caps.Checked = XmlHelper.GetAttributeValue(containerNode, "TwoCaps", chkSkip2Caps.Checked);
}
}
catch (Exception ex)
{
throw;
}
this.UpdateForm();
}
public DataContext(MetadataContext metadataContext)
{
_metadataContext = metadataContext;
_tables = new TableCollection(this);
_tables.Changed += member_Changed;
_tableRelations = new TableRelationCollection(this);
_tableRelations.Changed += member_Changed;
_constants = new ConstantCollection();
_constants.Changed += member_Changed;
_aggregates = new AggregateCollection();
_aggregates.AddDefaults();
_aggregates.Changed += member_Changed;
_functions = new FunctionCollection();
_functions.AddDefaults();
_functions.Changed += member_Changed;
}
/// <summary>
/// Called at the beginning of the decompilation of a database.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void InitializeDecompile(TableCollection tables)
{
Table propertyTable = tables["Property"];
if (null != propertyTable)
{
foreach (Row row in propertyTable.Rows)
{
if ("SecureCustomProperties" == row[0].ToString())
{
// if we've referenced any of the DirectX properties, add
// a PropertyRef to pick up the CA from the extension and then remove
// it from the SecureCustomExtensions property so we don't get duplicates
StringBuilder remainingProperties = new StringBuilder();
string[] secureCustomProperties = row[1].ToString().Split(';');
foreach (string property in secureCustomProperties)
{
if (property.StartsWith("WIX_DIRECTX_"))
{
Wix.PropertyRef propertyRef = new Wix.PropertyRef();
propertyRef.Id = property;
this.Core.RootElement.AddChild(propertyRef);
}
else
{
if (0 < remainingProperties.Length)
{
remainingProperties.Append(";");
}
remainingProperties.Append(property);
}
}
row[1] = remainingProperties.ToString();
break;
}
}
}
}
/// <summary>
/// Called at the beginning of the decompilation of a database.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void InitializeDecompile(TableCollection tables)
{
Table propertyTable = tables["Property"];
if (null != propertyTable)
{
foreach (Row row in propertyTable.Rows)
{
if ("WixUI_Mode" == (string)row[0])
{
Wix.UIRef uiRef = new Wix.UIRef();
uiRef.Id = String.Concat("WixUI_", (string)row[1]);
this.Core.RootElement.AddChild(uiRef);
this.removeLibraryRows = true;
break;
}
}
}
}
/// <summary>
/// Decompile the SecureCustomProperties field to PropertyRefs for known extension properties.
/// </summary>
/// <remarks>
/// If we've referenced any of the suite or directory properties, add
/// a PropertyRef to refer to the Property (and associated custom action)
/// from the extension's library. Then remove the property from
/// SecureCustomExtensions property so later decompilation won't create
/// new Property elements.
/// </remarks>
/// <param name="tables">The collection of all tables.</param>
private void CleanupSecureCustomProperties(TableCollection tables)
{
Table propertyTable = tables["Property"];
if (null != propertyTable)
{
foreach (Row row in propertyTable.Rows)
{
if ("SecureCustomProperties" == row[0].ToString())
{
StringBuilder remainingProperties = new StringBuilder();
string[] secureCustomProperties = row[1].ToString().Split(';');
foreach (string property in secureCustomProperties)
{
if (property.StartsWith("WIX_SUITE_", StringComparison.Ordinal) || property.StartsWith("WIX_DIR_", StringComparison.Ordinal)
|| property.StartsWith("WIX_ACCOUNT_", StringComparison.Ordinal))
{
Wix.PropertyRef propertyRef = new Wix.PropertyRef();
propertyRef.Id = property;
this.Core.RootElement.AddChild(propertyRef);
}
else
{
if (0 < remainingProperties.Length)
{
remainingProperties.Append(";");
}
remainingProperties.Append(property);
}
}
row[1] = remainingProperties.ToString();
break;
}
}
}
}
//private DateTime _createdDate = DateTime.Now;
#endregion
#region Constructor
public Database(INHydrateModelObject root)
: base(root)
{
_tables = new TableCollection(root);
_tables.ResetKey(Guid.Empty.ToString());
_columns = new ColumnCollection(root);
_columns.ResetKey(Guid.Empty.ToString());
_relations = new RelationCollection(root);
_relations.ResetKey(Guid.Empty.ToString());
_viewRelations = new ViewRelationCollection(root);
_viewRelations.ResetKey(Guid.Empty.ToString());
_customViews = new CustomViewCollection(root);
_customViews.ResetKey(Guid.Empty.ToString());
_customAggregates = new CustomAggregateCollection(root);
_customAggregates.ResetKey(Guid.Empty.ToString());
_customStoredProcedures = new CustomStoredProcedureCollection(root);
_customStoredProcedures.ResetKey(Guid.Empty.ToString());
_customViewColumns = new CustomViewColumnCollection(root);
_customViewColumns.ResetKey(Guid.Empty.ToString());
_customAggregateColumns = new CustomAggregateColumnCollection(root);
_customAggregateColumns.ResetKey(Guid.Empty.ToString());
_customStoredProcedureColumns = new CustomStoredProcedureColumnCollection(root);
_customStoredProcedureColumns.ResetKey(Guid.Empty.ToString());
_customRetrieveRules = new CustomRetrieveRuleCollection(root);
_customRetrieveRules.ResetKey(Guid.Empty.ToString());
_customRetrieveRuleParameters = new ParameterCollection(root);
_customRetrieveRuleParameters.ResetKey(Guid.Empty.ToString());
_functionParameters = new ParameterCollection(root);
_functionParameters.ResetKey(Guid.Empty.ToString());
_functions = new FunctionCollection(root);
_functions.ResetKey(Guid.Empty.ToString());
_functionColumns = new FunctionColumnCollection(root);
_functionColumns.ResetKey(Guid.Empty.ToString());
this.PrecedenceOrderList = new List<Guid>();
}
/// <summary>
/// Returns List of tables ordered to allow inserts
/// without violating constraints
/// </summary>
/// <param name="unorderedTables"></param>
/// <returns></returns>
public static SortedList OrderTablesByDependency( TableCollection unorderedTables)
{
ArrayList _referencedTables = new ArrayList();
SortedList _sortedTables = new SortedList();
//create a list of tables that are refernced by other tables
foreach(Table tableObject in unorderedTables )
{
if(tableObject.IsSystemObject) continue;
foreach(string tableName in GetTableDependencies(tableObject))
{
if(!_referencedTables.Contains(tableName))
{
_referencedTables.Add(tableName);
}
}
}
//tables that are referenced have to be filled first
while(true)
{
UpdateNonViolatingList(ref _referencedTables , ref _sortedTables ,ref unorderedTables);
if(_referencedTables.Count == 0) break;
}
//tables that are not referenced at end
foreach(Table tableObject in unorderedTables )
{
if(tableObject.IsSystemObject) continue;
if(!_sortedTables.ContainsValue(tableObject.Name))
{
_sortedTables.Add(_sortedTables.Count + 1 ,tableObject.Name );
}
}
return _sortedTables;
}
/// <summary>
/// Finalize the IIsWebError table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// Since there is no foreign key relationship declared for this table
/// (because it takes various parent types), it must be nested late.
/// </remarks>
private void FinalizeIIsWebErrorTable(TableCollection tables)
{
Table iisWebErrorTable = tables["IIsWebError"];
if (null != iisWebErrorTable)
{
foreach (Row row in iisWebErrorTable.Rows)
{
IIs.WebError webError = (IIs.WebError)this.Core.GetIndexedElement(row);
if (1 == (int)row[2]) // WebVirtualDir parent
{
IIs.WebVirtualDir webVirtualDir = (IIs.WebVirtualDir)this.Core.GetIndexedElement("IIsWebVirtualDir", (string)row[3]);
if (null != webVirtualDir)
{
webVirtualDir.AddChild(webError);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebErrorTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "ParentValue", (string)row[3], "IIsWebVirtualDir"));
}
}
else if (2 == (int)row[2]) // WebSite parent
{
IIs.WebSite webSite = (IIs.WebSite)this.Core.GetIndexedElement("IIsWebSite", (string)row[3]);
if (null != webSite)
{
webSite.AddChild(webError);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebErrorTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "ParentValue", (string)row[3], "IIsWebSite"));
}
}
else
{
// TODO: warn unknown parent type
}
}
}
}
public virtual void VisitTableCollection(TableCollection coll)
{
}
private void Process_Click(object sender, EventArgs e)
{
// Don't allow multiple clicks
btnProcess.Enabled = false;
// This is going to take awhile - change to wait cursor
Cursor current = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
// Connect to SQL Server
ConnectionInfo.DatabaseName = DatabaseName;
SqlConnection con = new SqlConnection(ConnectionInfo.ConnectionString);
SqlCommand cmd = new SqlCommand();
// This SqlCommand will be used for the detailed queries
cmd.Connection = con;
cmd.CommandTimeout = ExecutionTimeout;
SqlParameter schemaNameParameter = new SqlParameter();
schemaNameParameter.ParameterName = "@table_schema";
schemaNameParameter.SqlDbType = SqlDbType.NVarChar;
schemaNameParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(schemaNameParameter);
SqlParameter tableNameParameter = new SqlParameter();
tableNameParameter.ParameterName = "@table_name";
tableNameParameter.SqlDbType = SqlDbType.NVarChar;
tableNameParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(tableNameParameter);
SqlParameter isEligibleParameter = new SqlParameter();
isEligibleParameter.ParameterName = "@is_eligible";
isEligibleParameter.SqlDbType = SqlDbType.Bit;
isEligibleParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(isEligibleParameter);
SqlParameter msgParameter = new SqlParameter();
msgParameter.ParameterName = "@msg";
msgParameter.SqlDbType = SqlDbType.NVarChar;
msgParameter.Size = 4000;
msgParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(msgParameter);
con.Open();
// Do processing here
try
{
// let's get us the list of databases, find ours
// and iterate all the tables within our selected database
ServerConnection c = new ServerConnection(ConnectionInfo);
Server s = new Server(c);
Database d = s.Databases[connectionInfo.DatabaseName];
TableCollection t = d.Tables;
try
{
foreach (Table tbl in t)
{
// Don't reprocess our schemas
if (tbl.Schema == "tdc" || tbl.Schema == "history")
{
continue;
}
// Can this table be TDC enabled?
cmd.Parameters["@table_schema"].Value = tbl.Schema;
cmd.Parameters["@table_name"].Value = tbl.Name;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "tdc.usp_CanTableBeTDC_Enabled";
// Retrieve our results and display to the user
cmd.ExecuteNonQuery();
// Lets display results to the user
DisplayRow(tbl.Schema, tbl.Name, Convert.ToBoolean(cmd.Parameters["@is_eligible"].Value), cmd.Parameters["@msg"].Value.ToString());
// if(Convert.ToBoolean(cmd.Parameters["@is_eligible"].Value) == false)
// {
//// MessageBox.Show(cmd.Parameters["@msg"].Value.ToString());
// //dgResults
// }
}
}
catch (SqlException ex)
{
SqlError error = ex.Errors[0];
if (error.Number == 207)
{
MessageBox.Show("Due to a defect in the system stored procedure used to estimate compression savings, tables that contain columns with embedded special characters cannot be processed.\r\nThis defect has been reported to the Product Team.", "Compression Estimator", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (SqlException sqlEX)
{
MessageBox.Show(sqlEX.Message, sqlEX.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
Cursor.Current = current;
btnSave.Enabled = true;
btnScript.Enabled = true;
btnProcess.Enabled = false;
}
internal Body(Document A_0, DocumentObject A_1) : base(A_0, A_1)
{
this.m_bodyItems = new BodyRegionCollection(this);
this.paragraphCollection_0 = new ParagraphCollection(this.m_bodyItems);
this.tableCollection_0 = new TableCollection(this.m_bodyItems);
}
public virtual void TerminateTableCollection(TableCollection coll)
{
}
/// <summary>
/// Finalize the WebAddress table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// There is a circular dependency between the WebAddress and WebSite
/// tables, so nesting must be handled here.
/// </remarks>
private void FinalizeWebAddressTable(TableCollection tables)
{
Table iisWebAddressTable = tables["IIsWebAddress"];
Table iisWebSiteTable = tables["IIsWebSite"];
Hashtable addedWebAddresses = new Hashtable();
if (null != iisWebSiteTable)
{
foreach (Row row in iisWebSiteTable.Rows)
{
IIs.WebSite webSite = (IIs.WebSite)this.Core.GetIndexedElement(row);
IIs.WebAddress webAddress = (IIs.WebAddress)this.Core.GetIndexedElement("IIsWebAddress", (string)row[7]);
if (null != webAddress)
{
webSite.AddChild(webAddress);
addedWebAddresses[webAddress] = null;
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebSiteTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "KeyAddress_", (string)row[7], "IIsWebAddress"));
}
}
}
if (null != iisWebAddressTable)
{
foreach (Row row in iisWebAddressTable.Rows)
{
IIs.WebAddress webAddress = (IIs.WebAddress)this.Core.GetIndexedElement(row);
if (!addedWebAddresses.Contains(webAddress))
{
IIs.WebSite webSite = (IIs.WebSite)this.Core.GetIndexedElement("IIsWebSite", (string)row[1]);
if (null != webSite)
{
webSite.AddChild(webAddress);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebAddressTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Web_", (string)row[1], "IIsWebSite"));
}
}
}
}
}
public void ReadTableSettings()
{
using(StreamReader reader = this.GetTableSettingReader())
{
TableCollection currentCollection = null;
reader.ReadLine(); //skip header
while (!reader.EndOfStream)
{
String line = reader.ReadLine();
String[] row = line.Split(',');
for (int i = 0; i < row.Length; i++)
{
row[i] = row[i].Trim('"');//trim quotes
row[i] = row[i].Trim();//trun whitespace
}
if(String.IsNullOrEmpty(row[0]))
{ continue; }
string sName = string.Empty;
string sDelMeth = string.Empty;
string sTracCreated = string.Empty;
string sTracMod = string.Empty;
string sTrackMerge = string.Empty;
string sTrackRowVersion = string.Empty;
try
{
sName = row[TSettings_Col_Name];
sDelMeth = row[TSettings_Col_DeleteMethod];
sTracCreated = row[TSettings_Col_TrackCreated];
sTracMod = row[TSettings_Col_TrackModified];
sTrackMerge = row[TSettings_Col_TrackMergeState];
sTrackRowVersion = row[TSettings_Col_TrackRowVersion];
}
catch { }
if (sName.StartsWith(TABLE_COLLECTION_INDICATOR))
{
currentCollection = new TableCollection()
{
Name = sName.Substring(1),
DeleteMethod = this.ParseDeleteMethod(sDelMeth),
TrackCreated = this.ParseBool(sTracCreated),
TrackModified = this.ParseBool(sTracMod),
TrackMergeState = this.ParseBool(sTrackMerge),
TrackRowVersion = this.ParseBool(sTrackRowVersion)
};
this.TableCollections.Add(currentCollection);
continue;
}
else if(sName.StartsWith(META_DATA_PREFIX))
{
string[] s = sName.Split('=');
switch (s[0])
{
case "$SchemaVersion":
{
this.SchemaVersion = s[1];
break;
}
case "$MinimumCompatibleVersion":
{
this.MinimumCompatibleVersion = s[1];
break;
}
case "$DBVersion":
{
this.DBVersion = s[1];
break;
}
}
continue;
}
else if (currentCollection == null)
{
currentCollection = new TableCollection();
this.TableCollections.Add(currentCollection);
}
Table curTable = this.AllTables[sName];
curTable.DeleteMethod = this.ParseDeleteMethod(sDelMeth);
//track created and track modifeid can either be explicitly marked true or false, or be blank
//blank will be read as null and will defer track created and track modified to the table collection
bool? trackCreated = this.ParseNBool(sTracCreated);
bool? trackModified = this.ParseNBool(sTracMod);
bool? trackMerge = this.ParseNBool(sTrackMerge);
bool? trackRowVersion = this.ParseNBool(sTrackRowVersion);
if(trackCreated != null)
{
curTable.TrackCreated = trackCreated.Value;
}
if(trackModified != null)
{
curTable.TrackModified = trackModified.Value;
}
if (trackMerge != null)
{
curTable.TrackMergeState = trackMerge.Value;
}
if (trackRowVersion != null)
{
curTable.TrackRowVersion = trackRowVersion.Value;
}
curTable.TableCollection = currentCollection;
currentCollection.Add(curTable);
}
}
}
/// <summary>
/// Finalize the SqlScript table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// The SqlScript and SqlString tables contain a foreign key into the SqlDatabase
/// and Component tables. Depending upon the parent of the SqlDatabase
/// element, the SqlScript and SqlString elements are nested under either the
/// SqlDatabase or the Component element.
/// </remarks>
private void FinalizeSqlScriptAndSqlStringTables(TableCollection tables)
{
Table sqlDatabaseTable = tables["SqlDatabase"];
Table sqlScriptTable = tables["SqlScript"];
Table sqlStringTable = tables["SqlString"];
Hashtable sqlDatabaseRows = new Hashtable();
// index each SqlDatabase row by its primary key
if (null != sqlDatabaseTable)
{
foreach (Row row in sqlDatabaseTable.Rows)
{
sqlDatabaseRows.Add(row[0], row);
}
}
if (null != sqlScriptTable)
{
foreach (Row row in sqlScriptTable.Rows)
{
Sql.SqlScript sqlScript = (Sql.SqlScript) this.Core.GetIndexedElement(row);
Row sqlDatabaseRow = (Row)sqlDatabaseRows[row[1]];
string databaseComponent = (string)sqlDatabaseRow[4];
// determine if the SqlScript element should be nested under the database or another component
if (null != databaseComponent && databaseComponent == (string)row[2])
{
Sql.SqlDatabase sqlDatabase = (Sql.SqlDatabase) this.Core.GetIndexedElement(sqlDatabaseRow);
sqlDatabase.AddChild(sqlScript);
}
else // nest under the component of the SqlDatabase row
{
Wix.Component component = (Wix.Component) this.Core.GetIndexedElement("Component", (string)row[2]);
// set the Database value
sqlScript.SqlDb = (string)row[1];
if (null != component)
{
component.AddChild(sqlScript);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, sqlScriptTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Component_", (string)row[2], "Component"));
}
}
}
}
if (null != sqlStringTable)
{
foreach (Row row in sqlStringTable.Rows)
{
Sql.SqlString sqlString = (Sql.SqlString) this.Core.GetIndexedElement(row);
Row sqlDatabaseRow = (Row)sqlDatabaseRows[row[1]];
string databaseComponent = (string)sqlDatabaseRow[4];
// determine if the SqlScript element should be nested under the database or another component
if (null != databaseComponent && databaseComponent == (string)row[2])
{
Sql.SqlDatabase sqlDatabase = (Sql.SqlDatabase) this.Core.GetIndexedElement(sqlDatabaseRow);
sqlDatabase.AddChild(sqlString);
}
else // nest under the component of the SqlDatabase row
{
Wix.Component component = (Wix.Component) this.Core.GetIndexedElement("Component", (string)row[2]);
// set the Database value
sqlString.SqlDb = (string)row[1];
if (null != component)
{
component.AddChild(sqlString);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, sqlStringTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Component_", (string)row[2], "Component"));
}
}
}
}
}
/// <summary>
/// Finalize decompilation.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void FinalizeDecompile(TableCollection tables)
{
this.FinalizeSqlFileSpecTable(tables);
this.FinalizeSqlScriptAndSqlStringTables(tables);
}
public TableCollectionWrapper(TableCollection tableCollection)
{
_tableCollection = tableCollection;
}
/// <summary>
/// Finalize the SqlFileSpec table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// Since rows of the SqlFileSpec table are represented by either
/// the SqlFileSpec or SqlLogFileSpec depending upon the context in
/// which they are used in the SqlDatabase table, decompilation of this
/// table must occur after the SqlDatbase parents are decompiled.
/// </remarks>
private void FinalizeSqlFileSpecTable(TableCollection tables)
{
Table sqlDatabaseTable = tables["SqlDatabase"];
Table sqlFileSpecTable = tables["SqlFileSpec"];
if (null != sqlDatabaseTable && null != sqlFileSpecTable)
{
Hashtable sqlFileSpecRows = new Hashtable();
// index each SqlFileSpec row by its primary key
foreach (Row row in sqlFileSpecTable.Rows)
{
sqlFileSpecRows.Add(row[0], row);
}
// create the necessary SqlFileSpec and SqlLogFileSpec elements for each row
foreach (Row row in sqlDatabaseTable.Rows)
{
Sql.SqlDatabase sqlDatabase = (Sql.SqlDatabase) this.Core.GetIndexedElement(row);
if (null != row[6])
{
Row sqlFileSpecRow = (Row)sqlFileSpecRows[row[6]];
if (null != sqlFileSpecRow)
{
Sql.SqlFileSpec sqlFileSpec = new Sql.SqlFileSpec();
sqlFileSpec.Id = (string)sqlFileSpecRow[0];
if (null != sqlFileSpecRow[1])
{
sqlFileSpec.Name = (string)sqlFileSpecRow[1];
}
sqlFileSpec.Filename = (string)sqlFileSpecRow[2];
if (null != sqlFileSpecRow[3])
{
sqlFileSpec.Size = (string)sqlFileSpecRow[3];
}
if (null != sqlFileSpecRow[4])
{
sqlFileSpec.MaxSize = (string)sqlFileSpecRow[4];
}
if (null != sqlFileSpecRow[5])
{
sqlFileSpec.GrowthSize = (string)sqlFileSpecRow[5];
}
sqlDatabase.AddChild(sqlFileSpec);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, sqlDatabaseTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "FileSpec_", (string)row[6], "SqlFileSpec"));
}
}
if (null != row[7])
{
Row sqlFileSpecRow = (Row)sqlFileSpecRows[row[7]];
if (null != sqlFileSpecRow)
{
Sql.SqlLogFileSpec sqlLogFileSpec = new Sql.SqlLogFileSpec();
sqlLogFileSpec.Id = (string)sqlFileSpecRow[0];
if (null != sqlFileSpecRow[1])
{
sqlLogFileSpec.Name = (string)sqlFileSpecRow[1];
}
sqlLogFileSpec.Filename = (string)sqlFileSpecRow[2];
if (null != sqlFileSpecRow[3])
{
sqlLogFileSpec.Size = (string)sqlFileSpecRow[3];
}
if (null != sqlFileSpecRow[4])
{
sqlLogFileSpec.MaxSize = (string)sqlFileSpecRow[4];
}
if (null != sqlFileSpecRow[5])
{
sqlLogFileSpec.GrowthSize = (string)sqlFileSpecRow[5];
}
sqlDatabase.AddChild(sqlLogFileSpec);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, sqlDatabaseTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "FileSpec_Log", (string)row[7], "SqlFileSpec"));
}
}
}
}
}
public static void ScriptTableTriggers(
Database database,
string scriptDirectory,
Microsoft.SqlServer.Management.Smo.Scripter scripter,
bool matchOnNameContains,
string textToMatchOnNameContains)
{
string triggerDirectory = Path.Combine(scriptDirectory, "triggers");
System.IO.Directory.CreateDirectory(triggerDirectory);
Stopwatch blockStart = new Stopwatch();
blockStart.Start();
TableCollection allTables = database.Tables;
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> allTriggerObjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
int triggerIndex = 0;
ScriptMove.WriteToLog("Scripting triggers...");
foreach (Table oneTable in allTables)
{
if (!oneTable.IsSystemObject)
{
if (matchOnNameContains == false || (matchOnNameContains == true && oneTable.Name.ToUpper().Contains(textToMatchOnNameContains)))
{
if (oneTable.Triggers.Count > 0)
{
foreach (Trigger oneTrigger in oneTable.Triggers)
{
if (!oneTrigger.IsSystemObject)
{
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> oneTriggerObject = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
oneTriggerObject.Add(oneTrigger.Urn);
string fileName = string.Format("{0}.{1}.{2}.sql",
string.Format("0000{0}", triggerIndex).Right(4),
oneTable.Schema,
oneTrigger.Name);
string fullFileName = Path.Combine(triggerDirectory, fileName);
try
{
WriteScriptToFile(fullFileName, oneTrigger.Urn, ref scripter);
}
catch (Exception ex)
{
ScriptMove.WriteToLog(String.Format(" Unable to script {0} due to error {1}", oneTable.Name, ex.Message));
}
triggerIndex++;
}
}
}
}
}
}
ScriptMove.WriteToLog(String.Format("{0} table triggers scripted. Elapsed seconds: {1}", triggerIndex, blockStart.Elapsed.TotalSeconds));
}
public TableCollectionForm(TableCollection tableCollection):this()
{
_tableCollection = tableCollection;
this.LoadList();
}
public Schemata(DataRow itemToLoad)
: base(itemToLoad)
{
Tables = new TableCollection();
Views = new ViewCollection();
}
/// <summary>
/// Finalize the IIsWebVirtualDir table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// WebVirtualDir elements nest under either a WebSite or component
/// depending upon whether the component in the IIsWebVirtualDir row
/// is the same as the one in the parent IIsWebSite row.
/// </remarks>
private void FinalizeIIsWebVirtualDirTable(TableCollection tables)
{
Table iisWebSiteTable = tables["IIsWebSite"];
Table iisWebVirtualDirTable = tables["IIsWebVirtualDir"];
Hashtable iisWebSiteRows = new Hashtable();
// index the IIsWebSite rows by their primary keys
if (null != iisWebSiteTable)
{
foreach (Row row in iisWebSiteTable.Rows)
{
iisWebSiteRows.Add(row[0], row);
}
}
if (null != iisWebVirtualDirTable)
{
foreach (Row row in iisWebVirtualDirTable.Rows)
{
IIs.WebVirtualDir webVirtualDir = (IIs.WebVirtualDir)this.Core.GetIndexedElement(row);
Row iisWebSiteRow = (Row)iisWebSiteRows[row[2]];
if (null != iisWebSiteRow)
{
if ((string)iisWebSiteRow[1] == (string)row[1])
{
IIs.WebSite webSite = (IIs.WebSite)this.Core.GetIndexedElement(iisWebSiteRow);
webSite.AddChild(webVirtualDir);
}
else
{
Wix.Component component = (Wix.Component)this.Core.GetIndexedElement("Component", (string)row[1]);
if (null != component)
{
webVirtualDir.WebSite = (string)row[2];
component.AddChild(webVirtualDir);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebVirtualDirTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Component_", (string)row[1], "Component"));
}
}
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, iisWebVirtualDirTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Web_", (string)row[2], "IIsWebSite"));
}
}
}
}
public void Load(IList <Table> tables)
{
Tables = new TableCollection();
Tables.Add(tables);
}
/// <summary>
/// Finalize the IIsWebSiteCertificates table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// This table creates CertificateRef elements which nest under WebSite
/// elements.
/// </remarks>
private void FinalizeIIsWebSiteCertificatesTable(TableCollection tables)
{
Table IIsWebSiteCertificatesTable = tables["IIsWebSiteCertificates"];
if (null != IIsWebSiteCertificatesTable)
{
foreach (Row row in IIsWebSiteCertificatesTable.Rows)
{
IIs.CertificateRef certificateRef = (IIs.CertificateRef)this.Core.GetIndexedElement(row);
IIs.WebSite webSite = (IIs.WebSite)this.Core.GetIndexedElement("IIsWebSite", (string)row[0]);
if (null != webSite)
{
webSite.AddChild(certificateRef);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, IIsWebSiteCertificatesTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Web_", (string)row[0], "IIsWebSite"));
}
}
}
}
/// <summary>
/// Finalize the ServiceConfig table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// Since there is no foreign key from the ServiceName column to the
/// ServiceInstall table, this relationship must be handled late.
/// </remarks>
private void FinalizeServiceConfigTable(TableCollection tables)
{
Table serviceConfigTable = tables["ServiceConfig"];
Table serviceInstallTable = tables["ServiceInstall"];
Hashtable serviceInstalls = new Hashtable();
// index the ServiceInstall table because the foreign key used by the ServiceConfig
// table is actually the ServiceInstall.Name, not the ServiceInstall.ServiceInstall
// this is unfortunate because the service Name is not guaranteed to be unique, so
// decompiler must assume there could be multiple matches and add the ServiceConfig to each
// TODO: the Component column information should be taken into acount to accurately identify
// the correct column to use
if (null != serviceInstallTable)
{
foreach (Row row in serviceInstallTable.Rows)
{
string name = (string)row[1];
Wix.ServiceInstall serviceInstall = (Wix.ServiceInstall) this.Core.GetIndexedElement(row);
if (!serviceInstalls.Contains(name))
{
serviceInstalls.Add(name, new ArrayList());
}
((ArrayList)serviceInstalls[name]).Add(serviceInstall);
}
}
if (null != serviceConfigTable)
{
foreach (Row row in serviceConfigTable.Rows)
{
Util.ServiceConfig serviceConfig = (Util.ServiceConfig) this.Core.GetIndexedElement(row);
if (0 == (int)row[2])
{
Wix.Component component = (Wix.Component) this.Core.GetIndexedElement("Component", (string)row[1]);
if (null != component)
{
component.AddChild(serviceConfig);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, serviceConfigTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "Component_", (string)row[1], "Component"));
}
}
else
{
ArrayList serviceInstallElements = (ArrayList)serviceInstalls[row[0]];
if (null != serviceInstallElements)
{
foreach (Wix.ServiceInstall serviceInstall in serviceInstallElements)
{
serviceInstall.AddChild(serviceConfig);
}
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, serviceConfigTable.Name, row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "ServiceName", (string)row[0], "ServiceInstall"));
}
}
}
}
}
public static Table SingleTable(string connectionString, string databaseName, string tableName)
{
TableCollection tableCol = GetTablesFromDatabase(connectionString, databaseName);
return(tableCol[tableName]);
}
/// <summary>
/// Finalize the SecureObjects table.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
/// <remarks>
/// Nests the PermissionEx elements below their parent elements. There are no declared foreign
/// keys for the parents of the SecureObjects table.
/// </remarks>
private void FinalizeSecureObjectsTable(TableCollection tables)
{
Table createFolderTable = tables["CreateFolder"];
Table secureObjectsTable = tables["SecureObjects"];
Hashtable createFolders = new Hashtable();
// index the CreateFolder table because the foreign key to this table from the
// LockPermissions table is only part of the primary key of this table
if (null != createFolderTable)
{
foreach (Row row in createFolderTable.Rows)
{
Wix.CreateFolder createFolder = (Wix.CreateFolder) this.Core.GetIndexedElement(row);
string directoryId = (string)row[0];
if (!createFolders.Contains(directoryId))
{
createFolders.Add(directoryId, new ArrayList());
}
((ArrayList)createFolders[directoryId]).Add(createFolder);
}
}
if (null != secureObjectsTable)
{
foreach (Row row in secureObjectsTable.Rows)
{
string id = (string)row[0];
string table = (string)row[1];
Util.PermissionEx permissionEx = (Util.PermissionEx) this.Core.GetIndexedElement(row);
if ("CreateFolder" == table)
{
ArrayList createFolderElements = (ArrayList)createFolders[id];
if (null != createFolderElements)
{
foreach (Wix.CreateFolder createFolder in createFolderElements)
{
createFolder.AddChild(permissionEx);
}
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, "SecureObjects", row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "LockObject", id, table));
}
}
else
{
Wix.IParentElement parentElement = (Wix.IParentElement) this.Core.GetIndexedElement(table, id);
if (null != parentElement)
{
parentElement.AddChild(permissionEx);
}
else
{
this.Core.OnMessage(WixWarnings.ExpectedForeignRow(row.SourceLineNumbers, "SecureObjects", row.GetPrimaryKey(DecompilerCore.PrimaryKeyDelimiter), "LockObject", id, table));
}
}
}
}
}
/// <summary>
/// Finalize decompilation.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void FinalizeDecompile(TableCollection tables)
{
}
static int Main(string[] args)
{
Console.OutputEncoding = System.Text.Encoding.UTF8;
var commandLineApplication = new CommandLineApplication();
commandLineApplication.Name = "sqlscripter";
commandLineApplication.Description = "Sqlscripter";
var sqlserver = commandLineApplication.Option("-S | --server", "Sql Server", CommandOptionType.SingleValue);
var sqluser = commandLineApplication.Option("-U | --user", "Sql User. Do not use in order to switch to integrated authentication.", CommandOptionType.SingleValue);
var sqlpsw = commandLineApplication.Option("-P | --psw", "Sql Password", CommandOptionType.SingleValue);
var sqldb = commandLineApplication.Option("-d | --database", "Sql Database", CommandOptionType.SingleValue);
var nouseprogress = commandLineApplication.Option("--no-progress", "Disable progress bar", CommandOptionType.NoValue);
commandLineApplication.Command("info", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} render server information";
command.OnExecute(() =>
{
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
System.Console.WriteLine("Databases:");
foreach (var db in server.Databases)
{
System.Console.WriteLine($"\t{db}");
}
return(0);
});
});
commandLineApplication.Command("dbindex", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allow to connect to a database and build an ordered index of all objects";
var indexfile = command.Option("-i | --index", "Generate Index File", CommandOptionType.SingleValue);
var querymode = command.Option("--query-mode", "Use object query for objects", CommandOptionType.NoValue);
var one_stored = command.Option("--one-stored", "Generate one stored dependency", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
DateTime pinned = DateTime.UtcNow;
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Scripter scripter = new Scripter(server);
ScriptingOptions op = new ScriptingOptions
{
AllowSystemObjects = false
, WithDependencies = true
};
scripter.Options = op;
UrnCollection urns = new UrnCollection();
List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn> preobjects = new List <Microsoft.SqlServer.Management.Sdk.Sfc.Urn>();
Console.WriteLine("CONNECTED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//bool display_progress = (!useprogress.HasValue()) && System.Console.h
bool fast = querymode.HasValue();
Database db = server.Databases[sqldb.Value()];
//add all or just one sp
if (one_stored.HasValue())
{
var sp = db.StoredProcedures[one_stored.Value()];
urns.Add(sp.Urn);
}
else
{
SchemaCollection sc = db.Schemas;
foreach (Schema schema in sc)
{
if (!schema.IsSystemObject)
{
preobjects.Add(schema.Urn);
}
}
TableCollection tc = db.Tables;
add_urns_from_collection(tc, urns, (!nouseprogress.HasValue()));
if (fast)
{
add_urn_from_query(db, "P", (sp, sch) => db.StoredProcedures[sp, sch].Urn, urns, (!nouseprogress.HasValue())
, (sp, sch) => !db.StoredProcedures[sp, sch].IsSystemObject);
}
else
{
var sp = server.Databases[sqldb.Value()].StoredProcedures;
add_urns_from_collection(sp, urns);
}
//--------------------------------
if (fast)
{
add_urn_from_query(db, "V", (sp, sch) => db.Views[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var vs = server.Databases[sqldb.Value()].Views;
add_urns_from_collection(vs, urns);
}
var ss = server.Databases[sqldb.Value()].Synonyms;
add_urns_from_collection(ss, urns);
if (fast)
{
add_urn_from_query(db, "IF", (sp, sch) => db.UserDefinedFunctions[sp, sch].Urn, urns, (!nouseprogress.HasValue()));
}
else
{
var ff = server.Databases[sqldb.Value()].UserDefinedFunctions;
add_urns_from_collection(ff, urns);
}
var tt = server.Databases[sqldb.Value()].UserDefinedTypes;
add_urns_from_collection(tt, urns);
}
Console.WriteLine("DISCOVERING ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
//scripter.DiscoveryProgress += Scripter_DiscoveryProgress;
DependencyTree tr = scripter.DiscoverDependencies(urns, true);
Console.WriteLine("DEPENDENCY ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
DependencyCollection dc = scripter.WalkDependencies(tr);
Console.WriteLine("WALKED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
dependency_index index = dependency.index(tr);
Console.WriteLine("INDEXED ({0})", DateTime.UtcNow.Subtract(pinned));
pinned = DateTime.UtcNow;
string path = indexfile.Value();
if (null != path)
{
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
System.IO.File.AppendAllText(path, "#file auto-generated" + Environment.NewLine);
}
foreach (Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn in preobjects)
{
UrnToIndex(db.Name, path, urn, index);
}
foreach (DependencyCollectionNode j in dc)
{
Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn = j.Urn;
UrnToIndex(db.Name, path, urn, index);
}
Console.WriteLine("EXPORTED ({0})", DateTime.UtcNow.Subtract(pinned));
return(0);
});
});
commandLineApplication.Command("urn", command =>
{
var urn = command.Option("-u | --urn", "Sql Urn", CommandOptionType.SingleValue);
command.Description = @"Normalize an Input.
From Server[@Name='4f4c6527222b']/Database[@Name='MONITORING']/Table[@Name='Procedures' and @Schema='Gathering']
to Table:[Gathering].[Procedures]";
command.OnExecute(() => {
Console.WriteLine(NormalizeUrn(urn.Value()));
return(0);
});
});
commandLineApplication.Command("script", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = $"{command.Name} allows to script objects listed in a file or in the command line";
var target = command.Option("-t | --target", "Sql target Object. For instance Table:[dbo].[Table_1]", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Scripts Directory Output", CommandOptionType.SingleValue);
var file = command.Option("-f | -i | --file", "Input File", CommandOptionType.SingleValue);
var version = command.Option("--sql-version", "Sql Version Generation Target", CommandOptionType.SingleValue);
var file_version = command.Option("--file-version", "Enable object version support", CommandOptionType.NoValue);
var modified = command.Option("--modified", "Export all object modified in the last <input> minutes. Es 1440 last day", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
//TODO: ALLOW MULTIPLE TARGETS AND MULTIPLE FILES
List <string> objs = new List <string>();
objs.AddRange(target.Values.ToArray());
if (null != file.Value())
{
string [] lines = System.IO.File.ReadAllLines(file.Value());
objs.AddRange(lines);
}
if (modified.HasValue())
{
int minutes = int.Parse(modified.Value());
string [] mods = exporter.get_modified_objects(db, minutes);
foreach (string obj in mods)
{
Console.WriteLine(string.Format("\t\tMODIFIED:\t{0}", obj));
}
objs.AddRange(mods);
}
string outputdir = output.Value() ?? "./";
SqlServerVersion sql_version = SqlServerVersion.Version100;
if (version.HasValue())
{
sql_version = (SqlServerVersion)Enum.Parse(typeof(SqlServerVersion), version.Value());
}
scripter.Script(objs.ToArray(), db
, outputdir, (!nouseprogress.HasValue())
, sql_version
, file_version.HasValue());
return(0);
});
//scripter.Script(
});
commandLineApplication.Command("build", command =>
{
command.Options.AddRange(command.Parent.Options);
var indexfiles = command.Option("-i | --index", "Input Index File", CommandOptionType.MultipleValue);
var excludetyes = command.Option("-x | --exclude-types", "Types to exclude from the index", CommandOptionType.MultipleValue);
var output = command.Option("-o | --output", "Script Build Output", CommandOptionType.SingleValue);
var basepath = command.Option("-b | --basepath", "Root of files referenced by index", CommandOptionType.SingleValue);
var database_version = command.Option("--database-version", "Insert database version in script with object version", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
string outputfile = output.Value();
if (null != outputfile)
{
if (System.IO.File.Exists(outputfile))
{
System.IO.File.Delete(outputfile);
}
}
//ProcessDirs(pretypes.Values.ToArray(), outputfile);
string basep = basepath.Value();
string main_index = indexfiles.Values[0];
if (null == basep)
{
basep = System.IO.Path.GetDirectoryName(main_index);
}
foreach (string indexfile in indexfiles.Values)
{
string indexfilepath = System.IO.Path.GetFullPath(System.IO.Path.Join(basep, indexfile));
if (!System.IO.File.Exists(indexfilepath))
{
indexfilepath = System.IO.Path.GetFullPath(indexfile);
}
System.Console.WriteLine("Adding " + System.IO.Path.GetFileName(indexfile));
string[] types = System.IO.File.ReadAllLines(indexfilepath);
int types_count = 0;
foreach (string tt in types)
{
obj_info oi = util.ObjectInfo(tt);
util.drawTextProgressBar(++types_count, types.Length, $" ({tt}) ");
if (oi.is_type)
{
if (!excludetyes.Values.Contains(oi.type))
{
string source = util.FilePath(basep, oi, false);
string content = System.IO.File.ReadAllText(source);
if (database_version.HasValue())
{
content = scripter.insert_database_version(content, database_version.Value());
}
if (null != outputfile)
{
System.IO.File.AppendAllText(outputfile, content);
}
else
{
Console.Write(content);
}
}
}
}
}
//ProcessDirs(posttypes.Values.ToArray(), outputfile);
});
});
commandLineApplication.Command("coverage", command =>
{
command.Options.AddRange(command.Parent.Options);
command.Description = @"Run sql stetament from files or command line and track coverage";
var indexfiles = command.Option("-i | --input", "Input Coverage File", CommandOptionType.MultipleValue);
var statements = command.Option("-s | --statement", "Input Coverage Statement", CommandOptionType.MultipleValue);
var free_proccache = command.Option("-f | --free-proccache", @"Run DBCC FREEPROCCACHE before your test in order
to count only what you are running and not previous runs.
Do Not use in a production system.", CommandOptionType.NoValue);
var no_exec = command.Option("-n | --no-exec", @"Do not Run the procedure.", CommandOptionType.NoValue);
var datail = command.Option("--detail", @"Provide the list of not covered query_hash", CommandOptionType.NoValue);
var save = command.Option("--save", @"save a test result with performance and coverage", CommandOptionType.SingleValue);
command.OnExecute(() =>
{
util.disable_console = nouseprogress.HasValue();
ServerConnection serverConnection = get_server_connection(sqlserver, sqldb, sqluser, sqlpsw);
if (null == serverConnection)
{
return(2);
}
Server server = new Server(serverConnection);
Database db = server.Databases[sqldb.Value()];
if (null == db)
{
throw new ScripterException("Invalid database");
}
string save_path = null;
if (save.HasValue())
{
save_path = save.Value();
}
if (free_proccache.HasValue())
{
db.ExecuteNonQuery("DBCC FREEPROCCACHE");
}
foreach (string statement in statements.Values)
{
string sql = statement;
handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path);
}
foreach (string indexfile in indexfiles.Values)
{
string[] lines = System.IO.File.ReadAllLines(indexfile);
string sql = string.Join("\r\n", lines);
handle_coverage(db, sql, !no_exec.HasValue(), datail.HasValue(), save_path);
}
return(0);
});
});
commandLineApplication.HelpOption("-h | --help", inherited: true);
try
{
int r = commandLineApplication.Execute(args);
return(r);
}
catch (CommandParsingException ex)
{
Console.Error.Write("Invalid Command Line: ");
Console.Error.WriteLine(ex.Message);
Console.Error.WriteLine(commandLineApplication.GetHelpText());
return(22);
}
catch (Exception ex)
{
ConsoleColor color = Console.ForegroundColor;
try{
Console.ForegroundColor = ConsoleColor.Red;
Console.Error.WriteLine(ex.Message);
Console.ForegroundColor = ConsoleColor.Gray;
Console.Error.WriteLine(ex.ToString());
}
finally
{
Console.ForegroundColor = color;
}
return(99);
}
}
/// <summary>
/// Finalize decompilation.
/// </summary>
/// <param name="tables">The collection of all tables.</param>
public override void FinalizeDecompile(TableCollection tables)
{
}
public Schemata()
{
Tables = new TableCollection();
Views = new ViewCollection();
}
private void PasteTable(TableCollection tableCollection, XmlNode tableNode, XmlNode columnListNode)
{
try
{
var table = tableCollection.Add();
var id = table.Id;
table.XmlLoad(tableNode);
table.SetId(id);
table.SetKey(Guid.NewGuid().ToString());
table.Name = "[" + table.Name + "]";
table.Columns.Clear();
table.Relationships.Clear();
table.CustomRetrieveRules.Clear();
var columnMapper = new Dictionary<int, int>();
foreach (XmlNode child in columnListNode)
{
var column = ((ModelRoot)this.Object.Root).Database.Columns.Add();
id = column.Id;
column.XmlLoad(child);
columnMapper.Add(column.Id, id);
column.SetId(id);
column.SetKey(Guid.NewGuid().ToString());
table.Columns.Add(column.CreateRef());
column.ParentTableRef = table.CreateRef();
}
foreach (RowEntry r in table.StaticData)
{
foreach (CellEntry cell in r.CellEntries)
{
if (columnMapper.ContainsKey(cell.ColumnRef.Ref))
{
var newID = columnMapper[cell.ColumnRef.Ref];
cell.ColumnRef.Ref = newID;
}
}
}
}
catch (Exception ex)
{
throw;
}
}
public virtual void TerminateTableCollection(TableCollection coll)
{
}
/// <summary>
/// 为实体赋值-doc模式
/// </summary>
/// <param name="model"></param>
private void SetPlotModel_DOC(PlotModel model, string filePath, bool recursive = false)
{
Document document = new Document();
TableCollection tables = null;
document.LoadFromFile(filePath);
tables = document.Sections[0].Tables;
if (tables.Count == 0)
{
return;
}
if (tables[0].Rows.Count == 0)
{
return;
}
if (tables.Count == 0)
{
return;
}
if (tables[0].Rows.Count == 0)
{
return;
}
try
{
model.CoordinateList = new List <CoordinatesModel>();
model.PlotName = tables[0].Rows[2].Cells[1].Paragraphs[0].Text.Trim(); //地块名称
model.CbfDBName = tables[0].Rows[4].Cells[1].Paragraphs[0].Text.Trim(); //承包方代表名称
#region 处理界址点检查记录信息
List <string> list = new List <string>();
//循环多页
for (int j = 0; j < tables.Count; j++)
{
for (int i = 10; i < tables[j].Rows.Count; i++)
{
var row = tables[j].Rows[i];
if (string.IsNullOrEmpty(row.Cells[0].Paragraphs[0].Text))
{
break;
}
var coordinate = new CoordinatesModel();
coordinate.OrderNum = (i - 9).ToString();
coordinate.SerialNumber = row.Cells[0].Paragraphs[0].Text.Trim();
coordinate.BoundaryPointNum = row.Cells[1].Paragraphs[0].Text.Trim();
//对界址点编号中已存在的坐标点 不去重新计算赋值 直接添加入列表
if (!list.Contains(coordinate.BoundaryPointNum) && plotDic.ContainsKey(coordinate.BoundaryPointNum) && !recursive)//非递归模式
{
list.Add(coordinate.BoundaryPointNum);
coordinate = plotDic[coordinate.BoundaryPointNum];
coordinate.OrderNum = (i - 9).ToString();
model.CoordinateList.Add(coordinate);
continue;
}
coordinate.X = Convert.ToDouble(row.Cells[2].Paragraphs[0].Text.Trim()).ToString("f3");
coordinate.Y = Convert.ToDouble(row.Cells[3].Paragraphs[0].Text.Trim()).ToString("f3");
//先随机 ∆L
coordinate.difL = GetdifL();
coordinate.difSquareL = Math.Pow(Convert.ToDouble(coordinate.difL), 2.0).ToString("f3");
//再随机 X'
coordinate.cX = (Convert.ToDouble(coordinate.X) + MathCode.GetRandomNumber(-Convert.ToDouble(coordinate.difL) / 2, Convert.ToDouble(coordinate.difL) / 2, 3)).ToString("f3");
#region 随机三次X取样值
bool flagX = true;
while (flagX)
{
coordinate.X1 = (Convert.ToDouble(coordinate.X) + MathCode.GetRandomNumber(-0.75, 0.75, 3)).ToString("f3");
coordinate.X2 = (Convert.ToDouble(coordinate.X) + MathCode.GetRandomNumber(-0.75, 0.75, 3)).ToString("f3");
coordinate.X3 = (3 * Convert.ToDouble(coordinate.X) - Convert.ToDouble(coordinate.X1) - Convert.ToDouble(coordinate.X2)).ToString("f3");
if (Math.Abs(Convert.ToDouble(coordinate.X3) - Convert.ToDouble(coordinate.X)) < 0.8) //超出范围
{
flagX = false;
}
}
#endregion
coordinate.difX = (Convert.ToDouble(coordinate.X) - Convert.ToDouble(coordinate.cX)).ToString("f3");
//根据公式求出 ∆y ∆y = 开平方（∆L2-∆x2） 每次随机正负值
Random rd = new Random();
var r = rd.Next(0, 2);
coordinate.difY = ((r == 0 ? -1 : 1) * Math.Sqrt(Convert.ToDouble(coordinate.difSquareL) - Math.Pow(Convert.ToDouble(coordinate.difX), 2.0))).ToString("f3");
coordinate.cY = (Convert.ToDouble(coordinate.Y) - Convert.ToDouble(coordinate.difY)).ToString("f3");
#region 随机三次Y取样值
bool flagY = true;
while (flagY)
{
coordinate.Y1 = (Convert.ToDouble(coordinate.Y) + MathCode.GetRandomNumber(-0.75, 0.75, 3)).ToString("f3");
coordinate.Y2 = (Convert.ToDouble(coordinate.Y) + MathCode.GetRandomNumber(-0.75, 0.75, 3)).ToString("f3");
coordinate.Y3 = (3 * Convert.ToDouble(coordinate.Y) - Convert.ToDouble(coordinate.Y1) - Convert.ToDouble(coordinate.Y2)).ToString("f3");
if (Math.Abs(Convert.ToDouble(coordinate.Y3) - Convert.ToDouble(coordinate.Y)) < 0.8) //超出范围
{
flagY = false;
}
}
#endregion
if (!plotDic.ContainsKey(coordinate.BoundaryPointNum))//界址点编号字典添加坐标实体
{
plotDic.Add(coordinate.BoundaryPointNum, coordinate);
}
if (!list.Contains(coordinate.BoundaryPointNum))//不把最后一个回归原点的坐标统计入内
{
list.Add(coordinate.BoundaryPointNum);
model.CoordinateList.Add(coordinate);
}
}
}
#endregion
#region 处理面积检查记录信息
model.PlotArea = Convert.ToDouble(tables[0].Rows[6].Cells[1].Paragraphs[0].Text).ToString("f2");
#region 计算P'
//计算之前先判断是否有内环
bool isHaveNei = false;
int bigAreaCount = 0;
for (int i = 0; i < model.CoordinateList.Count; i++)
{
string serialNumber = model.CoordinateList[i].SerialNumber;
if (serialNumber.Contains("内环"))
{
isHaveNei = true;
bigAreaCount = i;
break;
}
}
string realArea = "";
if (!isHaveNei) //如果没有内环
{
double[] difXArr = new double[model.CoordinateList.Count];
double[] difYArr = new double[model.CoordinateList.Count];
for (int i = 0; i < model.CoordinateList.Count; i++)
{
difXArr[i] = Convert.ToDouble(model.CoordinateList[i].cX);
difYArr[i] = Convert.ToDouble(model.CoordinateList[i].cY);
}
model.PlotCheckArea = MathCode.AoArea(model.CoordinateList.Count, difXArr, difYArr).ToString("f2");
for (int i = 0; i < model.CoordinateList.Count; i++)
{
difXArr[i] = Convert.ToDouble(model.CoordinateList[i].X);
difYArr[i] = Convert.ToDouble(model.CoordinateList[i].Y);
}
realArea = MathCode.AoArea(model.CoordinateList.Count, difXArr, difYArr).ToString("f2");
}
else//如果有内环
{
#region 大环
double[] difXArr = new double[bigAreaCount];
double[] difYArr = new double[bigAreaCount];
for (int i = 0; i < bigAreaCount; i++)
{
difXArr[i] = Convert.ToDouble(model.CoordinateList[i].cX);
difYArr[i] = Convert.ToDouble(model.CoordinateList[i].cY);
}
double bigArea = MathCode.AoArea(bigAreaCount, difXArr, difYArr);//大面积
for (int i = 0; i < bigAreaCount; i++)
{
difXArr[i] = Convert.ToDouble(model.CoordinateList[i].X);
difYArr[i] = Convert.ToDouble(model.CoordinateList[i].Y);
}
double realBigArea = MathCode.AoArea(bigAreaCount, difXArr, difYArr);//真实大面积
#endregion
#region 小环
int smallAreaCount = model.CoordinateList.Count - bigAreaCount;
double[] difXArr2 = new double[smallAreaCount];
double[] difYArr2 = new double[smallAreaCount];
for (int i = 0; i < smallAreaCount; i++)
{
difXArr2[i] = Convert.ToDouble(model.CoordinateList[i + bigAreaCount].cX);
difYArr2[i] = Convert.ToDouble(model.CoordinateList[i + bigAreaCount].cY);
}
double smallArea = Math.Abs(MathCode.AoArea(smallAreaCount, difXArr2, difYArr2));//内环面积
for (int i = 0; i < smallAreaCount; i++)
{
difXArr2[i] = Convert.ToDouble(model.CoordinateList[i + bigAreaCount].X);
difYArr2[i] = Convert.ToDouble(model.CoordinateList[i + bigAreaCount].Y);
}
double realSmallArea = Math.Abs(MathCode.AoArea(smallAreaCount, difXArr2, difYArr2));//内环面积
#endregion
model.PlotCheckArea = (bigArea - smallArea).ToString("f2");
realArea = (realBigArea - realSmallArea).ToString("f2");
}
#endregion
model.DifArea = Math.Abs(Convert.ToDouble(model.PlotArea) - Convert.ToDouble(model.PlotCheckArea)).ToString("f2");
//R=∆P÷P′×100％
model.PercentageError = (Convert.ToDouble(model.DifArea) / Convert.ToDouble(model.PlotCheckArea) * 100).ToString("f1");
#endregion
#region 计算界址点中误差
//界址点中误差m= sqrt(∑[∆L2]/2n)；高精度检查时，界址点中误差m= sqrt(∑[∆L2]/ n)
double difLSum = 0;
foreach (var coordinate in model.CoordinateList)
{
difLSum += Convert.ToDouble(coordinate.difSquareL);
}
model.PlotM = Math.Sqrt(difLSum / (2 * model.CoordinateList.Count)).ToString("f4");
#endregion
calcCount++;
if (calcCount > 20)//如果计算次数超过40，说明有异常发生
{
//errorDic.Add(model.PlotCode, filePath + " 文件中面积值为：" + model.PlotArea + ",实际运算值为：" + realArea);
calcCount = 0;
model.IsGenerate = false;
return;
}
//最后判断如果 误差>5 或计算界址点中误差>=0.4 或面积P与P'相同 或P'小于零 则重新计算
if (Convert.ToDouble(model.PercentageError) >= 5 || Convert.ToDouble(model.PlotM) >= 0.4 || model.PlotArea == model.PlotCheckArea || Convert.ToDouble(model.PlotCheckArea) < 0)
{
SetPlotModel_DOC(model, filePath, true);
}
//standM = GetStandM();
calcCount = 0;
return;
}
catch (Exception ex)
{
errorDic.Add(model.PlotCode, filePath);
}
}
public async Task <TableReference[]> GetAll()
{
User activeUser = await userManager.GetUserAsync(User);
return(TableCollection.FindByUser(activeUser.ID).Select(x => new TableReference(x)).ToArray());
}
public virtual void VisitTableCollection(TableCollection coll)
{
}
public TableCollectionForm(TableCollection tableCollection) : this()
{
_tableCollection = tableCollection;
this.LoadList();
}
