<?php
/*---------------------------------------------------------*\
| ZitPG v0.04 |
| http://zoomit.vertigoproject.net |
+---------------------------------------------------------+
| Copyright©2004|2005 Aldemar Bernal |
| hide@address.com |
+---------------------------------------------------------+
| License |
| |
| This program is an open source project and you can use |
| or distribute it in your website but always keeping its |
| original copyright information and license. |
| |
| If you want to know more about this license, please |
| refer to the license.txt file or go to the official |
| LGPL Website http://www.gnu.org/licenses/lgpl.txt |
+---------------------------------------------------------+
| File: /create_postgresql_db.php |
| Description: Creates the postgresql database structure. |
\*---------------------------------------------------------*/
include_once('configuration.php');
include_once('classes/db_wrapper.php');
//Queries
$i_create_albums_table = "CREATE TABLE albums
(
id_album SERIAL,
id_default_photo INTEGER NOT NULL,
username VARCHAR(255) NOT NULL,
id_parent_album INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
PRIMARY KEY(id_album)
)";
$i_create_album_comments_table = "CREATE TABLE album_comments
(
id_album_comment SERIAL,
username VARCHAR(255) NOT NULL,
id_album INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_album_comment)
)";
$i_create_news_table = "CREATE TABLE news
(
id_news SERIAL,
username VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_news)
)";
$i_create_news_comments_table = "CREATE TABLE news_comments
(
id_news_comments SERIAL,
username VARCHAR(255) NOT NULL,
id_news INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_news_comments)
)";
$i_create_photos_table = "CREATE TABLE photos
(
id_photo SERIAL,
username VARCHAR(255) NOT NULL,
id_album INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
date DATE NULL,
description TEXT NOT NULL,
posted_date DATE NOT NULL,
photo_url TEXT NOT NULL,
times_visited INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY(id_photo)
)";
$i_create_photo_comments_table = "CREATE TABLE photo_comments
(
id_photo_comment SERIAL,
username VARCHAR(255) NOT NULL,
id_photo INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_photo_comment)
)";
$i_create_photo_ratings_table = "CREATE TABLE photo_ratings
(
id_photo_rating SERIAL,
id_photo INTEGER NOT NULL,
username VARCHAR(255) NOT NULL,
rating INTEGER NOT NULL,
PRIMARY KEY(id_photo_rating)
)";
$i_create_users_table = "CREATE TABLE users
(
username VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
show_email INTEGER NOT NULL DEFAULT '0',
realname VARCHAR(255) NOT NULL,
member_since DATE NOT NULL,
photo_url TEXT NOT NULL,
birthday DATE NOT NULL,
show_birthday INTEGER NOT NULL DEFAULT '1',
show_age INTEGER NOT NULL DEFAULT '1',
show_online INTEGER NOT NULL DEFAULT '1',
description TEXT NOT NULL,
quote TEXT NOT NULL,
PRIMARY KEY(username)
)";
$i_create_user_sessions = "CREATE TABLE user_sessions
(
hit_number SERIAL,
id_session VARCHAR(255) NOT NULL,
username VARCHAR(255) NULL,
last_time_online TIMESTAMP NOT NULL,
max_users_online INTEGER NOT NULL,
time_max_users_online TIMESTAMP NOT NULL,
PRIMARY KEY(hit_number, id_session)
)";
$GLOBALS["database_object"] = new db_wrapper(db_postgresql, $GLOBALS["postgresql_database"], TRUE, $GLOBALS["postgresql_server"], $GLOBALS["postgresql_username"], $GLOBALS["postgresql_password"], $GLOBALS["postgresql_port"]);
//Create Database Structure
if($GLOBALS["database_object"]->error == "")
{
if($GLOBALS["database_object"]->query($i_create_albums_table) == FALSE)echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_album_comments_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_news_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_news_comments_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_photos_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_photo_comments_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_photo_ratings_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_users_table) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
if($GLOBALS["database_object"]->query($i_create_user_sessions) == FALSE) echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
}
else
echo "<br /><strong>DB Error:</strong> " . $GLOBALS["database_object"]->error;
echo "<br />Database script finish!";
/*
If this script doesn't work for any reason here is
a copy of the database creation script, you can run
it directly in your postgresql server
CREATE TABLE albums (
id_album SERIAL,
id_default_photo INTEGER NOT NULL,
username VARCHAR(255) NOT NULL,
id_parent_album INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
PRIMARY KEY(id_album)
);
CREATE TABLE album_comments (
id_album_comment SERIAL,
username VARCHAR(255) NOT NULL,
id_album INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_album_comment)
);
CREATE TABLE news (
id_news SERIAL,
username VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_news)
);
CREATE TABLE news_comments (
id_news_comments SERIAL,
username VARCHAR(255) NOT NULL,
id_news INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_news_comments)
);
CREATE TABLE photos (
id_photo SERIAL,
username VARCHAR(255) NOT NULL,
id_album INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
date DATE NULL,
description TEXT NOT NULL,
posted_date TIMESTAMP NOT NULL,
photo_url TEXT NOT NULL,
times_visited INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY(id_photo)
);
CREATE TABLE photo_comments (
id_photo_comment SERIAL,
username VARCHAR(255) NOT NULL,
id_photo INTEGER NOT NULL,
comments TEXT NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY(id_photo_comment)
);
CREATE TABLE photo_ratings (
id_photo_rating SERIAL,
id_photo INTEGER NOT NULL,
username VARCHAR(255) NOT NULL,
rating INTEGER NOT NULL,
PRIMARY KEY(id_photo_rating)
);
CREATE TABLE users (
username VARCHAR(255) NOT NULL,
user_password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
show_email INTEGER NOT NULL DEFAULT '0',
realname VARCHAR(255) NOT NULL,
member_since DATE NOT NULL,
photo_url TEXT NOT NULL,
birthday DATE NOT NULL,
show_birthday INTEGER NOT NULL DEFAULT '1',
show_age INTEGER NOT NULL DEFAULT '1',
show_online INTEGER NOT NULL DEFAULT '1',
description TEXT NOT NULL,
quote TEXT NOT NULL,
PRIMARY KEY(username)
);
CREATE TABLE user_sessions (
hit_number SERIAL,
id_session VARCHAR(255) NOT NULL,
username VARCHAR(255) NULL,
last_time_online TIMESTAMP NOT NULL,
PRIMARY KEY(hit_number, id_session)
);
*/
?>