119 lines
4.4 KiB
Python
Executable File
119 lines
4.4 KiB
Python
Executable File
#!/usr/bin/python3
|
|
import sqlite3
|
|
import logging
|
|
|
|
database = "bbbbs.sqlite"
|
|
logging.basicConfig(level=logging.WARNING, encoding="utf-8") # set level to log.DEBUG when debugging
|
|
log = logging.getLogger("debug")
|
|
|
|
|
|
try:
|
|
sqliteConnection = sqlite3.connect(database)
|
|
cursor = sqliteConnection.cursor()
|
|
cursor.execute('select sqlite_version();')
|
|
log.debug("Sqlite version is %s" % cursor.fetchall())
|
|
log.info("connected to %s" % database)
|
|
except sqlite3.Error as error:
|
|
log.critical('Error occured: ', error)
|
|
|
|
def create_transmissions_table():
|
|
scheme = """
|
|
CREATE TABLE IF NOT EXISTS transmissions (
|
|
uid TEXT NOT NULL UNIQUE,
|
|
date TEXT,
|
|
author TEXT,
|
|
title TEXT,
|
|
transmission_text TEXT,
|
|
timeslots TEXT,
|
|
transmission_order INT,
|
|
play BOOLEAN
|
|
);
|
|
"""
|
|
if cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='transmissions';").fetchall() == []:
|
|
try:
|
|
cursor.execute(scheme)
|
|
log.info("Successfully created database transmissions")
|
|
except sqlite3.Error as error:
|
|
log.critical("Couldn't create transmissions table")
|
|
log.critical(error)
|
|
exit(1)
|
|
else:
|
|
log.debug('DB transmissions exists, not creating.')
|
|
|
|
def get_transmissions(date=None, timeslot=None):
|
|
return cursor.execute("SELECT * FROM transmissions").fetchall()
|
|
|
|
def add_transmission(uid):
|
|
command="INSERT INTO transmissions(uid, play) VALUES(?, 0)"
|
|
try:
|
|
cursor.execute(command, (uid,))
|
|
sqliteConnection.commit()
|
|
log.debug("Created a transmission uid %s"%(uid))
|
|
except sqlite3.Error as error:
|
|
log.error("Couldn't create a transmission with uid: %s"%(uid))
|
|
log.error(error)
|
|
|
|
def delete_transmission(uid):
|
|
command="DELETE FROM transmissions WHERE uid=?"
|
|
try:
|
|
cursor.execute(command, (uid,))
|
|
sqliteConnection.commit()
|
|
log.debug("Deleted a transmission with uid %s"%(uid))
|
|
except sqlite3.Error as error:
|
|
log.error("Couldn't delete a transmission with uid: %s"%(uid))
|
|
log.error(error)
|
|
|
|
def edit_transmission(uid, date, author, title, transmission_text, timeslots, transmission_order, play):
|
|
command="UPDATE transmissions set date=?, author=?, title=?, transmission_text=?, timeslots=?, transmission_order=?, play=? WHERE uid=?"
|
|
try:
|
|
cursor.execute(command, (date, author, title, transmission_text, timeslots, transmission_order, play, uid))
|
|
sqliteConnection.commit()
|
|
log.debug("Updated a transmission with uid %s (date %s, author %s)"%(uid, date, author))
|
|
except sqlite3.Error as error:
|
|
log.error("Couldn't create a transmission with uid %s (date %s, author %s)"%uid, (date, author))
|
|
log.error(error)
|
|
|
|
def get_transmission_data(uid):
|
|
data = cursor.execute("SELECT * FROM transmissions WHERE uid=?", (uid,)).fetchall()
|
|
if len(data) > 1:
|
|
log.critical("More than one record has the UID %s. Inspect the database manually!" % uid)
|
|
exit(2)
|
|
data = data[0]
|
|
return_data = {
|
|
"uid": data[0],
|
|
"date": data[1],
|
|
"author": data[2],
|
|
"title": data[3],
|
|
"message": data[4],
|
|
"timeslots": data[5],
|
|
"transmission_order": data[6],
|
|
"play": True if data[7] == 1 else False
|
|
}
|
|
return return_data
|
|
|
|
# set return_disabled=True to get all transmissions, including those that aren't enabled
|
|
def get_transmissions_by_timeslot(date, timeslot, cursor, return_disabled=False):
|
|
if return_disabled:
|
|
try:
|
|
data = cursor.execute('SELECT * FROM transmissions WHERE instr("timeslots", ?) > 1 AND date=?;', (timeslot,date)).fetchall()
|
|
except sqlite3.Error as error:
|
|
log.error("Coud not execute command to get transmissions by timeslot: " + error)
|
|
else:
|
|
try:
|
|
data = cursor.execute('SELECT * FROM transmissions WHERE instr("timeslots", ?) > 1 AND date=? AND play=1;', (timeslot,date)).fetchall()
|
|
except:
|
|
log.error("Coud not execute command to get transmissions by timeslot: " + error)
|
|
return_data = []
|
|
for row in data:
|
|
return_data.append({
|
|
"uid": row[0],
|
|
"date": row[1],
|
|
"author": row[2],
|
|
"title": row[3],
|
|
"message": row[4],
|
|
"timeslots": row[5],
|
|
"transmission_order": row[6],
|
|
"play": True if row[7] == 1 else False
|
|
})
|
|
return return_data
|