Makers Week 4: First Contact with Database

Database is a mysterious but powerful creature

Image for post
Image for post
source: Pixabay

London, Shoreditch

The 4th week at Makers was the most difficult week I encountered so far, thanks to the introduction of database concept. So I have to apologise for the delay of this week’s blog, as I spent the whole weekend working on the weekend project.

The goal of this week is pretty straightforward.

  • Explain the basics of how databases work (e.g. SQL, entity relationship model association, object relationship mapping, etc)
  • Build a simple web app with a database (During the week we built a bookmark manager, at the weekend it was Chitter Challenge — a simplified version of twitter; it’s also important to adopt and implement the RESTful route design as well)
  • Other (Understand what are env variables and how to set up in different development stage; learn and apply some ruby syntax sugar)

The first part is about database (relationship database) basics. Since I learnt about SQL previously, I felt comfortable to manipulate the language to create and modify the table. However, when it comes to object relationship mapping, that is another thing.

Let’s revisit the concept, according to Wikipedia, the heart of the problem involves translating the logical representation of the objects into an atomised form that is capable of being stored in the database while preserving the properties of the objects and their relationships so that they can be reloaded as objects when needed. If this storage and retrieval functionality is implemented, the objects are said to be persistent.

I guess the reason I felt challenging during this week was due to we integrated database into the MVC model in the curriculum — aka transforming SQL data into Object in Ruby.

Even though we know that we will all use object relationship mapping tools such as ActiveRecord or Data Mapper in the end, it would be great to know how exactly this transformation happens behind the scene. So instead of jumping into the easy life mode — object relationship mapping (ORM) systems at the beginning, we built the ORM ourselves. (An example below:)

The above example is about creating a new row in the database, and I did the same for other CRUD actions — updating, deleting, reading. Harder as it is, I do appreciate this process. It is easy to adopt the easy mode, picking the tool and starting to apply, but I would like to know what is happening under the wood.

In all, there are 3 steps to wrapping database data in program objects

  • Bring database data from database rows into the Ruby application.
  • Wrap that data in Ruby objects .
  • Ask a model to do things with the data it wraps.

Another interesting topic worths pointing out is the association between the tables. In ruby we have 6 basic associations.

  • belongs_to
  • has_one
  • has_many
  • has_many :through
  • has_one :through
  • has_and_belongs_to_many

You may notice that there might be more than one options to connect the tables. For example, you can have either has_many :through or has_and_belongs_to_many to make table A connect with table B through table B, or make table C the main entity, which has relationship with table B, and belongs to table A.

As if the database is not difficult enough, we also learn how to set up the test environment correctly.

For example, tests should always run against an empty database. We should set up any required test data in the test itself. To make the test independent of each other, we need to set up and clear out the test database each run. One way of doing so is to ‘drop’ the database between each run of the script:

Using TRUNCATE table, this approach cleans the data from the bookmarks table.

The last bit is about some syntax sugar, which I came across during the weekly project, so I did some exploration myself.

One interesting concept is required keyword arguments, which are defined with a trailing colon. As an uncommon way of defining methods, there are pros and cons of keyword arguments:

Let’s say we have a method to calculate the final price of the product:

This method does its job, but as a reader of the code using the mysterious_total method, you have no idea what those arguments mean without looking up the implementation of the method. By using keyword arguments, we know what the arguments mean without looking up the implementation of the called method:

So keyword arguments allow us to switch the order of the arguments, without affecting the behavior of the method:

If we switch the order of the positional arguments, we are not going to get the same results, giving our customers more of a discount than they deserve:

That’s all for this week. See you around next week:)

==========Highlights of the Week=============

A. Database Basics

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. It is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Primary key: A primary key uniquely specifies a tuple within a table. In order for an attribute to be a good primary key it must not repeat.

Foreign key: A foreign key is a field in a relational table that matches the primary key column of another table. It relates the two keys. Foreign keys need not have unique values in the referencing relation. A foreign key can be used to cross-reference tables, and it effectively uses the values of attributes in the referenced relation to restrict the domain of one or more attributes in the referencing relation. The concept is described formally as: “For all tuples in the referencing relation projected over the referencing attributes, there must exist a tuple in the referenced relation projected over those same attributes such that the values in each of the referencing attributes match the corresponding values in the referenced attributes.”

Rails supports six types of associations:

  • belongs_to
  • has_one
  • has_many
  • has_many :through
  • has_one :through
  • has_and_belongs_to_many

PostgreSQL: also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.

Common PostgreSQL command:

DROP TABLE deletes the table.

TRUNCATE TABLE empties it, but leaves its structure for future data

Ruby pg is a module that allows Ruby programs to interact with the PostgreSQL database engine. It supports the functions defined in the libpq C library.

