Database Recommendations?

[tmiranda]

I need to make use of a database in one of my plugins and I've never used a database before in Java. I see there are various plugins already available that have to do with database manipulation but I have no idea what they do or how to use them (SQLite JDBC, Sage H2).

My needs are simple. I'm currently storing data as serialized objects in a flat file that I read into memory and search as needed. To speed things up I keep the file loaded in memory but this is causing the plugin to use way too much Java Heap. The database can grow to be several hundred MB.

What database systems are available that:
- Are either already being used by other plugins (ideal) or can easily be added to the Sage repository?
- Have a reasonable learning curve.

[broconne]

JavaDB (formally known as Apache Derby) used to be popular. But it really seems H2 is more popular these days. There are a couple of plugins already out there that seem to be using H2. I have played with both, albeit in a limited fashion and I prefer H2. I thought someone else was using H2 in sage, but I don't see it in the libraries list -- maybe ortus was using it..

SQLLite is out there as a dependency already - but I have never used that one.

One benefit of H2 (IMO) over JavaDB is that you can have in memory databases - great for unit testing, etc.

However, from a difficulty aspect - the should all pretty much be the same. A few lines of initialization code and then just standard SQL.

[jaminben]

Ya, Ortus uses a H2 database.....but thats as much help as I can give as I didn't set it up

Cheers

Ben

Edit

Actually I think Phoenix uses H2 as well but I can't be sure.

[tmiranda]

Sounds like H2 is the leading candidate. Time to do some reading, thanks.

[PLUCKYHD]

Is it really necessary to store it seperate now with the new userrecord API additions to metadata you could store them there and would be a better way. I have been giving much that to switching smm over to using it. I am assuming you can store you complete custom objects in there but haven't had time to test it yet. I would stay away from a external db if at all possible especially if you don't have any database knowledge.

[tmiranda]

Originally Posted by PLUCKYHD

Is it really necessary to store it seperate now with the new userrecord API additions to metadata you could store them there and would be a better way. I have been giving much that to switching smm over to using it. I am assuming you can store you complete custom objects in there but haven't had time to test it yet. I would stay away from a external db if at all possible especially if you don't have any database knowledge.

I don't think it's possible for me to use the new metadata API. I'm working with Podcasts that may or may not have a MediaFile and the MediaFile may or may not have been deleted. I am also hesitant to fill up the wiz.bin with many MB of data. Maybe I've misunderstood this API?

As for not having any DB knowledge, that makes it all the more fun and challenging. A year ago I didn't have any Java knowledge and a year before that I didn't have any Studio knowledge.

[PLUCKYHD]

Originally Posted by tmiranda

As for not having any DB knowledge, that makes it all the more fun and challenging. A year ago I didn't have any Java knowledge and a year before that I didn't have any Studio knowledge.

I definately wouldn't worry about filling up the sage db the not having a media file is understandable and the issue I am facing with smm however I have deemed a way worthy of faking that aspect like playon does.

And I know about learning was just trying to steer you away as a database adds allot of maintenance and issues.

That said I understand the need an want for an external database so if you power on good luck.

[Fonceur]

Originally Posted by tmiranda

I am also hesitant to fill up the wiz.bin with many MB of data. Maybe I've misunderstood this API?

You are not putting the whole podcast in there (you might be able as a blob, but...), just links and such, so it shouldn't be that bad...

[tmiranda]

Originally Posted by Fonceur

You are not putting the whole podcast in there (you might be able as a blob, but...), just links and such, so it shouldn't be that bad...

No, not the whole podcast but there is a lot of other data. There is the "Podcast", which contains general information about the feed and then for each Podcast there are multiple "Episodes" that contain information about that specific Episode. Depending on how the plugin is used there may be dozens or hundreds of Podcasts defined and then each Podcast can have a hundred or more Episodes.

A lot of the data needs to stay around for a long time. For example, when a user downloads an Episode, watches it, and then deletes it. I need to keep track of which Episodes were downloaded and deleted and which were not.

I'm open to suggestions on how to use the existing Sage API, but it seems to me that a real database is the best way to go.

[Fuzzy]

Aside from that, there may be other advantages to treating the podcasts as mediafiles (the dummy files Plucky mentioned).

[tmiranda]

Originally Posted by Fuzzy

Aside from that, there may be other advantages to treating the podcasts as mediafiles (the dummy files Plucky mentioned).

After they are recorded they ARE MediaFiles, that's the easy part. It's before they are recorded and after they are deleted that I need to worry about.

[PLUCKYHD]

Originally Posted by tmiranda

After they are recorded they ARE MediaFiles, that's the easy part. It's before they are recorded and afterthey are deleted that I need to worry about.

That's easy use the series API to keep the history an future about each podcast as a series. That would actually work pretty well since jeff opened up that series for us. That would actually work pretty well treat each podcast series as a series.

[tmiranda]

Originally Posted by PLUCKYHD

That's easy use the series API to keep the history an future about each podcast as a series.

That's interesting, I'll have to take a closer look at that API.

[stuckless]

