Welcome to Geeklog Saturday, October 21 2017 @ 10:58 pm EDT


Status: offline

Roccivic

Forum User
Moderator
Registered: 19/05/2010
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:
PHP 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:
PHP 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:
PHP 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:
PHP Formatted Code
ALTER TABLE {$_TABLES['plugins']} ADD pi_load smallint(5) unsigned NOT NULL default '65535';


Any help would be great, thanks.

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/2005
Posts: 1232
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.

Status: offline

Roccivic

Forum User
Moderator
Registered: 19/05/2010
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

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/2005
Posts: 1232
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.

Status: offline

Roccivic

Forum User
Moderator
Registered: 19/05/2010
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

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/2005
Posts: 1232
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.

Status: offline

Roccivic

Forum User
Moderator
Registered: 19/05/2010
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:

All times are EDT. The time is now 10:58 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