According to Wikipedia, (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages.

In object-oriented programming, data-management tasks act on objects that are almost always non-scalar values. For example, an address book entry that represents a single person along with zero or more phone numbers and zero or more addresses. This could be modeled in an object-oriented implementation by a “Person object” with attributes/fields to hold each data item that the entry comprises. Various methods can be associated with the object, such as a method to return the preferred phone number, the home address, and so on.

However, many popular database products such as SQL database management systems (DBMS) can only store and manipulate scalar values such as integers and strings organized within tables. The programmer must either convert the object values into groups of simpler values for storage in the database (and convert them back upon retrieval), or only use simple scalar values within the program. Object-relational mapping implements the first approach

The heart of the problem involves translating the logical representation of the objects into an atomized form that is capable of being stored in the database while preserving the properties of the objects and their relationships so that they can be reloaded as objects when needed. If this storage and retrieval functionality is implemented, the objects are said to be persistent.

ORM stands for “Object to Relational Mapping” where

  • The Object part is the one you use with your programming language ( python in this case )
  • The Relational part is a Relational Database Manager System ( A database that is ) there are other types of databases but the most popular is relational ( you know tables, columns, pk fk etc eg Oracle MySQL, MS-SQL
  • And finally the Mapping part is where you do a bridge between your objects and your tables.

In applications where you don’t use a ORM framework you do this by hand. Using an ORM framework would allow you do reduce the boilerplate needed to create the solution.

In ruby , there are two popular ORM tools, active record and data mapper.

B. Set up Proper Environment

Env vars are easy to change between deploys without changing any code; unlike config files, there is little chance of them being checked into the code repo accidentally; and unlike custom config files, or other config mechanisms such as Java System Properties, they are a language- and OS-agnostic standard. According to Starr Horne:

When a process shuts down, its environment variables are lost. This is the same principal that causes you to lose environment variables when your server reboots, or when you exit your shell.

Child processes inherit env vars from their parent.Since children only get copies of their parents’ environment variables, changes made by the child have no effect on the parent. Local variables aren’t available to child processes. Export converts the local variable to an environment variable.

It’s normal to have multiple environments in applications. These might include:

  • A development environment that runs locally on your computer, so you can click around it and work on it.
  • A production environment that runs remotely on someone else’s computer, so other people on the internet can click around it.
  • A test environment that runs locally on your computer whenever you run your tests. It comes into being especially for your tests, and disappears straight after your tests finish.
  • There’s also a staging environment, where your application runs remotely on someone else’s computer, at a secret link so you can click around it to check it’s all working right before you move it to production.

For the data,

  • The production database will contain ‘real’ data. For instance: Facebook’s production environment contains real-life users, posts, comments, likes, and so on.
  • The test database will contain no data. This way, we can set up whatever data we need right before we run our tests.
  • The development database will initially contain no data. If we run our application locally, we can add data to it.

C. REST Route

Resources :The web is a network of resources. A resource is some data stored somewhere.

Representations Representations tie URLs to actions a user might want to take with these resources. E.g. GET /bookmarks/1

Web applications are state machines and REST defines their interface

Movements between the states are determined by user interactions, and are called state transitions.

  • Having 35 bookmarks (state)
  • Adding a bookmark (state transition)

For each of these, a RESTful route defines the next state of the machine, and the machine responds with the state it’s now in:

  • GET /bookmarks returns a webpage with 35 bookmarks
  • POST /bookmarks transitions the machine to a new state with 36 bookmarks, and returns a webpage with 36 bookmarks.

A RESTful route is a route that provides mapping between HTTP verbs (get, post, put, delete, patch) to controller CRUD actions (create, read, update, delete). Instead of relying solely on the URL to indicate what site to visit, a RESTful route also depends on the HTTP verb and the URL.

It’s important to note that much of the CRUD actions are different actions that occur on the same resource. Let’s take the example of an article with the ID 4. If we wanted to view the article, we would make a GET request to /articles/4. But what about when I want to update that article? Am I hitting a different resource? Nope! Just doing a different action to that same resource. So instead of a GET against /articles/4 we do a PUT. That's why separating what you're talking to (the resource/noun) from the action you're doing (the HTTP verb) is important! That's key to REST.

D. Ruby Syntax Sugar

Required keyword arguments: Ruby 2.0 doesn’t have built-in support for required keyword arguments. Luckily, Ruby 2.1 introduced required keyword arguments, which are defined with a trailing colon:

If a required keyword argument is missing, Ruby will raise a useful ArgumentError that tells us which required argument we must include.

Keyword arguments vs positional arguments: Assume we have a method with positional arguments:

This method does its job, but as a reader of the code using the mysterious_total method, I have no idea what those arguments mean without looking up the implementation of the method.

By using keyword arguments, we know what the arguments mean without looking up the implementation of the called method:

Keyword arguments allow us to switch the order of the arguments, without affecting the behavior of the method:

If we switch the order of the positional arguments, we are not going to get the same results, giving our customers more of a discount than they deserve:

You can implicitly create an array by listing multiple values when assigning:

This implicitly creates an Array.

You can use * or the “splat” operator or unpack an Array when assigning. This is similar to multiple assignment:

You can splat anywhere in the left-hand side of the assignment:

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store