def insert_edited(self, obj, old_text):
'''
Having already detected that the item has been edited, add a record to
the appropriate *_edits table containing the text that is being
replaced.
'''
if isinstance(obj, SUBMISSION_TYPES):
table = 'submission_edits'
else:
table = 'comment_edits'
if obj.edited is False:
replaced_at = int(time.time())
else:
replaced_at = int(obj.edited)
postdata = {
'idstr': obj.fullname,
'text': old_text,
'replaced_at': replaced_at,
}
cur = self.sql.cursor()
(qmarks, bindings) = sqlhelpers.insert_filler(SQL_EDITS_COLUMNS,
postdata)
query = 'INSERT INTO %s VALUES(%s)' % (table, qmarks)
cur.execute(query, bindings)
def new_ingredient(self, name):
'''
Add a new Ingredient to the database.
'''
name = self._normalize_ingredient_name(name)
try:
self.get_ingredient_by_name(name)
except exceptions.NoSuchIngredient:
pass
else:
raise exceptions.IngredientExists(name)
cur = self.sql.cursor()
data = {
'IngredientID': helpers.random_hex(),
'Name': name,
}
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_INGREDIENT_COLUMNS,
data)
query = 'INSERT INTO Ingredient VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
ingredient = objects.Ingredient(self, data)
self.log.debug('Created ingredient %s', ingredient.name)
return ingredient
def new_image(self, filepath):
'''
Register a new image in the database.
'''
# generate id and generate new filepath based on id
if isinstance(filepath, pathclass.Path):
filepath = filepath.absolute_path
id = helpers.random_hex()
filetype = filepath.rsplit('.', 1)[1]
new_filepath = '\\'.join(
id[i:i + 4] for i in range(0, len(id), 4)) + '.' + filetype
new_filepath = self.image_directory.join(new_filepath)
os.makedirs(new_filepath.parent.absolute_path, exist_ok=True)
new_filepath = new_filepath.absolute_path
shutil.copyfile(filepath, new_filepath)
data = {
'ImageID': id,
'ImageFilePath': new_filepath,
}
cur = self.sql.cursor()
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_IMAGE_COLUMNS,
data)
query = 'INSERT INTO Image VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
image = objects.Image(self, data)
self.log.debug('Created image with ID: %s, filepath: %s' %
(image.id, image.file_path))
return image
def new_user(
self,
username: str,
password: str,
*,
display_name: str = None,
bio_text: str = None,
profile_image: objects.Image = None,
):
'''
Register a new User to the database
'''
self._assert_valid_username(username)
self._assert_valid_password(password)
try:
self.get_user(username=username)
except exceptions.NoSuchUser:
pass
else:
raise exceptions.UserExists(username)
cur = self.sql.cursor()
user_id = helpers.random_hex()
password_hash = helpers.hash_password(password)
date_joined = helpers.now()
if not display_name:
display_name = username
if not bio_text:
bio_text = ''
if profile_image is not None:
profile_image_id = profile_image.id
else:
profile_image_id = None
user_data = {
'UserID': user_id,
'Username': username,
'DisplayName': display_name,
'PasswordHash': password_hash,
'BioText': bio_text,
'DateJoined': date_joined,
'ProfileImageID': profile_image_id,
}
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_USER_COLUMNS,
user_data)
query = 'INSERT INTO User VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
user = objects.User(self, user_data)
self.log.debug('Created user %s with ID %s', user.username, user.id)
return user
def sql_insert(self, table, data):
self.assert_table_exists(table)
column_names = constants.SQL_COLUMNS[table]
(qmarks, bindings) = sqlhelpers.insert_filler(column_names, data)
query = f'INSERT INTO {table} VALUES({qmarks})'
self.sql_execute(query, bindings)
def new_ingredient_tag(self, name, parent=None):
'''
Create a new IngredientTag, either a root or grouped under `parent`.
'''
name = self._normalize_ingredient_name(name)
try:
self.get_ingredient_tag_by_name(name)
except exceptions.NoSuchIngredientTag:
pass
else:
raise exceptions.IngredientTagExists(name)
if parent is not None:
parent_id = parent.id
else:
parent_id = None
data = {
'IngredientTagID': helpers.random_hex(),
'TagName': name,
'ParentTagID': parent_id,
}
cur = self.sql.cursor()
(qmarks, bindings) = sqlhelpers.insert_filler(
constants.SQL_INGREDIENTTAG_COLUMNS, data)
query = 'INSERT INTO IngredientTag VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
tag = objects.IngredientTag(self, data)
self.log.debug('Created IngredientTag %s', tag.name)
return tag
def insert_item(data):
id = data['id']
retrieved = int(time.time())
existing = select_item(id)
if existing is None:
row = {
'id': id,
'deleted': bool(data.get('deleted', False)),
'type': data['type'],
'author': data.get('by', None),
'time': int(data['time']),
'text': data.get('text', None),
'dead': bool(data.get('dead', False)),
'parent': data.get('parent', None),
'poll': data.get('poll', None),
'url': data.get('url', None),
'score': int_or_none(data.get('score', None)),
'title': data.get('title', None),
'descendants': int_or_none(data.get('descendants', None)),
'retrieved': retrieved,
}
log.info('Inserting item %s.', id)
(qmarks, bindings) = sqlhelpers.insert_filler(ITEMS_COLUMNS,
row,
require_all=True)
query = f'INSERT INTO items VALUES({qmarks})'
sql.execute(query, bindings)
log.loud('Inserted item %s.', id)
else:
row = {
'id': id,
'deleted': bool(data.get('deleted', False)),
'type': data['type'],
'author': data.get('by', existing.get('author', None)),
'time': int(data['time']),
'text': data.get('text', existing.get('text', None)),
'dead': bool(data.get('dead', False)),
'parent': data.get('parent', None),
'poll': data.get('poll', existing.get('poll', None)),
'url': data.get('url', existing.get('url', None)),
'score': int_or_none(data.get('score', existing.get('score',
None))),
'title': data.get('title', existing.get('title', None)),
'descendants': int_or_none(data.get('descendants', None)),
'retrieved': retrieved,
}
log.info('Updating item %s.', id)
(qmarks, bindings) = sqlhelpers.update_filler(row, where_key='id')
query = f'UPDATE items {qmarks}'
sql.execute(query, bindings)
log.loud('Updated item %s.', id)
return {'row': row, 'is_new': existing is None}
def add_tag(self, tag):
if self.has_tag(tag):
return
cur = self.recipedb.sql.cursor()
data = {
'IngredientID': self.id,
'IngredientTagID': tag.id,
}
(qmarks, bindings) = sqlhelpers.insert_filler(
constants.SQL_INGREDIENTINGREDIENTTAG_COLUMNS, data)
query = 'INSERT INTO Ingredient_IngredientTag_Map VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.recipedb.sql.commit()
def insert_comment(self, comment):
cur = self.sql.cursor()
cur.execute('SELECT * FROM comments WHERE idstr == ?',
[comment.fullname])
existing_entry = cur.fetchone()
if comment.author is None:
author = '[DELETED]'
else:
author = comment.author.name
if not existing_entry:
postdata = {
'idint': common.b36(comment.id),
'idstr': comment.fullname,
'created': comment.created_utc,
'author': author,
'parent': comment.parent_id,
'submission': comment.link_id,
'body': comment.body,
'score': comment.score,
'subreddit': comment.subreddit.display_name,
'distinguish': comment.distinguished,
'textlen': len(comment.body),
}
(qmarks,
bindings) = sqlhelpers.insert_filler(SQL_COMMENT_COLUMNS,
postdata)
query = 'INSERT INTO comments VALUES(%s)' % qmarks
cur.execute(query, bindings)
else:
body = self.check_for_edits(comment, existing_entry=existing_entry)
query = '''
UPDATE comments SET
score = coalesce(?, score),
body = coalesce(?, body),
distinguish = coalesce(?, distinguish)
WHERE idstr == ?
'''
bindings = [
comment.score, body, comment.distinguished, comment.fullname
]
cur.execute(query, bindings)
return existing_entry is None
def insert_stick(data, commit=True):
cur = sql.cursor()
cur.execute('SELECT 1 FROM sticks WHERE id == ?', [data['id']])
existing = cur.fetchone()
if existing:
(qmarks, bindings) = sqlhelpers.update_filler(data, 'id')
query = f'UPDATE sticks {qmarks}'
else:
(qmarks, bindings) = sqlhelpers.insert_filler(SQL_COLUMNS['sticks'],
data)
query = f'INSERT INTO sticks VALUES({qmarks})'
cur.execute(query, bindings)
if commit:
sql.commit()
def insert_id(id, commit=True):
cur = sql.cursor()
cur.execute('SELECT 1 FROM sticks WHERE id == ?', [id])
existing = cur.fetchone()
if not existing:
data = {'id': id}
columns = SQL_COLUMNS['sticks']
(qmarks, bindings) = sqlhelpers.insert_filler(columns,
data,
require_all=False)
query = f'INSERT INTO sticks VALUES({qmarks})'
cur.execute(query, bindings)
if commit:
sql.commit()
status = types.SimpleNamespace(id=id, is_new=not existing)
return status
def add_autocorrect(self, alternate_name):
alternate_name = self.recipedb._normalize_ingredient_name(
alternate_name)
try:
existing = self.recipedb.get_ingredient_by_name(alternate_name)
except exceptions.NoSuchIngredient:
pass
else:
raise exceptions.IngredientExists(alternate_name)
cur = self.recipedb.sql.cursor()
data = {
'IngredientID': self.id,
'AlternateName': alternate_name,
}
(qmarks, bindings) = sqlhelpers.insert_filler(
constants.SQL_INGREDIENTAUTOCORRECT_COLUMNS, data)
query = 'INSERT INTO IngredientAutocorrect VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.recipedb.sql.commit()
def follow(self, other):
if other == self:
return
cur = self.recipedb.sql.cursor()
cur.execute(
'SELECT * FROM User_Following_Map WHERE UserID = ? AND TargetID = ?',
[self.id, other.id])
if cur.fetchone() is not None:
return
data = {
'UserID': self.id,
'TargetID': other.id,
}
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_USERFOLLOW_COLUMNS,
data)
query = 'INSERT INTO User_Following_Map VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.recipedb.sql.commit()
def new_review(
self,
recipe,
user,
score=None,
text='',
):
if score is not None:
score = int(score)
self._assert_valid_review_score(score)
text = text or ''
if not text and not score:
raise ValueError('Text and score cannot both be blank')
cur = self.sql.cursor()
cur.execute('SELECT * FROM Review WHERE RecipeID = ? AND AuthorID = ?',
[recipe.id, user.id])
if cur.fetchone() is not None:
raise ValueError('%s has already reviewed %s' % (user, recipe))
review_id = helpers.random_hex()
review_data = {
'ReviewID': review_id,
'RecipeID': recipe.id,
'AuthorID': user.id,
'Score': score,
'Text': text,
'DateAdded': helpers.now(),
}
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_REVIEW_COLUMNS,
review_data)
query = 'INSERT INTO Review VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
review = objects.Review(self, review_data)
self.log.debug('Created review %s', review)
return review
def insert_video(self, video, *, add_channel=True, commit=True):
if not isinstance(video, ytapi.Video):
video = self.youtube.get_video(video)
if add_channel:
self.add_channel(video.author_id, get_videos=False, commit=False)
self.cur.execute('SELECT * FROM videos WHERE id == ?', [video.id])
fetch = self.cur.fetchone()
existing = fetch is not None
download_status = 'pending' if not existing else fetch[
SQL_VIDEO['download']]
data = {
'id': video.id,
'published': video.published,
'author_id': video.author_id,
'title': video.title,
'description': video.description,
'duration': video.duration,
'thumbnail': video.thumbnail['url'],
'download': download_status,
}
if existing:
(qmarks, bindings) = sqlhelpers.update_filler(data, where_key='id')
query = f'UPDATE videos {qmarks}'
else:
(qmarks,
bindings) = sqlhelpers.insert_filler(SQL_VIDEO_COLUMNS, data)
query = f'INSERT INTO videos VALUES({qmarks})'
self.cur.execute(query, bindings)
if commit:
self.sql.commit()
return {'new': not existing, 'row': data}
def set_ingredients(self, ingredients):
ingredients = [
self.recipedb._coerce_quantitied_ingredient(i) for i in ingredients
]
cur = self.recipedb.sql.cursor()
cur.execute('DELETE FROM Recipe_Ingredient_Map WHERE RecipeID = ?',
[self.id])
for quant_ingredient in ingredients:
recipe_ingredient_data = {
'RecipeID': self.id,
'IngredientID': quant_ingredient.ingredient.id,
'IngredientQuantity': quant_ingredient.quantity,
'IngredientPrefix': quant_ingredient.prefix,
'IngredientSuffix': quant_ingredient.suffix,
}
(qmarks, bindings) = sqlhelpers.insert_filler(
constants.SQL_RECIPEINGREDIENT_COLUMNS, recipe_ingredient_data)
query = 'INSERT INTO Recipe_Ingredient_Map VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.recipedb.sql.commit()
def process(
subreddit,
commit=True,
):
'''
Retrieve the API info for the subreddit and save it to the database
subreddit:
The subreddit(s) to process. Can be an individual or list of:
strings or Subreddit, Submission, or Comment objects.
'''
subreddits = []
processed_subreddits = []
if isinstance(subreddit, (tuple, list, set, types.GeneratorType)):
subreddits = iter(subreddit)
else:
subreddits = [subreddit]
for subreddit in subreddits:
subreddit = normalize_subreddit_object(subreddit)
processed_subreddits.append(subreddit)
created = subreddit.created_utc
created_human = humanize(subreddit.created_utc)
idstr = subreddit.id
is_nsfw = int(subreddit.over18 or 0)
name = subreddit.display_name
subscribers = subreddit.subscribers or 0
subreddit_type = SUBREDDIT_TYPE[subreddit.subreddit_type]
submission_type = SUBMISSION_TYPE[subreddit.submission_type]
now = int(get_now())
cur.execute('SELECT * FROM subreddits WHERE idstr == ?', [idstr])
f = cur.fetchone()
if f is None:
message = FORMAT_MESSAGE_NEW.format(
idstr=idstr,
human=created_human,
nsfw=is_nsfw,
name=name,
subscribers=subscribers,
)
print(message)
data = {
'idint': b36(idstr),
'idstr': idstr,
'created': created,
'human': created_human,
'nsfw': is_nsfw,
'name': name,
'subscribers': subscribers,
'subreddit_type': subreddit_type,
'submission_type': submission_type,
'last_scanned': now,
}
(qmarks, bindings) = sqlhelpers.insert_filler(SQL_SUBREDDIT_COLUMNS, data)
query = 'INSERT INTO subreddits VALUES(%s)' % qmarks
cur.execute(query, bindings)
else:
old_subscribers = f[SQL_SUBREDDIT['subscribers']]
subscriber_diff = subscribers - old_subscribers
if subscribers == 0 and old_subscribers > 2 and subreddit_type != SUBREDDIT_TYPE['private']:
print('SUSPICIOUS %s' % name)
data = {
'idint': b36(idstr),
'idstr': idstr,
'name': name,
'subscribers': old_subscribers,
'noticed': int(get_now()),
}
(qmarks, bindings) = sqlhelpers.insert_filler(SQL_SUSPICIOUS_COLUMNS, data)
query = 'INSERT INTO suspicious VALUES(%s)' % qmarks
cur.execute(query, bindings)
message = FORMAT_MESSAGE_UPDATE.format(
idstr=idstr,
human=created_human,
nsfw=is_nsfw,
name=name,
subscribers=subscribers,
subscriber_diff=subscriber_diff
)
print(message)
data = {
'idstr': idstr,
'subscribers': subscribers,
'subreddit_type': subreddit_type,
'submission_type': submission_type,
'last_scanned': now,
}
(query, bindings) = sqlhelpers.update_filler(data, where_key='idstr')
query = 'UPDATE subreddits %s' % query
cur.execute(query, bindings)
#cur.execute('''
# UPDATE subreddits SET
# subscribers = @subscribers,
# subreddit_type = @subreddit_type,
# submission_type = @submission_type,
# last_scanned = @last_scanned
# WHERE idstr == @idstr
# ''', data)
processed_subreddits.append(subreddit)
if commit:
sql.commit()
return processed_subreddits
def new_recipe(
self,
*,
author: objects.User,
blurb: str,
country_of_origin: str,
cuisine: str,
ingredients: list,
instructions: str,
meal_type: str,
name: str,
prep_time: int,
serving_size: int,
recipe_image: objects.Image,
):
'''
Add a new recipe to the database.
author: May be a string representing the author's ID, or a User object.
ingredients: A list of either ingredient's ID, or Ingredient objects.
'''
cur = self.sql.cursor()
recipe_id = helpers.random_hex()
if author is not None:
author_id = author.id
else:
author_id = None
if recipe_image is not None:
recipe_image_id = recipe_image.id
else:
recipe_image_id = None
recipe_data = {
'RecipeID': recipe_id,
'Name': name,
'AuthorID': author_id,
'CountryOfOrigin': country_of_origin,
'MealType': meal_type,
'Cuisine': cuisine,
'PrepTime': prep_time,
'DateAdded': helpers.now(),
'DateModified': helpers.now(),
'Blurb': blurb,
'ServingSize': serving_size,
'Instructions': instructions,
'RecipeImageID': recipe_image_id,
}
(qmarks,
bindings) = sqlhelpers.insert_filler(constants.SQL_RECIPE_COLUMNS,
recipe_data)
query = 'INSERT INTO Recipe VALUES(%s)' % qmarks
cur.execute(query, bindings)
ingredients = [
self._coerce_quantitied_ingredient(ingredient)
for ingredient in ingredients
]
for quant_ingredient in ingredients:
recipe_ingredient_data = {
'RecipeID': recipe_id,
'IngredientID': quant_ingredient.ingredient.id,
'IngredientQuantity': quant_ingredient.quantity,
'IngredientPrefix': quant_ingredient.prefix,
'IngredientSuffix': quant_ingredient.suffix,
}
(qmarks, bindings) = sqlhelpers.insert_filler(
constants.SQL_RECIPEINGREDIENT_COLUMNS, recipe_ingredient_data)
query = 'INSERT INTO Recipe_Ingredient_Map VALUES(%s)' % qmarks
cur.execute(query, bindings)
self.sql.commit()
recipe = objects.Recipe(self, recipe_data)
self.log.debug('Created recipe %s', recipe.name)
return recipe
def insert_submission(self, submission):
cur = self.sql.cursor()
cur.execute('SELECT * FROM submissions WHERE idstr == ?',
[submission.fullname])
existing_entry = cur.fetchone()
if submission.author is None:
author = '[DELETED]'
else:
author = submission.author.name
if not existing_entry:
if submission.is_self:
# Selfpost's URL leads back to itself, so just ignore it.
url = None
else:
url = submission.url
postdata = {
'idint': common.b36(submission.id),
'idstr': submission.fullname,
'created': submission.created_utc,
'self': submission.is_self,
'nsfw': submission.over_18,
'author': author,
'title': submission.title,
'url': url,
'selftext': submission.selftext,
'score': submission.score,
'subreddit': submission.subreddit.display_name,
'distinguish': submission.distinguished,
'textlen': len(submission.selftext),
'num_comments': submission.num_comments,
'flair_text': submission.link_flair_text,
'flair_css_class': submission.link_flair_css_class,
'augmented_at': None,
'augmented_count': None,
}
(qmarks,
bindings) = sqlhelpers.insert_filler(SQL_SUBMISSION_COLUMNS,
postdata)
query = 'INSERT INTO submissions VALUES(%s)' % qmarks
cur.execute(query, bindings)
else:
selftext = self.check_for_edits(submission,
existing_entry=existing_entry)
query = '''
UPDATE submissions SET
nsfw = coalesce(?, nsfw),
score = coalesce(?, score),
selftext = coalesce(?, selftext),
distinguish = coalesce(?, distinguish),
num_comments = coalesce(?, num_comments),
flair_text = coalesce(?, flair_text),
flair_css_class = coalesce(?, flair_css_class)
WHERE idstr == ?
'''
bindings = [
submission.over_18, submission.score, selftext,
submission.distinguished, submission.num_comments,
submission.link_flair_text, submission.link_flair_css_class,
submission.fullname
]
cur.execute(query, bindings)
return existing_entry is None
