01/19/11 10:21am  
Status: offline

Roccivic

Forum User
Moderator
Registered: 05/19/10
Posts: 136
I'm looking for some help with getting the equivalent PGSQL and MSSQL for a two MySQL queries.
This first query was already written by someone else and I just added the 'pi_load' field. I figure that it is best for this column to be an unsigned 16-bit integer that by default is assigned the maximum value that it can store. I think that the MySQL query is correct, but I'm unsure about the other two.

MySQL:
Text Formatted Code
CREATE TABLE {$_TABLES['plugins']} (
  pi_name varchar(30) NOT NULL default '',
  pi_version varchar(20) NOT NULL default '',
  pi_gl_version varchar(20) NOT NULL default '',
  pi_enabled tinyint(1) unsigned NOT NULL default '1',
  pi_homepage varchar(128) NOT NULL default '',
  pi_load smallint(5) unsigned NOT NULL default '65535',
  INDEX plugins_enabled(pi_enabled),
  PRIMARY KEY  (pi_name)
) ENGINE=MyISAM


MSSQL:
Text Formatted Code
CREATE TABLE [dbo].[{$_TABLES['plugins']}] (
    [pi_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pi_version] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pi_gl_version] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [pi_enabled] [tinyint] NOT NULL ,
    [pi_homepage] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [pi_load] [numeric](5, 65535) NOT NULL
) ON [PRIMARY]


PGSQL:
Text Formatted Code
CREATE TABLE {$_TABLES['plugins']} (
  pi_name varchar(30) NOT NULL default '',
  pi_version varchar(20) NOT NULL default '',
  pi_gl_version varchar(20) NOT NULL default '',
  pi_enabled smallint  NOT NULL default '1',
  pi_homepage varchar(128) NOT NULL default '',
  pi_load smallint NOT NULL default '65535',
  PRIMARY KEY  (pi_name)
  );
  CREATE INDEX plugins_enabled ON {$_TABLES['plugins']}(pi_enabled);



I want the second query to go into "{site_url}/sql/updates/mysql_1.7.1_to_1.8.0.php".
But I'm completely missing the MSSQL and PGSQL versions.

MySQL:
Text Formatted Code
ALTER TABLE {$_TABLES['plugins']} ADD pi_load smallint(5) unsigned NOT NULL default '65535';


Any help would be great, thanks.
 01/19/11 09:17pm  
Status: offline

Laugh

Site Admin
Admin
Registered: 09/27/05
Posts: 1475
Check the sql update files for the staticpage plugin. I believe they have a few ALTER TABLE statements in MSSQL and PGSQL that you can use for examples.

It is not as good as actually testing the SQL statements but it is a start.

One of the Geeklog Core Developers.
 01/20/11 10:18am  
Status: offline

Roccivic

Forum User
Moderator
Registered: 05/19/10
Posts: 136
Thanks Tom. Anyway, I guess that it's better to be safe than sorry, so I've installed pgsql-server on my PC and leached the required queries from an admin interface. Right now I'm virtualising Windows, so that I can install "SQL server 2005 express edition". Hopefully I'll be able to access it through NAT and somehow figure out the remaining query. Big Grin

Laters
 01/20/11 10:34am  
Status: offline

Laugh

Site Admin
Admin
Registered: 09/27/05
Posts: 1475
You may run into a few SQL Statement problems for PGSQL and MSSQL in other parts of Geeklog then. I know some of the later updates haven't been fully tested under these DBs. Though, as far as I know we have had no reports of problems from the community so things may be okay.
One of the Geeklog Core Developers.
 01/20/11 11:01am  
Status: offline

Roccivic

Forum User
Moderator
Registered: 05/19/10
Posts: 136
My experience so far is that every PGSQL query that starts with "CREATE INDEX" fails miserably on my machine complaining that the relation XYZ already exists. Anyway, indices are not required for any functionality, so I just removed every single one of these queries from "pgsql_tableanddata.php". Plugins contain these queries, too, but I wasn't too bothered so I left them there while testing.

The software that I used:
Ubuntu Linux 10.10 64-bit
Apache2 - v2.2.16
PHP - v5.3.3
Postgres - v8.4.6
 01/20/11 02:36pm  
Status: offline

Laugh

Site Admin
Admin
Registered: 09/27/05
Posts: 1475
I would report this in the bug tracker (seperate bugs for MSSQL and PGSQL) and add any additional problems you may find so we can get them ironed out for 1.8.0
One of the Geeklog Core Developers.
 01/20/11 05:53pm  
Status: offline

Roccivic

Forum User
Moderator
Registered: 05/19/10
Posts: 136
I managed to get SQL server working great in a virtual machine and got the mssql_* functions to come up in PHP. However, I couldn't manage to install Geeklog 1.7.1 at all. I verified that the database works fine and also executed some queries in a small PHP script. It looks like the problem is in "mssql.class.php". I'll post a bug report. :wink: