CompSci 316 (Fall 2012):
Introduction to Database Systems

Index

• Setting up development environment
• A Django primer
• Common tasks
• Coding tips
• django-social-auth
• Additional information

Setting up development environment

The latest version of Django (as of August 2012) is already installed on the server. We have provided an example Django site for the beer drinkers' database. To set it up, run "/home/dbcourse/examples/django-db-beers/setup.sh". This command will set up the directory django.my.net/ in your home directory as well as a new PostgreSQL database called mysite for use by Django. In this process, you will be prompted to create a Django admin user, which can be used to manage the Django site. You can now access the Django site at the URL http://django.my.net/admin/.

A Django primer

• Automatic mapping of object models to database tables. You specify models using Python classes; Django automatically handles their storage, retrieval, and update in a relational database. You can do (almost) all data manipulation on your Python objects without being concerned about SQL. Django also automatically generates user interfaces for editing them.
• Separation of content logic (views) and presentation (templates):
  • Each view handles one type of page requests. For example, one view displays all drinkers in the databases, and another allows editing of a drinker's information. The view code is responsible for modifying the database (if needed) and generating content (Python objects) to be displayed. The task of rendering contents is done by templates.
  • A template is responsible for rendering a web page, using the content provided by the view. There is typically one template for each view. Django templates can be inherited and extended, making it easy to support a consistent style and provide customization. Templates can do some simple post-processing with the content to facilitate presentation (such as sorting and looping through a list of items).
• Mapping of URLs to views. Django allows you to map URLs to views, and reverse-map views to URLs. With the latter, your view/template code can link to other pages by calling the associated views with their names, without worrying about the actual URLs used in a particular deployment.

We now explain the structure of the directory ~/django.my.net/:

• mysite/, where mysite is the name of your project (think of its as your website, which can host multiple Django "apps"). This directory holds your project-wide configuration. Under this directory, you will find:
  • settings.py: your main configuration file. Note the DATABASE setting, which tells Django how to connect to its database. Since Django will use the database for a variety of things, it is best to create one database for each Django project. Also note the execfile calls at the end of the file that incorporate app-specific settings.
  • settings_app_name.py: configuration for app named app_name. Apps include those you wrote yourself for this site (e.g., db_beers), as well as built-in and third-party Django apps (e.g., social_auth).
  • urls.py: mapping between URLs to views for your site. Note how it incorporates URL mappings for your individual apps.
• db_beers/, where db_beers is the name of a Django app. There can be multiple of these app directories. Within this directory, you will find:
  • models.py: your models, i.e., Python classes that will be automatically mapped to database tables by Django.
  • views.py: your views. Note that most of them end of with a render_to_response() call, which hands contents over to specific templates for display. Also note that you can redirect a request to another view using reverse URL mapping with a reverse() call.
  • urls.py: mapping between URLs to views for this particular app. It gets included by the project's urls.py (or it can be overridden).
  • templates/db_beers/: directory holding templates for your app. Note that we make a subdirectory with the app's name, so we can reference these templates using app_name/file_name, and avoid clashes with other app's templates.
  • static/db_beers/: directory holding static files needed by your app's pages, such as image, CSS, and JavaScript files. Note that we make a subdirectory with the app's name, so we can reference these static resources using app_name/file_name, and avoid clashes with other app's static resources.
  • templatetags/: code that extends the template language. See extras.py for a custom "filter" (which gets used by ~/django.my.net/db_beers/templates/db_beers/base.html).
• templates/: the directory holding additional templates. When an app invokes a template, this directory will be searched first before it's own templates/ subdirectory. Therefore, you can "override" app-supplied templates with ones in this directory.
• static/: the directory containing all static files. There is no need to modify this directory by yourself; Django provides a command to collect all static files from the static/ subdirectories of your apps.
• media/: by convention, the directory where media files uploaded by users reside.

Common tasks

Apache-related

While many changes you make to your site will be reflected automatically by reloading the page, the safest thing to do is to restart the Apache web server, using the command "sudo /etc/init.d/apache2 restart".

For debugging, you will find the error log in /var/log/apache2/error.log useful.

Database-related

Your Django site uses a database bearing your project name, in this case mysite. Django automatically sets up the database schema based on your models. This step is already done when you set up the site (see /home/dbcourse/examples/django-db-beers/setup.sh if you want the details). However, whenever you change your app data model or add/remove apps, you need to run "./manage.py syncdb" in the ~/django.my.net/ directory to make sure that the database tables are consistent with your apps.