Just to clarify... Phoenix does not use an external database. I did look at H2 at one point, and if I were to use a database, then I'd use H2.

But, to be honest, I haven't found a reason to actually use database to store anything.

The new UserRecordAPI was actually something that I had requested as a means to store user defined records in the wiz.bin. Now that it has been added, I don't see any reason to use a database at all.

You can store any fields you want in the UserRecordAPI, and as long as you have a primary key (ie, a unique identifier) for each record, then retrieving the data is fairly fast. Also the data can be retreived from the server or the client transparently, like any other sage api.

UserRecordAPI has the idea of "stores". "stores" are basically like tables. So you add a new record to a store using a key, and then you have a dynamic record on which you can operate.

For example, you might have a feed store, named, "tmiranda.feed", where the primary key is the feed url. You can then store data against that feed url, and retrieve using the feed url. Lkewise you can create a store, "tmiranda.media", where the primary key is the media download url. You can then track watched statuses, fetch date, etc, against the url.

you can use the UserRecordAPI in conjuntion with other APIs, such as the SeriesInfoInfo. ie, if you are storing "series" type information, then using the SeriesInfoAPI make sense.

If you do decide to use the UserRecordAPI, then I strongly suggest that you prefix all your "stores" with your own identifier. Just so you know, "phoenix.", "sagex." and "bmt." is reserved (the honour system)

[PLUCKYHD]

Originally Posted by stuckless

If you do decide to use the UserRecordAPI, then I strongly suggest that you prefix all your "stores" with your own identifier. Just so you know, "phoenix.", "sagex." and "bmt." is reserved (the honour system)

Man guess I need to rename

Does the user API allow storing custom objects it doesn't appear to but I haven't honestly looked at it hard yet.

[stuckless]

No, there are no typed objects, just Strings, in the UserRecordAPI. This is consistent with the other apis that have been added to mediafile, series, favorites, etc.

So, if you need to retain a value as an int, boolean, etc, then you'll have to manually convert the data from a string to the required type.

[PLUCKYHD]

Originally Posted by stuckless

No, there are no typed objects, just Strings, in the UserRecordAPI. This is consistent with the other apis that have been added to mediafile, series, favorites, etc.

So, if you need to retain a value as an int, boolean, etc, then you'll have to manually convert the data from a string to the required type.

That is what I thought when I glanced over it thanks.

[GKusnick]

One potential downside to storing a large volume of plugin-specific data in Wiz.bin is that people who uninstall your plugin then have no easy way to get rid of that leftover data. Storing it in an external database in a plugin-specific subdirectory makes it easier to find and remove later.

If you do decide to go the Wiz.bin route, you should probably provide a cleanup command that people can use to purge your plugin's data before uninstalling it.

[Slugger]

Until recently, I've always used SQLite. It works, but when we looked at adding STVi support for SRE it became apparent that SQLite doesn't fit well when you need to access the DB on the server from a PC client. It was either roll my own client/server comm to access the SQLite DB (lol!!) or move to a client/server DB. Once I started looking at H2 closely it was a no brainer.

I obviously won't remove the SQLite JDBC driver from the plugin repository, but any future projects will definitely be H2 and I'll eventually move my current projects that are still using SQLite (SageAlert) over to H2 at some point in the future. The web console for H2 was an added bonus on top of the client/server architecture. Also, referential constraints that are actually enforced is a really nice thing to have as well. Not that I've tried it yet, but Java classes can be used as UDFs, which kind of caught my attention as well. You can even embed Java code as UDFs directly via SQL.

Bottom line, I recommend H2, especially if you need to access the DB on PC clients.

And by the time we're done with SJQv4 you'll be an SQL/H2 expert!

[stuckless]

Originally Posted by GKusnick

One potential downside to storing a large volume of plugin-specific data in Wiz.bin is that people who uninstall your plugin then have no easy way to get rid of that leftover data. Storing it in an external database in a plugin-specific subdirectory makes it easier to find and remove later.

If you do decide to go the Wiz.bin route, you should probably provide a cleanup command that people can use to purge your plugin's data before uninstalling it.

I agree... Too bad there isn't a hook that gets called in the Plugin API to notify a plugin when it gets removed

Because the UserRecordAPI is made of up stores, as long a devs stick to a consistent naming scheme, it should be fairly easy for someone like yourself to get a list of all of the "stores" and then delete the ones that should not be there... you know, in case the developer doesn't remove their data store automatically, which is bound to happen. If I get time, I'll probably add a data browser to the bmt web ui, so that people can browse the stores, data, remove them, etc.

Originally Posted by Slugger

Once I started looking at H2 closely it was a no brainer.

Bottom line, I recommend H2, especially if you need to access the DB on PC clients.

I'd have to agree... H2 is a solid and feature rich database. I think that there is a sagex-h2 plugin in the repository. It's not mine, but I do depend on it in phoenix as well... even though I haven't actually needed to use the database yet. I'd recommend using it, since it has the plugin configuration for setting the port, enabling the servers, etc. We probably don't want to have 4-5 different H2 instances all running inside side.