Thread: template0

template0

From
"Marcel Wolf"
Date:

Thanks Guys!

Let me be a little for specific. I am trying to make a php/pg/apache app that when run checks for its database and if not found creates it. To do this I first connect to pg first under template1 and check for my database in pg_database. If found I close and connect to that database. If not found I create it while connected to template1 and then close and connect under my database. Here is the code. Maybe I need to post this to the php site. But when I look at using a database I need to connect to it with a user and dbname. I have been using template1 successfully except any new databases after the first one inherit the tables from the 1st database and I though the way to get around this was to use template0? Am I out in left field about all this?

Thanks

Marcel

 

 

<?php

$pgtemplate="user=postgre dbname=template0";

$pguser="user=postgre dbname=recipe";

$db="recipe";

 

            $link=pg_connect($pgtemplate) or die ("Couldn't connect to template try again.<br>".pg_last_error());

           

            $query = "SELECT * FROM pg_database";

 

            $result = pg_query ($query) or die ("Couldn't get the database names".pg_last_error());

 

            // If it worked get the database names.

            $max_array=pg_numrows($result);

            $got_database = false;                          

            for ($i=0;$i<$max_array;$i++) {

                        $database= pg_fetch_array ($result) or print ("Can't get the array of databases<br>");         

                        //print (" the 1st database is {$database['datname']}.<br>");

                        if ($database['datname'] == $db) {

                                    $got_database = true;

                        }          

            }

            if ($got_database == false) {

                        // Need to build my database

                        print "No database<br>";

                        pg_query ("CREATE DATABASE $db") or die ("Could not create database".pg_last_error());

                        // Created new database. disconnect from the template1 and reconnect to the new database.

                        pg_close ($link);

 

                        $link=pg_connect($pguser) or die ("Couldn't connect to the new database<br>".pg_last_error());

                                   

                       

                        $query = "CREATE TABLE cats (

                           cat character varying(25) NOT NULL,

                           jpg character varying(12) NOT NULL)

                        ";

                       

                        pg_query($query) or

                        die ("Query Failed.<br>".pg_last_error());

                       

                        $query = "CREATE TABLE recipe (

                           noo SERIAL,

                           nam character varying(50) NOT NULL,

                           cat character varying(25) NOT NULL,

                           ing text NOT NULL,

                           ins text NOT NULL,

                           PRIMARY KEY (noo)

                        )";

                       

                        pg_query($query) or

                        die ("Query Failed.<br>".pg_last_error());

                       

Re: template0

From
Josh Berkus
Date:
Marcel,

> Let me be a little for specific. I am trying to make a php/pg/apache app
> that when run checks for its database and if not found creates it. To do
> this I first connect to pg first under template1 and check for my
> database in pg_database. If found I close and connect to that database.
> If not found I create it while connected to template1 and then close and
> connect under my database. Here is the code. Maybe I need to post this
> to the php site. But when I look at using a database I need to connect
> to it with a user and dbname. I have been using template1 successfully
> except any new databases after the first one inherit the tables from the
> 1st database and I though the way to get around this was to use
> template0? Am I out in left field about all this?

Your code creates the tables in template1, not in the new database.   That's
your problem.   You need to fix it so that you pg_connect to the newly
created database.

BTW, the approach you're taking is vey insecure and I hope you're not using it
on a public site anywhere.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco