示例#1
0
  def sql_load_notes_in_creation_order( self, start = 0, count = 10, reverse = False ):
    """
    Return a SQL string to load a list of the most recently created notes within this notebook.

    @type start: int or NoneType
    @param start: index of recent note to start with (defaults to 0, the most recent note)
    @type count: int or NoneType
    @param count: number of recent notes to return (defaults to 10 notes)
    @type reverse: bool or NoneType
    @param reverse: whether to reverse the chronological order of notes. so if reverse is True,
                    the oldest notes are returned instead of the newest (defaults to False)
    """
    if reverse:
      ordering = u"asc"
    else:
      ordering = u"desc"

    return \
      """
      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,
        note_current.rank, note_current.user_id, luminotes_user_current.username, note_creation.revision as creation
      from
        note_current, luminotes_user_current,
        ( select id, min( revision ) as revision from note where notebook_id = %s group by id ) as note_creation
      where
        notebook_id = %s and note_current.id = note_creation.id and
        note_current.user_id = luminotes_user_current.id
      order by
        creation %s
      limit %d offset %d;
      """ % ( quote( self.object_id ), quote( self.object_id ), ordering, count, start )
示例#2
0
 def sql_update_group_admin(self, group_id, admin=False):
     """
 Return a SQL string to update the user's group membership to have the given admin flag.
 """
     return \
       "update user_group set admin = %s where user_id = %s and group_id = %s;" % \
       ( quote( admin and 't' or 'f' ), quote( self.object_id ), quote( group_id ) )
示例#3
0
    def sql_id_exists(object_id, revision=None):
        if revision:
            return "select id from luminotes_user where id = %s and revision = %s;" % (
                quote(object_id), quote(revision))

        return "select id from luminotes_user_current where id = %s;" % quote(
            object_id)
示例#4
0
 def sql_update_notebook_rank( self, notebook_id, rank ):
   """
   Return a SQL string to update the user's rank for the given notebook.
   """
   return \
     "update user_notebook set rank = %s where user_id = %s and notebook_id = %s;" % \
     ( quote( rank ), quote( self.object_id ), quote( notebook_id ) )
示例#5
0
 def sql_update(self):
     return "update download_access set revision = %s, item_number = %s, transaction_id = %s where id = %s;" % (
         quote(self.revision),
         quote(self.__item_number),
         quote(self.__transaction_id),
         quote(self.object_id),
     )
示例#6
0
 def sql_update_group_admin( self, group_id, admin = False ):
   """
   Return a SQL string to update the user's group membership to have the given admin flag.
   """
   return \
     "update user_group set admin = %s where user_id = %s and group_id = %s;" % \
     ( quote( admin and 't' or 'f' ), quote( self.object_id ), quote( group_id ) )
示例#7
0
 def sql_save_notebook_tag( self, notebook_id, tag_id, value = None ):
   """
   Return a SQL string to associate a tag with a notebook of this user.
   """
   return \
     "insert into tag_notebook ( notebook_id, tag_id, value, user_id ) values " + \
     "( %s, %s, %s, %s );" % ( quote( notebook_id ), quote( tag_id ), quote( value ), quote( self.object_id ) )
示例#8
0
 def sql_save_group( self, group_id, admin = False ):
   """
   Return a SQL string to save the id of a group to which this user has membership.
   """
   return \
     "insert into user_group ( user_id, group_id, admin ) values " + \
     "( %s, %s, %s );" % ( quote( self.object_id ), quote( group_id ), quote( admin and 't' or 'f' ) )
示例#9
0
    def sql_load(object_id, revision=None):
        if revision:
            return "select * from notebook where id = %s and revision = %s;" % (
                quote(object_id), quote(revision))

        return "select * from notebook_current where id = %s;" % quote(
            object_id)
示例#10
0
 def sql_save_notebook_tag(self, notebook_id, tag_id, value=None):
     """
 Return a SQL string to associate a tag with a notebook of this user.
 """
     return \
       "insert into tag_notebook ( notebook_id, tag_id, value, user_id ) values " + \
       "( %s, %s, %s, %s );" % ( quote( notebook_id ), quote( tag_id ), quote( value ), quote( self.object_id ) )
