/**
*
* execute a SQL query in the form SELECT $formula as Result
*
* @param model class $model
* @param string $formula A string that is a formula like 2+3
* @return mixed. The array of queries
*/
public function calculateViaSQL(Model $model, $formula)
{
$formula = "Select " . $formula . " as Result;";
$result = $this->dbo->fetchRow($formula);
if (empty($result)) {
return false;
} else {
return (string) $result[0]['Result'];
}
}
/**
* Creates XML from the ADODB record set
*
* @param object $rs - record set object
* @param bool $moveFirst - determines whether recordset is returned to first record
* @return string $xml - resulting xml
* @version V1.0 10 June 2006 (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved.
*
* Released under both BSD license and Lesser GPL library license. You can choose which license
* you prefer.
*/
function rs2xml($rs, $moveFirst = false)
{
if (!$rs) {
printf(ADODB_BAD_RS, 'rs2xml');
return false;
}
$xml = '';
$totalRows = 0;
$totalRows = $rs->numrows();
$domxml = new DOMDocument('1.0', 'utf-8');
$root = $domxml->appendChild($domxml->createElement('rows'));
$root->setAttribute('total-rows', $totalRows);
$row_count = 1;
while ($line = $rs->fetchRow()) {
$row = $root->appendChild($domxml->createElement('row'));
foreach ($line as $col_key => $col_val) {
$col = $row->appendChild($domxml->createElement('column'));
$col->setAttribute('name', strtolower($col_key));
$col->appendChild($domxml->createTextNode($col_val));
}
$row_count++;
}
$domxml->formatOutput = true;
$xml = $domxml->saveXML();
$domxml = null;
if ($moveFirst) {
$rs->MoveFirst();
}
return $xml;
}
/**
* Run a query and return a single row
*
* @param string $sql
* @return array
*/
function selectRow($sql)
{
$this->debug['queries']++;
$this->debug['actualQueries'][] = $sql;
$c = $this->db->fetchRow($sql);
return $c;
}
/**
* get the mysql variable
*
* @access public
* @param string name variable name
* @return string variable
*/
function getVariable($name)
{
$sql = 'SHOW VARIABLES LIKE \'' . $name . '\'';
$res = $this->_db->queryF($sql);
while (list($key, $value) = $this->_db->fetchRow($res)) {
$variable[$key] = $value;
}
return $variable[$name];
}
/**
* Read a session from the database
*
* @param string &sess_id ID of the session
*
* @return array Session data
*/
function read($sess_id)
{
$sql = sprintf('SELECT sess_data FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id));
if (false != ($result = $this->db->query($sql))) {
if (list($sess_data) = $this->db->fetchRow($result)) {
return $sess_data;
}
}
return '';
}
/**
* Load this object
* @since Version 3.10.0
* @return void;
*/
private function getFootage()
{
$data = $this->db->fetchRow("SELECT * FROM railcam_footage WHERE id = ?", $this->id);
$data['fileinfo'] = json_decode($data['fileinfo'], true);
$this->timeStamp = new DateTime($data['datestored']);
$this->setCamera(new Camera($data['railcam_id']));
$this->type = $data['type'];
$storageObject = new Storage($data['storage_id']);
$this->footageData = ["id" => $data['id'], "camera" => $this->cameraObject->getArray(), "type" => $this->type, "url" => ["original" => $storageObject->getWebUrl($data['fileinfo'])]];
return;
}
/**
* 获取所有的fields
*
* @access public
* @param string $table_name 数据表名
* @return array
*/
function get_fields($table_name)
{
$fields = array();
$result = $this->db->query("SHOW FIELDS FROM {$table_name}", 'SILENT');
if ($result) {
while ($row = $this->db->fetchRow($result)) {
$fields[] = $row['Field'];
}
}
return $fields;
}
/**
* Constructor
*
* @param array Options containing 'table', 'name'
*/
public function __construct(array $options = array())
{
// Initialize the options
$options = $this->_initialize($options);
// Mixin the KMixinClass
$this->mixin(new KMixinClass($this, 'Rowset'));
// Assign the classname with values from the config
$this->setClassName($options['name']);
// Set table object and class name
$this->_tableClass = 'com.' . $this->getClassName('prefix') . '.table.' . $this->getClassName('suffix');
$this->_table = isset($options['table']) ? $options['table'] : KFactory::get($this->_tableClass);
// Set the data
if (isset($options['data'])) {
$this->_data = $options['data'];
}
// Count the data
$this->resetCount();
// Instantiate an empty row to use for cloning later
$this->_emptyRow = $this->_table->fetchRow();
}
/**
* count objects matching a criteria
*
* @param object $criteria {@link CriteriaElement} to match
* @return int count of objects
* @access public
*/
function getCount($criteria = null)
{
$sql = 'SELECT COUNT(*) FROM ' . $this->_db->prefix($this->_dbtable);
if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
$sql .= ' ' . $criteria->renderWhere();
}
if (!($result =& $this->_db->query($sql))) {
return 0;
}
list($count) = $this->_db->fetchRow($result);
return $count;
}
/**
* Count the number of online users
*
* @param object $criteria {@link CriteriaElement}
*/
function getCount($criteria = null)
{
$sql = 'SELECT COUNT(*) FROM ' . $this->db->prefix('online');
if (is_object($criteria) && is_subclass_of($criteria, 'criteriaelement')) {
$sql .= ' ' . $criteria->renderWhere();
}
if (!($result = $this->db->query($sql))) {
return false;
}
list($ret) = $this->db->fetchRow($result);
return $ret;
}
/**
* 获得用于验证身份的信息
*
* @access private
* @return string or boolean 成功返回用于登录短信服务的帐号信息，失败返回false。
*/
function get_login_info()
{
$sql = 'SELECT `code`, `value` FROM ' . $this->ecs->table('shop_config') . " WHERE `code` = 'sms_user_name' OR `code` = 'sms_password'";
$result = $this->db->query($sql);
$retval = array();
if (!empty($result)) {
while ($temp_arr = $this->db->fetchRow($result)) {
$retval[$temp_arr['code']] = $temp_arr['value'];
}
return base64_encode($retval['sms_user_name'] . "\t" . $retval['sms_password']);
}
return false;
}
/**
* calculateTotal()
* Calculate totlas
* @access public
* @return type
*/
private function calculateTotal()
{
if (!$this->totalPages) {
$this->query = new \NG\Query();
$this->query->select("COUNT(*) as total")->from($this->table);
if (isset($this->where)) {
$this->query->where($this->where);
}
$result = $this->db->fetchRow($this->query->__toString());
$this->totalCount = $result['total'];
$this->totalPages = ceil($this->totalCount / $this->itemsPerPage);
}
return $this->totalPages;
}
/**
* Write a session to the database
*
* @param string $sess_id
* @param string $sess_data
*
* @return bool
**/
function write($sessionId, $data)
{
$sessionId = $this->db->quote($sessionId);
$data = $this->db->quote($data);
$time = time();
list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM `{$this->tableName}` WHERE sess_id={$sessionId}"));
if ($count > 0) {
$sql = "UPDATE `{$this->tableName}` SET sess_updated={$time},sess_data={$data} WHERE sess_id={$sessionId}";
} else {
$ip = $this->db->quote($_SERVER['REMOTE_ADDR']);
$sql = "INSERT INTO `{$this->tableName}` (sess_id,sess_updated,sess_ip,sess_data) VALUES ({$sessionId},{$time},{$ip},{$data})";
}
return $this->db->queryF($sql);
}
/**
* Write a session to the database
*
* @param string $sess_id
* @param string $sess_data
*
* @return bool
**/
function write($sess_id, $sess_data)
{
global $HTTP_SERVER_VARS;
list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM " . $this->db->prefix('session') . " WHERE sess_id='" . $sess_id . "'"));
if ($count > 0) {
$sql = sprintf("UPDATE %s SET sess_updated = %u, sess_data = '%s' WHERE sess_id = '%s'", $this->db->prefix('session'), time(), $sess_data, $sess_id);
} else {
$sql = sprintf("INSERT INTO %s (sess_id, sess_updated, sess_ip, sess_data) VALUES ('%s', %u, '%s', '%s')", $this->db->prefix('session'), $sess_id, time(), $HTTP_SERVER_VARS['REMOTE_ADDR'], $sess_data);
}
if (!$this->db->queryF($sql)) {
return false;
}
return true;
}
/**
* Write a session to the database
*
* @param string $sess_id
* @param string $sess_data
*
* @return bool
**/
function write($sess_id, $sess_data)
{
$sess_id = $this->db->quoteString($sess_id);
list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM " . $this->db->prefix('session') . " WHERE sess_id=" . $sess_id));
if ($count > 0) {
$sql = sprintf('UPDATE %s SET sess_updated = %u, sess_data = %s WHERE sess_id = %s', $this->db->prefix('session'), time(), $this->db->quoteString($sess_data), $sess_id);
} else {
$sql = sprintf('INSERT INTO %s (sess_id, sess_updated, sess_ip, sess_data) VALUES (%s, %u, %s, %s)', $this->db->prefix('session'), $sess_id, time(), $this->db->quoteString($_SERVER['REMOTE_ADDR']), $this->db->quoteString($sess_data));
}
if (!$this->db->queryF($sql)) {
return false;
}
return true;
}
/**
* Read a session from the database
*
* @param string &sess_id ID of the session
*
* @return array Session data
*/
function read($sess_id)
{
$sql = sprintf('SELECT sess_data, sess_ip FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id));
if (false != ($result = $this->db->query($sql))) {
if (list($sess_data, $sess_ip) = $this->db->fetchRow($result)) {
if ($this->securityLevel > 1) {
$pos = strpos($sess_ip, ".", $this->securityLevel - 1);
if (strncmp($sess_ip, $_SERVER['REMOTE_ADDR'], $pos)) {
$sess_data = '';
}
}
return $sess_data;
}
}
return '';
}
/**
* Read a session from the database
*
* @param string $sess_id ID of the session
*
* @return array Session data
*/
public function read($sess_id)
{
$ip = \Xmf\IPAddress::fromRequest();
$sql = sprintf('SELECT sess_data, sess_ip FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id));
// if (false != $result = $this->db->query($sql)) {
$result = $this->db->query($sql);
if (!empty($result)) {
if (list($sess_data, $sess_ip) = $this->db->fetchRow($result)) {
if ($this->securityLevel > 1) {
if (false === $ip->sameSubnet($sess_ip, $this->bitMasks[$this->securityLevel]['v4'], $this->bitMasks[$this->securityLevel]['v6'])) {
$sess_data = '';
}
}
return $sess_data;
}
}
return '';
}
/**
* Inserts or updates data row(s)
*
* Checking is performed what columns should be updated.
* If field is NULLABLE, contains some value and should be set to NULL,
* then the String 'null' must be sent in order to set NULL in database
*
* Method skips the excessive columns in the $data array
*
* @param object $resource Resource object that corresponds to the table to be inserted into or updated
* @param array $data Data array containing new values
* @param string|array $where Where statement(s) to indicate what row(s) will be effected (optional)
* @param boolean $enforceInsert During update, if row is not found an insert will be made
* @param boolean $returnCleanDataOnly Doesn't save the data, but cleans and returns it
* @return boolean True on successful operation and false on failure
*/
public static function save($resource, $data, $where = null, $enforceInsert = false, $returnCleanDataOnly = false)
{
$info = $resource->info();
$row = $where != null ? $resource->fetchRow($where) : null;
$dataClean = array();
foreach ($info['cols'] as $col) {
if ($info['metadata'][$col]['NULLABLE'] == 1 && $data[$col] == 'null') {
$dataClean[$col] = new Zend_Db_Expr('NULL');
} else {
if (isset($data[$col])) {
$dataClean[$col] = $data[$col];
// For changed fields that cannot be NULL
} else {
if ($row != null) {
$dataClean[$col] = $row[$col];
// For unchanged fields (avoiding nulling the "good" data)
}
}
}
}
// Good for debugging
/*
if ($resourceName == 'SomeResourceName'){
Zend_Debug::dump($info);
echo "<br />";
print_r($row->toArray());
echo "<br />";
print_r($dataClean);
die;
}
*/
if ($returnCleanDataOnly) {
return $dataClean;
}
// do not insert, if $where was specified but $row was not found
if (!($where != null && $row == null) || $enforceInsert) {
return $resource->saveRow($dataClean, $row);
}
}
/**
* Creates JSON ( http://www.json.org/ ) from the ADODB record set
*
* @param object $rs - record set object
* @param bool $moveFirst - determines whether recordset is returned to first record
* @return string $output - resulting json string
* @version V1.0 10 June 2006 (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved.
*
* Released under both BSD license and Lesser GPL library license. You can choose which license
* you prefer.
Example output from query "SELECT Name, Continent From Country LIMIT 10;"
{"rows":[
{"row":{"Name":"Afghanistan","Continent":"Asia"}},
{"row":{"Name":"Netherlands","Continent":"Europe"}},
{"row":{"Name":"Netherlands Antilles","Continent":"North America"}},
{"row":{"Name":"Albania","Continent":"Europe"}},
{"row":{"Name":"Algeria","Continent":"Africa"}},
{"row":{"Name":"American Samoa","Continent":"Oceania"}},
{"row":{"Name":"Andorra","Continent":"Europe"}},
{"row":{"Name":"Angola","Continent":"Africa"}},
{"row":{"Name":"Anguilla","Continent":"North America"}},
{"row":{"Name":"Antigua and Barbuda","Continent":"North America"}}
]}
*/
function rs2json($rs, $moveFirst = false)
{
if (!$rs) {
printf(ADODB_BAD_RS, 'rs2json');
return false;
}
$output = '';
$rowOutput = '';
$output .= '{"rows":';
$totalRows = $rs->numrows();
if ($totalRows > 0) {
$output .= '[';
$rowCounter = 1;
while ($row = $rs->fetchRow()) {
$rowOutput .= '{"row":{';
$cols = count($row);
$colCounter = 1;
foreach ($row as $key => $val) {
$rowOutput .= '"' . $key . '":';
$rowOutput .= '"' . $val . '"';
if ($colCounter != $cols) {
$rowOutput .= ',';
}
$colCounter++;
}
$rowOutput .= '}}';
if ($rowCounter != $totalRows) {
$rowOutput .= ',';
}
$rowCounter++;
}
$output .= $rowOutput . ']';
} else {
$output .= '"row"';
}
$output .= '}';
if ($moveFirst) {
$rs->MoveFirst();
}
return $output;
}
/**
* Iterator next method
* @return boolean TRUE if there are more results to fetch
* @access public
*/
function next()
{
if (!isset($this->QueryId)) {
return FALSE;
}
$this->properties = $this->QueryId->fetchRow(MDB2_FETCHMODE_ASSOC);
if (is_array($this->properties)) {
$this->prepare();
return TRUE;
} else {
$this->freeQuery();
return FALSE;
}
}
/**
* Get information about a piece of railcam footage from the database
* @since Version 3.10.0
* @param int $id
* @return \Railpage\Railcams\Footage
*/
public function getFootage($id = null)
{
$query = "SELECT * FROM railcam_footage WHERE id = ?";
return $this->db->fetchRow($query, $id);
}
/**
* Run some coreDatabaseTable tests (ORM)
*
* @param object $db
* @param object $test
*/
function ormTests($db, $test)
{
// Testing method access to the table peer attributes
TestOrmPeer::exampleMethod($test);
// Testing table data access through the peer class:
echo "<h2>Select</h2>";
//$db->setFetchMode(coreDatabase::FETCH_NUM);
$rows = $db->fetchAll(TestOrmPeer::select());
$db->dumpResultSet($rows);
echo "<h2>Count</h2>";
$test->out(TestOrmPeer::count('id > ?', 24), "count('id > 24')");
echo "<h2>Insert</h2>";
TestOrmPeer::insert(array('firstname' => 'Superman', 'age' => 28));
// show new row
$id = $db->lastInsertId();
echo '<p> Inserted new row with id: <b>' . $id . '</b>:';
$rows = $db->fetchRow(TestOrmPeer::select()->where('id = ?', $id));
$db->dumpResultSet($rows);
echo "<h2>Update</h2>";
// delay 1 second to see the UPDATED_ON changes
sleep(1);
TestOrmPeer::update(array('firstname' => 'She-Ra', 'age' => 31), 'id = ?', $id);
// show update results
echo '<p> Updated row with id: <b>' . $id . '</b> (1 second delayed):';
$rows = $db->fetchRow(TestOrmPeer::select()->where('id = ?', $id));
$db->dumpResultSet($rows);
echo "<h2>Delete</h2>";
echo '<p> Deleted row with id: <b>' . $id . '</b>.';
TestOrmPeer::delete('id >= ?', 27);
}
/**
* checkUserInDB()
* Builds select query to check user in DB and returns result as an array
*
* @return array|false
*/
private function checkUserInDB()
{
$query = new Query();
$query->select()->from($this->table)->where($this->identityColumn . " = ?", $this->identity)->andWhere($this->credentialColumn . " = ?", $this->credential)->limit(1);
return $this->dbAdapter->fetchRow($query);
}
/**
* Function to get a query from DB
*
* @param object $db Reference to the database object
* @param string $table The table to get the value from
* @param string $field The table to get the value from
* @param string $condition The where condition (where clause) to use
* @return mixed
* @todo Move to a static class method - database
*/
function getDbValue(&$db, $table, $field, $condition = '')
{
$table = $db->prefix($table);
$sql = "SELECT `{$field}` FROM `{$table}`";
if ($condition) {
$sql .= " WHERE {$condition}";
}
$result = $db->query($sql);
if ($result) {
$row = $db->fetchRow($result);
if ($row) {
return $row[0];
}
}
return false;
}
/**
* 获取查询信息中的一行数据
*
* 注：本函数(类方法)需与query()组合使用。
*
* @access public
*
* @param string $model 返回数据的索引类型：字段型/数据型 等。默认：字段型
*
* @return array
*/
public function fetchRow($model = 'PDO::FETCH_ASSOC')
{
return $this->_dbLink->fetchRow($model);
}
/**
* Get count of clips for the admin index page
*
* @param string $languagesel specific language
*
* @return int count of clips
*/
function &getClipsCountFromAdmin($languagesel)
{
global $xoopsConfig, $xoopsDB;
$smartConfig =& smartmedia_getModuleConfig();
if ($languagesel == 'all') {
$where = "";
} else {
$where = "WHERE clips_text.languageid = '" . $languagesel . "'";
}
$sql = "SELECT COUNT(DISTINCT clips.clipid)\r\n\t\t\t\t\tFROM (\r\n\t\t\t\t\t " . $xoopsDB->prefix('smartmedia_clips') . " AS clips\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_clips_text') . " AS clips_text ON clips.clipid = clips_text.clipid\r\n\t\t\t\t\t\t)\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders') . " AS folders ON clips.folderid=folders.folderid\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders_text') . " AS folders_text ON folders.folderid = folders_text.folderid\r\n\t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders_categories') . " AS categories\r\n\t\t\t\t\tON folders.folderid = categories.folderid\r\n\t\t\t\t \r\n " . $where;
//echo "<br />$sql<br />";
if (!($result =& $this->_db->query($sql))) {
return 0;
}
list($count) = $this->_db->fetchRow($result);
return $count;
}
/**
* verify that chosen transactional table hanlder is available in the database
*
* @param object $db database object that is extended by this class
* @param string $table_type name of the table handler
* @return mixed MDB_OK on success, a MDB error on failure
* @access private
*/
function _verifyTransactionalTableType(&$db, $table_type)
{
switch (strtoupper($table_type)) {
case 'BERKELEYDB':
case 'BDB':
$check = array('have_bdb');
break;
case 'INNODB':
$check = array('have_innobase', 'have_innodb');
break;
case 'GEMINI':
$check = array('have_gemini');
break;
case 'HEAP':
case 'ISAM':
case 'MERGE':
case 'MRG_MYISAM':
case 'MYISAM':
case '':
return MDB_OK;
default:
return $db->raiseError(MDB_ERROR_UNSUPPORTED, NULL, NULL, 'Verify transactional table', $table_type . ' is not a supported table type');
}
if (isset($this->verified_table_types[$table_type]) && $this->verified_table_types[$table_type] == $db->connection) {
return MDB_OK;
}
$not_supported = false;
for ($i = 0, $j = count($check); $i < $j; ++$i) {
$result = $db->query('SHOW VARIABLES LIKE ' . $db->getValue('text', $check[$i]), null, false);
if (MDB::isError($result)) {
return $db->mysqlRaiseError();
}
$has = $db->fetchRow($result, MDB_FETCHMODE_ORDERED);
if (MDB::isError($has)) {
return $has;
}
if (is_array($has)) {
$not_supported = true;
if ($has[1] == 'YES') {
$this->verified_table_types[$table_type] = $db->connection;
return MDB_OK;
}
}
}
if ($not_supported) {
return $db->raiseError(MDB_ERROR_UNSUPPORTED, null, null, $table_type . ' is not a supported table type by this MySQL database server');
}
return $db->raiseError(MDB_ERROR_UNSUPPORTED, null, null, 'could not tell if ' . $table_type . ' is a supported table type');
}
/**
* checkUserInDB()
* Builds select query to check user in DB and returns result as an array
* @access private
* @return array|false
*/
private function checkUserInDB()
{
return $this->dbAdapter->fetchRow("SELECT * FROM `" . $this->table . "`\n WHERE `" . $this->identityColumn . "` = '" . $this->identity . "'\n AND `" . $this->credentialColumn . "` = '" . $this->credential . "'\n LIMIT 1");
}
/**
* 获取查询结果行数
*
* @param object $criteria 查询类
* @access public
* @return int 结果行数
*/
function getCount($criteria = NULL)
{
if (is_object($criteria)) {
if ($criteria->getGroupby() == '') {
$sql = 'SELECT COUNT(*) FROM ' . $criteria->getTables() . ' ' . $criteria->renderWhere();
$nobuffer = true;
} else {
$sql = 'SELECT COUNT(' . $criteria->getGroupby() . ') FROM ' . $criteria->getTables() . ' ' . $criteria->renderWhere() . ' GROUP BY ' . $criteria->getGroupby();
$nobuffer = false;
}
$result = $this->db->query($sql, 0, 0, $nobuffer);
if (!$result) {
return 0;
}
if ($criteria->getGroupby() == '') {
list($count) = $this->db->fetchRow($result);
} else {
$count = $this->db->getRowsNum($result);
}
return $count;
}
return 0;
}
/**
* モジュールIDをキーとしたモジュールデータ配列を作成する。
*
* @param object $recordSet モジュールデータADORecordSetオブジェクト
* @return string 指定文字区切りの文字列
* @access private
*/
function &_fetchModule(&$recordSet)
{
$modules = array();
while ($module = $recordSet->fetchRow()) {
$moduleId = $module['module_id'];
$modules[$moduleId] = $module;
}
return $modules;
}