def add_command(command, response, admin_only):
"""Adds a command to the database"""
if not get_command(command):
cursor = database.get_db().cursor()
cursor.execute("INSERT INTO commands (command, response, admin_only) VALUES (?, ?, ?)", (command, response, admin_only, ))
database.get_db().commit()
return "Command {} has been added".format(command)
return "Command already exists"
def remove_command(command):
"""Removes command from the database"""
if get_command(command):
cursor = database.get_db().cursor()
cursor.execute("DELETE from commands where command = ?", (command, ))
database.get_db().commit()
return "Command {} has been removed".format(command)
return False
def store(teams, division):
db = get_db()
cursor = db.cursor()
cursor.execute('DELETE FROM division WHERE label=%s', division)
cursor.execute('INSERT INTO division set label=%s', division)
division_id = cursor.lastrowid
# clear old version of teams using that division label
#cursor.execute('DELETE FROM team WHERE division_id=%s', division_id)
# should be handled by foreign key constraint
for team in teams:
team['division_id'] = division_id
cursor.execute('DESCRIBE team')
fields = set((x[0] for x in cursor.fetchall()))
for team in teams:
keys = []
values = []
for k, v in team.items():
if k in fields:
keys.append(k)
values.append(v)
placeholder = ", ".join(["%s"] * len(keys))
stmt = "insert into `{table}` ({columns}) values ({values});".format(table='team',
columns=",".join(keys), values=placeholder)
cursor.execute(stmt, list(values))
db.commit()
def check_if_admin(group=None, user_id=None):
"""Returns true or false based if the user is an admin in the group"""
cursor = database.get_db().cursor()
data = cursor.execute('SELECT DISTINCT m.member_jid from memberTable as m, KIKcontactsTable as c where m.is_admin = 1 and m.group_id = ? and m.member_jid = ?', (group, user_id, )).fetchone()
if data:
return True
return False
def _process_form():
name = request.form['name']
location = request.form['location']
database = get_db()
database.execute(
f'insert into users (name, location) values ("{name}", "{location}")'
)
database.commit()
return f'Added user: {name} from {location}'
def show_commands():
"""Shows all the available commands"""
cursor = database.get_db().cursor()
result = ""
data = cursor.execute('SELECT * from commands').fetchall()
result = "Commands that are available: \n"
for command in data:
result += "{} {} {} \n".format(command[1], command[2], command[3])
return result;
def getGames(division):
header = [('GameNum', 'Date', 'Time', 'FieldID', 'HomeTeam', 'AwayTeam', 'RoundTypeCode')]
db = get_db()
cursor = db.cursor()
cursor.execute('''SELECT 0, DATE_FORMAT(gamedate, "%%m/%%d/%%Y") as date,
TIME_FORMAT(gametime, "%%T") as time, 0,
home, away, "B"
FROM game
WHERE agegroup=%s AND archive IS NULL''', division)
return header + list(cursor.fetchall())
def update_venue(division):
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT name, id FROM venue')
venues = cursor.fetchall()
print(venues)
cursor.execute(
'''SELECT team.id, name, declaredvenue, venue
FROM team
LEFT JOIN division ON division.id=division_id
WHERE venue_id IS NULL
AND division.label=%s
ORDER BY name''', division)
updates = []
print('\n\n')
for i, (tid, team_name, decl, venue) in enumerate(cursor.fetchall()):
# choose "venue" or "declared venue", with preference to "venue"
target = venue or decl
scores = []
if target:
for venue_name, venue_id in venues:
scores.append(
(fuzz.partial_ratio(target,
venue_name), venue_id, venue_name))
scores.sort(reverse=True)
if scores and scores[0][0] > 90:
updates.append((tid, scores[0][1]))
print(
f'{i+1:2}. Matched "{team_name}" using "{target}" with "{scores[0][2]}"\n'
)
elif target is None:
print(f'{i+1:2}. Error: No declared venue for "{team_name}"')
else:
print(
f'{i+1:2}. Error: No good matches for "{team_name}" using "{target}"'
)
print(' Best matches are:')
for best in scores[:3]:
print(' ', best[2])
def read_new_messages(app=None):
"""Reads new messages based on latest timestamp and checks for commands"""
global last_pull, lurker_mode
with app.app_context():
cursor = database.get_db().cursor()
group = '*****@*****.**'
data = cursor.execute('SELECT c.display_name, m.body, m.timestamp, m.partner_jid FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid and m.timestamp > ? order BY m.timestamp ASC;', (group, int(last_pull), )).fetchall()
for line in data:
if not line[1] is None:
command = line[1].lower()
result = get_command(command)
if result and not lurker_mode:
if not result[1]:
send_message(result[2])
elif check_if_admin(group, line[3]):
send_message(result[2])
if check_if_admin(group, line[3]):
if '!add' in command:
new_command = command.split(' ')[1]
admin_only = command.split(' ')[2]
response = " ".join(command.split(' ')[3:])
query = add_command(new_command, response, admin_only)
send_message(query)
elif '!remove' in command:
remove = command.split(' ')[1]
response = remove_command(remove)
if response:
send_message(response)
elif '!show' in command:
send_message(show_commands())
if command == "!lurk":
lurker_mode = not lurker_mode
if lurker_mode:
send_message("Lurking mode enabled")
else:
send_message("Lurking mode disabled")
def pull_db(app=None):
"""Pulls the database from the phone and merges it with our own database"""
global last_pull
with app.app_context():
cursor = database.get_db().cursor()
group = '*****@*****.**'
last_pull = int(cursor.execute('SELECT MAX(m.timestamp) FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid order BY m.timestamp ASC;', (group, )).fetchone()[0])
print ("PULLING DATA")
call(["adb", "pull", "/data/data/kik.pikek/databases/kikDatabase.db", "./databases"])
db_con = sqlite3.connect('databases/kikDatabase.db')
with io.open(config.DATA_BASE_DIR + 'dump.sql', 'w', encoding='utf8') as f:
for line in db_con.iterdump():
if 'CREATE TABLE' in line:
line = line.replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS')
if 'INSERT INTO' in line:
line = line.replace('INSERT INTO', 'INSERT OR IGNORE INTO ')
f.write('%s\n' % line)
db_con.close()
f = io.open(config.DATA_BASE_DIR + 'dump.sql','r', encoding='utf8')
command = f.read()
f.close()
cursor.executescript(command)
os.remove(config.DATA_BASE_DIR + 'kikDatabase.db')
read_new_messages(app)
def get_all_venues():
db = get_db()
cursor = db.cursor()
cursor.execute('SELECT id, name FROM venue ORDER BY name')
return cursor.fetchall()
return cursor.fetchall()
def get_params():
parser = ArgumentParser()
parser.add_argument('--check', action='store')
parser.add_argument('--show', action='store_true')
parser.add_argument('-c', action='store')
parser.add_argument('-v', action='store', type=int)
params = parser.parse_args()
return params
if __name__ == '__main__':
db = get_db()
args = get_params()
if args.check:
teams = get_teams(db, args.check)
venues = get_venues(db, teams)
if not venues:
if args.show:
for vid, name in get_all_venues():
print(f'{vid:3} {name}')
print('Assign a venue to the missing club')
if args.c:
if not args.v:
print('Provide a venue ID to be assigned to the club')
else:
cursor = db.cursor()
c_id = cursor.execute('INSERT INTO club SET name=%s', args.c)
def get_command(command):
"""returns a single command"""
cursor = database.get_db().cursor()
return cursor.execute('SELECT command, admin_only, response FROM commands where command = ?', (command, )).fetchone()
def group_chat(group=None):
c = database.get_db().cursor()
# data = c.execute('select display_name, jid FROM KIKcontactsTable WHERE jid like "%groups.kik.com%"').fetchall()
data = c.execute('SELECT c.display_name, m.body, m.timestamp FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = ? and m.partner_jid = c.jid order BY m.timestamp ASC;', (group, )).fetchall()
return render_template('group.html' , data=data)
def home():
c = database.get_db().cursor()
data = c.execute('select display_name, jid FROM KIKcontactsTable WHERE jid like "%groups.kik.com%" and display_name != "none"').fetchall()
# data = c.execute('SELECT c.display_name, m.body, m.timestamp FROM messagesTable as m, KIKcontactsTable as c where m.bin_id = "*****@*****.**" and m.partner_jid = c.jid order BY m.timestamp ASC;').fetchall()
return render_template('home.html' , data=data)