SQLize Online / PHPize Online  /  SQLtest Online

BEGIN; CREATE TABLE IF NOT EXISTS public."Account" ( "ID" serial NOT NULL, "BankID" integer, "CurrencyID" integer NOT NULL, "Name" character varying NOT NULL, "Number" character varying, "Comment" text, PRIMARY KEY ("ID") ); CREATE TABLE IF NOT EXISTS public."Bank" ( "ID" serial NOT NULL, "Name" character varying NOT NULL, "Comment" text, PRIMARY KEY ("ID"), UNIQUE ("Name") ); CREATE TABLE IF NOT EXISTS public."Operation" ( "ID" serial NOT NULL, "AccountID" integer NOT NULL, "ClientID" integer, "DateTime" timestamp(0) with time zone NOT NULL DEFAULT now(), "CategoryID" integer, "Sum" bigint, "Comment" text, "LinkedID" integer, PRIMARY KEY ("ID") ); CREATE TABLE IF NOT EXISTS public."Client" ( "ID" serial NOT NULL, "Name" character varying NOT NULL, "Location" character varying, "Comment" text, PRIMARY KEY ("ID") ); CREATE TABLE IF NOT EXISTS public."Category" ( "ID" serial NOT NULL, "Name" character varying NOT NULL, "Comment" text, "ParentID" integer, PRIMARY KEY ("ID") ); CREATE TABLE IF NOT EXISTS public."Currency" ( "ID" serial NOT NULL, "AlphaCode" character varying(3) NOT NULL, "NumCode" character varying(3), "Name" character varying NOT NULL, "IsPrimary" boolean, "Digits" smallint, "Multiplier" bigint, PRIMARY KEY ("ID"), UNIQUE ("AlphaCode"), UNIQUE ("NumCode"), UNIQUE ("Name") ); CREATE TABLE IF NOT EXISTS public."Rate" ( "CurrencyID" integer NOT NULL, "Date" date NOT NULL DEFAULT now(), "Units" integer NOT NULL DEFAULT 1, "Cost" bigint NOT NULL, PRIMARY KEY ("CurrencyID", "Date") ); CREATE TABLE IF NOT EXISTS public."Label" ( "ID" serial NOT NULL, "Name" character varying NOT NULL, "Comment" text, PRIMARY KEY ("ID"), UNIQUE ("Name") ); CREATE TABLE IF NOT EXISTS public."OperationLabel" ( "OperationID" integer NOT NULL, "LabelID" integer NOT NULL, UNIQUE ("OperationID", "LabelID") ); ALTER TABLE IF EXISTS public."Account" ADD FOREIGN KEY ("CurrencyID") REFERENCES public."Currency" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."Account" ADD FOREIGN KEY ("BankID") REFERENCES public."Bank" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."Operation" ADD FOREIGN KEY ("AccountID") REFERENCES public."Account" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."Operation" ADD FOREIGN KEY ("ClientID") REFERENCES public."Client" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."Operation" ADD FOREIGN KEY ("CategoryID") REFERENCES public."Category" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."Operation" ADD FOREIGN KEY ("LinkedID") REFERENCES public."Operation" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE NOT VALID; ALTER TABLE IF EXISTS public."Category" ADD FOREIGN KEY ("ParentID") REFERENCES public."Category" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE NOT VALID; ALTER TABLE IF EXISTS public."Rate" ADD FOREIGN KEY ("CurrencyID") REFERENCES public."Currency" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."OperationLabel" ADD FOREIGN KEY ("OperationID") REFERENCES public."Operation" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; ALTER TABLE IF EXISTS public."OperationLabel" ADD FOREIGN KEY ("LabelID") REFERENCES public."Label" ("ID") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID; END; SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; -- -- Data for Name: Bank; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Bank" VALUES (0, '*', NULL), (1, 'Сбербанк', NULL), (2, 'Альфа-Банк', NULL), (3, 'ВТБ', NULL), (4, 'Тинькофф Банк', ''); -- -- Data for Name: Currency; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Currency" VALUES (0, 'XTS', '963', 'Код для тестирования', NULL, NULL, NULL), (1, 'RUB', '643', 'Российский рубль', true, 2, 100), (2, 'USD', '840', 'Доллар США', NULL, 2, 100), (3, 'EUR', '978', 'Евро', NULL, 2, 100), (4, 'JPY', '392', 'Японская иена', NULL, 0, 1), (5, 'TND', '788', 'Тунисский динар', NULL, 3, 1000), (6, 'BTC', NULL, 'Bitcoin', NULL, 8, 100000000), (7, 'AUD', '036', 'Австралийский доллар', NULL, 2, 100), (8, 'XAU', '959', 'Тройская унция золота', NULL, NULL, NULL), (9, 'XXX', '999', 'Код для неденежных транзакций', NULL, NULL, NULL), (10, 'XMR', NULL, 'Monero', NULL, 12, 1000000000000); -- -- Data for Name: Account; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Account" VALUES (1, NULL, 1, 'Наличные рубли', NULL, NULL), (2, NULL, 2, 'Наличные доллары', NULL, NULL), (3, NULL, 3, 'Наличные евро', NULL, NULL), (4, NULL, 4, 'Наличные иены', NULL, NULL), (5, NULL, 5, 'Наличные динары', NULL, NULL), (6, 2, 1, 'Зарплатная карта', '2200567890120006', NULL), (7, 3, 1, 'Кредитная карта МИР', '2200123412340007', NULL), (8, 1, 2, 'Виртуальная карта в USD', '4817000012340008', NULL), (9, 1, 3, 'Виртуальная карта в EUR', '4817000012340009', NULL), (10, 2, 1, 'Вклады в рублях (Альфа-Банк)', NULL, NULL), (11, 1, 2, 'Вклады в USD', NULL, NULL), (12, 3, 1, 'Мультикарта', '2200123412340012', NULL), (13, 1, 1, 'СберКарта', '2200123412340013', NULL); -- -- Data for Name: Category; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Category" VALUES (1, 'Перевод средств', NULL, NULL), (2, 'Доходы', NULL, NULL), (3, 'Расходы', NULL, NULL), (4, 'Основной доход', NULL, 2), (5, 'Проценты по вкладам', NULL, 2), (6, 'Пропитание', NULL, 3), (7, 'Транспорт', NULL, 3), (8, 'Жильё', NULL, 3), (9, 'Развлечения', NULL, 3), (10, 'Здоровье', NULL, 3), (11, 'Проценты по кредиту', NULL, 3), (12, 'Банковское обслуживание', NULL, 3), (13, 'Долги', NULL, NULL), (14, 'Мы должны', NULL, 13), (15, 'Нам должны', NULL, 13), (16, 'Коммуникации', NULL, 3); -- -- Data for Name: Client; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Client" VALUES (0, '*', NULL, NULL), (1, '"Пятёрочка"', 'г.Москва, ул.Иванова, 1А', NULL), (2, '"Магнит"', 'г.Москва, ул.Магнитная, 2Б', NULL), (3, '"Жилкомсервис"', NULL, NULL), (4, '"Яндекс Такси"', '', NULL), (5, '"Домовой"', 'г.Домодедово, ул.Строительная, 5Е', NULL), (7, '"Пятёрочка"', 'г.Химки, ул.Победная, 6, корп.2', NULL), (8, '"Лента"', 'г.Зеленоград, просп.Строителей, 8, лит.А', NULL), (9, '"Магнит-Аптека"', 'г.Королёв, бульв.Науки, 9И', NULL), (10, '"Пятёрочка"', 'г.Москва, Центральное шоссе, 10, корп.3', NULL), (11, '"Ростелеком"', NULL, NULL); -- -- Data for Name: Label; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Label" VALUES (1, 'Успех', NULL); -- -- Data for Name: Operation; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Operation" VALUES (1, 1, NULL, '2022-01-01 00:00:00+03', NULL, 1000000, 'Остаток с 2021 года (10 тыс.руб.)', NULL), (2, 1, NULL, '2022-01-05 12:00:00+03', 1, -80000, 'Покупка 10 долларов по курсу 80 руб./дол.', NULL), (3, 2, NULL, '2022-01-05 12:00:00+03', 1, 1000, NULL, 2), (4, 6, NULL, '2022-01-10 14:00:00+03', 4, 5000000, 'Аванс за январь 2022', NULL), (5, 6, NULL, '2022-01-10 16:15:00+03', 1, -2500000, 'Открытие вклада в Альфа-Банке', NULL), (6, 10, NULL, '2022-01-10 16:15:00+03', 1, 2500000, NULL, 5), (7, 6, 3, '2022-01-11 12:00:00+03', 8, -1000000, 'Коммунальные платежи за январь 2022', NULL), (8, 7, 1, '2022-01-11 13:30:00+03', 6, -128050, 'Покупка продуктов', NULL), (9, 7, 4, '2022-01-11 14:00:00+03', 7, -50000, 'Такси из магазина', NULL), (10, 2, NULL, '2022-01-12 16:00:00+03', 1, -1000, 'Пополнение карты', NULL), (11, 8, NULL, '2022-01-12 16:00:00+03', 1, 1000, NULL, 10), (12, 8, NULL, '2022-01-12 17:20:00+03', 9, -500, 'Оплата муз. подписки за январь', NULL), (13, 7, 5, '2022-01-13 15:40:00+03', 8, -1000000, 'Новый диван', NULL), (14, 1, NULL, '2022-01-14 18:40:00+03', 8, -100000, 'Доставка дивана', NULL), (15, 1, NULL, '2022-01-16 19:00:00+03', 8, 200000, 'Продажа старого дивана на Avito', NULL), (16, 7, 2, '2022-01-17 15:50:00+03', 6, -204850, NULL, NULL), (18, 1, NULL, '2022-01-18 14:40:00+03', 10, -500000, 'Лечение зубов', NULL), (19, 1, NULL, '2022-01-19 12:00:00+03', 15, -100000, 'В долг другу до конца месяца', NULL), (20, 7, 10, '2022-01-19 13:00:13+03', 6, -300000, NULL, NULL), (21, 7, NULL, '2022-01-19 14:41:00+03', 7, -40000, 'Пополнение проездного', NULL), (22, 7, NULL, '2022-01-20 13:49:00+03', 16, -45000, 'Пополнение телефона', NULL), (23, 6, NULL, '2022-01-25 12:00:00+03', 4, 10000000, 'Зарплата за январь 2022', NULL), (24, 6, NULL, '2022-01-25 13:13:13+03', 1, -5000000, 'Перевод 50 тыс. в Сбербанк', NULL), (25, 13, NULL, '2022-01-25 13:15:15+03', 1, 5000000, NULL, 24), (26, 6, NULL, '2022-01-25 13:13:13+03', 12, -25000, '0.5% за перевод 50 тыс. в Сбербанк', NULL), (27, 13, NULL, '2022-01-25 14:00:00+03', 1, -3868245, 'Покупка $500 по курсу ЦБ 77,3649 руб./дол.', NULL), (28, 8, NULL, '2022-01-25 14:00:00+03', 1, 50000, NULL, 27), (29, 8, NULL, '2022-01-25 14:24:00+03', 1, -50000, 'Вклад в USD на 3 мес. под 6% годовых', NULL), (30, 11, NULL, '2022-01-25 14:24:00+03', 1, 50000, NULL, 29), (31, 13, 11, '2022-01-26 11:27:00+03', 16, -50000, 'Оплата доступа в Интернет на февраль', NULL), (32, 13, NULL, '2022-01-31 00:00:00+03', 12, -15000, 'Обслуживание карты в январе', NULL); -- -- Data for Name: OperationLabel; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."OperationLabel" VALUES (2, 1); -- -- Data for Name: Rate; Type: TABLE DATA; Schema: public; Owner: - -- INSERT INTO public."Rate" VALUES (2, '2021-12-31', 10000, 74292600), (3, '2021-12-31', 10000, 84069500), (4, '2021-12-31', 1000000, 64509700), (2, '2022-02-01', 10000, 77470200), (3, '2022-02-01', 10000, 86503200), (4, '2022-02-01', 1000000, 67152300), (2, '2022-03-01', 10000, 93558900), (3, '2022-03-01', 10000, 104477200), (4, '2022-03-01', 1000000, 80971800), (2, '2022-04-01', 10000, 83409700), (3, '2022-04-01', 10000, 92493000), (4, '2022-04-01', 1000000, 68534300), (2, '2022-05-01', 10000, 71023700), (3, '2022-05-01', 10000, 74558900), (4, '2022-05-01', 1000000, 54675700); SELECT * FROM public."Account"; -- -- Name: Account_ID_seq; Type: SEQUENCE SET; Schema: public; Owner: - -- SELECT pg_catalog.setval('public."Account_ID_seq"', 13, true); SELECT * FROM public."Account"; SELECT "NumCode" FROM public."Currency" WHERE "AlphaCode" = 'USD';