Getting Started with Ohm: A Cheat Sheet Guide

Ohm Cheatsheet

Ohm: a library for storing data in Redis, a key-value store

Ohm Version: 2.3.0

I. Prerequisites

  • Redis
  • Ruby

II. Installation

Just install the ohm gem, fire up an irb session or write a Ruby script and require it.

gem install ohm

III. Connecting to Redis

A. Single Connection

By default, Ohm connects to Redis at localhost (127.0.0.1), port 6379. If you wish to override this, you may set a different Redis URL with Redic, a lightweight Redis client.

require "ohm"
Ohm.redis = Redic.new("redis://<IP>:<PORT>")

# Sample: Ohm.redis = Redic.new("redis://58.163.21.31:6379")

B. Multiple Connections

Certain Models could connect to different Redis servers.

Ohm.redis = Redic.new(REDIS_URL1)

class Student < Ohm::Model
end

Student = Redic.new(REDIS_URL2)

IV. Simple Key-Value Fetch and Get

require "ohm"

Ohm.redis.call "SET", "Key", "Value"

Ohm.redis.call "GET", "Key"
# => "Value"

V. Mapping Objects to Key Value Store

A. Class Declaration

class Student < Ohm::Model
end

B. Classes Attributes, References, and More

 class Student < Ohm::Model
   attribute :student_number
   attribute :first_name
   attribute :last_name
   attribute :birthdate
   
   index :student_number
 end

class Course < Ohm::Model
  attribute :name
  attribute :domain
  reference :classroom, :Classroom
  set :students, :Student
  counter :waitlist
  counter :drops
  
  index :name
end

class Classroom < Ohm::Model
  attribute :name
  collection :courses, :Course
end

i. attribute

Any value that can be stored in a string. Numbers stored would be returned as a string.

ii. set

Similar to an unordered list.

a. Adding To a Set
course = Course.create(name: 'English 1')

student1 = Student.create(first_name: 'Adelen', last_name: 'Festin')
course.students.add(student1)

course.students.add(Student.create(first_name: 'Victoria', last_name: 'Po'))

course.students.count
# => 2
b. Iterating From a Set

Elements of a set are returned like enumerables to which you can apply iterative methods.

course.students.each do |student|
  puts student.last_name
end
# => Festin
#    Po

course.students.map(&:first_name)
# => ["Adelen", "Victoria"]
c. Removing From a Set
  • index starts at 1

Proper way:

Usage of the delete method on the set to actually remove the element from the set.

course.students.delete(course.students[2])

Improper way:

Deleting the object itself.

student = course.students[1]
student.delete

Doing so would result to having the deleted element still part of the list

course.students.count
# count would still include deleted element

But accessing the set element would result to nil:

course.students[1]

course.students.map(&:id)
# [.., nil, ..]

iii. list

class PreenlistmentList
  reference :course, :Course
  list :students, :Student
end

taekwondo = Course.create(name: 'PE 2 TKD')

student1 = Student.create(student_number: '2010-00033')
student2 = Student.create(student_number: '2010-18415')
student3 = Student.create(student_number: '2010-30011')

pl = PreenlistmentList.create(course: taekwondo)

# Pushes element to end of list
pl.students.push(student1)
pl.save # necessary for list to persist
pl.students.size

# Places element to front of list
pl.students.unshift(student2)
pl.save
pl.students.size


# Push student3 twice
pl.students.push(student3)
pl.save
pl.students.size
# => 3

pl.students.push(student3)
pl.save
pl.students.size
# => 4

# Deletes all occurences of student3
pl.students.delete(student3)

pl.students.size
# 2

iv. counter

Just like a regular attribute but direct manipulation and assignment is not allowed. Can only increment or decrement.

Course[1].incr(:waitlist) # 0 + 1
# => 1

course = Course[1]
course.incr(:waitlist) # 1 + 1
course.waitlist
# => 2

course.decr(:waitlist) # 2 - 1
# => 1

For multiple attributes, you may all increase and/or decrease in one line by separating the attributes with a comma.

course.decr(:waitlist, :drops)

v. reference

Reference to another model; similar to a foreign key.

ph108 = Classroom.create(name: 'Palma Hall, Room 108')

course = Course.create(name: 'Geog 1', classroom: ph108)

course.classroom
# <Classroom:0x007f8dc9f2bb70 @attributes= ...

course.classroom.name
# "Palma Hall, Room 108"

vi. collection

A shortcut accessor to search for all models that reference the current model. Returns an enumerable

Classroom[1].courses

Classroom[1].courses.count

VI. CRUD Operations

A. Creating Records

i. Immediate Create
course = Course.create name: 'Math 17'

