For those playing at home.
database access is actually pretty easy, using the ij utility from
http://db.apache.org/derby/derby_downloads.htmlyou then connect to the database, since serviio runs in embedded mode you need to shutdown serviio before you can connect else it will error. I've copied mine to a different folder while i play.
- Code:
connect 'jdbc:derby:d:\tmp\db\db';
will give you a command reference but the ones you need to know.
You can take a pretty good guess on what each of them hold, the one that i have been interested at the moment is MEDIA_ITEM
You can view the entire media database with
- Code:
SELECT * FROM MEDIA_ITEM;
you might want to widen your command prompt window as well to make it easier to read. This will output every file and every column.
you can get the column names with
- Code:
DESCRIBE MEDIA_ITEM;
Now lets build a custom table since you don't really care about all the data.
Lets say for what i'm interested in i want file_name title duration and file_size
- Code:
SELECT
id,file_name,title,duration,file_size
FROM media_item
where FILE_NAME like 'MasterChef%';
This outputs a table with only those columns as indicated by SELECT, and anything that starts with MasterChef the % is a wildcard, it can be used at the start or the end or both if you want, or without to find exact matches. Feel free to look at other columns for different information etc.
and now what i want to do edit/change something. you may have noticed above i included the id column, this is a unique value to a table like a line item so its easier to identify stuff.
So say i want to update the file_size and the duration as is my plan to allow a file to be played even while its being written.
- Code:
UPDATE media_item
SET duration = 10000
where id=31112;
UPDATE media_item
SET file_size = 9000000000
where id=31112;
and check that changes have happened
- Code:
select
id,file_name,title,duration,file_size
FROM media_item
WHERE id=31112;
you then have to commit i think, i'm not sure if autocommit is on by default but no harm. i believe you can also rollback changes as well via rollback and haven't tried that.
obviously this is messing with serviio at a low level so at your own risk is in order, currently i have only messed with a duplicate database, i have not yet loaded the database back to serviio.
Some things i will aim to find on the weekend
Is changing Duration/File size enough to allow uninterrupted playback of a file currently being written?
What happens if you do hit end of real file instead of fake file size?
It probably also breaks DLNA standards so no set correct action for this type of event?
When does serviio overwrite the changes?
What does serviio use as the reference to know if a file is currently in the database file_name? Or do multiple fields have to match?
If this works i then need to work out a way to script it, probably perl or bash via cygwin to stop service make changes start serviio.
If that works then maybe see if i can get class files that are compiled with a different JDBC string that links to a local database instead of an embedded, i can setup the derby database myself and link to that. And then make changes without stop/starting serviio.
Posted: Mon Nov 28, 2011 6:28 pm