def create_passwordreset():
"""
Create the passwordreset table in the database.
"""
with app.app_context():
conn = db.session.connection()
table = PasswordReset.metadata.tables['passwordreset']
table.create(bind=db.engine)
def migrate():
"""
Add columns 'location', 'org', and 'mult' to patroncontact table.
"""
with app.app_context():
conn = db.session.connection()
with conn.begin() as transaction:
conn.execute('ALTER TABLE patroncontact ADD COLUMN location TEXT')
conn.execute('ALTER TABLE patroncontact ADD COLUMN org TEXT')
conn.execute('ALTER TABLE patroncontact ADD COLUMN mult BOOLEAN CHECK (mult IN (0, 1))')
def update_passwords():
"""
Update the plain-text passwords to bcrypt. Checks to see if already hashed,
however admin should check if this needs to be run first.
"""
with app.app_context():
conn = db.session.connection()
for staff in Staff.query.all():
plaintext = staff.password.hashed
# Make some attempt not to hash already hashed passwords
if ishashed(plaintext):
continue
hashed = hashpassword(plaintext)
# Have to sneak around the Staff class to get at the literal database value.
sql = text('update staff set password = :hashed where username = :username')
conn.execute(sql, hashed=hashed, username=staff.username)
db.session.commit()
def add_records_for_test():
'''Add few test records to table Author and Book '''
from . import app, db
from models import Author, Book
with app.app_context():
author1 = Author('Ivan', 'Ivanov')
book1 = Book('PROLOG', 1993)
author1.books.append(book1)
book2 = Book('ADA95', 1995)
author1.books.append(book2)
author2 = Author('Vasil', 'Petrov')
author2.books.append(book1)
book3 = Book('Pascal', 1998)
author3 = Author('Serey', 'Guk')
author3.books.append(book3)
db.session.add(author1)
db.session.add(book1)
db.session.add(author2)
db.session.add(book2)
db.session.add(author3)
db.session.add(book3)
db.session.commit()
def update_passwords():
"""
Update the plain-text passwords to bcrypt. Checks to see if already hashed,
however admin should check if this needs to be run first.
"""
with app.app_context():
conn = db.session.connection()
for staff in Staff.query.all():
plaintext = staff.password.hashed
# Make some attempt not to hash already hashed passwords
if ishashed(plaintext):
continue
hashed = hashpassword(plaintext)
# Have to sneak around the Staff class to get at the literal database value.
sql = text(
'update staff set password = :hashed where username = :username'
)
conn.execute(sql, hashed=hashed, username=staff.username)
db.session.commit()
def migrate():
"""
Add database-level cascading deletes on foreign keys.
"""
with app.app_context():
conn = db.session.connection()
with conn.begin() as transaction:
# add ON DELETE to table `passwordreset`
conn.execute(
'CREATE TEMP TABLE passwordreset_backup('
'secret VARCHAR NOT NULL,created DATETIME,username VARCHAR);')
conn.execute(
'INSERT INTO passwordreset_backup'
' SELECT secret, created, username FROM passwordreset;')
conn.execute('DROP TABLE passwordreset;')
conn.execute(
'CREATE TABLE passwordreset ('
'secret VARCHAR NOT NULL,created DATETIME,username VARCHAR,'
'PRIMARY KEY (secret),'
'FOREIGN KEY(username) REFERENCES staff (username) ON DELETE CASCADE);'
)
conn.execute(
'INSERT INTO passwordreset'
' SELECT secret, created, username FROM passwordreset_backup;')
conn.execute('DROP TABLE passwordreset_backup;')
# add ON DELETE to table `readinglist`
conn.execute('CREATE TEMP TABLE readinglist_backup('
'"RLID" INTEGER NOT NULL,"ISBN" TEXT,recdate DATE,'
'username VARCHAR,book TEXT,author TEXT,comment TEXT,'
'sticky BOOLEAN,category TEXT);')
conn.execute(
'INSERT INTO readinglist_backup'
' SELECT "RLID", "ISBN", recdate, username, book'
' ,author, comment, sticky, category FROM readinglist;')
conn.execute('DROP TABLE readinglist;')
conn.execute(
'CREATE TABLE readinglist('
'"RLID" INTEGER NOT NULL,"ISBN" TEXT,recdate DATE,'
'username VARCHAR,book TEXT,author TEXT,comment TEXT, '
'sticky BOOLEAN,category TEXT,'
'PRIMARY KEY ("RLID"), '
'FOREIGN KEY(username) REFERENCES staff (username) ON DELETE CASCADE, '
'CHECK (sticky IN (0, 1)));')
conn.execute(
'INSERT INTO readinglist'
' SELECT "RLID", "ISBN", recdate, username, book,'
' author, comment, sticky, category FROM readinglist_backup;')
conn.execute('DROP TABLE readinglist_backup;')
# add ON DELETE to table `patroncontact`
conn.execute(
'CREATE TEMP TABLE patroncontact_backup('
'"PCID" INTEGER NOT NULL,reqdate TEXT,username VARCHAR,'
'name TEXT,email TEXT,contact TEXT,phone TEXT,'
'times TEXT,likes TEXT,dislikes TEXT,comment TEXT,'
'audience TEXT,format_pref TEXT,chat TEXT,handle TEXT,'
'location TEXT,org TEXT,mult BOOLEAN,status TEXT);')
conn.execute(
'INSERT INTO patroncontact_backup'
' SELECT "PCID", reqdate, username, name, email, contact, phone'
', times, likes, dislikes, comment, audience, format_pref, chat'
', handle, location, org, mult, status FROM patroncontact;')
conn.execute('DROP TABLE patroncontact;')
conn.execute(
'CREATE TABLE patroncontact ('
'"PCID" INTEGER NOT NULL, reqdate TEXT, username VARCHAR, '
'name TEXT, email TEXT, contact TEXT, phone TEXT, '
'times TEXT, likes TEXT, dislikes TEXT, comment TEXT, '
'audience TEXT, format_pref TEXT, chat TEXT, handle TEXT, '
'location TEXT, org TEXT, mult BOOLEAN, status TEXT, '
'PRIMARY KEY ("PCID"), '
'FOREIGN KEY(username) REFERENCES staff (username) ON DELETE CASCADE, '
'CHECK (mult IN (0, 1)));')
conn.execute(
'INSERT INTO patroncontact'
' SELECT "PCID", reqdate, username, name, email, contact, phone'
' ,times, likes, dislikes, comment, audience, format_pref, chat'
' ,handle, location, org, mult, status'
' FROM patroncontact_backup;')
conn.execute('DROP TABLE patroncontact_backup;')
from sqlalchemy import inspect from models import * from library import app, db app.app_context().push()
def init_db():
'''Initialise database. Create schema'''
from . import app, db
with app.app_context():
db.create_all()
