Re: Easy creation of input forms & reports for PostgreSql?

[Larry Holish <ljholish@speakeasy.net>]

On Sun, Apr 28, 2002 at 09:47:52AM -0400, stan wrote:
> I want to create a few databases to manage household inventory, music
> collection etc. I plan on using Postgres for this.
> 
> I was wondering if there were any easy tools to generate the user front
> ends (like forms in Oracle), and reports for this? If so, are the available
> as Debian packages?
 
Recently, I setup a very simple PostgreSQL database for my music
collection.  I didn't find any tools to do this on the web, but it was
easy enough to put something together.  I've included the SQL I used to
setup my music db below, which might give you some ideas and/or a
headstart.

I also wrote a quick/dirty python script (based on an example in Ben
Gertzfield's python-cddb Debian package) that will read a music CD,
query the FreeDB website for data, prompt the user for extra info, and
drop it all into my PostgreSQL database.  I'm willing to make the script
available if you're interested.

### included SQL defs

BEGIN;

/* Creates two tables for a personal music collection, related by
generated key rec_id. Larry Holish <ljholish@speakeasy.net> 3/28/02
*/

CREATE TABLE recordings (
	rec_id serial PRIMARY KEY,
	artist text NOT NULL,
	title text NOT NULL,
	label text,
	year text,
	format text CHECK (format IN ('CD','TAPE','LP')) DEFAULT 'CD',
	genre text,
	disc_ext text,
	total_length text,
	comments text,
	UNIQUE(artist,title,format)
);
COMMENT ON TABLE recordings IS 'Personal Music collection';
COMMENT ON column recordings.rec_id IS 'Primary key';
COMMENT ON column recordings.title IS 'Recording title';
COMMENT ON column recordings.artist IS 'Recording artist';
COMMENT ON column recordings.label IS 'Recording label';
COMMENT ON column recordings.year IS 'Recording release date';
COMMENT ON column recordings.format IS 'Recording format';
COMMENT ON column recordings.genre IS 'FreeDB database genre';
COMMENT ON column recordings.disc_ext IS 'FreeDB extended disc information';
COMMENT ON column recordings.total_length IS 'Total length of recording';
COMMENT ON column recordings.comments IS 'Comments';

CREATE TABLE tracks (
	rec_id int2 REFERENCES recordings ON DELETE CASCADE,
	track_title text,
	track_num int2,
	track_length text,
	track_ext text
);
COMMENT ON TABLE tracks IS 'Track information for each recording';
COMMENT ON column tracks.rec_id IS 'Foreign key that references rec_id';
COMMENT ON column tracks.track_title IS 'Track title';
COMMENT ON column tracks.track_num IS 'Track number';
COMMENT ON column tracks.track_length IS 'Track length in minutes:seconds';
COMMENT ON column tracks.track_ext IS 'FreeDB extended track information';

COMMIT;

-- 
Larry Holish
<ljholish@speakeasy.net>


-- 
To UNSUBSCRIBE, email to debian-user-request@lists.debian.org 
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org