コード例 #1
0
        /// <summary>
        /// The method is called when the user has selected to view a single view.
        /// The method generates a select on all fields, with no joins.
        /// </summary>
        /// <param name="selectedTable">The designer metadata designating the view.</param>
        /// <returns>The string containing the SQL command.</returns>
        private StringBuilder GenerateFromDataEntity(DataEntities.IDataEntityView selectedDataEntity, bool addGroupBy)
        {
            var  result = new StringBuilder();
            bool first  = true;

            Metadata.MetaModel.AxDataEntityView axDataEntity = this.MetadataProvider.DataEntityViews.Read(selectedDataEntity.Name);

            result.AppendLine(string.Format(CultureInfo.InvariantCulture, "USE {0}", BusinessDatabaseName));
            result.AppendLine("GO");

            dataEntityName = SqlNameMangling.GetSqlTableName(selectedDataEntity.Name);

            result.AppendLine("SELECT COUNT(*) AS COUNTER");
            result.AppendLine("FROM " + dataEntityName);

            return(result);
        }
コード例 #2
0
        /// <summary>
        /// The method is called when the user has selected to view a single view.
        /// The method generates a select on all fields, with no joins.
        /// </summary>
        /// <param name="selectedTable">The designer metadata designating the view.</param>
        /// <returns>The string containing the SQL command.</returns>
        private StringBuilder GenerateFromTable(Tables.ITable selectedTable, bool addGroupBy)
        {
            var result = new StringBuilder();

            // It is indeed a table. Look at the properties
            if (!selectedTable.IsKernelTable)
            {
                bool first = true;

                result.AppendLine(string.Format(CultureInfo.InvariantCulture, "USE {0}", BusinessDatabaseName));
                result.AppendLine("GO");

                Stack <Metadata.MetaModel.AxTable> tables = this.SuperTables(selectedTable.Name);

                tableName = SqlNameMangling.GetSqlTableName(tables.First().Name);

                // List any developer documentation as a SQL comment:
                //if (!string.IsNullOrEmpty(selectedTable.DeveloperDocumentation))
                //{
                //    result.Append("-- " + selectedTable.Name);
                //    result.AppendLine(" : " + this.ResolveLabel(selectedTable.DeveloperDocumentation));
                //}
                //else
                //{
                //    result.AppendLine();
                //}

                if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
                {
                    result.AppendLine($"SELECT {tableName}.DATAAREAID, COUNT(*) AS COUNTER");
                }
                else
                {
                    result.AppendLine("SELECT COUNT(*) AS COUNTER");
                }

                result.AppendLine("FROM " + tableName);

                if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes && addGroupBy)
                {
                    result.AppendLine($"GROUP BY {tableName}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
                }
            }

            return(result);
        }
コード例 #3
0
        /// <summary>
        /// The method is called when the user has selected to view a table extension instance.
        /// The method generates a select on all fields, with no joins.
        /// </summary>
        /// <param name="selectedExtensionTable">The designer metadata designating the view.</param>
        /// <returns>The string containing the SQL command.</returns>
        private StringBuilder GenerateFromTableExtension(Tables.ITableExtension selectedExtensionTable, bool addGroupBy)
        {
            var  result = new StringBuilder();
            bool first  = true;

            result.AppendLine(string.Format(CultureInfo.InvariantCulture, "USE {0}", BusinessDatabaseName));
            result.AppendLine("GO");
            result.AppendLine();

            Metadata.MetaModel.AxTableExtension extension = this.MetadataProvider.TableExtensions.Read(selectedExtensionTable.Name);
            var baseTableName = selectedExtensionTable.Name.Split('.').First();
            var tables        = this.SuperTables(baseTableName);

            tableName = baseTableName;

            HashSet <string> extendedFields = new HashSet <string>(StringComparer.OrdinalIgnoreCase);

            foreach (var extendedField in extension.Fields)
            {
                extendedFields.Add(extendedField.Name);
            }

            if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
            {
                result.AppendLine($"SELECT {SqlNameMangling.GetSqlTableName(tables.First().Name)}.DATAAREAID, COUNT(*) AS COUNTER");
            }
            else
            {
                result.AppendLine("SELECT COUNT(*) AS COUNTER");
            }

            result.AppendLine("FROM " + SqlNameMangling.GetSqlTableName(tables.First().Name));

            if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes && addGroupBy)
            {
                result.AppendLine($"GROUP BY {SqlNameMangling.GetSqlTableName(tables.First().Name)}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
            }

            return(result);
        }