course.id
# => "1"

course.name
# => "Math 17"

Course[1]
ii. Initialize and Save
another_course = Course.new name: 'CS 11'

another_course.name
# => "CS 11"

another_course.save

B. Reading / Looking up a record

i. By Index
course = Course[1]
course.name
# => "Math 17"
ii. By Query (id)
course = Course.find(id: 1).first
course.name
# => "Math 17"
iii. By Query (attributes)
course = Course.find(name: 'Math 17').first
course.name
# => "Math 17"

C. Updating Records

course = Course.find(name: 'Math 17').first
course.id
# => 1

course.update(name: "Math 53")
course.name
# => "Math 53"

course.id
# => 1

D.Deleting Records

i. Direct Access
Course[1].delete
ii. Prior Assignment
course = Course[1]
course.delete

VII. Filtering

A. Single Attribute

Course.find(domain: 'Math')

B. Multiple Attribute

Course.find(domain: 'English', waitlist: 0)

C. Multiple Values for an Attribute

# Find all courses with waitlist count = 0 and
# has domains of either Math or English

Course.find(waitlist: 0).combine(domain: ["Math", "English"])

D. With Exceptions

# Find all courses under the Math domain except
# for courses with Math 2 as the name

Course.find(domain: "Math").except(name: "Math 2")

VIII. Indices

Adding indices to models would allow you to execute find operations on the indexed attributes.

class People < Ohm::Model
  attribute :name
  attribute :gender

  index :name

end

Valid find lookups:

People.find(name: 'John Doe')

Invalid find lookups:

People.find(gender: 'Female')
# => Ohm::IndexNotFound: Ohm::IndexNotFound

# To fix: add gender to the index list
# index :name, :gender

IX. Sorting

All Sets can be sorted with sort which by default sorts by ID but can be overriden when passed with the by parameter.

On Ohm Version 2.3.0, you can only sort by numeric fields else a runtime error or an unsorted output may result

A. By

Indicate the attribute to which sorting will be based on.

For accuracy, use sort_by

 courses = Course.all.sort_by(:waitlist)

 courses.map(&:waitlist)
 => [0, 0, 1]
B. Order
Course.all.sort_by(:waitlist, order: 'ASC').map(&:waitlist)
# => [0, 0, 1]

Course.all.sort_by(:waitlist, order: 'DESC').map(&:waitlist)
# => [1, 0, 0]

B. Limit

Course.all.sort(limit: [1, 2])
# Gets 2 entries starting from offset 1

Course.all.sort(limit: [0, 1])
# Gets the first entry

X. Uniqueness

class Room < Ohm::Model
  attribute :name
  attribute :building
  unique :name
end

Room.create(name: 'Rm 180', building: 'Palma Hall')
# success

Room.create(name: 'Rm 180', building: 'DCS')
# Ohm::UniqueIndexViolation: UniqueIndexViolation: name

PostgreSQL 101: Getting Started! (Part 1)

PostgreSQL

An object-relational database system

I. Installation

A. Mac OSX:

brew install postgresql

B. Ubuntu

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

II. Console Commands

A. Connecting to PostgreSQL Server

To connect to the PostgreSQL server with as user postgres:

psql -U postgres

By default, psql connects to a PostgreSQL server running on localhost at port 5432. To connect to a different port and/or host. Add the -p and -h tag:

psql -U postgres -p 12345 -h 192.32.123.32

Once in, you may navigate via the following commands:

  • \l – list databases
  • \c – change databases
  • \d – list tables
  • \df – list functions
  • \df – list functions with definitions
  • \q – quit

III. Database Creation

CREATE DATABASE < database name >;

# Creates database with name: test_db
CREATE DATABASE test_db

IV. Database Drop

DROP DATABASE < database name >;

 # Drops database with name: test_db
DROP DATABASE test_db

V. Table Creation

CREATE TABLE programs(
  programid SERIAL PRIMARY KEY,
  degree CHARACTER VARYING,
  program CHARACTER VARYING
);

