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