184 lines
8.9 KiB
Transact-SQL
184 lines
8.9 KiB
Transact-SQL
use AnP
|
|
|
|
if object_id(N'dbo.common_tables_fill', N'P') is not null drop procedure dbo.common_tables_fill
|
|
go
|
|
create procedure dbo.common_tables_fill as begin set nocount on
|
|
|
|
declare @database varchar(64) = db_name()
|
|
declare @machine integer = dbo.machine_session_get()
|
|
declare @procedure integer
|
|
declare @machine_user varchar(32) = 'machine'
|
|
declare @machine_ip varchar(39) = '::1'
|
|
declare @machine_user_id integer = (select top 1 id from dbo.Users where nick = @machine_user)
|
|
declare @machine_ip_id integer = (select top 1 id from dbo.Ips where [address] = @machine_ip)
|
|
declare @guest_user_id integer = (select top 1 id from dbo.Users where nick = 'guest')
|
|
declare @guest_group_id integer = (select top 1 id from dbo.Groups where [name] = 'guest' and date_out is null)
|
|
declare @root_group_id integer = (select top 1 id from dbo.Groups where [name] = 'root' and date_out is null)
|
|
declare @user_permissions_id integer = (select top 1 id from dbo.Permissions where [name] = 'user' and date_out is null)
|
|
declare @guest_permissions_id integer = (select top 1 id from dbo.Permissions where [name] = 'guest' and date_out is null)
|
|
declare @administrator_permissions_id integer = (select top 1 id from dbo.Permissions where [name] = 'administrator' and date_out is null)
|
|
declare @user_group_id integer = (select top 1 id from dbo.Groups where [name] = 'users' and date_out is null)
|
|
|
|
execute dbo.procedure_get_id @database, 'common_tables_fill', @procedure output
|
|
|
|
if @machine is null begin
|
|
|
|
declare @group integer
|
|
declare @user_to_do bit = 0
|
|
declare @ip_to_do bit = 0
|
|
|
|
set @machine = 1
|
|
|
|
if @machine_user_id is null begin
|
|
insert into dbo.Users([procedure], [session], nick, [password], accessible) values
|
|
(@procedure, @machine, @machine_user, hashbytes('SHA2_512', ''), 0)
|
|
set @machine_user_id = scope_identity()
|
|
set @user_to_do = 1
|
|
end
|
|
|
|
if @machine_ip_id is null begin
|
|
insert into dbo.Ips([procedure], [session], [address]) values
|
|
(@procedure, @machine, @machine_ip)
|
|
set @machine_ip_id = scope_identity()
|
|
set @ip_to_do = 1
|
|
end
|
|
|
|
set @machine = (select top 1 id from dbo.SessionsActived where [user] = @machine_user_id and [ip] = @machine_ip_id)
|
|
|
|
if @machine is null begin
|
|
insert into dbo.SessionsActived([procedure], [user], [ip]) values
|
|
(@procedure, @machine_user_id, @machine_ip_id)
|
|
set @machine = scope_identity()
|
|
end
|
|
|
|
if @user_to_do = 1 begin
|
|
insert into dbo.Groups([procedure], [session], [name], owner) values
|
|
(@procedure, @machine, @machine_user, 1)
|
|
set @group = scope_identity()
|
|
insert into dbo.GroupsUsers([procedure], [session], [group], [user]) values
|
|
(@procedure, @machine, @group, @machine_user_id)
|
|
end
|
|
|
|
if @ip_to_do = 1 begin
|
|
insert into dbo.Groups([procedure], [session], [name], owner) values
|
|
(@procedure, @machine, @machine_ip, 1)
|
|
set @group = scope_identity()
|
|
insert into dbo.GroupsIps([procedure], [session], [group], [ip]) values
|
|
(@procedure, @machine, @group, @machine_ip_id)
|
|
end
|
|
|
|
if (select top 1 0 from dbo.Users where id = @machine_user_id and [session] = @machine) is null begin
|
|
update dbo.Users set [session] = @machine where id = @machine_user_id
|
|
delete from dbo._Users where [session] != @machine
|
|
end
|
|
if (select top 1 0 from dbo.Ips where id = @machine_ip_id and [session] = @machine) is null begin
|
|
update dbo.Ips set [session] = @machine where id = @machine_ip_id
|
|
delete from dbo._Ips where [session] != @machine
|
|
end
|
|
|
|
if (select top 1 0 from information_schema.table_constraints where
|
|
table_catalog = db_name() and
|
|
table_name = 'Users' and
|
|
constraint_name = 'fk_users_session'
|
|
) is null alter table dbo.Users add constraint fk_users_session foreign key([session]) references dbo.Sessions(id)
|
|
on update no action
|
|
on delete no action
|
|
|
|
if (select top 1 0 from information_schema.table_constraints where
|
|
table_catalog = db_name() and
|
|
table_name = '_Users' and
|
|
constraint_name = 'fk__users_session'
|
|
) is null alter table dbo._Users add constraint fk__users_session foreign key([session]) references dbo.Sessions(id)
|
|
on update no action
|
|
on delete no action
|
|
|
|
if (select top 1 0 from information_schema.table_constraints where
|
|
table_catalog = db_name() and
|
|
table_name = 'Ips' and
|
|
constraint_name = 'fk_ips_session'
|
|
) is null alter table dbo.Ips add constraint fk_ips_session foreign key([session]) references dbo.Sessions(id)
|
|
on update no action
|
|
on delete no action
|
|
|
|
if (select top 1 0 from information_schema.table_constraints where
|
|
table_catalog = db_name() and
|
|
table_name = '_Ips' and
|
|
constraint_name = 'fk__ips_session'
|
|
) is null alter table dbo._Ips add constraint fk__ips_session foreign key([session]) references dbo.Sessions(id)
|
|
on update no action
|
|
on delete no action
|
|
|
|
-- execute dbo.users_tables_upgrade
|
|
-- execute dbo.ips_tables_upgrade
|
|
|
|
end
|
|
|
|
if object_id(N'tempdb..#Settings') is not null drop table #Settings
|
|
create table #Settings(
|
|
[type] varchar(32) not null,
|
|
[name] varchar(64) not null,
|
|
[value] varchar(max) not null
|
|
)
|
|
|
|
insert into #Settings([type], [name], [value]) values
|
|
('data', 'data_hash_algorithm', 'SHA2_512')
|
|
|
|
if @guest_user_id is null begin
|
|
insert into dbo.Users([procedure], [session], nick, [password], accessible) values
|
|
(@procedure, @machine, 'guest', hashbytes('SHA2_512', ''), 0)
|
|
set @guest_user_id = scope_identity()
|
|
end
|
|
if @guest_group_id is null begin
|
|
insert into dbo.Groups([procedure], [session], [name], owner) values
|
|
(@procedure, @machine, 'guest', 1)
|
|
set @guest_group_id = scope_identity()
|
|
end
|
|
if @guest_permissions_id is null begin
|
|
insert into dbo.Permissions([procedure], [session], [name], [description]) values
|
|
(@procedure, @machine, 'guest', 'Guest permissions')
|
|
set @guest_permissions_id = scope_identity()
|
|
end
|
|
if (select top 1 0 from dbo.GroupsUsers where [group] = @guest_group_id and [user] = @guest_user_id) is null
|
|
insert into dbo.GroupsUsers([procedure], [session], [group], [user]) values
|
|
(@procedure, @machine, @guest_group_id, @guest_user_id)
|
|
if (select top 1 0 from dbo.PermissionsGroups where permission = @guest_permissions_id and [group] = @guest_group_id) is null
|
|
insert into dbo.PermissionsGroups([procedure], [session], permission, [group]) values
|
|
(@procedure, @machine, @guest_permissions_id, @guest_group_id)
|
|
|
|
if @root_group_id is null begin
|
|
insert into dbo.Groups([procedure], [session], [name], owner) values
|
|
(@procedure, @machine, 'root', 1)
|
|
set @root_group_id = scope_identity()
|
|
end
|
|
if @administrator_permissions_id is null begin
|
|
insert into dbo.Permissions([procedure], [session], [name], [description]) values
|
|
(@procedure, @machine, 'administrator', 'Administrator permissions')
|
|
set @administrator_permissions_id = scope_identity()
|
|
end
|
|
if (select top 1 0 from dbo.PermissionsGroups where permission = @administrator_permissions_id and [group] = @root_group_id) is null
|
|
insert into dbo.PermissionsGroups([procedure], [session], permission, [group]) values
|
|
(@procedure, @machine, @administrator_permissions_id, @root_group_id)
|
|
if (select top 1 0 from dbo.GroupsUsers where [group] = @root_group_id and [user] = @machine_user_id) is null
|
|
insert into dbo.GroupsUsers([procedure], [session], [group], [user]) values
|
|
(@procedure, @machine, @root_group_id, @machine_user_id)
|
|
|
|
if @user_group_id is null begin
|
|
insert into dbo.Groups([procedure], [session], [name], owner) values
|
|
(@procedure, @machine, 'users', 1)
|
|
set @user_group_id = scope_identity()
|
|
end
|
|
if @user_permissions_id is null begin
|
|
insert into dbo.Permissions([procedure], [session], [name], [description]) values
|
|
(@procedure, @machine, 'user', 'User permissions')
|
|
set @user_permissions_id = scope_identity()
|
|
end
|
|
if (select top 1 0 from dbo.PermissionsGroups where permission = @user_permissions_id and [group] = @user_group_id) is null
|
|
insert into dbo.PermissionsGroups([procedure], [session], permission, [group]) values
|
|
(@procedure, @machine, @user_permissions_id, @user_group_id)
|
|
|
|
drop table #Settings
|
|
|
|
end
|
|
go
|
|
|
|
execute dbo.common_tables_fill |