A while ago, I set up a PostgreSQL database and a few scripts for
maintaining our home library
(http://dynamic.ropine.com/jeeves/library/). I'd like to convert this
to a Maypole application so I can add more features to the system
without typing oodles of raw SQL. The first time I tried this (with
Maypole 1.7), I got errors about some of the tables not having primary
keys. (The tables in question were only being used for joins in
many-to-many operations, so I had not bothered giving them primary keys
in setting up the schema.) Also, the table and column names don't match
the pattern shown in the beer DB: instead of having a "title" table with
an "id" field, I have a "titles" table with a "title_id" field.
I tried setting up a SQLite database with more Maypole-friendly column
names and migrating the contents of the PostgreSQL database over, but I
keep getting "Incomplete SQL" errors when I try to read the contents of
the old file. So maybe I'm better off fixing the PostgreSQL database in
place.
Before I try this again with the latest version of Maypole, I'd like to
know what I need to do with the schema to make it acceptable, and what
special configuration commands I need in my application module to
explain my naming conventions to Maypole. (I see there's some stuff in
Class::DBI for defining how column names are translated to object
properties, but I'm not sure how Maypole interacts with that part of
Class::DBI.)
Here are the relevant parts of the schema:
CREATE TABLE titles (
title_id integer DEFAULT nextval('"titles_title_id_seq"'::text) NOT
NULL,
title character varying(300) NOT NULL,
is_public "char" NOT NULL,
veracity "char" NOT NULL,
unity "char" NOT NULL
);
CREATE TABLE authors (
author_id integer DEFAULT nextval('"authors_author_id_seq"'::text)
NOT NULL,
name character varying(100) NOT NULL
);
CREATE TABLE title_author_rel (
title_id integer,
author_id integer,
relationship character(6) DEFAULT 'by'::bpchar
);
CREATE TABLE volumes (
volume_id integer DEFAULT nextval('"volumes_volume_id_seq"'::text)
NOT NULL,
title_id integer,
volume_num smallint DEFAULT 1,
copy_num smallint DEFAULT 1,
binding "char"
);
CREATE TABLE language_names (
language_primary_subtag character(8) NOT NULL,
english_name character varying(40)
);
CREATE TABLE book_language (
title_id integer,
language_primary_subtag character(8) DEFAULT 'und'::bpchar,
language_subtag character(8),
id integer DEFAULT nextval('book_language_id_seq'::text)
);
CREATE TABLE dialect_names (
language_subtag character(8) NOT NULL,
english_name character varying(40) NOT NULL,
language_primary_subtag character(8)
);
ALTER TABLE ONLY titles
ADD CONSTRAINT titles_pkey PRIMARY KEY (title_id);
ALTER TABLE ONLY authors
ADD CONSTRAINT authors_pkey PRIMARY KEY (author_id);
ALTER TABLE ONLY volumes
ADD CONSTRAINT volumes_pkey PRIMARY KEY (volume_id);
ALTER TABLE ONLY language_names
ADD CONSTRAINT language_names_pkey PRIMARY KEY
(language_primary_subtag);
-- "If John Kerry was the last best hope for the Republic ... then maybe it wasn't worth saving in the first place. I don't believe that, though. And neither do you." --Dan Layman-Kennedy // seth gordon // sethg at ropine.com // http://dynamic.ropine.com/yo/ //_______________________________________________ maypole mailing list maypole at lists.netthink.co.uk http://lists.netthink.co.uk/listinfo/maypole
This archive was generated by hypermail 2.1.3 : Thu Feb 24 2005 - 22:25:57 GMT