1、实例代码
/**
* 以月为单位，根据时间创建子表
* @param $db
* @param $time
* @return string
*/
public function createTable(&$db, $time) {
/*
* CREATE TABLE zhubiao (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
* */
$main_table = $this->main_table;//主表名
$date_y = date("Y", $time);
$date_m = date("m", $time);
$this_month_first_day = date("Y-m-1", $time);
$next_month_first_day = date('Y-m-1', strtotime('next month', $time));
//子表
$table_name = "{$main_table}_" . $date_y . "_" . $date_m;
$res = $db->query("select 1 as cl from pg_class where relname='" . $table_name . "'::name and relkind='r'");
$res = $db->fetchArray($res);
if ($res) {//如果字表已经存在，则不再创建
return $table_name;
}
//创建带约束条件的子表
$sql = "CREATE TABLE " . $table_name . " (CHECK ( logdate >= DATE '" . $this_month_first_day . "' AND logdate < DATE '" . $next_month_first_day . "' )) INHERITS ({$main_table});";
//为插入数据创建规则
$sql_rule = "CREATE or Replace RULE " . $table_name . "_insert AS ON INSERT TO {$main_table} WHERE ( logdate >= DATE '" . $this_month_first_day . "' AND logdate < DATE '" . $next_month_first_day . "' )";
$sql_rule .= "DO INSTEAD INSERT INTO " . $table_name . " VALUES ( NEW.*);";
$ret = $db->query($sql);
$ret = $db->query($sql_rule);
return $table_name;
}
/**
* 删除三年前的子表
* @param $db
* @return mixed
*/
public function dropTable($db) {
//DROP TABLE products CASCADE;
$time = strtotime("-3 year");
$main_table = $this->main_table;//主表名
$date_y = date("Y", $time);
$date_m = date("m", $time);
$table_name = "{$main_table}_" . $date_y . "_" . $date_m;
return $db->query("DROP TABLE {$table_name} CASCADE");
}2、pgsql PHP DB类
/*
* 系统用户
*/
class db
{
private $linkid; // PostgreSQL连接标识符
private $host; // PostgreSQL服务器主机
private $port; // PostgreSQL服务器主机端口
private $user; // PostgreSQL用户
private $passwd; // PostgreSQL密码
private $db; // Postgresql数据库
private $result; // 查询的结果
private $querycount; // 已执行的查询总数
/* 类构造函数，用来初始化$host、$user、$passwd和$db字段。 */
function __construct() {
$host = "127.0.0.1";
$port = "5432";
$db = "test";
$user = "postgres";//"user= password=123456";
$passwd = "123456";
$this->host = $host;
$this->port = $port;
$this->user = $user;
$this->passwd = $passwd;
$this->db = $db;
$this->connect();
}
/* 连接Postgresql数据库 */
function connect() {
try {
$this->linkid = @pg_connect("host=$this->host port=$this->port dbname=$this->db user=$this->user password=$this->passwd");
if (!$this->linkid)
throw new Exception("Could not connect to PostgreSQL server.");
} catch (Exception $e) {
die($e->getMessage());
}
}
/* 执行数据库查询。 */
function query($query) {
try {
$this->result = pg_query($this->linkid, $query);
if (!$this->result)
throw new Exception("The database query failed." . $query);
} catch (Exception $e) {
echo $e->getMessage();
}
$this->querycount++;
return $this->result;
}
/* 确定受查询所影响的行的总计。 */
function affectedRows() {
$count = @pg_affected_rows($this->linkid);
return $count;
}
/* 确定查询返回的行的总计。 */
function numRows() {
$count = @pg_num_rows($this->result);
return $count;
}
/* 将查询的结果行作为一个对象返回。 */
function fetchObject() {
$row = @pg_fetch_object($this->result);
return $row;
}
/* 将查询的结果行作为一个索引数组返回。 */
function fetchRow() {
$row = @pg_fetch_row($this->result);
return $row;
}
/* 将查询的结果行作为一个关联数组返回。 */
function fetchArray() {
$row = @pg_fetch_array($this->result);
return $row;
}
/* 返回在这个对象的生存期内执行的查询总数。这不是必须的，但是您也许会感兴趣。 */
function numQueries() {
return $this->querycount;
}
}