[Maypole] maypole-izing my DB

From: Seth Gordon (sethg at ropine.com)
Date: Tue Nov 23 2004 - 15:02:24 GMT


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