SQLRoutines.py
import MySQLdb
import string
import time
DATABASE_1 = "eroticstories"
DATABASE_2 = "eroticstories"
HOST = "localhost"
USER = ""
PASSWORD = ""
##DATABASE_1 = "pythncgpgmng3"
##DATABASE_2 = "pythncgpgmng4"
##HOST = "localhost"
##USER = "pythncgpgmng"
##PASSWORD = "***********"
def CheckAuthorLogin(userid):
"""Check the Author / Voter login for existence"""
select_sql = "Select count(*) from author where Author_User_ID_Lower = '"
select_sql += userid.lower() + "'"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
count = int(c.fetchone()[0])
c.close()
return count
def CheckStoryCount(story_id):
"""Check the Story Count row for existence"""
select_sql = "Select Story_ID from story_count"
select_sql += " where Story_ID = " + str(story_id)
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
result = c.fetchone()
c.close()
if (result == None):
story_id = None
else:
story_id = int(result[0])
return story_id
def CheckStoryTitle(author_id, title):
"""Check the Author / Story Title for existence"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
select_sql = "Select Story_ID from story"
select_sql += " where Author_ID = " + str(author_id)
select_sql += " and Story_Title = " + db.string_literal(title)
c = db.cursor()
c.execute(select_sql)
result = c.fetchone()
c.close()
if (result == None):
story_id = None
else:
story_id = int(result[0])
return story_id
def CountStoryByAuthor(author_id):
"""Get a count of the Story rows by Author ID"""
select_sql = "Select count(Story_Id)"
select_sql += " from story"
select_sql += " where Author_ID = " + str(author_id)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = int(c.fetchone()[0])
c.close()
return values
def DeleteStoryView(days):
"""Delete the Story View rows that are older than the number of days specified"""
detail_sql = "Delete LOW_PRIORITY from story_view_detail "
detail_sql += "where Insert_Date < DATE_SUB(Now(), INTERVAL " + str(days) + " DAY)"
header_sql = "Delete LOW_PRIORITY from story_view_header "
header_sql += "where Insert_Date < DATE_SUB(Now(), INTERVAL " + str(days) + " DAY)"
db = MySQLdb.connect(db=DATABASE_2, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(detail_sql)
c.execute(header_sql)
c.close()
return
def DeleteSubmittedStory(story_id):
"""Delete the Submitted Story row"""
delete_sql = "Delete from submitted_story "
delete_sql += "where Story_ID = " + str(story_id)
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(delete_sql)
c.close()
return
def GetAllStoryByAuthor(author_id):
"""Get all the stories from the Story and Submitted Story
tables in Story Title order"""
# Can only be used with MySQL 4.0 and up
select_sql = "(Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Category_ID"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += ' ,"Story"'
select_sql += " from story)"
select_sql += " union "
select_sql += "(Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Category_ID"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += ' ,"Submitted Story"'
select_sql += " from submitted_story)"
select_sql += " where Author_ID = " + str(author_id)
select_sql += " order by Story_Title"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetAuthor(userid, password):
"""Get a particular Author by User ID (lower)"""
select_sql = "Select Author_ID"
select_sql += " ,Author_User_ID"
select_sql += " ,Author_Password"
select_sql += " ,IP_Address"
select_sql += " ,Registered_Date"
select_sql += " ,Last_Login_Date"
select_sql += " ,Email_Address"
select_sql += " ,Biography"
select_sql += " ,Sex"
select_sql += " ,Age"
select_sql += " ,Height"
select_sql += " ,Weight"
select_sql += " ,Location"
select_sql += " ,Orientation"
select_sql += " ,Dating_Status"
select_sql += " ,Looking_For_Friends"
select_sql += " ,Looking_For_Sex_Partners"
select_sql += " ,Looking_For_Swingers"
select_sql += " ,Looking_For_Lovers"
select_sql += " ,Looking_For_Men"
select_sql += " ,Looking_For_Women"
select_sql += " ,Smoke"
select_sql += " ,Drink"
select_sql += " ,Fetishes"
select_sql += " ,Dogs"
select_sql += " ,Cats"
select_sql += " ,Birds"
select_sql += " ,Reptiles"
select_sql += " ,Other_Pets"
select_sql += " ,Website"
select_sql += " ,ICQ"
select_sql += " ,AOL"
select_sql += " ,MS_Pager"
select_sql += " ,Current_Picture "
select_sql += " ,Updated_Date"
select_sql += " from author"
select_sql += " where Author_User_ID_Lower = '" + userid.lower() + "'"
select_sql += " and Author_Password = '" + password + "'"
# print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
# print "Author", userid, password, values
return values
def GetAuthors(offset, count):
"""Get all Authors for the Author Browse """
maximum = offset + count
select_sql = "Select Author_ID"
select_sql += " ,Author_User_ID"
select_sql += " ,Sex"
select_sql += " ,Age"
select_sql += " from author"
select_sql += " where Email_Address is not Null"
select_sql += " order by Author_User_ID"
select_sql += " limit " + str(offset) + ',' + str(maximum)
# print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetAuthorByID(author_id):
"""Get a particular Author by Author_ID"""
select_sql = "Select Author_ID"
select_sql += " ,Author_User_ID"
select_sql += " ,Author_Password"
select_sql += " ,IP_Address"
select_sql += " ,Registered_Date"
select_sql += " ,Last_Login_Date"
select_sql += " ,Email_Address"
select_sql += " ,Biography"
select_sql += " ,Sex"
select_sql += " ,Age"
select_sql += " ,Height"
select_sql += " ,Weight"
select_sql += " ,Location"
select_sql += " ,Orientation"
select_sql += " ,Dating_Status"
select_sql += " ,Looking_For_Friends"
select_sql += " ,Looking_For_Sex_Partners"
select_sql += " ,Looking_For_Swingers"
select_sql += " ,Looking_For_Lovers"
select_sql += " ,Looking_For_Men"
select_sql += " ,Looking_For_Women"
select_sql += " ,Smoke"
select_sql += " ,Drink"
select_sql += " ,Fetishes"
select_sql += " ,Dogs"
select_sql += " ,Cats"
select_sql += " ,Birds"
select_sql += " ,Reptiles"
select_sql += " ,Other_Pets"
select_sql += " ,Website"
select_sql += " ,ICQ"
select_sql += " ,AOL"
select_sql += " ,MS_Pager"
select_sql += " ,Current_Picture "
select_sql += " ,Updated_Date"
select_sql += " ,MONTHNAME(Registered_Date)"
select_sql += " ,DAYOFMONTH(Registered_Date)"
select_sql += " ,YEAR(Registered_Date)"
select_sql += " ,MONTHNAME(Updated_Date)"
select_sql += " ,DAYOFMONTH(Updated_Date)"
select_sql += " ,YEAR(Updated_Date)"
select_sql += " from author"
select_sql += " where Author_ID = " + str(author_id)
# print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
# print "Author", userid, password, values
return values
def GetStatStoryByAuthor(author_id):
"""Get all the stories from the Story table in Story Title order"""
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Category_ID"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += ' ,"Story"'
select_sql += " from story"
select_sql += " where Author_ID = " + str(author_id)
select_sql += " order by Story_Title"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStatSubmittedStoryByAuthor(author_id):
"""Get all the stories from the Submitted Story
table in Story Title order"""
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Category_ID"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += ' ,"Submitted Story"'
select_sql += " from submitted_story"
select_sql += " where Author_ID = " + str(author_id)
select_sql += " order by Story_Title"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStory(parameter):
"""Get the Story row"""
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Approved_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Word_Count"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " ,Story_Type"
select_sql += " ,Story_Language"
select_sql += " from story"
select_sql += " where " + parameter
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetStoryByAuthor(author_id):
"""Get count Story rows by Author ID, Story_title ascending"""
#maximum = offset + count
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Approved_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Word_Count"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " ,Story_Type"
select_sql += " ,Story_Language"
select_sql += " from story"
select_sql += " where Author_ID = " + str(author_id)
select_sql += " order by Story_Title"
#select_sql += " limit " + str(offset) + ',' + str(maximum)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStoryByCategory(category_id, offset, count):
"""Get count Story rows by Category ID, Story_title ascending"""
maximum = offset + count
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Approved_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Word_Count"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " ,Story_Type"
select_sql += " ,Story_Language"
select_sql += " from story"
select_sql += " where Category_ID = " + str(category_id)
select_sql += " order by Story_Title, Author_ID"
select_sql += " limit " + str(offset) + ',' + str(maximum)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStoryByDate(offset, count):
"""Get count Story rows by Submitted_Date descending"""
maximum = offset + count
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Approved_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Word_Count"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " ,Story_Type"
select_sql += " ,Story_Language"
select_sql += " from story"
select_sql += " order by Submitted_Date desc"
select_sql += " limit " + str(offset) + ',' + str(maximum)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStoryCategory(category_id):
"""Get a Story Category row"""
select_sql = "Select Category_ID"
select_sql += " ,Parent_Category_ID"
select_sql += " ,Category_Name"
select_sql += " ,Category_Description"
select_sql += " ,Category_Creation_Date"
select_sql += " ,Category_Story_Count"
select_sql += " ,Subcategory_Story_Count"
select_sql += " from story_category"
select_sql += " where Category_ID = " + str(category_id)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetStoryCategoryAll():
"""Get all of the Story Category rows"""
select_sql = "Select Category_ID"
select_sql += " ,Parent_Category_ID"
select_sql += " ,Category_Name"
select_sql += " ,Category_Description"
select_sql += " ,Category_Creation_Date"
select_sql += " ,Category_Story_Count"
select_sql += " ,Subcategory_Story_Count"
select_sql += " from story_category"
select_sql += " order by Category_Name"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStoryCategorySet(level):
"""Get a set of Story Category rows for the level"""
select_sql = "Select Category_ID"
select_sql += " ,Parent_Category_ID"
select_sql += " ,Category_Name"
select_sql += " ,Category_Description"
select_sql += " ,Category_Creation_Date"
select_sql += " ,Category_Story_Count"
select_sql += " ,Subcategory_Story_Count"
select_sql += " from story_category"
if (level == None):
select_sql += " where Parent_Category_ID = 0"
else:
select_sql += " where Parent_Category_ID = " + str(level)
select_sql += " order by Category_Name"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def GetStoryCount(story_id):
"""Get a Story Count row"""
select_sql = "Select Story_ID"
select_sql += " ,Vote_Count"
select_sql += " ,Vote_Total"
select_sql += " ,Story_View_Count"
select_sql += " ,Registered_Story_View_Count"
select_sql += " from story_count"
select_sql += " where Story_ID = " + str(story_id)
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetStoryViewDetail(parameters):
"""Get a Story View Detail row"""
select_sql = "Select Story_View_ID"
select_sql += " ,Piece_Count"
select_sql += " ,Insert_Date"
select_sql += " ,Piece"
select_sql += " from story_view_detail"
select_sql += " where " + parameters[0]
select_sql += " and " + parameters[1]
# print select_sql
db = MySQLdb.connect(db=DATABASE_2, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetStoryViewHeader(parameters):
"""Get a Story View Header row"""
select_sql = "Select Story_View_ID"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Total_Pieces"
select_sql += " ,Insert_Date"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Story_ID"
select_sql += " from story_view_header"
select_sql += " where " + parameters[0]
# print select_sql
db = MySQLdb.connect(db=DATABASE_2, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetSubmittedStory(story_id):
"""Get the next Submitted Story row"""
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Story_Notes"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " from submitted_story"
select_sql += " where Story_ID > " + str(story_id)
select_sql += " limit 1"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
c.close()
return values
def GetVote(parameter):
"""Get the Vote rows"""
select_sql = "Select Story_ID"
select_sql += " ,Vote_ID"
select_sql += " ,Vote"
select_sql += " ,Vote_Timestamp"
select_sql += " ,IP_Address"
select_sql += " ,Reader_ID"
select_sql += " from vote"
select_sql += " where " + parameter
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def InsertAuthor(values):
"""Insert a row into the Author table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
author_user_id = str(values[1])
author_user_id_lower = str(values[1]).lower()
author_password = str(values[2])
ip_address = str(values[3])
email_address = str(values[6])
biography = str(values[7])
sex = str(values[8])
age = str(values[9])
height = str(values[10])
weight = str(values[11])
location = str(values[12])
orientation = str(values[13])
dating_status = str(values[14])
looking_for_friends = str(values[15])
looking_for_sex_partners = str(values[16])
looking_for_swingers = str(values[17])
looking_for_lovers = str(values[18])
looking_for_men = str(values[19])
looking_for_women = str(values[20])
smoke = str(values[21])
drink = str(values[22])
fetishes = str(values[23])
dogs = str(values[24])
cats = str(values[25])
birds = str(values[26])
reptiles = str(values[27])
other_pets = str(values[28])
website = str(values[29])
icq = str(values[30])
aol = str(values[31])
ms_pager = str(values[32])
current_picture = str(values[33])
insert_sql = "Insert into author ("
insert_sql += " Author_ID"
insert_sql += " ,Author_User_ID"
insert_sql += " ,Author_User_ID_Lower"
insert_sql += " ,Author_Password"
insert_sql += " ,IP_Address"
insert_sql += " ,Registered_Date"
insert_sql += " ,Updated_Date"
insert_sql += " ,Last_Login_Date"
insert_sql += " ,Email_Address"
insert_sql += " ,Biography"
insert_sql += " ,Sex"
insert_sql += " ,Age"
insert_sql += " ,Height"
insert_sql += " ,Weight"
insert_sql += " ,Location"
insert_sql += " ,Orientation"
insert_sql += " ,Dating_Status"
insert_sql += " ,Looking_For_Friends"
insert_sql += " ,Looking_For_Sex_Partners"
insert_sql += " ,Looking_For_Swingers"
insert_sql += " ,Looking_For_Lovers"
insert_sql += " ,Looking_For_Men"
insert_sql += " ,Looking_For_Women"
insert_sql += " ,Smoke"
insert_sql += " ,Drink"
insert_sql += " ,Fetishes"
insert_sql += " ,Dogs"
insert_sql += " ,Cats"
insert_sql += " ,Birds"
insert_sql += " ,Reptiles"
insert_sql += " ,Other_Pets"
insert_sql += " ,Website"
insert_sql += " ,ICQ"
insert_sql += " ,AOL"
insert_sql += " ,MS_Pager"
insert_sql += " ,Current_Picture"
insert_sql += ") "
insert_sql += 'values (Null, '
insert_sql += '"' + author_user_id + '", '
insert_sql += '"' + author_user_id_lower + '", '
insert_sql += '"' + author_password + '", '
insert_sql += ip_address + ', '
insert_sql += 'Now(), Null, Null, '
if ((email_address == "Null") or (email_address == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + email_address + '", '
if ((biography == "Null") or (biography == "None")):
insert_sql += "Null, "
else:
insert_sql += db.string_literal(biography) + ', '
if ((sex == "Null") or (sex == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + sex + '", '
if ((age == "Null") or (age == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + age + '", '
if ((height == "Null") or (height == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + height + '", '
if ((weight == "Null") or (weight == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + weight + '", '
if ((location == "Null") or (location == "None")):
insert_sql += "Null, "
else:
insert_sql += db.string_literal(location) + ', '
if ((orientation == "Null") or (orientation == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + orientation + '", '
if ((dating_status == "Null") or (dating_status == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + dating_status + '", '
if ((looking_for_friends == "Null") or (looking_for_friends == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_friends + '", '
if ((looking_for_sex_partners == "Null") or (looking_for_sex_partners == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_sex_partners + '", '
if ((looking_for_swingers == "Null") or (looking_for_swingers == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_swingers + '", '
if ((looking_for_lovers == "Null") or (looking_for_lovers == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_lovers + '", '
if ((looking_for_men == "Null") or (looking_for_men == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_men + '", '
if ((looking_for_women == "Null") or (looking_for_women == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + looking_for_women + '", '
if ((smoke == "Null") or (smoke == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + smoke + '", '
if ((drink == "Null") or (drink == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + drink + '", '
if ((fetishes == "Null") or (fetishes == "None")):
insert_sql += "Null, "
else:
insert_sql += db.string_literal(fetishes) + ', '
if ((dogs == "Null") or (dogs == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + dogs + '", '
if ((cats == "Null") or (cats == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + cats + '", '
if ((birds == "Null") or (birds == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + birds + '", '
if ((reptiles == "Null") or (reptiles == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + reptiles + '", '
if ((other_pets == "Null") or (other_pets == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + other_pets + '", '
if ((website == "Null") or (website == "None") or (website == "http://")):
insert_sql += "Null, "
else:
insert_sql += '"' + website + '", '
if ((icq == "Null") or (icq == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + icq + '", '
if ((aol == "Null") or (aol == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + aol + '", '
if ((ms_pager == "Null") or (ms_pager == "None")):
insert_sql += "Null, "
else:
insert_sql += '"' + ms_pager + '", '
if ((current_picture == "Null") or (current_picture == "None")):
insert_sql += "Null"
else:
insert_sql += '"' + current_picture + '"'
insert_sql += ")"
# print "insert_sql =", insert_sql, "\n"
c = db.cursor()
c.execute(insert_sql)
c.close()
return
def InsertStory(values):
"""Insert a row into the Story table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
story_title = values[0]
story_description = values[1]
allow_voting_switch = values[2]
author_id = str(values[5])
category_id = str(values[6])
word_count = str(values[7])
story_text = values[8]
submitted_date = values[10]
story_type = values[11]
story_language = values[12]
#print submitted_date
insert_sql = "Insert into story ("
insert_sql += " Story_ID"
insert_sql += " ,Story_Title"
insert_sql += " ,Story_Description"
insert_sql += " ,Allow_Voting_Switch"
insert_sql += " ,Submitted_Date"
insert_sql += " ,Approved_Date"
insert_sql += " ,Story_Type"
insert_sql += " ,Story_Language"
insert_sql += " ,Author_ID"
insert_sql += " ,Category_ID"
insert_sql += " ,Word_Count"
insert_sql += " ,Story_Text"
insert_sql += ") "
insert_sql += 'values (Null, '
insert_sql += db.string_literal(story_title) + ', '
insert_sql += db.string_literal(story_description) + ', '
insert_sql += '"' + allow_voting_switch + '", '
insert_sql += '"' + submitted_date + '", '
insert_sql += 'Now(), '
insert_sql += '"' + story_type + '", '
insert_sql += '"' + story_language + '", '
insert_sql += author_id + ', '
insert_sql += category_id + ', '
insert_sql += word_count + ', '
insert_sql += db.string_literal(story_text)
insert_sql += ")"
update_sql = "Update story_category "
update_sql += "Set Category_Story_Count = Category_Story_Count + 1"
update_sql += " where Category_ID = " + category_id
#print "insert_sql =", insert_sql, "\n"
c = db.cursor()
c.execute(insert_sql)
c.execute(update_sql)
c.close()
return
def InsertStoryCategory(values):
"""Insert a row into the Story Category table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
category_name = str(values[0])
category_description = str(values[1])
parent_category_id = str(values[3])
insert_sql = "Insert into story_category ("
insert_sql += " Category_ID"
insert_sql += " ,Parent_Category_ID"
insert_sql += " ,Category_Name"
insert_sql += " ,Category_Description"
insert_sql += " ,Category_Creation_Date"
insert_sql += " ,Category_Story_Count"
insert_sql += " ,Subcategory_Story_Count"
insert_sql += ") "
insert_sql += 'values (Null, '
if (parent_category_id == "None"):
insert_sql += '0, '
else:
insert_sql += parent_category_id + ', '
insert_sql += db.string_literal(category_name) + ', '
insert_sql += db.string_literal(category_description) + ', '
insert_sql += 'Now(), 0, 0'
insert_sql += ")"
# print "insert_sql =", insert_sql, "\n"
c = db.cursor()
c.execute(insert_sql)
c.close()
return
def InsertStoryCount(story_id, story_view_count, registered_story_view_count):
"""Insert a row into the Story Count table"""
insert_sql = "Insert into story_count ("
insert_sql += " Story_ID"
insert_sql += " ,Vote_Count"
insert_sql += " ,Vote_Total"
insert_sql += " ,Story_View_Count"
insert_sql += " ,Registered_Story_View_Count"
insert_sql += ") "
insert_sql += 'values (' + str(story_id) + ', 0, 0, '
insert_sql += str(story_view_count) + ', '
insert_sql += str(registered_story_view_count)
insert_sql += ")"
# print "insert_sql =", insert_sql, "\n"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(insert_sql)
c.close()
return
def InsertStoryView(author_id, category_id, story_id, title,
description, allow_voting, pieces):
"""Insert a story into the Story View Header and Detail tables"""
db = MySQLdb.connect(db=DATABASE_2, host=HOST, user=USER, passwd=PASSWORD)
header_sql = "Insert into story_view_header ("
header_sql += " Story_View_ID"
header_sql += " ,Author_ID"
header_sql += " ,Category_ID"
header_sql += " ,Story_ID"
header_sql += " ,Total_Pieces"
header_sql += " ,Allow_Voting_Switch"
header_sql += " ,Insert_Date"
header_sql += " ,Story_Title"
header_sql += " ,Story_Description"
header_sql += ") "
header_sql += 'values (Null, '
header_sql += str(author_id) + ', '
header_sql += str(category_id) + ', '
header_sql += str(story_id) + ', '
header_sql += str(pieces[0]) + ', '
header_sql += '"' + allow_voting + '", '
header_sql += 'Now(), '
header_sql += db.string_literal(title) + ', '
header_sql += db.string_literal(description)
header_sql += ")"
select_sql = "Select LAST_INSERT_ID()"
# print "header_sql =", header_sql, "\n"
# print "select_sql =", select_sql, "\n"
c = db.cursor()
c.execute(header_sql)
for i in xrange(1,pieces[0]):
detail_sql = "Insert into story_view_detail ("
detail_sql += " Story_View_ID"
detail_sql += " ,Piece_Count"
detail_sql += " ,Insert_Date"
detail_sql += " ,Piece"
detail_sql += ") "
detail_sql += 'values (LAST_INSERT_ID(), '
detail_sql += str(i) + ', '
detail_sql += 'Now(), '
detail_sql += db.string_literal(pieces[i])
detail_sql += ")"
#print "detail_sql =", detail_sql, "\n"
c.execute(detail_sql)
c.execute(select_sql)
values = c.fetchone()
c.close()
return values[0]
def InsertSubmittedStory(values):
"""Insert a row into the Submitted Story table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
story_title = values[0]
story_description = values[1]
story_notes = values[2]
allow_voting_switch = values[3]
author_id = str(values[5])
category_id = str(values[6])
story_text = values[7]
insert_sql = "Insert into submitted_story ("
insert_sql += " Story_ID"
insert_sql += " ,Story_Title"
insert_sql += " ,Story_Description"
insert_sql += " ,Story_Notes"
insert_sql += " ,Allow_Voting_Switch"
insert_sql += " ,Submitted_Date"
insert_sql += " ,Author_ID"
insert_sql += " ,Category_ID"
insert_sql += " ,Story_Text"
insert_sql += ") "
insert_sql += 'values (Null, '
insert_sql += db.string_literal(story_title) + ', '
insert_sql += db.string_literal(story_description) + ', '
if (story_notes == None):
insert_sql += 'Null, '
else:
insert_sql += db.string_literal(story_notes) + ', '
insert_sql += '"' + allow_voting_switch + '", '
insert_sql += 'Now(), '
insert_sql += author_id + ', '
insert_sql += category_id + ', '
insert_sql += db.string_literal(story_text)
insert_sql += ")"
#print "insert_sql =", insert_sql, "\n"
c = db.cursor()
c.execute(insert_sql)
c.close()
return
def MatchStory(text, category_id, date_disp):
"""Get the Story rows that match the text"""
select_sql = "Select Story_ID"
select_sql += " ,Story_Title"
select_sql += " ,Story_Description"
select_sql += " ,Allow_Voting_Switch"
select_sql += " ,Submitted_Date"
select_sql += " ,Approved_Date"
select_sql += " ,Author_ID"
select_sql += " ,Category_ID"
select_sql += " ,Word_Count"
select_sql += " ,Story_Text"
select_sql += " ,DAYNAME(Submitted_Date)"
select_sql += " ,MONTHNAME(Submitted_Date)"
select_sql += " ,DAYOFMONTH(Submitted_Date)"
select_sql += " ,YEAR(Submitted_Date)"
select_sql += " ,Story_Type"
select_sql += " ,Story_Language"
select_sql += " ,Match (Story_Title, Story_Description, Story_Text) Against('" + text + "') as Score"
select_sql += " from story"
select_sql += " where Match (Story_Title, Story_Description, Story_Text) Against('" + text + "')"
if (category_id != None):
select_sql += " and Category_ID in " + category_id
if (int(date_disp) > 0):
select_sql += " and TO_DAYS(NOW()) - TO_DAYS(Submitted_Date) <= " + str(date_disp)
select_sql += " order by Score desc"
#print select_sql
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchall()
c.close()
return values
def UpdateAllowVotingSwitch(table, story_id, allow_voting_switch):
"""Update the Allow Voting Switch on the Story or Submitted Story table"""
update_sql = "Update " + table + " "
update_sql += "Set Allow_Voting_Switch = " + '"' + allow_voting_switch + '"'
update_sql += " where Story_ID = " + str(story_id)
#print "update_sql =", update_sql, "\n"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(update_sql)
c.close()
return
def UpdateAuthor(values):
"""Update a row on the Author table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
author_id = str(values[0])
author_user_id = str(values[1])
author_user_id_lower = str(values[1]).lower()
author_password = str(values[2])
ip_address = str(values[3])
email_address = str(values[6])
biography = str(values[7])
sex = str(values[8])
age = str(values[9])
height = str(values[10])
weight = str(values[11])
location = str(values[12])
orientation = str(values[13])
dating_status = str(values[14])
looking_for_friends = str(values[15])
looking_for_sex_partners = str(values[16])
looking_for_swingers = str(values[17])
looking_for_lovers = str(values[18])
looking_for_men = str(values[19])
looking_for_women = str(values[20])
smoke = str(values[21])
drink = str(values[22])
fetishes = str(values[23])
dogs = str(values[24])
cats = str(values[25])
birds = str(values[26])
reptiles = str(values[27])
other_pets = str(values[28])
website = str(values[29])
icq = str(values[30])
aol = str(values[31])
ms_pager = str(values[32])
current_picture = str(values[33])
update_sql = "Update author "
update_sql += "Set Author_User_ID = " + '"' + author_user_id + '"'
update_sql += " ,Author_User_ID_Lower = " + '"' + author_user_id_lower + '"'
update_sql += " ,Author_Password = " + '"' + author_password + '"'
update_sql += " ,IP_Address = " + ip_address
update_sql += " ,Updated_Date = Now()"
if ((email_address == "Null") or (email_address == "None")):
update_sql += " ,Email_Address = Null"
else:
update_sql += " ,Email_Address = " + '"' + email_address + '"'
if ((biography == "Null") or (biography == "None")):
update_sql += " ,Biography = Null"
else:
update_sql += " ,Biography = " + db.string_literal(biography)
if ((sex == "Null") or (sex == "None")):
update_sql += " ,Sex = Null"
else:
update_sql += " ,Sex = " + '"' + sex + '"'
if ((age == "Null") or (age == "None")):
update_sql += " ,Age = Null"
else:
update_sql += " ,Age = " + '"' + age + '"'
if ((height == "Null") or (height == "None")):
update_sql += " ,Height = Null"
else:
update_sql += " ,Height = " + '"' + height + '"'
if ((weight == "Null") or (weight == "None")):
update_sql += " ,Weight = Null"
else:
update_sql += " ,Weight = " + '"' + weight + '"'
if ((location == "Null") or (location == "None")):
update_sql += " ,Location = Null"
else:
update_sql += " ,Location = " + db.string_literal(location)
if ((orientation == "Null") or (orientation == "None")):
update_sql += " ,Orientation = Null"
else:
update_sql += " ,Orientation = " + '"' + orientation + '"'
if ((dating_status == "Null") or (dating_status == "None")):
update_sql += " ,Dating_Status = Null"
else:
update_sql += " ,Dating_Status = " + '"' + dating_status + '"'
if ((looking_for_friends == "Null") or (looking_for_friends == "None")):
update_sql += " ,Looking_For_Friends = Null"
else:
update_sql += " ,Looking_For_Friends = " + '"' + looking_for_friends + '"'
if ((looking_for_sex_partners == "Null") or (looking_for_sex_partners == "None")):
update_sql += " ,Looking_For_Sex_Partners = Null"
else:
update_sql += " ,Looking_For_Sex_Partners = " + '"' + looking_for_sex_partners + '"'
if ((looking_for_swingers == "Null") or (looking_for_swingers == "None")):
update_sql += " ,Looking_For_Swingers = Null"
else:
update_sql += " ,Looking_For_Swingers = " + '"' + looking_for_swingers + '"'
if ((looking_for_lovers == "Null") or (looking_for_lovers == "None")):
update_sql += " ,Looking_For_Lovers = Null"
else:
update_sql += " ,Looking_For_Lovers = " + '"' + looking_for_lovers + '"'
if ((looking_for_men == "Null") or (looking_for_men == "None")):
update_sql += " ,Looking_For_Men = Null"
else:
update_sql += " ,Looking_For_Men = " + '"' + looking_for_men + '"'
if ((looking_for_women == "Null") or (looking_for_women == "None")):
update_sql += " ,Looking_For_Women = Null"
else:
update_sql += " ,Looking_For_Women = " + '"' + looking_for_women + '"'
if ((smoke == "Null") or (smoke == "None")):
update_sql += " ,Smoke = Null"
else:
update_sql += " ,Smoke = " + '"' + smoke + '"'
if ((drink == "Null") or (drink == "None")):
update_sql += " ,Drink = Null"
else:
update_sql += " ,Drink = " + '"' + drink + '"'
if ((fetishes == "Null") or (fetishes == "None")):
update_sql += " ,Fetishes = Null"
else:
update_sql += " ,Fetishes = " + db.string_literal(fetishes)
if ((dogs == "Null") or (dogs == "None")):
update_sql += " ,Dogs = Null"
else:
update_sql += " ,Dogs = " + '"' + dogs + '"'
if ((cats == "Null") or (cats == "None")):
update_sql += " ,Cats = Null"
else:
update_sql += " ,Cats = " + '"' + cats + '"'
if ((birds == "Null") or (birds == "None")):
update_sql += " ,Birds = Null"
else:
update_sql += " ,Birds = " + '"' + birds + '"'
if ((reptiles == "Null") or (reptiles == "None")):
update_sql += " ,Reptiles = Null"
else:
update_sql += " ,Reptiles = " + '"' + reptiles + '"'
if ((other_pets == "Null") or (other_pets == "None")):
update_sql += " ,Other_Pets = Null"
else:
update_sql += " ,Other_Pets = " + '"' + other_pets + '"'
if ((website == "Null") or (website == "None") or (website == "http://")):
update_sql += " ,Website = Null"
else:
update_sql += " ,Website = " + '"' + website + '"'
if ((icq == "Null") or (icq == "None")):
update_sql += " ,ICQ = Null"
else:
update_sql += " ,ICQ = " + '"' + icq + '"'
if ((aol == "Null") or (aol == "None")):
update_sql += " ,AOL = Null"
else:
update_sql += " ,AOL = " + '"' + aol + '"'
if ((ms_pager == "Null") or (ms_pager == "None")):
update_sql += " ,MS_Pager = Null"
else:
update_sql += " ,MS_Pager = " + '"' + ms_pager + '"'
if ((current_picture == "Null") or (current_picture == "None")):
update_sql += " ,Current_Picture = Null"
else:
update_sql += " ,Current_Picture = " + '"' + current_picture + '"'
update_sql += " where Author_ID = " + author_id
#print "update_sql =", update_sql, "\n"
c = db.cursor()
c.execute(update_sql)
c.close()
return
def UpdateLastLoginTimestamp(author_id):
"""Update the Last Login Timestamp on the Author table"""
update_sql = "Update author "
update_sql += "Set Last_Login_Date = Now()"
update_sql += " where Author_ID = " + str(author_id)
#print "update_sql =", update_sql, "\n"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(update_sql)
c.close()
return
def UpdateStory(story_id, values):
"""Update the row on the Story table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
story_description = values[1]
allow_voting_switch = values[2]
category_id = values[6]
word_count = str(values[7])
story_text = values[8]
story_type = values[11]
story_language = values[12]
select_sql = "Select Category_ID"
select_sql += " from story"
select_sql += " where Story_ID = " + str(story_id)
update_sql = "Update story "
update_sql += "Set Story_Description = " + db.string_literal(story_description)
update_sql += " ,Allow_Voting_Switch = " + "'" + allow_voting_switch + "'"
update_sql += " ,Approved_Date = " + "Now()"
update_sql += " ,Story_Type = " + "'" + story_type + "'"
update_sql += " ,Story_Language = " + "'" + story_language + "'"
update_sql += " ,Category_ID = " + str(category_id)
update_sql += " ,Word_Count = " + word_count
update_sql += " ,Story_Text = " + db.string_literal(story_text)
update_sql += " where Story_ID = " + str(story_id)
#print "update_sql =", update_sql, "\n"
c = db.cursor()
c.execute(select_sql)
select_values = c.fetchone()
old_category_id = select_values[0]
c.execute(update_sql)
if (old_category_id != category_id):
update1_sql = "Update story_category "
update1_sql += "Set Category_Story_Count = Category_Story_Count - 1"
update1_sql += " where Category_ID = " + str(old_category_id)
update2_sql = "Update story_category "
update2_sql += "Set Category_Story_Count = Category_Story_Count + 1"
update2_sql += " where Category_ID = " + str(category_id)
c.execute(update1_sql)
c.execute(update2_sql)
c.close()
return
def UpdateStoryCategory(values):
"""Update the Name and / or Description on the Story Category table"""
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
category_name = str(values[0])
category_description = str(values[1])
category_id = str(values[2])
update_sql = "Update story_category "
update_sql += "Set Category_Name = " + db.string_literal(category_name)
update_sql += " ,Category_Description = " + db.string_literal(category_description)
update_sql += " where Category_ID = " + category_id
#print "update_sql =", update_sql, "\n"
c = db.cursor()
c.execute(update_sql)
c.close()
return
def UpdateStoryCount(story_id, story_view_count, registered_story_view_count):
"""Update the Story Count fields on the Story Count table"""
update_sql = "Update story_count "
update_sql += "Set Story_View_Count = Story_View_Count + " + str(story_view_count)
if (registered_story_view_count > 0):
update_sql += " ,Registered_Story_View_Count = Registered_Story_view_Count + "
update_sql += str(registered_story_view_count)
update_sql += " where Story_ID = " + str(story_id)
#print "update_sql =", update_sql, "\n"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(update_sql)
c.close()
return
def UpdateVote(story_id, ip_integer, reader_id, vote):
"""See if the Vote row exists. If not, insert the Vote row
and update the Story Count row"""
select_sql = "Select count(Vote_ID)"
select_sql += " from vote"
select_sql += " where Story_ID = " + str(story_id)
select_sql += " and Reader_ID = " + str(reader_id)
# print select_sql
insert_sql = "Insert into vote ("
insert_sql += " Story_ID"
insert_sql += " ,Vote_ID"
insert_sql += " ,Vote"
insert_sql += " ,Vote_Timestamp"
insert_sql += " ,IP_Address"
insert_sql += " ,Reader_ID"
insert_sql += ") "
insert_sql += 'values (' + str(story_id) + ', Null, '
insert_sql += vote + ', Now(), ' + str(ip_integer) + ', '
insert_sql += str(reader_id)
insert_sql += ")"
# print "insert_sql =", insert_sql, "\n"
update_sql = "Update story_count "
update_sql += "Set Vote_Count = Vote_Count + 1"
update_sql += " ,Vote_Total = Vote_Total + " + vote
update_sql += " where Story_ID = " + str(story_id)
#print "update_sql =", update_sql, "\n"
db = MySQLdb.connect(db=DATABASE_1, host=HOST, user=USER, passwd=PASSWORD)
c = db.cursor()
c.execute(select_sql)
values = c.fetchone()
count = int(values[0])
if (count == 0):
c.execute(insert_sql)
c.execute(update_sql)
c.close()
return count