private static Ret Delete(TableInfo tableInfo, TPk id, TFilter filter)
{
using (var scope = new SequelScope())
using (var tx = scope.CreateTransactionScope())
{
var idValue = default(TPk).Equals(id) ? null : (object)id;
var parameters = filter?._GetMap(FilterInfo.FieldNames);
var comparisons = string.Join(" and ",
FilterInfo.FieldNames
.Except(FilterInfo.RowNumberName)
.Select(x => $"{x} matches if set @{x}")
);
var rowNumber = (FilterInfo.RowNumberName != null)
? filter._Get(FilterInfo.RowNumberName)
: null;
var done =
@"delete from @{p_table} where @{p_pk} in (
select @{p_pk}
from (select @{p_pk}
, row_number() over (order by @{p_pk} asc) as row_number
from @{p_table}
where (@p_id is set and @{p_pk} matches @p_id)
or (@p_id is not set and @{p_comparisons})
) as T
where row_number matches if set @p_row_number
)
;
select @@rowcount
"
.AsSql()
.Set("p_table", tableInfo.TableName)
.Set("p_pk", tableInfo.PkName)
.Set("p_comparisons", comparisons)
.Set("p_row_number", rowNumber)
.Set("p_id", idValue)
.Set(parameters)
.ApplyTemplate()
.Echo()
.SelectOne <bool>();
tx.Complete();
return(done);
}
}
public static Ret <TTable[]> Find(TFilter filter = null)
{
try
{
using (var scope = new SequelScope())
{
var parameters = filter._GetMap(FilterInfo.FieldNames);
var comparisons = string.Join(" and ",
FilterInfo.FieldNames
.Except(FilterInfo.RowNumberName)
.Select(x => $"{x} matches if set @{x}")
);
var rowNumber = (FilterInfo.RowNumberName != null)
? filter._Get(FilterInfo.RowNumberName)
: null;
var entities =
@"select *
from (select *
, row_number() over (order by @{p_pk} asc) as row_number
from @{p_table} with (nolock)
where @{p_comparisons}
) as T
where row_number matches if set @p_row_number"
.AsSql()
.Set("p_table", TableInfo.TableName)
.Set("p_pk", TableInfo.PkName)
.Set("p_comparisons", comparisons)
.Set("p_row_number", rowNumber)
.Set(parameters)
.SelectGraphArray <TTable>();
return(entities);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Ret Update(TTable entity, params string[] fields)
{
try
{
using (var scope = new SequelScope())
using (var tx = scope.CreateTransactionScope())
{
if (fields.Length == 0)
{
fields = TableInfo.FieldNames.Except(TableInfo.PkName).ToArray();
}
var parameters = entity._GetMap(fields);
var assertions = string.Join(", ", parameters.Keys.Select(x => $"{x} = @{x}"));
var id = entity._Get(TableInfo.PkName);
var done =
@"update @{TableName}
set @{assertions}
where @{PkName} matches @id
;
select @@rowcount"
.AsSql()
.Set("TableName", TableInfo.TableName)
.Set("PkName", TableInfo.PkName)
.Set("assertions", assertions)
.Set("id", id)
.Set(parameters)
.SelectOne <bool>();
tx.Complete();
return(done);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Ret <TTable> Find(TPk id)
{
try
{
using (var scope = new SequelScope())
{
var entity =
@"select *
from @{p_table} with (nolock)
where @{p_pk} matches @p_id"
.AsSql()
.Set("p_table", TableInfo.TableName)
.Set("p_pk", TableInfo.PkName)
.Set("p_id", id)
.SelectOneGraph <TTable>();
return(entity);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Ret Insert(TTable entity)
{
try
{
using (var scope = new SequelScope())
using (var tx = scope.CreateTransactionScope())
{
var updatableFields = TableInfo.PkAutoIncrement
? TableInfo.FieldNames.Except(TableInfo.PkName).ToArray()
: TableInfo.FieldNames.ToArray();
var parameters = entity._GetMap(updatableFields);
var fields = string.Join(", ", parameters.Keys);
var values = parameters.Values;
var done =
@"insert into @{p_table} (@{p_fields}) values (@{p_values})
;
select @@rowcount"
.AsSql()
.Set("p_table", TableInfo.TableName)
.Set("p_pk", TableInfo.PkName)
.Set("p_fields", fields)
.Set("p_values", values)
.Set(parameters)
.SelectOne <bool>();
tx.Complete();
return(done);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static Ret InsertOrUpdate(TTable entity)
{
try
{
using (var scope = new SequelScope())
using (var tx = scope.CreateTransactionScope())
{
var updatableFields = TableInfo.PkAutoIncrement
? TableInfo.FieldNames.Except(TableInfo.PkName).ToArray()
: TableInfo.FieldNames.ToArray();
var parameters = entity._GetMap(updatableFields);
var assertions = string.Join(", ", parameters.Keys.Select(x => $"{x} = @{x}"));
var fields = string.Join(", ", parameters.Keys);
var values = parameters.Values;
var id = entity._Get(TableInfo.PkName);
var affectedId =
@"declare @x_affected_id int = null
;
update @{p_table}
set @{p_assertions}
where @{p_pk} matches @p_id
;
if @@rowcount > 0
begin
set @x_affected_id = @p_id
end
;
insert into @{p_table} (@{p_fields})
select @{p_values}
where not exists (select 1 from @{p_table} where @{p_pk} matches @p_id)
;
if @@rowcount > 0
begin
set @x_affected_id = scope_identity()
end
;
select @x_affected_id"
.AsSql()
.Set("p_table", TableInfo.TableName)
.Set("p_pk", TableInfo.PkName)
.Set("p_assertions", assertions)
.Set("p_fields", fields)
.Set("p_values", values)
.Set("p_id", id)
.Set(parameters)
.SelectOne();
tx.Complete();
var done = affectedId != null;
if (done)
{
entity._Set(TableInfo.PkName, affectedId);
}
return(done);
}
}
catch (Exception ex)
{
throw ex;
}
}
static Db()
{
using (var scope = new SequelScope(connectionString))
{
var version =
@"select DFvalor from TBsis_config where DFchave = 'versao'"
.AsSql()
.TrySelectOne <int>();
if (version == 1)
{
return;
}
using (var tx = scope.CreateTransactionScope())
{
//
// DDL
//
@"create table TBsis_config (
DFchave nvarchar(256) not null
constraint pk_TBsis_config primary key clustered,
DFvalor nvarchar(4000) null
)
;
insert into TBsis_config (DFchave, DFvalor) values ('versao', '1')
;
create table TBgrupo_empresa (
DFid_grupo_empresa int not null identity(1,1)
constraint pk_TBgrupo_empresa_DFid_grupo_empresa primary key clustered,
DFnome nvarchar(256) not null
)
;
create table TBempresa (
DFid_empresa int not null
constraint pk_TBempresa primary key clustered,
DFrazao_social nvarchar(256) not null,
DFnome_fantasia nvarchar(256) not null,
DFcnpj nvarchar(18) not null,
DFativo bit not null
constraint df_TBempresa_DFativo default (1),
DFid_grupo_empresa int null
constraint fk_TBempresa_TBgrupo_empresa
foreign key references TBgrupo_empresa(DFid_grupo_empresa)
on delete set null
)
;
create index ix_TBempresa_DFid_grupo_empresa on TBempresa (DFid_grupo_empresa)
;
create table TBpapel_usuario (
DFid_papel_usuario int not null identity(1,1)
constraint pk_TBpapel_usuario primary key clustered,
DFnome nvarchar(256) not null
)
;
create table TBusuario (
DFid_usuario int not null identity(1,1)
constraint pk_TBusuario primary key clustered,
DFlogin nvarchar(100) not null
constraint uk_TBusuario_DFlogin unique,
DFnome nvarchar(100) not null,
DFativo bit not null
constraint df_TBusuario_DFativo default (1),
DFsenha nvarchar(12) not null,
DFid_empresa_padrao int null
constraint fk_TBusuario_TBempresa
foreign key references TBempresa(DFid_empresa)
on delete set null,
DFid_papel_usuario int null
constraint fk_TBusuario_TBpapel_usuario
foreign key references TBpapel_usuario(DFid_papel_usuario)
on delete set null
)
;
create index ix_TBusuario_DFid_empresa_padrao on TBusuario (DFid_empresa_padrao)
;
create index ix_TBusuario_DFid_papel_usuario on TBusuario (DFid_papel_usuario)
;
create table TBpostagem (
DFid_postagem int not null identity(1,1)
constraint pk_TBpostagem primary key clustered,
DFid_autor int not null
constraint fk_TBpostagem_TBusuario
foreign key references TBusuario(DFid_usuario)
on delete cascade,
DFtitulo nvarchar(100) not null,
DFtexto nvarchar(max) not null,
DFdata_publicacao datetime not null
constraint df_TBpostagem_DFdata_publicacao default (getdate()),
DFid_postagem_referente int null
constraint fk_TBpostagem_TBpostagem
foreign key references TBpostagem(DFid_postagem),
DFtipo as (case when coalesce(DFid_postagem_referente, 0) = 0 then 'P' else 'C' end)
)
;
create index ix_TBpostagem_DFid_autor on TBpostagem(DFid_autor)
;
create index ix_TBpostagem_DFdata_publicacao on TBpostagem(DFdata_publicacao desc)
;
create index ix_TBpostagem_DFid_postagem_referente on TBpostagem(DFid_postagem_referente)
;
create index ix_TBpostagem_DFtipo on TBpostagem(DFtipo)
;
create table TBclassificacao (
DFid_classificacao int not null identity(1,1)
constraint pk_TBclassificacao primary key clustered,
DFid_autor int not null
constraint fk_TBclassificacao_TBusuario
foreign key references TBusuario (DFid_usuario),
DFid_postagem_referente int not null
constraint fk_TBclassificacao_TBpostagem
foreign key references TBpostagem (DFid_postagem)
on delete cascade,
DFclassificacao int not null
constraint ck_TBclassificacao_DFclassificacao
check (DFclassificacao = -1 or DFclassificacao = 1)
)"
.AsSql()
.Execute();
//
// DEMONSTRACAO
//
@"insert into TBgrupo_empresa (DFnome) values ('GRUPO SEM FRONTEIRAS')
declare @id_grupo_empresa int = scope_identity()
insert into TBempresa (DFid_empresa, DFrazao_social, DFnome_fantasia, DFcnpj, DFativo, DFid_grupo_empresa)
values (1, 'SEM FRONTEIRAS VAREJISTA', 'SUPERMERCADO SEM FRONTEIRAS', '01.234.567/0001-95', 1, @id_grupo_empresa)
, (2, 'SEM FRONTEIRAS ATACADISTA', 'CENTRAL SEM FRONTEIRAS', '01.234.567/0002-76', 1, @id_grupo_empresa)
insert into TBpapel_usuario (DFnome) values ('ADMIN')
declare @id_papel_admin int = scope_identity()
insert into TBpapel_usuario (DFnome) values ('USUARIO')
declare @id_papel_usuario int = scope_identity()
insert into TBusuario (DFlogin, DFnome, DFativo, DFsenha, DFid_empresa_padrao, DFid_papel_usuario)
values ('admin', 'ADMINISTRADOR', 1, 'qwer0987', 1, @id_papel_admin)
, ('fulano', 'FULANO', 1, '123', 1, @id_papel_usuario)
, ('beltrano', 'BELTRANO', 1, '123', 1, @id_papel_usuario)
, ('cicrano', 'CICRANO', 1, '123', 1, @id_papel_usuario)
, ('alano', 'ALANO', 2, '123', 1, @id_papel_usuario)
, ('mengano', 'MENGANO', 2, '123', 1, @id_papel_usuario)
, ('zutano', 'ZUTANO', 2, '123', 1, @id_papel_usuario)
, ('citano', 'CITANO', 2, '123', 1, @id_papel_usuario)
, ('perengano', 'PERENGANO', 2, '123', 1, @id_papel_usuario)"
.AsSql()
.Execute();
tx.Complete();
}
}
}