コード例 #4
0
        /// <summary>
        /// Generate the SQL command selecting the fields from the selected table, performing
        /// joins on the tables indicated by the relations selected.
        /// </summary>
        /// <param name="selectedRelations">The list of field groups to select fields from.</param>
        /// <returns>The string containing the SQL command.</returns>
        private StringBuilder GenerateFromTableRelations(IEnumerable <Tables.IRelation> selectedRelations)
        {
            Tables.ITable table = selectedRelations.First().Table;
            Stack <Metadata.MetaModel.AxTable> tables = this.SuperTables(table.Name);

            var result         = this.GenerateFromTable(table, false);
            int disambiguation = 1;

            // Now add the joins. Assume that multiple relations can be
            // selected; joins will be added for all of them.
            foreach (var relation in selectedRelations)
            {
                var relatedTabularObjectName = relation.RelatedTable;
                // TODO: In principle this could be a view. Assuming table for now

                result.AppendLine(string.Format(CultureInfo.InvariantCulture, "INNER JOIN {0} as t{1}", SqlNameMangling.GetSqlTableName(relatedTabularObjectName), disambiguation));
                result.Append("ON ");

                bool first = true;
                foreach (Tables.IRelationConstraint constraint in relation.RelationConstraints)
                {
                    if (!first)
                    {
                        result.Append(" AND ");
                    }

                    if (constraint is Tables.IRelationConstraintField)
                    {   // Table.field = RelatedTable.relatedField
                        var fieldConstraint = constraint as Tables.IRelationConstraintField;
                        result.Append(SqlNameMangling.GetSqlTableName(table.Name) + ".[" + SqlNameMangling.GetValidSqlNameForField(fieldConstraint.Field) + "]");
                        result.Append(" = ");
                        result.AppendLine(string.Format(CultureInfo.InvariantCulture, "t{0}", disambiguation) + "." + SqlNameMangling.GetValidSqlNameForField(fieldConstraint.RelatedField));
                    }
                    else if (constraint is Tables.IRelationConstraintFixed)
                    {   // Table.field = value
                        var fixedConstraint = constraint as Tables.IRelationConstraintFixed;

                        result.Append(SqlNameMangling.GetSqlTableName(table.Name) + ".[" + SqlNameMangling.GetValidSqlNameForField(fixedConstraint.Field) + "]");
                        result.Append(" = ");
                        result.AppendLine(fixedConstraint.Value.ToString(CultureInfo.InvariantCulture));
                    }
                    else if (constraint is Tables.IRelationConstraintRelatedFixed)
                    {   // Value = RelatedTable.field
                        var relatedFixedConstraint = constraint as Tables.IRelationConstraintRelatedFixed;
                        result.Append(relatedFixedConstraint.Value);
                        result.Append(" = ");
                        result.AppendLine(string.Format(CultureInfo.InvariantCulture, "t{0}", disambiguation) + ".[" + SqlNameMangling.GetValidSqlNameForField(relatedFixedConstraint.RelatedField) + "]");
                    }

                    first = false;
                }

                disambiguation += 1;
            }

            if (tables.First().SaveDataPerCompany == Metadata.Core.MetaModel.NoYes.Yes)
            {
                result.AppendLine($"GROUP BY {SqlNameMangling.GetSqlTableName(tables.First().Name)}.{SqlNameMangling.GetValidSqlNameForField("DATAAREAID")}");
            }

            return(result);
        }