示例#11
0
 def sql_update_notebook_rank(self, notebook_id, rank):
     """
 Return a SQL string to update the user's rank for the given notebook.
 """
     return \
       "update user_notebook set rank = %s where user_id = %s and notebook_id = %s;" % \
       ( quote( rank ), quote( self.object_id ), quote( notebook_id ) )
示例#12
0
 def sql_save_group(self, group_id, admin=False):
     """
 Return a SQL string to save the id of a group to which this user has membership.
 """
     return \
       "insert into user_group ( user_id, group_id, admin ) values " + \
       "( %s, %s, %s );" % ( quote( self.object_id ), quote( group_id ), quote( admin and 't' or 'f' ) )
示例#13
0
    def sql_load_note_by_id(self, note_id):
        """
    Return a SQL string to load a particular note within this notebook by the note's id.

    @type note_id: unicode
    @param note_id: id of note to load
    """
        return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s and id = %s;" % (
            quote(self.object_id), quote(note_id))
示例#14
0
    def sql_count_notebooks(self,
                            parents_only=False,
                            undeleted_only=False,
                            read_write=False,
                            tag_name=None,
                            tag_value=None,
                            exclude_notebook_name=None):
        """
    Return a SQL string to count the number notebooks to which this user has access.
    """
        if parents_only:
            parents_only_clause = " and trash_id is not null"
        else:
            parents_only_clause = ""

        if undeleted_only:
            undeleted_only_clause = " and deleted = 'f'"
        else:
            undeleted_only_clause = ""

        if read_write:
            read_write_clause = " and user_notebook.read_write = 't'"
        else:
            read_write_clause = ""

        if tag_name:
            tag_tables = ", tag_notebook, tag"
            tag_clause = \
              """
         and tag_notebook.tag_id = tag.id and tag_notebook.user_id = %s and
        tag_notebook.notebook_id = notebook_current.id and tag.name = %s
        """ % ( quote( self.object_id ), quote( tag_name ) )

            if tag_value:
                tag_clause += " and tag_notebook.value = %s" % quote(tag_value)
        else:
            tag_tables = ""
            tag_clause = ""

        if exclude_notebook_name:
            notebook_name_clause = " and not notebook_current.name = %s" % quote(
                exclude_notebook_name)
        else:
            notebook_name_clause = ""

        return \
          """
      select
        count( notebook_current.id )
      from
        user_notebook, notebook_current%s
      where
        user_notebook.user_id = %s%s%s%s%s%s and
        user_notebook.notebook_id = notebook_current.id;
      """ % ( tag_tables, quote( self.object_id ), parents_only_clause, undeleted_only_clause,
                  read_write_clause, tag_clause, notebook_name_clause )
示例#15
0
    def sql_load_note_by_title(self, title):
        """
    Return a SQL string to load a particular note within this notebook by the note's title. The
    title lookup is performed case-insensitively.

    @type note_id: unicode
    @param note_id: title of note to load
    """
        return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s and lower( title ) = lower( %s );" % (
            quote(self.object_id), quote(title))
示例#16
0
 def sql_update_access( self, notebook_id, read_write = Notebook.READ_ONLY, owner = False ):
   """
   Return a SQL string to update the user's notebook access to the given read_write and owner level.
   """
   return \
     "update user_notebook set read_write = %s, owner = %s, own_notes_only = %s where user_id = %s and notebook_id = %s;" % (
       quote( ( read_write not in ( Notebook.READ_ONLY, False ) ) and 't' or 'f' ),
       quote( owner and 't' or 'f' ),
       quote( ( read_write == Notebook.READ_WRITE_FOR_OWN_NOTES ) and 't' or 'f' ),
       quote( self.object_id ),
       quote( notebook_id ),
     )
示例#17
0
 def sql_in_group( self, group_id, admin = False ):
   """
   Return a SQL string to determine whether this has membership to the given group.
   """
   if admin is True:
     return \
       "select user_id from user_group where user_id = %s and group_id = %s and admin = 't';" % \
       ( quote( self.object_id ), quote( group_id ) )
   else:
     return \
       "select user_id from user_group where user_id = %s and group_id = %s;" % \
       ( quote( self.object_id ), quote( group_id ) )
示例#18
0
 def sql_in_group(self, group_id, admin=False):
     """
 Return a SQL string to determine whether this has membership to the given group.
 """
     if admin is True:
         return \
           "select user_id from user_group where user_id = %s and group_id = %s and admin = 't';" % \
           ( quote( self.object_id ), quote( group_id ) )
     else:
         return \
           "select user_id from user_group where user_id = %s and group_id = %s;" % \
           ( quote( self.object_id ), quote( group_id ) )
