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.