phpwebsite-developers

[Phpwebsite-developers] Postgres Support (making progress...)

[Brian G. <br...@vf...>]

Greetings, I've just joined the list and have scanned the archives for 
PG-related info in the past.  I've spent about 6-8 hours trying to convert 
phpwebsite 0.9.3 to support postgres.  So far, so good, although I've run 
into some issues around the sequence support.  For the most part, I've 
modified everything enough to install all of the modules but the database 
routines don't quite work yet. 

I've done a number of things including: 

* Rewrite sqlGetIndex() to work for postgres (although this will be 
database-specific and might be worth dropping such a routine) 

* Update all of the module install.sql and setup/install.sql to be more 
ANSI-compliant and use SQL-standard syntax for creating indexes 

Two pieces of documentation would be extremely helpful in moving forward: 

1. An ER diagram of the database model for PWS that states, in a 
database-independent fashion, what the relationships and datatypes should 
be.  For example, it's hard to know which fields should be unique primary 
keys and which should just be indexed from the SQL scripts. 

2. Dev roadmap that gives some rough direction of which files, systems or 
libraries need to be worked on to achieve database-independence.  That way 
contributors can do some heavy lifting. 

I know that 0.9.4 which is supposed to be db-independent is on something of 
a ~Spring release schedule but I need good postgres support before then.  
I'm hoping to take advantage of my enthusiasm if I can get a little 
direction and perhaps I can piggyback on Richard S' work to date. 

One big question I have is how are the auto-incrementing fields used?  Are 
they necessary?  I saw some discussion in the archives from August but no 
clear decisions? 

Is there a semi-stable tagged CVS build that is more recent than what's on 
the website I can work from? 

Thanks, 


Brian 

Re: [Phpwebsite-developers] Postgres Support (making progress...)

[David M. <dma...@so...>]

I've been trying the same thing.  Here is what I have so far.  Some of
the modules seem to have correct sql syntax in their install.sql files. 
Others need things changed like mediumtext and longtext to just text. 
"Tinyint(*)" needs to be changed to "smallint", "int(*)" needs to be
changed to "int" and the "unsigned" keyword needs to be removed. The
biggest problem I've been having is in the "setup/install.sql" file.  I
do not know the equivalent syntax for lines such as
"index(mod_title,id)".  Someone has mentioned about removing these line
but problems occur later on with index functions.  Postgresql uses
something like "create index idx on table(field)" but I don't know what
I should use for the idx variable. I tried it without using the variable
idx but I get table errors.  Someone has just helped me with another
problem which is in "core/Database.php" on line 769 which reads 

$sql = "ALTER TABLE $table_name ADD INDEX (" . implode(", ", $columns) .
")";

This needs to be changed to 

foreach ($columns as $item){  $sql = "CREATE INDEX on " .
$core->tbl_prefix . "$table_name ($item)";  $this->query($sql);}

David



On Tue, 2003-11-04 at 18:09, Brian Ghidinelli wrote:
> Greetings, I've just joined the list and have scanned the archives for 
> PG-related info in the past.  I've spent about 6-8 hours trying to convert 
> phpwebsite 0.9.3 to support postgres.  So far, so good, although I've run 
> into some issues around the sequence support.  For the most part, I've 
> modified everything enough to install all of the modules but the database 
> routines don't quite work yet. 
> 
> I've done a number of things including: 
> 
> * Rewrite sqlGetIndex() to work for postgres (although this will be 
> database-specific and might be worth dropping such a routine) 
> 
> * Update all of the module install.sql and setup/install.sql to be more 
> ANSI-compliant and use SQL-standard syntax for creating indexes 
> 
> Two pieces of documentation would be extremely helpful in moving forward: 
> 
> 1. An ER diagram of the database model for PWS that states, in a 
> database-independent fashion, what the relationships and datatypes should 
> be.  For example, it's hard to know which fields should be unique primary 
> keys and which should just be indexed from the SQL scripts. 
> 
> 2. Dev roadmap that gives some rough direction of which files, systems or 
> libraries need to be worked on to achieve database-independence.  That way 
> contributors can do some heavy lifting. 
> 
> I know that 0.9.4 which is supposed to be db-independent is on something of 
> a ~Spring release schedule but I need good postgres support before then.  
> I'm hoping to take advantage of my enthusiasm if I can get a little 
> direction and perhaps I can piggyback on Richard S' work to date. 
> 
> One big question I have is how are the auto-incrementing fields used?  Are 
> they necessary?  I saw some discussion in the archives from August but no 
> clear decisions? 
> 
> Is there a semi-stable tagged CVS build that is more recent than what's on 
> the website I can work from? 
> 
> Thanks, 
> 
> 
> Brian 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: SF.net Giveback Program.
> Does SourceForge.net help you be more productive?  Does it
> help you create better code?   SHARE THE LOVE, and help us help
> YOU!  Click Here: http://sourceforge.net/donate/
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers

Re: [Phpwebsite-developers] Postgres Support (making progress...)

[Brian G. <br...@vf...>]

David Macbanay wrote:
> Others need things changed like mediumtext and longtext to just text. 
> "Tinyint(*)" needs to be changed to "smallint", "int(*)" needs to be
> changed to "int" and the "unsigned" keyword needs to be removed. The

There are also some varchar binary's that need to have the binary 
stripped.

