static IEnumerable <string> Tables(StoreItemCollection storeItems) { foreach (var entitySet in storeItems.GetItems <EntityContainer>()[0].BaseEntitySets.OfType <EntitySet>()) { var result = new StringBuilder(); var additionalColumnComments = new Dictionary <string, string>(); result.AppendFormat("RECREATE TABLE {0} (", SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(entitySet))); result.AppendLine(); foreach (var property in MetadataHelpers.GetProperties(entitySet.ElementType)) { var column = GenerateColumn(property); result.Append("\t"); result.Append(column.ColumnName); result.Append(","); result.AppendLine(); foreach (var item in column.ColumnComments) { additionalColumnComments.Add(item.Key, item.Value); } } result.AppendFormat("CONSTRAINT {0} PRIMARY KEY ({1})", SqlGenerator.QuoteIdentifier(string.Format("PK_{0}", MetadataHelpers.GetTableName(entitySet))), string.Join(", ", entitySet.ElementType.KeyMembers.Select(pk => SqlGenerator.QuoteIdentifier(pk.Name)))); result.AppendLine(); result.Append(");"); result.AppendLine(); foreach (var identity in entitySet.ElementType.KeyMembers.Where(pk => MetadataHelpers.IsStoreGeneratedIdentity(pk)).Select(i => i.Name)) { result.AppendLine(string.Concat("CREATE GENERATOR GEN_", MetadataHelpers.GetTableName(entitySet), "_", identity, ";")); result.AppendLine(string.Concat("SET GENERATOR GEN_", MetadataHelpers.GetTableName(entitySet), "_", identity, " TO 0;")); result.AppendLine("set term !!;"); result.AppendLine(string.Concat("CREATE TRIGGER ", MetadataHelpers.GetTableName(entitySet), "_BI FOR ", SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(entitySet)))); result.AppendLine("ACTIVE BEFORE INSERT POSITION 0"); result.AppendLine("AS"); result.AppendLine("BEGIN"); result.AppendLine(string.Concat("if (NEW.", SqlGenerator.QuoteIdentifier(identity), " is NULL OR NEW.", SqlGenerator.QuoteIdentifier(identity), "=0) then NEW.", SqlGenerator.QuoteIdentifier(identity), " = GEN_ID(GEN_", MetadataHelpers.GetTableName(entitySet), "_", identity, ", 1);")); result.AppendLine("END!!"); result.AppendLine("set term ; !!"); additionalColumnComments.Add(identity, "#PK_GEN#"); } foreach (var comment in additionalColumnComments) { result.AppendFormat("COMMENT ON COLUMN {0}.{1} IS '{2}';", SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(entitySet)), SqlGenerator.QuoteIdentifier(comment.Key), comment.Value); result.AppendLine(); } yield return(result.ToString()); } }
static IEnumerable <string> Tables(StoreItemCollection storeItems) { foreach (var entitySet in storeItems.GetItems <EntityContainer>()[0].BaseEntitySets.OfType <EntitySet>()) { var result = new StringBuilder(); var additionalColumnComments = new Dictionary <string, string>(); result.AppendFormat("RECREATE TABLE {0} (", SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(entitySet))); result.AppendLine(); foreach (var property in MetadataHelpers.GetProperties(entitySet.ElementType)) { var column = GenerateColumn(property); result.Append("\t"); result.Append(column.ColumnName); result.Append(","); result.AppendLine(); foreach (var item in column.ColumnComments) { additionalColumnComments.Add(item.Key, item.Value); } } result.AppendFormat("CONSTRAINT {0} PRIMARY KEY ({1})", SqlGenerator.QuoteIdentifier(string.Format("PK_{0}", MetadataHelpers.GetTableName(entitySet))), string.Join(", ", entitySet.ElementType.KeyMembers.Select(pk => SqlGenerator.QuoteIdentifier(pk.Name)))); result.AppendLine(); result.Append(");"); result.AppendLine(); foreach (var identity in entitySet.ElementType.KeyMembers.Where(pk => MetadataHelpers.IsStoreGeneratedIdentity(pk)).Select(i => i.Name)) { additionalColumnComments.Add(identity, "#PK_GEN#"); } foreach (var comment in additionalColumnComments) { result.AppendFormat("COMMENT ON COLUMN {0}.{1} IS '{2}';", SqlGenerator.QuoteIdentifier(MetadataHelpers.GetTableName(entitySet)), SqlGenerator.QuoteIdentifier(comment.Key), comment.Value); result.AppendLine(); } yield return(result.ToString()); } }
static IEnumerable <string> GenerateTables(StoreItemCollection storeItems) { foreach (var entitySet in storeItems.GetItems <EntityContainer>()[0].BaseEntitySets.OfType <EntitySet>()) { var result = new StringBuilder(); var tableName = MetadataHelpers.GetTableName(entitySet); var schemaName = MetadataHelpers.GetSchemaName(entitySet); result.AppendFormat("CREATE TABLE {0}.{1} (", SqlGenerator.QuoteIdentifier(schemaName), SqlGenerator.QuoteIdentifier(tableName)); result.AppendLine(); result.Append("\t"); result.Append(string.Join("," + Environment.NewLine + "\t", MetadataHelpers.GetProperties(entitySet.ElementType).Select(p => GenerateColumn(p)))); result.Append(");"); result.AppendLine(); result.AppendFormat("ALTER TABLE {0}.{1} ADD PRIMARY KEY ({2});", SqlGenerator.QuoteIdentifier(schemaName), SqlGenerator.QuoteIdentifier(tableName), string.Join(", ", entitySet.ElementType.KeyMembers.Select(pk => SqlGenerator.QuoteIdentifier(pk.Name)))); result.AppendLine(); yield return(result.ToString()); } }
/// <summary> /// Retrieves the properties of in the EdmType underlying the input type usage, /// if that EdmType is a structured type (EntityType, RowType). /// </summary> /// <param name="typeUsage"></param> /// <returns></returns> internal static IList <EdmProperty> GetProperties(TypeUsage typeUsage) { return(MetadataHelpers.GetProperties(typeUsage.EdmType)); }
/// <summary> /// <see cref="AddDefaultColumns"/> /// Add the column names from the referenced extent/join to the /// select statement. /// /// If the symbol is a JoinSymbol, we recursively visit all the extents, /// halting at real extents and JoinSymbols that have an associated SqlSelectStatement. /// /// The column names for a real extent can be derived from its type. /// The column names for a Join Select statement can be got from the /// list of columns that was created when the Join's select statement /// was created. /// /// We do the following for each column. /// <list type="number"> /// <item>Add the SQL string for each column to the SELECT clause</item> /// <item>Add the column to the list of columns - so that it can /// become part of the "type" of a JoinSymbol</item> /// <item>Check if the column name collides with a previous column added /// to the same select statement. Flag both the columns for renaming if true.</item> /// <item>Add the column to a name lookup dictionary for collision detection.</item> /// </list> /// </summary> /// <param name="selectStatement">The select statement that started off as SELECT *</param> /// <param name="symbol">The symbol containing the type information for /// the columns to be added.</param> /// <param name="columnList">Columns that have been added to the Select statement. /// This is created in <see cref="AddDefaultColumns"/>.</param> /// <param name="columnDictionary">A dictionary of the columns above.</param> private void AddColumns(SqlSelectStatement selectStatement, Symbol symbol, List <Symbol> columnList, Dictionary <string, Symbol> columnDictionary) { var joinSymbol = symbol as JoinSymbol; if (joinSymbol != null) { if (!joinSymbol.IsNestedJoin) { // Recurse if the join symbol is a collection of flattened extents foreach (Symbol sym in joinSymbol.ExtentList) { // if sym is ScalarType means we are at base case in the // recursion and there are not columns to add, just skip if ((sym.Type == null) || MetadataHelpers.IsPrimitiveType(sym.Type.EdmType)) { continue; } AddColumns(selectStatement, sym, columnList, columnDictionary); } } else { foreach (Symbol joinColumn in joinSymbol.ColumnList) { // we write tableName.columnName // rather than tableName.columnName as alias // since the column name is unique (by the way we generate new column names) // // We use the symbols for both the table and the column, // since they are subject to renaming. selectStatement.Select.Append(new SelectColumn(joinColumn, symbol, joinColumn)); // check for name collisions. If there is, // flag both the colliding symbols. if (columnDictionary.ContainsKey(joinColumn.Name)) { columnDictionary[joinColumn.Name].NeedsRenaming = true; // the original symbol joinColumn.NeedsRenaming = true; // the current symbol. } else { columnDictionary[joinColumn.Name] = joinColumn; } columnList.Add(joinColumn); } } } else { // This is a non-join extent/select statement, and the CQT type has // the relevant column information. // The type could be a record type(e.g. Project(...), // or an entity type ( e.g. EntityExpression(...) // so, we check whether it is a structuralType. // Consider an expression of the form J(a, b=P(E)) // The inner P(E) would have been translated to a SQL statement // We should not use the raw names from the type, but the equivalent // symbols (they are present in symbol.Columns) if they exist. // // We add the new columns to the symbol's columns if they do // not already exist. // // If the symbol represents a SqlStatement with renamed output columns, // we should use these instead of the rawnames and we should also mark // this selectStatement as one with renamed columns if (symbol.OutputColumnsRenamed) { selectStatement.OutputColumnsRenamed = true; selectStatement.OutputColumns = new Dictionary <string, Symbol>(); } if ((symbol.Type == null) || MetadataHelpers.IsPrimitiveType(symbol.Type.EdmType)) { AddColumn(selectStatement, symbol, columnList, columnDictionary, "X"); } else { foreach (EdmProperty property in MetadataHelpers.GetProperties(symbol.Type)) { AddColumn(selectStatement, symbol, columnList, columnDictionary, property.Name); } } } }