Note that Django doesn't support primary keys consisting of multiple columns. For example, in our beer drinkers' database, Django doesn't allow (beer, drinker) to be key of Frequents; instead, it creates a surrogate id for Frequents. For this reason, Django's tables are actually different from those specified in /home/dbcourse/examples/db-beers/create.sql. If you are curious about how Django creates the schema, use "./manage.py sqlall db_beers"; this command will show you the SQL commands (but won't actually run them).

You can access the database directly through the PostgreSQL command-line processor (see PostgreSQL Notes). The command is "psql mysite", where mysite is the database name. In most cases, however, you probably just need to use Django's built-in web-based interface for administering the database. You can access that at the URL http://django.my.net/admin/. To log in, use the Django admin user name and password you specified when setting up the site.

Django can automatically load data into the database using "fixtures"; see Django documentation for more details. For our example, since we already have the load script for the beer drinkers' database, we use that instead. The data is already loaded when you set up the site, but if you ever need to "reset" the database content (after you modified it, for example), run "(cd /home/dbcourse/examples/db-beers/; psql mysite -af load.sql)". Note that the \COPY commands leave out surrogate ids in their column lists, and the data files do not specify values of these ids either. The database automatically generates them.

Adding an app

When adding a new app app_name, remember to (all paths below are relative to ~/django.my.net/):

• Make sure the directory app_name/ follows the same directory structure as db_beers/. Remember that the app's static files should go under app_name/static/app_name/ and templates under app_name/templates/app_name/.
• Create mysite/settings_app_name.py and edit mysite/settings.py to execute the new settings file.
• Edit mysite/urls.py to incorporate the URL mappings for app_name.
• Run "./manage.py syncdb".
• Load initial data if needed.
• Run "./manage.py collectstatic" to collect all static files under static/.
• Restart Apache.

As an example, you can try adding a new app called db_beers_alt by following the steps above. This app has pretty much the same functionality as db_beers but uses fewer advanced Django features, and does not use django-social-auth. To avoid clashing with db_beers, it operates on a separate set of tables whose names are prefixed with "db_beers_alt_". To add the db_beers_alt app, start with "cp -r /home/dbcourse/examples/django-db-beers/db_beers_alt/ ~/django.my.net/db_beers_alt/". For settings and URL mapping, follow db_beers as an example. You should set up the app to be accessible via http://django.my.net/db-beers-alt/. After ./manage.py syncdb, to load data, use "psql mysite -af /home/dbcourse/examples/django-db-beers/init-db-beers-alt.sql". Then, collect static files.

Changing an app

• If the model is changed, remember to run "./manage.py syncdb".
• If the set of static files has changed, remember to run "./manage.py collectstatic".
• Restart Apache.

Coding tips

Avoid hardcoding URLs and paths in your code as much as possible. Instead, use reverse URL mapping and variables PROJ_ROOT and PROJ_NAME (initialized in settings.py). Doing so will make it much less painful when you need to move your site/apps.

Django provides of lots handy libraries. For example, Django can automatically generate forms for editing an object and its associated objects. See db_beers views and templates for examples. Of course, you are free to roll your own forms if you want more control; the db_beers_alt app takes this approach.

You almost don't need to write SQL in Django; Django's syntax for simple things like key lookups and following foreign keys is convenient. However, for slightly more complex queries, it gets ungainly quickly and can be harder to read and debug than SQL. You can see some examples of SQL vs. Django syntax in db_beers_alt's views.

django-social-auth

django-social-auth is a Django package that allows visitor to your site to authenticate using their social network accounts (at Twitter, Facebook, Google, for example). Our example website demonstrates the use of this package. When you visit a page that requires login, you will be redirected to a page where you can choose to login with your Twitter or Facebook account, or an account created by our local Django site (e.g., the admin account; note that we did not provide an interface for new users to register with our site, but we could).

For the Twitter and Facebook logins to work, you will need to provide correct settings in ~/django.my.net/mysite/settings_social_auth.py for the following variables: TWITTER_CONSUMER_KEY, TWITTER_CONSUMER_SECRET, FACEBOOK_APP_ID, and FACEBOOK_API_SECRET. Follow the instructions on how to obtain correct settings for Twitter and for Facebook.

Note that logging out from our Django site doesn't imply logging out from the social network accounts. For example, Facebook may keep you logged in for an extended period of time before prompting you again for password. Therefore, when you log into our site again through Facebook, Facebook will consider you as already logged in, and you will be redirected to your target page without being asked for password.

Additional information

• Django 1.4 documentation.
• django-social-auth package (including pointer to documentation).