def test_multiple(self):
self.insert_row()
query = (
Manager.alter()
.add_column("column_a", Integer(default=0, null=True))
.add_column("column_b", Integer(default=0, null=True))
)
query.run_sync()
response = Band.raw("SELECT * FROM manager").run_sync()
column_names = response[0].keys()
self.assertTrue("column_a" in column_names)
self.assertTrue("column_b" in column_names)
class Ad(Table):
title = Varchar(length=255)
slug = Varchar(length=255)
content = Text()
created = Timestamp()
view = Integer(default=0)
price = Integer()
room = Integer()
visitor = Integer()
address = Varchar(length=255)
city = Varchar(length=255)
ad_user = ForeignKey(references=User)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.title])
class Movie(Table):
name = Varchar(length=100, required=True)
rating = Integer()
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.name])
class Venue(Table):
name = Varchar(length=100)
capacity = Integer(default=0, secret=True)
@classmethod
def get_readable(cls) -> Readable:
return Readable(template="%s", columns=[cls.name])
class Review(Table):
class Rating(Enum):
bad = 1
average = 2
good = 3
great = 4
score = Integer(choices=Rating)
class Band(Table):
name = Varchar(length=50)
manager = ForeignKey(Manager, null=True)
popularity = Integer(default=0)
@classmethod
def get_readable(cls) -> Readable:
return Readable(template="%s", columns=[cls.name])
class Movie(Table, db=DB):
name = Varchar(length=300)
rating = Real()
duration = Integer()
director = ForeignKey(references=Director)
won_oscar = Boolean()
description = Text()
release_date = Timestamp()
box_office = Numeric(digits=(5, 1))
class Question(Table):
"""
An question table.
"""
title = Varchar(length=200)
slug = Varchar(length=200)
description = Text()
created_at = Timestamp()
view = Integer(default=0)
question_like = Integer(default=0)
accepted_answer = Boolean(default=False)
user = ForeignKey(references=User)
category = ForeignKey(references=Category)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.title])
class Review(Table):
content = Text()
created = Timestamp()
review_grade = Integer()
review_user = ForeignKey(references=User)
ad = ForeignKey(references=Ad)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.ad])
class Movie(Table):
name = Varchar(length=300)
rating = Real(help_text="The rating on IMDB.")
duration = Interval()
director = ForeignKey(references=Director)
oscar_nominations = Integer()
won_oscar = Boolean()
description = Text()
release_date = Timestamp()
box_office = Numeric(digits=(5, 1), help_text="In millions of US dollars.")
tags = Array(base_column=Varchar())
class Director(Table, help_text="The main director for a movie."):
name = Varchar(length=300, null=False)
years_nominated = Array(
base_column=Integer(),
help_text=(
"Which years this director was nominated for a best director "
"Oscar."
),
)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.name])
class Answer(Table):
"""
An answer table.
"""
content = Text()
created_at = Timestamp()
answer_like = Integer(default=0)
is_accepted_answer = Boolean(default=False)
ans_user = ForeignKey(references=User)
question = ForeignKey(references=Question)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.question])
def test_using_expression(self):
"""
Test the `using_expression` option, which can be used to tell Postgres
how to convert certain column types.
"""
Band(name="1").save().run_sync()
alter_query = Band.alter().set_column_type(
old_column=Band.name,
new_column=Integer(),
using_expression="name::integer",
)
alter_query.run_sync()
popularity = Band.select(Band.name).first().run_sync()["name"]
self.assertEqual(popularity, 1)
def test_add(self):
"""
This needs a lot more work. Need to set values for existing rows.
Just write the test for now ...
"""
self.insert_row()
add_query = Band.alter().add_column("weight",
Integer(null=True, default=None))
add_query.run_sync()
response = Band.raw("SELECT * FROM band").run_sync()
column_names = response[0].keys()
self.assertTrue("weight" in column_names)
self.assertEqual(response[0]["weight"], None)
class Director(Table, help_text="The main director for a movie."):
class Gender(enum.Enum):
male = "m"
female = "f"
non_binary = "n"
name = Varchar(length=300, null=False)
years_nominated = Array(
base_column=Integer(),
help_text=(
"Which years this director was nominated for a best director "
"Oscar."),
)
gender = Varchar(length=1, choices=Gender)
@classmethod
def get_readable(cls):
return Readable(template="%s", columns=[cls.name])
class Movie(Table):
class Genre(int, enum.Enum):
fantasy = 1
sci_fi = 2
documentary = 3
horror = 4
action = 5
comedy = 6
romance = 7
musical = 8
name = Varchar(length=300)
rating = Real(help_text="The rating on IMDB.")
duration = Interval()
director = ForeignKey(references=Director)
oscar_nominations = Integer()
won_oscar = Boolean()
description = Text()
release_date = Timestamp()
box_office = Numeric(digits=(5, 1), help_text="In millions of US dollars.")
tags = Array(base_column=Varchar())
barcode = BigInt(default=0)
genre = SmallInt(choices=Genre, null=True)
class SessionsBase(Table, tablename="sessions"):
"""
Use this table, or inherit from it, to create a session store.
"""
#: Stores the session token.
token: Varchar = Varchar(length=100, null=False)
#: Stores the user ID.
user_id: Integer = Integer(null=False)
#: Stores the expiry date for this session.
expiry_date: Timestamp = Timestamp(default=TimestampOffset(hours=1),
null=False)
#: We set a hard limit on the expiry date - it can keep on getting extended
#: up until this value, after which it's best to invalidate it, and either
#: require login again, or just create a new session token.
max_expiry_date: Timestamp = Timestamp(default=TimestampOffset(days=7),
null=False)
@classmethod
async def create_session(
cls,
user_id: int,
expiry_date: t.Optional[datetime] = None,
max_expiry_date: t.Optional[datetime] = None,
) -> SessionsBase:
"""
Creates a session in the database.
"""
while True:
token = secrets.token_urlsafe(nbytes=32)
if not await cls.exists().where(cls.token == token).run():
break
session = cls(token=token, user_id=user_id)
if expiry_date:
session.expiry_date = expiry_date
if max_expiry_date:
session.max_expiry_date = max_expiry_date
await session.save().run()
return session
@classmethod
def create_session_sync(
cls,
user_id: int,
expiry_date: t.Optional[datetime] = None) -> SessionsBase:
"""
A sync equivalent of :meth:`create_session`.
"""
return run_sync(cls.create_session(user_id, expiry_date))
@classmethod
async def get_user_id(
cls,
token: str,
increase_expiry: t.Optional[timedelta] = None) -> t.Optional[int]:
"""
Returns the ``user_id`` if the given token is valid, otherwise
``None``.
:param increase_expiry:
If set, the ``expiry_date`` will be increased by the given amount
if it's close to expiring. If it has already expired, nothing
happens. The ``max_expiry_date`` remains the same, so there's a
hard limit on how long a session can be used for.
"""
session: SessionsBase = (await cls.objects().where(cls.token == token
).first().run())
if not session:
return None
now = datetime.now()
if (session.expiry_date > now) and (session.max_expiry_date > now):
if increase_expiry and (t.cast(datetime, session.expiry_date) - now
< increase_expiry):
session.expiry_date = (t.cast(datetime, session.expiry_date) +
increase_expiry)
await session.save().run()
return t.cast(t.Optional[int], session.user_id)
else:
return None
@classmethod
def get_user_id_sync(cls, token: str) -> t.Optional[int]:
"""
A sync wrapper around :meth:`get_user_id`.
"""
return run_sync(cls.get_user_id(token))
@classmethod
async def remove_session(cls, token: str):
"""
Deletes a matching session from the database.
"""
await cls.delete().where(cls.token == token).run()
@classmethod
def remove_session_sync(cls, token: str):
"""
A sync wrapper around :meth:`remove_session`.
"""
return run_sync(cls.remove_session(token))
def test_integer(self):
self._test_add_column(
column=Integer(null=True, default=None),
column_name="members",
expected_value=None,
)
class Band(Table):
name = Varchar(length=50)
manager = ForeignKey(Manager, null=True)
popularity = Integer(default=0)
class Venue(Table):
name = Varchar(length=100)
capacity = Integer(default=0)
class Band(Table):
label_id = UUID()
date_signed = Date()
name = Varchar(length=50)
manager = ForeignKey(Manager, null=True)
popularity = Integer(default=0)
class Band(Table):
name = Varchar()
manager = ForeignKey(Manager)
popularity = Integer()
class Manager(Table):
name = Varchar()
phone_number = Integer()
class Band(Table):
name = Varchar()
popularity = Integer()
class Band(Table):
name = Varchar(length=50)
manager = ForeignKey(references=Manager, null=True)
popularity = Integer()