示例#19
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 ) )
示例#20
0
  def sql_update( self ):
    rank = self.__rank
    if rank is None:
      rank = quote( None )

    # this relies on a database trigger to copy the updated row into the note table
    return \
      """
      update note_current set id = %s, revision = %s, title = %s, contents = %s, notebook_id = %s,
      startup = %s, deleted_from_id = %s, rank = %s, user_id = %s where id = %s;
      """ % \
      ( quote( self.object_id ), quote( self.revision ), quote( self.__title ),
        quote( self.__contents ), quote( self.__notebook_id ), quote( self.__startup and 't' or 'f' ),
        quote( self.__deleted_from_id ), rank, quote( self.user_id ), quote( self.object_id ) )
示例#21
0
 def sql_load_similar(self):
     # select invites with the same notebook_id, and email_address as this invite
     return \
       """
   select
     invite.id, invite.revision, invite.from_user_id, invite.notebook_id, invite.email_address,
     invite.read_write, invite.owner, invite.redeemed_user_id, luminotes_user_current.username
   from
     invite left outer join luminotes_user_current
   on
     ( invite.redeemed_user_id = luminotes_user_current.id )
   where
     invite.notebook_id = %s and invite.email_address = %s and invite.id != %s;
   """ % ( quote( self.__notebook_id ), quote( self.__email_address ), quote( self.object_id ) )
示例#22
0
  def sql_count_notebooks( self, parents_only = False, undeleted_only = False, read_write = False,
                           tag_name = None, tag_value = None, exclude_notebook_name = None ):
    """
    Return a SQL string to count the number notebooks to which this user has access.
    """
    if parents_only:
      parents_only_clause = " and trash_id is not null"
    else:
      parents_only_clause = ""

    if undeleted_only:
      undeleted_only_clause = " and deleted = 'f'"
    else:
      undeleted_only_clause = ""

    if read_write:
      read_write_clause = " and user_notebook.read_write = 't'"
    else:
      read_write_clause = ""

    if tag_name:
      tag_tables = ", tag_notebook, tag"
      tag_clause = \
        """
         and tag_notebook.tag_id = tag.id and tag_notebook.user_id = %s and
        tag_notebook.notebook_id = notebook_current.id and tag.name = %s
        """ % ( quote( self.object_id ), quote( tag_name ) )

      if tag_value:
        tag_clause += " and tag_notebook.value = %s" % quote( tag_value )
    else:
      tag_tables = ""
      tag_clause = ""

    if exclude_notebook_name:
      notebook_name_clause = " and not notebook_current.name = %s" % quote( exclude_notebook_name )
    else:
      notebook_name_clause = ""

    return \
      """
      select
        count( notebook_current.id )
      from
        user_notebook, notebook_current%s
      where
        user_notebook.user_id = %s%s%s%s%s%s and
        user_notebook.notebook_id = notebook_current.id;
      """ % ( tag_tables, quote( self.object_id ), parents_only_clause, undeleted_only_clause,
              read_write_clause, tag_clause, notebook_name_clause )
示例#23
0
 def sql_load_similar( self ):
   # select invites with the same notebook_id, and email_address as this invite
   return \
     """
     select
       invite.id, invite.revision, invite.from_user_id, invite.notebook_id, invite.email_address,
       invite.read_write, invite.owner, invite.redeemed_user_id, luminotes_user_current.username
     from
       invite left outer join luminotes_user_current
     on
       ( invite.redeemed_user_id = luminotes_user_current.id )
     where
       invite.notebook_id = %s and invite.email_address = %s and invite.id != %s;
     """ % ( quote( self.__notebook_id ), quote( self.__email_address ), quote( self.object_id ) )
示例#24
0
 def sql_update_access(self,
                       notebook_id,
                       read_write=Notebook.READ_ONLY,
                       owner=False):
     """
 Return a SQL string to update the user's notebook access to the given read_write and owner level.
 """
     return \
       "update user_notebook set read_write = %s, owner = %s, own_notes_only = %s where user_id = %s and notebook_id = %s;" % (
         quote( ( read_write not in ( Notebook.READ_ONLY, False ) ) and 't' or 'f' ),
         quote( owner and 't' or 'f' ),
         quote( ( read_write == Notebook.READ_WRITE_FOR_OWN_NOTES ) and 't' or 'f' ),
         quote( self.object_id ),
         quote( notebook_id ),
       )
