<snip/>
But the .script file *is* actually a simple .sql script.Yes, but as I remember it, wouldn't be appropriate to use as the source in other DB's, but...
Can you remember why? I haven't really looked closer into it.
Well, it was the stuff like:
GRANT ALL ON CLASS "org.hsqldb.Library" TO PUBLIC GRANT ALL ON CLASS "java.lang.Math" TO PUBLIC CREATE USER SA PASSWORD "" ADMIN CREATE ALIAS DAYNAME FOR "org.hsqldb.Library.dayname" CREATE ALIAS SPACE FOR "org.hsqldb.Library.space" CREATE ALIAS SUBSTRING FOR "org.hsqldb.Library.substring" CREATE ALIAS SQRT FOR "java.lang.Math.sqrt" CREATE ALIAS ABS FOR "java.lang.Math.abs" ...
That I wouldn't want running in my db. However, looking back at your original suggestion about merging the base hsql with the samples, there may not be a problem. We could use just:
CREATE TABLE DEPARTMENT(ID INTEGER NOT NULL,NAME VARCHAR NOT NULL,UNIQUE(ID))
CREATE TABLE EMPLOYEE(ID INTEGER NOT NULL,DEPARTMENT_ID INTEGER NOT NULL,NAME VARCHAR NOT NULL,UNIQUE(ID))
CREATE TABLE USER(UID INTEGER IDENTITY PRIMARY KEY,NAME VARCHAR,FIRSTNAME VARCHAR,UNAME VARCHAR,UNIQUE(UNAME))
CREATE TABLE GROUPS(GID INTEGER IDENTITY PRIMARY KEY,GNAME VARCHAR,UNIQUE(GNAME))
CREATE TABLE USER_GROUPS(UID INTEGER,GID INTEGER,UNIQUE(UID,GID),FOREIGN KEY(UID)REFERENCES USER(UID),FOREIGN KEY(GID)REFERENCES GROUPS(GID))
CREATE TABLE STATE_TAX(CATEGORY VARCHAR NOT NULL,GROSSTAX_COLLECTED DOUBLE NOT NULL,NETTAX_COLLECTED DOUBLE NOT NULL,YEAR INTEGER NOT NULL)
CREATE TABLE MEDIA(ID INTEGER IDENTITY PRIMARY KEY,IMAGE BINARY,MIMETYPE VARCHAR)
INSERT INTO DEPARTMENT VALUES(1,'Development') INSERT INTO DEPARTMENT VALUES(2,'Management') INSERT INTO DEPARTMENT VALUES(3,'Testers') INSERT INTO EMPLOYEE VALUES(1,1,'Donald Ball') INSERT INTO EMPLOYEE VALUES(2,1,'Sylvain Wallez ') INSERT INTO EMPLOYEE VALUES(3,1,'Carsten Ziegeler ') ...
The only question it seem to me being, does hsql honor vanilla sql _here_, or do things have to be created in some subset/flavor specific to hsql?
The only reason approaches like this would have any value would be to facilitate using other than hsql db for the samples. If I already have mysql installed, I may
choose to exclude the hsql block.
Don't understand this. Why would you want to try the samples in your db?
I don't really see why we should take the burden on making sure that our
sql creates all tables and data successfully on database X just because
the user wants to run the samples on his database out of the box.
(I mean it's very basic sql - it should not. But I wouldn't take a bet on any datatype or the primary key definitions. I've seen too much crap out there;)
Don't take me wrong. I am not against this - but atleast *I* just cannot see the real value yet. Please tell me :)
It's OK, I don't even know if I'm for it! ;) Seriously, I guess Chris has echoed that it has been a frequent request, and it seems reasonable to me. But admittedly that's not a very compelling case for a lot of work. I see two separate questions:
1) Should we take steps to make it easier to run the samples in another database.
2) Should we automate it.
I'd say the first is worth some effort, and the second probably not if they can be separated.
So, if we have a samples.sql that gets merged into db.script via ant for the database block using hsql by default and can also be used to manually create the appropriate tables and sample data in your db of choice, that would accomplish #1 while stopping short of #2.
Does that KISS?
BTW - there would need to be some logic to determine during the database build whether the hsql block was installed (and it would need to have been processed first, right?) before attempting to merge this script with a file created by the hsql block. Or am I missing something?
Geoff
cheers -- Torsten
