[Koha-devel] kohastructure.sql

[Simon Mitchell]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20090916/70c7613b/attachment-0003.htm>