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")\
.build()
GET_FROM_USER = Select()\
.fields(ID)\
.table(POLL).join(POLL_USER, on=Condition(OWNER, EQUAL, POLL_USER_ID))\
.where(
MultipleCondition(
AND,
Condition(POLL_USER_USER_ID, EQUAL, ":user_id"),
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)\
.where(Condition(POLL_ID, EQUAL, ":poll"))\
.order_by(NUMBER, DESC)\
.limit(1)\
.build()
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)\
.table(POLL_PUBLICATION)\
.where(Condition(POLL_ID, EQUAL, ":poll"))\
.order_by(NUMBER, DESC)\
.limit(1)\
.build()
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)\
.values(":id", ":text")\
.build()
# 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)
POLL_USER.constraint(Unique(POLL_USER_USER_ID, USERNAME, NAME))
ADD_USER = Insert().or_(IGNORE)\
.table(USER)\
.columns(ID)\
.values(":id")\
.build()
ADD_POLL_USER = Insert().or_(IGNORE)\
.table(POLL_USER)\
.columns(POLL_USER_USER_ID, USERNAME, NAME)\
.values(":user_id", ":username", ":name")\
.build()
GET_POLL_USER_ID = Select()\
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")\
.build()
GET_LAST_NUMBER_FOR_POLL = Select()\
.fields(NUMBER)\
.table(POLL_VOTE_OPTION).join(POLL_PUBLICATION, on=Condition(PUBLICATION, EQUAL, PUBLICATION_ID))\
.where(Condition(POLL_ID, EQUAL,
Select()