Database

[Jeff Mitchell]

Quoting Alexandre Oliveira <aleprjlists at gmail.com>:
> I see your point about the urls, though. Maybe they should be actually
> be on the uniqueid table, and be a FK on the tags table? It would mean
> we'd always  need uniqueids for any url in the collection before we
> add it to the tags table. Sounds sensible to me, can it be a problem
> in any way?

I don't see why this would be a problem.  There is a uniqueid for  
every single file in the collection.  It's "more" unique if a file's  
taglib handler supports render()...especially if the file's tag is at  
the end of the file...but otherwise it's still generated via the first  
8192 bytes of the file and the length of the file.

Shash said:
"Just thought of another advantage of putting urls in the uniqueid  
table: Files
that are not taggable for whatever reasons (on CD, in wave format, whatever)
can be identified by uniqueid and we can just look up their tags in the
table."

This isn't quite the case as only files in the collection get a  
uniqueid.  So WAV files (which I believe we have a taglib helper for)  
will get one, but CD tracks, etc. will not (unless we do some funky  
KIO stuff with the audiocd:/ slave on the .cda files...maybe possible  
with seekable kioslaves in KDE4?)

--Jeff


>
> Also, anyone knows about that playlists table?
> Is the asin and locale enough to uniquely identify a amazon cover?
>
> On 2/23/07, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
>> If I understand the graph correctly, you have url and deviceid in   
>> uniqueid as
>> foreign keys.  This is not the case.  Although I don't understand FKs too
>> well as far as I understand it they're used to link data between two tables
>> to ensure that values are consistent (or is it that when you look   
>> at a linked
>> value you're actually looking at the same set of data as the referenced
>> table?).  I'm not against making it a foreign key (or rather, I'd need to
>> think about it a bit to make sure things would really be OK) of the  
>>  url field
>> in tags, though.  IIRC, the original reason that it wasn't a foreign key is
>> that data in the uniqueid table was going to be persistent even if a
>> particular track was removed from the collection.  As things progressed
>> further I realized that this wasn't the correct way to do things as the
>> values in that table could then get quickly out of hand.  So at   
>> this point it
>> not being a foreign key is (again, IIRC) just...because  :-)
>>
>> --Jeff
>>
>> On Thursday 22 February 2007, Alexandre Oliveira wrote:
>> > I wanted to start some discussion about the database on IRC yesterday,
>> > but as I didn't understand the database very much myself, I wasn't
>> > very successful.
>> >
>> > I didn't follow very much the two deep changes on the database we had
>> > recently, and which happened almost together, the Dynamic Collections
>> > and AFT.
>> > Both changed the way our tables link, and they added some (necessary)
>> > complexity.
>> >
>> > Anyway, as I was really bored and I wanted to do something, I decided
>> > to build a diagram of the current shape of our database. After trying
>> > some apps like Kivio and Dia I run back to ErWin, which's a commercial
>> > app for windows that I had been obliged to use in the past, and thus I
>> > was quite familiar with.
>> >
>> > As we don't define foreign keys (only one for the labels!), the
>> > current picture of the database was actually bizarre, and wouldn't
>> > help at all with the planning. So I just got the tables and added what
>> > I think the proper links should be. Max and Jeff, please check if this
>> > is actually what you planned.
>> > Note that I'm not suggesting this schema, it's just supposed to be
>> > what we have now with 1.4.
>> >
>> > The idea is using this current schema to discuss what we can do as
>> > improvements, and a picture helps a lot IMO.
>> >
>> > For people not familiar with the notation, everything on the first
>> > part of each table, together, forms the primary key. Don't be scared
>> > about indices formed of 4 values. :-)
>> > The icons just represent the type of the data, FK means Foreign Key,
>> > and AK in this case represents the non primary Unique Keys (AK stands
>> > for Alternate Keys, but we don't have any non unique key).
>> > For the linkings, circle means 0, -- means one, and the symbol with
>> > the 3 dashes means many.
>> > Dashed lines mean non identifying relations, ie, a relation that adds
>> > a value that isn't used to identify the record.
>> >
>> >
>> > Sadly litesql doesn't seem to be ready for us, so I fear we'll end up
>> > either using qt stuff, that doesn't actually help much, or coding the
>> > support ourselves (in a better way, hopefully).
>>
>>
>> _______________________________________________
>> Amarok-devel mailing list
>> Amarok-devel at kde.org
>> https://mail.kde.org/mailman/listinfo/amarok-devel
>>
> _______________________________________________
> Amarok-devel mailing list
> Amarok-devel at kde.org
> https://mail.kde.org/mailman/listinfo/amarok-devel
>