Merge Database & Podcast Database?

[hasezoey]

Should we in the future maybe merge these 2 databases or should they stay separate?

[tramhao]

Perhaps it could save some memory. I think merge them will be better.

[hasezoey]

i thought on starting to work on this, but wanted feedback with the database design before committing to it, what do you think about this database design?

Open questions:

• albums.title: TODO: should we force "NOT NULL"?
• files_album: TODO: should this maybe get integrated to the "files" table as a file can really only belong to one album?
• Question 1: should podcast author and music artist link to the same place or be separate?
• Question 2: should "created_at" and "modified_at" for local music files also be stored for quick search?
• Question 3: should "files.file" be split to "directory, file_stem, ext" instead of just the full path? (similar to the current library.db design)
• Question 4: should we do some kind of design where there is one single "last_position" table for both music and podcasts?

anything i missed or would be good to have now?

[hasezoey]

@tramhao, could you take a look at this if this is what we should go for?

[tramhao]

i thought on starting to work on this, but wanted feedback with the database design before committing to it, what do you think about this database design?

Open questions:

• albums.title: TODO: should we force "NOT NULL"?
  Album title should not be forced "NOT NULL" I think.
• files_album: TODO: should this maybe get integrated to the "files" table as a file can really only belong to one album?
  Yes, there shouldn't be a separate files_album.
• Question 1: should podcast author and music artist link to the same place or be separate?
  I think they should be the same.
• Question 2: should "created_at" and "modified_at" for local music files also be stored for quick search?
  These two are not necessary for music. They're from file, not meta data.
• Question 3: should "files.file" be split to "directory, file_stem, ext" instead of just the full path? (similar to the current library.db design)
  Sounds better to split.
• Question 4: should we do some kind of design where there is one single "last_position" table for both music and podcasts?

If databases are merged, one single last_position is better.

anything i missed or would be good to have now?

[hasezoey]

Question 3: should "files.file" be split to "directory, file_stem, ext" instead of just the full path? (similar to the current library.db design)

Sounds better to split.

I have looked through the code again, and directory and ext are basically unused and only name(file_stem) is used, but not for searching and could easily be replaced by just getting it from the full path.
What would we actually do with the split parts in the future? (if its directly search, wouldnt it be better to just do a readdir? and for extension search, should we offer something like that?)
The only thing i could think of this being useful is having the file_stem (or just full filename with extension?) as a fallback to searching for just a metadata title (which could be empty), but if we do this, wouldnt it be enough to split it into directory + filename (the filename is file_stem+extension, ie music.mp3).

Question 4: should we do some kind of design where there is one single "last_position" table for both music and podcasts?

If databases are merged, one single last_position is better.

I thought about this a bit, if we do one single table, how should we link them to the files? Have each their own link table (last_position_files(file, last_position_id) & last_position_podcasts(file, last_position_id & last_position()) or have one table with different fields (last_position(file, podcast))?
The more i think about it again, its actually sounds better to just have the last_position where it currently is.

---

Here is the current state with all the updates (except Question 3 & Question 4)

Also another new question as i dont remember exactly why i did this, should we merge files & files_metadata? (i think the original reason was to split "necessary" from "nullable" / non-necessary metadata)

[tramhao]

Question 3: should "files.file" be split to "directory, file_stem, ext" instead of just the full path? (similar to the current library.db design)

Sounds better to split.

I have looked through the code again, and directory and ext are basically unused and only name(file_stem) is used, but not for searching and could easily be replaced by just getting it from the full path. What would we actually do with the split parts in the future? (if its directly search, wouldnt it be better to just do a readdir? and for extension search, should we offer something like that?) The only thing i could think of this being useful is having the file_stem (or just full filename with extension?) as a fallback to searching for just a metadata title (which could be empty), but if we do this, wouldnt it be enough to split it into directory + filename (the filename is file_stem+extension, ie music.mp3).
In my mind, the database should store original information and later processing could be implemented by share functions. What do you think?

Question 4: should we do some kind of design where there is one single "last_position" table for both music and podcasts?

If databases are merged, one single last_position is better.

I thought about this a bit, if we do one single table, how should we link them to the files? Have each their own link table (last_position_files(file, last_position_id) & last_position_podcasts(file, last_position_id & last_position()) or have one table with different fields (last_position(file, podcast))? The more i think about it again, its actually sounds better to just have the last_position where it currently is.

I just found today that when I listen to podcasts, the last_position is not remembered(or wrongly matched). Haven't look into it. Perhaps leave them separate is easier for debugging?

Here is the current state with all the updates (except Question 3 & Question 4)

Also another new question as i dont remember exactly why i did this, should we merge files & files_metadata? (i think the original reason was to split "necessary" from "nullable" / non-necessary metadata)

I don't quite remember the usage of files_metadata. Need look into.

[Refael10ru]

Any updates about it/ways I can help it to progress?

[hasezoey]

I had not advanced it beyond a SQL "design" for now, as i had focused on some other places to understand better where / how the database is used, and maybe to even remove it in some places (by moving stuff to be server-side).
EDIT: see #425 (and continuation, once its finished) for what i meant

[hasezoey]

Part One, the re-implementation for the metadata of the tracks has been done in #511.