Re: [Gnump3d-users] Test the pre-release?

[Stuffed Crust]

On Thu, Aug 31, 2006 at 05:55:14PM +0100, Steve Kemp wrote:
>   The latter might be a challenge.  I'm wondering if it makes sense
>  to actually serve files by path in the future, since that is what
>  allows the basic-auth sidestepping.

Building in a proper auth model is going to get pretty complicated, and 
I'd prefer to not go there.

....

Your schema, while ultimately more flexible, is IMO overly complicated 
for what gnump3d needs.  I'd prefer to keep the DB as flat as possible, 
keeping with the "it's a web view of a directory of music" paradigm we 
work with already.  The primary key should be the path+music file.

CREATE TABLE gnump3d {
        db_version int NOT NULL -- so we can upgrade!
};

 -- It's worth mentioning that all of the below stuff can be 
 -- obtained via a stat() call.

CREATE TABLE files {
{
        id int UNIQUE NOT NULL, -- numeric ID

        fname text UNIQUE NOT NULL, -- full path and filename
        size integer NOT NULL,  -- in bytes
        mtime timestamp NOT NULL -- last modified time (via stat() call)
        -- type text -- (...mime type?)
};
        -- the below are extracted from the ID3 tag (or equivalent)

CREATE TABLE tags {
{
        int file_id NOT NULL UNIQUE PRIMARY KEY, -- references files.id

        length integer, -- in seconds
        year integer, 
        artist text, 
        comment text,
        title text,
        album text,
        genre text, -- may need to map id3v1->text here..?
        bitrate int -- eg "128" ? Personally I'd just drop this, 
                    -- as we can compute it from the SIZE and LENGTH fields
        -- add others?
};      

sample query:

SELECT files.*, tags.* 
  FROM files, tags 
 WHERE fname = "/Artists/P/Poets of the Fall/Late Goodbye.mp3" 
   AND tags.file_id = files.id;

and:

SELECT files.*, tags.* 
  FROM files, tags 
 WHERE tags.file_id = 12345 
   AND tags.file_id = files.id;

To future-proof this slightly, we could split the tags table apart 
like so:

CREATE TABLE tags {
        file_id int UNIQUE NOT NULL,
        tag_id int NOT NULL,
        tag_dat text NOT NULL   
}

CREATE TABLE tag_names {
        id int UNIQUE PRIMARY KEY NOT NULL,
        name text NOT NULL
}

queries:

SELECT * 
  FROM file 
 WHERE fname = "/Artists/P/Poets of the Fall/Late Goodbye.mp3";

SELECT tags.tag_dat as data, tag_names.text as name 
  FROM tags, tag_names
 WHERE tags.file_id = 12345
 WHERE tag_names.id = tags.tag_id;

>   selected?).  Plus sides is that we don't ever care about HTTP encoding
>   decoding, etc.

This is pretty trivial; URLEncoding is well-defined.

 - Solomon
-- 
Solomon Peachy                         pizza at shaftnet dot org         
Melbourne, FL                          ^^ (mail/jabber/gtalk) ^^
Quidquid latine dictum sit, altum viditur.          ICQ: 1318344

pgpL_MbmRJXbE.pgp
Description: PGP signature