schema.sql

-- Create tables

CREATE TABLE "community_type" (
  "id" serial2 PRIMARY KEY,
  "name" text NOT NULL UNIQUE
);

CREATE TABLE "community" (
  "vkid" int4 PRIMARY KEY,
  "deactivated" boolean NOT NULL,
  "type" int2 NOT NULL REFERENCES "community_type"("id"),
  "name" text NOT NULL,
  "description" text NOT NULL,
  "members" int4 CHECK("members" >= 0),
  "status" text NOT NULL,
  "verified" boolean,
  "site" text NOT NULL,
  "age_limit" int2 CHECK("age_limit" IN (0, 16, 18)),
  "updated" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "audience_updated" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "age_range" (
  "id" serial2 PRIMARY KEY,
  "name" text NOT NULL UNIQUE
);

CREATE TABLE "country" (
  "vkid" int2 PRIMARY KEY, -- unknown: -1
  "name" text NOT NULL UNIQUE
);

CREATE TABLE "application" (
  "id" serial2 PRIMARY KEY,
  "name" text NOT NULL UNIQUE
);

CREATE TABLE "profile" (
  "id" serial4 PRIMARY KEY,
  "sex_vkid" int2 NOT NULL CHECK("sex_vkid" IN (0, 1, 2)), -- 0: unknown, 1: female, 2: male (according to VK API)
  "age_range_id" int2 NOT NULL REFERENCES "age_range"("id"),
  "country_vkid" int2 NOT NULL REFERENCES "country"("vkid"),
  "app_id" int2 NOT NULL REFERENCES "application"("id"),
  UNIQUE ("sex_vkid", "age_range_id", "country_vkid", "app_id")
);

CREATE TABLE "audience" (
  "community_vkid" int4 NOT NULL REFERENCES "community"("vkid"),
  "profile_id" int4 NOT NULL REFERENCES "profile"("id"),
  "count" int4 NOT NULL CHECK("count" > 0),
  PRIMARY KEY ("community_vkid", "profile_id")
);

CREATE TABLE "account" (
  "token" text PRIMARY KEY,
  "active" boolean NOT NULL DEFAULT TRUE
);


-- Triggers
-- update "community.updated" when updating
CREATE FUNCTION tgf_community_updated() RETURNS TRIGGER AS $$
BEGIN
  NEW."updated" := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tg_community_updated BEFORE UPDATE ON "community"
FOR EACH ROW EXECUTE PROCEDURE tgf_community_updated();

-- update "community.audience_updated" when inserting into "audience"
CREATE FUNCTION tgf_community_audience_updated() RETURNS TRIGGER AS $$
DECLARE var_community_vkid int4;
BEGIN
  SELECT "community_vkid" INTO var_community_vkid FROM new_table LIMIT 1;
  UPDATE "community" SET "audience_updated"=CURRENT_TIMESTAMP WHERE "vkid"=var_community_vkid;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tg_community_audience_updated AFTER INSERT ON "audience"
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE tgf_community_audience_updated();

-- Indexes


-- Initialize tables

INSERT INTO "community_type" ("name")
VALUES ('PUBLIC_PAGE'),
       ('OPEN_GROUP'),
       ('CLOSED_GROUP'),
       ('PRIVATE_GROUP');

INSERT INTO "age_range" ("name")
VALUES ('UNKNOWN'),
       ('-14'),
       ('15-17'),
       ('18-21'),
       ('22-25'),
       ('26-29'),
       ('30-34'),
       ('35-39'),
       ('40-44'),
       ('45-49'),
       ('50-59'),
       ('60+');

INSERT INTO "application" ("name")
VALUES ('UNKNOWN'),
       ('IOS'),
       ('ANDROID'),
       ('MOBILE'),
       ('BROWSER');

INSERT INTO "country" ("vkid", "name")
VALUES (-1, 'UNKNOWN'),
       (19, 'Австралия'),
       (20, 'Австрия'),
       (5, 'Азербайджан'),
       (21, 'Албания'),
       (22, 'Алжир'),
       (23, 'Американское Самоа'),
       (24, 'Ангилья'),
       (25, 'Ангола'),
       (26, 'Андорра'),
       (27, 'Антигуа и Барбуда'),
       (28, 'Аргентина'),
       (6, 'Армения'),
       (29, 'Аруба'),
       (30, 'Афганистан'),
       (31, 'Багамы'),
       (32, 'Бангладеш'),
       (33, 'Барбадос'),
       (34, 'Бахрейн'),
       (3, 'Беларусь'),
       (35, 'Белиз'),
       (36, 'Бельгия'),
       (37, 'Бенин'),
       (38, 'Бермуды'),
       (39, 'Болгария'),
       (40, 'Боливия'),
       (235, 'Бонайре, Синт-Эстатиус и Саба'),
       (41, 'Босния и Герцеговина'),
       (42, 'Ботсвана'),
       (43, 'Бразилия'),
       (44, 'Бруней-Даруссалам'),
       (45, 'Буркина-Фасо'),
       (46, 'Бурунди'),
       (47, 'Бутан'),
       (48, 'Вануату'),
       (233, 'Ватикан'),
       (49, 'Великобритания'),
       (50, 'Венгрия'),
       (51, 'Венесуэла'),
       (52, 'Виргинские острова, Британские'),
       (53, 'Виргинские острова, США'),
       (54, 'Восточный Тимор'),
       (55, 'Вьетнам'),
       (56, 'Габон'),
       (57, 'Гаити'),
       (58, 'Гайана'),
       (59, 'Гамбия'),
       (60, 'Гана'),
       (61, 'Гваделупа'),
       (62, 'Гватемала'),
       (63, 'Гвинея'),
       (64, 'Гвинея-Бисау'),
       (65, 'Германия'),
       (236, 'Гернси'),
       (66, 'Гибралтар'),
       (67, 'Гондурас'),
       (68, 'Гонконг'),
       (69, 'Гренада'),
       (70, 'Гренландия'),
       (71, 'Греция'),
       (7, 'Грузия'),
       (72, 'Гуам'),
       (73, 'Дания'),
       (237, 'Джерси'),
       (231, 'Джибути'),
       (74, 'Доминика'),
       (75, 'Доминиканская Республика'),
       (76, 'Египет'),
       (77, 'Замбия'),
       (78, 'Западная Сахара'),
       (79, 'Зимбабве'),
       (8, 'Израиль'),
       (80, 'Индия'),
       (81, 'Индонезия'),
       (82, 'Иордания'),
       (83, 'Ирак'),
       (84, 'Иран'),
       (85, 'Ирландия'),
       (86, 'Исландия'),
       (87, 'Испания'),
       (88, 'Италия'),
       (89, 'Йемен'),
       (90, 'Кабо-Верде'),
       (4, 'Казахстан'),
       (91, 'Камбоджа'),
       (92, 'Камерун'),
       (10, 'Канада'),
       (93, 'Катар'),
       (94, 'Кения'),
       (95, 'Кипр'),
       (96, 'Кирибати'),
       (97, 'Китай'),
       (98, 'Колумбия'),
       (99, 'Коморы'),
       (100, 'Конго'),
       (101, 'Конго, демократическая республика'),
       (102, 'Коста-Рика'),
       (103, 'Кот д`Ивуар'),
       (104, 'Куба'),
       (105, 'Кувейт'),
       (11, 'Кыргызстан'),
       (138, 'Кюрасао'),
       (106, 'Лаос'),
       (12, 'Латвия'),
       (107, 'Лесото'),
       (108, 'Либерия'),
       (109, 'Ливан'),
       (110, 'Ливия'),
       (13, 'Литва'),
       (111, 'Лихтенштейн'),
       (112, 'Люксембург'),
       (113, 'Маврикий'),
       (114, 'Мавритания'),
       (115, 'Мадагаскар'),
       (116, 'Макао'),
       (117, 'Македония'),
       (118, 'Малави'),
       (119, 'Малайзия'),
       (120, 'Мали'),
       (121, 'Мальдивы'),
       (122, 'Мальта'),
       (123, 'Марокко'),
       (124, 'Мартиника'),
       (125, 'Маршалловы Острова'),
       (126, 'Мексика'),
       (127, 'Микронезия, федеративные штаты'),
       (128, 'Мозамбик'),
       (15, 'Молдова'),
       (129, 'Монако'),
       (130, 'Монголия'),
       (131, 'Монтсеррат'),
       (132, 'Мьянма'),
       (133, 'Намибия'),
       (134, 'Науру'),
       (135, 'Непал'),
       (136, 'Нигер'),
       (137, 'Нигерия'),
       (139, 'Нидерланды'),
       (140, 'Никарагуа'),
       (141, 'Ниуэ'),
       (142, 'Новая Зеландия'),
       (143, 'Новая Каледония'),
       (144, 'Норвегия'),
       (145, 'Объединенные Арабские Эмираты'),
       (146, 'Оман'),
       (147, 'Остров Мэн'),
       (148, 'Остров Норфолк'),
       (149, 'Острова Кайман'),
       (150, 'Острова Кука'),
       (151, 'Острова Теркс и Кайкос'),
       (152, 'Пакистан'),
       (153, 'Палау'),
       (154, 'Палестинская автономия'),
       (155, 'Панама'),
       (156, 'Папуа — Новая Гвинея'),
       (157, 'Парагвай'),
       (158, 'Перу'),
       (159, 'Питкерн'),
       (160, 'Польша'),
       (161, 'Португалия'),
       (162, 'Пуэрто-Рико'),
       (163, 'Реюньон'),
       (1, 'Россия'),
       (164, 'Руанда'),
       (165, 'Румыния'),
       (9, 'США'),
       (166, 'Сальвадор'),
       (167, 'Самоа'),
       (168, 'Сан-Марино'),
       (169, 'Сан-Томе и Принсипи'),
       (170, 'Саудовская Аравия'),
       (171, 'Свазиленд'),
       (172, 'Святая Елена'),
       (173, 'Северная Корея'),
       (174, 'Северные Марианские острова'),
       (175, 'Сейшелы'),
       (176, 'Сенегал'),
       (177, 'Сент-Винсент'),
       (178, 'Сент-Китс и Невис'),
       (179, 'Сент-Люсия'),
       (180, 'Сент-Пьер и Микелон'),
       (181, 'Сербия'),
       (182, 'Сингапур'),
       (234, 'Синт-Мартен'),
       (183, 'Сирийская Арабская Республика'),
       (184, 'Словакия'),
       (185, 'Словения'),
       (186, 'Соломоновы Острова'),
       (187, 'Сомали'),
       (188, 'Судан'),
       (189, 'Суринам'),
       (190, 'Сьерра-Леоне'),
       (16, 'Таджикистан'),
       (191, 'Таиланд'),
       (192, 'Тайвань'),
       (193, 'Танзания'),
       (194, 'Того'),
       (195, 'Токелау'),
       (196, 'Тонга'),
       (197, 'Тринидад и Тобаго'),
       (198, 'Тувалу'),
       (199, 'Тунис'),
       (17, 'Туркменистан'),
       (200, 'Турция'),
       (201, 'Уганда'),
       (18, 'Узбекистан'),
       (2, 'Украина'),
       (202, 'Уоллис и Футуна'),
       (203, 'Уругвай'),
       (204, 'Фарерские острова'),
       (205, 'Фиджи'),
       (206, 'Филиппины'),
       (207, 'Финляндия'),
       (208, 'Фолклендские острова'),
       (209, 'Франция'),
       (210, 'Французская Гвиана'),
       (211, 'Французская Полинезия'),
       (212, 'Хорватия'),
       (213, 'Центрально-Африканская Республика'),
       (214, 'Чад'),
       (230, 'Черногория'),
       (215, 'Чехия'),
       (216, 'Чили'),
       (217, 'Швейцария'),
       (218, 'Швеция'),
       (219, 'Шпицберген и Ян Майен'),
       (220, 'Шри-Ланка'),
       (221, 'Эквадор'),
       (222, 'Экваториальная Гвинея'),
       (223, 'Эритрея'),
       (14, 'Эстония'),
       (224, 'Эфиопия'),
       (226, 'Южная Корея'),
       (227, 'Южно-Африканская Республика'),
       (232, 'Южный Судан'),
       (228, 'Ямайка'),
       (229, 'Япония');