Location: PHPKode > projects > ZitPG (Zoom It! Photo Gallery) > zitpg_v0.04/create_postgresql_db.php
<?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)
			);

	*/

?>
Return current item: ZitPG (Zoom It! Photo Gallery)