Beispiel #1
0
    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 ) )
Beispiel #2
0
  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 ) )
Beispiel #3
0
    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() ) )
Beispiel #4
0
  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() ) )