> biggest problem I've been having is in the "setup/install.sql" file.  I
> do not know the equivalent syntax for lines such as
> "index(mod_title,id)".  Someone has mentioned about removing these line

I used:

create index cache_idx on cache (mod_title, id);

This works just fine.  However, I don't know if it should be UNIQUE or 
not.  This is where an ER diagram would come in handy.  Perhaps this 
week I will be a single diff of everything I've changed so far.  I'm 
working on a Redhat Linux box against Postgres 7.3 but it should be 
similar for any other releases.


Brian

Re: [Phpwebsite-developers] Postgres Support (making progress...)

[David M. <dma...@so...>]

> > biggest problem I've been having is in the "setup/install.sql" file.  I
> > do not know the equivalent syntax for lines such as
> > "index(mod_title,id)".  Someone has mentioned about removing these line
> 
> I used:
> 
> create index cache_idx on cache (mod_title, id);
> 
Here is the error that I get when I use that statement

DB Error: syntax error
CREATE INDEX on cache (mod_title) [nativecode=ERROR: parser: parse error
at or near "on" at character 14 ]

David

Re: [Phpwebsite-developers] Postgres Support (making progress...)

[Richard S. <ri...@ri...>]

You forgot the cache_idx. You must name an index in order to create it.

- Richard Sumilang


On Nov 4, 2003, at 6:01 PM, David Macbanay wrote:

>>> biggest problem I've been having is in the "setup/install.sql" file. 
>>>  I
>>> do not know the equivalent syntax for lines such as
>>> "index(mod_title,id)".  Someone has mentioned about removing these 
>>> line
>>
>> I used:
>>
>> create index cache_idx on cache (mod_title, id);
>>
> Here is the error that I get when I use that statement
>
> DB Error: syntax error
> CREATE INDEX on cache (mod_title) [nativecode=ERROR: parser: parse 
> error
> at or near "on" at character 14 ]
>
> David
>
>
>
> -------------------------------------------------------
> This SF.net email is sponsored by: SF.net Giveback Program.
> Does SourceForge.net help you be more productive?  Does it
> help you create better code?   SHARE THE LOVE, and help us help
> YOU!  Click Here: http://sourceforge.net/donate/
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>
>

Re: [Phpwebsite-developers] Postgres Support (making progress...)

[David M. <dma...@so...>]

On Wed, 2003-11-05 at 14:30, Richard Sumilang wrote:
> You forgot the cache_idx. You must name an index in order to create it.
> 
> - Richard Sumilang

I finally figured out that the error was occurring from where I had been
trying to replace line 769 in core/Database.php with a postgres
compatible version.  What finally seemed to work is replace the function
with

foreach ($columns as $item){  $sql = "CREATE INDEX idx on "
$this->tbl_prefix . "$table_name ($item)";
  if ($this->query($sql)) return TRUE;
  else return FALSE;
}

Now I get a error from line 484 where it performs a "show index" query.



> 
> On Nov 4, 2003, at 6:01 PM, David Macbanay wrote:
> 
> >>> biggest problem I've been having is in the "setup/install.sql" file. 
> >>>  I
> >>> do not know the equivalent syntax for lines such as
> >>> "index(mod_title,id)".  Someone has mentioned about removing these 
> >>> line
> >>
> >> I used:
> >>
> >> create index cache_idx on cache (mod_title, id);
> >>
> > Here is the error that I get when I use that statement
> >
> > DB Error: syntax error
> > CREATE INDEX on cache (mod_title) [nativecode=ERROR: parser: parse 
> > error
> > at or near "on" at character 14 ]
> >
> > David
> >
> >
> >
> > -------------------------------------------------------
> > This SF.net email is sponsored by: SF.net Giveback Program.
> > Does SourceForge.net help you be more productive?  Does it
> > help you create better code?   SHARE THE LOVE, and help us help
> > YOU!  Click Here: http://sourceforge.net/donate/
> > _______________________________________________
> > Phpwebsite-developers mailing list
> > Php...@li...
> > https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
> >
> >
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: SF.net Giveback Program.
> Does SourceForge.net help you be more productive?  Does it
> help you create better code?   SHARE THE LOVE, and help us help
> YOU!  Click Here: http://sourceforge.net/donate/
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers

[Phpwebsite-developers] Re: Postgres Support (making progress...)

[Brian G. <br...@vf...>]

David Macbanay writes:
> Now I get a error from line 484 where it performs a "show index" query.

I rewrote this too, but I don't know how well it will fare on multi-column 
indexes.  It works fine for the default case of a single primary key though: 

function sqlGetIndex($tableName){ 

   $tablename = $this->tbl_prefix . $tableName;
   $sql = "SELECT c2.relname, i.indisprimary, i.indisunique, 
pg_catalog.pg_get_indexdef(i.indexrelid) as column FROM pg_catalog.pg_class 
c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = (SELECT c.oid 
FROM pg_catalog.pg_class c WHERE c.relname ~ '^" . $tablename . "$') AND 
c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY i.indisprimary DESC, 
i.indisunique DESC, c2.relname";
//    echo $sql; 

   if ($tableInfo = $this->getAllAssoc($sql))
   { 

     if (strlen($tableInfo[0]["column"]) > 0)
     {
       $start = strpos($tableInfo[0]["column"], "(") + 1;
       $end = strpos($tableInfo[0]["column"], "(");
       return substr($tableInfo[0]["column"], $start, $end-$start);
     } 

   } 

   return NULL;
 }