Работа с внутренней базой данных¶
Примеры работы¶
Список ключей:
sqlite> select * from key;
1|2019-11-25 10:29:55.476690|2019-11-25 10:29:55.476753|6257|pin|1||5392
Показать журнал проездов:
sqlite> select * from entry;
2019-11-25 10:52:28.818129|2019-11-25 10:52:29.388287|1|d4c241a6-f07f-492b-b57e-98f4eb7e71df||1234|pin|number_not_found||||1|1127748
2019-11-25 10:52:53.531720|2019-11-25 10:52:53.860082|2|515796c3-49a9-419b-8be1-2c4df0b4a209||9635|pin|number_not_found||||1|1127753
2019-11-25 10:52:57.264143|2019-11-25 10:52:57.618184|3|52d96b1f-fc8c-4a70-bd92-da8b505e0fd2||7845|pin|number_not_found||||1|1127754
2019-11-25 10:53:01.606338|2019-11-25 10:53:01.959493|4|08bb5b55-99a6-46e6-8c6c-b67e8012b162||8874|pin|number_not_found||||1|1127756
2019-11-25 10:53:07.412017|2019-11-25 10:53:07.760756|5|93a46230-dec5-4bea-bc4e-c99ff1a4e5d7||9659|pin|number_not_found||||1|1127757
2019-11-25 10:53:14.341663|2019-11-25 10:53:14.630556|6|d3be5cd2-6a61-47bb-8be5-e22bc63fbcb8||9653|pin|number_not_found||||1|1127759
2019-11-25 10:53:18.898774|2019-11-25 10:53:19.255547|7|79cca544-c140-4cbd-b391-2a8d153882f0||0789|pin|number_not_found||||1|1127761
2019-11-25 10:55:35.858007|2019-11-25 10:55:36.579612|8|558b3d23-3b5f-4d67-8916-378bd37f574f|1|6257|pin|accepted||success|Opened|1|1127776
2019-11-25 11:11:35.000413|2019-11-25 11:11:35.622377|9|f418c243-d27c-4391-917a-1a2042edac91|1|6257|pin|accepted||success|Opened|1|1127855
Добавить ключ:
sqlite> INSERT INTO key (created_at, updated_at, number, key_type) VALUES(DATETIME(), DATETIME(), '1234', 'key');
sqlite>
Вывод схемы базы:
sqlite> .schema
CREATE TABLE alembic_version (
version_num VARCHAR(32) NOT NULL,
CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);
CREATE TABLE config (
id INTEGER NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
param VARCHAR NOT NULL,
value VARCHAR,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS "key" (
id INTEGER NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
number VARCHAR NOT NULL,
key_type VARCHAR NOT NULL,
is_enabled BOOLEAN,
expire_time DATETIME,
external_id INTEGER,
PRIMARY KEY (id),
UNIQUE (number, key_type),
CHECK (is_enabled IN (0, 1))
);
CREATE TABLE entry (
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
id INTEGER NOT NULL,
uid VARCHAR,
key_id INTEGER,
key_number VARCHAR,
key_type VARCHAR,
status VARCHAR,
cause VARCHAR,
open_status VARCHAR,
open_status_message VARCHAR,
external_sync BOOLEAN,
external_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(key_id) REFERENCES "key" (id),
UNIQUE (uid),
CHECK (external_sync IN (0, 1))
);
CREATE INDEX ix_entry_uid ON entry (uid);