示例#25
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 ) )
示例#26
0
  def sql_create( self ):
    rank = self.__rank
    if rank is None:
      rank = quote( None )

    # this relies on a database trigger to copy the new row into the note table
    return \
      "insert into note_current ( id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id ) " + \
      "values ( %s, %s, %s, %s, %s, %s, %s, %s, %s );" % \
      ( quote( self.object_id ), quote( self.revision ), quote( self.__title ),
        quote( self.__contents ), quote( self.__notebook_id ), quote( self.__startup and 't' or 'f' ),
        quote( self.__deleted_from_id ), rank, quote( self.user_id ) )
示例#27
0
 def sql_count_notes(self):
     """
 Return a SQL string to count the total number of notes in this notebook.
 """
     return \
       "select count( id ) from note_current where notebook_id = %s;" % \
       ( quote( self.object_id ) )
示例#28
0
 def sql_count_notes( self ):
   """
   Return a SQL string to count the total number of notes in this notebook.
   """
   return \
     "select count( id ) from note_current where notebook_id = %s;" % \
     ( quote( self.object_id ) )
示例#29
0
 def sql_increment_rank(self, start_note_rank):
     """
 Return a SQL string to increment the rank for every note in this notebook (in rank order)
 starting from the given note rank. Notes before the given note rank are not updated.
 """
     return \
       """
   update
     note_current
   set
     rank = rank + 1,
     revision = %s
   where
     notebook_id = %s and
     rank is not null and
     rank >= %s;
   """ % ( quote( datetime.now( tz = utc ) ), quote( self.object_id ), start_note_rank )
示例#30
0
 def sql_increment_rank( self, start_note_rank ):
   """
   Return a SQL string to increment the rank for every note in this notebook (in rank order)
   starting from the given note rank. Notes before the given note rank are not updated.
   """
   return \
     """
     update
       note_current
     set
       rank = rank + 1,
       revision = %s
     where
       notebook_id = %s and
       rank is not null and
       rank >= %s;
     """ % ( quote( datetime.now( tz = utc ) ), quote( self.object_id ), start_note_rank )
示例#31
0
 def sql_load_tags(self, user_id):
     """
 Return a SQL string to load a list of all the tags associated with this notebook by the given
 user.
 """
     return \
       """
   select
     tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description, tag_notebook.value
   from
     tag_notebook, tag
   where
     tag_notebook.notebook_id = %s and 
     tag_notebook.user_id = %s and
     tag_notebook.tag_id = tag.id
   order by tag.name;
   """ % ( quote( self.object_id ), quote( user_id ) )
示例#32
0
 def sql_load_tags( self, user_id ):
   """
   Return a SQL string to load a list of all the tags associated with this notebook by the given
   user.
   """
   return \
     """
     select
       tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description, tag_notebook.value
     from
       tag_notebook, tag
     where
       tag_notebook.notebook_id = %s and 
       tag_notebook.user_id = %s and
       tag_notebook.tag_id = tag.id
     order by tag.name;
     """ % ( quote( self.object_id ), quote( user_id ) )
示例#33
0
 def sql_revoke_invite_access( notebook_id, trash_id, email_address ):
   return \
     """
     delete from
       user_notebook
     where
       notebook_id in ( %s, %s ) and
       user_notebook.user_id in (
         select
           redeemed_user_id
         from
           invite
         where
           notebook_id = %s and
           email_address = %s
       );
     """ % ( quote( notebook_id ), quote( trash_id ), quote( notebook_id ), quote( email_address ) )
示例#34
0
 def sql_revoke_invite_access(notebook_id, trash_id, email_address):
     return \
       """
   delete from
     user_notebook
   where
     notebook_id in ( %s, %s ) and
     user_notebook.user_id in (
       select
         redeemed_user_id
       from
         invite
       where
         notebook_id = %s and
         email_address = %s
     );
   """ % ( quote( notebook_id ), quote( trash_id ), quote( notebook_id ), quote( email_address ) )
示例#35
0
 def sql_load_note_files(note_id):
     return \
       """
   select
     file.id, file.revision, file.notebook_id, file.note_id, file.filename, file.size_bytes, file.content_type
   from
     file
   where
     file.note_id = %s;
   """ % quote( note_id )
