Active Records Venkat Subramaniam svenkatcs uh edu 1

Active Records Venkat Subramaniam – svenkat@cs. uh. edu 1

What’s Active Records? • O-R Mapping layer • To make database access almost a non-issue • Relies heavily on convention over configuration – some say this is simple, others say this is simplistic – Tables map to classes – Rows map to objects – Columns map to attributes • You write minimal code – Quite a bit of code is synthesized behind the scene Venkat Subramaniam – svenkat@cs. uh. edu 2

Accessing Data • You derive your class from Active. Record: : Base • That’s almost all you need • You are provided with methods you can readily use • Attributes are inferred based on column names in schema – You may have additional attributes • Like having a clear text password in memory while the password column is encrypted • Has primary key id by convention Venkat Subramaniam – svenkat@cs. uh. edu 3

Locking • Supports Pessimistic or Optimistic Locking • Optimistic if your table has an integer column named lock_version – Active Records take care of rest • You can control this by Active. Record: : Base. lock_optimistically = false Venkat Subramaniam – svenkat@cs. uh. edu 4

Classes to Tables • Active Record assumes the table name is plural form of your model class – Multiword class name transorms to words separated by underscores – Controlled by global glag in environment. rb • Active. Record: : Base. pluralize_table_names = false – Common plurans and then some weird ones • people (Person), journals (Journal), children (Child), dear_friends (Dear. Friend) • You can break away from the convention (for legacy database) using set_table directive Venkat Subramaniam – svenkat@cs. uh. edu 5

My. Sql • Create mysql database – mysql – create database csalum_development; • Create a create. sql script for creating table • Run script – mysql –p –r root csalum_development < create. sql Venkat Subramaniam – svenkat@cs. uh. edu 6

A Model Class Venkat Subramaniam – svenkat@cs. uh. edu 7

Exploring Active Records • Columns() method tells us Venkat Subramaniam – svenkat@cs. uh. edu 8

SQL Type to Ruby Type Mapping • • Standard SQL to Ruby type mapping int, integer -> Fixnum decimal, numeric, float, double -> Float interval, date -> Date clob, blob, text, char, varchar, string -> String datetime, time -> Time Money? – decimal to float may lead to rounding errors – For currency you may use • units of cents • aggregate Money objects Venkat Subramaniam – svenkat@cs. uh. edu 9

Accessing Attributes • Active Records converts column values to appropriate Ruby types • You can get raw value of an attribute as well by appending attribute name with _before_type_cast • Use caution with boolean types – No consistent represenation in databases – Use the ? form of method to get correct value – Still poses problems for i 18 n Venkat Subramaniam – svenkat@cs. uh. edu 10

CRUD • Active Records is based on the simple notion that you mostly need basic operations on tables – Create, Read, Update, and Delete • Methods for these are synthesized behind the scene – new – finders – save – update – Delete Venkat Subramaniam – svenkat@cs. uh. edu 11

Creating A Row • Use new to create object • Remember to save Venkat Subramaniam – svenkat@cs. uh. edu 12

Ways to create Venkat Subramaniam – svenkat@cs. uh. edu 13

create() Method • Combines new and save method • create(hash) => object • create(array_of_hash) => objects Venkat Subramaniam – svenkat@cs. uh. edu 14

Reading • find takes primary key or an array of primary keys • Gets fancier than that as well – Can take other parameters • : first specified to return first matching row • : all specified to return all matching rows • : condition helps send parameters to SQL where clause Venkat Subramaniam – svenkat@cs. uh. edu 15

Reading No guarantee on ordering unless you specify order by Venkat Subramaniam – svenkat@cs. uh. edu 16

Specifying Criteria • Unsafe way – SQL Injection problem – Don’t try this at home Venkat Subramaniam – svenkat@cs. uh. edu 17

Specifying Criteria – Better Ways Venkat Subramaniam – svenkat@cs. uh. edu 18

Specialized finds • You can search based on column values • Methods synthesized for you Venkat Subramaniam – svenkat@cs. uh. edu 19

Update/Save • Save updates existing row or inserts a new row • save returns true if model is valid and can be saved • save! raises a Record. Invalid exception if objects can’t be saved • You may also use update_attribute or update_attributes • Class method update allows you to update a row without reading it • update_all is like SQL update with SET and WHERE clause Venkat Subramaniam – svenkat@cs. uh. edu 20

Deleting • destroy allows you to delete a row based on id or condition • Once deleted, object in memory is frozen • destroy_all is a classes methods that destroys all objects that meet given condition Venkat Subramaniam – svenkat@cs. uh. edu 21