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
_______________________________________________
Koha-devel mailing list
Koha-devel@lists.koha.org
http://lists.koha.org/mailman/listinfo/koha-devel
