Hi guy, am having a play with a script to convert the current mysql kohastructure.sql to postgresql etc.
My thinking is that any updates to the mysql kohastructure.sql could be handled by automatically by a script.

Firstly I have noticed that some table name and columns names are not quoted.
Could some one fix that the next time an edit is done.

Example

DROP TABLE IF EXISTS `branch_transfer_limits`;
CREATE TABLE branch_transfer_limits (
    limitId int(8) NOT NULL auto_increment,
    toBranch varchar(10) NOT NULL,
    fromBranch varchar(10) NOT NULL,
    itemtype varchar(10) NULL,
    ccode varchar(10) NULL,
    PRIMARY KEY (limitId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And

My postgresql database complains that there is no unique key on aqbookfund bookfundid
My thinking is this is cause by REFERENCES `aqbookfund` (`bookfundid`) which should be REFERENCES `aqbookfund` (`bookfundid`,`branchcode`)
in table aqorderbreakdown.

Does this need to be fixed or is bookfundid unique and justs needs a unique key?

DROP TABLE IF EXISTS `aqbookfund`;
CREATE TABLE `aqbookfund` (
`bookfundid` varchar(10) NOT NULL default '',
`bookfundname` mediumtext,
`bookfundgroup` varchar(5) default NULL,
`branchcode` varchar(10) NOT NULL default '',
PRIMARY KEY (`bookfundid`,`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `aqorderbreakdown`;
CREATE TABLE `aqorderbreakdown` (
`ordernumber` int(11) default NULL,
`linenumber` int(11) default NULL,
`branchcode` varchar(10) default NULL,
`bookfundid` varchar(10) NOT NULL default '',
`allocation` smallint(6) default NULL,
KEY `ordernumber` (`ordernumber`),
KEY `bookfundid` (`bookfundid`),
CONSTRAINT `aqorderbreakdown_ibfk_1` FOREIGN KEY (`ordernumber`) REFERENCES `aqorders` (`ordernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `aqorderbreakdown_ibfk_2` FOREIGN KEY (`bookfundid`) REFERENCES `aqbookfund` (`bookfundid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

++++++++++++++++++++++++++++++++++++++++

some more ramblings follows

I have been look at http://en.wikibooks.org/w/index.php?title=Programming:Converting_MySQL_to_PostgreSQL#Syntax
and like the idea of using SET sql_mode='ANSI_QUOTES' at the top of the mysql sql files to get rid of the ` qoute and use ".

The later versions of Postgresql has introduce new features that may make it possible koha to run on postgresql.

Enum example -

Mysql -
CREATE TABLE `import_items` (
`import_items_id` int(11) NOT NULL auto_increment,
`import_record_id` int(11) NOT NULL,
`itemnumber` int(11) default NULL,
`branchcode` varchar(10) default NULL,
`status` enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged',
`marcxml` longtext NOT NULL,
`import_error` mediumtext,
PRIMARY KEY (`import_items_id`),
CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
             REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
KEY `itemnumber` (`itemnumber`),
KEY `branchcode` (`branchcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Postgresql

CREATE TYPE enum_import_items_status AS ENUM ('error', 'staged', 'imported', 'reverted', 'ignored');

DROP TABLE IF EXISTS "import_items";
CREATE TABLE "import_items" (
"import_items_id" serial,
"import_record_id" integer NOT NULL,
"itemnumber" integer default NULL,
"branchcode" varchar(10) default NULL,
"status" enum_import_items_status NOT NULL default 'staged',
"marcxml" text NOT NULL,
"import_error" text ,
PRIMARY KEY ("import_items_id")
);
ALTER TABLE import_items ADD   CONSTRAINT "import_items_ibfk_1" FOREIGN KEY ("import_record_id")
REFERENCES "import_records" ("import_record_id") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX import_items_itemnumber_idx ON import_items ("itemnumber");
CREATE INDEX import_items_branchcode_idx ON import_items ("branchcode");
++++++++++++++++++++++++++++

Regards

Simon
Sydney, Australia