Difference between revisions of "User:DrewJensen/HSQLdb/TestDB"

From Apache OpenOffice Wiki
Jump to: navigation, search
(How)
 
(→‎schema)
 
(One intermediate revision by the same user not shown)
Line 1: Line 1:
 +
Language and Locale.
 +
 +
I think we can tackle this by using scripts to build the actual ODB files.
 +
HSQLdb will pick up the system locale when the file is created.
 +
A smaller set of languages, for object names, covers a much larger set of locales.
 +
(en-US, en-GB, en-NZ...for example)
  
 
How to distrubute:
 
How to distrubute:
Line 4: Line 10:
 
OXT  
 
OXT  
 
- used to distribute basic libarary
 
- used to distribute basic libarary
 +
- really simple interface via a dialog box
 +
- create db
  
 
BAS  
 
BAS  
−
- create a new Base file, in users locale
+
- create a new Base file
 
- create database schema from script
 
- create database schema from script
  
Line 20: Line 28:
  
 
Simple.
 
Simple.
 +
 +
 +
10/17 -
 +
 +
Another way of approaching a test database is to...hmmm...port it.
 +
 +
Sakila is the latest test db for MySQL (or at least a recent one)
 +
 +
I've downloaded version 0.8 of the database and moved the first few tables (no triggers yet) into a Base file.
 +
 +
LOL - well, that's right still can't upload an ODB file...really no one has asked for this till now...I'm missing something, must be.
 +
 +
Till I learn what that is - the file as script:
 +
 +
*LOL* - and you can't upload a file named Sakila-hsql19-01.sql either....
 +
 +
How about this for the moment and I'll send an email to Clayton about odb files...
 +
<source lang="sql">
 +
SET DATABASE UNIQUE NAME HSQLDB2461286D8B
 +
SET DATABASE GC 0
 +
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
 +
SET DATABASE EVENT LOG LEVEL 0
 +
SET DATABASE SQL SIZE TRUE
 +
SET DATABASE SQL NAMES FALSE
 +
SET DATABASE TRANSACTION CONTROL LOCKS
 +
SET DATABASE DEFAULT TABLE TYPE CACHED
 +
SET FILES WRITE DELAY 60
 +
SET FILES BACKUP INCREMENT TRUE
 +
SET FILES CACHE SIZE 6000
 +
SET FILES CACHE ROWS 25000
 +
SET FILES SCALE 1
 +
SET FILES DEFRAG 20
 +
SET FILES NIO FALSE
 +
SET FILES LOG SIZE 10
 +
SET DATABASE TEXT TABLE DEFAULTS ''
 +
SET DATABASE COLLATION "Latin1_General"
 +
CREATE USER SA PASSWORD ''
 +
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
 +
SET SCHEMA PUBLIC
 +
