Forum Subject: Query translation
Posted on: 01/19/11 10:21am
By: Roccivic
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:
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:
[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:
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:
Any help would be great, thanks.
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.
Re: Query translation
Posted on: 01/19/11 09:17pm
By: Laugh
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.
It is not as good as actually testing the SQL statements but it is a start.
Re: Query translation
Posted on: 01/20/11 10:18am
By: Roccivic
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. 
Laters
Laters
Re: Query translation
Posted on: 01/20/11 10:34am
By: Laugh
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.
Re: Query translation
Posted on: 01/20/11 11:01am
By: Roccivic
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
The software that I used:
Ubuntu Linux 10.10 64-bit
Apache2 - v2.2.16
PHP - v5.3.3
Postgres - v8.4.6
Re: Query translation
Posted on: 01/20/11 02:36pm
By: Laugh
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
Re: Query translation
Posted on: 01/20/11 05:53pm
By: Roccivic
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: