USER_ID = Column("user_id", INTEGER, "not null") TIME_POINT_QUERY = Column("time_point", TEXT, "not null") QUERY_TEXT = Column("query", TEXT) OFFSET = Column("offset", TEXT) LANGUAGE_CODE = Column("language_code", TEXT) LOCALE = Column("locale", TEXT) RESULTS_FOUND_LEN = Column("results_found_len", INTEGER) RESULTS_SENT_LEN = Column("results_sent_len", INTEGER) PROCESSING_SECONDS = Column("processing_seconds", REAL) TIME_POINT_CHOSEN_RESULT = Column("time_point", TEXT) CHOSEN_ZONE_NAME = Column("chosen_zone_name", TEXT) CHOOSING_SECONDS = Column("choosing_seconds", REAL) QUERY = Table("query") QUERY.column(TIMESTAMP) QUERY.column(USER_ID) QUERY.column(TIME_POINT_QUERY) QUERY.column(QUERY_TEXT) QUERY.column(OFFSET) QUERY.column(LANGUAGE_CODE, version=2) QUERY.column(LOCALE) QUERY.column(RESULTS_FOUND_LEN) QUERY.column(RESULTS_SENT_LEN) QUERY.column(PROCESSING_SECONDS) CHOSEN_RESULT = Table("chosen_result") CHOSEN_RESULT.column(TIMESTAMP) CHOSEN_RESULT.column(USER_ID) CHOSEN_RESULT.column(TIME_POINT_CHOSEN_RESULT) CHOSEN_RESULT.column(CHOSEN_ZONE_NAME)
from sqlite_framework.sql.item.expression.compound.condition import Condition from sqlite_framework.sql.item.table import Table from sqlite_framework.sql.statement.builder.insert import Insert from sqlite_framework.sql.statement.builder.select import Select NAME = "version_info" VERSION = 1 COMPONENT = Column("component", TEXT, PRIMARY_KEY, NOT_NULL) COMPONENT_VERSION = Column("version", INTEGER) VERSION_INFO = Table("version_info") VERSION_INFO.column(COMPONENT) VERSION_INFO.column(COMPONENT_VERSION) SET_VERSION = Insert().or_(REPLACE)\ .table(VERSION_INFO)\ .columns(COMPONENT, COMPONENT_VERSION)\ .values(":component", ":version")\ .build() GET_VERSION = Select()\ .fields(COMPONENT_VERSION)\ .table(VERSION_INFO)\ .where(Condition(COMPONENT, EQUAL, ":component"))\ .build()
NAME = "poll" VERSION = 1 ID = Column("poll_id", INTEGER, PRIMARY_KEY, NOT_NULL) # filled automatically OWNER = Column("owner", INTEGER, NOT_NULL, References(POLL_USER, on_delete=CASCADE)) NUMBER = Column("number", INTEGER, NOT_NULL) TYPE = Column("type", INTEGER, NOT_NULL) ANONYMITY = Column("anonymity", INTEGER, NOT_NULL) TITLE = Column("title", TEXT, NOT_NULL) TIMESTAMP = Column("created_at", INTEGER, NOT_NULL, Default(CURRENT_UNIX_TIMESTAMP)) COMPLETE = Column("complete", INTEGER, NOT_NULL, Default(0, is_expr=False)) POLL = Table("poll") POLL.column(ID) POLL.column(OWNER) POLL.column(NUMBER) POLL.column(TYPE) POLL.column(ANONYMITY) POLL.column(TITLE) POLL.column(TIMESTAMP) POLL.column(COMPLETE) POLL.constraint(Unique( OWNER, NUMBER)) # real constraint should be Unique(owner.user_id, number) ADD_POLL = Insert()\ .table(POLL)\ .columns(OWNER, NUMBER, TYPE, ANONYMITY, TITLE)\ .values(":owner", ":number", ":type", ":anonymity", ":title")\
INTEGER) # references: message_id column IS_EDIT = Column("is_edit", INTEGER) # boolean MESSAGE_TEXT = Column("text", TEXT) NEW_CHAT_MEMBER = Column("new_chat_member", INTEGER) # user_id LEFT_CHAT_MEMBER = Column("left_chat_member", INTEGER) # user_id GROUP_CHAT_CREATED = Column("group_chat_created", INTEGER) # boolean MIGRATE_TO_CHAT_ID = Column("migrate_to_chat_id", INTEGER) MIGRATE_FROM_CHAT_ID = Column("migrate_from_chat_id", INTEGER) MESSAGE_ID_COMMAND = Column("message_id", INTEGER) # references: message.id column COMMAND_TEXT = Column("command", TEXT) COMMAND_ARGS = Column("command_args", TEXT) MESSAGE = Table("message") MESSAGE.column(ID) MESSAGE.column(TIMESTAMP) MESSAGE.column(CHAT_ID) MESSAGE.column(MESSAGE_ID) MESSAGE.column(USER_ID) MESSAGE.column(DATE) MESSAGE.column(IS_FORWARD, version=2) MESSAGE.column(REPLY_TO_MESSAGE, version=2) MESSAGE.column(IS_EDIT, version=2) MESSAGE.column(MESSAGE_TEXT) MESSAGE.column(NEW_CHAT_MEMBER, version=2) MESSAGE.column(LEFT_CHAT_MEMBER, version=2) MESSAGE.column(GROUP_CHAT_CREATED, version=2) MESSAGE.column(MIGRATE_TO_CHAT_ID, version=2) MESSAGE.column(MIGRATE_FROM_CHAT_ID, version=2)
from poll.domain.model.poll.option import PollOptionInfo, PollOption, PollOptionNumber from poll.domain.model.poll.poll import Poll COMPONENT_NAME = "poll_option" COMPONENT_VERSION = 1 ID = Column("id", INTEGER, PRIMARY_KEY, NOT_NULL) # filled automatically POLL_ID = Column("poll", INTEGER, NOT_NULL, References(POLL, on_delete=CASCADE)) NUMBER = Column("number", INTEGER, NOT_NULL) NAME = Column("name", TEXT, NOT_NULL) POLL_OPTION = Table("poll_option") POLL_OPTION.column(ID) POLL_OPTION.column(POLL_ID) POLL_OPTION.column(NUMBER) POLL_OPTION.column(NAME) POLL_OPTION.constraint(Unique(POLL_ID, NUMBER)) ADD_OPTION = Insert()\ .table(POLL_OPTION)\ .columns(POLL_ID, NUMBER, NAME)\ .values(":poll", ":number", ":name")\ .build() GET_LAST_NUMBER_FOR_POLL = Select()\ .fields(NUMBER)\ .table(POLL_OPTION)\
NAME = "user" VERSION = 2 USER_ID = Column("user_id", INTEGER, "primary key", "not null") FIRST_NAME = Column("first_name", TEXT) LAST_NAME = Column("last_name", TEXT) USERNAME = Column("username", TEXT) LANGUAGE_CODE = Column("language_code", TEXT) IS_BOT = Column("is_bot", INTEGER) # boolean TIMESTAMP_ADDED = Column("timestamp_added", TEXT) USER_ID_USER_HISTORY = Column("user_id", INTEGER, "not null") TIMESTAMP_REMOVED = Column("timestamp_removed", TEXT) USER = Table("user") USER.column(USER_ID) USER.column(FIRST_NAME) USER.column(LAST_NAME) USER.column(USERNAME) USER.column(LANGUAGE_CODE) USER.column(IS_BOT, version=2) USER.column(TIMESTAMP_ADDED) USER_HISTORY = Table("user_history") USER_HISTORY.column(USER_ID_USER_HISTORY) USER_HISTORY.column(FIRST_NAME) USER_HISTORY.column(LAST_NAME) USER_HISTORY.column(USERNAME) USER_HISTORY.column(LANGUAGE_CODE) USER_HISTORY.column(IS_BOT, version=2) USER_HISTORY.column(TIMESTAMP_ADDED)
from poll.domain.model.poll.publication import PollPublication COMPONENT_NAME = "poll_publication" COMPONENT_VERSION = 1 PUBLICATION_ID = Column("publication_id", INTEGER, PRIMARY_KEY, NOT_NULL) # filled automatically PUBLICATION = Column("publication_value", TEXT, UNIQUE, NOT_NULL) POLL_ID = Column("poll", INTEGER, NOT_NULL, References(POLL, on_delete=CASCADE)) NUMBER = Column("number", INTEGER, NOT_NULL) TIMESTAMP = Column("published_at", INTEGER, NOT_NULL, Default(CURRENT_UNIX_TIMESTAMP)) POLL_PUBLICATION = Table("poll_publication") POLL_PUBLICATION.column(PUBLICATION_ID) POLL_PUBLICATION.column(PUBLICATION) POLL_PUBLICATION.column(POLL_ID) POLL_PUBLICATION.column(NUMBER) POLL_PUBLICATION.column(TIMESTAMP) POLL_PUBLICATION.constraint(Unique(POLL_ID, NUMBER)) ADD_PUBLICATION = Insert()\ .table(POLL_PUBLICATION)\ .columns(PUBLICATION, POLL_ID, NUMBER)\ .values(":publication", ":poll", ":number")\ .build() GET_LAST_NUMBER_FOR_POLL = Select()\ .fields(NUMBER)\
from sqlite_framework.sql.item.table import Table from sqlite_framework.sql.result.row import ResultRow from sqlite_framework.sql.statement.builder.delete import Delete from sqlite_framework.sql.statement.builder.insert import Insert from sqlite_framework.sql.statement.builder.select import Select NAME = "test" VERSION = 2 ID = Column("id", INTEGER, PRIMARY_KEY, NOT_NULL) TEXT_COLUMN = Column("text", TEXT) TEST = Table("test") TEST.column(ID) TEST.column(TEXT_COLUMN) TEST.constraint(Unique(TEXT_COLUMN)) OTHER_ID = Column("other_id", INTEGER, NOT_NULL, References(TEST, on_delete=CASCADE)) TIMESTAMP = Column("timestamp", INTEGER, Default(CURRENT_UNIX_TIMESTAMP)) TEST2 = Table("test2") TEST2.column(OTHER_ID) TEST2.column(TIMESTAMP, version=2) ADD_TEST = Insert().or_(REPLACE)\ .table(TEST)\ .columns(ID, TEXT_COLUMN)\
from sqlite_framework.sql.statement.builder.select import Select from poll.data.data_source.sqlite.component.base import BasePollSqliteStorageComponent from poll.data.data_source.sqlite.model.poll.user import PollUserData from poll.domain.model.poll.user import PollUser from poll.domain.model.user.user import User COMPONENT_NAME = "user" COMPONENT_VERSION = 1 # USER table definition ID = Column("user_id", INTEGER, PRIMARY_KEY, NOT_NULL) USER = Table("user") USER.column(ID) # POLL_USER table definition POLL_USER_ID = Column("poll_user_id", INTEGER, PRIMARY_KEY, NOT_NULL) # to be filled automatically POLL_USER_USER_ID = Column("user_id", INTEGER, NOT_NULL, References(USER, on_delete=CASCADE)) USERNAME = Column("username", TEXT, NOT_NULL) NAME = Column("name", TEXT, NOT_NULL) POLL_USER = Table("poll_user") POLL_USER.column(POLL_USER_ID) POLL_USER.column(POLL_USER_USER_ID) POLL_USER.column(USERNAME) POLL_USER.column(NAME)
COMPONENT_NAME = "poll_vote_option" COMPONENT_VERSION = 1 ID = Column("id", INTEGER, PRIMARY_KEY, NOT_NULL) # filled automatically USER = Column("user", INTEGER, NOT_NULL, References(POLL_USER, on_delete=CASCADE)) PUBLICATION = Column("publication", INTEGER, NOT_NULL, References(POLL_PUBLICATION, on_delete=CASCADE)) OPTION = Column("option", INTEGER, NOT_NULL, References(POLL_OPTION, on_delete=CASCADE)) NUMBER = Column("poll_vote_number", INTEGER, NOT_NULL) TIMESTAMP = Column("timestamp", INTEGER, NOT_NULL, Default(CURRENT_UNIX_TIMESTAMP)) POLL_VOTE_OPTION = Table("poll_vote_option") POLL_VOTE_OPTION.column(ID) POLL_VOTE_OPTION.column(USER) POLL_VOTE_OPTION.column(PUBLICATION) POLL_VOTE_OPTION.column(OPTION) POLL_VOTE_OPTION.column(NUMBER) POLL_VOTE_OPTION.column(TIMESTAMP) # real constraint should be Unique(user.user_id, publication, option) POLL_VOTE_OPTION.constraint(Unique(USER, PUBLICATION, OPTION)) # real constraint should be Unique(poll, number) POLL_VOTE_OPTION.constraint(Unique(PUBLICATION, NUMBER)) ADD_VOTE = Insert()\ .table(POLL_VOTE_OPTION)\ .columns(USER, PUBLICATION, OPTION, NUMBER)\ .values(":user", ":publication", ":option", ":number")\