def join(self,
table: Table,
on: EXPRESSION_TYPE = None,
using: Column = None,
full_cartesian: bool = False):
join = "join {table}".format(table=table.str()) # unsafe formatting
assert on is None or using is None, "both 'on' and 'using' cannot be specified at the same time"
if on is not None:
join += " on {on}".format(on=ExpressionParser.parse(on).str())
elif using is not None:
join += " using ({using})".format(using=using.name)
elif not full_cartesian:
raise Exception(
"Trying to create a join without adding 'on' nor 'using' clauses. "
"This results in a full cartesian product of both tables, "
"and that is probably not what you want to achieve. "
"To avoid it, set either an 'on' condition or a 'using' column. "
"If you really want to perform the full cartesian product, "
"set the 'full_cartesian' parameter to true.")
if self._not_none(self._join):
self._join += " " + join
else:
self._join = join
return self
TIMESTAMP = Column("timestamp", TEXT)
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)
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)\
from sqlite_framework.sql.item.constraint.column.simple import PRIMARY_KEY, NOT_NULL
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()
from poll.data.data_source.sqlite.model.poll.option import FullPollOptionData
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)\
def table(self, table: Table):
self._from = "from {table}".format(
table=table.str()) # unsafe formatting
return self
from sqlite_framework.sql.statement.builder.select import Select
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)
REPLY_TO_MESSAGE = Column("reply_to_message",
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.poll import Poll
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()\
from sqlite_framework.sql.item.expression.constants import CURRENT_UNIX_TIMESTAMP
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)\
from sqlite_framework.sql.statement.builder.insert import Insert
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)
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)\
def table(self, table: Table):
self._table = table.str()
return self