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 )
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 ) )
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)
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 ) )
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),
)
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 ) )
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 ) )
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' ) )
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)
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 ) )
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 ) )
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' ) )
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))
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 )
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))
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 ),
)
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 ) )
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 ) )
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_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 ) )
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 ) )
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 )
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 ) )
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 ),
)
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_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 ) )
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 ) )
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 ) )
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 )
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 )
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 ) )
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 ) )
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 ) )
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 ) )
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 )
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 )
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 )
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 )
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 ) )
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 ) )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 )
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 ) )
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' ),
)
