{{extend 'layout.html'}}
{{import os}}

<h1>web2py<sup style="font-size:0.5em;">TM</sup> Database Abstraction Layer (DAL)</h1>

<h2>Quick Reference</h2>
<h3>define_table, insert, count, delete, update</h3>
{{=CODE("""
db = SQLDB(‘postgres://user:password@hostname/db’, pools=10)
db.define_table(‘person’,db.Field(’name’,’string’))
id= db.person.insert(name=’max’)
query=(db.person.id==id)
db(query).count()
db(query).delete()
db(query).update(name=’Max’)
rows = db(query).select(orderby=db.person.name)
for row in rows: print row.name
""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Examples of uri strings for SQLDB</h3>
<ul>
<li>sqlite://test.db</li>
<li>mysql://user:password@localhost/database</li>
<li>postgres://user:password@localhost/database</li>
<li>mssql://user:password@host/database</li>
<li>firebird://user:password@server:3050/database</li>
<li>oracle://user:password@database</li>
</ul>

<h3>Valid field Types</h3>
<ul>
<li>db.Field(name, 'string')</li>
<li>db.Field(name, 'text')</li>
<li>db.Field(name, 'password')</li>
<li>db.Field(name, 'blob')</li>
<li>db.Field(name, 'upload')</li>
<li>db.Field(name, 'boolean')</li>
<li>db.Field(name, 'integer')</li>
<li>db.Field(name, 'double')</li>
<li>db.Field(name, 'time')</li>
<li>db.Field(name, 'date')</li>
<li>db.Field(name, 'datetime')</li>
<li>db.Field(name, db.referenced_table) # reference field</li>
</ul>

<h3>Valid Field Attribute</h3>
<ul>
<li>length (only for string type, defaults to 32)</li>
<li>default (defaults to None)</li>
<li>required (defaults to False)</li>
<li>notnull (defaults to False)</li>
<li>requires (<a href="{{=URL(r=request,f='api')}}">validator or list of validators</a>,
for forms)</li>
<li>comment (for forms)</li>
<li>widget (for forms)</li>
<li>represent (for forms)</li>
<li>readable (for forms)</li>
<li>writable (for forms)</li>
<li>update (default value if the record is updated)</li>
<li>uploadfield (for upload fields)</li>
<li>authorize (for upload fields, function to be used if data can be downloaded, see authentication)</li>
<li>autodelete (for upload fields, if set to true linked uploaded images are removed upon deletion of the record)</li><li>label (for forms)</li>
</ul>

<h3>On Migrations</h3>
Changing the list of fields or field types in a model, triggers an automatic migration, i.e. web2py generates SQL to alter the table accordingly. If the table does not exist it is created. Migration actions are logged in the file sql.log accessibled via the admin/design interface.
Migration can be turned off on a per-table basis by passing migrate=False to define_table.

<h3>Select Attributes</h3>

{{=CODE("rows = db(query).select(*fields, orderby=..., left=..., groupby=..., having=..., limitby=..., cache=...)")}}

<h3>Shortcuts</h3>
{{=CODE("""
db['person']                   ### db.person
db.person['name']              ### db.person.name
db['person']['name']           ### db.person.name
db.person[0]=dict(name='Max')  ### insert
db.person[id]=dict(name='Max') ### update by db.person.id
print db.person[id]            ### select by db.person.id
del db.person[id]              ### delete by db.person.id
""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Truncate and Drop a table</h3>
{{=CODE("""
db.person.truncate()
db.person.drop()""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Reference Fields Inner joins</h3>
{{=CODE("""
db.define_table(‘dog’,db.Field(’name’))
db.define_table(‘friendship’, db.Field(’person’,db.person), db.Field(‘dog’,db.dog))
db.friendship.insert(person=id, dog=db.dog.insert(name=’Snoopy’))
friends=(db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
rows = db(friends).select(db.person.name, db.dog.name)
for row in rows: print row.person.name, ’is friend of’, row.dog.name""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Left Outer Joins</h3>
{{=CODE("""
query=(db.person.id>0)
friends=(db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
rows = db(query).select(db.person.name, db.dog.name, left=db.dog.on(friends))
for row in rows: print row.person.name, ’is friend of’, row.dog.name or ‘nobody’""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Complex queries</h3>
{{=CODE("""
query = (db.person.id==1)|((db.person.id==2)&(db.person.name==’Max’))
query = (db.person.id==db.friendship.person)&(db.dog.id==db.friendship.dog)
query = db.person.name.lower().like(‘m%’)
query = db.person.id.belongs(('max','Max','MAX'))
query = db.person.birth.year()+1==2008
rows = db(query).select()""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Nested selects</h3>
{{=CODE("""
query = db.person.id.belongs(db()._select(db.friendship.person)""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Aggregates</h3>
{{=CODE("""
rows=db(friends).select(db.person.name,db.dog.id.count(),groupby=db.dog.id)""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

Aggregate functions db.table.field.count(), .max(), .min(), sum().

<h3>Aliases</h3>

{{=CODE("""
person=db.person
friendship=db.friendship
puppy=db.dog.with_alias('puppy')
query=(puppy.id==friendhip.dog)&(friendship.person==person.id)
rows=db().select(person.name,puppy.name,left=puppy.on(query))
""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}


<h3>Caching</h3>
{{=CODE("""
rows=db().select(db.person.ALL,cache=(cache.ram,3600))
""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

cache=(model,cache_timeout) where model can be cache.ram, cache.disk, cache.memcache or user defined caching model, cache_timeout is in seconds.

<h3>CSV Input</h3>

{{=CODE("db.person.import_from_csv_file(open(filename,’rb’))")}}

<h3>CSV Output</h3>

{{=CODE("str(rows)")}}

<h3>HTML output</h3>

{{=CODE("print rows.xml()")}}

<h3>Set field validators</h3>
{{=CODE("""
db.person.name.requires=IS_NOT_IN_DB(db,db.person.name)
db.friendship.person.requires=IS_IN_DB(db,db.person.id,’%(name)s’)""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}

<h3>Generate and process a form from a model</h3>
{{=CODE("""
form = SQLFORM(db.friendship)
if form.accepts(request.vars, session): response.flash=’record inserted’
elif form.errors: response.flash=’form errors’""".strip(),language='web2py',link=URL(r=request,c='global',f='vars'))}}
The form can then be displayed in a view with:
{{=CODE("{"+"{=form}"+"}",language='html')}}

<h3>SQL + HTML Examples</h3>
Given {{=CODE("rows=db().select(db.users.ALL)")}}
<ul>
<li><tt>SQLTABLE(rows)</tt> turns the rows into a CSS friendly table</li>
<li><tt>SQLFORM(db.users)</tt> makes an input form for users</li>
<li><tt>SQLFORM(db.users,rows[i])</tt> makes an edit form for the user in rows[i]</li>
<li><tt>SQLFORM(db.users,rows[i],deletable=True)</tt> makes an edit/delete form for the user in row[i]</li>
<li><tt>SQLFORM(....,fields=['name','email'])</tt> allows to specify which fields should be displayed in the form. Only those fields will be validated.</li>
<li><tt>SQLFORM(....,labels=['name':'Your Name'])</tt> allows to change the labels of the listed fields.</li>
</ul>
<p>
A form=SQLFORM(...) object has one method of practical interest:
<tt>form.accepts(request.vars,session)</tt> that processed the input variables (in request.vars) within the session and returns true if the form is valid, false otherwise. Processed variables are in form.vars and errors are in form.errors. The form is modified accordingly. If the form is accepted, accepts also perform the appropriate insert/update/delete in the database.
</p>

<h3>Extended usage</h3>
<p>The DAL API are exposed in web2py models, controllers and views but you can access them anywhere with
{{=CODE("""from gluon.sql import *""")}}
</p>

<h2>Doctest Examples</h2>
<p>More details is available in true examples below.
{{=CODE("""
    >>> db=SQLDB("sqlite://test.db")
    >>> #OR db=SQLDB("mysql://username:password@host:port/dbname")
    >>> #OR db=SQLDB("postgres://username:password@host:port/dbname")

    # syntax: SQLField('fieldname','fieldtype',length=32,
    #                   required=False, default=None,
    #                   requires=[IS_EMAIL(error_message='invalid email')])

    >>> tmp=db.define_table('users',
          # But notice that SQLField(...) is depreciated and you'd better use db.Field(...) instead
          SQLField('stringfield','string',length=32,required=True),
          SQLField('booleanfield','boolean',default=False),
          SQLField('passwordfield','password'),
          SQLField('textfield','text'),
          SQLField('blobfield','blob'),
          SQLField('uploadfield','upload'),
          SQLField('integerfield','integer'),
          SQLField('doublefield','double'),
          SQLField('datefield','date',default=datetime.date.today()),
          SQLField('timefield','time'),
          SQLField('datetimefield','datetime'),
          migrate='test_user.table')

   # Insert a field

    >>> db.users.insert(stringfield='a',booleanfield=True,
                       passwordfield='p',textfield='x',blobfield='x',
                       uploadfield=None, integerfield=5,doublefield=3.14,
                       datefield=datetime.date(2001,1,1),
                       timefield=datetime.time(12,30,15),
                       datetimefield=datetime.datetime(2002,2,2,12,30,15))
    1

    # Drop the table

    >>> db.users.drop()

    # Examples of insert, select, update, delete

    >>> tmp=db.define_table('person',
              SQLField('name'),
              SQLField('birth','date'),
              migrate='test_person.table')
    >>> person_id=db.person.insert(name="Marco",birth='2005-06-22')
    >>> person_id=db.person.insert(name="Massimo",birth='1971-12-21')
    >>> len(db().select(db.person.ALL))
    2
    >>> me=db(db.person.id==person_id).select()[0] # test select
    >>> me.name
    'Massimo'
    >>> db(db.person.name=='Massimo').update(name='massimo') # test update
    >>> db(db.person.name=='Marco').delete() # test delete

    Update a single record

    >>> me.update_record(name="Max")
    >>> me.name
    'Max'

    Examples of complex search conditions

    >>> len(db((db.person.name=='Max')&(db.person.birth<'2003-01-01')).select())
    1
    >>> len(db((db.person.name=='Max')|(db.person.birth<'2003-01-01')).select())
    1
    >>> me=db(db.person.id==person_id).select(db.person.name)[0]
    >>> me.name
    'Max'

    # Examples of search conditions using extract from date/datetime/time

    >>> len(db(db.person.birth.month()==12).select())
    1
    >>> len(db(db.person.birth.year()>1900).select())
    1

    Example of usage of NULL

    >>> len(db(db.person.birth==None).select()) ### test NULL
    0
    >>> len(db(db.person.birth!=None).select()) ### test NULL
    1

    # Examples of search conditions using lower, upper, and like

    >>> len(db(db.person.name.upper()=='MAX').select())
    1
    >>> len(db(db.person.name.like('%ax')).select())
    1
    >>> len(db(db.person.name.upper().like('%AX')).select())
    1
    >>> len(db(~db.person.name.upper().like('%AX')).select())
    0

    # orderby, groupby and limitby

    >>> people=db().select(db.person.name,orderby=db.person.name)
    >>> order=db.person.name|~db.person.birth
    >>> people=db().select(db.person.name,orderby=order)
    >>> people=db().select(db.person.name,orderby=order,groupby=db.person.name)
    >>> people=db().select(db.person.name,orderby=order,limitby=(0,100))

    # Example of one 2 many relation

    >>> tmp=db.define_table('dog',
              SQLField('name'),
              SQLField('birth','date'),
              SQLField('owner',db.person),
              migrate='test_dog.table')
    >>> db.dog.insert(name='Snoopy',birth=None,owner=person_id)
    1

    # A simple JOIN

    >>> len(db(db.dog.owner==db.person.id).select())
    1

    # Drop tables

    >>> db.dog.drop()
    >>> db.person.drop()

    # Example of many 2 many relation and SQLSet

    >>> tmp=db.define_table('author',SQLField('name'),
                            migrate='test_author.table')
    >>> tmp=db.define_table('paper',SQLField('title'),
                            migrate='test_paper.table')
    >>> tmp=db.define_table('authorship',
            SQLField('author_id',db.author),
            SQLField('paper_id',db.paper),
            migrate='test_authorship.table')
    >>> aid=db.author.insert(name='Massimo')
    >>> pid=db.paper.insert(title='QCD')
    >>> tmp=db.authorship.insert(author_id=aid,paper_id=pid)

    # Define a SQLSet

    >>> authored_papers=db((db.author.id==db.authorship.author_id)&
                           (db.paper.id==db.authorship.paper_id))
    >>> rows=authored_papers.select(db.author.name,db.paper.title)
    >>> for row in rows: print row.author.name, row.paper.title
    Massimo QCD

    # Example of search condition using  belongs

    >>> set=(1,2,3)
    >>> rows=db(db.paper.id.belongs(set)).select(db.paper.ALL)
    >>> print rows[0].title
    QCD

    # Example of search condition using nested select

    >>> nested_select=db()._select(db.authorship.paper_id)
    >>> rows=db(db.paper.id.belongs(nested_select)).select(db.paper.ALL)
    >>> print rows[0].title
    QCD

    # Output in csv

    >>> str(authored_papers.select(db.author.name,db.paper.title))
    author.name,paper.title
    Massimo,QCD

    # Delete all leftover tables

    >>> db.authorship.drop()
    >>> db.author.drop()
    >>> db.paper.drop()

    # Commit or rollback your work

    >>> db.commit() # or db.rollback()
  """,language='web2py',link='/examples/global/vars/')}}</p>

<p><tt>migrate</tt> can be False (do not create/alter tables), True (create/alter tables) or a filename (create/alter tables and store migration information in the file).</p>
<p>
Mind there are little idiosyncrasies like the fact that "user" is not a valid field name in PostgreSQL, or the fact that sqlite3 will ignore the type of a field and allow you to put anything in it despite the declared type. Every database backend has its own keywords that may conflict with your tablenames.
</p><br/><br/>