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.