public IndexDefinition(ITableDefinition table, string name) { Table = table; Name = name; Columns = new List<IndexColumnDefinition>(); Grbit = CreateIndexGrbit.None; Density = 100; }
private void BuildAddTableNode(ITableDefinition table, IAstNode parent) { IAddTableNode addTableNode = new AddTableNode(parent, table.Name); parent.ChildNodes.Add(addTableNode); foreach(IColumnDefinition column in table.Columns) { BuildAddColumnNode(column, addTableNode); } // foreach }
public PagingControlConfiguration(ITableDefinition tableDefinition) { TableDefinition = tableDefinition; ContainerCssClass = "table-pagination"; DisabledCssClass = "disabled"; ActiveCssClass = "active"; FirstPageText = "<<"; PreviousPageText = "<"; NextPageText = ">"; LastPageText = ">>"; }
// some table alterations can be done by manualy editing the stored create table statments // this method IS NOT APROPRIATE for removing or reordering colums, removing unique constraints or removing primary key constraints. // this method IS APROPRIATE for removing check, foreign key, not null, or default value constraints. // https://www.sqlite.org/lang_altertable.html#otheralter // IMPORTANT: call BeginTransaction before calling this method and EndTransaction after // you may allso need to drop and recreate any views, triggers, or indexes depending on what you have changed on the table public static void UpdateTableDDL(CruiseDatastore db, ITableDefinition tableDef) { var schemaVersion = db.ExecuteScalar <int>("PRAGMA schema_version;"); db.Execute("PRAGMA writable_schema=ON;"); var tableName = tableDef.TableName; db.Execute("UPDATE sqlite_master SET sql=@p1 WHERE type='table' AND name=@p2", tableDef.CreateTable, tableName); db.Execute($"PRAGMA schema_version={schemaVersion + 1};"); var integrityCheck = db.QueryScalar <string>("PRAGMA main.integrity_check;").ToArray(); if (integrityCheck.FirstOrDefault() != "ok") { Logger.Log(String.Join("|", integrityCheck), "Updater_V3", FMSC.ORM.Logging.LogLevel.Trace); throw new SchemaException("Integrity Check Failed While Updating Table Definition On " + tableName); } }
/// <summary> /// Asseblyes header string. /// </summary> /// <param name="table">Export table description.</param> /// <param name="fields">Field names.</param> /// <returns>Header string.</returns> private string _AssemblyHeaderString(ITableDefinition table, ICollection <string> fields) { bool isSeparatorNeed = false; var sb = new StringBuilder(); foreach (string field in fields) { if (isSeparatorNeed) { sb.Append(_separator); } sb.AppendFormat(STRING_FORMAT, table.GetFieldTitleByName(field)); isSeparatorNeed = true; } return(sb.ToString()); }
public override void CreateTable(ITableDefinition tableDefinition) { var createStmt = new StringBuilder(string.Format("create table {0} (", tableDefinition.Name)); for (var i = 0; i < tableDefinition.Columns.Count; i++) { if (i > 0) { createStmt.Append(", "); } createStmt.Append(tableDefinition.Columns[i].Name); createStmt.Append(" "); createStmt.Append(tableDefinition.Columns[i].GetColumnDefinition()); } createStmt.Append(")"); Commands.ExecuteNonQuery(createStmt.ToString()); }
public static IControlOptionTableDefinition GetControleOptions(ITableDefinition table) { if (table == null) { return(null); } Type tp = null; if (m_dicTypeTableToTypeControle.TryGetValue(table.GetType(), out tp)) { if (tp != null) { IControlOptionTableDefinition ctrl = Activator.CreateInstance(tp, new object[0]) as IControlOptionTableDefinition; return(ctrl); } } return(null); }
//-------------------------------------------------------------------------------- public void m_menuBrowse_Click(object sender, EventArgs e) { TreeNode node = SelectedNode; ITableDefinition table = GetTableForNode(node); if (table != null) { DataTable dataTable = m_sources.GetTable(table); if (dataTable != null) { CFormVisuTable.ShowTable(dataTable); } else { MessageBox.Show("Impossible de voir les données"); } } }
//--------------------------------------- public DataTable GetData(ITableDefinition tableDefinition, params string[] strIdsColonnesSource) { CTableDefinitionSNMP tableSNMP = tableDefinition as CTableDefinitionSNMP; if (tableSNMP != null && m_interrogateur != null) { return(GetTableSnmp(tableSNMP, strIdsColonnesSource)); } CTableDefinitionSnmpOfScalar tableScalars = tableDefinition as CTableDefinitionSnmpOfScalar; if (tableScalars != null && m_interrogateur != null) { return(GetTableScalars(tableScalars, strIdsColonnesSource)); } return(null); }
private void ReadIndexInfo(ITableDefinition tableDefinition, XmlNode index) { Debug.Assert(index != null, "index != null"); string indexName = index["IndexName"].InnerText; string location = index["Location"].InnerText; bool isUnique = Convert.ToBoolean(index["IsUnique"].InnerText); string db = index["DbSpecific"].InnerText; IIndexDefinition indexDefinition = _dbContext.PowerPlant.CreateIndexDefinition(indexName, tableDefinition.Name, location, isUnique); indexDefinition.DbSpecific = TableDefinition.ConvertStringToDbType(db); indexDefinition.Columns = new List <IIndexColumn>(); foreach (XmlNode indexCol in index["IndexColumns"].ChildNodes) { indexDefinition.Columns.Add(IndexColumnFactory.CreateInstance(indexCol.Name, indexCol.InnerText)); } tableDefinition.Indexes.Add(indexDefinition); }
internal async Task <TResult> _Load <TResult>(Type type, object key, CycleCache cache) where TResult : new() { Interlocked.Increment(ref _taskCount); try { var newType = type; var keyIndex = -1; ITableDefinition tableDef = null; tableDef = TableDefinitions.SingleOrDefault(pair => pair.Key == type).Value; if (tableDef == null) { foreach (var subDef in TableDefinitions.Where(pair => Database.Engine.PlatformAdapter.IsAssignableFrom(type, pair.Key)) .Select(pair => pair.Value)) { keyIndex = await subDef.Keys.GetIndexForKeyAsync(key).ConfigureAwait(false); if (keyIndex < 0) { continue; } newType = subDef.TableType; tableDef = subDef; break; } if (tableDef == null) { throw new SterlingTableNotFoundException(type, Name); } } else { keyIndex = await tableDef.Keys.GetIndexForKeyAsync(key).ConfigureAwait(false); } if (keyIndex < 0) { return(default);
//-------------------------------------------------------------------------------- private void CTreeListeTables_BeforeExpand(object sender, TreeViewCancelEventArgs e) { if (e.Node.Tag is CEasyQuerySource) { if (e.Node.Nodes.Count == 1 && e.Node.Nodes[0].Tag == null) { using (CWaitCursor waiter = new CWaitCursor()) { e.Node.Nodes.Clear(); CEasyQuerySource source = e.Node.Tag as CEasyQuerySource; FillNodes(e.Node.Nodes, source, source.RootFolder); } } } else if (e.Node.Tag is ITableDefinition) { if (e.Node.Nodes.Count == 1 && e.Node.Nodes[0].Tag == null) { e.Node.Nodes.Clear(); ITableDefinition table = e.Node.Tag as ITableDefinition; foreach (IColumnDefinition col in table.Columns) { TreeNode node = new TreeNode(col.ColumnName); node.Tag = col; node.ImageIndex = GetImageNum(col.ImageKey); node.SelectedImageIndex = node.ImageIndex; e.Node.Nodes.Add(node); } } } else if (e.Node.Tag is CEasyQuerySourceFolder) { if (e.Node.Nodes.Count == 1 && e.Node.Nodes[0].Tag == null) { CEasyQuerySourceFolder folder = e.Node.Tag as CEasyQuerySourceFolder; e.Node.Nodes.Clear(); FillNodes(e.Node.Nodes, folder.Source, folder); if (Nodes.Count == 1) { Nodes[0].Expand(); } } } }
public IEnumerable <IPersonalDataRow> ReadPersonalDataTable(object userId, string tableId, object ownerId) { if (userId is null) { throw new ArgumentNullException(nameof(userId)); } if (String.IsNullOrEmpty(tableId)) { throw new ArgumentException($"Parameter {nameof(tableId)} must not be null or empty", nameof(tableId)); } if (ownerId is null) { throw new ArgumentNullException(nameof(ownerId)); } lock (dataAccessLock) { CheckUserId(userId); CheckTableId(tableId); CheckOwnerId(ownerId); IEnumerable <string> columnIds = dataProvider.ListColumns(tableId).Select(c => c.ID); IEnumerable <object> rowIds = dataProvider.GetRowIdsForOwnerId(tableId, ownerId); CheckAccessibility(PurposeType.HoldingAndReading, ownerId, tableId, rowIds, columnIds); IEnumerable <IPersonalDataRow> personalDataRows = dataProvider.ReadPersonalDataRowsByOwner(tableId, ownerId); ITableDefinition table = dataProvider.GetTable(tableId); string tableLogName = table.Name ?? table.ID; if (personalDataRows.Any(p => p.PersonalDataCells.Any(c => c.IsDefined))) { WriteOwnerLog(ownerId, $"We accessed your personal data in the table \"{tableLogName}\" in all rows and columns"); } WriteUserLog(userId, $"Whole table \"{tableId}\" of owner ID \"{ownerId.ToString()}\" has been accessed"); return(personalDataRows); } }
public IPersonalDataRow ReadPersonalDataRow(object userId, string tableId, object rowId) { if (userId is null) { throw new ArgumentNullException(nameof(userId)); } if (String.IsNullOrEmpty(tableId)) { throw new ArgumentException($"Parameter {nameof(tableId)} must not be null or empty", nameof(tableId)); } if (rowId is null) { throw new ArgumentNullException(nameof(rowId)); } lock (dataAccessLock) { CheckUserId(userId); CheckTableId(tableId); CheckRowId(tableId, rowId); object ownerId = dataProvider.GetOwnerForRowId(tableId, rowId); IEnumerable <IColumnDefinition> columns = dataProvider.ListColumns(tableId); CheckAccessibility(PurposeType.HoldingAndReading, ownerId, tableId, rowId, columns.Select(c => c.ID)); IPersonalDataRow row = dataProvider.ReadPersonalDataRow(tableId, rowId); ITableDefinition table = dataProvider.GetTable(tableId); string tableLogName = table.Name ?? table.ID; if (row.PersonalDataCells.Any(r => r.IsDefined)) { WriteOwnerLog(ownerId, $"We accessed your personal data and read the whole row with ID \"{rowId.ToString()}\" in the table \"{tableLogName}\""); } WriteUserLog(userId, $"Whole row of owner ID \"{ownerId.ToString()}\" has been accessed in table \"{tableId}\""); return(row); } }
/////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////// /////////////////////////////////////////////////////////////////////////////////////////// /// <summary> /// Does export. /// </summary> /// <param name="filePath">Export file path.</param> /// <param name="table">Export table description.</param> /// <param name="schedules">Schedules to exporting.</param> /// <param name="tracker">Cancel tracker (can be null).</param> public void DoExport(string filePath, ITableDefinition table, ICollection <Schedule> schedules, ICancelTracker tracker) { Debug.Assert(!string.IsNullOrEmpty(filePath)); Debug.Assert(null != table); Debug.Assert(null != schedules); Debug.Assert((TableType.Routes == table.Type) || (TableType.Stops == table.Type) || (TableType.Orders == table.Type)); try { // create file using (var fs = new FileStream(filePath, FileMode.Create)) { using (var sw = new StreamWriter(fs)) { sw.AutoFlush = true; ICollection <string> fields = table.Fields; Debug.Assert(0 < table.Fields.Count); // write header sw.WriteLine(_AssemblyHeaderString(table, fields)); _CheckCancelState(tracker); _WriteContent(table.Type, schedules, fields, tracker, sw); } } } catch (Exception e) { if (File.Exists(filePath)) { File.Delete(filePath); } throw (e); } }
//------------------------------------------------------------------------------- private CColumnDefinitionSNMP GetSourceColumn( IObjetDeEasyQuery objetDeQuery, CChampEntiteFromQueryToChampCustom champ) { if (objetDeQuery == null || objetDeQuery.Query == null) { return(null); } ITableDefinition table = null; IColumnDefinition col = null; if (objetDeQuery.Query.FindSource(champ.Champ.ColonneSource, objetDeQuery, out table, out col)) { return(col as CColumnDefinitionSNMP); } return(null); }
public long LoadData(IDataReader reader, string fileName, ITableDefinition tableDefinition) { long rowsCopied; using (var sqlBulkCopy = new SqlBulkCopy(_dbContext.ConnectionString, SqlBulkCopyOptions.TableLock)) { sqlBulkCopy.DestinationTableName = tableDefinition.Name; sqlBulkCopy.EnableStreaming = true; sqlBulkCopy.BulkCopyTimeout = 0; sqlBulkCopy.BatchSize = BatchSize; using (reader) { sqlBulkCopy.WriteToServer(reader); rowsCopied = reader.RecordsAffected; } } return(rowsCopied); }
public static ITableRender <TModel> Get <TModel>(ITableDefinition <TModel> config, TableRequestModel model, ITableUrlManager urlManager) { switch (model.Format) { case TableRenderFormat.Html: return(new HtmlTableRender <TModel>(config, urlManager)); case TableRenderFormat.Json: return(new JsonArrayTableRender <TModel>(config)); case TableRenderFormat.Xml: return(new XmlTableRender <TModel>(config)); case TableRenderFormat.Csv: return(new CsvTableRender <TModel>(config)); } throw new Exception("Unknown table render type"); }
/// <summary> /// Adds fields to table. /// </summary> /// <param name="tableDefinition">Table definition.</param> /// <param name="tableDescription">Table Description.</param> /// <param name="columns">Database columns.</param> private void _AddFieldsToTable(ITableDefinition tableDefinition, TableDescription tableDescription, ADOX.Columns columns) { Debug.Assert(null != tableDefinition); Debug.Assert(null != tableDescription); Debug.Assert(null != columns); ICollection <string> fields = tableDefinition.Fields; foreach (string field in fields) { FieldInfo info = tableDescription.GetFieldInfo(field); Debug.Assert(null != info); columns.Append(info.Name, _ConvertType(info.Type), info.Size); // make field not required ADOX.Column column = columns[info.Name]; column.Attributes = ADOX.ColumnAttributesEnum.adColNullable; } }
//------------------------------------------------------------- public bool FindSource(IColumnDeEasyQuery colonne, IObjetDeEasyQuery objetContenantLaColonne, out ITableDefinition tableSource, out IColumnDefinition colSource) { CColumnEQFromSource colFromSource = colonne as CColumnEQFromSource; tableSource = null; colSource = null; if (colFromSource == null) { return(false); } CODEQFromObjetsSource objASource = objetContenantLaColonne as CODEQFromObjetsSource; if (objASource != null) { foreach (IObjetDeEasyQuery source in objASource.ElementsSource) { //Certaines tables n'ont pas leur propres colonnes (les filtres par exemple, mais contiennent directement //les colonnes de leur source. Il faut donc également chercher dans les colonnes source qui //ont l'id de la colonne. IColumnDeEasyQuery colDeSource = source.Columns.FirstOrDefault(c => c.Id == colFromSource.IdColumnSource || c.Id == colFromSource.Id); if (colDeSource != null) { return(FindSource(colDeSource, source, out tableSource, out colSource)); } } } CODEQTableFromBase objFromBase = objetContenantLaColonne as CODEQTableFromBase; if (objFromBase != null) { tableSource = objFromBase.TableDefinition; colSource = tableSource.Columns.FirstOrDefault(c => c.Id == colFromSource.IdColumnSource); return(colSource != null); } return(false); }
//----------------------------------------------------- public DataTable GetData( ITableDefinition tableDefinition, params string[] strIdsColonnesSource) { if (m_dataset != null) { foreach (DataTable table in m_dataset.Tables) { if ((string)table.ExtendedProperties[c_strTableId] == tableDefinition.Id) { DataTable tableFinale = null; tableFinale = table.Clone(); foreach (DataRow row in table.Rows) { tableFinale.ImportRow(row); } return(tableFinale); } } } return(null); }
public IPersonalDataRow ReadPersonalDataRow(object ownerId, string tableId, object rowId) { if (ownerId is null) { throw new ArgumentNullException(nameof(ownerId)); } if (String.IsNullOrEmpty(tableId)) { throw new ArgumentException($"Parameter {nameof(tableId)} must not be null or empty", nameof(tableId)); } if (rowId is null) { throw new ArgumentNullException(nameof(rowId)); } lock (dataAccessLock) { CheckOwnerId(ownerId); CheckTableId(tableId); CheckRowId(tableId, rowId); object trueOwnerId = dataProvider.GetOwnerForRowId(tableId, rowId); if (!ownerId.Equals(trueOwnerId)) { throw new PersonalDataDBException($"The requested row does not belong to the owner in the parameter {nameof(ownerId)}"); } IPersonalDataRow row = dataProvider.ReadPersonalDataRow(tableId, rowId); ITableDefinition table = dataProvider.GetTable(tableId); string tableLogName = table.Name ?? table.ID; WriteOwnerLog(ownerId, $"You accessed your personal data and read the whole row with ID \"{rowId.ToString()}\" in the table \"{tableLogName}\""); return(row); } }
private void WriteRow(long rowCounter, IDataReader reader, ITableDefinition tableDefinition, DataFileTextWriter dataWriter) { for (int i = 0; i < tableDefinition.Columns.Count; i++) { if (reader.IsDBNull(i)) { dataWriter.Write("NULL,"); continue; } if (tableDefinition.Columns[i].Type == ColumnTypeName.Blob || tableDefinition.Columns[i].Type == ColumnTypeName.OldBlob) { string rawFileName = $"i{rowCounter:D15}.raw"; dataWriter.Write(rawFileName); WriteBlobColumn(i, $@"{Directory}{tableDefinition.Name}\{rawFileName}", reader); } else { dataWriter.Write(tableDefinition.Columns[i].ToString(reader.GetValue(i))); } dataWriter.Write(","); } }
/// <summary> /// Adds keys to index of table. /// </summary> /// <param name="tableDescription">Table description.</param> /// <param name="tableDefinition">Table definition.</param> /// <param name="indexes">Database indexes.</param> private void _AddKeysToTableIndex(TableDescription tableDescription, ITableDefinition tableDefinition, ADOX.Indexes indexes) { ICollection <TableInfo> patternTables = _structureKeeper.GetPattern(ExportType.Access); foreach (TableInfo tableInfo in patternTables) { if (tableInfo.Type != tableDefinition.Type) { continue; // skip } foreach (TableIndex indexDefinition in tableInfo.Indexes) { if (_IsIndexFieldSelected(indexDefinition.FieldNames, tableDefinition.Fields)) { _AddKeyToTableIndex(tableDescription, indexDefinition, indexes); } } break; // process done } }
//-------------------------------------------------------------------------------- private void CTreeListeTables_ItemDrag(object sender, ItemDragEventArgs e) { TreeNode node = e.Item as TreeNode; if (node == null) { return; } ITableDefinition def = GetTableForNode(node); if (def != null) { IObjetDeEasyQuery objet = def.GetObjetDeEasyQueryParDefaut(); if (objet != null) { CDonneeDragDropObjetGraphique data = new CDonneeDragDropObjetGraphique( Name, objet, new Point(objet.Size.Width / 2, objet.Size.Height / 2)); DoDragDrop(data, DragDropEffects.Copy | DragDropEffects.None); } } }
public void WriteSchema(ITableDefinition tableDefinition, string fullPath) { XmlWriterSettings settings = new XmlWriterSettings(); settings.Indent = true; settings.NewLineOnAttributes = true; XmlWriter writer = XmlWriter.Create(fullPath, settings); try { writer.WriteStartDocument(); writer.WriteComment(string.Format("Table definition file for ACopy program")); BodyToXmlFile(tableDefinition, writer); writer.WriteEndDocument(); } finally { writer.Flush(); writer.Close(); } }
public static void CreateTable(CruiseDatastore db, ITableDefinition tableDef) { var createCommand = tableDef.CreateTable; db.Execute(createCommand); var createIndexes = tableDef.CreateIndexes; if (createIndexes != null) { db.Execute(createIndexes); } var createTombstone = tableDef.CreateTombstoneTable; if (createTombstone != null) { db.Execute(createTombstone); } var initialize = tableDef.InitializeTable; if (initialize != null) { db.Execute(initialize); } var triggers = tableDef.CreateTriggers; if (triggers != null) { foreach (var trigger in triggers) { db.Execute(trigger); } } }
public bool WriteTable(string tableName) { try { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); _logger.Write($"{tableName,30} Started..."); var xmlSchema = XmlSchemaFactory.CreateInstance(_dbContext); if (UseU4Indexes) { xmlSchema.U4Indexes = U4IndexesFactory.CreateInstance(_dbContext); } ITableDefinition tableDefinition = xmlSchema.Write(Directory, tableName, SchemaFileSuffix); if (tableDefinition.HasBlobColumn) { System.IO.Directory.CreateDirectory($@"{Directory}{tableDefinition.Name}"); } long rowCounter = WriteTableDataToDataFile(tableDefinition, DataFileSuffix); stopWatch.Stop(); string message = $"{tableDefinition.Name,30} {rowCounter,10} rows copied. Time: {stopWatch.Elapsed}"; _logger.Write(message); } catch (Exception ex) { _logger.Write($"ERROR with table {tableName}"); _logger.Write(ex); return(false); } return(true); }
/// <summary> /// Gets the create table SQL. /// </summary> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <param name="isTemporary">if set to <c>true</c> [is temporary].</param> /// <returns>System.String.</returns> protected override string GetCreateTableSql(ITableDefinition table, string prefix = null, bool isTemporary = false) { var result = new StringBuilder(); result.AppendFormat("CREATE TABLE {2}{0}({1}", table.Name, Environment.NewLine, prefix ?? string.Empty); foreach (var field in table.FieldList.Where(field => field.GenerateDbColumn && !string.IsNullOrWhiteSpace(field.ColumnName) && field.ColumnType != ColumnTypes.AuditTrail)) { result.AppendFormat("\t[{0}] {1}", field.ColumnName, GetSqlType(field)); if (!string.IsNullOrEmpty(field.DefaultValue)) result.Append(GetDefaultValueClause(field)); if (field.IsIdentity) result.Append("IDENTITY(1,1)"); if (field.IsPrimaryKey) result.Append(" NOT NULL"); if (field.ColumnName == Constants.LastModifiedColumnName) result.Append("NOT NULL DEFAULT GetDate()"); result.AppendLine(","); } var idField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.IdColumnName && f.GenerateDbColumn); var versionMasterIdField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.VersionMasterId && f.GenerateDbColumn); if (idField != null && versionMasterIdField != null) { result.AppendFormat( CultureInfo.InvariantCulture, "[{0}] AS (CASE WHEN [{1}] > 0 THEN [{1}] ELSE [{2}] END) PERSISTED,", Constants.VersionSeriesIdColumnName, Constants.VersionMasterId, Constants.IdColumnName).AppendLine(); } if (!Constants.SealedProcesses.Contains(table.Name)) { // Add a computed column that retrieves the derived process display name from DerivedProcess column. var derivedProcessField = table.FieldList.FirstOrDefault(f => f.SystemName == Constants.DerivedProcessColumnName); if (derivedProcessField != null) { result.AppendFormat(CultureInfo.InvariantCulture, @"[{1}] AS (CASE WHEN LEN({0}) - LEN(REPLACE({0}, '|', '')) = 2 THEN REVERSE(SUBSTRING(REVERSE({0}), 1, CHARINDEX('|', REVERSE({0})) - 1)) WHEN LEN({0}) - LEN(REPLACE({0}, '|', '')) = 1 THEN SUBSTRING({0}, 1, CHARINDEX('|', {0}) - 1) ELSE {0} END) PERSISTED,", Constants.DerivedProcessColumnName, Constants.DerivedProcessDisplayNameColumnName).AppendLine(); } } result.AppendLine(")"); //TODO: this call can be removed after september, 2013 //remove autoNumber field index result.AppendLine(GetAutoNumberConstraint(table)); return result.ToString(); }
/// <summary> /// Gets the identity insert SQL. /// </summary> /// <param name="on">if set to <c>true</c> [on].</param> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected override string GetIdentityInsertSql(bool @on, ITableDefinition table, string prefix = null) { return string.Format("SET IDENTITY_INSERT {1}{0} {2} {3}", table.Name, prefix, on ? "ON" : "OFF", Environment.NewLine); }
/// <summary> /// Generates a script that returns the id mappings for the specified cross-reference field. /// </summary> /// <param name="table"> /// The table that contains the cross-reference field. /// </param> /// <param name="referenceField"> /// The cross-reference field. /// </param> /// <returns> /// The script. /// </returns> private string GetCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition referenceField) { if (referenceField.ColumnType == ColumnTypes.Reference) { return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.SystemName); } if (referenceField.ColumnType == ColumnTypes.MultiReference) { if (referenceField.ReferenceField != null) { // MCR with link field. return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.ReferenceProcess.SystemName, referenceField.ReferenceField.SystemName); } else { // MCR with junction table. var junctionTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName( table.Name, referenceField.SystemName, referenceField.ReferenceProcess.SystemName); var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(table.Name, referenceField.ReferenceProcess.SystemName); var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(referenceField.ReferenceProcess.SystemName); return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, junctionTableName, masterKey, referenceField.ReferenceProcess.SystemName, childKey); } } throw new NotSupportedException("Invalid reference field."); }
/// <summary> /// Gets the definition of the view that returns the id mappings for the specified reference field. /// </summary> /// <param name="table"> /// The table that contains the reference field. /// </param> /// <param name="referenceField"> /// The reference field. /// </param> /// <returns> /// The <see cref="ViewDefinition"/>. /// </returns> private ViewDefinition GetReferenceFieldIdMappingsView(ITableDefinition table, TableFieldDefinition referenceField) { var viewName = QueryGeneratorUtils.GetReferenceFieldIdMappingsViewName(table.Name, referenceField.SystemName); var sql = new StringBuilder(); sql.AppendFormat(CultureInfo.InvariantCulture, @"CREATE VIEW [dbo].[{0}] AS (", viewName); sql.Append(GetReferenceFieldIdMappingsSelect(table, referenceField)); sql.AppendFormat(CultureInfo.InvariantCulture, @" )"); return new ViewDefinition { Name = viewName, Definition = sql.ToString() }; }
/// <summary> /// Gets the rename table SQL. /// </summary> /// <param name="dbTable">The database table.</param> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected override string GetRenameTableSql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null) { return string.Format("EXECUTE sp_rename N'{1}{0}', N'{2}', 'OBJECT'", table.Name, prefix, dbTable.TableName); }
/// <summary> /// Extension to register an index /// </summary> /// <typeparam name="T">The type of the table</typeparam> /// <typeparam name="TIndex1">The index</typeparam> /// <typeparam name="TIndex2">The second index</typeparam> /// <typeparam name="TKey">The key</typeparam> /// <param name="table">The table definition</param> /// <param name="name">The name of the index</param> /// <param name="indexer">The indexer</param> /// <returns>The table</returns> public static ITableDefinition WithIndex <T, TIndex1, TIndex2, TKey>(this ITableDefinition table, string name, Func <T, Tuple <TIndex1, TIndex2> > indexer) where T : class, new() { ((TableDefinition <T, TKey>)table).RegisterIndex(name, indexer); return(table); }
/// <summary> /// Gets the create audit table. /// </summary> /// <param name="table">The table.</param> /// <returns>System.String.</returns> protected override string GetCreateAuditTable(ITableDefinition table) { if (Constants.NonAuditableProcesses.Contains(table.Name)) { return string.Format(@" IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('audit.{0}')) DROP TABLE [audit].[{0}] ", table.Name); } var result = GetCreateAuditSchema(); return result + string.Format(@" IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[audit].[{0}]') AND type in (N'U')) BEGIN CREATE TABLE [audit].[{0}]( [Id] [int] IDENTITY(1,1) NOT NULL, [Type] [char](1) NULL, [ItemId] [int] NOT NULL, [FieldName] [nvarchar](128) NULL, [OldValue] [nvarchar](MAX) NULL, [NewValue] [nvarchar](MAX) NULL, [UpdateDate] [datetime] NOT NULL DEFAULT (getdate()), [UserName] [nvarchar](128) NULL, CONSTRAINT [PK_{0}_AuditId] PRIMARY KEY CLUSTERED ( [Id] DESC ) ) END IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_ItemId' ) BEGIN CREATE NONCLUSTERED INDEX IX_{0}_ItemId ON audit.{0}(ItemId) END IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_FieldName' ) BEGIN DROP INDEX IX_{0}_FieldName ON audit.{0} END IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_Id' ) BEGIN DROP INDEX IX_{0}_Id ON audit.{0} END IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_UpdateDate' ) BEGIN DROP INDEX IX_{0}_UpdateDate ON audit.{0} END IF EXISTS ( SELECT 1 FROM sys.indexes WHERE object_id = object_id('audit.{0}') AND name = 'IX_{0}_UserName' ) BEGIN DROP INDEX IX_{0}_UserName ON audit.{0} END IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRichTextText]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'-- ============================================= -- Author: CEBOS, Inc. -- Create date: 10/10/2012 -- Description: Extract body from html for the radRichText -- ============================================= CREATE FUNCTION [dbo].[GetRichTextText] ( @text NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @startIndex INT, @endIndex INT DECLARE @body NVARCHAR(MAX) SET @startIndex = CHARINDEX(''<body>'', @text) IF @startIndex = 0 RETURN @text SET @startIndex = @startIndex + 6 SET @endIndex = CHARINDEX(''</body>'', @text, @startIndex) IF @endIndex = 0 RETURN @text RETURN SUBSTRING(@text, @startIndex, @endIndex - @startIndex) END ' END ", table.Name); }
private static DataIndexDbDefinition CreateIndexDefinition(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null) { var tableName = prefix + table.Name; var indexColumns = new[] { relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName }; var filter = relationship.ExcludeRemovedItems ? "([IsRemoved]=(0))" : string.Empty; var index = new DataIndexDbDefinition(GetForeignKeyIndexName(tableName, indexColumns), tableName); index.IndexFields.AddRange(indexColumns.Select(columnName => new DataIndexFieldDbDefinition(columnName, true))); index.FilterDefinition = filter; return index; }
/// <summary> /// Gets the primary key alter constraint SQL. /// </summary> /// <param name="artifPkField">The artif pk field.</param> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected override string GetPrimaryKeyAlterConstraintSql(IEnumerable<TableFieldDefinition> artifPkField, ITableDefinition table, string prefix = null) { var columnsList = from c in artifPkField select string.Format("[{0}] DESC", c.ColumnName); return string.Format( @" ALTER TABLE [{2}{0}] ADD CONSTRAINT [PK_{2}{0}] PRIMARY KEY CLUSTERED({1}) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) {3} ", table.Name, string.Join(",", columnsList), prefix, CnGo); }
/// <summary> /// Adds fields to table. /// </summary> /// <param name="tableDefinition">Table definition.</param> /// <param name="tableDescription">Table Description.</param> /// <param name="columns">Database columns.</param> private void _AddFieldsToTable(ITableDefinition tableDefinition, TableDescription tableDescription, ADOX.Columns columns) { Debug.Assert(null != tableDefinition); Debug.Assert(null != tableDescription); Debug.Assert(null != columns); ICollection<string> fields = tableDefinition.Fields; foreach (string field in fields) { FieldInfo info = tableDescription.GetFieldInfo(field); Debug.Assert(null != info); columns.Append(info.Name, _ConvertType(info.Type), info.Size); // make field not required ADOX.Column column = columns[info.Name]; column.Attributes = ADOX.ColumnAttributesEnum.adColNullable; } }
/// <summary> /// Gets the create indexes SQL. /// </summary> /// <param name="table">The table.</param> /// <returns>System.String.</returns> protected override string GetCreateIndexesSql(ITableDefinition table) { var result = string.Empty; if (table.FieldList.Any(f => !string.IsNullOrEmpty(f.SystemName) && f.SystemName.Equals("IsRemoved"))) result += string.Format(@" IF NOT EXISTS (SELECT * FROM sys.indexes i WHERE name = N'IX_{0}_IsRemoved') CREATE NONCLUSTERED INDEX IX_{0}_IsRemoved ON dbo.{0} (IsRemoved)", table.Name); if (table.FieldList.Any(f => !string.IsNullOrEmpty(f.SystemName) && f.SystemName.Equals("VersionMasterId"))) result += string.Format(@" IF NOT EXISTS (SELECT * FROM sys.indexes i WHERE name = N'IX_{0}_VersionMasterId') CREATE NONCLUSTERED INDEX [IX_{0}_VersionMasterId] ON [dbo].[{0}] ([VersionMasterId]) INCLUDE ([Id])", table.Name); return result; }
/// <summary> /// Gets the data copy SQL. /// </summary> /// <param name="dbTable">The database table.</param> /// <param name="table">The table.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected override string GetDataCopySql(DbTableDefinition dbTable, ITableDefinition table, string prefix = null) { var result = new StringBuilder(); var insertColumnList = new StringBuilder(); var selectColumnList = new StringBuilder(); // copy all fields foreach (var field in table.FieldList) { if (!field.GenerateDbColumn) continue; var dbColumn = dbTable.Columns.FirstOrDefault(x => x.ColumnName == field.ColumnName); // only if they present in old table if (dbColumn == null) continue; insertColumnList.Append("[").Append(field.ColumnName).Append("],"); var sqlType = GetSqlType(field); // convert values var oldType = GetSqlType(dbColumn); var newType = GetSqlType(field).ToLower(); if (AreSameDataTypes(oldType, newType) == false) { var defaultValue = string.IsNullOrEmpty(field.DefaultValue) ? "NULL" : field.DefaultValue; if (IsNumericType(newType)) selectColumnList.Append("CASE WHEN IsNumeric(") .Append(dbColumn.ColumnName) .Append(")=1 THEN ") .Append("CAST(REPLACE([") .Append(dbColumn.ColumnName) .Append("], ',', '.') AS ") .Append(sqlType) .Append(") ELSE ") .Append(defaultValue) .Append(" END,"); else if (IsDateType(newType)) selectColumnList.Append("CASE WHEN IsDate(") .Append(dbColumn.ColumnName) .Append(")=1 THEN ") .Append("CAST([") .Append(dbColumn.ColumnName) .Append("] AS ") .Append(sqlType) .Append(") ELSE NULL END,"); else if (IsStringType(newType)) selectColumnList.Append("CAST([").Append(dbColumn.ColumnName).Append("] AS ").Append(sqlType).Append("),"); else selectColumnList.Append(defaultValue).Append(","); } else if (dbColumn.ColumnType == "nvarchar" && dbColumn.ColumnSubType == string.Empty && newType != "nvarchar" && newType.Contains("nvarchar")) { selectColumnList.Append("CONVERT(") .Append(newType) .Append(",") .Append(dbColumn.ColumnName) .Append("),"); } else selectColumnList.Append("[").Append(dbColumn.ColumnName).Append("],"); } //if a key field has been changed var keyFields = table.FieldList.Where(x => x.IsPrimaryKey); var insertColumnListString = insertColumnList.ToString(); foreach (var field in keyFields) { if (!insertColumnListString.Contains(field.ColumnName)) { Log4NetLogger.Instance.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", string.Format("Failed to save data for the table '{0}'. Missing key column '{1}'.", dbTable.TableName, field.ColumnName)); return string.Empty; } } insertColumnList.Remove(insertColumnList.Length - 1, 1); selectColumnList.Remove(selectColumnList.Length - 1, 1); result.Append("IF EXISTS(SELECT * FROM ").Append(dbTable.TableName).AppendLine(")"); result.Append("INSERT INTO [").Append(prefix).Append(table.Name).Append("] (").Append(insertColumnList).AppendLine(")"); result.Append("SELECT ").Append(selectColumnList).Append(" FROM [").Append(dbTable.TableName).AppendLine("] WITH (HOLDLOCK TABLOCKX)"); return result.ToString(); }
/// <summary> /// Generates a script that returns the id mappings for the specified reference (cross-reference, reverse cross-reference or tree-view) field. /// </summary> /// <param name="table"> /// The table that contains the reference field. /// </param> /// <param name="field"> /// The reference field. /// </param> /// <returns> /// The script. /// </returns> private string GetReferenceFieldIdMappingsSelect(ITableDefinition table, TableFieldDefinition field) { if (field.IsRef) { return GetCrossReferenceIdMappingsSelect(table, field); } if (field.IsReverseRef) { return GetReverseCrossReferenceIdMappingsSelect(table, field); } throw new NotSupportedException("Invalid reference field."); }
/// <summary> /// Generates a script that drops the full-text index on the specified table. /// </summary> /// <param name="table"> /// The table. /// </param> /// <param name="forceDrop"> /// A value indicating whether the index should be dropped even if its definition hasn't changed. /// </param> /// <returns> /// The script. /// </returns> protected override string GetDropFullTextIndexScript(ITableDefinition table, bool forceDrop = false) { var sql = new StringBuilder(); if (IsFullTextInstalled()) { var currentIndex = GetExistingFullTextIndexDefinition(table); if (currentIndex != null) { var dropIndex = forceDrop || !IsFullTextSearchSupported(table); if (!dropIndex) { // Check if index structure has changed. var newIndex = GetFullTextIndexDefinition(table); if (!currentIndex.Equals(newIndex)) { dropIndex = true; } } if (dropIndex) { sql.AppendFormat(CultureInfo.InvariantCulture, @" IF EXISTS( SELECT 1 FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID('[dbo].[{0}]')) BEGIN DROP FULLTEXT INDEX ON [dbo].[{0}] END GO ", table.Name); } } } return sql.ToString(); }
/// <summary> /// Generates a script that returns the id mappings for the specified reverse cross-reference or tree-view field. /// </summary> /// <param name="table"> /// The table that contains the reverse cross-reference or tree-view field. /// </param> /// <param name="reverseReferenceField"> /// The reverse cross-reference or tree-view field. /// </param> /// <returns> /// The script. /// </returns> private string GetReverseCrossReferenceIdMappingsSelect(ITableDefinition table, TableFieldDefinition reverseReferenceField) { var referenceField = reverseReferenceField.ReferenceField; if (reverseReferenceField.ColumnType == ColumnTypes.ReverseReference) { if (referenceField.ColumnType == ColumnTypes.Reference) { if (reverseReferenceField.ShowLatestVersion) { return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId] FROM [dbo].[{0}] t1 CROSS APPLY ( SELECT TOP 1 t2.[Id] FROM [dbo].[{1}] t2 WHERE t2.[Id] = ( SELECT TOP 1 t3.[Id] FROM [dbo].[{1}] t3 WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}] ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC) ORDER BY t2.[Id] ) t4 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 CROSS APPLY ( SELECT TOP 1 [Id] FROM [dbo].[{1}] WHERE [IsRemoved] = 0 AND [{2}] = t1.[Id]) t2 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName); } if (referenceField.ColumnType == ColumnTypes.MultiReference) { if (referenceField.ReferenceField != null) { // MCR with link field. // Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version. return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName); } // MCR with join table. var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName( referenceField.DefinedIn.SystemName, referenceField.SystemName, table.Name); var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name); var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name); if (reverseReferenceField.ShowLatestVersion) { return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t5.[Id] AS [RefId] FROM [dbo].[{0}] t1 CROSS APPLY ( SELECT TOP 1 t2.[Id] FROM [dbo].[{1}] t2 WHERE t2.[Id] = ( SELECT TOP 1 t3.[Id] FROM [dbo].[{1}] t3 INNER JOIN [dbo].[{2}] t4 ON t4.[{3}] = t3.[Id] WHERE t3.[IsRemoved] = 0 AND t3.[{4}] = t2.[{4}] AND t4.[{5}] = t1.[Id] ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC) ORDER BY t2.[Id] ) t5 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, joinTableName, masterKey, Constants.VersionSeriesIdColumnName, childKey); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t4.[Id] AS [RefId] FROM [dbo].[{0}] t1 CROSS APPLY ( SELECT TOP 1 t3.[Id] FROM [dbo].[{1}] t2 INNER JOIN [dbo].[{2}] t3 ON t3.[Id] = t2.[{3}] AND t3.[IsRemoved] = 0 WHERE t2.[{4}] = t1.[Id] ORDER BY t3.[Id]) t4 WHERE t1.[IsRemoved] = 0 ", table.Name, joinTableName, referenceField.DefinedIn.SystemName, masterKey, childKey); } if (referenceField.ColumnType == ColumnTypes.Checklist) { return string.Format( CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, QueryGeneratorUtils.GetChecklistJoinTableName(referenceField.DefinedIn.SystemName, referenceField.SystemName), QueryGeneratorUtils.GetChecklistJoinTableChildKey(referenceField.ReferencedProcess.SystemName), referenceField.DefinedIn.SystemName, QueryGeneratorUtils.GetChecklistJoinTableMasterKey(referenceField.DefinedIn.SystemName)); } throw new NotSupportedException("Invalid reference field."); } if (reverseReferenceField.ColumnType == ColumnTypes.ReverseMultiReference) { if (referenceField.ColumnType == ColumnTypes.Reference) { if (reverseReferenceField.ShowLatestVersion) { return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 AND t2.[Id] = ( SELECT TOP 1 t3.[Id] FROM [dbo].[{1}] t3 WHERE t3.[IsRemoved] = 0 AND t3.[{2}] = t1.[Id] AND t3.[{3}] = t2.[{3}] ORDER BY LEN(t3.[VersionNumber]) DESC, t3.[VersionNumber] DESC) ", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName, Constants.VersionSeriesIdColumnName); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, referenceField.SystemName); } if (referenceField.ColumnType == ColumnTypes.MultiReference) { if (referenceField.ReferenceField != null) { // MCR with link field. // Note: A RCR based on a MCR with link field will always return just one item, therefore we don't need to check latest version. return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, referenceField.DefinedIn.SystemName, referenceField.ReferenceField.SystemName); } // MCR with join table. var joinTableName = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableName( referenceField.DefinedIn.SystemName, referenceField.SystemName, table.Name); var masterKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableMasterKey(referenceField.DefinedIn.SystemName, table.Name); var childKey = QueryGeneratorUtils.GetMultiCrossReferenceJoinTableChildKey(table.Name); if (reverseReferenceField.ShowLatestVersion) { return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 AND t3.[Id] = ( SELECT TOP 1 t4.[Id] FROM [dbo].[{3}] t4 INNER JOIN [dbo].[{1}] t5 ON t5.[{4}] = t4.[Id] WHERE t4.[IsRemoved] = 0 AND t4.[{5}] = t3.[{5}] AND t5.[{2}] = t1.[Id] ORDER BY LEN(t4.[VersionNumber]) DESC, t4.[VersionNumber] DESC) ", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey, Constants.VersionSeriesIdColumnName); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] INNER JOIN [dbo].[{3}] t3 ON t3.[Id] = t2.[{4}] AND t3.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, joinTableName, childKey, referenceField.DefinedIn.SystemName, masterKey); } throw new NotSupportedException("Invalid reference field."); } if (reverseReferenceField.ColumnType == ColumnTypes.TreeView) { if (!string.IsNullOrEmpty(reverseReferenceField.ReferenceMatchField) && reverseReferenceField.ReferenceMatchField != Constants.IdColumnName) { var matchField = table.FieldList.FirstOrDefault(f => f.SystemName == reverseReferenceField.ReferenceMatchField); if (matchField == null) { throw new InvalidOperationException( string.Format("Cannot find match field '{0}' in table '{1}'.", reverseReferenceField.ReferenceMatchField, table.Name)); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t3.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[Id] = t1.[{2}] AND t2.[IsRemoved] = 0 INNER JOIN [dbo].[{3}] t3 ON t3.[{4}] = t2.[Id] AND t3.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, matchField.ReferenceProcess.SystemName, matchField.ColumnName, referenceField.DefinedIn.SystemName, referenceField.SystemName); } return string.Format(CultureInfo.InvariantCulture, @" SELECT t1.[Id] AS [Id], t2.[Id] AS [RefId] FROM [dbo].[{0}] t1 INNER JOIN [dbo].[{1}] t2 ON t2.[{2}] = t1.[Id] AND t2.[IsRemoved] = 0 WHERE t1.[IsRemoved] = 0 ", table.Name, reverseReferenceField.ReferenceProcess.SystemName, referenceField.SystemName); } throw new NotSupportedException("Invalid reference field."); }
private static FullTextIndexDefinition GetExistingFullTextIndexDefinition(ITableDefinition table) { const string CommandText = @" DECLARE @tableId INT = OBJECT_ID(@tableName) SELECT OBJECT_NAME(i.object_id) ,fc.name FROM sys.fulltext_indexes i INNER JOIN sys.fulltext_catalogs fc ON fc.fulltext_catalog_id = i.fulltext_catalog_id WHERE i.object_id = @tableId SELECT c.name ,ic.language_id FROM sys.fulltext_index_columns ic INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.object_id = @tableId"; var fullTableName = string.Format(CultureInfo.InvariantCulture, "[dbo].[{0}]", table.Name); using (var connectionManager = GetRuntimeDatabaseConnectionManager()) { using (var cmd = new SqlCommand(CommandText, connectionManager.Connection)) { cmd.Parameters.AddWithValue("@tableName", fullTableName); using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { var index = new FullTextIndexDefinition { TableName = reader.GetString(0), CatalogName = reader.GetString(1) }; if (reader.NextResult()) { while (reader.Read()) { var column = new FullTextIndexColumnDefinition { ColumnName = reader.GetString(0), LCID = reader.GetInt32(1) }; index.Columns.Add(column); } } return index; } return null; } } } }
/// <summary> /// Generates a script that creates the full-text index on the specified table. /// </summary> /// <param name="table"> /// The table. /// </param> /// <returns> /// The script. /// </returns> protected override string GetCreateFullTextIndexScript(ITableDefinition table) { if (!IsFullTextSearchSupported(table)) { return string.Empty; } var sql = new StringBuilder(); var index = GetFullTextIndexDefinition(table); if (index != null && index.Columns.Any()) { sql.AppendFormat( CultureInfo.InvariantCulture, @" IF NOT EXISTS( SELECT 1 FROM sys.fulltext_catalogs ftc WHERE ftc.name = '{2}' ) BEGIN CREATE FULLTEXT CATALOG {2} WITH ACCENT_SENSITIVITY = OFF AUTHORIZATION [dbo] END IF NOT EXISTS( SELECT 1 FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID('[dbo].[{0}]')) BEGIN CREATE FULLTEXT INDEX ON [dbo].[{0}] ({1}) KEY INDEX PK_{0} ON {2} WITH STOPLIST = OFF END ", table.Name, string.Join(", ", index.Columns.Select(x => string.Format("[{0}] LANGUAGE {1}", x.ColumnName, x.LCID))), Constants.SqlServerFullTextCatalogName); } return sql.ToString(); }
/// <summary> /// Generate trigger code for every process field. /// </summary> /// <param name="table">Table where we create this trigger.</param> /// <param name="result">Result code.</param> /// <param name="isForInsert">true if insert trigger is generating, false if update trigger.</param> private static void GenerateAuditTriggerFields(ITableDefinition table, StringBuilder result, bool isForInsert) { foreach (var fieldDefinition in table.FieldList.Where(f => (!f.IsHidden || f.IsLocalizedCopy) && !f.IsSystemField && f.GenerateDbColumn) .Where( fieldDefinition => fieldDefinition.ColumnType != ColumnTypes.Image && fieldDefinition.ColumnType != ColumnTypes.AuditTrail)) { if (fieldDefinition.ColumnName == Constants.CurrentStateColumnName) { if (isForInsert) { result.AppendFormat(@" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, si.Name, i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i LEFT OUTER JOIN [{2}] si ON si.Id = i.[{3}] ", table.Name, fieldDefinition.ColumnName, Constants.StateProcessName, Constants.CurrentStateColumnName); } else { result.AppendFormat(@" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', sd.Name, si.Name, i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id LEFT OUTER JOIN [{2}] sd ON sd.Id = d.[{3}] LEFT OUTER JOIN [{2}] si ON si.Id = i.[{3}] WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName, Constants.StateProcessName, Constants.CurrentStateColumnName); } } else if (fieldDefinition.ColumnType == ColumnTypes.Reference) { if ( fieldDefinition.ReferenceDisplayFields.Any( x => x.SystemName == fieldDefinition.ReferencedColumnName && (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef))) { continue; } if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable) || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName)) { continue; } if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName)) { if (isForInsert) { result.AppendFormat( @" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, 'Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i LEFT OUTER JOIN [{2}] si ON si.Id = i.[{1}] ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName); } else { result.AppendFormat( @" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', 'Process={2}, Id=' + CAST(sd.Id AS VARCHAR(10)) + ', ' + CAST(sd.[{3}] AS NVARCHAR(MAX)), 'Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id LEFT OUTER JOIN [{2}] sd ON sd.Id = d.[{1}] LEFT OUTER JOIN [{2}] si ON si.Id = i.[{1}] WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName); } } else { if (isForInsert) { result.AppendFormat( @" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, 'Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i LEFT OUTER JOIN dbo.[{4}] si ON i.[{1}] = si.Id INNER JOIN [{2}] spi ON spi.Id = si.ParentId ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName, fieldDefinition.ReferencedProcessName); } else { result.AppendFormat( @" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', 'Process={2}, Id=' + CAST(spd.Id AS VARCHAR(10)) + ', ' + CAST(spd.[{3}] AS NVARCHAR(MAX)), 'Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id LEFT OUTER JOIN dbo.[{4}] sd ON d.[{1}] = sd.Id LEFT OUTER JOIN dbo.[{4}] si ON i.[{1}] = si.Id LEFT OUTER JOIN [{2}] spd ON spd.Id = sd.ParentId LEFT OUTER JOIN [{2}] spi ON spi.Id = si.ParentId WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName, fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferencedColumnName, fieldDefinition.ReferencedProcessName); } } } else if (fieldDefinition.ColumnType == ColumnTypes.File) { if (isForInsert) { result.AppendFormat(@" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, COALESCE(NULLIF(fp.OriginalFileName,''), NULLIF(fp.ReportName,''), ''), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i LEFT OUTER JOIN dbo.[{2}] fp ON fp.Id = i.[{1}] ", table.Name, fieldDefinition.ColumnName, Constants.FileProcessName); } else { result.AppendFormat(@" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName); } } else if (fieldDefinition.ColumnType == ColumnTypes.Approval) { if (isForInsert) { result.AppendFormat( @" INSERT INTO [audit].[{0}] SELECT 'I', i.[Id], '{1}', NULL, {2}, i.[LastModifiedOn], i.[LastModifiedBy] FROM INSERTED i LEFT OUTER JOIN [dbo].[{3}] ap ON ap.[Id] = i.[{4}] ", table.Name, AdoHelper.Escape(fieldDefinition.DisplayName), ConvertApprovalStateToDisplayName("ap.[ApprovalState]"), Constants.ApprovalProcessName, fieldDefinition.ColumnName); } else { result.AppendFormat( @" IF UPDATE([{0}]) INSERT INTO [audit].[{1}] SELECT 'U', d.[Id], '{2}', {3}, {4}, i.[LastModifiedOn], i.[LastModifiedBy] FROM DELETED d INNER JOIN INSERTED i ON i.[Id] = d.[Id] LEFT OUTER JOIN [dbo].[{5}] a1 ON a1.[Id] = d.[{0}] LEFT OUTER JOIN [dbo].[{5}] a2 ON a2.[Id] = i.[{0}] WHERE (d.[{0}] IS NULL AND i.[{0}] IS NOT NULL) OR (i.[{0}] IS NULL AND d.[{0}] IS NOT NULL) OR (i.[{0}] <> d.[{0}]) ", fieldDefinition.ColumnName, table.Name, AdoHelper.Escape(fieldDefinition.DisplayName), ConvertApprovalStateToDisplayName("a1.[ApprovalState]"), ConvertApprovalStateToDisplayName("a2.[ApprovalState]"), Constants.ApprovalProcessName); } } else if (fieldDefinition.AllowRichText) { if (isForInsert) { result.AppendFormat(@" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, dbo.GetRichTextText(i.[{1}]), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i ", table.Name, fieldDefinition.ColumnName); } else { result.AppendFormat(@" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', dbo.GetRichTextText(d.[{1}]), dbo.GetRichTextText(i.[{1}]), i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName); } } else if (GetSqlType(fieldDefinition) == "[xml]") { if (isForInsert) { if (fieldDefinition.ColumnType == ColumnTypes.Choice) { result.AppendFormat( @" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, (SELECT STUFF( (SELECT CHAR(13) + CHAR(10) + ISNULL(Choice, '') FROM ( SELECT CASE WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 THEN 'Global Choice' ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' + 'Score: ' + choice.value('@Score', 'nvarchar(50)') END AS Choice FROM i.[{1}].nodes('/ChoiceInfo/Choice') col(choice) ) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i ", table.Name, fieldDefinition.ColumnName); } else { result.AppendFormat( @" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, CAST(i.[{1}] AS nvarchar(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i ", table.Name, fieldDefinition.ColumnName); } } else { if (fieldDefinition.ColumnType == ColumnTypes.Choice) { result.AppendFormat( @" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', (SELECT STUFF( (SELECT CHAR(13) + CHAR(10) + ISNULL(Choice, '') FROM ( SELECT CASE WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 THEN 'Global Choice' ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' + 'Score: ' + choice.value('@Score', 'nvarchar(50)') END AS Choice FROM d.[{1}].nodes('/ChoiceInfo/Choice') col(choice) ) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')), (SELECT STUFF( (SELECT CHAR(13) + CHAR(10) + ISNULL(Choice, '') FROM ( SELECT CASE WHEN choice.value('../@IsGlobalChoice', 'bit') = 1 THEN 'Global Choice' ELSE 'Choice: ' + choice.value('@Choice', 'nvarchar(50)') + ', ' + 'Acceptance criteria: ' + choice.value('@AcceptanceCriteria', 'nvarchar(50)') + ', ' + 'Score: ' + choice.value('@Score', 'nvarchar(50)') END AS Choice FROM i.[{1}].nodes('/ChoiceInfo/Choice') col(choice) ) AS T FOR XML PATH(''), ROOT('root'), TYPE).value('/root[1]', 'NVARCHAR(MAX)'), 1, 2, '')), i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) ", table.Name, fieldDefinition.ColumnName); } else { result.AppendFormat( @" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U', d.Id, '{1}', CAST(d.[{1}] AS nvarchar(MAX)), CAST(i.[{1}] AS nvarchar(MAX)), i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) ", table.Name, fieldDefinition.ColumnName); } } } else { if (isForInsert) { result.AppendFormat(@" INSERT INTO [audit].[{0}] SELECT 'I', i.Id, '{1}', NULL, i.[{1}], i.LastModifiedOn, i.LastModifiedBy FROM INSERTED i ", table.Name, fieldDefinition.ColumnName); } else { result.AppendFormat(@" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'U',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, fieldDefinition.ColumnName); } } } }
private FullTextIndexDefinition GetFullTextIndexDefinition(ITableDefinition table) { var index = new FullTextIndexDefinition { TableName = table.Name, CatalogName = Constants.SqlServerFullTextCatalogName }; foreach (var field in table.FieldList.Where(IncludeInFullTextIndex)) { var columnName = field.ColumnName; if (columnName == Constants.DerivedProcessColumnName) { if (Constants.SealedProcesses.Contains(table.Name)) { continue; } columnName = Constants.DerivedProcessDisplayNameColumnName; } index.Columns.Add(new FullTextIndexColumnDefinition { ColumnName = columnName, LCID = GetFullTextLanguageId(field.CultureName) }); } return index; }
/// <summary>Gets the audit trigger script for multi cr table.</summary> /// <param name="table">The table.</param> /// <param name="fieldDefinition">The field definition.</param> /// <param name="primaryTable">The primary table.</param> /// <param name="foreignTable">The foreign table.</param> /// <returns></returns> private string GetAuditTriggerScriptForMultiCRTable(ITableDefinition table, TableFieldDefinition fieldDefinition, TableRelationshipDefinition primaryTable, TableRelationshipDefinition foreignTable) { if (fieldDefinition.ReferenceDisplayFields.Any(x => x.SystemName == fieldDefinition.ReferenceDisplayField && (x.ColumnType == ColumnTypes.MultiReference || x.IsReverseRef))) return string.Empty; if (string.IsNullOrEmpty(fieldDefinition.ReferenceColumnTable) || string.IsNullOrEmpty(fieldDefinition.ReferencedColumnName)) return string.Empty; var result = string.Format(@" IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]')) drop trigger [dbo].[{0}_Audit]; GO CREATE TRIGGER [dbo].[{0}_Audit] ON [dbo].[{0}] AFTER INSERT,DELETE AS BEGIN SET NOCOUNT ON; IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED ) RETURN ", table.Name); if (fieldDefinition.ReferenceColumnTable.Equals(fieldDefinition.ReferencedProcessName)) { result += string.Format(@" IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED ) BEGIN INSERT INTO audit.[{0}] SELECT 'D' ,p.Id ,'{1}' ,'Link removed: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)) ,NULL ,p.LastModifiedOn ,p.LastModifiedBy FROM DELETED d INNER JOIN dbo.{0} p ON p.Id = d.[{4}] LEFT OUTER JOIN [{2}] si ON si.Id = d.[{5}] RETURN END IF NOT EXISTS ( SELECT * FROM DELETED ) BEGIN INSERT INTO audit.[{0}] SELECT 'I' ,p.[Id] ,'{1}' ,NULL ,'Link added: Process={2}, Id=' + CAST(si.Id AS VARCHAR(10)) + ', ' + CAST(si.[{3}] AS NVARCHAR(MAX)) ,p.LastModifiedOn ,p.LastModifiedBy FROM INSERTED i INNER JOIN dbo.{0} p ON p.Id = i.[{4}] LEFT OUTER JOIN [{2}] si ON si.Id = i.[{5}] RETURN END END GO ", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), fieldDefinition.ReferenceColumnTable, fieldDefinition.ReferenceDisplayField, primaryTable.JoinFieldName, foreignTable.JoinFieldName); } else { result += string.Format(@" IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED ) BEGIN INSERT INTO audit.[{0}] SELECT 'D' ,d.{0}Id ,'{1}' ,'Link removed: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)) ,NULL ,t.LastModifiedOn ,t.LastModifiedBy FROM DELETED d LEFT OUTER JOIN dbo.{2} p ON p.Id = d.[{5}] INNER JOIN [{4}] spi ON spi.Id = p.ParentId INNER JOIN dbo.[{0}] t on t.Id = d.{0}Id RETURN END IF NOT EXISTS ( SELECT * FROM DELETED ) BEGIN INSERT INTO audit.[{0}] SELECT 'I' ,i.{0}Id ,'{1}' ,NULL ,'Link added: Process={2}, Id=' + CAST(spi.Id AS VARCHAR(10)) + ', ' + CAST(spi.[{3}] AS NVARCHAR(MAX)) ,t.LastModifiedOn ,t.LastModifiedBy FROM INSERTED i LEFT OUTER JOIN dbo.{2} p ON p.Id = i.[{5}] INNER JOIN [{4}] spi ON spi.Id = p.ParentId INNER JOIN dbo.[{0}] t on t.Id = i.{0}Id RETURN END END GO ", primaryTable.FKTableName, fieldDefinition.DisplayName.DoubleSingleQuote(), foreignTable.FKTableName, fieldDefinition.ReferenceDisplayField, fieldDefinition.ReferenceColumnTable, foreignTable.JoinFieldName); } return result; }
private static bool IsFullTextSearchSupported(ITableDefinition table) { return !Constants.NonFullTextSearchableProcesses.Contains(table.Name); }
/// <summary> /// Gets the fk constraints SQL. /// </summary> /// <param name="dbTable">The database table.</param> /// <param name="fk">The fk.</param> /// <param name="table">The table.</param> /// <returns>System.String.</returns> protected override string GetFKConstraintsSql(DbTableDefinition dbTable, DbForeignKeyDefinition fk, ITableDefinition table) { var fKeys = string.Join(",", fk.ForeignKeyColumnsName); var pKeys = string.Join(",", fk.PrimaryKeyColumnsName); return string.Format( @" IF (EXISTS (SELECT * FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_SCHEMA] = 'dbo' AND [TABLE_NAME] = N'{0}')) BEGIN ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ({4}) ALTER TABLE [{0}] CHECK CONSTRAINT [{1}] END ", fk.ForeignKeyTableName, fk.ConstraintName, fKeys, fk.PrimaryKeyTableName, pKeys); }
/// <summary> /// Gets the add foreign key SQL. /// </summary> /// <param name="table">The table.</param> /// <param name="relationship">The relationship.</param> /// <param name="prefix">The prefix.</param> /// <returns>System.String.</returns> protected override string GetAddForeignKeySql(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null) { return string.Format( @" IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'{4}{1}') BEGIN ALTER TABLE [{4}{0}] WITH CHECK ADD CONSTRAINT [{4}{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([Id]) ALTER TABLE [{4}{0}] CHECK CONSTRAINT [{4}{1}] END {5} ", table.Name, relationship.Name, relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName, relationship.FKTableName, prefix, GenerateCreateIndexIfNotExists(CreateIndexDefinition(table, relationship, prefix))); }
private static IEnumerable<DataIndexDbDefinition> GetTableIndexes(ITableDefinition table, string prefix) { const char EscapeCharacter = '!'; using (var ctx = GetRuntimeDatabaseConnectionManager()) { var commandText = string.Format(CultureInfo.InvariantCulture, @" SELECT ind.name as IndexName ,t.name as ProcessName ,ind.filter_definition as FilterDefinition ,col.name as FieldName ,ic.is_included_column as IsIncluded FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id AND ind.name LIKE '{0}%' ESCAPE '{1}' WHERE ind.object_id = (SELECT OBJECT_ID(@tableName)) ORDER BY ind.name, ic.key_ordinal", AdoHelper.EscapeLikePattern(prefix, EscapeCharacter), EscapeCharacter); using (var command = new SqlCommand(commandText, ctx.Connection)) { command.Parameters.AddWithValue("@tableName", table.Name); using (var reader = new SafeDataReader(command.ExecuteReader())) { var indexList = new Collection<DataIndexDbDefinition>(); while (reader.Read()) { var indexName = reader.GetString(0); var processName = reader.GetString(1); var index = indexList.FirstOrDefault(ix => ix.IndexName == indexName && ix.ProcessName == processName); if (index == null) { index = new DataIndexDbDefinition { IndexName = indexName, ProcessName = processName, FilterDefinition = reader.GetString(2) }; indexList.Add(index); } var fieldName = reader.GetString(3); var included = reader.GetBoolean(4); index.IndexFields.Add(new DataIndexFieldDbDefinition(fieldName, !included)); } return indexList; } } } }
/// <summary> /// Gets the automatic number constraint. /// </summary> /// <param name="table">The table.</param> /// <returns>System.String.</returns> private string GetAutoNumberConstraint(ITableDefinition table) { string sql = string.Empty; foreach (var field in table.FieldList.Where(field => field.GenerateDbColumn && !string.IsNullOrWhiteSpace(field.ColumnName) && field.ColumnType == ColumnTypes.AutoNumber)) { sql += string.Format(@" IF EXISTS (SELECT * FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id WHERE i.name = N'IXU_{0}_{1}' AND t.name = N'{0}') DROP INDEX IXU_{0}_{1} ON {0}" , table.Name, field.ColumnName); } return sql; }
private static IEnumerable<DataIndexDbDefinition> GetForeignKeyIndexes(ITableDefinition table) { return table.Relationships.Select(relationship => CreateIndexDefinition(table, relationship)); }
public abstract void CreateTable(ITableDefinition tableDefinition);
protected void AssertTableDefinition(ITableDefinition expectedTableDefinition, ITableDefinition retrievedTableDefinition) { retrievedTableDefinition.Name.Should().BeEquivalentTo(expectedTableDefinition.Name); AssertColumns(expectedTableDefinition.Columns[0], retrievedTableDefinition.Columns[0]); }
/// <summary> /// Extension to register the dirty flag /// </summary> /// <typeparam name="T">The type of the table</typeparam> /// <typeparam name="TKey">Key type</typeparam> /// <returns>The table</returns> public static ITableDefinition WithDirtyFlag <T, TKey>(this ITableDefinition table, Predicate <T> isDirty) where T : class, new() { ((TableDefinition <T, TKey>)table).RegisterDirtyFlag(isDirty); return(table); }
public bool CanFill(ITableDefinition tableDefinition) { return(tableDefinition is CTableDefinitionStructureSNMP); }
/// <summary> /// Adds keys to index of table. /// </summary> /// <param name="tableDescription">Table description.</param> /// <param name="tableDefinition">Table definition.</param> /// <param name="indexes">Database indexes.</param> private void _AddKeysToTableIndex(TableDescription tableDescription, ITableDefinition tableDefinition, ADOX.Indexes indexes) { ICollection<TableInfo> patternTables = _structureKeeper.GetPattern(ExportType.Access); foreach (TableInfo tableInfo in patternTables) { if (tableInfo.Type != tableDefinition.Type) continue; // skip foreach(TableIndex indexDefinition in tableInfo.Indexes) { if (_IsIndexFieldSelected(indexDefinition.FieldNames, tableDefinition.Fields)) _AddKeyToTableIndex(tableDescription, indexDefinition, indexes); } break; // process done } }
/// <summary> /// Gets the audit trigger script. /// </summary> /// <param name="table">The table.</param> /// <returns>System.String.</returns> protected override string GetAuditTriggerScript(ITableDefinition table) { var sql = string.Format(@" IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_Audit]')) drop trigger [dbo].[{0}_Audit]; GO ", table.Name); if (Constants.NonAuditableProcesses.Contains(table.Name)) return sql; var result = new StringBuilder(sql); result.AppendFormat( @" CREATE TRIGGER [dbo].[{0}_Audit] ON [dbo].[{0}] AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; IF NOT EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED ) RETURN IF NOT EXISTS ( SELECT * FROM INSERTED ) AND EXISTS ( SELECT * FROM DELETED ) BEGIN -- deleted from outside of Elements INSERT INTO audit.[{0}] SELECT 'D' ,d.Id ,NULL ,'Last modified by: ' + d.LastModifiedBy ,'Last modified on: ' + CONVERT(VARCHAR(8), d.LastModifiedOn, 112) + ' ' + CONVERT(VARCHAR(12), d.LastModifiedOn, 114) ,GETDATE() ,SYSTEM_USER FROM DELETED d RETURN END IF NOT EXISTS ( SELECT * FROM DELETED ) BEGIN INSERT INTO audit.[{0}] SELECT 'I' ,i.Id ,NULL ,NULL ,NULL ,GETDATE() ,i.LastModifiedBy FROM INSERTED i ", table.Name); GenerateAuditTriggerFields(table, result, true); result.Append(@" RETURN END "); if (table.FieldList.Any(f => f.SystemName.Equals(Constants.IsRemovedColumnName))) { result.AppendFormat(@" IF UPDATE([{1}]) INSERT INTO [audit].[{0}] SELECT 'D',d.Id,'{1}',d.[{1}], i.[{1}], i.LastModifiedOn, i.LastModifiedBy FROM DELETED d INNER JOIN INSERTED i ON i.Id = d.Id WHERE (d.[{1}] IS NULL AND i.[{1}] IS NOT NULL) OR (i.[{1}] IS NULL AND d.[{1}] IS NOT NULL) OR (i.[{1}] <> d.[{1}]) ", table.Name, Constants.IsRemovedColumnName); } GenerateAuditTriggerFields(table, result, false); result.AppendLine("END"); result.AppendLine("GO"); return result.ToString(); }