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() ) )