trunk/scripts/create_instr_db.sql

CREATE TABLE instr_dirs (
    dir_id         INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_dir_id  INTEGER DEFAULT 0,
    dir_name       TEXT,
    created        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description    TEXT,
    FOREIGN KEY(parent_dir_id) REFERENCES instr_dirs(dir_id),
    UNIQUE (parent_dir_id,dir_name)
);

INSERT INTO instr_dirs (dir_id, parent_dir_id, dir_name) VALUES (0, 0, "/");

CREATE TABLE instruments (
    instr_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    dir_id          INTEGER DEFAULT 0,
    instr_name      TEXT,
    instr_file      TEXT,
    instr_nr        INTEGER,
    format_family   TEXT,
    format_version  TEXT,
    instr_size      INTEGER,
    created         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description     TEXT,
    is_drum         INTEGER(1),
    product         TEXT,
    artists         TEXT,
    keywords        TEXT,
    FOREIGN KEY(dir_id) REFERENCES instr_dirs(dir_id),
    UNIQUE (dir_id,instr_name)
);
1	iliev	1161	CREATE TABLE instr_dirs (
2			dir_id INTEGER PRIMARY KEY AUTOINCREMENT,
3			parent_dir_id INTEGER DEFAULT 0,
4			dir_name TEXT,
5			created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
6			modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7			description TEXT,
8			FOREIGN KEY(parent_dir_id) REFERENCES instr_dirs(dir_id),
9			UNIQUE (parent_dir_id,dir_name)
10			);
11
12			INSERT INTO instr_dirs (dir_id, parent_dir_id, dir_name) VALUES (0, 0, "/");
13
14			CREATE TABLE instruments (
15			instr_id INTEGER PRIMARY KEY AUTOINCREMENT,
16			dir_id INTEGER DEFAULT 0,
17			instr_name TEXT,
18			instr_file TEXT,
19			instr_nr INTEGER,
20			format_family TEXT,
21			format_version TEXT,
22			instr_size INTEGER,
23			created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
24			modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
25			description TEXT,
26			is_drum INTEGER(1),
27			product TEXT,
28			artists TEXT,
29			keywords TEXT,
30			FOREIGN KEY(dir_id) REFERENCES instr_dirs(dir_id),
31			UNIQUE (dir_id,instr_name)
32			);