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);
}
}
}
}