[SQL-CVS] r3247 - SQLObject/branches/0.10/docs

[<sub...@co...>]

Author: phd
Date: 2008-02-11 08:17:48 -0700 (Mon, 11 Feb 2008)
New Revision: 3247

Added:
   SQLObject/branches/0.10/docs/SelectResults.txt
   SQLObject/branches/0.10/docs/Views.txt
Modified:
   SQLObject/branches/0.10/docs/SQLObject.txt
   SQLObject/branches/0.10/docs/index.txt
   SQLObject/branches/0.10/docs/rebuild
Log:
Merged documentation updates by Luke Opperman from the trunk.


Modified: SQLObject/branches/0.10/docs/SQLObject.txt
===================================================================
--- SQLObject/branches/0.10/docs/SQLObject.txt	2008-02-10 01:07:41 UTC (rev 3246)
+++ SQLObject/branches/0.10/docs/SQLObject.txt	2008-02-11 15:17:48 UTC (rev 3247)
@@ -134,8 +134,8 @@
     >>> from sqlobject import *
     >>> import sys, os
 
-Declaring the Class
--------------------
+Declaring a Connection
+----------------------
 
 The connection URI must follow the standard URI syntax::
 
@@ -180,6 +180,9 @@
 The ``sqlhub.processConnection`` assignment means that all classes
 will, by default, use this connection we've just set up.
 
+Declaring the Class
+-------------------
+
 We'll develop a simple addressbook-like database.  We could create the
 tables ourselves, and just have SQLObject access those tables, but
 let's have SQLObject do that work.  First, the class:
@@ -225,9 +228,11 @@
 treat that key as immutable (otherwise you'll confuse SQLObject
 terribly).
 
-You can `override the id name <#idName>`_ in the database, but it is
+You can `override the id name`_ in the database, but it is
 always called ``.id`` from Python.
 
+.. _`override the id name`: `Class sqlmeta`_
+
 Using the Class
 ---------------
 
@@ -337,9 +342,13 @@
 
 By default SQLObject sends an ``UPDATE`` to the database for every
 attribute you set, or every time you call ``.set()``.  If you want to
-avoid this many updates, add ``_lazyUpdate = True`` to your class
-definition.  Then updates will only be written to the database when
-you call ``inst.syncUpdate()`` or ``obj.sync()``: ``.sync()`` also
+avoid this many updates, add ``lazyUpdate = True`` to your class `sqlmeta
+definition`_.
+
+.. _`sqlmeta definition`: `Class sqlmeta`_
+
+Then updates will only be written to the database when
+you call ``inst.syncUpdate()`` or ``inst.sync()``: ``.sync()`` also
 refetches the data from the database, which ``.syncUpdate()`` does not
 do.
 
@@ -417,9 +426,13 @@
 
 .. note::
   MultipleJoin, as well as RelatedJoin, returns a list of results.
-  Would you prefer to get a SelectResults objects, you should use
-  SQLMultipleJoin and SQLRelated Join. Usage stays immutated.
+  It is often preferable to get a `SelectResults`_ object instead, 
+  in which case you should use
+  SQLMultipleJoin and SQLRelatedJoin. The declaration of these joins is
+  unchanged from above, but the returned iterator has many additional useful methods.
 
+.. _`SelectResults` : SelectResults.html
+
 Many-to-Many Relationships
 --------------------------
 
@@ -449,11 +462,13 @@
     >>> User.createTable()
     >>> Role.createTable()
 
-Note the use of the ``sqlmeta`` class.  This class is used to store
-different kinds of metadata (and override that metadata, like
-``table``).  This is new in SQLObject 0.7.  See the section `Class sqlmeta`_
-for more information on how it works and what attributes have special meanings.
+.. note::
 
+  The sqlmeta class is used to store
+  different kinds of metadata (and override that metadata, like table).
+  This is new in SQLObject 0.7. See the section `Class sqlmeta`_ for more
+  information on how it works and what attributes have special meanings.
+
 And usage::
 
     >>> bob = User(username='bob')
@@ -523,15 +538,15 @@
 Selecting Multiple Objects
 --------------------------
 