示例#36
0
 def sql_load_note_files( note_id ):
   return \
     """
     select
       file.id, file.revision, file.notebook_id, file.note_id, file.filename, file.size_bytes, file.content_type
     from
       file
     where
       file.note_id = %s;
     """ % quote( note_id )
示例#37
0
文件: Tag.py 项目: osborne6/luminotes
  def sql_load_by_name( name, notebook_id = None, user_id = None ):
    if notebook_id:
      notebook_id_clause = " and tag.notebook_id = %s" % quote( notebook_id )
    else:
      notebook_id_clause = ""

    if user_id:
      user_id_clause = " and tag.user_id = %s" % quote( user_id )
    else:
      user_id_clause = ""

    return \
      """
      select
        tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description
      from
        tag
      where
        tag.name = %s%s%s;
      """ % ( quote( name ), notebook_id_clause, user_id_clause )
示例#38
0
 def sql_load_revisions( self ):
   return """ \
     select
       note.revision, luminotes_user_current.id, username
     from
       note left outer join luminotes_user_current
     on
       ( note.user_id = luminotes_user_current.id )
     where
       note.id = %s order by note.revision;
   """ % quote( self.object_id )
示例#39
0
    def sql_calculate_storage(self, database_backend):
        """
    Return a SQL string to calculate the total bytes of storage usage by this user. This includes
    storage for all the user's notes (including past revisions) and their uploaded files. It does
    not include storage for the notebooks themselves.
    """
        if database_backend == Persistent.POSTGRESQL_BACKEND:
            # this counts bytes for the contents of each column
            note_size_clause = "pg_column_size( note.* )"
        else:
            # this isn't perfect, because length() counts UTF-8 characters instead of bytes.
            # some columns are left out because they can be null, which screws up the addition
            note_size_clause = \
              """
        length( note.id ) + length( note.revision ) + length( note.title ) + length( note.contents ) +
        length( note.notebook_id ) + length( note.startup ) + length( note.user_id )
        """

        return \
          """
      select * from (
        select
          coalesce( sum( %s ), 0 )
        from
          user_notebook, note
        where
          user_notebook.user_id = %s and
          user_notebook.owner = 't' and
          note.notebook_id = user_notebook.notebook_id
      ) as note_storage,
      (
        select
          coalesce( sum( file.size_bytes ), 0 )
        from
          user_notebook, file
        where
          user_notebook.user_id = %s and
          user_notebook.owner = 't' and
          file.notebook_id = user_notebook.notebook_id
      ) as file_storage;
      """ % ( note_size_clause, quote( self.object_id ), quote( self.object_id ) )
示例#40
0
  def sql_calculate_storage( self, database_backend ):
    """
    Return a SQL string to calculate the total bytes of storage usage by this user. This includes
    storage for all the user's notes (including past revisions) and their uploaded files. It does
    not include storage for the notebooks themselves.
    """
    if database_backend == Persistent.POSTGRESQL_BACKEND:
      # this counts bytes for the contents of each column
      note_size_clause = "pg_column_size( note.* )"
    else:
      # this isn't perfect, because length() counts UTF-8 characters instead of bytes.
      # some columns are left out because they can be null, which screws up the addition
      note_size_clause = \
        """
        length( note.id ) + length( note.revision ) + length( note.title ) + length( note.contents ) +
        length( note.notebook_id ) + length( note.startup ) + length( note.user_id )
        """

    return \
      """
      select * from (
        select
          coalesce( sum( %s ), 0 )
        from
          user_notebook, note
        where
          user_notebook.user_id = %s and
          user_notebook.owner = 't' and
          note.notebook_id = user_notebook.notebook_id
      ) as note_storage,
      (
        select
          coalesce( sum( file.size_bytes ), 0 )
        from
          user_notebook, file
        where
          user_notebook.user_id = %s and
          user_notebook.owner = 't' and
          file.notebook_id = user_notebook.notebook_id
      ) as file_storage;
      """ % ( note_size_clause, quote( self.object_id ), quote( self.object_id ) )
