sqla-hierarchy 0.1
Adjacency List Relationships helper (only using databases own implementations)
License: MIT License
Keywords: sqlachemy,hierarchy,adjacency list,python

--------------
        SQLA_Hierarchy
        --------------
        
        -----------
        Description
        -----------
        
        Given a `Table`_ object and a `Select`_ expression, this class will return the information from these objects with some extra columns that will properly denote the hierarchical relation between the rows. The returned Hierarchy object could then be executed and it will return the same Select statement submitted plus the following columns:
        
        - level: the relative level of the row related to its parent
        - connect_path: a list with all the ids that compound this part of the hierarchy, from the root node to the current value (**IMPORTANT**: Oracle requires some extra post processing to generate the list) 
        - is_leaf: boolean indicating is the particular id is a leaf or not
        
        The resultset will be returned properly ordered by the levels in the hierarchy
        
        Special remarks:
        
        - The selected table must have a self referential foreign key relation, otherwise it will raise MissingForeignKey
        - Not every database is supported (at the moment). Check the global var supported_db for an up2date list. Trying to execute Hierarchy with an unsupported db will raise NotImplementedError or HierarchyLesserError (check the errors classes docstring for the exact meaning of each of them).
        - To prevent the query from returning every node as a different starting node and, therefore, having duplicate values, you can provide the 'starting_node' parameter in the kwargs. The value you must provide is the parent id for the root node you want to start building the hierarchical tree. None has the same meaning as "0" since we perform a coalesce function in the query. By default the system will add a 'starting_node'="0". If you don't want a starting node, pass 'starting_node'=False and the clause will not be added to the query
        
        *Supported databases*:
        
        - PostgreSQL (>=8.4.0)
        - Oracle (>=10g)
        
        *Databases we might support on a next version*:
        
        - SQL Server
        - DB2
        
        *Databases that we know we cannot support* (because they do not implement recursive):
        
        - MySQL
        - SQLite
        
        -------------
        Some examples
        -------------
        
        First of all, let's set up some imports and variables we will be using ::
        
            >>> import ConfigParser
            >>> from sqlalchemy import Table, Column, ForeignKey, MetaData, create_engine
            >>> from sqlalchemy import Unicode, select, and_
            >>> from sqlalchemy.orm import mapper, relationship, scoped_session, sessionmaker
            >>> from sqla_hierarchy import *
            >>> DBSession = scoped_session(sessionmaker())
            >>> metadata = MetaData()
            >>> config = ConfigParser.ConfigParser() 
            >>> config.read('setup.cfg')
            ['setup.cfg']
            >>> engine = create_engine('postgresql://%s' % config.get('dburi', 'pg-db'))
            >>> DBSession.configure(bind=engine)
            >>> metadata.bind = engine
        
        Let's build some simple table/class to hold boss/employee relation ::
        
            >>> example_tb = Table('employee', metadata,  
            ...                    Column('id', Unicode, primary_key=True), 
            ...                    Column('boss', Unicode, ForeignKey('employee.id')))
            >>> class Employee(object): 
            ...     def __init__(self, employee, boss=None): 
            ...         self.id = employee
            ...         self.boss = boss
            ...     def __repr__(self): 
            ...         return "<Employee %s, Boss %s>" % (self.id, self.boss) 
            ...  
            >>> mapper(Employee, example_tb, properties={  #doctest: +ELLIPSIS
            ...        'parent': relationship(Employee, remote_side=[example_tb.c.id])}) 
            <Mapper at 0x...; Employee>
            >>> example_tb.drop(checkfirst=True)
            >>> example_tb.create(checkfirst=True)
        
        Add some data ::
        
            >>> pl = [Employee(u'King Cold', None), Employee(u'Frieza', u'King Cold'), 
            ...       Employee(u'Zarbon', u'Frieza'), Employee(u'Dodoria', u'Frieza'), 
            ...       Employee(u'Captain Ginyu', u'Frieza'), 
            ...       Employee(u'Jeice', u'Captain Ginyu'),
            ...       Employee(u'Burter', u'Captain Ginyu'),
            ...       Employee(u'Recoome', u'Captain Ginyu'),
            ...       Employee(u'Guldo', u'Captain Ginyu'),
            ...       Employee(u'Dr Gero', None), Employee(u'A-16', u'Dr Gero'), 
            ...       Employee(u'A-17', u'Dr Gero'), Employee(u'A-18', u'Dr Gero'), 
            ...       Employee(u'Cell', u'Dr Gero'), Employee(u'Cell Junior', u'Cell')] 
            >>> DBSession.add_all(pl)
            >>> DBSession.commit()
        
        Now let's query some basic relations. First we want a list of bosses and employees using some indentation to visually understand who depends on who ::
        
            >>> qry = Hierarchy(DBSession, example_tb, select([example_tb])) 
            >>> rs = DBSession.execute(qry).fetchall()
            >>> for ev in rs:
            ...     if ev.level == 1:
            ...         print(ev.id)
            ...     else:
            ...         print(" "*2*ev.level+ev.id) 
            Dr Gero
                A-16
                A-17
                A-18
                Cell
                  Cell Junior
            King Cold
                Frieza
                  Captain Ginyu
                    Burter
                    Guldo
                    Jeice
                    Recoome
                  Dodoria
                  Zarbon
        
        Let's take a look at the special attributes sqla_hierachy added ::
        
            >>> print(rs[0])
            (u'Dr Gero', None, 1, ['Dr Gero'], False)
        
        *Level*: The relative position of this record in the tree. '1' means he/she is the root. The higher the number, the bigger the distance is between the root and the current record ::
        
            >>> print(rs[0].level)
            1
        
        *Connect Path*: Print the full list of members of this relation: from the root up to the current record::
        
            >> print(rs[0].connect_path)
            ['Dr Gero']
        
        *Is Leaf*: If is_leaf is True, this record has no more dependents. If False, there are more records that depend on him/her::
        
            >>> print(rs[0].is_leaf)
            False
            
        Now an example with a record that is a leaf ::
        
            >>> print(rs[9].level, rs[9].is_leaf)
            (4, True)
            >>> print(rs[9].connect_path)
            ['King Cold', 'Frieza', 'Captain Ginyu', 'Burter']
        
        .. _Table: http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Table
        .. _Select: http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.Select _