Hi,
Thanks for the detailed reply. I have now *almost* got this working
and suspect the remaining issues are bugs in my own code. Your reply
was very helpful, as was looking again at the examples (particularly
the advanced example, which I had not paid attention to earlier).
Anyway, I am replying now (before I have everything working) partly
because I didn't want to stay silent for so long before saying
thank-you, and also because I have hit a couple of issues that you
might want to look at:
1 - it seems that the API documentation on the web site isn't the same
as the documentation in the download (for example, the
org.apache.empire.data.beans package is missing).
2 - it seems that some of the SQL related code is inconsistent about
quoting names. I initially used lower case names for everything, but
then had an error because they are not quoted during database creation
(so get changed to upper case) BUT the sequence for for HSQL does
quote the sequence name (which is then not found because it is lower
case).
Now I just need to fix a couple more bugs in my code and I think
things will work...
Thanks again,
Andrew
PS Here is the case-related error (note the quotation of the sequence name):
org.acooke.uykfd.db.Schema$: No records found for query SELECT NEXT
VALUE FOR canonicals_seq FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES
WHERE SEQUENCE_NAME='canonicals_seq'
at
org.apache.empire.commons.ErrorObject.internalSetError(ErrorObject.java:255)
at org.apache.empire.commons.ErrorObject.error(ErrorObject.java:314)
at org.apache.empire.commons.ErrorObject.error(ErrorObject.java:336)
at org.apache.empire.db.DBDatabase.querySingleValue(DBDatabase.java:655)
at
org.apache.empire.db.hsql.DBDatabaseDriverHSql.getNextSequenceValue(DBDatabaseDriverHSql.java:236)
at
org.apache.empire.db.DBDatabase.getNextSequenceValue(DBDatabase.java:619)
at
org.apache.empire.db.DBTableColumn.getRecordDefaultValue(DBTableColumn.java:136)
at org.apache.empire.db.DBRowSet.updateRecord(DBRowSet.java:659)
at org.apache.empire.db.DBRecord.update(DBRecord.java:719)
And here is the schema when I used lower case (as I said, this all
works if I use upper case names):
-- creating sequence for column tracks.id --
CREATE SEQUENCE track_id_seq START WITH 1;
-- creating sequence for column artist_tags.id --
CREATE SEQUENCE artist_tags_seq START WITH 1;
-- creating sequence for column canonicals.id --
CREATE SEQUENCE canonicals_seq START WITH 1;
-- creating sequence for column album_tags.id --
CREATE SEQUENCE album_tags_seq START WITH 1;
-- creating sequence for column song_title_tags.id --
CREATE SEQUENCE song_title_tags_seq START WITH 1;
-- creating table tracks --
CREATE TABLE tracks (
id BIGINT NOT NULL,
artist_tag BIGINT NOT NULL,
album_tag BIGINT NOT NULL,
song_title_tag BIGINT NOT NULL,
track_number_tag BIGINT NOT NULL,
path VARCHAR(100) NOT NULL,
CONSTRAINT tracks_PK PRIMARY KEY (id));
-- creating table artist_tags --
CREATE TABLE artist_tags (
id BIGINT NOT NULL,
value VARCHAR(100) NOT NULL,
canonical BIGINT NOT NULL,
CONSTRAINT artist_tags_PK PRIMARY KEY (id));
-- creating table canonicals --
CREATE TABLE canonicals (
id BIGINT NOT NULL,
value VARCHAR(100) NOT NULL,
CONSTRAINT canonicals_PK PRIMARY KEY (id));
-- creating table album_tags --
CREATE TABLE album_tags (
id BIGINT NOT NULL,
value VARCHAR(100) NOT NULL,
canonical BIGINT NOT NULL,
CONSTRAINT album_tags_PK PRIMARY KEY (id));
-- creating table song_title_tags --
CREATE TABLE song_title_tags (
id BIGINT NOT NULL,
value VARCHAR(100) NOT NULL,
canonical BIGINT NOT NULL,
CONSTRAINT song_title_tags_PK PRIMARY KEY (id));
-- creating foreign key constraint artist_tags_canonical_FK --
ALTER TABLE artist_tags ADD CONSTRAINT artist_tags_canonical_FK
FOREIGN KEY (canonical) REFERENCES canonicals (id);
-- creating foreign key constraint tracks_artist_tag_FK --
ALTER TABLE tracks ADD CONSTRAINT tracks_artist_tag_FK FOREIGN KEY
(artist_tag) REFERENCES artist_tags (id);
-- creating foreign key constraint album_tags_canonical_FK --
ALTER TABLE album_tags ADD CONSTRAINT album_tags_canonical_FK FOREIGN
KEY (canonical) REFERENCES canonicals (id);
-- creating foreign key constraint tracks_album_tag_FK --
ALTER TABLE tracks ADD CONSTRAINT tracks_album_tag_FK FOREIGN KEY
(album_tag) REFERENCES album_tags (id);
-- creating foreign key constraint song_title_tag_canonical_FK --
ALTER TABLE song_title_tags ADD CONSTRAINT song_title_tag_canonical_FK
FOREIGN KEY (canonical) REFERENCES canonicals (id);
-- creating foreign key constraint tracks_song_title_t_FK --
ALTER TABLE tracks ADD CONSTRAINT tracks_song_title_t_FK FOREIGN KEY
(song_title_tag) REFERENCES song_title_tags (id);
