Changes needed for MySQL support

[Former user]

Hi, I reverse-engineered serviio for adding MySQL support and needed to do some compatibility changes:

org.serviio.library.dao.* classes:

• Change the OFFSET <offset> FETCH FIRST <count> ROWS ONLY to LIMIT <count> OFFSET <offset>
• Change the CURRENT TIMESTAMP usage to CURRENT_TIMESTAMP
• Change the RANDOM() function usage to RAND()

serviio.properties changes: - Change db_schema_url property to jdbc:mysql://<your mysql host>/<your database>?user=<your username>&password=<your password>

package changes: - Replace the sql/* files with the attached ones

installation changes - Add mysql connector/J jar on the lib folder

Suggested implementation (I didn't checked the code besides dao.): - Check the driver implementation on db_schema_url serviio property and save on some (p1) property - Create some function (getPageStatement) to return the limit / offset based on the p1 property and use it on the dao. classes - Create some function (getNowStatement) to return the CURRENT TIMESTAMP based on the p1 property and use it on the dao. classes - Create some function (getRandStatement) to return the RANDOM() based on the p1 property and use it on the dao. classes - On sql folder, create subfolders for the database implementations supported, in this case, derby and mysql - Move the current .sql files to the derby folder - Put the provided sql files on the mysql folder - On the database initialization / update process, use the .sql files based on the p1 property

[Petr Nejedly]

hmm, ok. I thought it'd be just the code changes but I don't much fancy keeping 2 sets of sql up to date.

If you're up to it, it might be better for you to manage that and provide an updated .jar file on the wiki page whenever there is a new release?

[Former user]

Hi peter, I answered by e-mail 2014-02-01 and it didn´t worked (no comment there :D)

*"Hi petr, if you notify me about the releases I can mantain the sql updates. Just release to me a couple of days before the public releases and I send it back to you.

You take care of the code, and I mantain the sql files since I'll be using Serviio for a long time.

You just need to give me the credits for the mysql port (if you want), and I can mantain the sql files. I'll be maintaining them for my use anyway.

What do you think about this?"*

[Petr Nejedly]

Code implemented for the queries

Note that you can use -DdbURL=jdbc:derby:d:/java/workspace/serviio/db or any other URL (like mysql) when starting the server without the need to override the internal value in serviio.properties.

For the SQL files I'll leave it with you. You can then keep a Wiki page up to date with serviio.jar (incl. the sql files) and instructions on how to install and set it up. Put whatever credits you wish on the Wiki page. Thanks.

If you are not in the beta group yet, please apply so that you can test this out before it goes live.

[Sebastiano Pilla]

I've found that the scripts in the attached zip file do not work on Linux: the scripts create tables with UPPERCASE names, whereas the Serviio Java code uses lowercase table names. Linux having a case-sensitive filesystem and MySQL insisting to create a physical file per table conspire in making this not work.

[Former user]

MySQL have this behavior configurable (https://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html), you can change it to 1 (lower_case_table_names = 1). I use that way on my mysql installations.