-While the full power of all the kinds of joins you can do with a
-relational database are not revealed in SQLObject, a simple ``SELECT``
-is available.
+SQLObject allows nearly arbitrary queries, with one overriding caveat:
+the resulting objects must be instances of a SQLObject class.
+``select`` is a class method which usually takes one argument, the equivalent
+to the SQL ``WHERE`` clause. A simple call (with the SQL that's generated)::
 
-``select`` is a class method, and you call it like (with the SQL
-that's generated)::
-
-    >>> Person._connection.debug = True
+    >>> Person._connection.debug = True # to print SQL as it's sent to the database
     >>> peeps = Person.select(Person.q.firstName=="John")
+    >>> # Notice that we haven't queried the database yet,
+    >>> # ``peeps`` is an iterable SelectResults instance.
     >>> list(peeps)
      1/Select  :  SELECT person.id, person.first_name, person.middle_initial, person.last_name FROM person WHERE (person.first_name = 'John')
      1/COMMIT  :  auto
@@ -568,7 +583,7 @@
 
 .. _orderBy:
 
-You can use the keyword arguments `orderBy` to create ``ORDER BY`` in the
+You can use the keyword argument `orderBy` to create ``ORDER BY`` in the
 select statements: `orderBy` takes a string, which should be the *database*
 name of the column, or a column in the form ``Person.q.firstName``.  You
 can use ``"-colname"`` or ``DESC(Person.q.firstName``) to specify
@@ -576,70 +591,20 @@
 types as well), or call ``MyClass.select().reversed()``. orderBy can also
 take a list of columns in the same format: ``["-weight", "name"]``.
 
-You can use the special class variable `_defaultOrder` to give a
+You can use the special `class sqlmeta variable`_ `defaultOrder` to give a
 default ordering for all selects.  To get an unordered result when
-`_defaultOrder` is used, use ``orderBy=None``.
+`defaultOrder` is used, use ``orderBy=None``.
 
-Select results are generators, which are lazily evaluated.  So the SQL
-is only executed when you iterate over the select results, or if you
-use ``list()`` to force the result to be executed.  When you iterate
-over the select results, rows are fetched one at a time.  This way you
-can iterate over large results without keeping the entire result set
-in memory.  You can also do things like ``.reversed()`` without
-fetching and reversing the entire result -- instead, SQLObject can
-change the SQL that is sent so you get equivalent results.
+.. _`class sqlmeta variable`: `Class sqlmeta`_
 
-You can also slice select results.  This modifies the SQL query, so
-``peeps[:10]`` will result in ``LIMIT 10`` being added to the end of
-the SQL query.  If the slice cannot be performed in the SQL (e.g.,
-peeps[:-10]), then the select is executed, and the slice is performed
-on the list of results.  This will generally only happen when you use
-negative indexes.
+Select results are generators that allow lazy execution of the underlying
+database query, and further modification of the query before execution.
+Methods are provided for reversing, slicing, ``SELECT DISTINCT``, and ``count``
+and other aggregates. For more information see the `SelectResults`_ documentation.
 
-In certain cases, you may get a select result with an object in it
-more than once, e.g., in some joins.  If you don't want this, you can
-add the keyword argument ``MyClass.select(..., distinct=True)``, which
-results in a ``SELECT DISTINCT`` call.
+For more information on the where clause in the queries see the
+`SQLBuilder`_ documentation.
 
-You can get the length of the result without fetching all the results
-by calling ``count`` on the result object, like
-``MyClass.select().count()``.  A ``COUNT(*)`` query is used -- the
-actual objects are not fetched from the database.  Together with
-slicing, this makes batched queries easy to write:
-
-    start = 20
-    size = 10
-    query = Table.select()
-    results = query[start:start+size]
-    total = query.count()
-    print "Showing page %i of %i" % (start/size + 1, total/size + 1)
-
-.. note::
-
-   There are several factors when considering the efficiency of this
-   kind of batching, and it depends very much how the batching is
-   being used.  Consider a web application where you are showing an
-   average of 100 results, 10 at a time, and the results are ordered
-   by the date they were added to the database.  While slicing will
-   keep the database from returning all the results (and so save some
-   communication time), the database will still have to scan through
-   the entire result set to sort the items (so it knows which the
-   first ten are), and depending on your query may need to scan
-   through the entire table (depending on your use of indexes).
-   Indexes are probably the most important way to improve importance
-   in a case like this, and you may find caching to be more effective
-   than slicing.
-
-   In this case, caching would mean retrieving the *complete* results.
-   You can use ``list(MyClass.select(...))`` to do this.  You can save
-   these results for some limited period of time, as the user looks
-   through the results page by page.  This means the first page in a
-   search result will be slightly more expensive, but all later pages
-   will be very cheap.
-
-For more information on the where clause in the queries, see the
-`SQLBuilder documentation`_.
-
 Select-By Method
 ~~~~~~~~~~~~~~~~
 
@@ -719,6 +684,9 @@
    database will be queried for the table's columns, and any missing
    columns (possible all columns) will be added automatically.
 
+The following attributes provide introspection but should not be set or
+directly - see `Runtime Column and Join Changes`_ for dynamically modifying these class elements.
+
 `columns`:
    A dictionary of ``{columnName: anSOColInstance}``.  You can get
    information on the columns via this read-only attribute.
@@ -1180,7 +1148,6 @@
         MyTable.select((MyTable.q.name + MyTable.q.surname) == u'value'.encode(dbEncoding))
 
 
-.. Relationships_:
 
 Relationships Between Classes/Tables
 ------------------------------------
@@ -1366,11 +1333,11 @@
 
 One can call as much .commit()'s, but after a .rollback() one has to call
 .begin(). The last .commit() should be called as .commit(close=True) to
-release low-level connection.
+release low-level connection back to the connection pool.
 
 You can use SELECT FOR UPDATE in those databases that support it::
 
-    Person.select(Person.q.name=="value", forUpdate=True)
+    Person.select(Person.q.name=="value", forUpdate=True, connection=trans)
 
 
 Automatic Schema Generation
@@ -1454,32 +1421,35 @@
 
 *This is not supported in SQLite*
 
-Runtime Column Changes
-----------------------
+Runtime Column and Join Changes
+-------------------------------
 
 *SQLite does not support this feature*
 
 You can add and remove columns to your class at runtime.  Such changes
 will effect all instances, since changes are made inplace to the
-class.  There are two methods, `addColumn` and `delColumn`, both of
+class.  There are two methods of the `class sqlmeta object`_,
+`addColumn` and `delColumn`, both of
 which take a `Col` object (or subclass) as an argument.  There's also
 an option argument `changeSchema` which, if True, will add or drop the
 column from the database (typically with an ``ALTER`` command).
 
 When adding columns, you must pass the name as part of the column
 constructor, like ``StringCol("username", length=20)``.  When removing
-columns, you can either use the Col object (as found in `_columns`, or
+columns, you can either use the Col object (as found in `sqlmeta.columns`, or
 which you used in `addColumn`), or you can use the column name (like
 ``MyClass.delColumn("username")``).
 
+.. _`class sqlmeta object`: `Class sqlmeta`_
+
 .. _addJoin:
 
-You can also add Joins__, like
+You can also add Joins_, like
 ``MyClass.addJoin(MultipleJoin("MyOtherClass"))``, and remove joins with
 `delJoin`.  `delJoin` does not take strings, you have to get the join
-object out of the `_joins` attribute.
+object out of the `sqlmeta.joins` attribute.
 
-__ Relationships_:
+.. _Joins : `Relationships between Classes/Tables`_
 
 Legacy Database Schemas
 =======================
@@ -1581,7 +1551,8 @@
 convention.  For instance::
 
     class Person(SQLObject):
-        _style = MixedCaseStyle(longID=True)
+        class sqlmeta:
+            style = MixedCaseStyle(longID=True)
         firstName = StringCol()
         lastName = StringCol()
 
@@ -1607,23 +1578,9 @@
 Irregular Naming
 ----------------
 
-While naming conventions are nice, they are not always present.  You
-can control most of the names that SQLObject uses, independent of the
-Python names (so at least you don't have to propagate the
-irregularity to your brand-spanking new Python code).
+This is now covered in the `Class sqlmeta`_ section.
 
-Here's a simple example::
 
-    class User(SQLObject):
-        _table = "user_table"
-        _idName = "userid"
-
-        username = StringCol(length=20, dbName='name')
-
-The attribute `_table` overrides the table name.  `_idName` provides
-an alternative to ``id``.  The ``dbName`` keyword argument gives the
-column name.
-
 Non-Integer Keys
 ----------------
 
@@ -1727,7 +1684,7 @@
 column -- strings can go in integer columns, dates in integers, etc.
 
 SQLiteConnection doesn't support `automatic class generation`_ and
-SQLite does not support `runtime column changes`_.
+SQLite does not support `runtime column and join changes`_.
 
 SQLite may have concurrency issues, depending on your usage in a
 multi-threaded environment.

Copied: SQLObject/branches/0.10/docs/SelectResults.txt (from rev 3246, SQLObject/trunk/docs/SelectResults.txt)
===================================================================
--- SQLObject/branches/0.10/docs/SelectResults.txt	                        (rev 0)
+++ SQLObject/branches/0.10/docs/SelectResults.txt	2008-02-11 15:17:48 UTC (rev 3247)
@@ -0,0 +1,194 @@
+SelectResults: Using Queries
+============================
+
+.. contents:: Contents:
+
+Overview
+--------
+
+SelectResults are returned from ``.select`` and ``.selectBy`` methods on SQLObject classes, and from ``SQLMultipleJoin``, and ``SQLRelatedJoin`` accessors on SQLObject instances.
+
+Select results are generators, which are lazily evaluated.  The SQL
+is only executed when you iterate over the select results, fetching
+rows one at a time.  This way you
+can iterate over large results without keeping the entire result set
+in memory. You can also do things like ``.reversed()`` without
+fetching and reversing the entire result -- instead, SQLObject can
+change the SQL that is sent so you get equivalent results.
+
+.. note::
+   To retrieve the results all at once use the python idiom
+   of calling ``list()`` on the generator to force execution
+   and convert the results to a stored list.
+
+You can also slice select results.  This modifies the SQL query, so
+``peeps[:10]`` will result in ``LIMIT 10`` being added to the end of
+the SQL query.  If the slice cannot be performed in the SQL (e.g.,
+peeps[:-10]), then the select is executed, and the slice is performed
+on the list of results.  This will generally only happen when you use
+negative indexes.
+
+In certain cases, you may get a select result with an object in it
+more than once, e.g., in some joins.  If you don't want this, you can
+add the keyword argument ``MyClass.select(..., distinct=True)``, which
+results in a ``SELECT DISTINCT`` call.
+
+You can get the length of the result without fetching all the results
+by calling ``count`` on the result object, like
+``MyClass.select().count()``.  A ``COUNT(*)`` query is used -- the
+actual objects are not fetched from the database.  Together with
+slicing, this makes batched queries easy to write::
+
+    start = 20
+    size = 10
+    query = Table.select()
+    results = query[start:start+size]
+    total = query.count()
+    print "Showing page %i of %i" % (start/size + 1, total/size + 1)
+
+.. note::
+
+   There are several factors when considering the efficiency of this
+   kind of batching, and it depends very much how the batching is
+   being used.  Consider a web application where you are showing an
+   average of 100 results, 10 at a time, and the results are ordered
+   by the date they were added to the database.  While slicing will
+   keep the database from returning all the results (and so save some
+   communication time), the database will still have to scan through
+   the entire result set to sort the items (so it knows which the
+   first ten are), and depending on your query may need to scan
+   through the entire table (depending on your use of indexes).
+   Indexes are probably the most important way to improve importance
+   in a case like this, and you may find caching to be more effective
+   than slicing.
+
+   In this case, caching would mean retrieving the *complete* results.
+   You can use ``list(MyClass.select(...))`` to do this.  You can save
+   these results for some limited period of time, as the user looks
+   through the results page by page.  This means the first page in a
+   search result will be slightly more expensive, but all later pages
+   will be very cheap.
+
+Retrieval Methods
+-----------------
+
+Iteration
+~~~~~~~~~
+
+As mentioned in the overview, the typical way to access the results
+is by treating it as a generator and iterating over it (in a loop,
+by converting to a list, etc).
+
+``getOne(default=optional)``
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+In cases where your restrictions cause there to always be a single record
+in the result set, this method will return it or raise an exception:
+SQLObjectIntegrityError if more than one result is found, or 
+SQLObjectNotFound if there are actually no results, unless you pass in
+a default like ``.getOne(None)``.
+
+Cloning Methods
+---------------
+
+These methods return a modified copy of the SelectResult instance 
+they are called on, so successive calls can chained, eg 
+``results = MyClass.selectBy(city='Boston').filter(MyClass.q.commute_distance>10).orderBy('vehicle_mileage')``
+or used independently later on.
+
+
+``orderBy(column)``
+~~~~~~~~~~~~~~~~~~~
+
+Takes a string column name (optionally prefixed with '-' for DESCending)
+or a `SQLBuilder expression`_.
+
+``limit(num)``
+~~~~~~~~~~~~~~
+
+Only return first num many results. Equivalent to results[:num] slicing.
+
+``lazyColumns(v)``
+~~~~~~~~~~~~~~~~~~
+
+Only fetch the IDs for the results, the rest of the columns will be
+retrieved when attributes of the returned instances are accessed.
+
+``reversed()``
+~~~~~~~~~~~~~~
+
+Reverse-order. Alternative to calling orderBy with SQLBuilder.DESC or '-'.
+
+
+``distinct()``
+~~~~~~~~~~~~~~
+
+In SQL, SELECT DISTINCT, removing duplicate rows.
+
+``filter(expression)``
+~~~~~~~~~~~~~~~~~~~~~~
+
+Add additional expressions to restrict result set.
+Takes either a string static SQL expression valid in a WHERE clause,
+or a `SQLBuilder expression`_. ANDed with any previous expressions. 
+
+.. _`SQLBuilder expression`: SQLBuilder.html
+
+
+Aggregate Methods
+-----------------
+
+These return column values (strings, numbers, etc) 
+not new SQLResults instances, by making the appropriate
+SQL query (the actual result rows are not retrieved).
+Any that take a column can also take a SQLBuilder 
+column instance, e.g. ``MyClass.q.size``.
+
+
+``count()``
+~~~~~~~~~~~
+
+Returns the length of the result set, by a SQL ``SELECT COUNT(...)``
+query.
+
+``sum(column)``
+~~~~~~~~~~~~~~~
+
+The sum of values for ``column`` in the result set.
+
+``min(column)``
+~~~~~~~~~~~~~~~
+
+The minimum value for ``column`` in the result set.
+
+``max(column)``
+~~~~~~~~~~~~~~~
+
+The maximum value for ``column`` in the result set.
+
+``avg(column)``
+~~~~~~~~~~~~~~~
+
+The average value for the ``column`` in the result set.
+
+Traversal to related SQLObject classes
+--------------------------------------
+
+``throughTo.join_name and throughTo.foreign_key_name``
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+This accessor lets you retrieve the objects related to your 
+SelectResults by either a join or foreign key relationship,
+in the same manner as the cloning methods above. For instance::
+
+  Schools.select(Schools.q.student_satisfaction>90).throughTo.teachers
+
+returns a SelectResult of Teachers of Schools with satisfied students,
+assuming Schools has a SQLMultipleJoin or SQLRelatedJoin attribute
+named ``teachers``. Similarily, with a self-joining foreign key named
+``father``::
+
+  Person.select(Person.q.name=='Steve').throughTo.father.throughTo.father
+
+returns a SelectResult of Persons who are the paternal grandfather of someone
+named 'Steve'.
\ No newline at end of file

Copied: SQLObject/branches/0.10/docs/Views.txt (from rev 3246, SQLObject/trunk/docs/Views.txt)
===================================================================
--- SQLObject/branches/0.10/docs/Views.txt	                        (rev 0)
+++ SQLObject/branches/0.10/docs/Views.txt	2008-02-11 15:17:48 UTC (rev 3247)
@@ -0,0 +1,64 @@
+Views and SQLObjects
+====================
+
+In general, if your database backend supports defining views
+you may define them outside of SQLObject and treat them
+as a regular table when defining your SQLObject class.
+
+
+ViewSQLObject
+-------------
+
+The rest of this document is experimental.
+
+``from sqlobject.views import *``
+
+``ViewSQLObject`` is an attempt to allow defining
+views that allow you to define a SQL query that acts
+like a SQLObject class. You define columns based on
+other SQLObject classes .q SQLBuilder columns, have columns
+that are aggregates of other columns, and join
+multiple SQLObject classes into one and add restrictions
+using SQLBuilder expressions.
+
+The resulting classes are currently read only, if you find
+use for this idea please bring discussion to the mailing list.
+
+A short example from the tests will suffice for now.
+
+Base classes::
+
+  class PhoneNumber(SQLObject):
+    number = StringCol()
+    calls = SQLMultipleJoin('PhoneCall')
+    incoming = SQLMultipleJoin('PhoneCall', joinColumn='toID')
+    
+  class PhoneCall(SQLObject):
+    phoneNumber = ForeignKey('PhoneNumber')
+    to = ForeignKey('PhoneNumber')
+    minutes = IntCol()
+
+View classes::
+
+  class ViewPhoneCall(ViewSQLObject):
+    class sqlmeta:
+        idName = PhoneCall.q.id
+        clause = PhoneCall.q.phoneNumberID==PhoneNumber.q.id
+    
+    minutes = IntCol(dbName=PhoneCall.q.minutes)
+    number = StringCol(dbName=PhoneNumber.q.number)
+    phoneNumber = ForeignKey('PhoneNumber', dbName=PhoneNumber.q.id)
+    call = ForeignKey('PhoneCall', dbName=PhoneCall.q.id)
+
+  class ViewPhone(ViewSQLObject):
+    class sqlmeta:
+        idName = PhoneNumber.q.id
+        clause = PhoneCall.q.phoneNumberID==PhoneNumber.q.id
+    
+    minutes = IntCol(dbName=func.SUM(PhoneCall.q.minutes))
+    numberOfCalls = IntCol(dbName=func.COUNT(PhoneCall.q.phoneNumberID))
+    number = StringCol(dbName=PhoneNumber.q.number)
+    phoneNumber = ForeignKey('PhoneNumber', dbName=PhoneNumber.q.id)
+    calls = SQLMultipleJoin('PhoneCall', joinColumn='phoneNumberID')
+    vCalls = SQLMultipleJoin('ViewPhoneCall', joinColumn='phoneNumberID')
+

Modified: SQLObject/branches/0.10/docs/index.txt
===================================================================
--- SQLObject/branches/0.10/docs/index.txt	2008-02-10 01:07:41 UTC (rev 3246)
+++ SQLObject/branches/0.10/docs/index.txt	2008-02-11 15:17:48 UTC (rev 3247)
@@ -22,10 +22,12 @@
 * `Main SQLObject documentation <SQLObject.html>`_
 * `Frequently Asked Questions <FAQ.html>`_
 * `sqlbuilder documentation <SQLBuilder.html>`_
+* `select() and SelectResults <SelectResults.html>`_
 * `A brief description of SQLObject architecture <sqlobject-architecture.html>`_
 * `sqlobject-admin documentation <sqlobject-admin.html>`_
 * `Inheritance <Inheritance.html>`_
 * `Versioning <Versioning.html>`_
+* `Views <Views.html>`_
 * `Developer Guide <DeveloperGuide.html>`_
 * `Contributors <Authors.html>`_
 

Modified: SQLObject/branches/0.10/docs/rebuild
===================================================================
--- SQLObject/branches/0.10/docs/rebuild	2008-02-10 01:07:41 UTC (rev 3246)
+++ SQLObject/branches/0.10/docs/rebuild	2008-02-11 15:17:48 UTC (rev 3247)
@@ -6,7 +6,7 @@
 export PYTHONPATH=$parent:$PYTHONPATH
 
 NORMAL="Authors DeveloperGuide FAQ Inheritance News News1
-        SQLBuilder SQLObject TODO Versioning
+        SQLBuilder SQLObject SelectResults TODO Versioning Views
         web/index web/links web/repository web/community
         index community sqlobject-architecture sqlobject-admin"