コード例 #1
0
 public IndexDefinition(ITableDefinition table, string name)
 {
     Table = table;
     Name = name;
     Columns = new List<IndexColumnDefinition>();
     Grbit = CreateIndexGrbit.None;
     Density = 100;
 }
コード例 #2
0
        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
        }
コード例 #3
0
 public PagingControlConfiguration(ITableDefinition tableDefinition)
 {
     TableDefinition = tableDefinition;
     ContainerCssClass = "table-pagination";
     DisabledCssClass = "disabled";
     ActiveCssClass = "active";
     FirstPageText = "<<";
     PreviousPageText = "<";
     NextPageText = ">";
     LastPageText = ">>";
 }
コード例 #4
0
        // 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);
            }
        }
コード例 #5
0
        /// <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());
        }
コード例 #6
0
ファイル: OracleSchema.cs プロジェクト: radtek/ACopy
        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());
        }
コード例 #7
0
        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);
        }
コード例 #8
0
ファイル: CTreeListeTables.cs プロジェクト: ykebaili/sc2idlls
        //--------------------------------------------------------------------------------
        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");
                }
            }
        }
コード例 #9
0
        //---------------------------------------
        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);
        }
コード例 #10
0
        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);
        }
コード例 #11
0
        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);
コード例 #12
0
ファイル: CTreeListeTables.cs プロジェクト: ykebaili/sc2idlls
 //--------------------------------------------------------------------------------
 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();
             }
         }
     }
 }
コード例 #13
0
ファイル: UserService.cs プロジェクト: czmirek/PersonalDataDB
        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);
            }
        }
コード例 #14
0
ファイル: UserService.cs プロジェクト: czmirek/PersonalDataDB
        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);
            }
        }
コード例 #15
0
        ///////////////////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////////////////

        /// <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);
            }
        }
コード例 #16
0
        //-------------------------------------------------------------------------------
        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);
        }
コード例 #17
0
ファイル: SqlServerFastCopy.cs プロジェクト: radtek/ACopy
        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);
        }
コード例 #18
0
        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");
        }
コード例 #19
0
        /// <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;
            }
        }
コード例 #20
0
ファイル: CEasyQuery.cs プロジェクト: ykebaili/sc2idlls
        //-------------------------------------------------------------
        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);
        }
コード例 #21
0
 //-----------------------------------------------------
 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);
 }
コード例 #22
0
        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);
            }
        }
コード例 #23
0
        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(",");
            }
        }
コード例 #24
0
        /// <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
            }
        }
コード例 #25
0
ファイル: CTreeListeTables.cs プロジェクト: ykebaili/sc2idlls
        //--------------------------------------------------------------------------------
        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);
                }
            }
        }
コード例 #26
0
        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();
            }
        }
コード例 #27
0
        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);
                }
            }
        }
コード例 #28
0
        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);
        }
コード例 #29
0
        /// <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();
        }
コード例 #30
0
 /// <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);
 }
コード例 #31
0
        /// <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.");
        }
コード例 #32
0
        /// <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() };
        }
コード例 #33
0
 /// <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);
 }
コード例 #34
0
ファイル: Extensions.cs プロジェクト: kouweizhong/SterlingDB
 /// <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);
 }
コード例 #35
0
        /// <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);
        }
コード例 #36
0
        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;
        }
コード例 #37
0
        /// <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);
        }
コード例 #38
0
        /// <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;
            }
        }
コード例 #39
0
        /// <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;
        }
コード例 #40
0
        /// <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();
        }
コード例 #41
0
        /// <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.");
        }
コード例 #42
0
        /// <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();
        }
コード例 #43
0
        /// <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.");
        }
コード例 #44
0
        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;
                    }
                }
            }
        }
コード例 #45
0
        /// <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();
        }
コード例 #46
0
        /// <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);
                    }
                }
            }
        }
コード例 #47
0
        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;
        }
コード例 #48
0
        /// <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;
        }
コード例 #49
0
 private static bool IsFullTextSearchSupported(ITableDefinition table)
 {
     return !Constants.NonFullTextSearchableProcesses.Contains(table.Name);
 }
コード例 #50
0
        /// <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);
        }
コード例 #51
0
        /// <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)));
        }
コード例 #52
0
        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;
                    }
                }
            }
        }
コード例 #53
0
        /// <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;
        }
コード例 #54
0
 private static IEnumerable<DataIndexDbDefinition> GetForeignKeyIndexes(ITableDefinition table)
 {
     return table.Relationships.Select(relationship => CreateIndexDefinition(table, relationship));
 }
コード例 #55
0
 public abstract void CreateTable(ITableDefinition tableDefinition);
コード例 #56
0
 protected void AssertTableDefinition(ITableDefinition expectedTableDefinition, ITableDefinition retrievedTableDefinition)
 {
     retrievedTableDefinition.Name.Should().BeEquivalentTo(expectedTableDefinition.Name);
     AssertColumns(expectedTableDefinition.Columns[0], retrievedTableDefinition.Columns[0]);
 }
コード例 #57
0
ファイル: Extensions.cs プロジェクト: kouweizhong/SterlingDB
 /// <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);
 }
コード例 #58
0
 public bool CanFill(ITableDefinition tableDefinition)
 {
     return(tableDefinition is CTableDefinitionStructureSNMP);
 }
コード例 #59
0
        /// <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
            }
        }
コード例 #60
0
        /// <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();
        }