About the Speaker

• From the Czech republic
• Works in PHP since 2000
• Self-employed programmer and teacher
• Co-author of official PHP Manual
• Author of popular Czech blog about PHP
• Author of book 1001 tips and tricks about PHP (Czech)

NotORM Philosophy

• Do you know SimpleXML? You can access XML document …

  <rss version="0.91">
  	<channel>
  		<item>
  			<title>Kde zachytávat výjimky</title>
  			<link>http://php.vrana.cz/kde-zachytavat-vyjimky.php</link>
  		</item>
  		<item>
  			<title>Kde zachytáváte výjimky?</title>
  			<link>http://php.vrana.cz/kde-zachytavate-vyjimky.php</link>
  		</item>
  		<item>
  			<title>Jak je to s těmi chybami?</title>
  			<link>http://php.vrana.cz/jak-je-to-s-temi-chybami.php</link>
  		</item>
  	</channel>
  </rss>
  

• … with a simple PHP code

  $rss = simplexml_load_file($filename);
  
  echo $rss["version"] . "\n";
  foreach ($rss->channel->item as $item) {
  	echo $item->title . "\n";
  }
  

  • -> to access inside elements
  • foreach to iterate through elements
  • $elements[3] to access element by its order
  • $element["attribute"] to access attributes
• NotORM is similar – you can access the database …

  

• … with a simple PHP code

  $db = new NotORM($pdo);
  
  foreach ($db->application() as $application) { // get all applications
      echo $application["title"] . "\n"; // print application title
  	echo $application->author["name"] . "\n"; // get name of the application author
  }
  

  • -> to access tables
  • foreach to iterate through rows
  • $table[3] to access a row by ID
  • $row["column"] to access a value

What is NotORM?

Easy and efficient access to database data

Uses PDO (tested with MySQL, SQLite, PostgreSQL, MS SQL, Oracle)

Free even for commercial use (Apache license)

www.notorm.com

Live demo

• Database structure
• PHP code
• Result

Convenient API

Database

• $db = new NotORM($pdo[, $structure[, $cache]])
  • Get database representation

Result

• $table = $db->$tableName([$where[, $parameters[, ...]]])
  • Get representation of table $tableName
• $table->where($where[, $parameters[, ...]])
  • Set WHERE
• $table->order($columns)
  • Set ORDER BY, can be expression ("columm DESC, id DESC")
• $table->select($columns)
  • Set retrieved columns, can be expression ("col, MD5(col) AS hash")
• $table->limit($limit[, $offset])
  • Set LIMIT and OFFSET
• $table->group($functions[, $having])
  • Get result of aggregation functions as array ("COUNT(*), MAX(published)")
• count($table)
  • Get number of rows in result set
• (string) $table
  • Get SQL query

Row

• foreach ($table as $id => $row)
  • Iterate all rows in result
• $row = $db->$tableName[$id]
  • Get single row with ID $id from table $tableName
• $row = $table[$id]
  • Get row with ID $id from the result
• $row = $table->fetch()
  • Get next row from the result
• $data = $row[$column]
  • Get data from column $column
• $row2 = $row->$tableName
  • Get row from referenced table
• $table2 = $row->$tableName([$where[, $parameters[, ...]]])
  • Get result from referencing table
• (string) $row
  • Get primary key value

Live demo

• Access referencing tables

Efficiency

1. Constant number of queries
   • Only one query is executed to get each type of result

     Single query	Constant number	Multiple queries

   • If only one row is required then it is still possible to issue $db->table("id", $id)
2. Only required columns are transferred
   • Automatically selected if enabled
3. No caching involved, fast by its nature
   • Caching could mean stalled data
4. Best utilization of MySQL query cache
   • All results are deleted after update in any used table
   • NotORM uses single-table queries

Live demo

• Show executed queries
• Enable automatic selection of columns

Column Names

• No specific table or column names are required
• It is useful to respect any convention

  $structure = new NotORM_Structure_Convention(
      $primary = 'id',
      $foreign = '%s_id',
      $table = '%s'
  );
  

• Otherwise, it is possible to use own mapping or auto-discovery
  • Create an object implementing the NotORM_Structure interface
  • Use the NotORM_Structure_Discovery class (slow)

Persistency

• $id = $db->$tableName()->insert($array)
  • Insert row into table $tableName and return the auto increment value
• $id = $db->$tableName($array)
  • Shortcut for $db->$tableName()->insert($array)
• $affected = $table->update($array)
  • Update all rows in table result
• $affected = $table->delete()
  • Delete all rows in table result
• $affected = $row->update($array)
  • Update single row
• $affected = $row->update()
  • Update single row by real modifications
• $affected = $row->delete()
  • Delete single row
• $table->freeze()
  • Disable persistence for this result and its children

Available in separate branch

Cache

Stores only the information about used columns, no data

• Session – only for presentation purposes
• File – can be mapped to tmpfs
• Database – can use the Memory storage engine
• Memcache – usually on foreign server
• APC – not very stable yet

Weaknesses

• Filtering and accessing data from two tables requires one additional query in MySQL

  $db->product("available", true)
  	->where("category_id", $db->category("active", true))
  

  -- one query in most databases
  SELECT * FROM product WHERE available = 1 AND category_id IN (
  	SELECT id FROM category WHERE active = 1
  )
  
  -- two queries in MySQL for performance reasons
  

• Many-to-many relations would be usually faster with JOIN
  • Only if you don't need a column from the connecting table

Why NotORM?

• Selecting data in relationships is quite complex with SQL joins
  • Inserting, updating and deleting data is usually easy
• NotORM does not require defining any classes for data entities
  • Repetitive and boring task

Questions?

Thank you