def get_images(self, event_id):
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT * FROM IMAGETABLE where event_id =%s ORDER BY image_id;"""
try:
cursor.execute(query,(event_id,))
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
event_id = fetched_data[0]
image_id = fetched_data[1]
date = fetched_data[2]
content = fetched_data[3]
image_row = [(Image(event_id, image_id, content, date))]
images = image_row
for event_id, image_id, date, content in cursor:
image_row = [(Image(event_id, image_id, content, date))]
images += image_row
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return images
def get_timeinfo(self):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT map_id,decade,year,share_date,content_type,content_header FROM TIMETABLE ORDER BY map_id;"""
try:
cursor.execute(query)
fetched_data = cursor.fetchone()
connection.commit()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
map_id = fetched_data[0]
decade = fetched_data[1]
year = fetched_data[2]
share_date = fetched_data[3]
content_type = fetched_data[4]
content_header = fetched_data[5]
timeline = [(map_id, (Time(title, content, date, place)))]
for title, content, date, place, event_id in cursor:
events = [(event_id, (Event(decade, year, share_date,
content_type, content_header)))]
except connection.Error as error:
print(error)
connection.close()
return timeline
def get_events(self):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT * FROM EVENTTABLE ORDER BY event_id;"""
try:
cursor.execute(query)
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
title = fetched_data[0]
date = fetched_data[1]
place = fetched_data[2]
event_id = fetched_data[3]
events = [(Event(title, event_id, date, place))]
for row in cursor:
title, date, place, event_id = row
events_row = [(Event(title, event_id, date, place))]
events += events_row
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return events
def get_time(self, map_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT map_id,decade,year,share_date,content_type,content_header FROM TIMETABLE WHERE map_id= %s;"""
try:
cursor.execute(query, (map_id, ))
fetched_data = cursor.fetchone()
connection.commit()
if fetched_data is None:
status = 'There is no timeinfo '
connection.close()
return None
else:
map_id = fetched_data[0]
decade = fetched_data[1]
year = fetched_data[2]
share_date = fetched_data[3]
content_type = fetched_data[4]
content_header = fetched_data[5]
time = Time(decade, year, share_date, content_type,
content_header)
except connection.Error as error:
print(error)
connection.close()
return time
def get_document(self, document_id, event_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT * FROM DOCUMENTTABLE WHERE event_id= %s AND document_id = %s;"""
try:
cursor.execute(query, (event_id, document_id))
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
else:
title = fetched_data[0]
date = fetched_data[1]
content = fetched_data[2]
event_id = fetched_data[3]
document_id = fetched_data[4]
document = Document(event_id, document_id, content, title,
date)
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return document
def delete_image(self, image_id, event_id):
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """DELETE FROM IMAGETABLE WHERE event_id = %s AND image_id = %s"""
try:
cursor.execute(query,(event_id,image_id,))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def delete_timeinfo(self, map_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """DELETE FROM TIMETABLE WHERE map_id = %s"""
try:
cursor.execute(query, (map_id, ))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def update_image_id(self, image_id, event_id, new_id):
image = current_app.store_images.get_image(image_id,event_id)
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """UPDATE IMAGETABLE SET image_id = %s WHERE event_id = %s """
try:
cursor.execute(query,(int(new_id), event_id,))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def update_document_id(self, document_id, event_id, new_id):
document = current_app.store_documents.get_document(
document_id, event_id)
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """UPDATE DOCUMENTTABLE SET document_id = %s WHERE event_id = %s AND document_id = %s """
try:
cursor.execute(query, (new_id, event_id, document_id))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def delete_document(self, document_id, event_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """DELETE FROM DOCUMENTTABLE WHERE event_id = %s AND document_id = %s"""
try:
cursor.execute(query, (
event_id,
document_id,
))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def update_event_id(self, event_id, new_id):
event = current_app.store.get_event(event_id)
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """UPDATE EVENTTABLE SET event_id = %s WHERE event_id = %s """
try:
cursor.execute(query, (
int(new_id),
event_id,
))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def update_document(self, document, event_id, document_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """UPDATE DOCUMENTTABLE SET title = %s,date = %s, content = %s WHERE event_id = %s AND document_id = %s"""
try:
cursor.execute(query, (
document.title,
document.date,
document.content,
event_id,
document_id,
))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def add_event(self, event):
self.last_event_id += 1
self.events[self.last_event_id] = event
event._id = self.last_event_id
# username = current_app.user.username;
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
# query = """INSERT INTO EVENTTABLE (title,date,place,username) VALUES (%s, %s, %s, %s)"""
query = """INSERT INTO EVENTTABLE (title,date,place) VALUES (%s, %s, %s)"""
try:
# cursor.execute(query, (event.title, event.date, event.place, username))
cursor.execute(query, (event.title, event.date, event.place))
self.last_key = cursor.lastrowid
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def update_timeinfo(self, time, map_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """UPDATE TIMETABLE SET decade = %d, year = %d, share_date = %d, content_type= %s,content_header= %s WHERE map_id = %s"""
try:
cursor.execute(query, (
map_id,
time.decade,
time.year,
time.share_date,
time.content_type,
time.content_header,
))
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def get_image_id(self, event_id):
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT COUNT(*) FROM IMAGETABLE WHERE event_id = %s;"""
try:
cursor.execute(query,(event_id,))
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
else:
count_image = fetched_data[0]
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return count_image
def get_total_events(self):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT COUNT(*) FROM EVENTTABLE;"""
try:
cursor.execute(query)
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
total_count = fetched_data
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return total_count
def add_timeinfo(self, time):
self.last_map_id += 1
self.timeline[self.last_map_id] = time
map._id = self.last_map_id
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
map_id = time.map_id
decade = time.decade
year = time.year
share_date = time.share_date
content_type = time.content_type
content_header = time.content_header
query = """INSERT INTO TIMETABLE (map_id,decade,year,share_date,content_type,content_header) VALUES (%s, %d, %d, %d, %s, %s)"""
try:
cursor.execute(
query, (time.map_id, time.decade, time.year, time.share_date,
time.content_type, time.content_header))
connection.commit()
self.last_key = cursor.lastrowid
except connection.Error as error:
print(error)
connection.close()
def get_image(self, image_id, event_id):
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT * FROM IMAGETABLE WHERE event_id= %s AND image_id = %s;"""
try:
cursor.execute(query,(event_id,image_id,))
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
else:
event_id = fetched_data[0]
image_id = fetched_data[1]
date = fetched_data[2]
content = fetched_data[3]
image = Image(event_id, image_id, date, content)
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return image
def add_image(self, image):
self.last_image_id = self.get_image_id(image.event_id)
self.last_image_id += 1
self.images[self.last_image_id] = image
image._id = self.last_image_id
dsn = get_connection_for_events();
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
date = image.date
event_id = image.event_id
content = image.content
image_id = image.image_id
# username = current_app.user.username;
# query = """INSERT INTO IMAGETABLE (date,event_id,content,image_id,username) VALUES (%s, %s, %s, %s, %s)"""
query = """INSERT INTO IMAGETABLE (date,event_id,content,image_id) VALUES (%s, %s, %s, %s)"""
try:
# cursor.execute(query, (date, event_id, content, image_id, username))
cursor.execute(query, (date, event_id, content, image_id))
self.last_key = cursor.lastrowid
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def get_event(self, event_id):
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
query = """ SELECT * FROM EVENTTABLE WHERE event_id= %s;"""
try:
cursor.execute(query, (event_id, ))
fetched_data = cursor.fetchone()
if fetched_data is None:
status = 'There is no event '
connection.close()
return None
else:
title = fetched_data[0]
date = fetched_data[1]
place = fetched_data[2]
event_id = fetched_data[3]
event = [(Event(title, event_id, date, place))]
connection.commit()
except connection.Error as error:
print(error)
connection.close()
return event
def add_document(self, document):
self.last_document_id += 1
self.documents[self.last_document_id] = document
document._id = self.last_document_id
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
title = document.title
date = document.date
event_id = document.event_id
document_id = document.document_id
content = document.content
# username = current_app.user.username;
# query = """INSERT INTO DOCUMENTTABLE (title,date,content,event_id,document_id,username) VALUES (%s, %s, %s, %s, %s, %s)"""
query = """INSERT INTO DOCUMENTTABLE (title,date,content,event_id,document_id) VALUES (%s, %s, %s, %s, %s)"""
try:
# cursor.execute(query, (title,date,content,event_id,document_id, username))
cursor.execute(query,
(title, date, content, event_id, document_id))
self.last_key = cursor.lastrowid
connection.commit()
except connection.Error as error:
print(error)
connection.close()
def init_db():
dsn = get_connection_for_events()
with dbapi2.connect(dsn) as connection:
cursor = connection.cursor()
"""cursor.execute(DROP TABLE IF EXISTS USERTABLE )"""
# query = """DROP TABLE IF EXISTS DOCUMENTTABLE """
# cursor.execute(query)
# query = """DROP TABLE IF EXISTS IMAGETABLE """
# cursor.execute(query)
# query = """DROP TABLE IF EXISTS EVENTTABLE """
# cursor.execute(query)
cursor.execute("""DROP TABLE IF EXISTS TIMETABLE """)
cursor.execute("""DROP TABLE IF EXISTS MMAPTABLE""") #main map table
try:
cursor.execute(
"""CREATE TABLE IF NOT EXISTS EVENTTABLE (title varchar(30), date varchar(10), place varchar(40), event_id serial primary key)"""
)
cursor.execute(
"""CREATE TABLE IF NOT EXISTS IMAGETABLE (event_id int references eventtable ON DELETE CASCADE , image_id int not null, date varchar(10), content text, primary key (event_id,image_id))"""
)
cursor.execute(
"""CREATE TABLE IF NOT EXISTS DOCUMENTTABLE (title varchar(30), date varchar(10), content text, event_id int references eventtable ON DELETE CASCADE, document_id int not null, primary key (event_id, document_id))"""
)
cursor.execute(
"""CREATE TABLE IF NOT EXISTS TIMETABLE (map_id varchar(40) primary key references TIMEMAPTABLE(mapID) on delete cascade, decade int not null,year int not null,share_date date not null,content_type varchar(40),content_header varchar(40))"""
)
# cursor.execute("""INSERT INTO TIMETABLE (map_id,decade,year,share_date,content_type,content_header) VALUES ('1',1960,1963,'1963-08-22','text','X-15 aircraft')""" )
# cursor.execute("""INSERT INTO TIMETABLE (map_id,decade,year,share_date,content_type,content_header) VALUES ('2',2010,2016,'1963-06-03','text','Mohammed Morsi')""" )
# cursor.execute("""INSERT INTO MMAPTABLE (post_id,user_id,lat,long,photo,video,document) VALUES (1,1,'41.1055936','29.0253398','https://upload.wikimedia.org/wikipedia/commons/thumb/3/3c/ITU-Lecture-Hall.JPG/270px-ITU-Lecture-Hall.JPG')""" )
connection.commit()
except connection.Error as error:
print(error)
connection.close()
try:
conn = getConnection()
userCursor = conn.cursor()
userCursor.execute(
"""CREATE TABLE IF NOT EXISTS USERTABLE (userId SERIAL PRIMARY KEY,username varchar(20) UNIQUE,password varchar(20), email varchar(40),name varchar(20),surname varchar(20))"""
)
conn.commit()
except conn.Error as userError:
print(userError)
conn.close()
try:
timeMapconnection = getConnection()
timeMapCursor = timeMapconnection.cursor()
timeMapCursor.execute("""DROP TABLE IF EXISTS TIMEMAPTABLE """)
timeMapCursor.execute(
"""CREATE TABLE IF NOT EXISTS TIMEMAPTABLE (mapID varchar(40) primary key, number_of_shared_item int not null)"""
)
timeMapCursor.execute(
"""INSERT INTO TIMEMAPTABLE (mapID,number_of_shared_item) VALUES ('0',60)"""
)
timeMapCursor.execute(
"""INSERT INTO TIMEMAPTABLE (mapID,number_of_shared_item) VALUES ('1',190)"""
)
timeMapCursor.execute(
"""INSERT INTO TIMEMAPTABLE (mapID,number_of_shared_item) VALUES ('2',160)"""
)
timeMapCursor.execute(
"""INSERT INTO TIMEMAPTABLE (mapID,number_of_shared_item) VALUES ('3',1000)"""
)
timeMapconnection.commit()
except timeMapconnection.Error as timeMapError:
print(timeMapError)
timeMapconnection.close()
try:
userMapConnection = getConnection()
userMapCursor = userMapConnection.cursor()
userMapCursor.execute(
"""CREATE TABLE IF NOT EXISTS USERMAPTABLE (userMap_id INT,user_id varchar(20),mapInformation varchar(250),locationLabel varchar(30),lat FLOAT(10) NOT NULL,lng FLOAT(10) NOT NULL)"""
)
userMapConnection.commit()
except userMapConnection.Error as userMapError:
print(userMapError)
userMapConnection.close()
try:
socialTableconn = getConnection()
socialTablecursor = socialTableconn.cursor()
socialTablecursor.execute(
"""CREATE TABLE IF NOT EXISTS FRIENDSTABLE (friendRecordId SERIAL PRIMARY KEY ,user_id varchar(20) references USERTABLE(username) on delete cascade,firends_id varchar(20) references USERTABLE(username) on delete cascade,status varchar(20))"""
)
socialTableconn.commit()
except socialTableconn.Error as socialError:
print(socialError)
socialTableconn.close()
try:
requestTableconn = getConnection()
requestTableCursor = requestTableconn.cursor()
requestTableCursor.execute(
"""CREATE TABLE IF NOT EXISTS REQUESTTABLE (requestId SERIAL PRIMARY KEY, requester varchar(20) references USERTABLE(username) on delete cascade, requested varchar(20) references USERTABLE(username) on delete cascade)"""
)
requestTableconn.commit()
except requestTableconn.Error as requestError:
print(requestError)
try:
messageTableConn = getConnection()
messageTableCursor = messageTableConn.cursor()
messageTableCursor.execute(
"""CREATE TABLE IF NOT EXISTS MESSAGETABLE (messageId SERIAL PRIMARY KEY,user_id varchar(20) references USERTABLE(username) on delete cascade,firends_id varchar(20) references USERTABLE(username) on delete cascade,content varchar(300),status varchar(20))"""
)
messageTableConn.commit()
except messageTableConn.Error as messageError:
print(messageError)
messageTableConn.close()
try:
commentTableConn = getConnection()
commentTableCursor = commentTableConn.cursor()
commentTableCursor.execute(
"""CREATE TABLE IF NOT EXISTS COMMENTTABLE (commentId SERIAL PRIMARY KEY,userId INT references USERTABLE(userId) on delete cascade,user_name varchar(20) references USERTABLE(username) on delete cascade,friendUsername varchar(20) references USERTABLE(username) on delete cascade,content varchar(300))"""
)
commentTableConn.commit()
except commentTableConn.Error as messageError:
print(messageError)
commentTableConn.close()
try:
notificationTableConn = getConnection()
notificationTableCursor = notificationTableConn.cursor()
notificationTableCursor.execute(
"""CREATE TABLE IF NOT EXISTS NOTIFICATIONTABLE (notificationId SERIAL PRIMARY KEY,user_name varchar(20) references USERTABLE(username) on delete cascade,friendUsername varchar(20) references USERTABLE(username) on delete cascade,messageId INT references MESSAGETABLE(messageId),commentId INT references COMMENTTABLE(commentId))"""
)
notificationTableConn.commit()
except notificationTableConn.Error as messageError:
print(messageError)
notificationTableConn.close()