def sql_search_notes(user_id, first_notebook_id, search_text, database_backend): """ Return a SQL string to perform a full-text search for notes within notebooks readable by the given user whose contents contain the given search_text. This is a case-insensitive search. @type search_text: unicode @param search_text: text to search for within the notes """ if database_backend == Persistent.POSTGRESQL_BACKEND: # strip out all search operators search_text = Notebook.SEARCH_OPERATORS.sub(u"", search_text).strip() # join all words with boolean "and" operator search_text = u"&".join( Notebook.WHITESPACE_PATTERN.split(search_text)) return \ """ select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null, null, headline( drop_html_tags( contents ), query ) as summary from ( select note_current.id, note_current.revision, note_current.title, note_current.contents, note_current.notebook_id, note_current.startup, note_current.deleted_from_id, rank_cd( search, query ) as rank, note_current.user_id, null, null, query from note_current, user_notebook, to_tsquery( 'default', %s ) query where note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and note_current.deleted_from_id is null and query @@ search order by note_current.notebook_id = %s desc, rank desc limit 20 ) as sub; """ % ( quote( search_text ), quote( user_id ), quote( first_notebook_id ) ) else: search_text = search_text.strip().lower() # TODO: use SQLite's FTS (full text search) support instead return \ """ select note_current.* from note_current, user_notebook where note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and note_current.deleted_from_id is null and lower( note_current.contents ) like %s order by note_current.notebook_id = %s desc, note_current.rank desc limit 20 """ % ( quote( user_id ), quote_fuzzy( search_text ), quote( first_notebook_id ) )
def sql_search_notes( user_id, first_notebook_id, search_text, database_backend ): """ Return a SQL string to perform a full-text search for notes within notebooks readable by the given user whose contents contain the given search_text. This is a case-insensitive search. @type search_text: unicode @param search_text: text to search for within the notes """ if database_backend == Persistent.POSTGRESQL_BACKEND: # strip out all search operators search_text = Notebook.SEARCH_OPERATORS.sub( u"", search_text ).strip() # join all words with boolean "and" operator search_text = u"&".join( Notebook.WHITESPACE_PATTERN.split( search_text ) ) return \ """ select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null, null, headline( drop_html_tags( contents ), query ) as summary from ( select note_current.id, note_current.revision, note_current.title, note_current.contents, note_current.notebook_id, note_current.startup, note_current.deleted_from_id, rank_cd( search, query ) as rank, note_current.user_id, null, null, query from note_current, user_notebook, to_tsquery( 'default', %s ) query where note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and note_current.deleted_from_id is null and query @@ search order by note_current.notebook_id = %s desc, rank desc limit 20 ) as sub; """ % ( quote( search_text ), quote( user_id ), quote( first_notebook_id ) ) else: search_text = search_text.strip().lower() # TODO: use SQLite's FTS (full text search) support instead return \ """ select note_current.* from note_current, user_notebook where note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and note_current.deleted_from_id is null and lower( note_current.contents ) like %s order by note_current.notebook_id = %s desc, note_current.rank desc limit 20 """ % ( quote( user_id ), quote_fuzzy( search_text ), quote( first_notebook_id ) )
def sql_search_titles(notebook_id, search_text): """ Return a SQL string to perform a search for notes within the given notebook whose titles contain the given search_text. This is a case-insensitive search. @type search_text: unicode @param search_text: text to search for within the notes """ search_text = search_text.strip() return \ """ select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null, null, title as summary from note_current where notebook_id = %s and deleted_from_id is null and lower( title ) like %s order by revision desc limit 20; """ % ( quote( notebook_id ), quote_fuzzy( search_text.lower() ) )
def sql_search_titles( notebook_id, search_text ): """ Return a SQL string to perform a search for notes within the given notebook whose titles contain the given search_text. This is a case-insensitive search. @type search_text: unicode @param search_text: text to search for within the notes """ search_text = search_text.strip() return \ """ select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null, null, title as summary from note_current where notebook_id = %s and deleted_from_id is null and lower( title ) like %s order by revision desc limit 20; """ % ( quote( notebook_id ), quote_fuzzy( search_text.lower() ) )