anjQsDRZ

1Citizen.CreateThread(function()
2    Wait(1000) -- Wait for GHMatti to Initialize
3    MySQL.SingleQuery([[
4    CREATE TABLE IF NOT EXISTS vrp_users(
5    id INTEGER AUTO_INCREMENT,
6    last_login VARCHAR(100),
7    whitelisted BOOLEAN,
8    banned BOOLEAN,
9    bantime VARCHAR(100) NOT NULL DEFAULT "",
10    banreason VARCHAR(1000) NOT NULL DEFAULT "",
11    banadmin VARCHAR(100) NOT NULL DEFAULT "",
12    CONSTRAINT pk_user PRIMARY KEY(id)
13    );
14    ]])
15    MySQL.SingleQuery([[
16    CREATE TABLE IF NOT EXISTS vrp_user_ids (
17    identifier VARCHAR(100) NOT NULL,
18    user_id INTEGER,
19    banned BOOLEAN,
20    CONSTRAINT pk_user_ids PRIMARY KEY(identifier)
21    );
22    ]])
23    MySQL.SingleQuery([[
24    CREATE TABLE IF NOT EXISTS vrp_user_tokens (
25    token VARCHAR(200),
26    user_id INTEGER,
27    banned BOOLEAN  NOT NULL DEFAULT 0,
28    CONSTRAINT pk_user_tokens PRIMARY KEY(token)
29    );
30    ]])
31    MySQL.SingleQuery([[
32    CREATE TABLE IF NOT EXISTS vrp_user_data(
33    user_id INTEGER,
34    dkey VARCHAR(100),
35    dvalue TEXT,
36    CONSTRAINT pk_user_data PRIMARY KEY(user_id,dkey),
37    CONSTRAINT fk_user_data_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
38    );
39    ]])
40    MySQL.SingleQuery([[
41    CREATE TABLE IF NOT EXISTS vrp_srv_data(
42    dkey VARCHAR(100),
43    dvalue TEXT,
44    CONSTRAINT pk_srv_data PRIMARY KEY(dkey)
45    );
46    ]])
47    MySQL.SingleQuery([[
48    CREATE TABLE IF NOT EXISTS vrp_user_moneys(
49    user_id INTEGER,
50    wallet INTEGER,
51    bank INTEGER,
52    CONSTRAINT pk_user_moneys PRIMARY KEY(user_id),
53    CONSTRAINT fk_user_moneys_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
54    );
55    ]])
56    MySQL.SingleQuery([[
57    CREATE TABLE IF NOT EXISTS vrp_user_business(
58    user_id INTEGER,
59    name VARCHAR(30),
60    description TEXT,
61    capital INTEGER,
62    laundered INTEGER,
63    reset_timestamp INTEGER,
64    CONSTRAINT pk_user_business PRIMARY KEY(user_id),
65    CONSTRAINT fk_user_business_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
66    );
67    ]])
68    MySQL.SingleQuery([[
69    CREATE TABLE IF NOT EXISTS vrp_user_vehicles(
70    user_id INTEGER,
71    vehicle VARCHAR(100),
72    vehicle_plate varchar(255) NOT NULL,
73    rented BOOLEAN NOT NULL DEFAULT 0,
74    rentedid varchar(200) NOT NULL DEFAULT '',
75    rentedtime varchar(2048) NOT NULL DEFAULT '',
76    CONSTRAINT pk_user_vehicles PRIMARY KEY(user_id,vehicle),
77    CONSTRAINT fk_user_vehicles_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE
78    );
79    ]])
80    MySQL.SingleQuery([[
81    CREATE TABLE IF NOT EXISTS vrp_user_homes(
82    user_id INTEGER,
83    home VARCHAR(100),
84    number INTEGER,
85    CONSTRAINT pk_user_homes PRIMARY KEY(user_id),
86    CONSTRAINT fk_user_homes_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE,
87    UNIQUE(home,number)
88    );
89    ]])
90    MySQL.SingleQuery([[
91    CREATE TABLE IF NOT EXISTS vrp_user_identities(
92    user_id INTEGER,
93    registration VARCHAR(100),
94    phone VARCHAR(100),
95    firstname VARCHAR(100),
96    name VARCHAR(100),
97    age INTEGER,
98    CONSTRAINT pk_user_identities PRIMARY KEY(user_id),
99    CONSTRAINT fk_user_identities_users FOREIGN KEY(user_id) REFERENCES vrp_users(id) ON DELETE CASCADE,
100    INDEX(registration),
101    INDEX(phone)
102    );
103    ]])
104    MySQL.SingleQuery([[
105    CREATE TABLE IF NOT EXISTS vrp_warnings (
106    warning_id INT AUTO_INCREMENT,
107    user_id INT,
108    warning_type VARCHAR(25),
109    duration INT,
110    admin VARCHAR(100),
111    warning_date DATE,
112    reason VARCHAR(2000),
113    PRIMARY KEY (warning_id)
114    )
115    ]])
116    MySQL.SingleQuery("ALTER TABLE vrp_users ADD IF NOT EXISTS bantime varchar(100) NOT NULL DEFAULT '';")
117    MySQL.SingleQuery("ALTER TABLE vrp_users ADD IF NOT EXISTS banreason varchar(100) NOT NULL DEFAULT '';")
118    MySQL.SingleQuery("ALTER TABLE vrp_users ADD IF NOT EXISTS banadmin varchar(100) NOT NULL DEFAULT ''; ")
119    MySQL.SingleQuery("ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS rented BOOLEAN NOT NULL DEFAULT 0;")
120    MySQL.SingleQuery("ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS rentedid varchar(200) NOT NULL DEFAULT '';")
121    MySQL.SingleQuery("ALTER TABLE vrp_user_vehicles ADD IF NOT EXISTS rentedtime varchar(2048) NOT NULL DEFAULT '';")
122    MySQL.createCommand("vRPls/create_modifications_column", "alter table vrp_user_vehicles add if not exists modifications text not null")
123	MySQL.createCommand("vRPls/update_vehicle_modifications", "update vrp_user_vehicles set modifications = @modifications where user_id = @user_id and vehicle = @vehicle")
124	MySQL.createCommand("vRPls/get_vehicle_modifications", "select modifications from vrp_user_vehicles where user_id = @user_id and vehicle = @vehicle")
125	MySQL.execute("vRPls/create_modifications_column")
126    print("[vRP] init base tables")
127end)
128
129
130
131
132
133
134MySQL.createCommand("vRP/create_user","INSERT INTO vrp_users(whitelisted,banned) VALUES(false,false)")
135MySQL.createCommand("vRP/add_identifier","INSERT INTO vrp_user_ids(identifier,user_id) VALUES(@identifier,@user_id)")
136MySQL.createCommand("vRP/userid_byidentifier","SELECT user_id FROM vrp_user_ids WHERE identifier = @identifier")
137MySQL.createCommand("vRP/identifier_all","SELECT * FROM vrp_user_ids WHERE identifier = @identifier")
138MySQL.createCommand("vRP/select_identifier_byid_all","SELECT * FROM vrp_user_ids WHERE user_id = @id")
139
140MySQL.createCommand("vRP/set_userdata","REPLACE INTO vrp_user_data(user_id,dkey,dvalue) VALUES(@user_id,@key,@value)")
141MySQL.createCommand("vRP/get_userdata","SELECT dvalue FROM vrp_user_data WHERE user_id = @user_id AND dkey = @key")
142
143MySQL.createCommand("vRP/set_srvdata","REPLACE INTO vrp_srv_data(dkey,dvalue) VALUES(@key,@value)")
144MySQL.createCommand("vRP/get_srvdata","SELECT dvalue FROM vrp_srv_data WHERE dkey = @key")
145
146MySQL.createCommand("vRP/get_banned","SELECT banned FROM vrp_users WHERE id = @user_id")
147MySQL.createCommand("vRP/set_banned","UPDATE vrp_users SET banned = @banned, bantime = @bantime,  banreason = @banreason,  banadmin = @banadmin WHERE id = @user_id")
148MySQL.createCommand("vRP/set_identifierbanned","UPDATE vrp_user_ids SET banned = @banned WHERE identifier = @iden")
149MySQL.createCommand("vRP/getbanreasontime", "SELECT * FROM vrp_users WHERE id = @user_id")
150
151MySQL.createCommand("vRP/get_whitelisted","SELECT whitelisted FROM vrp_users WHERE id = @user_id")
152MySQL.createCommand("vRP/set_whitelisted","UPDATE vrp_users SET whitelisted = @whitelisted WHERE id = @user_id")
153MySQL.createCommand("vRP/set_last_login","UPDATE vrp_users SET last_login = @last_login WHERE id = @user_id")
154MySQL.createCommand("vRP/get_last_login","SELECT last_login FROM vrp_users WHERE id = @user_id")
155
156--Token Banning 
157MySQL.createCommand("vRP/add_token","INSERT INTO vrp_user_tokens(token,user_id) VALUES(@token,@user_id)")
158MySQL.createCommand("vRP/check_token","SELECT user_id, banned FROM vrp_user_tokens WHERE token = @token")
159MySQL.createCommand("vRP/check_token_userid","SELECT token FROM vrp_user_tokens WHERE user_id = @id")
160MySQL.createCommand("vRP/ban_token","UPDATE vrp_user_tokens SET banned = @banned WHERE token = @token")
161--Token Banning
162