app/Academy w3d2
SQL Awesomeness
Today we learned more about SQL. After clearing up some questions over the assessment and yesterday's more challenging exercises, Max split SQL into the DML and the DDL.
DDL - Data Definition Language
The Data Definition Language (DDL) is all about establishing a database's schema.
A database's schema is its blueprint. It includes such information as which tables are in the database, the names and types of each table's columns, and constraints placed on data.
Some common SQL statements that belong to the DDL are:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Source- StackOverflow: What is DDL and DML
Yesterday we ran a shell script that setup a database and populated several tables for us, so we didn't really have any exposure to the DDL yesterday.
DML - Data Manipulation Language
The Data Manipulation Language (DML) is all about managing data within schema objects.
Some common SQL statements that belong to the DML are:
SELECT - retrieve data from the a database
INSERT - insert data into a table UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
Source- StackOverflow: What is DDL and DML
All of the different SQL queries we ran yesterday were DML statements.
SQLite3
Max walked us through setting up an SQLite3 database.
SQLite is a database system that is great for situations when:
The database and application are on the same machine.
We don't need to have more than one connection to the database at any given time.
The data set is relatively small.
In a small demo, Max wrote a shell script to CREATE a database table of people in the Game of Thrones Series.
Once the table had been set up with the appropriate column names, data types, and constraints, he added some lines to the script to seed the table by using INSERT to add some of the characters.
It was nice that there is so much overlap in syntax between PostgreSQL and SQLite3!
Ruby's SQLite3 Gem
There's a gem for ruby called (fittingly enough) sqlite3. Max demonstrated how to use it to build an application to interface with our database. This sort of application employs object relational mapping (ORM).
Establishing the Connection
The code below is all we had to do to connect to our SQLite3 database.
require 'sqlite3' require 'singleton' class PeopleDatabase < SQLite3::Database include Singleton def initialize super('game_of_thrones.db') self.results_as_hash = true # Query result returns an array of rows # {'column_name' => value } instead of # ['column_name', 'value'] self.type_translation = true # Change results to correct ruby type end end
Including the Singleton module implements the singleton pattern, which prevents instantiation of more than one object of the class. This is useful to prevent concurrent connections to the database.
Including the module makes PeopleDatabase::new a private method, so now we must use PeopleDatabase#instance in order to access the instance methods inherited from the SQLite3::Database class.
Executing statements
With the database singleton ready to go, we executed SQL statements using the following syntax:
PeopleDatabase.instance.execute(<<-SQL, 'Arya', 16, 'Stark') INSERT INTO people ( name, age, house ) VALUES ( ?, ?, ? ) SQL
It turns out the Database#execute instance method is a lot more powerful than we knew today, so I'm looking forward to refactoring our code with a block to process results and hashes to make order-independent bind variables and placeholders.
Building the ORM in Pairs
Armed with the tools from lecture, my partner and I set out to build a simple ORM to manage three classes of object: users, questions, and replies. Additionally, users can like and follow questions.
Today's partner was stupidly smart (again), and we raced through the day's work together.
Setup
We wrote a simple shell script to set up the database's schema (DDL) and seeded it with some simple data (DML).
Models
From there we built classes to interface with each table and methods that handled DML tasks, like looking up the top n most-liked questions, and a user's "average karma" (average number of likes for a User's questions).
Bonus: Extract Superclass
Once we had the skeleton of our ORM working, we did some refactoring based on a great code review from Winnie. The bonus had us refactor our classes using the Extract Superclass refactoring pattern.
Each of our models had similar #find_by_id(id) instance methods, so we made a ModelBase class and moved the generalized behavior there.
Return of the ::method_missing
Finally we ended the day by building a method_missing delegator that intercepted any call to an unknown method whose name began with find_by....
We had a lot of fun getting this to work, and I'm looking forward to revisiting it armed with the discovery of Database#execute additional behavior.
Today was a good day.











