private string FillBusinessDays(IDBRevision sender, eDBType dBType)
{
return
(@"WITH DaysCTE AS
(
SELECT TOP 0 ID = NEWID(), ShortName = '', FullName = ''
UNION ALL SELECT ID = NEWID(), ShortName = 'Mon', FullName = 'Monday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Tue', FullName = 'Tuesday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Wed', FullName = 'Wednesday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Thu', FullName = 'Thursday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Fri', FullName = 'Friday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Sat', FullName = 'Saturday'
UNION ALL SELECT ID = NEWID(), ShortName = 'Sun', FullName = 'Sunday'
)
INSERT INTO Flyinline.BusinessDay (ID, shortName, fullname)
SELECT
t.ID, t.ShortName, t.FullName
FROM
DaysCTE t
LEFT JOIN Flyinline.BusinessDay d ON t.ShortName = d.ShortName AND t.FullName = d.FullName
WHERE
d.ID is null
");
}
private string FillRolePermissions(IDBRevision sender, eDBType dBType)
{
return
(@"
;WITH
RolesCTE AS
(
SELECT RoleID = ID FROM Common.Role t WHERE t.Name IN ('Client', 'BusinessOwner')
),
ClaimsCTE AS
(
SELECT ClaimID = ID FROM Common.Claim t WHERE t.Name IN ('Routes.HomePage.View')
)
INSERT INTO Common.RolePermission (ID, RoleID, ClaimID, CanExecute)
SELECT a.*
FROM
(
SELECT ID = NEWID(), RoleID, ClaimID, CanExecute = 1
FROM
RolesCTE r,
ClaimsCTE c
) a
LEFT JOIN Common.RolePermission existing on a.RoleID = existing.RoleID and a.ClaimID = existing.ClaimID
WHERE existing.ID IS NULL
");
}
private string FillDrzava(IDBRevision sender, eDBType dBType)
{
var ret = string.Format(
@"GO
WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),
--I removed Pass5, since I'm only populating the Numbers table to 10,000
Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT INTO {0} (ID, Naziv)
SELECT
ID = NEWID(),
Naziv = CAST(Number as nvarchar(512))
FROM Tally
WHERE
Number <= 10000
WAITFOR DELAY '00:00:03'
", sender.Parent.SchemaName + "." + sender.Parent.SchemaObjectName);
return(ret);
}
private string FillAdmin2(IDBRevision sender, eDBType dBType)
{
return
(@"--WAITFOR DELAY '00:00:01'
--exec sp_updatestats
INSERT INTO Common.Role (ID, Name)
SELECT TOP 0 NEWID(), 'Administrator'");
}
private string FillClaims(IDBRevision sender, eDBType dBType)
{
return
(@"
INSERT INTO Common.Claim (ID, Name)
SELECT
a.ID, a.Name
FROM
(
SELECT ID = NEWID(), Name = 'UserManager'
UNION ALL SELECT ID = NEWID(), Name = 'RoleManager'
) a
LEFT JOIN Common.Claim c on a.Name = c.Name
WHERE
c.Name IS NULL
");
}
private string FillClaims(IDBRevision sender, eDBType dBType)
{
return
(@"WITH ClaimsCTE AS
(
SELECT TOP 0 ID = NEWID(), Name = ''
UNION ALL SELECT ID = NEWID(), Name = 'Routes.HomePage.View'
)
INSERT INTO Common.Claim (ID, Name)
SELECT
claims.ID, claims.Name
FROM
ClaimsCTE claims
LEFT JOIN Common.Claim c ON claims.Name = c.Name
WHERE
c.ID is null
");
}
private string FillRoles(IDBRevision sender, eDBType dBType)
{
return
(@"WITH RolesCTE AS
(
SELECT ID = NEWID(), Name = 'Client'
UNION ALL SELECT NEWID(), 'Admin'
UNION ALL SELECT NEWID(), 'BusinessOwner'
)
INSERT INTO Common.Role (ID, Name)
SELECT
t.ID, t.Name
FROM
RolesCTE t
LEFT JOIN Common.Role r ON t.Name = r.Name
WHERE
r.ID is null
");
}
private string FillAdminRolePermissions(IDBRevision sender, eDBType dBType)
{
return
(@"
INSERT INTO Common.Role
SELECT
ID = NEWID(), Name = 'Administrator'
WHERE
NOT EXISTS (SELECT ID FROM Common.Role r WHERE r.Name = 'Administrator')
INSERT INTO Common.RolePermission (ID, ClaimID, RoleID, CanExecute)
SELECT
n.*
FROM
(SELECT ID = NEWID(), ClaimID = c.ID, RoleID = (SELECT ID FROM Common.Role r WHERE r.Name = 'Administrator'), CanExecute = 1 FROM Common.Claim c) n
LEFT JOIN Common.RolePermission rp ON n.ClaimID = rp.ClaimID AND rp.RoleID = n.roleID
WHERE rp.ID is null
");
}
private string UpdateStatistics(IDBRevision sender, eDBType dBType)
{
return
(@"WAITFOR DELAY '00:00:01'
--exec sp_updatestats");
}