示例#41
0
    def sql_load_by_name(name, notebook_id=None, user_id=None):
        if notebook_id:
            notebook_id_clause = " and tag.notebook_id = %s" % quote(
                notebook_id)
        else:
            notebook_id_clause = ""

        if user_id:
            user_id_clause = " and tag.user_id = %s" % quote(user_id)
        else:
            user_id_clause = ""

        return \
          """
      select
        tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description
      from
        tag
      where
        tag.name = %s%s%s;
      """ % ( quote( name ), notebook_id_clause, user_id_clause )
示例#42
0
 def sql_create( self ):
   return \
     "insert into luminotes_user ( id, revision, username, salt, password_hash, email_address, storage_bytes, rate_plan ) " + \
     "values ( %s, %s, %s, %s, %s, %s, %s, %s );" % \
     ( quote( self.object_id ), quote( self.revision ), quote( self.__username ),
       quote( self.__salt ), quote( self.__password_hash ), quote( self.__email_address ),
       self.__storage_bytes, self.__rate_plan )
示例#43
0
 def sql_create(self):
     return \
       "insert into luminotes_user ( id, revision, username, salt, password_hash, email_address, storage_bytes, rate_plan ) " + \
       "values ( %s, %s, %s, %s, %s, %s, %s, %s );" % \
       ( quote( self.object_id ), quote( self.revision ), quote( self.__username ),
         quote( self.__salt ), quote( self.__password_hash ), quote( self.__email_address ),
         self.__storage_bytes, self.__rate_plan )
示例#44
0
  def sql_load( object_id, revision = None ):
    # Files don't store old revisions
    if revision:
      raise NotImplementedError()

    return \
      """
      select
        file.id, file.revision, file.notebook_id, file.note_id, file.filename, file.size_bytes, file.content_type
      from
        file
      where
        file.id = %s;
      """ % quote( object_id )
示例#45
0
文件: Tag.py 项目: osborne6/luminotes
  def sql_load( object_id, revision = None ):
    # Tags don't store old revisions
    if revision:
      raise NotImplementedError()

    return \
      """
      select
        tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description
      from
        tag
      where
        tag.id = %s;
      """ % quote( object_id )
示例#46
0
    def sql_load(object_id, revision=None):
        # Files don't store old revisions
        if revision:
            raise NotImplementedError()

        return \
          """
      select
        file.id, file.revision, file.notebook_id, file.note_id, file.filename, file.size_bytes, file.content_type
      from
        file
      where
        file.id = %s;
      """ % quote( object_id )
示例#47
0
    def sql_load(object_id, revision=None):
        # Tags don't store old revisions
        if revision:
            raise NotImplementedError()

        return \
          """
      select
        tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description
      from
        tag
      where
        tag.id = %s;
      """ % quote( object_id )
示例#48
0
    def sql_load_notes_in_creation_order(self,
                                         start=0,
                                         count=10,
                                         reverse=False):
        """
    Return a SQL string to load a list of the most recently created notes within this notebook.

    @type start: int or NoneType
    @param start: index of recent note to start with (defaults to 0, the most recent note)
    @type count: int or NoneType
    @param count: number of recent notes to return (defaults to 10 notes)
    @type reverse: bool or NoneType
    @param reverse: whether to reverse the chronological order of notes. so if reverse is True,
                    the oldest notes are returned instead of the newest (defaults to False)
    """
        if reverse:
            ordering = u"asc"
        else:
            ordering = u"desc"

        return \
          """
      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,
        note_current.rank, note_current.user_id, luminotes_user_current.username, note_creation.revision as creation
      from
        note_current, luminotes_user_current,
        ( select id, min( revision ) as revision from note where notebook_id = %s group by id ) as note_creation
      where
        notebook_id = %s and note_current.id = note_creation.id and
        note_current.user_id = luminotes_user_current.id
      order by
        creation %s
      limit %d offset %d;
      """ % ( quote( self.object_id ), quote( self.object_id ), ordering, count, start )
示例#49
0
 def sql_load_groups(self):
     """
 Return a SQL string to load a list of the groups to which this user has membership.
 """
     return \
       """
   select
     luminotes_group_current.*, user_group.admin
   from
     user_group, luminotes_group_current
   where
     user_group.user_id = %s and
     user_group.group_id = luminotes_group_current.id
   order by luminotes_group_current.name;
   """ % quote( self.object_id )
