<?php
/**
* @version $Id: pgsql.adapter.php 1138 2012-05-18 17:13:22Z datahell $
* @package Elxis
* @subpackage Database
* @copyright Copyright (c) 2006-2012 Elxis CMS (http://www.elxis.org). All rights reserved.
* @license Elxis Public License ( http://www.elxis.org/elxis-public-license.html )
* @author Elxis Team ( http://www.elxis.org )
* @description Elxis CMS is free software. Read the license for copyright notices and details
*/
defined('_ELXIS_') or die ('Direct access to this location is not allowed.');
class elxisPgsqlAdapter extends elxisDbAdapter {
/*************************************/
/* CALL THE PARENT CLASS CONSTRUCTOR */
/*************************************/
public function __construct($pdo=null) {
parent::__construct($pdo);
}
/*************************************/
/* ADD LIMIT/OFFSET TO SQL STATEMENT */
/*************************************/
public function addLimit($sql, $offset=-1, $limit=-1) {
if ($limit <= 0) { return $sql; }
$sql .= ' LIMIT '.$limit;
if ($offset > 0) { $sql .= ' OFFSET '.$offset; }
return $sql;
}
/*****************************************************************/
/* GET THE LAST ID GENERATED BY AN IDENTITY/AUTOINCREMENT COLUMN */
/*****************************************************************/
public function lastInsertId($tableName = null, $primaryKey = null) {
if ($tableName !== null) {
$sequenceName = $tableName;
if ($primaryKey) { $sequenceName .= '_'.$primaryKey; }
$sequenceName .= '_seq';
$sql = "SELECT CURRVAL(".$sequenceName.")";
return (int)$this->pdo->query($sql)->fetchColumn();
}
return $this->pdo->lastInsertId($tableName);
}
/****************************/
/* LIST ALL DATABASE TABLES */
/****************************/
public function listTables() {
$sql = "SELECT c.relname AS table_name FROM pg_class c, pg_user u"
."\n WHERE c.relowner = u.usesysid AND c.relkind = 'r'"
."\n AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) AND c.relname !~ '^(pg_|sql_)'"
."\n UNION SELECT c.relname AS table_name FROM pg_class c"
."\n WHERE c.relkind = 'r'"
."\n AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname)"
."\n AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner)"
."\n AND c.relname !~ '^pg_'";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
return $stmt->fetchCol();
}
/************************************************/
/* BACKUP DATABASE (EXPERIMENTAL - NOT TESTED!) */
/************************************************/
public function backup($params) {
if (count($params) == 0) { return -2; }
$elxis = eFactory::getElxis();
$dsn = $elxis->getConfig('DB_DSN');
if (trim($dsn) != '') {
$dbname = $this->getFromDSN($dsn, 'dbname');
} else {
$dbname = $elxis->getConfig('DB_NAME');
}
if ($dbname == '') { return -2; }
$com = '--';
$out = $com."\n";
$out .= $com.' PostgreSQL backup taken by Elxis CMS v'.$elxis->getVersion()."\n";
$out .= $com.' Copyright (c) 2006-'.date('Y')." elxis.org. All rights reserved.\n";
$out .= $com.' Database: '.$dbname."\n";
$out .= $com.' URL: '.$elxis->getConfig('URL')."\n";
$out .= $com.' Date (UTC): '.gmdate('Y-m-d H:i:s')."\n";
$out .= $com."\n\n";
if ($params['create_db'] == true) {
$out .= 'CREATE DATABASE '.$dbname.';'."\n\n";
}
foreach ($params['tables'] as $table) {
$sql = "SELECT attnum, attname, typname, atttypmod-4, attnotnull, atthasdef, adsrc AS def"
."\n FROM pg_attribute, pg_class, pg_type, pg_attrdef WHERE pg_class.oid=attrelid AND pg_type.oid=atttypid"
."\n AND attnum>0 AND pg_class.oid=adrelid AND adnum=attnum AND atthasdef='t' AND lower(relname)='".$table."'"
."\n UNION SELECT attnum, attname, typname, atttypmod-4, attnotnull, atthasdef, '' AS def"
."\n FROM pg_attribute, pg_class, pg_type WHERE pg_class.oid=attrelid AND pg_type.oid=atttypid"
."\n AND attnum>0 AND atthasdef='f' AND lower(relname)='".$table."'";
$query = $this->pdo->query($sql);
if ($query === false) { continue; }
$out .= $com."\n";
$out .= $com.' Definition of table '.$table."\n";
$out .= $com."\n\n";
if ($params['add_drop'] == true) {
$out .= 'DROP TABLE '.$table.' CASCADE;'."\n";
}
$rows = $query->fetchAll(PDO::FETCH_NUM);
if ($rows) {
$n = count($rows);
$k = 1;
$out .= 'CREATE TABLE '.$table." (\n";
foreach ($rows as $row) {
$out .= $row[1].' '.$row[2];
if ($row[2] == 'varchar') { $out .= '('.$row[3].')'; }
if ($row[4] == 't') { $out .= ' NOT NULL'; }
if ($row[5] == 't') { $out .= ' DEFAULT '.$row[6]; }
if ($k < $n) { $out .= ','; }
$out .= "\n";
$k++;
}
$out .= ");\n\n";
}
$sql = "SELECT pg_index.indisprimary, pg_catalog.pg_get_indexdef(pg_index.indexrelid)"
."\n FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index AS pg_index"
."\n WHERE c.relname = '".$table."' AND c.oid = pg_index.indrelid"
."\n AND pg_index.indexrelid = c2.oid AND pg_index.indisprimary";
$query = $this->pdo->query($sql);
if ($query !== false) {
$rows = $query->fetchAll(PDO::FETCH_NUM);
if ($rows) {
$out .= $com."\n";
$out .= $com.' Creating index for '.$table."\n";
$out .= $com."\n\n";
foreach ($rows as $row) {
$t = str_replace("CREATE UNIQUE INDEX", "", $row[1]);
$t = str_replace("USING btree", "|", $t);
$t = str_replace("ON", "|", $t);
$arr = explode("|", $t);
$out .= "ALTER TABLE ONLY ".$arr[1]." ADD CONSTRAINT ".$arr[0]." PRIMARY KEY ".$arr[2].";\n";
}
$out .= "\n";
}
}
if ($params['add_insert'] == false) { continue; }
if (in_array($table, $params['no_insert_tables'])) { continue; }
$stmt = $this->pdo->prepare('SELECT * FROM '.$table);
if (!$stmt->execute()) { $out .= "\n\n"; continue; }
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!$rows) { continue; }
foreach ($rows as $row) {
$i = 1;
$n = count($row);
$vstr = '';
foreach($row as $k => $v) {
if ($v === NULL) {
$vstr .= 'NULL';
} else if ($v == '') {
$vstr .= 'NULL';
} else if (is_int($v)) {
$vstr .= $v;
} else if (is_numeric($v) && (intval($v) == $v)) {
$vstr .= $v;
} else {
$vstr .= '\''.addslashes($v).'\'';
}
if ($i < $n) { $vstr .= ', '; }
$i++;
}
$out .= 'INSERT INTO '.$table.' VALUES ('.$vstr.');'."\n";
}
$out .= "\n";
}
$sql = "SELECT cl.relname AS tabela, ct.conname, pg_get_constraintdef(ct.oid) FROM pg_catalog.pg_attribute a"
."\n JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')"
."\n JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)"
."\n JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND ct.confrelid != 0 AND ct.conkey[1] = a.attnum)"
."\n JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')"
."\n JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)"
."\n JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1]) order by cl.relname";
$query = $this->pdo->query($sql);
if ($query !== false) {
$rows = $query->fetchAll(PDO::FETCH_NUM);
if ($rows) {
foreach ($rows as $row) {
$out .= $com."\n";
$out .= $com.' Creating relacionships for '.$row[0]."\n";
$out .= $com."\n";
$out .= 'ALTER TABLE ONLY '.$row[0].' ADD CONSTRAINT '.$row[1].' '.$row[2].";\n";
}
$out .= "\n";
}
}
return $out;
}
}
?>