def __init__(self, username, password, _id=None):
self.username = username
self.password = password
# the dot hex makes it a string
self.user_id = None
# init database
self.db = Database('eigi-data.db')
def run_sql_script(settings, filename, database=None):
"""Run a specified mySQL script.
Args:
dbconn: An open connection to the Drupal database.
filename: Filename with full path to the script.
Returns:
True if the file was executed.
"""
result = False
if not database:
database = settings['database']['drupal_database']
try:
dbconn = Database(settings['database']['drupal_host'],
settings['database']['drupal_username'],
settings['database']['drupal_password'], database)
except AttributeError:
logging.error("Settings file is missing database information.")
except OperationalError:
logging.error("Could not access the database. "
"Aborting database creation.")
else:
if os.path.isfile(filename):
if dbconn.connected():
result = dbconn.execute_sql_file(filename, database)
else:
logging.error("No database connection")
else:
logging.error("No script file found at: %s", filename)
return result
def load_dataset(db_dir, db_name):
"""Load the dataset from the database.
Args:
db_dir -- directory of the database
db_name -- name of the database
Return:
dataset --- list of Taskset-objects
"""
logger = logging.getLogger('traditional-SA.main.load_dataset')
# try to create a Database-object
try:
my_database = Database(db_dir=db_dir, db_name=db_name)
except ValueError as val_err:
logger.error("Could not create Database-object: %s", val_err)
return None
# read the data-set from the database
logger.info("Reading task-sets from the database...")
start_time = time.time()
dataset = my_database.read_table_taskset() # read table 'TaskSet'
end_time = time.time()
logger.info("Read %d task-sets from the database.", len(dataset))
logger.info("Time elapsed: %f \n", end_time - start_time)
return dataset
def test_schedulability_test():
"""Main function for testing of single schedulability tests."""
my_database = Database(os.getcwd(), "panda_v3.db")
taskset_46429 = my_database.read_table_taskset(taskset_id=46429)[0]
print("----- Simulation -----")
start_t = time.time()
result = simulate(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- Basic Utilization -----")
start_t = time.time()
result = basic_utilization_test(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- RM Utilization -----")
start_t = time.time()
result = rm_utilization_test(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- HB Utilization -----")
start_t = time.time()
result = hb_utilization_test(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- RTA Audsley -----")
start_t = time.time()
result = rta_audsley(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- RTA Buttazzo -----")
start_t = time.time()
result = rta_buttazzo(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- RM Workload -----")
start_t = time.time()
result = rm_workload_test(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
print("----- HET Workload -----")
start_t = time.time()
result = het_workload_test(taskset_46429)
end_t = time.time()
print("Result: %r -- Time elapsed: %f s" % (result, end_t - start_t))
class User(object):
def __init__(self, username, password, _id=None):
self.username = username
self.password = password
# the dot hex makes it a string
self.user_id = None
# init database
self.db = Database('eigi-data.db')
# check if username exists
def check_for_username(self):
"""
Checks if the username is in the database.
"""
db_resp = self.db.get_row('user', 'username', self.username)
if db_resp is None:
return False
return True
def get_hashed_password(self, username):
"""
Returns the hasehd password from the database for the given username.
"""
db_resp = self.db.get_row('user', 'username', self.username)
return db_resp[2]
def insert_hased_password(self, password):
"""
Inserts hashed password into the database.
Replaces password if already there.
"""
# get hashed version
hased_password = PasswordUtil.hash_password(password)
self.db.make_query('''
update user
set hash_value = "{}"
where username = "******"
'''.format(hased_password, self.username))
print(hased_password)
def check_password(self):
hashed_password = self.get_hashed_password(self.username)
return PasswordUtil.check_hashed_password(self.password,
hashed_password)
def __init__(self, options):
"""Gonna need a db, and some creds."""
log.info("Starting AG Chatter Bot.")
self.options = options
# Build Constructors
self.idx2word = Database(
host=options.redis_host, pass_=options.redis_pass, db=0
)
self.word2idx = Database(
host=options.redis_host, pass_=options.redis_pass, db=1
)
self.dataReader = DataReader(
self.options, self.idx2word, self.word2idx
)
self.model = Model(
self.options
)
log.debug(options)
log.info("Init complete.")
class Chatter(object):
"""Chatter App."""
def __init__(self, options):
"""Gonna need a db, and some creds."""
log.info("Starting AG Chatter Bot.")
self.options = options
# Build Constructors
self.idx2word = Database(
host=options.redis_host, pass_=options.redis_pass, db=0
)
self.word2idx = Database(
host=options.redis_host, pass_=options.redis_pass, db=1
)
self.dataReader = DataReader(
self.options, self.idx2word, self.word2idx
)
self.model = Model(
self.options
)
log.debug(options)
log.info("Init complete.")
def sanity(self):
"""This kind of thing should be standardized."""
log.info("Starting Stanity Check")
key = "stuff"
value = "morestuff"
self.idx2word.write_data(key, value)
new_value = self.idx2word.read_data(key)
assert value == new_value
log.debug("Passed Stanity Check")
return True
def main(self):
"""This kind of thing should be standardized."""
if self.sanity():
# Add the path to files in the config.yaml
dataset = self.dataReader.make_buckets()
print(dataset)
return True
return False
def time_per_taskset():
# load the dataset
my_database = Database(os.getcwd(), "panda_v3.db")
dataset = my_database.read_table_taskset()
# create empty list for times
times = []
for taskset in dataset: # iterate over all task-sets
# do schedulability analysis
start_t = time.time()
simulate(taskset)
end_t = time.time()
# add time
times.append(end_t - start_t)
# calculate average time
average_t = sum(times) / len(times)
print("SIMULATION -- Average time per task-set: %f s" % (average_t))
def process_migration(settings, database=None):
# Continue unless something happens to abort process
continue_script = True
print "The migration process will alter your database"
continue_script = cli.query_yes_no("Are you sure you want to continue?",
"no")
if continue_script:
try:
if not database:
database = settings['database']['drupal_database']
dbconn = Database(settings['database']['drupal_host'],
settings['database']['drupal_username'],
settings['database']['drupal_password'],
database)
except AttributeError:
logger.error("Settings file is missing database information.")
except OperationalError:
logger.error(
"Could not access the database. Aborting database creation.")
else:
cli.print_header("Preparing {} for migration".format(database))
continue_script = prepare.prepare_migration(
settings, dbconn, database)
if continue_script:
if check_migration_prerequisites(settings, dbconn, database):
cli.print_header("Migrating content from {}".format(database))
continue_script = migrate.run_migration(settings, dbconn, database)
else:
logging.critical("Migration aborted because it did not meet "
"the prerequistes for success.")
if continue_script:
cli.print_header("Deploying to test environment")
continue_script = deploy.deploy_database(settings, dbconn, database)
if not continue_script:
sys.exit(1)
def run_diagnostics(settings, database=None):
""" Show Drupal database analysis but don't alter any Drupal CMS tables.
Args:
database: The Drupal database to analyse.
"""
results = {}
try:
if not database:
database = settings['database']['drupal_database']
dbconn = Database(settings['database']['drupal_host'],
settings['database']['drupal_username'],
settings['database']['drupal_password'], database)
except AttributeError:
logging.error("Settings file is missing database information.")
except OperationalError:
logging.error("Could not access the database. "
"Aborting database creation.")
else:
try:
drupal_version = dbconn.get_drupal_version()
except OperationalError:
drupal_version = None
logging.warning("Could not get Drupal version.")
if drupal_version:
logging.debug("Checking tables...")
all_tables_present = check_tables(dbconn, float(drupal_version))
else:
drupal_version = "Unknown"
logging.error(
"Could not check tables since the Drupal version is unknown.")
try:
# General analysis of Drupal database properties
drupal_sitename = dbconn.get_drupal_sitename()
drupal_posts_count = len(dbconn.get_drupal_posts())
drupal_terms_count = len(dbconn.get_drupal_terms())
drupal_node_types = dbconn.get_drupal_node_types()
drupal_node_types_count = len(drupal_node_types)
drupal_node_count_by_type = dbconn.get_drupal_node_count_by_type()
logging.debug("Looking for common problems")
# Look for common problems
duplicate_terms_count = len(
dbconn.get_drupal_duplicate_term_names())
terms_exceeded_char_count = len(
dbconn.get_terms_exceeded_charlength())
duplicate_aliases_count = len(dbconn.get_duplicate_aliases())
except Exception as ex:
logging.error(
"Could not run diagnostics. Please use a database interface "
"that supports Drupal version %s.", drupal_version)
else:
results = {
"sitename": drupal_sitename,
"version": drupal_version,
"posts_count": drupal_posts_count,
"terms_count": drupal_terms_count,
"duplicate_terms_count": duplicate_terms_count,
"node_types_count": drupal_node_types_count,
"node_types": drupal_node_types,
"terms_exceeded_char_count": terms_exceeded_char_count,
"duplicate_aliases_count": duplicate_aliases_count,
"node_count_by_type": drupal_node_count_by_type
}
return results
def __init__(self):
self.db = Database('eigi-data.db')
class Album(object):
def __init__(self):
self.db = Database('eigi-data.db')
def increment_views(self, album_id):
self.db.make_query('''
update album
set views = views + 1
where album_id = "{}"
'''.format(album_id))
def get_albums(self):
"""
Returns all albums.
"""
album_data = self.db.get_query_as_list(
"select * from album order by date_created desc;")
# I also need an image to represent the album
rtn_dict = {}
count = 0
for album in album_data:
print('\n', 'album_id', album['album_id'])
album_cover_dict = self.get_album_cover(album['album_id'])
print(album_cover_dict)
# a new album may not have this yet so check that there is a value first
if len(album_cover_dict) > 0:
album['large_square'] = album_cover_dict[0]['large_square']
rtn_dict[count] = album
count += 1
return rtn_dict
def get_album(self, album_id):
# update view count
self.increment_views(album_id)
query = '''
select * from album where album_id = {}
'''.format(album_id)
album_data = self.db.get_query_as_list(query)
print(album_data)
if len(album_data) > 0 and album_data[0]['photos'] > 0:
# list index out of range
# setting large square in the return data to the value returned by self.get_album_cover
if self.get_album_cover(album_data[0]['album_id']):
album_data[0]['large_square'] = self.get_album_cover(
album_data[0]['album_id'])[0]['large_square']
album_data[0]['title'] = name_util.make_decoded(
album_data[0]['title'])
album_data[0]['description'] = name_util.make_decoded(
album_data[0]['description'])
print(album_data)
return album_data[0]
elif len(album_data) > 0:
return album_data[0]
else:
return album_data
def get_containing_album(self, photo_id):
"""
Get the album that a photo belongs to.
"""
query_string = '''
select album.album_id, album.title, album.views, album.description, album.photos, date_created
from photo_album
join album on(photo_album.album_id=album.album_id)
where photo_album.photo_id={}
'''.format(photo_id)
album_data = self.db.get_query_as_list(query_string)
return album_data
def get_album_cover(self, album_id):
query_string = '''
select images.large_square from album
join photo_album on(album.album_id=photo_album.album_id)
join photo on(photo_album.photo_id=photo.photo_id)
join images on(images.photo_id=photo.photo_id)
where album.album_id={}
order by photo.date_uploaded asc limit 1
'''.format(album_id)
album_cover = self.db.get_query_as_list(query_string)
# print(album_cover)
return album_cover
def get_album_photos(self, album_id):
# increment the view count
self.increment_views(album_id)
query_string = '''
select album.title, album.album_id,
album.description, album.views, album.photos,
images.large_square,
images.original,
photo.photo_id, photo.date_taken,
photo.photo_title, photo.date_uploaded, photo.views
from album
join photo_album on(album.album_id=photo_album.album_id)
join photo on(photo_album.photo_id=photo.photo_id)
join images on(images.photo_id=photo.photo_id)
where album.album_id={}
order by photo.date_uploaded asc
'''.format(album_id)
album_data = self.db.get_query_as_list(query_string)
rtn_dict = {}
count = 0
for d in album_data:
rtn_dict[count] = d
count += 1
print()
print('here be dragons')
print('\n', rtn_dict)
if not rtn_dict:
print(10 * '\nnope')
album_data = self.get_album(album_id)
print(album_data)
rtn_dict = {0: album_data}
return rtn_dict
return rtn_dict
def get_photo_album(self, album_id):
"""
used to getting albums when deleting them
"""
query = '''
select * from photo_album where album_id = {}
'''.format(album_id)
photo_album_data = self.db.make_query(query)
return photo_album_data
def delete_album(self, album_id):
# you have to delete from photo_album first
# then from album, this is due to database constraints
delete_from_photo_album = '''
delete from photo_album where album_id = {}
'''.format(album_id)
resp = self.db.make_query(delete_from_photo_album)
print(resp)
delete_from_album = '''
delete from album where album_id = {}
'''.format(album_id)
self.db.make_query(delete_from_album)
# print()
# print(self.get_album(album_id))
# print(self.get_photo_album(album_id))
# print()
if not self.get_album(album_id) and not self.get_photo_album(album_id):
return True
return False
def update_album(self, album_id, new_title, new_description):
# you already know that it exists because otherwise the user wouldn't see it
self.db.make_query('''
update album
set title = '{}', description = '{}'
where album_id = '{}'
'''.format(new_title, new_description, album_id))
def add_photos_to_album(self, album_id, photos):
# db.insert_data(
# table='tag',
# tag_name=new_tag,
# user_id='28035310@N00'
# )
for photo in photos:
self.db.insert_data(table='photo_album',
photo_id=photo,
album_id=album_id)
# i need to update the photo count in album
# get the number of photos in the album after adding them
query_string = '''
SELECT COUNT(photo_id)
FROM photo_album
WHERE album_id='{}';
'''.format(album_id)
# update the count in album
photo_count = self.db.make_query(query_string)[0][0]
print(photo_count)
# update the count in album
query_string = '''
UPDATE album
SET photos = {}
WHERE album_id='{}';
'''.format(int(photo_count), album_id)
self.db.make_query(query_string)
def get_album_photos_in_range(self, album_id, limit=20, offset=0):
"""
Returns the latest 10 photos.
Offset is where you want to start from, so 0 would be from the most recent.
10 from the tenth most recent etc.
"""
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = ('''
select photo.photo_title, photo.photo_id, album.album_id,
album.title, photo.views, photo.date_uploaded, photo.date_taken,
images.original, images.large_square
from photo_album
join photo on(photo.photo_id=photo_album.photo_id)
join album on(photo_album.album_id=album.album_id)
join images on(photo.photo_id=images.photo_id)
where album.album_id='{}'
order by date_uploaded
desc limit {} offset {}
''').format(album_id, limit, offset)
q_data = c.execute(query_string)
rtn_dict = {'limit': limit, 'offset': offset, 'photos': []}
"""
I think it may actually be better to layout what fields you want here.
And maybe include all sizes.
"""
data = [dict(ix) for ix in q_data]
a_dict = {}
count = 0
for d in data:
a_dict[count] = d
count += 1
rtn_dict = {'photos': a_dict}
rtn_dict['limit'] = limit
rtn_dict['offset'] = offset
return rtn_dict
def remove_photos_from_album(self, album_id, photos):
for photo_id in photos:
query_string = '''
delete from photo_album
where(photo_id='{}'
and album_id='{}')
'''.format(photo_id, album_id)
self.db.make_query(query_string)
# get the number of photos in the album after adding them
query_string = '''
SELECT COUNT(photo_id)
FROM photo_album
WHERE album_id='{}';
'''.format(album_id)
# update the count in album
photo_count = self.db.make_query(query_string)[0][0]
print(photo_count)
# update the count in album
query_string = '''
UPDATE album
SET photos = {}
WHERE album_id='{}';
'''.format(int(photo_count), album_id)
self.db.make_query(query_string)
def create_album(self, user_id, title, description):
# one of the current ids, a ten digit number
# 5053198694
created = datetime.datetime.now()
identifier = str(int(uuid.uuid4()))[0:10]
self.db.insert_data(table='album',
album_id=identifier,
user_id='28035310@N00',
views=0,
title=title,
description=description,
photos=0,
date_created=(str(created)),
date_updated=(str(created)))
print('album created with identifier ', identifier)
return identifier
def get_albums_in_range(self, limit=20, offset=0):
"""
Returns the latest 20 albums.
Offset is where you want to start from, so 0 would be from the most recent.
10 from the tenth most recent etc.
"""
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = ('''
select * from album
order by date_created
desc limit {} offset {}
''').format(limit, offset)
q_data = c.execute(query_string)
rtn_dict = {'limit': limit, 'offset': offset, 'photos': []}
"""
I think it may actually be better to layout what fields you want here.
And maybe include all sizes.
"""
data = [dict(ix) for ix in q_data]
# print(data)
for album in data:
album_id = album['album_id']
album_cover = self.get_album_cover(album_id)
# print()
if len(album_cover) > 0:
# print(album_cover[0]['large_square'])
album['large_square'] = album_cover[0]['large_square']
else:
# placeholder image
album['large_square'] = '/static/images/logo.jpg'
# print()
# print(album)
# album['large_square'] = self.get_album_cover(album['album_id'])[
# 0]['large_square']
# print()
# print(album)
a_dict = {}
count = 0
for d in data:
a_dict[count] = d
count += 1
rtn_dict = {'albums': a_dict}
rtn_dict['limit'] = limit
rtn_dict['offset'] = offset
return rtn_dict
def __init__(self):
self.db = Database('eigi-data.db')
self.user_id = '28035310@N00'
self.tag = Tag()
class UploadedPhotos(object):
"""
Handles a table of photos connected to a user.
These represent recently uploaded files that have not had values set for things like title, tags etc.
They will be stored in the table until they are saved.
"""
def __init__(self):
self.db = Database('eigi-data.db')
self.user_id = '28035310@N00'
self.tag = Tag()
def save_photo(self, photo_id, date_uploaded, original, large_square,
exif_data, date_taken):
# print('original', original)
# print('original file path', os.path.join(original))
# print()
# print(os.getcwd() + original)
# print()
# print(ExifUtil.read_exif('test_portrait.jpg'))
# print(ExifUtil.get_datetime_taken('test_portrait.jpg'))
# date_taken = None
exif_id = str(int(uuid.uuid4()))[0:10]
# a photo may not have any exif data
# try:
# date_taken = ExifUtil.get_datetime_taken(os.getcwd() + original)
# # exif_data = ExifUtil.read_exif(os.getcwd() + original)
# # print()
# # print(exif_data)
# # print('date_taken', date_taken)
# # print()
# except Exception as e:
# print('problem reading exif data ', e)
# if exif_data is not None:
# # make into a blob
# exif_data = json.dumps(exif_data)
# # print(exif_data)
# insert exif data
self.db.insert_data(exif_id=exif_id,
exif_data=exif_data,
photo_id=photo_id,
table='exif')
# print(original)
# get_datetime_taken(os.getcwd() + original)
# print(photo_id, self.user_id)
# write to the uploaded_photo table
query_string = '''
insert into upload_photo(photo_id, user_id)
values('{}', '{}')
'''.format(photo_id, self.user_id)
# print(query_string)
self.db.make_query(query_string)
# write to the photo table
self.db.make_query('''
insert into photo(photo_id, user_id, views, date_uploaded, date_taken)
values({},'{}', {}, '{}', '{}')
'''.format(int(photo_id), self.user_id, 0, date_uploaded,
str(date_taken)))
# write to images
self.db.make_query('''
insert into images(photo_id, original, large_square)
values({},'{}','{}')
'''.format(int(photo_id), original, large_square))
# should probably get and store exif data
def get_photos_in_range(self, limit=20, offset=0):
"""
Returns the latest 10 photos.
Offset is where you want to start from, so 0 would be from the most recent.
10 from the tenth most recent etc.
"""
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = (
'''select photo_id, views, photo_title, date_uploaded, date_taken, images.original, images.large_square from photo
join images using(photo_id)
order by date_uploaded
desc limit {} offset {}''').format(limit, offset)
q_data = c.execute(query_string)
rtn_dict = {'limit': limit, 'offset': offset, 'photos': []}
"""
I think it may actually be better to layout what fields you want here.
And maybe include all sizes.
"""
data = [dict(ix) for ix in q_data]
a_dict = {}
count = 0
for d in data:
a_dict[count] = d
count += 1
rtn_dict = {'photos': a_dict}
rtn_dict['limit'] = limit
rtn_dict['offset'] = offset
return rtn_dict
def get_uploaded_photos(self):
# photo_id
# from image the original size
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = ('''
select * from upload_photo
join photo on(photo.photo_id=upload_photo.photo_id)
join images on(images.photo_id=upload_photo.photo_id)
''')
q_data = c.execute(query_string)
data = [dict(ix) for ix in q_data]
print(data)
# print((self.tag.get_photo_tags(data[0]['photo_id'])))
# fix this later so that it doesn't suck
for photo in data:
# print(self.tag.get_photo_tags(photo['photo_id']))
photo['tags'] = []
if photo['photo_title']:
photo['photo_title'] = name_util.make_decoded(
photo['photo_title'])
for tag in self.tag.get_photo_tags(photo['photo_id']):
for key, value in tag.items():
print()
print('key', key, 'value', value)
if key == 'human_readable_tag':
print('wtf', value, photo['tags'])
photo['tags'].append(value)
a_dict = {}
count = 0
for d in data:
a_dict[count] = d
count += 1
rtn_dict = {'photos': a_dict}
return rtn_dict
def get_uploaded_photos_test(self):
# photo_id
# from image the original size
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = ('''
select * from upload_photo
join photo on(photo.photo_id=upload_photo.photo_id)
join images on(images.photo_id=upload_photo.photo_id)
''')
q_data = c.execute(query_string)
data = [dict(ix) for ix in q_data]
print(data)
return {'photos': data}
# cur_dir = os.getcwd()
# a_dict = {}
# count = 0
# for d in data:
# a_dict[count] = d
# count += 1
# # d['original'] = cur_dir + d['original']
# # d['large_square'] = cur_dir + d['large_square']
# rtn_dict = {'photos': a_dict}
# return rtn_dict
def discard_photo(self, photo_id):
"""
Removes the specified photo from photo, upload_photo tables.
Also deletes the files from the disk.
Returns True if the photo is not in the upload_photo table.
"""
# delete the files from the disk, you need to know the path to do this
# which you should get from images
images_data = self.db.make_query('''
select * from images where photo_id = {}
'''.format(photo_id))
if len(images_data) > 0:
print(images_data[0][0:len(images_data[0]) - 1])
current_path = os.getcwd()
photos_on_disk = []
# the last returned element is the photo_id so to avoid that
# I took the slice of everything up to that
for image in images_data[0][0:len(images_data[0]) - 1]:
if image is not None:
photos_on_disk.append(current_path + image)
for photo in photos_on_disk:
try:
os.remove(photo)
except Exception as e:
print('Problem removing file ', e)
else:
print('no data')
# remove photo from table photo
self.db.make_query('''
delete from photo where photo_id = {}
'''.format(photo_id))
# images should cascade delete, but check
# Seems so
# remove from upload_photo table
self.db.make_query('''
delete from upload_photo where photo_id = {}
'''.format(photo_id))
upload_photos = self.get_uploaded_photos()
# print(upload_photos['photos'])
for photo in upload_photos['photos']:
# print()
# print(upload_photos['photos'][photo])
if photo_id in upload_photos['photos'][photo]:
print('PROBLEM?')
return False
return True
# IMPORTANT!
# you should test this later after implementing adding tags to uploaded photos
# remove from tags? i don't think you need to? you can have orphaned tags
def update_title(self, photo_id, new_title):
self.db.make_query('''
update photo
set photo_title = '{}'
where photo_id = {}
'''.format(new_title, photo_id))
# check title has been updated
data = self.db.make_query('''
select * from photo where photo_id = {}
'''.format(photo_id))
if len(data) > 0:
if data[0][3] == new_title:
return True
return False
def add_to_photostream(self, data):
print('PROBLEM DATA ', data)
# get the photo_id for eatch photo
for photo in data.values():
# set the date_posted to the current datetime
date_posted = datetime.datetime.now()
# get the photo_id
print(photo['photo_id'], date_posted)
if photo['photo_title'] is None:
check_title = self.db.make_query('''
select photo_title from photo where photo_id = {}
'''.format(photo['photo_id']))
if len(check_title) < 1:
print('here be problems?')
self.db.make_query('''
update photo
set photo_title = ''
where photo_id = {}
'''.format(photo['photo_id']))
# update the date_posted column in the table photo
self.db.make_query('''
update photo
set date_posted = '{}'
where photo_id = {}
'''.format(date_posted, photo['photo_id']))
test_data = self.db.make_query('''
select date_posted from photo
where photo_id = {}
'''.format(photo['photo_id']))
if test_data:
# remove the photo from the table upload_photo
self.db.make_query('''
delete from upload_photo
where photo_id = {}
'''.format(photo['photo_id']))
def add_all_to_album(self, album_id):
# get all uploaded photos
uploaded_photos = self.db.make_query('''
select * from upload_photo
''')
print(uploaded_photos)
for photo in uploaded_photos:
photo_id = photo[0]
date_posted = datetime.datetime.now()
# set published datetime
self.db.make_query('''
update photo
set date_posted = "{}"
where photo_id = {}
'''.format(date_posted, photo_id))
print(photo_id)
# db.insert_data(
# table='tag',
# tag_name=new_tag,
# user_id='28035310@N00'
# )
self.db.make_query('''
insert into photo_album (photo_id, album_id)
values ('{}', '{}')
'''.format(photo_id, album_id))
# get photo count for album
photo_count = self.db.make_query('''
select photos from album where album_id = '{}'
'''.format(album_id))
print(photo_count)
photo_count = int(photo_count[0][0]) + 1
self.db.make_query('''
update album
set photos = {}
where album_id = '{}'
'''.format(photo_count, album_id))
# DANGER!
self.db.make_query('''
delete from upload_photo
''')
import os
import datetime
from database_interface import Database
from resize_photo import PhotoUtil
db = Database('eigi-data.db')
photo_ids = db.make_query('''
select photo_id from photo
''')
# print(photo_ids)
for photo in photo_ids:
print()
photo_id = photo[0]
# read date taken for the photo
# 30081941117
date_uploaded = db.make_query('''
select date_uploaded from photo where photo_id = "{}"
'''.format(photo_id))
date_uploaded = date_uploaded[0][0]
dt_obj = datetime.datetime.fromtimestamp(int(date_uploaded))
filename = db.make_query('''
select original, large_square from images where photo_id = "{}"
def __init__(self):
self.db = Database('eigi-data.db')
self.tag = Tag()
self.album = Album()
class AlterDB(object):
def __init__(self):
self.db = Database('eigi-data.db')
def temp_tag_table(self):
resp = self.db.make_query('''
CREATE TABLE IF NOT EXISTS test_tag (
tag_name TEXT NOT NULL UNIQUE,
user_id TEXT NOT NULL,
photos INT,
PRIMARY KEY (tag_name, user_id)
FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
''')
print(resp)
def recreate_tag_table(self):
resp = self.db.make_query('''
CREATE TABLE IF NOT EXISTS tag (
tag_name TEXT NOT NULL UNIQUE,
user_id TEXT NOT NULL,
photos INT,
PRIMARY KEY (tag_name, user_id)
FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
''')
print(resp)
def copy_data(self):
data = self.db.get_query_as_list('''
select * from tag
''')
for tag in data:
print()
print(tag)
count = self.db.get_query_as_list('''
select count(tag_name)
from photo_tag
where tag_name = '{}'
'''.format(tag['tag_name']))[0]['count(tag_name)']
print(count)
print()
self.db.make_query('''
insert into test_tag (tag_name, user_id, photos)
values('{}', '{}', {})
'''.format(tag['tag_name'], '28035310@N00', count))
def make_upload_table(self):
self.db.make_query('''
CREATE TABLE upload_photo(
photo_id int
primary key unique not null,
user_id text not null,
foreign key(user_id)
references user(user_id) on delete cascade )
''')
def drop_table(self, table_name):
self.db.make_query('''
drop table if exists '{}'
'''.format(table_name))
def copy_back_to_tag(self):
data = self.db.get_query_as_list('''
select * from test_tag
''')
for tag in data:
print()
print(tag)
count = self.db.get_query_as_list('''
select count(tag_name)
from photo_tag
where tag_name = '{}'
'''.format(tag['tag_name']))[0]['count(tag_name)']
print(count)
print()
self.db.make_query('''
insert into tag (tag_name, user_id, photos)
values('{}', '{}', {})
'''.format(tag['tag_name'], '28035310@N00', count))
class Tag(object):
def __init__(self):
self.db = Database('eigi-data.db')
def check_all_tag_photo_counts(self):
"""
Gets a count of all the photos associated with a tag.
Checks that the photos column in tag is up to date.
"""
data = self.db.get_query_as_list(
'''
select * from tag
'''
)
for tag in data:
print()
print(tag)
# query for the number of photos using the tag
# compare it to the number in the photos column
# update if necessary
query_count = self.db.get_query_as_list(
'''
select count(tag_name)
from photo_tag
where tag_name = "{}"
'''.format(tag['tag_name'])
)
if query_count[0]['count(tag_name)'] == tag['photos']:
print('OK', 'actual photos number with tag',
query_count[0]['count(tag_name)'], 'in photos column', tag['photos'])
else:
print('MISSMATCH IN PHOTOS AND PHOTOS WITH TAG\n', 'actual photos number with tag',
query_count[0]['count(tag_name)'], 'in photos column', tag['photos'])
tag_name = tag['tag_name']
count = query_count[0]['count(tag_name)']
break
print('\nDONE NO PROBLEMS!')
def update_photo_count(self, tag_name):
"""
Updates the photo count for the given tag.
"""
# query_count = self.db.get_query_as_list(
# '''
# select count(tag_name)
# from photo_tag
# where tag_name = "{}"
# '''.format(tag_name)
# )
count = self.get_photo_count_by_tag(tag_name)
self.db.make_query(
'''
update tag
set photos = {}
where tag_name = "{}"
'''.format(count, tag_name)
)
def tag_photo_count(self):
"""
Gets a count of all the photos associated with a tag.
Checks that the photos column in tag is up to date.
"""
data = self.db.get_query_as_list(
'''
select * from tag
'''
)
# print(data)
for tag in data:
print()
print(tag)
# query for the number of photos using the tag
# compare it to the number in the photos column
# update if necessary
query_count = self.db.get_query_as_list(
'''
select count(tag_name)
from photo_tag
where tag_name = "{}"
'''.format(tag['tag_name'])
)
# print(query_count)
if query_count[0]['count(tag_name)'] == tag['photos']:
print('OK', 'actual photos number with tag',
query_count[0]['count(tag_name)'], 'in photos column', tag['photos'])
else:
print('MISSMATCH IN PHOTOS AND PHOTOS WITH TAG\n', 'actual photos number with tag',
query_count[0]['count(tag_name)'], 'in photos column', tag['photos'])
break
def check_forbidden(self, tag_name):
print('hello from check_forbidden')
print(tag_name)
forbidden = [";", "/", "?", ":", "@", "=", "&", '"', "'", "<", ">",
"#", "%", "{", "}", "|", "\\", "/", "^", "~", "[", "]", "`"]
for char in tag_name:
if char in forbidden:
return urllib.parse.quote(tag_name, safe='')
return tag_name
def decode_tag(self, tag_name):
return urllib.parse.unquote(tag_name)
def get_all_tags(self):
# as a list of dict values
tag_data = self.db.get_query_as_list(
"SELECT tag_name, photos FROM tag order by tag_name"
)
rtn_dict = {
}
count = 0
for tag in tag_data:
rtn_dict[count] = tag
tag_name = tag['tag_name']
# adding the number of photos with the tag
rtn_dict[count]['photos'] = tag['photos']
rtn_dict[count]['human_readable_tag'] = self.decode_tag(
tag['tag_name'])
count += 1
return rtn_dict
def get_photo_tags(self, photo_id):
"""
Get the tags for a single photo.
select photo.photo_id, photo.photo_title, photo_tag.tag_name from photo
join photo_tag on(photo_tag.photo_id=photo.photo_id)
where photo.photo_id={}
"""
query_string = '''
select photo_tag.tag_name from photo
join photo_tag on(photo_tag.photo_id=photo.photo_id)
where photo.photo_id={}
'''.format(photo_id)
# so an array of tags would be ok
tag_data = self.db.get_query_as_list(query_string)
for tag in tag_data:
# print(self.decode_tag(tag['tag_name']))
tag['human_readable_tag'] = self.decode_tag(tag['tag_name'])
# print(tag_data)
return tag_data
def get_photo_count_by_tag(self, tag_name):
query_string = '''
select count(photo_id) from photo
join photo_tag using(photo_id)
where tag_name = "{}"
'''.format(tag_name)
photo_count = self.db.get_query_as_list(query_string)
if len(photo_count) > 0:
return photo_count[0]['count(photo_id)']
def get_photos_by_tag(self, tag_name):
"""
Get all the photos that are associated with a particular tag.
I will need to handle spaces.
"""
# q_data = None
query_string = '''
select photo_id, photo_title, views, tag_name, large_square from photo
join photo_tag using(photo_id)
join images using(photo_id)
where tag_name = "{}"
order by views desc
'''.format(tag_name)
tag_data = self.db.get_query_as_list(query_string)
# print(tag_data)
rtn_dict = {
'tag_info': {'number_of_photos': self.get_photo_count_by_tag(tag_name)}
}
count = 0
for t in tag_data:
rtn_dict[count] = t
rtn_dict[count]['human_readable_tag'] = name_util.make_decoded(
rtn_dict[count]['tag_name'])
count += 1
return rtn_dict
def get_tag(self, tag_name):
tag_data = self.db.make_query(
'''
select tag_name from tag where tag_name = "{}"
'''.format(tag_name)
)
return tag_data
def check_photo_tag(self, tag_name):
data = self.db.make_query(
'''select * from photo_tag where tag_name = "{}" '''
.format(tag_name))
if len(data) > 0:
return True
return False
def remove_tag_name(self, tag_name):
if '%' in tag_name:
tag_name = urllib.parse.quote(tag_name, safe='')
self.db.make_query(
'''
delete from tag where tag_name = "{}"
'''.format(tag_name)
)
self.db.make_query(
'''
delete from photo_tag where tag_name = "{}"
'''.format(tag_name)
)
self.update_photo_count(tag_name)
def delete_tag(self, tag_name):
# you have to remove the tag from the tag table
self.db.delete_rows_where('tag', 'tag_name', tag_name)
# and also in photo_tag
self.db.delete_rows_where('photo_tag', 'tag_name', tag_name)
if not self.get_tag(tag_name) and not self.check_photo_tag(tag_name):
return True
else:
return False
def clean_tags(self):
forbidden = [' ', ';']
# as a list of dict values
tag_data = self.db.get_query_as_list("SELECT * FROM tag")
for tag in tag_data:
print(tag['tag_name'], tag['tag_name'] in forbidden)
if tag['tag_name'] in forbidden:
print('please just ket me die already, ', tag['tag_name'])
self.remove_tag_name(tag['tag_name'])
tag_data = self.db.get_query_as_list("SELECT * FROM photo_tag")
for tag in tag_data:
print(tag['tag_name'], tag['tag_name'] in forbidden)
if tag['tag_name'] in forbidden:
print('please just ket me die already, ', tag['tag_name'])
self.remove_tag_name(tag['tag_name'])
def remove_tags_from_photo(self, photo_id, tag_list):
for tag in tag_list:
print(tag)
# if the tag isn't present it will just fail silently
resp = self.db.make_query(
'''
delete from photo_tag
where photo_id = {}
and tag_name = "{}"
'''.format(photo_id, tag)
)
print(resp)
self.update_photo_count(tag)
def replace_tags(self, photo_id, tag_list):
"""
Replaes the tags attached to a photo with new tags.
"""
# get all the tags attached to the photo
current_tags = self.db.make_query(
'''
select * from photo_tag where photo_id = {}
'''.format(photo_id)
)
print(current_tags)
# remove the current tags
self.db.make_query(
'''
delete from photo_tag where photo_id = {}
'''.format(photo_id)
)
for tag in tag_list:
# add tags in the tag_list
self.db.make_query(
'''
insert into photo_tag (photo_id, tag_name)
values ({}, "{}")
'''.format(photo_id, tag)
)
self.update_photo_count(tag)
def add_tags_to_photo(self, photo_id, tag_list):
print('\nHello from add_tags_to_photo, the tag list is: ', tag_list)
# for i in range(len(tag_list)):
# tag_list[i] = urllib.parse.quote(tag_list[i], safe='')
# print(tag_list[i],
# 'parsed', urllib.parse.unquote(tag_list[i]), tag_list)
# for each tag
# check if the tag is in the database already
# if it is not then add it to the tag table
for tag in tag_list:
# will return None if the tag is not in the tag table
# tag_name is the column name
data = self.db.get_row('tag', 'tag_name', tag)
print('data is', data)
if data is None:
print('\nthat value {} is not in the db\n'.format(tag))
self.db.make_query(
'''
insert into tag (tag_name, user_id, photos)
values ("{}", "{}", {})
'''.format(
tag,
'28035310@N00',
self.get_photo_count_by_tag(tag)
)
)
# self.db.insert_data(
# table='tag',
# tag_name=tag,
# user_id='28035310@N00',
# photos=self.get_photo_count_by_tag(tag)
# )
print('\nshould be added now...\n')
if self.db.get_row('tag', 'tag_name', tag):
print('\nadded tag, ', tag, '\n')
# The tag is now in the database.
# Does return 0 if there are no pictures using the tag.
# print(self.get_photo_count_by_tag(tag))
# add the tag to the table photo_tag
# resp = self.db.insert_data(
# table='photo_tag',
# photo_id=photo_id,
# tag_name=tag,
# )
self.db.make_query(
'''
insert into photo_tag (photo_id, tag_name)
values ({}, "{}")
'''.format(photo_id, tag)
)
data = self.db.make_query(
'''
select * from photo_tag where photo_id = {}
'''.format(photo_id)
)
tags_in_data = []
if len(data) > 0:
for tag in data:
tags_in_data.append(tag[1])
print(tags_in_data)
for tag in tag_list:
if tag not in tags_in_data:
return False
else:
self.update_photo_count(tag)
return True
def update_tag(self, new_tag, old_tag):
print('hello from update_tag - passed values, ', new_tag, old_tag)
# check if new tag exists
test = self.db.make_query(
'''
select * from tag where tag_name = "{}"
'''.format(new_tag)
)
# print(test)
if not test:
# if the tag doesn't exist already then update it
# existing tag to the new tag
self.db.make_query(
'''
update tag
set tag_name = "{}"
where tag_name = "{}"
'''.format(new_tag, old_tag)
)
# if new tag exists or not you have to update photo_tag
self.db.make_query(
'''
update photo_tag
set tag_name = "{}"
where tag_name = "{}"
'''.format(new_tag, old_tag)
)
# update the photo count for the tag table
self.update_photo_count(new_tag)
if self.get_tag(new_tag) and not self.get_tag(old_tag):
return True
else:
return False
class Photos(object):
def __init__(self):
self.db = Database('eigi-data.db')
self.tag = Tag()
self.album = Album()
@classmethod
def url_decode_tag(cls, tag_name):
return urllib.parse.unquote(tag_name)
@classmethod
def needs_decode(cls, a_str):
# print(a_str)
if a_str is None:
return ""
if '%' in a_str:
return cls.url_decode_tag(a_str)
else:
return a_str
def get_photos_in_range(self, limit=20, offset=0):
"""
Returns the latest 20 photos.
Offset is where you want to start from, so 0 would be from the most recent.
10 from the tenth most recent etc.
"""
# get number of photos in database total
num_photos = self.db.make_query('''
select count(photo_id)
from photo
''')[0][0]
if offset > num_photos:
offset = num_photos - (num_photos % 20)
q_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = (
'''select photo_id, views, photo_title, date_posted, date_taken, images.original, images.large_square from photo
join images using(photo_id)
order by date_posted
desc limit {} offset {}''').format(limit, offset)
q_data = c.execute(query_string)
rtn_dict = {'limit': limit, 'offset': offset, 'photos': []}
"""
I think it may actually be better to layout what fields you want here.
And maybe include all sizes.
"""
data = [dict(ix) for ix in q_data]
# print()
for photo in data:
# print('HERE', photo)
photo['photo_title'] = self.needs_decode(photo['photo_title'])
# print(photo)
# print()
a_dict = {}
count = 0
for d in data:
a_dict[count] = d
count += 1
rtn_dict = {'photos': a_dict}
rtn_dict['limit'] = limit
rtn_dict['offset'] = offset
return rtn_dict
def get_date_posted(self, photo_id):
photo_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
query_string = ('''
select date_posted from photo
where photo_id={}
'''.format(photo_id))
photo_data = [x for x in c.execute(query_string)]
if len(photo_data) < 1:
return None
else:
return photo_data[0][0]
def get_next_photo(self, photo_id):
"""
you need the date that the current was uploaded
"""
date_posted = self.get_date_posted(photo_id)
photo_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
query_string = ('''
select * from photo join images using(photo_id)
where date_posted > '{}'
order by date_posted asc limit 1
'''.format(date_posted))
try:
photo_data = [x for x in c.execute(query_string)]
except Exception as e:
print('Problem in getting next photo, ', e)
# print(photo_data)
if len(photo_data) < 1:
return None
else:
# print(photo_data)
# the photo_id of the next photo
return photo_data[0][0]
def get_previous_photo(self, photo_id):
date_posted = self.get_date_posted(photo_id)
print(date_posted)
photo_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
# Problem here was that it was treating datetime as something other than a string
next_string = ('''
select * from photo join images using(photo_id)
where date_posted < '{}'
order by date_posted desc limit 1
'''.format(date_posted))
photo_data = [x for x in c.execute(next_string)]
if len(photo_data) < 1:
return None
else:
return photo_data[0][0]
def get_photo(self, photo_id):
# update view count
self.db.make_query('''
update photo
set views = views + 1
where photo_id = {}
'''.format(photo_id))
rtn_data = {}
photo_data = None
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
c.row_factory = sqlite3.Row
query_string = (
"select * from photo join images using(photo_id) where photo_id={}"
.format(photo_id))
photo_data = [dict(x) for x in c.execute(query_string)]
next_photo = self.get_next_photo(photo_id)
prev_photo = self.get_previous_photo(photo_id)
print('PHOTO DATA\n', photo_data[0]['photo_id'])
# prevent None being retunred when the last pictures are reached
if next_photo is None:
next_photo = photo_data[0]['photo_id']
if prev_photo is None:
prev_photo = photo_data[0]['photo_id']
"""
Get the tags for the current photo.
"""
tag_data = self.tag.get_photo_tags(photo_id)
album_data = self.album.get_containing_album(photo_id)
# print('tags ', tag_data)
if len(album_data) > 0:
# print('album_id', album_data[0]['album_id'])
album_id = album_data[0]['album_id']
album_cover = self.album.get_album_cover(album_id)
# print(album_cover)
album_data[0]['large_square'] = album_cover[0]['large_square']
# print('album_data ', album_data)
if len(photo_data) > 0:
# becasuse it is a list containing a dict
photo_data = photo_data[0]
rtn_data = {
'photo_id': photo_data['photo_id'],
'title': self.needs_decode(photo_data['photo_title']),
'views': photo_data['views'],
'tags': tag_data,
'containing_album': album_data,
'original': photo_data['original'],
'next': next_photo,
'previous': prev_photo
}
return rtn_data
def get_photos_range(self, table_name, start, stop):
with sqlite3.connect(self.db.db_name) as connection:
c = connection.cursor()
return [
x
for x in c.execute("SELECT * FROM photos ".format(table_name))
]
def update_title(self, photo_id, new_title):
resp = self.db.make_query('''
update photo
set photo_title = '{}'
where photo_id = '{}'
'''.format(new_title, photo_id))
# print(resp)
def delete_photo(self, photo_id):
# Update tag count, data for later update
tags = self.tag.get_photo_tags(photo_id)
# check if photo is in an album
album_check = self.db.make_query('''
select * from photo_album where photo_id = '{}'
'''.format(photo_id))
print(album_check)
# # if the photo is in an album decrement the value
# # decrement the value of the album count
if len(album_check) > 0:
for album in album_check:
print(album[1])
self.db.make_query('''
update album
set photos = photos - 1
where album_id = '{}'
'''.format(album[1]))
# delete photo from photo_album
self.db.make_query('''
delete from photo_album where photo_id = '{}'
'''.format(photo_id))
self.db.make_query('''
delete from photo where photo_id = '{}'
'''.format(photo_id))
print(album_check)
# update photo_tag count after deleting the photo
for tag in tags:
print('tag name', tag['tag_name'])
self.tag.update_photo_count(tag['tag_name'])
# some change with flask?
# for some reason it accepts secret_key but nothing else
# without doing this
# app.config['USERNAME'] = '******'
# app.config['PASSWORD'] = '******'
# so secret key is built in from the get go
# app.config['SECRET_KEY'] = 'secret'
app.config[
'SECRET_KEY'] = b'\xef\x03\xc8\x96\xb7\xf9\xf3^\x16\xcbz\xd7\x83K\xfa\xcf'
"""
$ python -c 'import os; print(os.urandom(16))'
b'_5#y2L"F4Q8z\n\xec]/'
"""
db = Database('eigi-data.db')
p = Photos()
a = Album()
t = Tag()
up = UploadedPhotos()
"""
$ export FLASK_APP=app.py
$ export FLASK_ENV=development
Make it reload on changes:
$ export FLASK_DEBUG=1
$ flask run
lsof -w -n -i tcp:5000
kill -9 processId
"""