CREATE CACHED TABLE PUBLIC.ACTOR(ACTOR_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR(45) NOT NULL,LAST_NAME VARCHAR(45) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
 +
ALTER TABLE PUBLIC.ACTOR ALTER COLUMN ACTOR_ID RESTART WITH 0
 +
CREATE INDEX IDX_ACTOR_LAST_NAME ON PUBLIC.ACTOR(LAST_NAME)
 +
CREATE CACHED TABLE PUBLIC.ADDRESS(ADDRESS_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ADDRESS VARCHAR(50) NOT NULL,ADDRESS2 VARCHAR(50) DEFAULT NULL,DISTRICT VARCHAR(20) NOT NULL,CITY_ID INTEGER NOT NULL,POSTAL_CODE VARCHAR(10) DEFAULT NULL,PHONE VARCHAR(20) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
 +
ALTER TABLE PUBLIC.ADDRESS ALTER COLUMN ADDRESS_ID RESTART WITH 0
 +
CREATE INDEX IDX_FK_CITY_ID ON PUBLIC.ADDRESS(CITY_ID)
 +
CREATE CACHED TABLE PUBLIC.CATEGORY(CATEGORY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(25) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
 +
ALTER TABLE PUBLIC.CATEGORY ALTER COLUMN CATEGORY_ID RESTART WITH 0
 +
CREATE CACHED TABLE PUBLIC.CITY(CITY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,CITY VARCHAR(50) NOT NULL,COUNTRY_ID INTEGER NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
 +
ALTER TABLE PUBLIC.CITY ALTER COLUMN CITY_ID RESTART WITH 0
 +
CREATE INDEX IDX_FK_COUNTRY_ID ON PUBLIC.CITY(COUNTRY_ID)
 +
CREATE CACHED TABLE PUBLIC.COUNTRY(COUNTRY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,COUNTRY VARCHAR(50) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
 +
ALTER TABLE PUBLIC.COUNTRY ALTER COLUMN COUNTRY_ID RESTART WITH 0
 +
CREATE CACHED TABLE PUBLIC.CUSTOMER(CUSTOMER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,STORE_ID INTEGER NOT NULL,FIRST_NAME VARCHAR(45) NOT NULL,LAST_NAME VARCHAR(45) NOT NULL,EMAIL VARCHAR(50) DEFAULT NULL,ADDRESS_ID INTEGER NOT NULL,ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,CREATE_DATE TIMESTAMP NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT FK_CUSTOMER_ADDRESS FOREIGN KEY(ADDRESS_ID) REFERENCES PUBLIC.ADDRESS(ADDRESS_ID) ON UPDATE CASCADE)
 +
ALTER TABLE PUBLIC.CUSTOMER ALTER COLUMN CUSTOMER_ID RESTART WITH 0
 +
CREATE INDEX IDX_FK_STORE_ID ON PUBLIC.CUSTOMER(STORE_ID)
 +
CREATE INDEX IDX_FK_ADDRESS_ID ON PUBLIC.CUSTOMER(ADDRESS_ID)
 +
CREATE INDEX IDX_LAST_NAME ON PUBLIC.CUSTOMER(LAST_NAME)
 +
ALTER TABLE PUBLIC.ADDRESS ADD CONSTRAINT FK_ADDRESS_CITY FOREIGN KEY(CITY_ID) REFERENCES PUBLIC.CITY(CITY_ID) ON UPDATE CASCADE
 +
ALTER TABLE PUBLIC.CITY ADD CONSTRAINT FK_CITY_COUNTRY FOREIGN KEY(COUNTRY_ID) REFERENCES PUBLIC.COUNTRY(COUNTRY_ID) ON UPDATE CASCADE
 +
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
 +
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
 +
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
 +
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
 +
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
 +
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
 +
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
 +
GRANT DBA TO SA
 +
 +
</source>

Latest revision as of 00:04, 18 October 2009

Language and Locale.

I think we can tackle this by using scripts to build the actual ODB files. HSQLdb will pick up the system locale when the file is created. A smaller set of languages, for object names, covers a much larger set of locales. (en-US, en-GB, en-NZ...for example)

How to distrubute:

OXT - used to distribute basic libarary - really simple interface via a dialog box - create db

BAS - create a new Base file - create database schema from script

Should support multiple Schema scripts. Each Schema should be represented by multiple scripts, with object names in native languages.

The OO.o wizard will allow us to do this. Each person needs only to create, via the gui and using the wizard, a common set of tables. The tables and columns will be created with NL names. Have the person extract the script file, or send the odb file to ...guess it's me...to be assembled into the BAS file.

Use BasicAddonBuilder to generate the OXT file..

Simple.


10/17 -

Another way of approaching a test database is to...hmmm...port it.

Sakila is the latest test db for MySQL (or at least a recent one)

I've downloaded version 0.8 of the database and moved the first few tables (no triggers yet) into a Base file.

LOL - well, that's right still can't upload an ODB file...really no one has asked for this till now...I'm missing something, must be.

Till I learn what that is - the file as script:

  • LOL* - and you can't upload a file named Sakila-hsql19-01.sql either....

How about this for the moment and I'll send an email to Clayton about odb files... 

SET DATABASE UNIQUE NAME HSQLDB2461286D8B
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL NAMES FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT TABLE TYPE CACHED
SET FILES WRITE DELAY 60
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 6000
SET FILES CACHE ROWS 25000
SET FILES SCALE 1
SET FILES DEFRAG 20
SET FILES NIO FALSE
SET FILES LOG SIZE 10
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE COLLATION "Latin1_General"
CREATE USER SA PASSWORD ''
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE CACHED TABLE PUBLIC.ACTOR(ACTOR_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR(45) NOT NULL,LAST_NAME VARCHAR(45) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.ACTOR ALTER COLUMN ACTOR_ID RESTART WITH 0
CREATE INDEX IDX_ACTOR_LAST_NAME ON PUBLIC.ACTOR(LAST_NAME)
CREATE CACHED TABLE PUBLIC.ADDRESS(ADDRESS_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ADDRESS VARCHAR(50) NOT NULL,ADDRESS2 VARCHAR(50) DEFAULT NULL,DISTRICT VARCHAR(20) NOT NULL,CITY_ID INTEGER NOT NULL,POSTAL_CODE VARCHAR(10) DEFAULT NULL,PHONE VARCHAR(20) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.ADDRESS ALTER COLUMN ADDRESS_ID RESTART WITH 0
CREATE INDEX IDX_FK_CITY_ID ON PUBLIC.ADDRESS(CITY_ID)
CREATE CACHED TABLE PUBLIC.CATEGORY(CATEGORY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,NAME VARCHAR(25) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.CATEGORY ALTER COLUMN CATEGORY_ID RESTART WITH 0
CREATE CACHED TABLE PUBLIC.CITY(CITY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,CITY VARCHAR(50) NOT NULL,COUNTRY_ID INTEGER NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.CITY ALTER COLUMN CITY_ID RESTART WITH 0
CREATE INDEX IDX_FK_COUNTRY_ID ON PUBLIC.CITY(COUNTRY_ID)
CREATE CACHED TABLE PUBLIC.COUNTRY(COUNTRY_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,COUNTRY VARCHAR(50) NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)
ALTER TABLE PUBLIC.COUNTRY ALTER COLUMN COUNTRY_ID RESTART WITH 0
CREATE CACHED TABLE PUBLIC.CUSTOMER(CUSTOMER_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,STORE_ID INTEGER NOT NULL,FIRST_NAME VARCHAR(45) NOT NULL,LAST_NAME VARCHAR(45) NOT NULL,EMAIL VARCHAR(50) DEFAULT NULL,ADDRESS_ID INTEGER NOT NULL,ACTIVE BOOLEAN DEFAULT TRUE NOT NULL,CREATE_DATE TIMESTAMP NOT NULL,LAST_UPDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,CONSTRAINT FK_CUSTOMER_ADDRESS FOREIGN KEY(ADDRESS_ID) REFERENCES PUBLIC.ADDRESS(ADDRESS_ID) ON UPDATE CASCADE)
ALTER TABLE PUBLIC.CUSTOMER ALTER COLUMN CUSTOMER_ID RESTART WITH 0
CREATE INDEX IDX_FK_STORE_ID ON PUBLIC.CUSTOMER(STORE_ID)
CREATE INDEX IDX_FK_ADDRESS_ID ON PUBLIC.CUSTOMER(ADDRESS_ID)
CREATE INDEX IDX_LAST_NAME ON PUBLIC.CUSTOMER(LAST_NAME)
ALTER TABLE PUBLIC.ADDRESS ADD CONSTRAINT FK_ADDRESS_CITY FOREIGN KEY(CITY_ID) REFERENCES PUBLIC.CITY(CITY_ID) ON UPDATE CASCADE
ALTER TABLE PUBLIC.CITY ADD CONSTRAINT FK_CITY_COUNTRY FOREIGN KEY(COUNTRY_ID) REFERENCES PUBLIC.COUNTRY(COUNTRY_ID) ON UPDATE CASCADE
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
Retrieved from "https://wiki.openoffice.org/w/index.php?title=User:DrewJensen/HSQLdb/TestDB&oldid=143310"
Personal tools