def add_appt(adv, stud, adv_email, stud_email, date, start, end, table = DEFAULT_TABLE):
if is_repeat(date, start, end):
print "Meeting conflict - new appointment not added."
return 0
else:
query = '''
INSERT INTO %s (
advisor_name,
student_name,
advisor_email,
student_email,
appointment_date,
appointment_start_time,
appointment_end_time)
''' % table
query += '''
VALUES (
"%s",
"%s",
"%s",
"%s",
"%s",
"%s",
"%s");
''' % (adv, stud, adv_email, stud_email, date, start, end);
print query
print execute(query)
print execute('SELECT * FROM %s;' % (table))
unique_id = get_unique_id(date, start)
return unique_id
def drop_appt_by_id(unique_id, table = DEFAULT_TABLE):
query = '''
DELETE FROM %s WHERE
('id'=%s);
''' % (table, str(unique_id))
#print query
execute(query)
return
def drop_appt(date, start, table = DEFAULT_TABLE):
unique_id = get_unique_id(date, start)
query = '''
DELETE FROM %s WHERE
('appointment_date'="%s" AND 'appointment_start_time'="%s");
''' % (table, date, start)
query
execute(query)
#print execute('SELECT * FROM %s;' % (table))
return unique_id
def new_table(table):
print 'Tables BEFORE:\n'
print execute('show tables;')
print '------------------------------------'
query = '''
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `%s`;
-- Create a table to hold the appts
CREATE TABLE `%s` (
`id` int NOT NULL AUTO_INCREMENT,
`advisor_name` varchar(100),
`student_name` varchar(100),
`advisor_email` varchar(100),
`student_email` varchar(100),
`appointment_date` date,
`appointment_start_time` time,
`appointment_end_time` time,
`date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
''' % (table, table)
execute(query, False)
print 'Tables AFTER:\n'
print execute('show tables;')
print execute('show create table %s;' % table, False)
def get_student(date, start, table = DEFAULT_TABLE):
query = '''
SELECT 'student_name' FROM %s WHERE
('appointment_date'="%s" AND 'appointment_start_time'="%s");
''' % (table, date, start)
student = execute(query, False).strip()
return student
def get_unique_id(date, start, table = DEFAULT_TABLE):
query = '''
SELECT 'student_email' FROM %s WHERE
('appointment_date'="%s" AND 'appointment_start_time'="%s");
''' % (table, date, start)
email = execute(query, False).strip()
unique_id = email + '::' + date + '::' + start
return unique_id
def is_repeat(date, start, end, table = DEFAULT_TABLE):
conflict_found = False
query = '''
SELECT 'id' FROM %s WHERE
'appointment_date'="%s" AND (
('appointment_start_time'="%s" OR 'appointment_end_time'="%s") OR
('appointment_start_time'>"%s" AND 'appointment_start_time'<"%s") OR
('appointment_end_time'>"%s" AND 'appointment_end_time'<"%s"));
''' % (table, date, start, end, start, end, start, end)
# print query
# print execute(query, False)
if len( execute(query, False) ) > 0:
conflict_found = True
return conflict_found
def drop_table(table):
print 'Tables BEFORE:\n'
print execute('show tables;')
print '------------------------------------'
query = '''
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `%s`;
''' % (table)
execute(query, False)
print 'Tables AFTER:\n'
print execute('show tables;')
def view_appt(table = DEFAULT_TABLE): query = 'SELECT * FROM %s' % (table) appts = execute(query) return appts
return result
fullcmd = ''
cmd = gettext('mysql419 >>')
while (cmd != 'exit'):
# handle cases of comment lines
if ('#' in cmd): # ignore inline text after comment tag
comment_tag = cmd.find('#')
cmd = cmd[0:comment_tag]
if ('-- ' in cmd) or ('--\n' in cmd) or \
('--\t' in cmd): # ignore inline text after comment tag
comment_tag = cmd.find('--')
cmd = cmd[0:comment_tag]
while ('/*' in cmd): # ignore text between comment tags
while ('*/' not in cmd):
cmd += gettext('')
comment_tag1 = cmd.find('/*')
comment_tag2 = cmd.find('*/') + 2
cmd = cmd[0:comment_tag1] + cmd[comment_tag2:]
# comment lines have been excluded
fullcmd += cmd
if ';' in fullcmd:
print "(query >> '" + fullcmd + "')"
print sql_cmd.execute(fullcmd)
fullcmd = ''
cmd = gettext('mysql419 >>')
else:
cmd = gettext('>>')
fullcmd = ''
cmd = gettext('mysql419 >>')
while (cmd != 'exit'):
# handle cases of comment lines
if ('#' in cmd): # ignore inline text after comment tag
comment_tag = cmd.find('#')
cmd = cmd[0:comment_tag]
if ('-- ' in cmd) or ('--\n' in cmd) or \
('--\t' in cmd): # ignore inline text after comment tag
comment_tag = cmd.find('--')
cmd = cmd[0:comment_tag]
while ('/*' in cmd): # ignore text between comment tags
while ('*/' not in cmd):
cmd += gettext('')
comment_tag1 = cmd.find('/*')
comment_tag2 = cmd.find('*/') + 2
cmd = cmd[0:comment_tag1] + cmd[comment_tag2:]
# comment lines have been excluded
table_border = False
fullcmd += cmd
if ';' in fullcmd:
print "(query >> '" + fullcmd + "')"
print sql_cmd.execute(fullcmd, table_border)
fullcmd = ''
cmd = gettext('mysql419 >>')
else:
cmd = gettext('>>')