Lecture 6

At this point, you should be familiar with the content of the Database Abstraction Layer chapter of the web2py book. You do not need to know all of it of course, but you do need to be able to know roughly what is in there, how to do basic queries, updates, deletes, insertions, and how to find information in the chapter.

Here is the definition of the database tables for the geographical news wiki we discussed:

db.define_table('article',

    Field('created_by', db.auth_user, default=auth.user_id),

    Field('creation_date', 'datetime', default=datetime.datetime.utcnow()),

    # The fields below are used for indexing, producing maps, etc.

    Field('event_date', 'datetime', default=datetime.datetime.utcnow()),

    Field('rank', 'double', default=0.0),

    Field('live_revision', 'reference revision'),

    # The following fields are copied from the revision.

    Field('title', 'string', required=True),

    Field('tags', 'string'),

    Field('latitude', 'double'),

    Field('longitude', 'double'),

    format = '%(title)s %(creation_date)s'

db.define_table('revision',

    Field('article', db.article),

    Field('author', db.auth_user, default=auth.user_id),

    Field('date', 'datetime', default=datetime.datetime.utcnow()),

    Field('event_date', 'datetime', default=datetime.datetime.utcnow()),

    Field('title', 'string', required=True),

    Field('tags', 'string'),

    Field('body', 'text', required=True, default=''),

    Field('latitude', 'double'),

    Field('longitude', 'double'),

    Field('comment', 'string'),

    Field('is_live', 'boolean', default=True),

    format = '%(author)s %(date)s',

The code above works with the following methods for creating a page and updating a revision:

@auth.requires_login()

def create():

    """Creates an article."""

    form = SQLFORM(db.revision)

    if form.accepts(request.post_vars, session):

        revision_id = form.vars.id

        revision = db.revision(revision_id)

        session.flash = 'The news article has been created.'

        # Creates the news article.

        article_id = db.article.insert(

            live_revision=revision_id,

            title=revision.title,

            latitude=revision.latitude,

            longitude=revision.longitude,)

        # Updates the page to which the revision refers.

        revision.update_record(article=article_id)

        redirect(URL('index'))

    return dict(form=form)

@auth.requires_login()

def edit_revision():

    revision = db.revision(request.args(0)) or redirect(URL('index'))

    article = db.article(revision.article) or redirect(URL('index'))

    form = SQLFORM(db.revision)

    form[0][-1][1].append(SPAN(' ', INPUT(_type='button',_value='Cancel',_onclick="window.location='%s';" % URL('index'))))

    if not request.post_vars:

        form.vars.title = revision.title

        form.vars.body = revision.body

        form.vars.tags = revision.tags

        form.vars.latitude = revision.latitude

        form.vars.longitude = revision.longitude

        form.vars.event_date = revision.event_date

    form.vars.article = article.id

    if form.accepts(request.post_vars, session):

        # The old revision is no longer live.

        revision.update_record(is_live=False)

        # Copies the new revision data into the article.

        article.update_record(

            title=form.vars.title,

            tags=form.vars.tags,

            latitude=form.vars.latitude,

            longitude=form.vars.longitude,

            event_date=form.vars.event_date,

            live_revision=form.vars.id,

        # Go look at the new revision now.

        redirect(URL('view_article', args=[article.id]))

    # Populates the form with the values from the current revision.

    return dict(revision=revision, article=article, form=form)

Here is the generic code to do insertions, deletions, etc. See the book for more details (this is exactly what was presented in class):

from datetime import datetime

db.define_table('page',

    Field('title', length=256), # title of most recent revision

    Field('revision', 'reference revision'), # most recent revision

db.define_table('revision',

    Field('created_on', 'datetime', default=datetime.utcnow()),

    Field('author', db.auth_user, default=auth.user_id),

    Field('page', db.page),

    Field('content', 'text'),

    Field('is_spam', 'boolean'),

    Field('title', length=256),

db.page.revision.on_delete = 'SET NULL'

###########################

id = db.revision.insert(page = page_number, content="hello",

                        title = "my first page")

URL('default', 'view_page', args=[id])

db(<what>).select(<either all fields, or some>).orderby().limit()

rows = db(db.revision.author == auth.user_id).select()

for r in rows:

    logger.info("the title was: %r" % r.title)

# updates

# as in sql, many rows at once:

db(db.revision.author == auth.user_id).update(db.revision.is_spam = False)

rows = db((db.revision.author == auth.user_id) &

          (db.revision.page_id == page_id)).select()

row = db(db.permission_table.user == auth.user_id).select().first()

if row is not None:

    logger.info("my row is: %r" % row)

# way 1

row.update_record(user = "olga", created_on = datetime.utcnow())

# way 2

row.user = "olga"

row.created_on = datetime.utcnow()

row.update_record()

db(db.revision.author == auth.user_id).delete()

Page updated

Report abuse