DB programming with Object-relational mapping

DB programming with

Object-relational

mapping

Frameworks

•

Most web based db programming requires a number of tasks that are highly repetitive and common.

•

Examples: data validation, input sanitization, etc.

•

Frameworks are designed to provide common tools for these tasks so that the programs are easy and fast to develop.

Frameworks

•

The common principle for frameworks is DRY: don’t repeat yourself.

•

For example, do not write the code for the

same validation in different places, simply reuse it.

•

Framework make it easy to use DRY, with the help of the MVC framework.

MVC - view

•

MVC framework consists of model-view-controllers.

•

A view is an interface a user sees. Generally the view is mostly the design of the

interface, not the code to populate it with data.

MVC - model

•

Applications have to use data, normally from databases. They display this data, modify,

create and destroy it.

•

A model (like a data model) tells the application what type of an object it is modifying.

MVC - controller

•

A controller is like a traffic controller, it loads data from database to a view, reads input from screen and saves it to the database, it describes which page is loaded when a link is clicked.

•

Controllers contain the application logic as well as the navigation structure of the application.

MVC

•

By storing the application in terms of these distinct components makes it possible to develop functionality for each component separately and/or reuse it.

Object-relational mapping

•

Most database programming is done using object oriented languages

•

In the programming paradigms we have seen so far, the program has no knowledge of the model of the database, only query results.

•

Object-relational mapping (ORM for short) is a paradigm where the database model is mapped directly to classes in the program.

ORM frameworks

•

ORM frameworks support ORM at the model level. The database model is stored as an object oriented model.

•

In frameworks like Django, Rails, the database model is written in the program (using Python for example,

instead of SQL) and then created in the database using the framework functions.

•

Furthermore, most of the querying is done with

functions in the framework, not SQL, allowing better integration with the underlying programming language.

Example systems

•

Webobjects uses Java

•

ITunes is implemented with Webobjects

•

Hibernate uses Java

•

free/open source, growing market share

•

Ruby on Rails uses Ruby

•

free, growing market share, mac os 10.5 ships with RoR

Django on CS

•

Django is installed for you on prospector.cs.rpi.edu. To run it, make sure /usr/bin is in your path. If not, add the

following lines to the end of your .bashrc file:

PATH=$PATH:/usr/bin export PATH

•

Now, you can start a new project by typing:

Django on CS

•

Now you can modify settings.py to point to the CS installation of postgresql (substitute your username):

DATABASE_ENGINE = 'postgresql_psycopg2' DATABASE_NAME = 'sibel' DATABASE_USER = 'csc4380_sibel' DATABASE_PASSWORD = 'yourpassword' DATABASE_HOST = 'csc4380.cs.rpi.edu' DATABASE_PORT = '5432'

Object relational model

•

Used frequently in database backed applications

•

Database tables are mapped to classes in the programs, tuples to objects

•

The object-relational mapper manages opening connections, reading and saving database objects, receiving database

Django ORM

•

Django uses Python classes to map tables to classes, and rows into objects

•

In addition to usual db types, it supports special types for web based application based on these types

•

Objects are first defined in the django

program, and then mapped to the db using the syncdb command

Django ORM

•

Examples of data types:

•

CharField

•

IntegerField

•

EmailField, URLField, IPAddressField

•

ForeignKey, ManyToManyField (to define relationship between classes/tables)

Example

class Category(models.Model) : type = models.CharField(max_length=100) class Author(models.Model) : name = models.CharField(max_length=100) class Book(models.Model) : title = models.CharField(max_length=100) author = models.ForeignKey(Author) categories = models.ManyToManyField(Category)

Django ORM

•

Each class has a primary key (identifier) called “id” by default. The primary key can also be changed to another attribute.

•

For many to many mappings, Django creates an extra relation that holds the name of the relation that stores pairs of mapped objects (for example, pair of book and category pairs). It is possible to

create a class for this join relation with the keyword “through”

categories = models.ManyToManyField(Category, through “BookCategories”)

class BookCategories(models.Model): book = models.ForeignKey(Book)

author = models.ForeignKey(Category) priority = models.IntegerField()

Django ORM

•

Once models are defined and synced to a database, they can be used in programs.

•

Read objects from a class as a list

•

Follow foreign keys to find/retrieve the object the foreign key points to

•

Follow many to many relationships to read all the objects that is linked using a list

Using the Django objects

# reading a specific object from the database # equivalent to a select clause

book = Book.objects.get(title=”Moby Dick”)

# follow foreign key

# in SQL, this involves a join between two tables author = book.Author

Using the Django objects

# read all books for a specific author

# this also requires a join in SQL, also note that

# relation between book and author is defined one way # but used in either direction

Using the Django objects

# many to many relationships are queried in the same way

categories = book.categories_set.all()

# in either direction

Querying

•

Django allows the use of SQL as a last resort, but in this case, the query result does not return an object of a specific type, using the DB:API libraries.

•

Most Django queries are written using simple methods that operate on (i.e. select) objects that are linked to each other (and the join is implicit)

•

all: returns all

•

filter: similar to select, with conditions involving equality,

comparisons, contains (SQL like): examples: gt for greater than.

•

for each attribute, a comparison method can be constructed with “__”, example: title__contains = “Moby” is equivalent to SQL: title LIKE ‘%Moby%’

Querying

#queries return query sets

books_on_databases = Book.objects.filter(title__contains = “Database”) for book in books_on_databases

print book.title

john_does = Person.objects.filter(last=”Doe”, first=”John”) today = datetime.now()

Querying

#build new conditions on existing query sets

#use exclude for negative conditions, no Oracle DB books please books = books_on_databases.exclude(title__contains = “Oracle”) #Books by Melville, the SQL will involve a join

books = Book.objects.filter(author__name = “Melville”).order_by(‘title’) #it is also possible to support query composition with or (|), and (&)

john_does = Person.objects.filter(last=”Doe”).exclude(Q(first=”John”) | Q(middle=”Joseph”))

Extras

•

You can also add snippets of SQL like SELECT, WHERE to queries to handle

Security?

•

By passing strings from the program to SQL using placeholders like we see above, we allow the program to encode strings by escaping special characters. This allows the system to be safe against SQL injection

attacks.

•

When displaying data entered by user in your site, do not interpret HTML code they may put in their typed text, display it as is.

Sanitizing input

•

Do not use:

•

matches = Person.objects.all().extra(where[“first=’%s’ % unknown_input()])

•

which would work but takes the user input and sends it to the database directly without sanitizing.

•

Instead use

•

matches = Person.objects.all().extra(where[“first=’%s’], params=[unknown_input()])

•

which takes specific inputs and encodes them for the place holder %s in a safe way for the database.

•

You have to do sanitizing of inputs in all applications: in java, use prepared statements for example.

Limit destructive operations

•

Example from guides.rubyonrails.org:

•

Bob browses a message board and views a post from a hacker where there is a crafted HTML image element. The element

references a command in Bob’s project management application, rather than an image file.

•

<img src="http://www.webapp.com/project/1/destroy">

•

Bob’s session at www.webapp.com is still alive, because he didn’t log out a few minutes ago.

•

By viewing the post, the browser finds an image tag. It tries to load the suspected image from www.webapp.com. It will also send along the cookie with the valid session id.

Limit destructive operations

•

Solution:

•

Any operation that destroys or changes

objects in the database should be accessible only with a POST action (not GET).

•

Delete is a form of post in the REST