Ñò s"Ic @sßdZddkZddkZddklZddklZlZlZddk l Z ddk l Z ddk lZddklZlZd eiifd „ƒYZd eifd „ƒYZd efd„ƒYZdS(s÷ treebeard.mp_tree ----------------- Materialized Path Tree. :copyright: 2008 by Gustavo Picon :license: Apache License 2.0 This is an efficient implementation of Materialized Path trees for Django 1.0+, as described by `Vadim Tropashko`_ in `SQL Design Patterns`_. Materialized Path is probably the fastest way of working with trees in SQL without the need of extra work in the database, like Oracle's ``CONNECT BY`` or sprocs and triggers for nested intervals. In a materialized path approach, every node in the tree will have a :attr:`~MP_Node.path` attribute, where the full path from the root to the node will be stored. This has the advantage of needing very simple and fast queries, at the risk of inconsistency because of the denormalization of ``parent``/``child`` foreign keys. This can be prevented with transactions. ``django-treebeard`` uses a particular approach: every step in the path has a fixed width and has no separators. This makes queries predictable and faster at the cost of using more characters to store a step. To address this problem, every step number is encoded. Also, two extra fields are stored in every node: :attr:`~MP_Node.depth` and :attr:`~MP_Node.numchild`. This makes the read operations faster, at the cost of a little more maintenance on tree updates/inserts/deletes. Don't worry, even with these extra steps, materialized path is more efficient than other approaches. .. note:: The materialized path approach makes heavy use of ``LIKE`` in your database, with clauses like ``WHERE path LIKE '002003%'``. If you think that ``LIKE`` is too slow, you're right, but in this case the :attr:`~MP_Node.path` field is indexed in the database, and all ``LIKE`` clauses that don't **start** with a ``%`` character will use the index. This is what makes the materialized path approach so fast. .. _`Vadim Tropashko`: http://vadimtropashko.wordpress.com/ .. _`Sql Design Patterns`: http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm .. _`Django Model Inheritance with abstract classes`: http://docs.djangoproject.com/en/dev/topics/db/models/#abstract-base-classes iÿÿÿÿN(t serializers(tmodelst transactiont connection(tQ(tsettings(tNode(tInvalidMoveToDescendantt PathOverflowtMP_NodeQuerySetcBseZdZed„ZRS(sg Custom queryset for the tree node manager. Needed only for the customized delete method. c Csï|ott|ƒiƒnÄh}x|iddƒD]{}t}xTtdt|iƒ|iƒD]3}|i |i|ƒ}||jo t }PqcqcW|p|||i= %(depth)s %(extrand)s GROUP BY subpath) AS t2 ON t1.path=t2.subpath ORDER BY t1.pathttablet subpathlenR textrandiiþÿÿÿiÿÿÿÿ(R t_get_children_path_intervalR t_metatdb_tableRRtcursortexecutet descriptiontfetchalltdicttziptdescendants_countRRR#( R<R+R tparamsRctsqlRgRLt_[1]tfieldt field_namest node_dataR&((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_descendants_group_countøs.    (   cCs|iS(sh :returns: the depth (level) of the node See: :meth:`treebeard.Node.get_depth` (R (R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt get_depth9scCsj|iiid|iƒ}|idjo;|i|i|idƒ}|id|i|ƒƒ}n|S(s› :returns: A queryset of all the node's siblings, including the node itself. See: :meth:`treebeard.Node.get_siblings` R it path__range(t __class__RRR RR Rd(R$RKR*((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt get_siblingsBs  cCsM|iƒo|iiiƒS|iiid|idd|i|iƒƒS(ss :returns: A queryset of all the node's children See: :meth:`treebeard.Node.get_children` R iRv(RRwRR]RR RdR (R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt get_childrenRs cCs<y!|iƒid|iƒdSWntj odSXdS(sŸ :returns: The next node's sibling, or None if it was the rightmost sibling. See: :meth:`treebeard.Node.get_next_sibling` tpath__gtiN(RxRR t IndexErrorR;(R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_next_sibling^s!cCs|ii|ƒid|iƒS(s­ :returns: A queryset of all the node's descendants as DFS, doesn't include the node itself See: :meth:`treebeard.Node.get_descendants` RF(RwR^texcludeRD(R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_descendantskscCsBy'|iƒid|iƒiƒdSWntj odSXdS(s¢ :returns: The previous node's sibling, or None if it was the leftmost sibling. See: :meth:`treebeard.Node.get_prev_sibling` tpath__ltiN(RxRR treverseR{R;(R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_prev_siblingus'cCs|iS(sª :returns: The number the node's children, calculated in the most efficient possible way. See: :meth:`treebeard.Node.get_children_count` (R(R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_children_count‚scCsZ|i|ij}|idjo4|i|i|idƒ}|o|ii|ƒS|S(sº :returns: ``True`` if the node if a sibling of another node given as an argument, else, returns ``False`` See: :meth:`treebeard.Node.is_sibling_of` i(R RR t startswith(R$R&tauxR*((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt is_sibling_ofŒs cCs*|ii|iƒo|i|idjS(s¶ :returns: ``True`` is the node if a child of another node given as an argument, else, returns ``False`` See: :meth:`treebeard.Node.is_child_of` i(R RƒR (R$R&((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt is_child_of›scCs&|ii|iƒo|i|ijS(sÀ :returns: ``True`` if the node if a descendant of another node given as an argument, else, returns ``False`` See: :meth:`treebeard.Node.is_descendant_of` (R RƒR (R$R&((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytis_descendant_of¥scKs|iƒ o!|io|iƒid|S|i|}|id|_|iƒp|i|iƒiƒ|_nW|i|i|idƒ|_t |iƒ|ii i dƒi jot dƒ‚n|iƒ||_|id7_|iƒ|S(sž Adds a child to the node. See: :meth:`treebeard.Node.add_child` :raise PathOverflow: when no more child nodes can be added ssorted-siblingiR skThe new node is too deep in the tree, try increasing the path.max_length property and UPDATE your database(RR7tget_last_childR8RwR R9R R:RRet get_fieldR3RRt_cached_parent_objR(R$R=R@((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt add_child¯s      c Ksw|i|ƒ}|i|}|i|_|djoo|i|iƒ|ƒ}y |i|iƒdiƒ}Wntj o d}nX|djo d}q³ndg}}g}|i |||i|||dt ƒ\}}|i ||idƒ} | o|i |i| dƒƒntiƒ} x$|D]\} } | i| | ƒq6W||_|iƒtiƒ|S(sá Adds a new node as a sibling to the current node object. See: :meth:`treebeard.Node.add_sibling` :raise PathOverflow: when the library can't make room for the node's new position ssorted-siblingis last-siblingitincN(t_fix_add_sibling_optsRwR tget_sorted_pos_querysetRxt_get_lastpos_in_pathRHR R{R;t_move_add_sibling_auxRRRt_get_sql_update_numchildRRgRhRRR#( R$tposR=R@tsiblingstnewpoststmtst_R?R*RgRotvals((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR8Ós6        !     cCs#|iiid|id|i!ƒS(st :returns: the root node for the current node object. See: :meth:`treebeard.Node.get_root` R i(RwRtgetR R(R$((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytget_rootscCsfg}tdt|iƒ|iƒdD]}||id|!q'~}|iiid|ƒidƒS(sÍ :returns: A queryset containing the current node object's ancestors, starting by the root node and descending to the parent. See: :meth:`treebeard.Node.get_ancestors` iitpath__inR (RRR RRwRRR(R$RpR’tpaths((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt get_ancestors s@cCs•t|iƒ|i}|djodSy|o |`n|iSWntj onX|i|i|dƒ}|iiid|ƒ|_|iS(s¼ :returns: the parent node of the current node object. Caches the result in the object itself to help in loops. See: :meth:`treebeard.Node.get_parent` iNR ( RR RRŠtAttributeErrorRRwRR˜(R$tupdateR R*((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRs   c CsÍ|i|ƒ}|i}|i|||iƒ\}}}}}|i|ƒotdƒ‚n||ijo^|djpL|d jo|i|iƒijp&|djo|i|iƒijodS|djoo|i|i ƒ|ƒ}y |i |i ƒdiƒ}Wnt j o d}nX|djo d}qIng}|i|||||||tƒ\}}|i|||ƒtiƒ} x$|D]\} } | i| | ƒqŸWtiƒdS( s  Moves the current node and all it's descendants to a new position relative to another node. See: :meth:`treebeard.Node.move` :raise PathOverflow: when the library can't make room for the node's new position s Can't move node to a descendant.tlefttrights last-siblings first-siblingNssorted-siblingi(R s last-sibling(t_fix_move_optsR t_fix_move_to_childR R‡Rtget_last_siblingtget_first_siblingRŽRxRRHR{R;RRt_updates_after_moveRRgRhRR#( R$ttargetR’toldpathtnewdepthR“R”R•R?RgRoR—((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pytmove/s>            cCs|o|d||i!SdS(sM :returns: The base path of another path up to a given depth iR`(R(R<R R ((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRgscCsY|i||dƒ}ti|t|iƒ|iƒ}d|d|it|ƒ|fS(s¾ Builds a path given some values :param path: the base path :param depth: the depth of the node :param newstep: the value (integer) of the new step is%s%s%st0(Rtnumconvtint2strRRQR(R<R R tnewstepR*tkey((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR:qs !cCs¢t|iƒ}ti||i ||iƒd}ti|||iƒ}t|ƒ|ijotd|fƒ‚nd||i d|it|ƒ|fS(sN :returns: The path of the next sibling of a given node path. isPath Overflow from: '%s's%s%s%sRª(RRQR«tstr2intRR¬R(R<R tbaseR”R®((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR9s$"cCs'ti||i t|iƒ|iƒS(sI :returns: The integer value of the last step in a path. (R«R¯RRRQ(R<R ((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRscCs$|o|dt|ƒ|i!SdS(s< :returns: The parent path for a given path iR`(RR(R<R ((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt_get_parent_path_from_path–scCs.||id|i||id|ifS(sR :returns: An interval of all possible children paths for a node. iiÿÿÿÿ(RQR(R<R ((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRd sc Cs|djp |djoY||iƒjoF|iƒ} |i| iƒ} |o|i|i|| ƒƒq n—|d jo‚|iƒ}h|id|iƒd6|id|iƒd6|d6|}|i|iƒ} hdd6| d6| dd6|}n|i |i||ƒ} xÈ|i ƒD]º} |i| i|i| iƒƒ\} }|i| |fƒ|os|i | iƒo |d|t |dƒ}n|ii | iƒo&|d|it |dƒ|_qáq'q'W|o|i|i|| ƒƒn|| fS( s¥ Handles the reordering of nodes and branches when adding/moving nodes. :returns: A tuple containing the old path and the new path. s last-siblingR t path__gteRŸRzs first-siblingiiN( R£R9R Rt_get_sql_newpath_in_branchesR;RxRRR:R€RƒR(R<R’R”R¨R¦R“R•R§t movebranchtlastR?tbasenumR&RoR—((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR©s<   !      .cCs¾|i}d}d}g}|d joƒ|}|d7}|iƒo"d}d}|iiiƒ}n,|iƒ}hdd6dd6dd6|}|id7_d}n|||||fS( sO Update preliminar vars in :meth:`move` when moving to a child s first-childs last-childs sorted-childis first-siblings last-siblingssorted-siblingN(s first-childs last-childs sorted-child(R R;RRwRR]RˆR(R$R’R¦R¨R+R”R“((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR¢às$       cCsÜtidjo3t|ƒt|ƒjo|i|i|ƒƒn|i|ƒ}|i|ƒ}| o|p|o| p ||joL|o|i|i|dƒƒn|o|i|i|dƒƒqØndS(sÝ Updates the list of sql statements needed after moving nodes. 1. :attr:`depth` updates *ONLY* needed by mysql databases (*sigh*) 2. update the number of children of parent nodes tmysqltdecRŒN(RtDATABASE_ENGINERRt_get_sql_update_depth_in_branchR±R‘(R<R§R?R•t oldparentpatht newparentpath((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR¥ÿs ) c Csd|iif}tidjo d}n!tidjo d}nd}d|fg}|t|ƒdg}t|ƒt|ƒjoKtidjo;|id |fƒ|i|t|ƒd|igƒnd }|i|d gƒd |d i|ƒ|f}||fS(s¯ :returns" The sql needed to move a branch to another position. .. note:: The generated sql will only update the depth values if needed. s UPDATE %s SETtsqlite3s"%s||SUBSTR(path, %s, LENGTH(path))R·sCONCAT(%s, SUBSTR(path, %s))s%s||SUBSTR(path, %s)spath=%sisdepth=LENGTH(%s)/%%ssWHERE path LIKE %st%s%s %s %ss, ( ReRfRR¹RRtextendRtjoin( R<R§R?tsql1tsqlpathtsql2R—tsql3Ro((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR³s   )'cCs0d|iif}|i|dg}||fS(sn :returns: The sql needed to update the depth of all the nodes in a branch. s8UPDATE %s SET depth=LENGTH(path)/%%s WHERE path LIKE %%sR¾(ReRfR(R<R RoR—((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRºHsRŒcCs;d|iihdd6dd6|f}|g}||fS(sG :returns: The sql needed the numchild value of a node s1UPDATE %s SET numchild=numchild%s1 WHERE path=%%st+RŒt-R¸(ReRf(R<R tincdecRoR—((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR‘Vs   tMetacBseZdZeZdgZRS(s! Abstract model. R (R,R-R.Rtabstracttordering(((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyRÈbsN((6R,R-R.RRQR7Rt CharFieldRR tPositiveIntegerFieldR RR/Rt classmethodRAR;RPRXR[R^R_RtRuRxRyR|R~RR‚R…R†R‡R‹R8R™RœRRR©RR:R9RR±RdRR¢R¥R³RºR‘RÈ(((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyR1ŽsdŽ   "-0, @   $ 0  8    5 - (R.R!R«t django.coreRt django.dbRRRtdjango.db.modelsRt django.confRttreebeard.modelsRttreebeard.exceptionsRRtquerytQuerySetR tManagerR/R1(((sS/afs/sipb.mit.edu/project/remit/demo-code/django-treebeard-1.1/treebeard/mp_tree.pyt3s  B