CREATE TABLE students(
  studentid SERIAL PRIMARY KEY,
  student_number CHARACTER VARYING UNIQUE,
  first_name CHARACTER VARYING,
  last_name CHARACTER VARYING,
  programid INTEGER REFERENCES programs,
  insertedon TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

A. Column Data Types

  • SERIAL
  • CHARACTER VARYING
  • CHARACTER(10)
  • INTEGER
  • TIMESTAMP WITHOUT TIME ZONE

B. Common Added Options

  • PRIMARY KEY
  • UNIQUE
  • DEFAULT

VI. CRUD Operations

A. Insertion of Rows

Template:

INSERT INTO table_name(column1, column2, column3...)
VALUES(value1, value2, value3...);

Sample:

INSERT INTO programs(degree, program)
VALUES('BS', 'Computer Science');

INSERT INTO programs(degree, program)
VALUES('BS', 'Business Administration and Accountancy');

INSERT INTO students(student_number, first_name, last_name, programid)
VALUES('2010-00031', 'Juan', 'Cruz', 1);

INSERT INTO students(student_number, first_name, last_name, programid)
VALUES('2010-00032', 'Pedro', 'Santos', 2);

B. Read/Lookup of Row

i. Get All Rows

SELECT * FROM students;

ii. Get Rows Satisfying Certain Conditions

# Gets row/s with studentid = 1

SELECT * FROM students where studentid = 1;

# Gets row/s where the last_name starts with 'cru' (non case sensitive)

SELECT * FROM students where last_name ilike 'cru%';

# Gets row/s where the student_number column is either 2010-0033, '2010-30011', or '2010-18415'

SELECT * FROM students where student_number in ('2010-00033', '2010-30011', '2010-18415');

iii. Get Specific Columns from Resulting Rows

# Selects the lastname and firstname from the students table

SELECT last_name, firstname from students;

# Selects the program column from rows of the programs table satisfying the condition and then prepending the given string

SELECT 'BUSINESS PROGRAM: ' || program from programs where program ilike '%business%';

C. Update of Row

i. Update all Rows

UPDATE students SET last_name = 'Cruz';

ii. Update Rows Satisfying Conditions

UPDATE students SET last_name = 'Santos' where studentid = 1;

UPDATE programs SET degree = 'BA' where programid NOT IN (2);

D. Deletion of Row

i. Delete all Rows

 DELETE FROM students

ii. Delete Rows Satisfying Conditions

DELETE FROM students WHERE studentid NOT IN (1,2)

VII. Queries

A. Joins

i. Inner Join

Syntax:
SELECT * FROM table_1 JOIN table_2 using (common_column_name);
Example:
SELECT student_number, program FROM students JOIN programs using (programid);

ii. Left Join

Syntax:
SELECT * FROM table_1 LEFT JOIN table_2 on table_1.column_name = table_2.column_name;
Example:

We insert a student row without a program

INSERT INTO students(student_number, first_name, last_name)
VALUES('2010-35007', 'Juana', 'Change');

Doing a left join would still return the recently inserted row but with empty Programs-related fields.

SELECT * FROM students LEFT join programs on students.programid = programs.programid;

iii. Right Join

Syntax:
SELECT * FROM table_1 RIGHT JOIN table_2 on table_1.column_name = table_2.column_name;
Example:

We insert a program row without any students attached

INSERT INTO programs(degree, program)
VALUES('BS', 'Information Technology');

Doing a right join would still return the recently inserted row but with empty Students-related fields.

SELECT * FROM students RIGHT join programs on students.programid = programs.programid;

B.Where

Specify conditions by which rows from the query will be filtered.

SELECT * from students where programid IS NOT NULL;

C. Group By

Allows use of aggregate functions with the attributes provided to the GROUP BY clause as basis for aggregations

SELECT program, COUNT(*) FROM students
JOIN programs USING (programid) GROUP BY program;

Above example counts students per program.

D. Having

Similar to WHERE but applies the condition to the groups produced with GROUP BY.

SELECT program, COUNT(*) FROM students
JOIN programs USING (programid) GROUP BY program HAVING COUNT(*) > 1;

E. Union

Joins resulting datasets from multiple queries.

select * from students where programid in (1, 2)

UNION

select * from students;

MongoDB 101: A Starter Guide

MongoDB

Open source document database

I. Definitions

A. Document

  • Represent one record in MongoDB; consists of key-value pairs.

  • Similar to JSON Objects

  • Values may include other documents, arrays, or arrays of documents

     {
         "_id" : ObjectId("54c955492b7c8eb21818bd09"),
         "student_number" : "2010-30010",
         "last_name" : "Dela Cruz",
         "first_name" : "Juan",
         "middle_name" : "Masipag",
         "address" : {
             "street" : "#35 Maharlika St.",
             "zipcode" : "30011",
             "city" : "Quezon City",
             "coord" : [ -73.9557413, 40.7720266 ]
         },
         "gwa" : "1.75",
         "program" : "Computer Science",
         "degree" : "Bachelor of Science"
     }
    

B. Collection

Documents are stored in collections. They are similar to tables but unlike a table, a collection does not require documents to have the same schema.

Documents stored in a collection has a unique identifier _id that acts as the primary key.

II. Installation

A. Mac OSx

brew install mongodb

B. Ubuntu

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv EA312927

echo "deb http://repo.mongodb.org/apt/ubuntu trusty/mongodb-org/3.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-3.2.list

sudo apt-get update

sudo apt-get install -y mongodb-org

III. Setup

A. Running the Database

By default, mongod looks for your database at /data/db

 mongod

In case your database is at a different path, provide the –dbpath parameter

 mongod --dbpath .

B. Running the Console

 mongo

By default, Mongo console would connect to localhost at port 27017. Make sure that mongod is running before you issue the mongo command.

C. Switching database

use mongo-cheatsheet
# switched to db mongo-cheatsheet; would be created if non existent

III. CRUD Operations

A. Insertion

db.students.insert(
  {
     "student_number" : "2010-30010",
     "last_name" : "Dela Cruz",
     "first_name" : "Juan",
     "middle_name" : "Masipag",
     "address" : {
       "street" : "#35 Maharlika St.",
       "zipcode" : "30011",
       "city" : "Quezon City",
       "coord" : [ -73.9557413, 40.7720266 ]
     },
     "gwa" : 1.75,
     "course" : "BS Computer Science"
  })
  
 # => WriteResult({ "nInserted" : 1 })
 
 # _id is automatically asssigned

B. Read or Lookup

# Find student with student_number 2010-30010
db.students.find( { "student_number": "2010-30010" } )

# Find student with zipcode (embedded attribute) 30011
db.students.find( { "address.zipcode": "30011" } )

# Find students with the gwa column greater than 1.25
db.students.find( { "gwa": { $gt: 1.25 } } )

# Find students with the gwa column less than 1.25 and course is BS Computer Science
db.students.find( { "gwa": { $lt: 1.25 } , "course": "BS Computer Science"} )

# Find students with the gwa column less than 1.25 or course is Computer Science
db.students.find( { $or: [{ "gwa": { $lt: 1.25 } } , {"course": "BS Computer Science"}]})

C. Update

i. Update Attribute/s

Updates first matching document with first_name: Juan

db.students.update(
    { "first_name" : "Juan" },
    {
        $set: { "first_name": "Juana" }
    }
)

The following code snipper updates the first matching document with first name Juan. And also sets the field lastModified to true (since it is non existent on the first run based on our schema, it will be created and set.)

db.students.update(
    { "first_name" : "Juan" },
    {
        $set: { "first_name": "Juana" },
        $currentDate: { "lastModified": true }
    }
)

ii. Update Embedded Fields

db.students.update(
    { "first_name" : "Juana" },
    { $set: { "address.street": "#45 Maginhawa St.",
              "address.city": "Quezon City" }}
)

iii. Updating All Matching Documents

By default, update only updates the first matching document. To tell MongoDB to update all matching, we pass multi: true

db.students.update(
  { "first_name" : "Juan" },
  { $set: { "address.street": "East 31st Street" } },
  { multi: true}
)
# WriteResult({ "nMatched" : 2, "nUpserted" : 0, "nModified" : 2 })

iv. Replace

db.students.update(
    { "first_name" : "Juan" },
    {
        "first_name" : "Victoria",
        "address" : {
            "street" : "Emerson Subdivision",
            "city" : "Saog, Marilao"}
    }
)

If you want to insert in case the data is non-existent, pass upsert: true as well to the update call.

{ upsert: true }

D. Delete

i. Delete a document

db.students.remove( { "first_name": "Juan" } )
# removes all document

db.students.remove( { "first_name": "Victoria" }, { justOne: true } )
# removes only one of the matching document     

ii. Drop a Collection

db.students.drop
# => true

IV. Query

In addition to simple lookup commands, you can also use aggregation:

db.students.aggregate(
[
 { $group: { "_id": "$address.city", "count": { $sum: 1 } } }
]
);

Other available operators:

  • sort
  • project
  • and many moreā€¦

V. Data Import

A. Import from JSON, CSV, TSV

To import dataset from a JSON, CSV, or TSV

mongoimport --db mongo-cheatsheet --collection students --drop --file primer-dataset.json

Where:

  • Database name: mongo-cheatsheet
  • Collection name: students
  • Source File: primer-dataset.json

By default, connects to localhost:27017. If you wish to connect to other ports, add flags: –host and –port

mongoimport --db mongo-cheatsheet --collection students --drop --file primer-dataset.json --host 192.168.123.321 --port 27019

B. Restore from Mongo Backup

To restore from a mongoDB backup:

mongorestore --drop --db mongo-cheatsheet /path/to/your/dump

Where:

  • Database name: mongo-cheatsheet
  • Dump path: /path/to/your/dump

C. Backup a Mongo Database

mongodump --db mongo-cheatsheet
  • Database name: mongo-cheatsheet