SQL - Object-Relational Frameworks¶
Tight integration between application logic and the database
Describe the database model as an object-oriented class description
Write queries not in SQL but directly in the programming language
Create tools that are DB agnostic
Main focus:
Most web based db programming requires a number of tasks that are highly repetitive and common (and not as glamarous as SQL programming).
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.
Examples: authentication tools, password/email data types
Many commonly used examples:
- Django for Python: Disqus, bitbucket, instagram, pinterest
- Ruby on Rails or Grail for Ruby: airbnb, ask.fm, couchsurfind, github
- Hibernate for Java
- DataObjects.Net for .NET
- SQLAlchemy and Flask for Python
We will base the examples below on Django.
MVC/T: Models, Views and Templates (or Controllers)¶
- Build a full-stack application by defining the different components
- Models are the data models of the tables that will be stored in the database
- Views are the HTML pages that you will see, loading data from models and executing functions for certain actions (like button clicks)
- Controller is the application logic: that tells you what will happen when certain actions are executed: run queries, db changes and render new HTML pages
- Often views are a mix of HTML/Python and Javascript for active elements
Models¶
Define your DB tables using an object-relational paradigm
Each table is a class, storing objects of this type
class Student(models.Model): name = models.CharField(max_length=255) email = models.CharField(max_length=255) address = models.CharField(max_length=255) year = models.IntegerField() gpa = models.FloatField() major = models.CharField(max_length=2)
The table associated will be called Students and will have a primary key id by default (can be overridden).
Views¶
Views can query these objects using simple queries:
def index(request): students = Student.objects.all() return render(request, 'index.html', {'students':students,})
Templates can render these objects using simple loops:
<ul> {% for student in students %} <li><b>{{ student.name }}</b>:</li> <ul> <li>ID: {{student.id}}</li> <li>Address: {{student.address}}</li> <li>Email: {{student.email}}</li> <li>Year: {{student.year}}</li> <li>GPA: {{student.gpa}}</li> </ul> {% endfor %} </ul>
Complex Models¶
Foreign keys:
class Department(models.Model): name = models.CharField(max_length=255) office = models.CharField(max_length=40) phone = models.CharField(max_length=12) class Major(models.Model): name = models.CharField(max_length=255) department = models.ForeignKey(Department, on_delete.Models.CASCADE)
Allows for the querying and retrieval of models through the foreign keys:
departments = Deparment.objects.all() majors = Major.objects.all() for major in majors: print (major.department.name) majors = Major.objects.filter(department__name = 'Computer Science')
Querying¶
- Most queries are simple filter statements over single relations or relations obtained through foreign keys.
- Does not require you to know full SQL.
- Most application function is easily mapped to CRUD operations (create, read, update and delete) that are easily supported
- Be careful if your join is different than what the foreign key implies
- Be careful about how much data is read for each object and when: for deep nested structures, does it read the whole hierarchy?
Summary¶
OR frameworks are quite powerful and provide a lot of functionality off the shelf
DRY principle: do not repeat yourself: write code once and use many times
For the tools, you pay a price: restrictive models and naming conventions
Example: lack of support for multi-attribute keys
You need to be careful if your query is best handled by the tool and by custom SQL
Same as application logic: is it better to write functions in the views or a stored procedure in the back end.