DBEX

ScriptPostDeployment.sql

-- add admin user
declare @adminUname varchar(32) = 'admin';
declare @adminPword varchar(32) = 'admin';
declare @adminSalt binary(64) = crypt_gen_random(64);
declare @adminStoredPword binary(64) = hashbytes('SHA2_512', convert(binary, @adminPword) + @adminSalt);
insert into [dbo].[User]([uname], [storedPword], [salt], [priv]) values
    (@adminUname, @adminStoredPword, @adminSalt, 0);

-- add demo flights
declare @Airport table([iata] char(3) primary key);
insert into @Airport values
    ('PEK'), ('TSN'), ('HET'), ('DSN'), ('HLD'), ('SJW'), ('TYN'), ('XIY'),
    ('XNN'), ('ZGC'), ('INC'), ('WUH'), ('CSX'), ('HAK'), ('SYX'), ('CGO'),
    ('LYA'), ('CAN'), ('SZX'), ('NNG'), ('KWL'), ('HKG'), ('MFM'), ('TPE'),
    ('PVG'), ('SHA'), ('HGH'), ('WNZ'), ('NGB'), ('FOC'), ('XMN'), ('HFE'),
    ('TNA'), ('TAO'), ('YNT'), ('NKG'), ('KHN'), ('HRB'), ('CGQ'), ('SHE'),
    ('DLC'), ('KWE'), ('KMG'), ('CTU'), ('CKG'), ('LXA'), ('URC');
declare @Airline table([iata] char(2) primary key);
insert into @Airline values
    ('CA'), ('CZ'), ('MU'), ('HU'), ('3U'), ('MF'), ('FM'), ('ZH'), ('JD'),
    ('CN'), ('GS'), ('PN'), ('8L'), ('9C'), ('SC'), ('OQ'), ('TV'), ('JR'),
    ('KY'), ('G5'), ('HO'), ('EU'), ('OK'), ('KN'), ('VD'), ('Y8'), ('CK'),
    ('O3'), ('UW'), ('GD'), ('J5'), ('JI'), ('CX'), ('KA'), ('LD'), ('HX'),
    ('UO'), ('NX'), ('CI'), ('BR'), ('AE'), ('GE'), ('B7'), ('EF');
declare @Class table([id] int primary key, [name] nvarchar(32) unique);
insert into @Class values (1, N'头等舱'), (2, N'商务舱'), (3, N'经济舱');
declare @nFlight int = 256;
declare @i int = 1;
while @i <= @nFlight begin
    declare @fnum varchar(6), @dept char(3), @dest char(3);
    select top 1 @fnum = [iata] + convert(varchar, convert(int, rand() * 9900.0 + 100.00))
        from @Airline
        order by newid();
    select top 1 @dept = [A].[iata], @dest = [B].[iata]
        from @Airport as [A], @Airport as [B]
        where [A].[iata] != [B].[iata]
        order by newid();
    declare @d datetime = dateadd(minute, convert(int, rand() * 10080.0), getutcdate())
    declare @estmDept datetime = datetimefromparts(
        datepart(year, @d), datepart(month, @d), datepart(day, @d),
        datepart(hour, @d), datepart(minute, @d), 0, 0
    );
    declare @durInMin int = convert(int, rand() * 500.0 + 40);
    insert into [dbo].[Flight]([fnum], [dept], [dest], [estmDept], [durInMin]) values
        (@fnum, @dept, @dest, @estmDept, @durInMin);
    declare @fid int = scope_identity();
    declare @price money = rand() * 5000.0 + 5000.0;
    declare @seat decimal(18, 15) = rand() * 0.085 + 3.338; -- 3.339 ~ 3.424
    declare @j int = 1;
    while @j <= (select count(*) from @Class) begin
        declare @cname nvarchar(32);
        select @cname = [name] from @Class where [id] = @j;
        set @price = (rand() * 0.3 + 0.5) * @price;
        set @seat = power(@seat * 2.0 / 3.0, @seat / 2.0);
        insert into [dbo].[Class]([fid], [cname], [price], [totalSeat]) values
            (@fid, @cname, @price, convert(int, @seat));
        set @j = @j + 1;
    end
    set @i = @i + 1;
end