Personal wiki notebook (not under development)
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

Notebook.py 19KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471
  1. import re
  2. from copy import copy
  3. from Note import Note
  4. from Persistent import Persistent, quote, quote_fuzzy
  5. from datetime import datetime
  6. from pytz import utc
  7. class Notebook( Persistent ):
  8. """
  9. A collection of wiki notes.
  10. """
  11. WHITESPACE_PATTERN = re.compile( r"\s+" )
  12. SEARCH_OPERATORS = re.compile( r"[&|!()'\\:]" )
  13. READ_ONLY = 0 # user can only view the notes within this notebook
  14. READ_WRITE = 1 # user can view and edit the notes within this notebook
  15. READ_WRITE_FOR_OWN_NOTES = 2 # user can only edit their own notes, not notes created by others
  16. def __init__( self, object_id, revision = None, name = None, trash_id = None, deleted = False,
  17. user_id = None, read_write = None, owner = True, rank = None, own_notes_only = False, note_count = None ):
  18. """
  19. Create a new notebook with the given id and name.
  20. @type object_id: unicode
  21. @param object_id: id of the notebook
  22. @type revision: datetime or NoneType
  23. @param revision: revision timestamp of the object (optional, defaults to now)
  24. @type name: unicode or NoneType
  25. @param name: name of this notebook (optional)
  26. @type trash_id: Notebook or NoneType
  27. @param trash_id: id of the notebook where deleted notes from this notebook go to die (optional)
  28. @type deleted: bool or NoneType
  29. @param deleted: whether this notebook is currently deleted (optional, defaults to False)
  30. @type user_id: unicode or NoneType
  31. @param user_id: id of the user who most recently updated this notebook object (optional)
  32. @type read_write: bool or NoneType
  33. @param read_write: whether this view of the notebook is currently read-write. one of:
  34. READ_ONLY, READ_WRITE, READ_WRITE_FOR_OWN_NOTES (optional, defaults to READ_WRITE)
  35. @type owner: bool or NoneType
  36. @param owner: whether this view of the notebook currently has owner-level access (optional, defaults to True)
  37. @type rank: float or NoneType
  38. @param rank: indicates numeric ordering of this note in relation to other notebooks
  39. @type own_notes_only: bool or NoneType
  40. @param own_notes_only: True makes read_write be READ_WRITE_FOR_OWN_NOTES (optional, defaults to False)
  41. @type note_count: int or NoneType
  42. @param note_count: a count of the number of notes within this notebook (optional)
  43. @rtype: Notebook
  44. @return: newly constructed notebook
  45. """
  46. Persistent.__init__( self, object_id, revision )
  47. self.__name = name
  48. self.__trash_id = trash_id
  49. self.__deleted = deleted
  50. self.__user_id = user_id
  51. read_write = {
  52. None: Notebook.READ_WRITE,
  53. True: Notebook.READ_WRITE,
  54. False: Notebook.READ_ONLY,
  55. }.get( read_write, read_write )
  56. if own_notes_only is True and read_write != Notebook.READ_ONLY:
  57. read_write = Notebook.READ_WRITE_FOR_OWN_NOTES
  58. self.__read_write = read_write
  59. self.__owner = owner
  60. self.__rank = rank
  61. self.__note_count = note_count
  62. self.__tags = []
  63. @staticmethod
  64. def create( object_id, name = None, trash_id = None, deleted = False, user_id = None, read_write = None, owner = True, rank = None, own_notes_only = False, note_count = None ):
  65. """
  66. Convenience constructor for creating a new notebook.
  67. @type object_id: unicode
  68. @param object_id: id of the notebook
  69. @type name: unicode or NoneType
  70. @param name: name of this notebook (optional)
  71. @type trash_id: Notebook or NoneType
  72. @param trash_id: id of the notebook where deleted notes from this notebook go to die (optional)
  73. @type deleted: bool or NoneType
  74. @param deleted: whether this notebook is currently deleted (optional, defaults to False)
  75. @type user_id: unicode or NoneType
  76. @param user_id: id of the user who most recently updated this notebook object (optional)
  77. @type read_write: bool or NoneType
  78. @param read_write: whether this view of the notebook is currently read-write. one of:
  79. READ_ONLY, READ_WRITE, READ_WRITE_FOR_OWN_NOTES (optional, defaults to READ_WRITE)
  80. @type owner: bool or NoneType
  81. @param owner: whether this view of the notebook currently has owner-level access (optional, defaults to True)
  82. @type rank: float or NoneType
  83. @param rank: indicates numeric ordering of this note in relation to other notebooks
  84. @type own_notes_only: bool or NoneType
  85. @param own_notes_only: True makes read_write be READ_WRITE_FOR_OWN_NOTES (optional, defaults to False)
  86. @type note_count: int or NoneType
  87. @param note_count: a count of the number of notes within this notebook (optional)
  88. @rtype: Notebook
  89. @return: newly constructed notebook
  90. """
  91. return Notebook( object_id, name = name, trash_id = trash_id, user_id = user_id, read_write = read_write, owner = owner, rank = rank, own_notes_only = own_notes_only, note_count = note_count )
  92. @staticmethod
  93. def sql_load( object_id, revision = None ):
  94. if revision:
  95. return "select * from notebook where id = %s and revision = %s;" % ( quote( object_id ), quote( revision ) )
  96. return "select * from notebook_current where id = %s;" % quote( object_id )
  97. @staticmethod
  98. def sql_id_exists( object_id, revision = None ):
  99. if revision:
  100. return "select id from notebook where id = %s and revision = %s;" % ( quote( object_id ), quote( revision ) )
  101. return "select id from notebook_current where id = %s;" % quote( object_id )
  102. def sql_exists( self ):
  103. return Notebook.sql_id_exists( self.object_id, self.revision )
  104. def sql_create( self ):
  105. return \
  106. "insert into notebook ( id, revision, name, trash_id, deleted, user_id ) " + \
  107. "values ( %s, %s, %s, %s, %s, %s );" % \
  108. ( quote( self.object_id ), quote( self.revision ), quote( self.__name ),
  109. quote( self.__trash_id ), quote( self.deleted ), quote( self.user_id ) )
  110. def sql_update( self ):
  111. return self.sql_create()
  112. @staticmethod
  113. def sql_load_by_friendly_id( friendly_id ):
  114. return "select * from notebook_current where friendly_id( name ) = %s;" % quote( friendly_id )
  115. def sql_load_notes_in_rank_order( self, start = 0, count = None ):
  116. """
  117. Return a SQL string to load a list of all the notes within this notebook.
  118. Note: If the database backend is SQLite, a start parameter cannot be given without also
  119. providing a count parameter.
  120. """
  121. if count is not None:
  122. limit_clause = " limit %s" % count
  123. else:
  124. limit_clause = ""
  125. if start:
  126. offset_clause = " offset %s" % start
  127. else:
  128. offset_clause = ""
  129. return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s order by rank%s%s;" % ( quote( self.object_id ), limit_clause, offset_clause )
  130. def sql_load_notes_in_update_order( self, start = 0, count = None ):
  131. """
  132. Return a SQL string to load a list of all the notes within this notebook.
  133. Note: If the database backend is SQLite, a start parameter cannot be given without also
  134. providing a count parameter.
  135. """
  136. if count is not None:
  137. limit_clause = " limit %s" % count
  138. else:
  139. limit_clause = ""
  140. if start:
  141. offset_clause = " offset %s" % start
  142. else:
  143. offset_clause = ""
  144. return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s order by revision desc%s%s;" % ( quote( self.object_id ), limit_clause, offset_clause )
  145. def sql_load_non_startup_notes( self ):
  146. """
  147. Return a SQL string to load a list of the non-startup notes within this notebook.
  148. """
  149. return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s and startup = 'f' order by rank;" % quote( self.object_id )
  150. def sql_load_startup_notes( self ):
  151. """
  152. Return a SQL string to load a list of the startup notes within this notebook.
  153. """
  154. return "select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id from note_current where notebook_id = %s and startup = 't' order by rank;" % quote( self.object_id )
  155. def sql_load_notes_in_creation_order( self, start = 0, count = 10, reverse = False ):
  156. """
  157. Return a SQL string to load a list of the most recently created notes within this notebook.
  158. @type start: int or NoneType
  159. @param start: index of recent note to start with (defaults to 0, the most recent note)
  160. @type count: int or NoneType
  161. @param count: number of recent notes to return (defaults to 10 notes)
  162. @type reverse: bool or NoneType
  163. @param reverse: whether to reverse the chronological order of notes. so if reverse is True,
  164. the oldest notes are returned instead of the newest (defaults to False)
  165. """
  166. if reverse:
  167. ordering = u"asc"
  168. else:
  169. ordering = u"desc"
  170. return \
  171. """
  172. select
  173. note_current.id, note_current.revision, note_current.title, note_current.contents,
  174. note_current.notebook_id, note_current.startup, note_current.deleted_from_id,
  175. note_current.rank, note_current.user_id, luminotes_user_current.username, note_creation.revision as creation
  176. from
  177. note_current, luminotes_user_current,
  178. ( select id, min( revision ) as revision from note where notebook_id = %s group by id ) as note_creation
  179. where
  180. notebook_id = %s and note_current.id = note_creation.id and
  181. note_current.user_id = luminotes_user_current.id
  182. order by
  183. creation %s
  184. limit %d offset %d;
  185. """ % ( quote( self.object_id ), quote( self.object_id ), ordering, count, start )
  186. def sql_load_note_by_id( self, note_id ):
  187. """
  188. Return a SQL string to load a particular note within this notebook by the note's id.
  189. @type note_id: unicode
  190. @param note_id: id of note to load
  191. """
  192. 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 ) )
  193. def sql_load_note_by_title( self, title ):
  194. """
  195. Return a SQL string to load a particular note within this notebook by the note's title. The
  196. title lookup is performed case-insensitively.
  197. @type note_id: unicode
  198. @param note_id: title of note to load
  199. """
  200. 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 ) )
  201. @staticmethod
  202. def sql_search_notes( user_id, first_notebook_id, search_text, database_backend ):
  203. """
  204. Return a SQL string to perform a full-text search for notes within notebooks readable by the
  205. given user whose contents contain the given search_text. This is a case-insensitive search.
  206. @type search_text: unicode
  207. @param search_text: text to search for within the notes
  208. """
  209. if database_backend == Persistent.POSTGRESQL_BACKEND:
  210. # strip out all search operators
  211. search_text = Notebook.SEARCH_OPERATORS.sub( u"", search_text ).strip()
  212. # join all words with boolean "and" operator
  213. search_text = u"&".join( Notebook.WHITESPACE_PATTERN.split( search_text ) )
  214. return \
  215. """
  216. select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null,
  217. null, headline( drop_html_tags( contents ), query ) as summary from (
  218. select
  219. note_current.id, note_current.revision, note_current.title, note_current.contents,
  220. note_current.notebook_id, note_current.startup, note_current.deleted_from_id,
  221. rank_cd( search, query ) as rank, note_current.user_id, null, null, query
  222. from
  223. note_current, user_notebook, to_tsquery( 'default', %s ) query
  224. where
  225. note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and
  226. note_current.deleted_from_id is null and
  227. query @@ search order by note_current.notebook_id = %s desc, rank desc limit 20
  228. ) as sub;
  229. """ % ( quote( search_text ), quote( user_id ),
  230. quote( first_notebook_id ) )
  231. else:
  232. search_text = search_text.strip().lower()
  233. # TODO: use SQLite's FTS (full text search) support instead
  234. return \
  235. """
  236. select
  237. note_current.*
  238. from
  239. note_current, user_notebook
  240. where
  241. note_current.notebook_id = user_notebook.notebook_id and user_notebook.user_id = %s and
  242. note_current.deleted_from_id is null and
  243. lower( note_current.contents ) like %s
  244. order by note_current.notebook_id = %s desc, note_current.rank desc limit 20
  245. """ % ( quote( user_id ), quote_fuzzy( search_text ), quote( first_notebook_id ) )
  246. @staticmethod
  247. def sql_search_titles( notebook_id, search_text ):
  248. """
  249. Return a SQL string to perform a search for notes within the given notebook whose titles contain
  250. the given search_text. This is a case-insensitive search.
  251. @type search_text: unicode
  252. @param search_text: text to search for within the notes
  253. """
  254. search_text = search_text.strip()
  255. return \
  256. """
  257. select id, revision, title, contents, notebook_id, startup, deleted_from_id, rank, user_id, null, null,
  258. title as summary
  259. from
  260. note_current
  261. where
  262. notebook_id = %s and
  263. deleted_from_id is null and
  264. lower( title ) like %s
  265. order by
  266. revision desc limit 20;
  267. """ % ( quote( notebook_id ),
  268. quote_fuzzy( search_text.lower() ) )
  269. def sql_highest_note_rank( self ):
  270. """
  271. Return a SQL string to determine the highest numbered rank of all notes in this notebook."
  272. """
  273. return "select coalesce( max( rank ), -1 ) from note_current where notebook_id = %s;" % quote( self.object_id )
  274. def sql_count_notes( self ):
  275. """
  276. Return a SQL string to count the total number of notes in this notebook.
  277. """
  278. return \
  279. "select count( id ) from note_current where notebook_id = %s;" % \
  280. ( quote( self.object_id ) )
  281. def sql_load_tag_by_name( self, user_id, tag_name ):
  282. """
  283. Return a SQL string to load a tag associated with this notebook by the given user.
  284. """
  285. return \
  286. """
  287. select
  288. tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description, tag_notebook.value
  289. from
  290. tag_notebook, tag
  291. where
  292. tag_notebook.notebook_id = %s and
  293. tag_notebook.user_id = %s and
  294. tag_notebook.tag_id = tag.id and
  295. tag.name = %s
  296. order by tag.name;
  297. """ % ( quote( self.object_id ), quote( user_id ), quote( tag_name ) )
  298. def sql_load_tags( self, user_id ):
  299. """
  300. Return a SQL string to load a list of all the tags associated with this notebook by the given
  301. user.
  302. """
  303. return \
  304. """
  305. select
  306. tag.id, tag.revision, tag.notebook_id, tag.user_id, tag.name, tag.description, tag_notebook.value
  307. from
  308. tag_notebook, tag
  309. where
  310. tag_notebook.notebook_id = %s and
  311. tag_notebook.user_id = %s and
  312. tag_notebook.tag_id = tag.id
  313. order by tag.name;
  314. """ % ( quote( self.object_id ), quote( user_id ) )
  315. def sql_load_note_ids_starting_from_rank( self, start_note_rank ):
  316. """
  317. Return a SQL string to load a list of all the note ids with rank greater than or equal to the
  318. given rank.
  319. """
  320. return \
  321. """
  322. select
  323. id
  324. from
  325. note_current
  326. where
  327. notebook_id = %s and
  328. rank is not null and
  329. rank >= %s;
  330. """ % ( quote( self.object_id ), start_note_rank )
  331. def sql_increment_rank( self, start_note_rank ):
  332. """
  333. Return a SQL string to increment the rank for every note in this notebook (in rank order)
  334. starting from the given note rank. Notes before the given note rank are not updated.
  335. """
  336. return \
  337. """
  338. update
  339. note_current
  340. set
  341. rank = rank + 1,
  342. revision = %s
  343. where
  344. notebook_id = %s and
  345. rank is not null and
  346. rank >= %s;
  347. """ % ( quote( datetime.now( tz = utc ) ), quote( self.object_id ), start_note_rank )
  348. def to_dict( self ):
  349. d = Persistent.to_dict( self )
  350. d.update( dict(
  351. name = self.__name,
  352. friendly_id = self.friendly_id,
  353. trash_id = self.__trash_id,
  354. read_write = self.__read_write,
  355. owner = self.__owner,
  356. deleted = self.__deleted,
  357. user_id = self.__user_id,
  358. note_count = self.__note_count,
  359. tags = self.__tags,
  360. ) )
  361. return d
  362. def __set_name( self, name ):
  363. self.__name = name
  364. self.update_revision()
  365. HTML_REFERENCE_PATTERN = re.compile( "&[a-zA-Z]+;|&#\d+;" )
  366. FRIENDLY_ID_STRIP_PATTERN = re.compile( "[^a-zA-Z0-9\-]+" )
  367. def __friendly_id( self ):
  368. # convert to lowercase, remove HTML character/entity refs, collapse whitespace to dashes, strip
  369. # other punctuation. strip leading/trailing dashes
  370. friendly_id = self.HTML_REFERENCE_PATTERN.sub( u" ", self.__name.lower() )
  371. friendly_id = self.WHITESPACE_PATTERN.sub( u"-", friendly_id )
  372. return self.FRIENDLY_ID_STRIP_PATTERN.sub( u"", friendly_id ).strip( "-" )
  373. def __set_read_write( self, read_write ):
  374. # The read_write member isn't actually saved to the database, so setting it doesn't need to
  375. # call update_revision().
  376. read_write = {
  377. None: Notebook.READ_WRITE,
  378. True: Notebook.READ_WRITE,
  379. False: Notebook.READ_ONLY,
  380. }.get( read_write, read_write )
  381. self.__read_write = read_write
  382. def __set_owner( self, owner ):
  383. # The owner member isn't actually saved to the database, so setting it doesn't need to
  384. # call update_revision().
  385. self.__owner = owner
  386. def __set_deleted( self, deleted ):
  387. self.__deleted = deleted
  388. self.update_revision()
  389. def __set_user_id( self, user_id ):
  390. self.__user_id = user_id
  391. self.update_revision()
  392. def __set_rank( self, rank ):
  393. # The rank member isn't actually saved to the database, so setting it doesn't need to
  394. # call update_revision().
  395. self.__rank = rank
  396. def __set_tags( self, tags ):
  397. # The tags member isn't actually saved to the database, so setting it doesn't need to
  398. # call update_revision().
  399. self.__tags = tags
  400. name = property( lambda self: self.__name, __set_name )
  401. friendly_id = property( __friendly_id )
  402. trash_id = property( lambda self: self.__trash_id )
  403. read_write = property( lambda self: self.__read_write, __set_read_write )
  404. owner = property( lambda self: self.__owner, __set_owner )
  405. deleted = property( lambda self: self.__deleted, __set_deleted )
  406. user_id = property( lambda self: self.__user_id, __set_user_id )
  407. rank = property( lambda self: self.__rank, __set_rank )
  408. note_count = property( lambda self: self.__note_count )
  409. tags = property( lambda self: self.__tags, __set_tags )