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()