Welcome to Geeklog, Anonymous Tuesday, December 10 2024 @ 10:51 pm EST
Geeklog Forums
Query translation
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:
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.
11
11
Quote
Status: offline
Laugh
Site Admin
Admin
Registered: 09/27/05
Posts: 1470
Location:Canada
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.
It is not as good as actually testing the SQL statements but it is a start.
One of the Geeklog Core Developers.
14
10
Quote
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. 
Laters
Laters
9
11
Quote
Status: offline
Laugh
Site Admin
Admin
Registered: 09/27/05
Posts: 1470
Location:Canada
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.
One of the Geeklog Core Developers.
12
12
Quote
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
The software that I used:
Ubuntu Linux 10.10 64-bit
Apache2 - v2.2.16
PHP - v5.3.3
Postgres - v8.4.6
13
14
Quote
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:
11
10
Quote
All times are EST. The time is now 10:51 pm.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content