| Subject: | CamelCase table name mismatch errors |
While trying to add AutoCRUD to my Catalyst app, it failed due to mismatched table name errors.
I was able produce the same symptoms by modifying the DemoApp sleeve_notes table name. Here are the AutoCRUD results from using 4 modifications of examples/sql/demo_app.sql to generate the __autocrud_demo_app.sqlite database:
CREATE TABLE sleeve_notes -- WORKS (original)
CREATE TABLE sleevenotes -- a. WORKS
CREATE TABLE sleeveNotes -- b. ERROR
Caught exception in DemoApp::Model::AutoCRUD::StorageEngine::DBIC->schema_metadata
"Error with filter 2 : mismatched (rel) table name between SQLT and DBIC: [sleevenotes, Result::SleeveNote]"
CREATE TABLE sleeveNote -- c. ERROR:
Caught exception in ...
mismatched (rel) table name between SQLT and DBIC: [sleevenote, SleeveNote]"
CREATE TABLE SleeveNote -- d. ERROR:
Caught exception in ...
mismatched (rel) table name between SQLT and DBIC: [sleevenote, Result::SleeveNote]"
Case "a" was to demonstrate to myself that editing only the demo_app.sql file did not damage the demo. It produces a ResultSource Schema class Sleevenote, whereas cases b,c, and d all generate SleeveNote, unchanged from the original.
DBIx::Class::Schema::Loader has several rules to map DB table names to schema (ResultSource) classes. Table names with underbars are converted to camel case and plural table names become singular. Cases b, c, d exhibit case mismatches and case b also differs singular to plural.
My environment:
Catalyst::Plugin::AutoCRUD: 2.200002
DBIx::Class::Schema::Loader: 0.07043
DBIx::Class: 0.082820
Linux 3.11.10-29-desktop
Thanks for looking into this,
Tom
| Subject: | demo_app.sql |
CREATE TABLE artist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
forename varchar(255) NOT NULL,
surname varchar(255) NOT NULL,
pseudonym varchar(255),
born date
);
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('Mike', 'Smith', 'Alpha Artist', '1970-02-28');
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('David', 'Brown', 'Band Beta', '1992-05-30');
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('Adam', 'Smith', 'Gamma Group', '1981-05-10');
-- album should get a column copyrights as many_to_many via the
-- tracks table. it should also have artists as many_to_many and
-- tracks as has_many.
CREATE TABLE album (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title varchar(255) NOT NULL,
recorded date,
deleted boolean DEFAULT 'false'
);
INSERT INTO album (title,recorded,deleted) VALUES ('DJ Mix 1', '1989-01-02', 'true');
INSERT INTO album (title,recorded,deleted) VALUES ('DJ Mix 2', '1989-02-02', 'true');
INSERT INTO album (title,recorded,deleted) VALUES ('DJ Mix 3', '1989-03-02', 'true');
INSERT INTO album (title,recorded,deleted) VALUES ('Pop Songs', '2007-05-30', 'false');
INSERT INTO album (title,recorded,deleted) VALUES ('Greatest Hits', '2002-05-21', 'false');
-- the link table album_artist should not appear as a column
-- in either of the refrenced tables (being detected as is_data)
CREATE TABLE album_artist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
album_id int REFERENCES album(id) NOT NULL,
artist_id int REFERENCES artist(id) NOT NULL
);
INSERT INTO album_artist (album_id,artist_id) VALUES (1,1);
INSERT INTO album_artist (album_id,artist_id) VALUES (2,1);
INSERT INTO album_artist (album_id,artist_id) VALUES (3,1);
INSERT INTO album_artist (album_id,artist_id) VALUES (2,3);
INSERT INTO album_artist (album_id,artist_id) VALUES (4,2);
INSERT INTO album_artist (album_id,artist_id) VALUES (5,1);
INSERT INTO album_artist (album_id,artist_id) VALUES (5,2);
INSERT INTO album_artist (album_id,artist_id) VALUES (5,3);
-- CREATE TABLE sleevenotes ( -- WORKS
-- CREATE TABLE sleeveNotes ( -- ERROR
-- Caught exception in DemoApp::Model::AutoCRUD::StorageEngine::DBIC->schema_metadata
-- "Error with filter 2 : mismatched (rel) table name between SQLT and DBIC: [sleevenotes, Result::SleeveNote]"
-- CREATE TABLE sleeveNote ( -- ERROR:
-- Caught exception in DemoApp::Model::AutoCRUD::StorageEngine::DBIC->schema_metadata
-- "Error with filter 2 : mismatched (rel) table name between SQLT and DBIC: [sleevenote, SleeveNote]"
-- CREATE TABLE SleeveNote ( -- ERROR:
-- Caught exception in DemoApp::Model::AutoCRUD::StorageEngine::DBIC->schema_metadata
-- "Error with filter 2 : mismatched (rel) table name between SQLT and DBIC: [sleevenote, Result::SleeveNote]"
CREATE TABLE sleeve_notes ( -- WORKS (original)
id INTEGER PRIMARY KEY AUTOINCREMENT,
text varchar(255) NOT NULL,
album_id int REFERENCES album(id) UNIQUE NOT NULL
);
-- INSERT INTO sleevenotes (text,album_id) VALUES ('This is a groovy album.', 1);
-- INSERT INTO sleeveNotes (text,album_id) VALUES ('This is a groovy album.', 1);
-- INSERT INTO sleeveNote (text,album_id) VALUES ('This is a groovy album.', 1);
-- INSERT INTO SleeveNote (text,album_id) VALUES ('This is a groovy album.', 1);
INSERT INTO sleeve_notes (text,album_id) VALUES ('This is a groovy album.', 1);
CREATE TABLE copyright (
id INTEGER PRIMARY KEY AUTOINCREMENT,
rights_owner varchar(255) NOT NULL,
copyright_year integer
);
INSERT INTO copyright (rights_owner, copyright_year) VALUES ('Label A', '1987');
INSERT INTO copyright (rights_owner, copyright_year) VALUES ('Label B', '1991');
INSERT INTO copyright (rights_owner, copyright_year) VALUES ('Label C', '2001');
CREATE TABLE track (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title varchar(255) NOT NULL,
length varchar(255),
album_id int REFERENCES album (id) NOT NULL,
copyright_id int REFERENCES copyright (id),
sales int,
releasedate date
);
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 1.1', '1:30', 1, 1, 5460000, '1994-04-05');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 1.2', '1:40', 1, 2, 1775000, '1995-01-15');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 1.3', '1:50', 1, 1, 2100000, '1989-08-18');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 2.1', '2:30', 2, 2, 153000, '1990-01-04');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 2.2', '2:40', 2, 1, 1020480, '1991-11-11');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 2.3', '2:50', 2, 2, 9625543, '1980-07-21');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 3.1', '3:30', 3, 1, 1953540, '1998-06-12');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 3.2', '3:40', 3, 2, 2668000, '1998-01-04');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Track 3.3', '3:50', 3, 1, 20000, '1999-11-14');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Pop Song One', '1:01', 4, 2, 2685000, '1995-01-04');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Hit Tune', '2:02', 5, 2, 1536000, '1990-11-06');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Hit Tune II', '3:03', 5, 2, 195300, '1990-11-06');
INSERT INTO track (title,length,album_id,copyright_id,sales,releasedate) VALUES ('Hit Tune 3', '4:04', 5, 2, 1623000, '1990-11-06');