Dischi wrote:
>So you have one table for a file and one for the attributes? Isn't
>that slow? How do you get a list of all files in a dir?
select fileid from file where url="file:\\mnt\data\my-mp3-dir"
X=fileid
select * from file where dirid = X
It's actually quite simular to the current mediadb.
All attributes needed for immediate display are stored in the file table.
Only fields/attributes that all files and directory's share are stored
in the file table.
(perhaps "cover"/"extra_cover" should be added)
If extra attributes are needed , then they are extracted from the
file_attr table.
Like the current mediadb that defers the extracting of the mminfo pickle.
Usually this is on selection of an item.
>Dischi, never created a database schema before
Well, this is not really a normalized schema..
The usual way would be 1 table per media-type and a fixed set of
fields per media type.
But that's no fun ;-).
And a key requirement for freevo is that any attribute can be added to
the database.
This schema is more like the current mediadb.
Martijn.
On 6/20/05, Dirk Meyer <[EMAIL PROTECTED]> wrote:
> Martijn Voncken wrote:
> > Hi,
> >
> > See previous topic: "media database , smartplaylists,albumtree etc"
> >
> > The current schema I'm testing:
> >
> > create table media (mediaid integer primary key,medianame character);
> > create table attr (attrid integer primary key,attrname character);
> >
> > create table file (fileid integer primary key
> > ,mediaid integer default 0,dirid integer default null
> > ,filename character,mtime integer default null
> > ,url character unique,removable integer default 0,title
> character);
> > create index ix_file_dirid on file (dirid);
> > insert into file (fileid,dirid,filename,title,url)
> > values(0,null,'','Root','file:///');
> >
> > create table file_attr (fileid integer,mediaid integer,attrid
> > integer,value character);
> > create unique index ix_file_attr on file_attr (fileid,attrid);
> > create index ix_file_attr_media_val on file_attr (mediaid,attrid,value);
> > /*
> > note that unique and primary fields create an (invisible) index.
> > dirid = fileid of parent directory.
> > mediaid = {0:dir,1:image,etc..}
> > */
>
> I have no idea what you are talking about :)
>
> So you have one table for a file and one for the attributes? Isn't
> that slow? How do you get a list of all files in a dir?
>
>
> Dischi, never created a database schema before
>
> --
> Unix is the worst operating system; except for all others.
> -- Berry Kercheval
>
>
-------------------------------------------------------
SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
from IBM. Find simple to follow Roadmaps, straightforward articles,
informative Webcasts and more! Get everything you need to get up to
speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id492&op=click
_______________________________________________
Freevo-devel mailing list
Freevo-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/freevo-devel
