github.com/mmrath/gobase@v0.0.1/apps/db_migration/resources/migrations/10000_initial_setup/up.sql (about) 1 -- Sets up a trigger for the given table to automatically set a column called 2 -- `updated_at` whenever the row is modified (unless `updated_at` was included 3 -- in the modified columns) 4 -- 5 -- # Example 6 -- 7 -- ```sql 8 -- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW()); 9 -- 10 -- SELECT diesel_manage_updated_at('users'); 11 -- ``` 12 CREATE OR REPLACE FUNCTION auto_manage_updated_at_and_version(_tbl regclass) 13 RETURNS VOID AS 14 $$ 15 BEGIN 16 EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s 17 FOR EACH ROW EXECUTE PROCEDURE auto_set_audit_columns()', _tbl); 18 END; 19 $$ 20 LANGUAGE plpgsql; 21 22 CREATE OR REPLACE FUNCTION auto_set_audit_columns() 23 RETURNS trigger AS 24 $$ 25 BEGIN 26 IF (NEW IS DISTINCT FROM OLD) 27 THEN 28 NEW.updated_at := current_timestamp; 29 NEW.version := OLD.version + 1; 30 NEW.created_at := OLD.created_at; 31 NEW.created_by := OLD.created_by; 32 END IF; 33 RETURN NEW; 34 END; 35 $$ 36 LANGUAGE plpgsql; 37 38 39 CREATE TABLE language 40 ( 41 id SERIAL, 42 name TEXT NOT NULL, 43 locale TEXT NOT NULL, 44 CONSTRAINT pk_language PRIMARY KEY (id), 45 CONSTRAINT uk_name UNIQUE (name), 46 CONSTRAINT uk_locale UNIQUE (locale) 47 ); 48 49 50 INSERT INTO language (id, name, locale) 51 VALUES (1, 'English', 'en'), 52 (2, 'Italian', 'it'), 53 (3, 'German', 'de'), 54 (4, 'French', 'fr'), 55 (5, 'Portuguese - Brazilian', 'pt_BR'), 56 (6, 'Dutch', 'nl'), 57 (7, 'Spanish', 'es'), 58 (8, 'Norwegian', 'nb_NO'), 59 (9, 'Danish', 'da'), 60 (10, 'Japanese', 'ja'), 61 (11, 'Swedish', 'sv'), 62 (12, 'Spanish - Spain', 'es_ES'), 63 (13, 'French - Canada', 'fr_CA'), 64 (14, 'Lithuanian', 'lt'), 65 (15, 'Polish', 'pl'), 66 (16, 'Czech', 'cs'), 67 (17, 'Croatian', 'hr'), 68 (18, 'Albanian', 'sq'), 69 (19, 'Greek', 'el'), 70 (20, 'English - United Kingdom', 'en_GB'), 71 (21, 'Portuguese - Portugal', 'pt_PT'), 72 (22, 'Slovenian', 'sl'), 73 (23, 'Finnish', 'fi'), 74 (24, 'Romanian', 'ro'), 75 (25, 'Turkish - Turkey', 'tr_TR'), 76 (26, 'Thai', 'th'); 77 78 79 CREATE TABLE date_format 80 ( 81 id SERIAL, 82 c_format TEXT NOT NULL, 83 date_picker_format TEXT NOT NULL, 84 js_format TEXT NOT NULL, 85 CONSTRAINT pk_date_format PRIMARY KEY (id), 86 CONSTRAINT uk_date_format__c_format UNIQUE (c_format) 87 ); 88 89 90 INSERT INTO date_format (id, c_format, date_picker_format, js_format) 91 VALUES (1, 'd/M/Y', 'dd/M/yyyy', 'DD/MMM/YYYY'), 92 (2, 'd-M-Y', 'dd-M-yyyy', 'DD-MMM-YYYY'), 93 (3, 'd/F/Y', 'dd/MM/yyyy', 'DD/MMMM/YYYY'), 94 (4, 'd-F-Y', 'dd-MM-yyyy', 'DD-MMMM-YYYY'), 95 (5, 'M j, Y', 'M d, yyyy', 'MMM D, YYYY'), 96 (6, 'F j, Y', 'MM d, yyyy', 'MMMM D, YYYY'), 97 (7, 'D M j, Y', 'D MM d, yyyy', 'ddd MMM Do, YYYY'), 98 (8, 'Y-m-d', 'yyyy-mm-dd', 'YYYY-MM-DD'), 99 (9, 'd-m-Y', 'dd-mm-yyyy', 'DD-MM-YYYY'), 100 (10, 'm/d/Y', 'mm/dd/yyyy', 'MM/DD/YYYY'), 101 (11, 'd.m.Y', 'dd.mm.yyyy', 'D.MM.YYYY'), 102 (12, 'j. M. Y', 'd. M. yyyy', 'DD. MMM. YYYY'), 103 (13, 'j. F Y', 'd. MM yyyy', 'DD. MMMM YYYY'); 104 105 106 CREATE TABLE datetime_format 107 ( 108 id SERIAL, 109 c_format TEXT NOT NULL, 110 js_format TEXT NOT NULL, 111 CONSTRAINT pk_datetime_format PRIMARY KEY (id), 112 CONSTRAINT uk_datetime_format__c_format UNIQUE (c_format) 113 ); 114 115 INSERT INTO datetime_format (id, c_format, js_format) 116 VALUES (1, 'd/M/Y g:i a', 'DD/MMM/YYYY h:mm:ss a'), 117 (2, 'd-M-Y g:i a', 'DD-MMM-YYYY h:mm:ss a'), 118 (3, 'd/F/Y g:i a', 'DD/MMMM/YYYY h:mm:ss a'), 119 (4, 'd-F-Y g:i a', 'DD-MMMM-YYYY h:mm:ss a'), 120 (5, 'M j, Y g:i a', 'MMM D, YYYY h:mm:ss a'), 121 (6, 'F j, Y g:i a', 'MMMM D, YYYY h:mm:ss a'), 122 (7, 'D M jS, Y g:i a', 'ddd MMM Do, YYYY h:mm:ss a'), 123 (8, 'Y-m-d g:i a', 'YYYY-MM-DD h:mm:ss a'), 124 (9, 'd-m-Y g:i a', 'DD-MM-YYYY h:mm:ss a'), 125 (10, 'm/d/Y g:i a', 'MM/DD/YYYY h:mm:ss a'), 126 (11, 'd.m.Y g:i a', 'D.MM.YYYY h:mm:ss a'), 127 (12, 'j. M. Y g:i a', 'DD. MMM. YYYY h:mm:ss a'), 128 (13, 'j. F Y g:i a', 'DD. MMMM YYYY h:mm:ss a'); 129 130 CREATE TABLE timezone 131 ( 132 id SERIAL, 133 name TEXT NOT NULL, 134 gmt_offset TEXT NOT NULL, 135 location TEXT NOT NULL, 136 CONSTRAINT pk_timezone PRIMARY KEY (id), 137 CONSTRAINT uk_timezone__code UNIQUE (name) 138 ); 139 140 INSERT INTO timezone (id, name, gmt_offset, location) 141 VALUES (1, 'Pacific/Midway', '-11:00', 'Midway Island'), 142 (2, 'US/Samoa', '-11:00', 'Samoa'), 143 (3, 'US/Hawaii', '-10:00', 'Hawaii'), 144 (4, 'US/Alaska', '-09:00', 'Alaska'), 145 (5, 'US/Pacific', '-08:00', 'Pacific Time (US & Canada)'), 146 (6, 'America/Tijuana', '-08:00', 'Tijuana'), 147 (7, 'US/Arizona', '-07:00', 'Arizona'), 148 (8, 'US/Mountain', '-07:00', 'Mountain Time (US & Canada)'), 149 (9, 'America/Chihuahua', '-07:00', 'Chihuahua'), 150 (10, 'America/Mazatlan', '-07:00', 'Mazatlan'), 151 (11, 'America/Mexico_City', '-06:00', 'Mexico City'), 152 (12, 'America/Monterrey', '-06:00', 'Monterrey'), 153 (13, 'Canada/Saskatchewan', '-06:00', 'Saskatchewan'), 154 (14, 'US/Central', '-06:00', 'Central Time (US & Canada)'), 155 (15, 'US/Eastern', '-05:00', 'Eastern Time (US & Canada)'), 156 (16, 'US/East-Indiana', '-05:00', 'Indiana (East)'), 157 (17, 'America/Bogota', '-05:00', 'Bogota'), 158 (18, 'America/Lima', '-05:00', 'Lima'), 159 (19, 'America/Caracas', '-04:30', 'Caracas'), 160 (20, 'Canada/Atlantic', '-04:00', 'Atlantic Time (Canada)'), 161 (21, 'America/La_Paz', '-04:00', 'La Paz'), 162 (22, 'America/Santiago', '-04:00', 'Santiago'), 163 (23, 'Canada/Newfoundland', '-03:30', 'Newfoundland'), 164 (24, 'America/Buenos_Aires', '-03:00', 'Buenos Aires'), 165 (25, 'America/Godthab', '-03:00', 'Greenland'), 166 (26, 'Atlantic/Stanley', '-02:00', 'Stanley'), 167 (27, 'Atlantic/Azores', '-01:00', 'Azores'), 168 (28, 'Atlantic/Cape_Verde', '-01:00', 'Cape Verde Is.'), 169 (29, 'Africa/Casablanca', '00:00', 'Casablanca'), 170 (30, 'Europe/Dublin', '00:00', 'Dublin'), 171 (31, 'Europe/Lisbon', '00:00', 'Lisbon'), 172 (32, 'Europe/London', '00:00', 'London'), 173 (33, 'Africa/Monrovia', '00:00', 'Monrovia'), 174 (34, 'Europe/Amsterdam', '+01:00', 'Amsterdam'), 175 (35, 'Europe/Belgrade', '+01:00', 'Belgrade'), 176 (36, 'Europe/Berlin', '+01:00', 'Berlin'), 177 (37, 'Europe/Bratislava', '+01:00', 'Bratislava'), 178 (38, 'Europe/Brussels', '+01:00', 'Brussels'), 179 (39, 'Europe/Budapest', '+01:00', 'Budapest'), 180 (40, 'Europe/Copenhagen', '+01:00', 'Copenhagen'), 181 (41, 'Europe/Ljubljana', '+01:00', 'Ljubljana'), 182 (42, 'Europe/Madrid', '+01:00', 'Madrid'), 183 (43, 'Europe/Paris', '+01:00', 'Paris'), 184 (44, 'Europe/Prague', '+01:00', 'Prague'), 185 (45, 'Europe/Rome', '+01:00', 'Rome'), 186 (46, 'Europe/Sarajevo', '+01:00', 'Sarajevo'), 187 (47, 'Europe/Skopje', '+01:00', 'Skopje'), 188 (48, 'Europe/Stockholm', '+01:00', 'Stockholm'), 189 (49, 'Europe/Vienna', '+01:00', 'Vienna'), 190 (50, 'Europe/Warsaw', '+01:00', 'Warsaw'), 191 (51, 'Europe/Zagreb', '+01:00', 'Zagreb'), 192 (52, 'Europe/Athens', '+02:00', 'Athens'), 193 (53, 'Europe/Bucharest', '+02:00', 'Bucharest'), 194 (54, 'Africa/Cairo', '+02:00', 'Cairo'), 195 (55, 'Africa/Harare', '+02:00', 'Harare'), 196 (56, 'Europe/Helsinki', '+02:00', 'Helsinki'), 197 (57, 'Europe/Istanbul', '+02:00', 'Istanbul'), 198 (58, 'Asia/Jerusalem', '+02:00', 'Jerusalem'), 199 (59, 'Europe/Kiev', '+02:00', 'Kyiv'), 200 (60, 'Europe/Minsk', '+02:00', 'Minsk'), 201 (61, 'Europe/Riga', '+02:00', 'Riga'), 202 (62, 'Europe/Sofia', '+02:00', 'Sofia'), 203 (63, 'Europe/Tallinn', '+02:00', 'Tallinn'), 204 (64, 'Europe/Vilnius', '+02:00', 'Vilnius'), 205 (65, 'Asia/Baghdad', '+03:00', 'Baghdad'), 206 (66, 'Asia/Kuwait', '+03:00', 'Kuwait'), 207 (67, 'Africa/Nairobi', '+03:00', 'Nairobi'), 208 (68, 'Asia/Riyadh', '+03:00', 'Riyadh'), 209 (69, 'Asia/Tehran', '+03:30', 'Tehran'), 210 (70, 'Europe/Moscow', '+04:00', 'Moscow'), 211 (71, 'Asia/Baku', '+04:00', 'Baku'), 212 (72, 'Europe/Volgograd', '+04:00', 'Volgograd'), 213 (73, 'Asia/Muscat', '+04:00', 'Muscat'), 214 (74, 'Asia/Tbilisi', '+04:00', 'Tbilisi'), 215 (75, 'Asia/Yerevan', '+04:00', 'Yerevan'), 216 (76, 'Asia/Kabul', '+04:30', 'Kabul'), 217 (77, 'Asia/Karachi', '+05:00', 'Karachi'), 218 (78, 'Asia/Tashkent', '+05:00', 'Tashkent'), 219 (79, 'Asia/Kolkata', '+05:30', 'Kolkata'), 220 (80, 'Asia/Kathmandu', '+05:45', 'Kathmandu'), 221 (81, 'Asia/Yekaterinburg', '+06:00', 'Ekaterinburg'), 222 (82, 'Asia/Almaty', '+06:00', 'Almaty'), 223 (83, 'Asia/Dhaka', '+06:00', 'Dhaka'), 224 (84, 'Asia/Novosibirsk', '+07:00', 'Novosibirsk'), 225 (85, 'Asia/Bangkok', '+07:00', 'Bangkok'), 226 (86, 'Asia/Ho_Chi_Minh', '+07:00', 'Ho Chi Minh'), 227 (87, 'Asia/Jakarta', '+07:00', 'Jakarta'), 228 (88, 'Asia/Krasnoyarsk', '+08:00', 'Krasnoyarsk'), 229 (89, 'Asia/Chongqing', '+08:00', 'Chongqing'), 230 (90, 'Asia/Hong_Kong', '+08:00', 'Hong Kong'), 231 (91, 'Asia/Kuala_Lumpur', '+08:00', 'Kuala Lumpur'), 232 (92, 'Australia/Perth', '+08:00', 'Perth'), 233 (93, 'Asia/Singapore', '+08:00', 'Singapore'), 234 (94, 'Asia/Taipei', '+08:00', 'Taipei'), 235 (95, 'Asia/Ulaanbaatar', '+08:00', 'Ulaan Bataar'), 236 (96, 'Asia/Urumqi', '+08:00', 'Urumqi'), 237 (97, 'Asia/Irkutsk', '+09:00', 'Irkutsk'), 238 (98, 'Asia/Seoul', '+09:00', 'Seoul'), 239 (99, 'Asia/Tokyo', '+09:00', 'Tokyo'), 240 (100, 'Australia/Adelaide', '+09:30', 'Adelaide'), 241 (101, 'Australia/Darwin', '+09:30', 'Darwin'), 242 (102, 'Asia/Yakutsk', '+10:00', 'Yakutsk'), 243 (103, 'Australia/Brisbane', '+10:00', 'Brisbane'), 244 (104, 'Australia/Canberra', '+10:00', 'Canberra'), 245 (105, 'Pacific/Guam', '+10:00', 'Guam'), 246 (106, 'Australia/Hobart', '+10:00', 'Hobart'), 247 (107, 'Australia/Melbourne', '+10:00', 'Melbourne'), 248 (108, 'Pacific/Port_Moresby', '+10:00', 'Port Moresby'), 249 (109, 'Australia/Sydney', '+10:00', 'Sydney'), 250 (110, 'Asia/Vladivostok', '+11:00', 'Vladivostok'), 251 (111, 'Asia/Magadan', '+12:00', 'Magadan'), 252 (112, 'Pacific/Auckland', '+12:00', 'Auckland'), 253 (113, 'Pacific/Fiji', '+12:00', 'Fiji'); 254 255 256 CREATE TABLE currency 257 ( 258 id SERIAL, 259 code TEXT NOT NULL, 260 symbol TEXT NULL, 261 name TEXT NOT NULL, 262 precision SMALLINT NOT NULL, 263 format TEXT NOT NULL, 264 CONSTRAINT pk_currency PRIMARY KEY (id), 265 CONSTRAINT uk_currency__code UNIQUE (code) 266 ); 267 268 269 INSERT INTO currency (id, code, symbol, name, precision, format) 270 VALUES (1, 'USD', '$', 'US Dollar', 2, '###,##.##'), 271 (2, 'GBP', '£', 'British Pound', 2, '###,##.##'), 272 (3, 'EUR', '€', 'Euro', 2, '###,##.##'), 273 (4, 'ZAR', 'R', 'South African Rand', 2, '###,##.##'), 274 (5, 'DKK', 'kr', 'Danish Krone', 2, '###,##.##'), 275 (6, 'ILS', 'NIS ', 'Israeli Shekel', 2, '###,##.##'), 276 (7, 'SEK', 'kr', 'Swedish Krona', 2, '###,##.##'), 277 (8, 'KES', 'KSh ', 'Kenyan Shilling', 2, '###,##.##'), 278 (9, 'CAD', 'C$', 'Canadian Dollar', 2, '###,##.##'), 279 (10, 'PHP', 'P ', 'Philippine Peso', 2, '###,##.##'), 280 (11, 'INR', '₹', 'Indian Rupee', 2, '###,##.##'), 281 (12, 'AUD', '$', 'Australian Dollar', 2, '###,##.##'), 282 (13, 'SGD', '$', 'Singapore Dollar', 2, '###,##.##'), 283 (14, 'NOK', 'kr', 'Norske Kroner', 2, '###,##.##'), 284 (15, 'NZD', '$', 'New Zealand Dollar', 2, '###,##.##'), 285 (16, 'VND', '', 'Vietnamese Dong', 0, '###,##.##'), 286 (17, 'CHF', '', 'Swiss Franc', 2, '###,##.##'), 287 (18, 'GTQ', 'Q', 'Guatemalan Quetzal', 2, '###,##.##'), 288 (19, 'MYR', 'RM', 'Malaysian Ringgit', 2, '###,##.##'), 289 (20, 'BRL', 'R$', 'Brazilian Real', 2, '###,##.##'), 290 (21, 'THB', '', 'Thai Baht', 2, '###,##.##'), 291 (22, 'NGN', '', 'Nigerian Naira', 2, '###,##.##'), 292 (23, 'ARS', '$', 'Argentine Peso', 2, '###,##.##'), 293 (24, 'BDT', 'Tk', 'Bangladeshi Taka', 2, '###,##.##'), 294 (25, 'AED', 'DH ', 'United Arab Emirates Dirham', 2, '###,##.##'), 295 (26, 'HKD', '', 'Hong Kong Dollar', 2, '###,##.##'), 296 (27, 'IDR', 'Rp', 'Indonesian Rupiah', 2, '###,##.##'), 297 (28, 'MXN', '$', 'Mexican Peso', 2, '###,##.##'), 298 (29, 'EGP', 'E£', 'Egyptian Pound', 2, '###,##.##'), 299 (30, 'COP', '$', 'Colombian Peso', 2, '###,##.##'), 300 (31, 'XOF', 'CFA ', 'West African Franc', 2, '###,##.##'), 301 (32, 'CNY', 'RMB ', 'Chinese Renminbi', 2, '###,##.##'), 302 (33, 'RWF', 'RF ', 'Rwandan Franc', 2, '###,##.##'), 303 (34, 'TZS', 'TSh ', 'Tanzanian Shilling', 2, '###,##.##'), 304 (35, 'ANG', '', 'Netherlands Antillean Guilder', 2, '###,##.##'), 305 (36, 'TTD', 'TT$', 'Trinidad and Tobago Dollar', 2, '###,##.##'), 306 (37, 'XCD', 'EC$', 'East Caribbean Dollar', 2, '###,##.##'), 307 (38, 'GHS', '', 'Ghanaian Cedi', 2, '###,##.##'), 308 (39, 'BGN', '', 'Bulgarian Lev', 2, '###,##.##'), 309 (40, 'AWG', 'Afl. ', 'Aruban Florin', 2, '###,##.##'), 310 (41, 'TRY', 'TL ', 'Turkish Lira', 2, '###,##.##'), 311 (42, 'RON', '', 'Romanian New Leu', 2, '###,##.##'), 312 (43, 'HRK', 'kn', 'Croatian Kuna', 2, '###,##.##'), 313 (44, 'SAR', '', 'Saudi Riyal', 2, '###,##.##'), 314 (45, 'JPY', '¥', 'Japanese Yen', 0, '###,##.##'), 315 (46, 'MVR', '', 'Maldivian Rufiyaa', 2, '###,##.##'), 316 (47, 'CRC', '', 'Costa Rican Colón', 2, '###,##.##'), 317 (48, 'PKR', 'Rs ', 'Pakistani Rupee', 0, '###,##.##'), 318 (49, 'PLN', 'zł', 'Polish Zloty', 2, '###,##.##'), 319 (50, 'LKR', 'LKR', 'Sri Lankan Rupee', 2, '###,##.##'), 320 (51, 'CZK', 'Kč', 'Czech Koruna', 2, '###,##.##'), 321 (52, 'UYU', '$', 'Uruguayan Peso', 2, '###,##.##'), 322 (53, 'NAD', '$', 'Namibian Dollar', 2, '###,##.##'), 323 (54, 'TND', '', 'Tunisian Dinar', 2, '###,##.##'), 324 (55, 'RUB', '', 'Russian Ruble', 2, '###,##.##'), 325 (56, 'MZN', 'MT', 'Mozambican Metical', 2, '###,##.##'), 326 (57, 'OMR', '', 'Omani Rial', 2, '###,##.##'), 327 (58, 'UAH', '', 'Ukrainian Hryvnia', 2, '###,##.##'), 328 (59, 'MOP', 'MOP$', 'Macanese Pataca', 2, '###,##.##'), 329 (60, 'TWD', 'NT$', 'Taiwan New Dollar', 2, '###,##.##'), 330 (61, 'DOP', 'RD$', 'Dominican Peso', 2, '###,##.##'), 331 (62, 'CLP', '$', 'Chilean Peso', 0, '###,##.##'), 332 (63, 'ISK', 'kr', 'Icelandic Króna', 2, '###,##.##'), 333 (64, 'PGK', 'K', 'Papua New Guinean Kina', 2, '###,##.##'), 334 (65, 'JOD', '', 'Jordanian Dinar', 2, '###,##.##'), 335 (66, 'MMK', 'K', 'Myanmar Kyat', 2, '###,##.##'), 336 (67, 'PEN', 'S/ ', 'Peruvian Sol', 2, '###,##.##'), 337 (68, 'BWP', 'P', 'Botswana Pula', 2, '###,##.##'), 338 (69, 'HUF', 'Ft', 'Hungarian Forint', 0, '###,##.##'), 339 (70, 'UGX', 'USh ', 'Ugandan Shilling', 2, '###,##.##'), 340 (71, 'BBD', '$', 'Barbadian Dollar', 2, '###,##.##'), 341 (72, 'BND', 'B$', 'Brunei Dollar', 2, '###,##.##'), 342 (73, 'GEL', '', 'Georgian Lari', 2, '###,##.##'), 343 (74, 'QAR', 'QR', 'Qatari Riyal', 2, '###,##.##'), 344 (75, 'HNL', 'L', 'Honduran Lempira', 2, '###,##.##'), 345 (76, 'AFN', '؋', 'Afgani', 2, '###,##.##'); 346 347 CREATE TABLE country 348 ( 349 id SERIAL, 350 code TEXT NOT NULL, 351 name TEXT NOT NULL, 352 dial_code SMALLINT NOT NULL, 353 currency TEXT NOT NULL, 354 CONSTRAINT pk_country PRIMARY KEY (id), 355 CONSTRAINT uk_country__name UNIQUE (name), 356 CONSTRAINT uk_country__code UNIQUE (code) 357 ); 358 359 INSERT INTO country (id, code, name, dial_code, currency) 360 VALUES (1, 'AF', 'Afghanistan', 93, 'AFN'), 361 (2, 'AL', 'Albania', 355, 'ALL'), 362 (3, 'DZ', 'Algeria', 213, 'DZD'), 363 (4, 'AS', 'American Samoa', 1684, 'XXX'), 364 (5, 'AD', 'Andorra', 376, 'EUR'), 365 (6, 'AO', 'Angola', 244, 'AOA'), 366 (7, 'AI', 'Anguilla', 1264, 'XCD'), 367 (8, 'AQ', 'Antarctica', 0, 'XXX'), 368 (9, 'AG', 'Antigua And Barbuda', 1268, 'XCD'), 369 (10, 'AR', 'Argentina', 54, 'ARS'), 370 (11, 'AM', 'Armenia', 374, 'AMD'), 371 (12, 'AW', 'Aruba', 297, 'AWG'), 372 (13, 'AU', 'Australia', 61, 'AUD'), 373 (14, 'AT', 'Austria', 43, 'EUR'), 374 (15, 'AZ', 'Azerbaijan', 994, 'AZN'), 375 (16, 'BS', 'Bahamas The', 1242, 'XXX'), 376 (17, 'BH', 'Bahrain', 973, 'BHD'), 377 (18, 'BD', 'Bangladesh', 880, 'BDT'), 378 (19, 'BB', 'Barbados', 1246, 'BBD'), 379 (20, 'BY', 'Belarus', 375, 'BYR'), 380 (21, 'BE', 'Belgium', 32, 'EUR'), 381 (22, 'BZ', 'Belize', 501, 'BZD'), 382 (23, 'BJ', 'Benin', 229, 'XOF'), 383 (24, 'BM', 'Bermuda', 1441, 'BMD'), 384 (25, 'BT', 'Bhutan', 975, 'BTN'), 385 (26, 'BO', 'Bolivia', 591, 'BOB'), 386 (27, 'BA', 'Bosnia and Herzegovina', 387, 'BAM'), 387 (28, 'BW', 'Botswana', 267, 'BWP'), 388 (29, 'BV', 'Bouvet Island', 0, 'XXX'), 389 (30, 'BR', 'Brazil', 55, 'BRL'), 390 (31, 'IO', 'British Indian Ocean Territory', 246, 'USD'), 391 (32, 'BN', 'Brunei', 673, 'BND'), 392 (33, 'BG', 'Bulgaria', 359, 'BGN'), 393 (34, 'BF', 'Burkina Faso', 226, 'XOF'), 394 (35, 'BI', 'Burundi', 257, 'BIF'), 395 (36, 'KH', 'Cambodia', 855, 'KHR'), 396 (37, 'CM', 'Cameroon', 237, 'XAF'), 397 (38, 'CA', 'Canada', 1, 'CAD'), 398 (39, 'CV', 'Cape Verde', 238, 'CVE'), 399 (40, 'KY', 'Cayman Islands', 1345, 'KYD'), 400 (41, 'CF', 'Central African Republic', 236, 'XAF'), 401 (42, 'TD', 'Chad', 235, 'XAF'), 402 (43, 'CL', 'Chile', 56, 'CLP'), 403 (44, 'CN', 'China', 86, 'CNY'), 404 (45, 'CX', 'Christmas Island', 61, 'XXX'), 405 (46, 'CC', 'Cocos (Keeling) Islands', 672, 'AUD'), 406 (47, 'CO', 'Colombia', 57, 'COP'), 407 (48, 'KM', 'Comoros', 269, 'KMF'), 408 (49, 'CG', 'Congo', 242, 'XXX'), 409 (50, 'CD', 'Congo The Democratic Republic Of The', 242, 'XXX'), 410 (51, 'CK', 'Cook Islands', 682, 'NZD'), 411 (52, 'CR', 'Costa Rica', 506, 'CRC'), 412 (53, 'CI', 'Cote D''Ivoire (Ivory Coast)', 225, 'XXX'), 413 (54, 'HR', 'Croatia (Hrvatska)', 385, 'XXX'), 414 (55, 'CU', 'Cuba', 53, 'CUC'), 415 (56, 'CY', 'Cyprus', 357, 'EUR'), 416 (57, 'CZ', 'Czech Republic', 420, 'CZK'), 417 (58, 'DK', 'Denmark', 45, 'DKK'), 418 (59, 'DJ', 'Djibouti', 253, 'DJF'), 419 (60, 'DM', 'Dominica', 1767, 'XCD'), 420 (61, 'DO', 'Dominican Republic', 1809, 'DOP'), 421 (62, 'TP', 'East Timor', 670, 'USD'), 422 (63, 'EC', 'Ecuador', 593, 'USD'), 423 (64, 'EG', 'Egypt', 20, 'EGP'), 424 (65, 'SV', 'El Salvador', 503, 'USD'), 425 (66, 'GQ', 'Equatorial Guinea', 240, 'XAF'), 426 (67, 'ER', 'Eritrea', 291, 'ERN'), 427 (68, 'EE', 'Estonia', 372, 'EUR'), 428 (69, 'ET', 'Ethiopia', 251, 'ETB'), 429 (70, 'XA', 'External Territories of Australia', 61, 'XXX'), 430 (71, 'FK', 'Falkland Islands', 500, 'FKP'), 431 (72, 'FO', 'Faroe Islands', 298, 'DKK'), 432 (73, 'FJ', 'Fiji Islands', 679, 'XXX'), 433 (74, 'FI', 'Finland', 358, 'EUR'), 434 (75, 'FR', 'France', 33, 'EUR'), 435 (76, 'GF', 'French Guiana', 594, 'XXX'), 436 (77, 'PF', 'French Polynesia', 689, 'XPF'), 437 (78, 'TF', 'French Southern Territories', 0, 'XXX'), 438 (79, 'GA', 'Gabon', 241, 'XAF'), 439 (80, 'GM', 'Gambia The', 220, 'XXX'), 440 (81, 'GE', 'Georgia', 995, 'GEL'), 441 (82, 'DE', 'Germany', 49, 'EUR'), 442 (83, 'GH', 'Ghana', 233, 'GHS'), 443 (84, 'GI', 'Gibraltar', 350, 'GIP'), 444 (85, 'GR', 'Greece', 30, 'EUR'), 445 (86, 'GL', 'Greenland', 299, 'XXX'), 446 (87, 'GD', 'Grenada', 1473, 'XCD'), 447 (88, 'GP', 'Guadeloupe', 590, 'XXX'), 448 (89, 'GU', 'Guam', 1671, 'XXX'), 449 (90, 'GT', 'Guatemala', 502, 'GTQ'), 450 (91, 'XU', 'Guernsey and Alderney', 44, 'XXX'), 451 (92, 'GN', 'Guinea', 224, 'GNF'), 452 (93, 'GW', 'Guinea-Bissau', 245, 'XOF'), 453 (94, 'GY', 'Guyana', 592, 'GYD'), 454 (95, 'HT', 'Haiti', 509, 'HTG'), 455 (96, 'HM', 'Heard and McDonald Islands', 0, 'XXX'), 456 (97, 'HN', 'Honduras', 504, 'HNL'), 457 (98, 'HK', 'Hong Kong S.A.R.', 852, 'XXX'), 458 (99, 'HU', 'Hungary', 36, 'HUF'), 459 (100, 'IS', 'Iceland', 354, 'ISK'), 460 (101, 'IN', 'India', 91, 'INR'), 461 (102, 'ID', 'Indonesia', 62, 'IDR'), 462 (103, 'IR', 'Iran', 98, 'IRR'), 463 (104, 'IQ', 'Iraq', 964, 'IQD'), 464 (105, 'IE', 'Ireland', 353, 'EUR'), 465 (106, 'IL', 'Israel', 972, 'ILS'), 466 (107, 'IT', 'Italy', 39, 'EUR'), 467 (108, 'JM', 'Jamaica', 1876, 'JMD'), 468 (109, 'JP', 'Japan', 81, 'JPY'), 469 (110, 'XJ', 'Jersey', 44, 'GBP'), 470 (111, 'JO', 'Jordan', 962, 'JOD'), 471 (112, 'KZ', 'Kazakhstan', 7, 'KZT'), 472 (113, 'KE', 'Kenya', 254, 'KES'), 473 (114, 'KI', 'Kiribati', 686, 'AUD'), 474 (115, 'KP', 'Korea North', 850, 'XXX'), 475 (116, 'KR', 'Korea South', 82, 'XXX'), 476 (117, 'KW', 'Kuwait', 965, 'KWD'), 477 (118, 'KG', 'Kyrgyzstan', 996, 'KGS'), 478 (119, 'LA', 'Laos', 856, 'LAK'), 479 (120, 'LV', 'Latvia', 371, 'EUR'), 480 (121, 'LB', 'Lebanon', 961, 'LBP'), 481 (122, 'LS', 'Lesotho', 266, 'LSL'), 482 (123, 'LR', 'Liberia', 231, 'LRD'), 483 (124, 'LY', 'Libya', 218, 'LYD'), 484 (125, 'LI', 'Liechtenstein', 423, 'CHF'), 485 (126, 'LT', 'Lithuania', 370, 'EUR'), 486 (127, 'LU', 'Luxembourg', 352, 'EUR'), 487 (128, 'MO', 'Macau S.A.R.', 853, 'XXX'), 488 (129, 'MK', 'Macedonia', 389, 'XXX'), 489 (130, 'MG', 'Madagascar', 261, 'MGA'), 490 (131, 'MW', 'Malawi', 265, 'MWK'), 491 (132, 'MY', 'Malaysia', 60, 'MYR'), 492 (133, 'MV', 'Maldives', 960, 'MVR'), 493 (134, 'ML', 'Mali', 223, 'XOF'), 494 (135, 'MT', 'Malta', 356, 'EUR'), 495 (136, 'XM', 'Man (Isle of)', 44, 'XXX'), 496 (137, 'MH', 'Marshall Islands', 692, 'USD'), 497 (138, 'MQ', 'Martinique', 596, 'XXX'), 498 (139, 'MR', 'Mauritania', 222, 'MRO'), 499 (140, 'MU', 'Mauritius', 230, 'MUR'), 500 (141, 'YT', 'Mayotte', 269, 'XXX'), 501 (142, 'MX', 'Mexico', 52, 'MXN'), 502 (143, 'FM', 'Micronesia', 691, 'XXX'), 503 (144, 'MD', 'Moldova', 373, 'MDL'), 504 (145, 'MC', 'Monaco', 377, 'EUR'), 505 (146, 'MN', 'Mongolia', 976, 'MNT'), 506 (147, 'MS', 'Montserrat', 1664, 'XCD'), 507 (148, 'MA', 'Morocco', 212, 'MAD'), 508 (149, 'MZ', 'Mozambique', 258, 'MZN'), 509 (150, 'MM', 'Myanmar', 95, 'MMK'), 510 (151, 'NA', 'Namibia', 264, 'NAD'), 511 (152, 'NR', 'Nauru', 674, 'AUD'), 512 (153, 'NP', 'Nepal', 977, 'NPR'), 513 (154, 'AN', 'Netherlands Antilles', 599, 'XXX'), 514 (155, 'NL', 'Netherlands The', 31, 'XXX'), 515 (156, 'NC', 'New Caledonia', 687, 'XPF'), 516 (157, 'NZ', 'New Zealand', 64, 'NZD'), 517 (158, 'NI', 'Nicaragua', 505, 'NIO'), 518 (159, 'NE', 'Niger', 227, 'XOF'), 519 (160, 'NG', 'Nigeria', 234, 'NGN'), 520 (161, 'NU', 'Niue', 683, 'NZD'), 521 (162, 'NF', 'Norfolk Island', 672, 'XXX'), 522 (163, 'MP', 'Northern Mariana Islands', 1670, 'XXX'), 523 (164, 'NO', 'Norway', 47, 'NOK'), 524 (165, 'OM', 'Oman', 968, 'OMR'), 525 (166, 'PK', 'Pakistan', 92, 'PKR'), 526 (167, 'PW', 'Palau', 680, 'XXX'), 527 (168, 'PS', 'Palestinian Territory Occupied', 970, 'XXX'), 528 (169, 'PA', 'Panama', 507, 'PAB'), 529 (170, 'PG', 'Papua new Guinea', 675, 'PGK'), 530 (171, 'PY', 'Paraguay', 595, 'PYG'), 531 (172, 'PE', 'Peru', 51, 'PEN'), 532 (173, 'PH', 'Philippines', 63, 'PHP'), 533 (174, 'PN', 'Pitcairn Island', 0, 'XXX'), 534 (175, 'PL', 'Poland', 48, 'PLN'), 535 (176, 'PT', 'Portugal', 351, 'EUR'), 536 (177, 'PR', 'Puerto Rico', 1787, 'XXX'), 537 (178, 'QA', 'Qatar', 974, 'QAR'), 538 (179, 'RE', 'Reunion', 262, 'XXX'), 539 (180, 'RO', 'Romania', 40, 'RON'), 540 (181, 'RU', 'Russia', 70, 'RUB'), 541 (182, 'RW', 'Rwanda', 250, 'RWF'), 542 (183, 'SH', 'Saint Helena', 290, 'SHP'), 543 (184, 'KN', 'Saint Kitts And Nevis', 1869, 'XCD'), 544 (185, 'LC', 'Saint Lucia', 1758, 'XCD'), 545 (186, 'PM', 'Saint Pierre and Miquelon', 508, 'XXX'), 546 (187, 'VC', 'Saint Vincent And The Grenadines', 1784, 'XCD'), 547 (188, 'WS', 'Samoa', 684, 'WST'), 548 (189, 'SM', 'San Marino', 378, 'EUR'), 549 (190, 'ST', 'Sao Tome and Principe', 239, 'STD'), 550 (191, 'SA', 'Saudi Arabia', 966, 'SAR'), 551 (192, 'SN', 'Senegal', 221, 'XOF'), 552 (193, 'RS', 'Serbia', 381, 'RSD'), 553 (194, 'SC', 'Seychelles', 248, 'SCR'), 554 (195, 'SL', 'Sierra Leone', 232, 'SLL'), 555 (196, 'SG', 'Singapore', 65, 'BND'), 556 (197, 'SK', 'Slovakia', 421, 'EUR'), 557 (198, 'SI', 'Slovenia', 386, 'EUR'), 558 (199, 'XG', 'Smaller Territories of the UK', 44, 'XXX'), 559 (200, 'SB', 'Solomon Islands', 677, 'SBD'), 560 (201, 'SO', 'Somalia', 252, 'SOS'), 561 (202, 'ZA', 'South Africa', 27, 'ZAR'), 562 (203, 'GS', 'South Georgia', 0, 'XXX'), 563 (204, 'SS', 'South Sudan', 211, 'SSP'), 564 (205, 'ES', 'Spain', 34, 'EUR'), 565 (206, 'LK', 'Sri Lanka', 94, 'LKR'), 566 (207, 'SD', 'Sudan', 249, 'SDG'), 567 (208, 'SR', 'Suriname', 597, 'SRD'), 568 (209, 'SJ', 'Svalbard And Jan Mayen Islands', 47, 'XXX'), 569 (210, 'SZ', 'Swaziland', 268, 'SZL'), 570 (211, 'SE', 'Sweden', 46, 'SEK'), 571 (212, 'CH', 'Switzerland', 41, 'CHF'), 572 (213, 'SY', 'Syria', 963, 'SYP'), 573 (214, 'TW', 'Taiwan', 886, 'TWD'), 574 (215, 'TJ', 'Tajikistan', 992, 'TJS'), 575 (216, 'TZ', 'Tanzania', 255, 'TZS'), 576 (217, 'TH', 'Thailand', 66, 'THB'), 577 (218, 'TG', 'Togo', 228, 'XOF'), 578 (219, 'TK', 'Tokelau', 690, 'XXX'), 579 (220, 'TO', 'Tonga', 676, 'TOP'), 580 (221, 'TT', 'Trinidad And Tobago', 1868, 'TTD'), 581 (222, 'TN', 'Tunisia', 216, 'TND'), 582 (223, 'TR', 'Turkey', 90, 'TRY'), 583 (224, 'TM', 'Turkmenistan', 7370, 'TMT'), 584 (225, 'TC', 'Turks And Caicos Islands', 1649, 'USD'), 585 (226, 'TV', 'Tuvalu', 688, 'AUD'), 586 (227, 'UG', 'Uganda', 256, 'UGX'), 587 (228, 'UA', 'Ukraine', 380, 'UAH'), 588 (229, 'AE', 'United Arab Emirates', 971, 'AED'), 589 (230, 'GB', 'United Kingdom', 44, 'GBP'), 590 (231, 'US', 'United States', 1, 'USD'), 591 (232, 'UM', 'United States Minor Outlying Islands', 1, 'XXX'), 592 (233, 'UY', 'Uruguay', 598, 'UYU'), 593 (234, 'UZ', 'Uzbekistan', 998, 'UZS'), 594 (235, 'VU', 'Vanuatu', 678, 'VUV'), 595 (236, 'VA', 'Vatican City State (Holy See)', 39, 'XXX'), 596 (237, 'VE', 'Venezuela', 58, 'VEF'), 597 (238, 'VN', 'Vietnam', 84, 'VND'), 598 (239, 'VG', 'Virgin Islands (British)', 1284, 'XXX'), 599 (240, 'VI', 'Virgin Islands (US)', 1340, 'XXX'), 600 (241, 'WF', 'Wallis And Futuna Islands', 681, 'XXX'), 601 (242, 'EH', 'Western Sahara', 212, 'XXX'), 602 (243, 'YE', 'Yemen', 967, 'YER'), 603 (244, 'YU', 'Yugoslavia', 38, 'XXX'), 604 (245, 'ZM', 'Zambia', 260, 'ZMW'), 605 (246, 'ZW', 'Zimbabwe', 263, 'BWP'); 606 607 608 CREATE TABLE user_account 609 ( 610 id BIGSERIAL, 611 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 612 created_by TEXT NOT NULL, 613 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 614 updated_by TEXT NOT NULL, 615 version INTEGER NOT NULL DEFAULT 1, 616 first_name TEXT NOT NULL, 617 last_name TEXT NOT NULL, 618 email TEXT NOT NULL, 619 phone_number TEXT NULL, 620 account_type INTEGER NULL, 621 active BOOLEAN NOT NULL DEFAULT FALSE, 622 expires_at TIMESTAMP WITH TIME ZONE NULL, 623 CONSTRAINT pk_user_account PRIMARY KEY (id) 624 ); 625 626 CREATE UNIQUE INDEX uk_user_account__email ON user_account (lower(email)); 627 CREATE UNIQUE INDEX uk_user_account__phone_number ON user_account (lower(phone_number)); 628 629 SELECT auto_manage_updated_at_and_version('user_account'); 630 631 632 CREATE TABLE user_credential 633 ( 634 id BIGINT, 635 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp, 636 version INTEGER NOT NULL DEFAULT 1, 637 password_hash TEXT, 638 expires_at TIMESTAMP WITH TIME ZONE, 639 invalid_attempts INT NOT NULL DEFAULT 0, 640 locked BOOLEAN NOT NULL DEFAULT FALSE, 641 activation_key TEXT, 642 activation_key_expires_at TIMESTAMP WITH TIME ZONE, 643 activated BOOLEAN NOT NULL DEFAULT FALSE, 644 reset_key TEXT, 645 reset_key_expires_at TIMESTAMP WITH TIME ZONE, 646 reset_at TIMESTAMP WITH TIME ZONE, 647 CONSTRAINT pk_user_credential PRIMARY KEY (id), 648 CONSTRAINT fk_user_credential_01 FOREIGN KEY (id) REFERENCES user_account (id) 649 ); 650 651 SELECT auto_manage_updated_at_and_version('user_credential'); 652 653 CREATE TABLE auth_token 654 ( 655 id BIGSERIAL NOT NULL, 656 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp, 657 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT current_timestamp, 658 user_id BIGINT NOT NULL REFERENCES user_account (id), 659 token TEXT NOT NULL UNIQUE, 660 expires_at TIMESTAMP WITH TIME ZONE NOT NULL, 661 mobile BOOLEAN NOT NULL DEFAULT FALSE, 662 identifier TEXT, 663 CONSTRAINT pk_auth_token PRIMARY KEY (id) 664 ); 665 666 CREATE TABLE role 667 ( 668 id SERIAL PRIMARY KEY, 669 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 670 created_by TEXT NOT NULL, 671 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 672 updated_by TEXT NOT NULL, 673 version INTEGER NOT NULL DEFAULT 1, 674 name TEXT NOT NULL, 675 description TEXT NOT NULL 676 ); 677 CREATE UNIQUE INDEX role_uk_01 ON role (lower(name)); 678 SELECT auto_manage_updated_at_and_version('role'); 679 680 681 CREATE TABLE permission 682 ( 683 id SERIAL PRIMARY KEY, 684 resource TEXT NOT NULL, 685 authority TEXT NOT NULL, 686 description TEXT NOT NULL 687 ); 688 689 CREATE UNIQUE INDEX permission_uk_01 ON permission (lower(resource), lower(authority)); 690 691 692 CREATE TABLE role_permission 693 ( 694 role_id INTEGER NOT NULL, 695 permission_id INTEGER NOT NULL, 696 CONSTRAINT role_permissions_pk PRIMARY KEY (role_id, permission_id), 697 CONSTRAINT role_permission_fk_01 FOREIGN KEY (role_id) REFERENCES role (id), 698 CONSTRAINT role_permission_fk_02 FOREIGN KEY (permission_id) REFERENCES permission (id) 699 ); 700 701 702 CREATE TABLE user_role 703 ( 704 user_id BIGINT NOT NULL, 705 role_id INTEGER NOT NULL, 706 CONSTRAINT user_role_pk PRIMARY KEY (user_id, role_id), 707 CONSTRAINT user_role_fk_01 FOREIGN KEY (role_id) REFERENCES role (id), 708 CONSTRAINT user_role_fk_02 FOREIGN KEY (user_id) REFERENCES user_account (id) 709 ); 710 711 CREATE TABLE user_group 712 ( 713 id SERIAL PRIMARY KEY, 714 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 715 created_by TEXT NOT NULL, 716 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 717 updated_by TEXT NOT NULL, 718 version INTEGER NOT NULL DEFAULT 1, 719 name TEXT NOT NULL, 720 description TEXT NOT NULL 721 ); 722 723 CREATE UNIQUE INDEX user_group_uk ON user_group (lower(name)); 724 725 CREATE TABLE user_group_user 726 ( 727 group_id INTEGER NOT NULL, 728 user_id BIGINT NOT NULL, 729 CONSTRAINT user_group_user_pk PRIMARY KEY (group_id, user_id), 730 CONSTRAINT user_group_user_fk_01 FOREIGN KEY (group_id) REFERENCES user_group (id), 731 CONSTRAINT user_group_user_fk_02 FOREIGN KEY (user_id) REFERENCES user_account (id) 732 ); 733 734 CREATE TABLE user_group_role 735 ( 736 group_id INTEGER NOT NULL, 737 role_id INTEGER NOT NULL, 738 CONSTRAINT user_group_role_pk PRIMARY KEY (group_id, role_id), 739 CONSTRAINT user_group_role_fk_01 FOREIGN KEY (group_id) REFERENCES user_group (id), 740 CONSTRAINT user_group_role_fk_02 FOREIGN KEY (role_id) REFERENCES role (id) 741 ); 742 743