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