Relational Dabatases

Relational Dabatases

Three Tier Architecture

RDBMS • Relational Database Management Systems • A way of saving and accessing data on persistent (disk) storage.

Why Use an RDBMS

Relational Model • First published by Edgar F. Codd in 1970 • Received Turing Award in 1981 • A relational database consists of a collection of tables • A table consists of rows and columns • Each row represents a record • Each column represents an attribute of the records contained in the table

Example

Structured Query Language (SQL) • Queries select c1.name as 'from', c2.name as 'to', t.time, f.date, f.available from flight f, timetable t, campus c1, campus c2 where f.timetable_id = t.id and t.leavingfrom = c1.id and t.goingto = c2.id; • Data manipulation insert into flight (timetable_id, date, available) values (1, current_date(),3) update flight set available = 2 where id = 3 delete from flight

client client Server disk i/o server process Client/Server Databases client processes tcp/ip connections

API tcp/ip connection to server db library Inside the Client Process client application code

CodeIgniter DB API • Implemented by DB.php library • CODEIGNITER_ROOT/core/database • Configuration • CODEIGNITER_ROOT/application/config/database.php $db['default']['hostname'] = ’bungle07.cs.toronto.edu'; $db['default']['username'] = 'c2delara'; $db['default']['password'] = '123456789'; $db['default']['database'] = ’uofttheater'; $db['default']['dbdriver'] = 'mysql’; • Loading • CODEIGNITER_ROOT/application/config/autoload.php $autoload['libraries'] = array('database’); • Controller function $this->load->database();

Running Queries • $query = $this->db->query('YOUR QUERY HERE'); • Return values • Read queries  false if failure, result object if success • Write queries  false if failure, true if success • Error handling • $this->db->_error_message(); • $this->db->_error_number();

Processing Results • $query->num_rows()  num of rows returned by query • $query->result()  array of objects • $query->row(n)  a specific object for the nthtuple • $query->result(Class)  array of objects of type Class • $query->row(n, Class)  a specific object for the nthtuple • $query->result_array()  result as pure array • $query->row_array(n)  an array for the nthtuple • $query->list_fields()  array with field names • $query->field_data()  array with field info (name, max_length, primary_key, type)

Active Data Objects • Pattern that allows information to be retrieved, inserted, and updated with minimal scripting. • Database independent • Query syntax is generated by db adapter • Safer queries • Values automatically escaped

Active Data Objects • Read $query = $this->db->get('mytable');// Produces: SELECT * FROM mytable Modifiers: $this->db->where(), $this->db->like(), $this->db->join(), $this->db->group_by() ,…… • Update $data = array( 'title' => 'My title' , 'name' => 'My Name’ ); $this->db->insert('mytable', $data); // Produces: INSERT INTO mytable (title, name) VALUES ('My title', 'My name’) $this->db->delete('mytable', array('id' => $id)); $this->db->update('mytable', $data, array('id' => $id));

CRUD Pattern • Pattern for operating on db tables • CodeIgniter URL mapping: server_name/app_name/controller_name/create server_name/app_name/controller_name/read/id server_name/app_name/controller_name/update/id server_name/app_name/controller_name/delete/id

Transactions • Atomic changes to multiple db records • Failures • Concurrency control $this->db->trans_begin(); … lots of updates … if ($this->db->trans_status() == FALSE) { $this->db->trans_rollback(); } else { $this->db->trans_commit(); }

Relational Dabatases