Source for file TeraWurflDatabase_MySQL4.php
Documentation is available at TeraWurflDatabase_MySQL4.php
* Tera_WURFL - PHP MySQL driven WURFL
* Tera-WURFL was written by Steve Kamerman, and is based on the
* Java WURFL Evolution package by Luca Passani and WURFL PHP Tools by Andrea Trassati.
* This version uses a MySQL database to store the entire WURFL file, multiple patch
* files, and a persistent caching mechanism to provide extreme performance increases.
* @package TeraWurflDatabase
* @author Steve Kamerman <stevekamerman AT gmail.com>
* @version Stable 2.1.3 $Date: 2010/09/18 15:43:21
* @license http://www.mozilla.org/MPL/ MPL Vesion 1.1
* Provides connectivity from Tera-WURFL to MySQL 4. This connector is also compatible with MySQL 5.
* @package TeraWurflDatabase
* The maximum number of new rows that the database can handle in one INSERT statement
protected static $DB_MAX_INSERTS = 500;
* Destructor, disconnect from database
// Device Table Functions (device,hybrid,patch)
$res = $this->dbcon->query("SELECT * FROM `". TeraWurflConfig::$TABLE_PREFIX. 'Merge'. "` WHERE `deviceID`=". $this->SQLPrep($wurflID)) or die($this->dbcon->error);
throw new Exception("Tried to lookup an invalid WURFL Device ID: $wurflID");
$data = $res->fetch_assoc();
return WurflConstants::$GENERIC;
if($device['actual_device_root']){
$res = $this->dbcon->query("SELECT `deviceID`, `user_agent` FROM `$tablename` WHERE `match`=1");
while($row = $res->fetch_assoc()){
$data[$row['deviceID']]= $row['user_agent'];
$query = "SELECT `deviceID` FROM `". TeraWurflConfig::$TABLE_PREFIX. 'Merge'. "` WHERE `user_agent`=". $this->SQLPrep($userAgent);
$res = $this->dbcon->query($query);
$data = $res->fetch_assoc();
return $data['deviceID'];
$insert_errors = array();
foreach($tables as $table => $devices){
// insert records into a new temp table until we know everything is OK
$temptable = $table . (self::$DB_TEMP_EXT);
$parts = explode('_',$table);
foreach($devices as $device){
$this->dbcon->query("INSERT INTO `". TeraWurflConfig::$TABLE_PREFIX. 'Index'. "` (`deviceID`,`matcher`) VALUE (". $this->SQLPrep($device['id']). ",". $this->SQLPrep($matcher). ")");
// convert device root to tinyint format (0|1) for db
if(strlen($device['user_agent']) > 255){
$insert_errors[] = "Warning: user agent too long: \"". ($device['id']). '"';
$insertcache[] = sprintf("(%s,%s,%s,%s,%s,%s)",
$this->SQLPrep($device['user_agent']),
$this->SQLPrep($device['fall_back']),
$this->SQLPrep((isset ($device['actual_device_root']))? $device['actual_device_root']: ''),
preg_match('/^DO_NOT_MATCH/',$device['user_agent'])? '0': '1',
// This batch of records is ready to be inserted
if(count($insertcache) >= self::$DB_MAX_INSERTS){
$query = "INSERT INTO `$temptable` (`deviceID`, `user_agent`, `fall_back`, `actual_device_root`, `match`, `capabilities`) VALUES ". implode(",",$insertcache);
$this->dbcon->query($query) or $insert_errors[] = "DB server reported error on id \"". $device['id']. "\": ". $this->dbcon->error;
$insertedrows += $this->dbcon->affected_rows;
// some records are probably left in the insertcache
if(count($insertcache) > 0){
$query = "INSERT INTO `$temptable` (`deviceID`, `user_agent`, `fall_back`, `actual_device_root`, `match`, `capabilities`) VALUES ". implode(",",$insertcache);
$this->dbcon->query($query) or $insert_errors[] = "DB server reported error on id \"". $device['id']. "\": ". $this->dbcon->error;
$insertedrows += $this->dbcon->affected_rows;
if(count($insert_errors) > 0){
// leave the temp table in the DB for manual inspection
$this->dbcon->query("DROP TABLE IF EXISTS `$table`");
$this->dbcon->query("RENAME TABLE `$temptable` TO `$table`");
* Drops and creates the given device table
* @param string Table name (ex: TeraWurflConfig::$HYBRID)
* @return boolean success
$droptable = "DROP TABLE IF EXISTS ". $tablename;
$createtable = "CREATE TABLE `". $tablename. "` (
`deviceID` varchar(64) binary NOT NULL default '',
`user_agent` varchar(255) default NULL,
`fall_back` varchar(64) default NULL,
`actual_device_root` tinyint(1) default '0',
`match` tinyint(1) default '1',
`capabilities` mediumtext,
PRIMARY KEY (`deviceID`),
KEY `fallback` (`fall_back`),
KEY `useragent` (`user_agent`),
KEY `dev_root` (`actual_device_root`),
$this->dbcon->query($droptable);
$this->dbcon->query($createtable);
* Drops then creates all the UserAgentMatcher device tables
* @return boolean success
$table = TeraWurflConfig::$TABLE_PREFIX. "_". $matcher;
* Drops and creates the MERGE table
* @param array Table names
* @return boolean success
foreach($tables as &$table){$table= "SELECT * FROM `$table`";}
$droptable = "DROP TABLE IF EXISTS ". $tablename;
$createtable = "INSERT INTO `$tablename` ". implode(" UNION ALL ",$tables);
$this->dbcon->query($createtable) or die("ERROR: ". $this->dbcon->error);
* Drops and creates the index table
* @return boolean success
$droptable = "DROP TABLE IF EXISTS ". $tablename;
$createtable = "CREATE TABLE `". $tablename. "` (
`deviceID` varchar(64) binary NOT NULL default '',
`matcher` varchar(64) NOT NULL,
$this->dbcon->query($droptable);
$this->dbcon->query($createtable);
* Creates the settings table if it does not already exist
* @return boolean success
$checktable = "SHOW TABLES LIKE '$tablename'";
$res = $this->dbcon->query($checktable);
if($res->num_rows > 0) return true;
$createtable = "CREATE TABLE `". $tablename. "` (
`id` varchar(64) NOT NULL,
`value` varchar(255) DEFAULT NULL,
$this->dbcon->query($createtable);
// should return (bool)false or the device array
$res = $this->dbcon->query("SELECT * FROM `$tablename` WHERE `user_agent`=". $this->SQLPrep($userAgent)) or die("Error: ". $this->dbcon->error);
//echo "[[UA NOT FOUND IN CACHE: $userAgent]]";
$data = $res->fetch_assoc();
$packed_device = $this->SQLPrep(serialize($device));
$this->dbcon->query("INSERT DELAYED INTO `$tablename` (`user_agent`,`cache_data`) VALUES ($ua,$packed_device)");
if($this->dbcon->affected_rows > 0){
$droptable = "DROP TABLE IF EXISTS `$tablename`";
$createtable = "CREATE TABLE `$tablename` (
`user_agent` varchar(255) binary NOT NULL default '',
`cache_data` mediumtext NOT NULL,
PRIMARY KEY (`user_agent`)
$this->dbcon->query($droptable);
$this->dbcon->query($createtable);
$droptable = "DROP TABLE IF EXISTS `$tablename`";
$createtable = "CREATE TABLE `$tablename` (
`user_agent` varchar(255) binary NOT NULL default '',
`cache_data` mediumtext NOT NULL,
PRIMARY KEY (`user_agent`)
$this->dbcon->query($droptable);
$this->dbcon->query($createtable);
// We'll use this instance to rebuild the cache and to facilitate logging
$temptable = TeraWurflConfig::$TABLE_PREFIX. 'Cache'. self::$DB_TEMP_EXT;
$checkcachequery = "SHOW TABLES LIKE '$cachetable'";
$checkres = $this->dbcon->query($checkcachequery);
if($checkres->num_rows === 0){
// This can only happen if the table doesn't exist
// This table must be empty, so we're finished
// $rebuilder->toLog($query,LOG_ERR,"rebuildCacheTable");
$rebuilder->toLog("Created empty cache table",LOG_NOTICE,"rebuildCacheTable");
$droptemptable = "DROP TABLE IF EXISTS `$temptable`";
$this->dbcon->query($droptemptable);
$query = "RENAME TABLE `$cachetable` TO `$temptable`";
$this->dbcon->query($query);
$query = "SELECT `user_agent` FROM `$temptable`";
$res = $this->dbcon->query($query);
// No records in cache table == nothing to rebuild
$rebuilder->toLog("Rebuilt cache table, existing table was empty - this is very unusual.",LOG_WARNING,"rebuildCacheTable");
while($dev = $res->fetch_assoc()){
// Just looking the device up will force it to be cached
$rebuilder->GetDeviceCapabilitiesFromAgent($dev['user_agent']);
// Reset the number of queries since we're not going to re-instantiate the object
$rebuilder->db->numQueries = 0;
$droptable = "DROP TABLE IF EXISTS `$temptable`";
$this->dbcon->query($droptable);
$rebuilder->toLog("Rebuilt cache table.",LOG_NOTICE,"rebuildCacheTable");
// Supporting DB Functions
// truncate or drop+create given table
* Establishes connection to database (does not check for DB sanity)
$this->dbcon = @new mysqli($host,TeraWurflConfig::$DB_USER,TeraWurflConfig::$DB_PASS,TeraWurflConfig::$DB_SCHEMA,$port);
$this->dbcon = @new mysqli(TeraWurflConfig::$DB_HOST,TeraWurflConfig::$DB_USER,TeraWurflConfig::$DB_PASS,TeraWurflConfig::$DB_SCHEMA);
if(mysqli_connect_errno()){
$query = sprintf("REPLACE INTO `%s` (`%s`, `%s`) VALUES (%s, %s)", $tablename, 'id', 'value', $this->SQLPrep($key), $this->SQLPrep($value));
$this->dbcon->query($query);
$query = "SELECT `value` FROM `". TeraWurflConfig::$TABLE_PREFIX. 'Settings'. "` WHERE `id` = ". $this->SQLPrep($key);
$res = $this->dbcon->query($query);
if($res->num_rows == 0) return null;
$row = $res->fetch_assoc();
// prep raw text for use in queries (adding quotes if necessary)
if($value == '') $value = 'NULL';
else if (!is_numeric($value) || $value[0] == '0') $value = "'" . $this->dbcon->real_escape_string($value) . "'"; //Quote if not integer
$tablesres = $this->dbcon->query("SHOW TABLES");
while($table = $tablesres->fetch_row())$tables[]= $table[0];
while($table = $tablesres->fetch_row())$tables[]= $table[0];
$fieldsres = $this->dbcon->query("SHOW COLUMNS FROM ". $table);
while($row = $fieldsres->fetch_assoc()){
$fields[] = 'CHAR_LENGTH(`'. $row['Field']. '`)';
$fieldnames[]= $row['Field'];
$bytesizequery = "SUM(". implode('+',$fields). ") AS `bytesize`";
$query = "SELECT COUNT(*) AS `rowcount`, $bytesizequery FROM `$table`";
$res = $this->dbcon->query($query);
$rows = $res->fetch_assoc();
$stats['rows'] = $rows['rowcount'];
$stats['bytesize'] = $rows['bytesize'];
if(in_array("actual_device_root",$fieldnames)){
$res = $this->dbcon->query("SELECT COUNT(*) AS `devcount` FROM `$table` WHERE actual_device_root=1");
$row = $res->fetch_assoc();
$stats['actual_devices'] = $row['devcount'];
$cacheres = $this->dbcon->query("SELECT user_agent FROM ". TeraWurflConfig::$TABLE_PREFIX. 'Cache'. " ORDER BY user_agent");
while($ua = $cacheres->fetch_row())$uas[]= $ua[0];
$res = $this->dbcon->query("SELECT version() AS `version`");
if(!$res || $res->num_rows == 0) return '[not connected]';
$row = $res->fetch_assoc();
|