示例#50
0
 def sql_load_groups( self ):
   """
   Return a SQL string to load a list of the groups to which this user has membership.
   """
   return \
     """
     select
       luminotes_group_current.*, user_group.admin
     from
       user_group, luminotes_group_current
     where
       user_group.user_id = %s and
       user_group.group_id = luminotes_group_current.id
     order by luminotes_group_current.name;
     """ % quote( self.object_id )
示例#51
0
 def sql_load_note_ids_starting_from_rank(self, start_note_rank):
     """
 Return a SQL string to load a list of all the note ids with rank greater than or equal to the
 given rank.
 """
     return \
       """
   select
     id
   from
     note_current
   where
     notebook_id = %s and
     rank is not null and
     rank >= %s;
   """ % ( quote( self.object_id ), start_note_rank )
示例#52
0
 def sql_load_notebook_invites(notebook_id):
     # select a list of invites to the given notebook
     return \
       """
   select
     invite.id, invite.revision, invite.from_user_id, invite.notebook_id, invite.email_address,
     invite.read_write, invite.owner, invite.redeemed_user_id, luminotes_user_current.username
   from
     invite left outer join luminotes_user_current
   on
     ( invite.redeemed_user_id = luminotes_user_current.id )
   where
     invite.notebook_id = %s
   order by
     invite.email_address, invite.redeemed_user_id;
   """ % quote( notebook_id )
示例#53
0
 def sql_load_note_ids_starting_from_rank( self, start_note_rank ):
   """
   Return a SQL string to load a list of all the note ids with rank greater than or equal to the
   given rank.
   """
   return \
     """
     select
       id
     from
       note_current
     where
       notebook_id = %s and
       rank is not null and
       rank >= %s;
     """ % ( quote( self.object_id ), start_note_rank )
示例#54
0
 def sql_load_notebook_invites( notebook_id ):
   # select a list of invites to the given notebook
   return \
     """
     select
       invite.id, invite.revision, invite.from_user_id, invite.notebook_id, invite.email_address,
       invite.read_write, invite.owner, invite.redeemed_user_id, luminotes_user_current.username
     from
       invite left outer join luminotes_user_current
     on
       ( invite.redeemed_user_id = luminotes_user_current.id )
     where
       invite.notebook_id = %s
     order by
       invite.email_address, invite.redeemed_user_id;
     """ % quote( notebook_id )
示例#55
0
  def sql_load( object_id, revision = None ):
    # invites don't store old revisions
    if revision:
      raise NotImplementedError()

    return \
      """
      select
        invite.id, invite.revision, invite.from_user_id, invite.notebook_id, invite.email_address,
        invite.read_write, invite.owner, invite.redeemed_user_id, luminotes_user_current.username
      from
        invite left outer join luminotes_user_current
      on
        ( invite.redeemed_user_id = luminotes_user_current.id )
      where
        invite.id = %s;
      """ % quote( object_id )
示例#56
0
 def sql_has_access( self, notebook_id, read_write = False, owner = False ):
   """
   Return a SQL string to determine whether this user has access to the given notebook.
   """
   if read_write is True and owner is True:
     return \
       "select user_id from user_notebook where user_id = %s and notebook_id = %s and read_write = 't' and owner = 't';" % \
       ( quote( self.object_id ), quote( notebook_id ) )
   elif read_write is True:
     return \
       "select user_id from user_notebook where user_id = %s and notebook_id = %s and read_write = 't';" % \
       ( quote( self.object_id ), quote( notebook_id ) )
   elif owner is True:
     return \
       "select user_id from user_notebook where user_id = %s and notebook_id = %s and owner = 't';" % \
       ( quote( self.object_id ), quote( notebook_id ) )
   else:
     return \
       "select user_id from user_notebook where user_id = %s and notebook_id = %s;" % \
       ( quote( self.object_id ), quote( notebook_id ) )
示例#57
0
  def sql_save_notebook( self, notebook_id, read_write = True, owner = True, rank = None, own_notes_only = False ):
    """
    Return a SQL string to save the id of a notebook to which this user has access.
    """
    if rank is None: rank = quote( None )

    return \
      "insert into user_notebook ( user_id, notebook_id, read_write, owner, rank, own_notes_only ) values " + \
      "( %s, %s, %s, %s, %s, %s );" % (
        quote( self.object_id ),
        quote( notebook_id ),
        quote( read_write and 't' or 'f' ),
        quote( owner and 't' or 'f' ),
        rank,
        quote( own_notes_only and 't' or 'f' ),
      )