private void Initialize() { Table.TableName = TableName; InitializeColumns(); Table.PrimaryKey = PrimaryKeyColumns.Select(i => Table.Columns[i]).ToArray(); IsInitialized = true; }
private string[] GetPKs() { if (string.IsNullOrWhiteSpace(PrimaryKeyColumns)) { return(new string[0]); } return(PrimaryKeyColumns.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)); }
private void CreateTempTablePrimaryKey(string tempTableName) { using (var cmd = CreateCommandForPrimaryKey()) { string pkList = string.Join(", ", PrimaryKeyColumns.Select(item => $"[{item}]")); cmd.CommandText = $"ALTER TABLE [{tempTableName}] " + $"ADD CONSTRAINT [PK_{tempTableName.Trim(PrefixTempTable)}] " + $"PRIMARY KEY NONCLUSTERED ({pkList})"; cmd.ExecuteNonQuery(); } }
public virtual void Initialize() { Schemas.ForEach(s => s.Initialize(Tables)); Tables.ForEach(t => t.Initialize(Schemas, Columns, PrimaryKeys, ForeignKeys, CheckConstraints)); Columns.ForEach(c => c.Initialize(Tables)); PrimaryKeys.ForEach(k => k.Initialize(Tables, PrimaryKeyColumns)); PrimaryKeyColumns.ForEach(c => c.Initialize(PrimaryKeys, Tables, Columns)); ForeignKeys.ForEach(k => k.Initialize(Tables, ForeignKeyColumns)); ForeignKeyColumns.ForEach(c => c.Initialize(ForeignKeys, Tables, Columns)); CheckConstraints.ForEach(c => c.Initialize(Tables)); }
public virtual long GetRowId(object obj) { var rowIdCol = PrimaryKeyColumns.FirstOrDefault(c => c.IsRowId); if (rowIdCol != null) { return((long)rowIdCol.GetValue(obj)); } string sql = "SELECT rowid FROM " + EscapedName + " WHERE " + BuildWherePrimaryKeyStatement(); var pk = GetPrimaryKey(obj); return(Database.ExecuteScalar <long>(sql, pk)); }
public virtual string GetCreateSql(string tableName) { string sql = "CREATE "; if (IsVirtual) { sql += "VIRTUAL "; } sql += "TABLE " + SQLiteStatement.EscapeName(tableName); if (!IsVirtual) { sql += " ("; sql += string.Join(",", Columns.Select(c => c.GetCreateSql(SQLiteCreateSqlOptions.ForCreateColumn))); if (PrimaryKeyColumns.Skip(1).Any()) { string pk = string.Join(",", PrimaryKeyColumns.Select(c => c.EscapedName)); if (!string.IsNullOrWhiteSpace(pk)) { sql += ",PRIMARY KEY (" + pk + ")"; } } sql += ")"; } if (DisableRowId) { // https://sqlite.org/withoutrowid.html sql += " WITHOUT ROWID"; } if (IsVirtual) { sql += " USING " + Module; if (ModuleArguments != null && ModuleArguments.Length > 0) { sql += "(" + string.Join(",", ModuleArguments) + ")"; } } return(sql); }
/// <summary> /// Creates a primary key for temporary table. /// </summary> /// <param name="tempTableName">Name of the temporary table.</param> /// <param name="columnName">Name of the column which must be created in temp table. If the value /// is <see langword="null"/>, no column is created, just the primary key.</param> private void CreateTempTablePrimaryKey(string tempTableName, string columnName) { using (var cmd = CreateCommandForPrimaryKey()) { if (columnName != null) { cmd.CommandText = "SELECT data_type FROM information_schema.columns " + $"WHERE table_name = '{DestinationTableName}' AND column_name = '{columnName}'"; string dataType = (string)cmd.ExecuteScalar(); cmd.CommandText = $"ALTER TABLE [{tempTableName}] ADD [{columnName}] [{dataType}] NOT NULL"; cmd.ExecuteNonQuery(); } string pkList = string.Join(", ", PrimaryKeyColumns.Select(item => $"[{item}]")); cmd.CommandText = $"ALTER TABLE [{tempTableName}] " + $"ADD CONSTRAINT [PK_{tempTableName.Trim(PrefixTempTable)}] " + $"PRIMARY KEY NONCLUSTERED ({pkList})"; cmd.ExecuteNonQuery(); } }
public virtual string BuildCreateSql(string tableName) { string sql = "CREATE TABLE " + SQLiteStatement.EscapeName(tableName) + " ("; sql += string.Join(",", Columns.Select(c => c.GetCreateSql(SQLiteCreateSqlOptions.ForCreateColumn))); if (PrimaryKeyColumns.Count() > 1) { string pk = string.Join(",", PrimaryKeyColumns.Select(c => c.EscapedName)); if (!string.IsNullOrWhiteSpace(pk)) { sql += ",PRIMARY KEY (" + pk + ")"; } } sql += ")"; if (DisableRowId) { // https://sqlite.org/withoutrowid.html sql += " WITHOUT ROWID"; } return(sql); }
public virtual void SetPrimaryKey(SQLiteLoadOptions options, object instance, object[] primaryKey) { if (instance == null) { throw new ArgumentNullException(nameof(instance)); } if (primaryKey == null) { throw new ArgumentNullException(nameof(primaryKey)); } var pkCols = PrimaryKeyColumns.ToList(); if (pkCols.Count != primaryKey.Length) { throw new ArgumentException(null, nameof(primaryKey)); } for (int i = 0; i < primaryKey.Length; i++) { pkCols[i].SetValue(options, instance, primaryKey[i]); } }
/// <inheritdoc/> /// <exception cref="ValidationException">Empty secondary key columns collection.</exception> protected override void ValidateState() { using (var ea = new ExceptionAggregator()) { ea.Execute(base.ValidateState); // Secondary key columns: empty set, duplicates var keyColumns = KeyColumns.Select(valueRef => valueRef.Value).ToList(); if (keyColumns.Count == 0) { ea.Execute(() => { throw new ValidationException(Strings.ExEmptyKeyColumnsCollection, Path); }); } foreach (var group in keyColumns .GroupBy(keyColumn => keyColumn) .Where(group => group.Count() > 1)) { ea.Execute((_column) => { throw new ValidationException( string.Format(Strings.ExMoreThenOneKeyColumnReferenceToColumnX, _column.Name), Path); }, group.Key); } // Primary key columns if (PrimaryKeyColumns.Count != Parent.PrimaryIndex.KeyColumns.Count) { ea.Execute(() => { throw new ValidationException(Strings.ExInvalidPrimaryKeyColumnsCollection, Path); }); } for (int i = 0; i < PrimaryKeyColumns.Count; i++) { var ref1 = PrimaryKeyColumns[i]; var ref2 = Parent.PrimaryIndex.KeyColumns[i]; if (ref1.Value != ref2.Value || ref1.Direction != ref2.Direction) { ea.Execute(() => { throw new ValidationException(Strings.ExInvalidPrimaryKeyColumnsCollection, Path); }); } } // Included columns var fullKeySet = EnumerableExtensions.ToHashSet(KeyColumns .Select(cr => cr.Value) .Concat(PrimaryKeyColumns.Select(cr => cr.Value))); foreach (var columnRef in IncludedColumns) { if (fullKeySet.Contains(columnRef.Value)) { ea.Execute(() => { throw new ValidationException(Strings.ExInvalidIncludedColumnsCollection, Path); }); } } foreach (var group in IncludedColumns .GroupBy(keyColumn => keyColumn) .Where(group => group.Count() > 1)) { ea.Execute((_column) => { throw new ValidationException( string.Format(Strings.ExMoreThenOneIncludedColumnReferenceToColumnX, _column.Name), Path); }, group.Key); } ea.Complete(); } }
/// <summary> /// <inheritdoc/> /// </summary> protected override void Prepare() { CheckKeyHasValue(Name); CheckOptionsProperties(); Schema = DefaultWhereNull(Schema, () => "dbo"); DbTable = Root !.DbTables !.Where(x => x.Name == Name && x.Schema == Schema).SingleOrDefault(); if (DbTable == null) { throw new CodeGenException(this, nameof(Name), $"Specified Schema.Table '{Schema}.{Name}' not found in database."); } Alias = DefaultWhereNull(Alias, () => new string(StringConversion.ToSentenceCase(Name) !.Split(' ').Select(x => x.Substring(0, 1).ToLower(System.Globalization.CultureInfo.InvariantCulture).ToCharArray()[0]).ToArray())); ViewName = DefaultWhereNull(ViewName, () => "vw" + Name); ViewSchema = DefaultWhereNull(ViewSchema, () => Schema); if (!string.IsNullOrEmpty(Permission) && Permission.Split(".", StringSplitOptions.RemoveEmptyEntries).Length == 1) { Permission += ".Read"; } ColumnNameIsDeleted = DefaultWhereNull(ColumnNameIsDeleted, () => Root !.ColumnNameIsDeleted); ColumnNameTenantId = DefaultWhereNull(ColumnNameTenantId, () => Root !.ColumnNameTenantId); ColumnNameOrgUnitId = DefaultWhereNull(ColumnNameOrgUnitId, () => Root !.ColumnNameOrgUnitId); ColumnNameRowVersion = DefaultWhereNull(ColumnNameRowVersion, () => Root !.ColumnNameRowVersion); ColumnNameCreatedBy = DefaultWhereNull(ColumnNameCreatedBy, () => Root !.ColumnNameCreatedBy); ColumnNameCreatedDate = DefaultWhereNull(ColumnNameCreatedDate, () => Root !.ColumnNameCreatedDate); ColumnNameUpdatedBy = DefaultWhereNull(ColumnNameUpdatedBy, () => Root !.ColumnNameUpdatedBy); ColumnNameUpdatedDate = DefaultWhereNull(ColumnNameUpdatedDate, () => Root !.ColumnNameUpdatedDate); ColumnNameDeletedBy = DefaultWhereNull(ColumnNameDeletedBy, () => Root !.ColumnNameDeletedBy); ColumnNameDeletedDate = DefaultWhereNull(ColumnNameDeletedDate, () => Root !.ColumnNameDeletedDate); PrepareJoins(); if (Order != null && Order.Count > 0) { foreach (var order in Order) { order.Prepare(Root !, this); } } foreach (var c in DbTable.Columns) { if (c.IsPrimaryKey) { var cc = new QueryColumnConfig { Name = c.Name, DbColumn = c }; cc.Prepare(Root !, this); PrimaryKeyColumns.Add(cc); } if ((ExcludeColumns == null || !ExcludeColumns.Contains(c.Name !)) && (IncludeColumns == null || IncludeColumns.Contains(c.Name !))) { var cc = new QueryColumnConfig { Name = c.Name, DbColumn = c }; var ca = AliasColumns?.Where(x => x.StartsWith(c.Name + "^", StringComparison.Ordinal)).FirstOrDefault(); if (ca != null) { var parts = ca.Split("^", StringSplitOptions.RemoveEmptyEntries); if (parts.Length == 2) { cc.NameAlias = parts[1]; } } cc.Prepare(Root !, this); Columns.Add(cc); } } if (Where == null) { Where = new List <QueryWhereConfig>(); } if (ColumnTenantId != null) { Where.Add(new QueryWhereConfig { Statement = $"[{Alias}].[{ColumnTenantId.Name}] = dbo.fnGetTenantId(NULL)" }); } if (ColumnIsDeleted != null) { Where.Add(new QueryWhereConfig { Statement = $"([{Alias}].[{ColumnIsDeleted.Name}] IS NULL OR [{Alias}].[{ColumnIsDeleted.Name}] = 0)" }); } if (!string.IsNullOrEmpty(Permission)) { Where.Add(new QueryWhereConfig { Statement = $"{Root!.GetUserPermissionSql}(NULL, NULL, '{Permission.ToUpperInvariant()}', NULL) = 1" });
public virtual string BuildWherePrimaryKeyStatement() => string.Join(" AND ", PrimaryKeyColumns.Select(c => SQLiteStatement.EscapeName(c.Name) + "=?"));
/// <summary> /// Gets only the PocoColumns from the collection of all columns that are PK columns. /// </summary> public IList <PocoColumn> GetPrimaryKeyColumns() { // Since the underlying collection (Columns) maintains order, this should be consistent in ordering return(Columns.Where(c => PrimaryKeyColumns.Contains(c.ColumnName)).ToList()); }
public void WriteCreateStatement(DdlRules rules, TextWriter writer) { if (rules.TableCreation == CreationStyle.DropThenCreate) { writer.WriteLine("DROP TABLE IF EXISTS {0} CASCADE;", Identifier); writer.WriteLine("CREATE TABLE {0} (", Identifier); } else { writer.WriteLine("CREATE TABLE IF NOT EXISTS {0} (", Identifier); } if (rules.Formatting == DdlFormatting.Pretty) { var columnLength = Columns.Max(x => x.Name.Length) + 4; var typeLength = Columns.Max(x => x.Type.Length) + 4; var lines = Columns.Select(column => $" {column.Name.PadRight(columnLength)}{column.Type.PadRight(typeLength)}{column.Declaration()}") .ToList(); if (PrimaryKeyColumns.Any()) { lines.Add(PrimaryKeyDeclaration()); } for (int i = 0; i < lines.Count - 1; i++) { writer.WriteLine(lines[i] + ","); } writer.WriteLine(lines.Last()); } else { var lines = Columns .Select(column => column.ToDeclaration()) .ToList(); if (PrimaryKeyColumns.Any()) { lines.Add(PrimaryKeyDeclaration()); } for (int i = 0; i < lines.Count - 1; i++) { writer.WriteLine(lines[i] + ","); } writer.WriteLine(lines.Last()); } switch (PartitionStrategy) { case PartitionStrategy.None: writer.WriteLine(");"); break; case PartitionStrategy.Range: writer.WriteLine($") PARTITION BY RANGE ({PartitionExpressions.Join(", ")});"); break; } // TODO -- support OriginWriter //writer.WriteLine(OriginWriter.OriginStatement("TABLE", Identifier.QualifiedName)); foreach (var foreignKey in ForeignKeys) { writer.WriteLine(); writer.WriteLine(foreignKey.ToDDL(this)); } foreach (var index in Indexes) { writer.WriteLine(); writer.WriteLine(index.ToDDL(this)); } }
internal string PrimaryKeyDeclaration() { return($"CONSTRAINT {PrimaryKeyName} PRIMARY KEY ({PrimaryKeyColumns.Join(", ")})"); }