1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
:VERSION 1
CREATE TABLE UserAccounts (
"PrincipalID" uuid NOT NULL Primary key,
"ScopeID" uuid NOT NULL,
"FirstName" varchar(64) NOT NULL,
"LastName" varchar(64) NOT NULL,
"Email" varchar(64) NULL,
"ServiceURLs" text NULL,
"Created" int default NULL
);
:VERSION 2
BEGIN TRANSACTION;
INSERT INTO UserAccounts ("PrincipalID", "ScopeID", "FirstName", "LastName", "Email", "ServiceURLs", "Created")
SELECT UUID AS "PrincipalID", '00000000-0000-0000-0000-000000000000' AS "ScopeID",
username AS "FirstName",
lastname AS "LastName",
email as "Email", (
'AssetServerURI=' +
userAssetURI + ' InventoryServerURI=' + userInventoryURI + ' GatewayURI= HomeURI=') AS "ServiceURLs",
created as "Created" FROM users;
COMMIT;
:VERSION 3
BEGIN TRANSACTION;
CREATE UNIQUE INDEX "PrincipalID" ON UserAccounts("PrincipalID");
CREATE INDEX "Email" ON UserAccounts("Email");
CREATE INDEX "FirstName" ON UserAccounts("FirstName");
CREATE INDEX "LastName" ON UserAccounts("LastName");
CREATE INDEX Name ON UserAccounts("FirstName","LastName");
COMMIT;
:VERSION 4
BEGIN TRANSACTION;
ALTER TABLE UserAccounts ADD "UserLevel" integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD "UserFlags" integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD "UserTitle" varchar(64) NOT NULL DEFAULT '';
COMMIT;
|
