def signup():
"""Routing function for signup page.
It allows only *POST* request.
If the valid values are gotten from the request.form then new user is created and inserted
to the db.
"""
if request.method == "POST":
try:
conn, cur = getDb()
username = request.form["username"]
password = request.form["password"]
email = str(request.form["email"])
role = request.form["role"]
regtime = getCurrTimeStr()
lastlogin = regtime
online = False
usr = user.User(username, password, email, role, lastlogin, regtime, online)
users = user.Users(conn, cur)
users.add_user(usr)
return redirect(url_for("admin"))
except IntegrityError:
conn.rollback()
roles = None
error = "This username already registered."
return render_template("adminpanel.html", error=error, roles=roles)
else:
conn.rollback()
error = sys.exc_info()[0]
roles = None
return render_template("adminpanel.html", error=error, roles=roles)
def signin():
"""Routing function for signin page."""
conn, cur = getDb()
error = None
roles = None
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
if login_success(username, password):
error = "Logged in!"
cur.execute("SELECT role, lastlogin FROM users WHERE username='******';" % username)
role, lastlogin = cur.fetchone()
g.role = role
g.lastlogin = lastlogin
session["username"] = request.form["username"]
now = getCurrTimeStr()
cur.execute("UPDATE users SET lastlogin='******' WHERE username='******'" % (now, username))
cur.execute("UPDATE users SET online=TRUE WHERE username='******'" % username)
conn.commit()
else:
error = "Invalid username or password!"
if "username" in session:
username = session["username"]
cur.execute("SELECT role, lastlogin FROM users WHERE username='******';" % username)
role, lastlogin = cur.fetchone()
g.role = role
g.lastlogin = lastlogin
return render_template("signin.html")
def admin():
"""Routing function for admin page.
This page allows *POST* and *GET* requests.
*GET request:* If the user signed in adminpanel page is rendered.
Otherwise signin page is rendered.
*POST request:* Checks the request.form values for registered
users. If the values are valid it adds user to the session and
renders the adminpanel. Otherwise error message is flashed.
"""
conn, cur = getDb()
error = None
roles = None
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
if login_success(username, password):
error = "Logged in!"
query = "SELECT role, lastlogin FROM users WHERE username=%s"
cur.execute(query, (username,))
role, lastlogin = cur.fetchone()
g.role = role
g.lastlogin = lastlogin
session["username"] = request.form["username"]
now = getCurrTimeStr()
query = "UPDATE users SET lastlogin=%s WHERE username=%s"
cur.execute(query, (now, username))
query = "UPDATE users SET online=TRUE WHERE username=%s"
cur.execute(query, (username,))
conn.commit()
else:
error = "Invalid username or password!"
if "username" in session:
username = session["username"]
query = "SELECT role, lastlogin FROM users WHERE username=%s"
cur.execute(query, (username,))
role, lastlogin = cur.fetchone()
g.role = role
g.lastlogin = lastlogin
else:
flash("Wrong username or password")
return render_template("signin.html")
return render_template("adminpanel.html", error=error, roles=roles)
def initialize_database():
conn, cur = db.getDb()
try:
conn, cur = db.getDb()
drop_tables()
# users table
query = """CREATE TABLE users (id serial PRIMARY KEY,
username varchar(32) UNIQUE NOT NULL,
password varchar(255) NOT NULL,
email varchar(255) UNIQUE NOT NULL,
role varchar(12) DEFAULT 'level1',
lastlogin varchar(26),
regtime varchar(26),
online boolean DEFAULT FALSE);
"""
cur.execute(query)
now = getCurrTimeStr()
query = """INSERT INTO users
(username, password, email, role, lastlogin, regtime, online)
values ('admin', '1234', '*****@*****.**', 'admin','%s', '%s', FALSE );""" % (now, now)
cur.execute(query)
# countries table
query = """CREATE TABLE countries (id serial PRIMARY KEY,
name varchar(65) NOT NULL,
code varchar(2) NOT NULL);
"""
cur.execute(query)
# leagues table
query = """CREATE TABLE leagues (id serial PRIMARY KEY,
name varchar(255) NOT NULL,
country_id integer references countries(id) );
"""
cur.execute(query)
# players table
query = """CREATE TABLE players (id serial PRIMARY KEY,
name varchar(32) NOT NULL,
surname varchar(32) NOT NULL,
age varchar(3),
pp varchar(32),
country_id integer references countries(id));
"""
cur.execute(query)
#seasons table
query="""CREATE TABLE seasons ( id serial PRIMARY KEY,
year varchar(9));"""
cur.execute(query)
# awards table
query = """CREATE TABLE awards (id serial PRIMARY KEY,
name varchar(255) NOT NULL)
"""
cur.execute(query)
# award_stats table
query = """CREATE TABLE award_stats (id serial PRIMARY KEY,
award_id integer references awards(id),
player_id integer references players(id),
season_id integer references seasons(id));
"""
cur.execute(query)
# coaches table
query = """CREATE TABLE coaches (id serial PRIMARY KEY,
name varchar(32) NOT NULL,
surname varchar(32) NOT NULL,
country_id integer REFERENCES countries(id));
"""
cur.execute(query)
# teams table
query = """CREATE TABLE teams (id serial PRIMARY KEY,
name varchar(255) NOT NULL,
coach_id integer references coaches(id) );
"""
cur.execute(query)
# teamrosters table
query = """CREATE TABLE teamrosters (id serial PRIMARY KEY,
player_id integer REFERENCES players(id),
team_id integer REFERENCES teams(id));
"""
cur.execute(query)
# standings table
query="""CREATE TABLE standings ( id serial PRIMARY KEY,
season_id integer REFERENCES seasons(id),
league_id integer REFERENCES leagues(id),
team_id integer REFERENCES teams(id));"""
cur.execute(query)
# schedules table
query="""CREATE TABLE schedules (id serial PRIMARY KEY,
team1_id integer REFERENCES teams(id),
team2_id integer REFERENCES teams(id),
season_id integer REFERENCES seasons(id),
league_id integer REFERENCES leagues(id),
date timestamp,
saloon varchar(255),
score1 integer,
score2 integer,
state boolean
);"""
cur.execute(query)
# matches table
query = """CREATE TABLE matches (id serial PRIMARY KEY,
schedule_id integer REFERENCES schedules(id),
T1_3PT integer,
T1_2PT integer,
T1_block integer,
T1_reb integer,
T1_rate integer,
T2_3PT integer,
T2_2PT integer,
T2_block integer,
T2_reb integer,
T2_rate integer );
"""
cur.execute(query)
# DO NOT ADD ANYTHING AFTER THIS LINE
conn.commit() # commit changes
except:
print(sys.exc_info())
conn.rollback()
return 'create table error'
# populate db with sample data
try:
# populate countries data
cur.execute(open("sampledata/countries.sql","r").read())
# populate leagues data
cur.execute(open("sampledata/seasons.sql","r").read())
cur.execute(open("sampledata/awards.sql","r").read())
cur.execute(open("sampledata/leagues.sql","r").read())
cur.execute(open("sampledata/players.sql","r").read())
cur.execute(open("sampledata/coaches.sql","r").read())
cur.execute(open("sampledata/teams.sql","r").read())
cur.execute(open("sampledata/standings.sql","r").read())
cur.execute(open("sampledata/schedules.sql","r").read())
cur.execute(open("sampledata/teamrosters.sql","r").read())
conn.commit() # commit changes
except:
print(sys.exc_info())
conn.rollback()
return redirect(url_for('home'))
