using mySQL for data storage

[aran]

I'm designing an american football management sim, it's going to be free and open-source (working title: Free Football Sim). It will be coded in java (of course ). I need a big time data storage solution for all the stats and ratings that i'm going to be tracking (think scalability; 100 years of statistical information about thousands of players who have been on any of up to 40 teams). I know some SQL, so mySQL has popped into my mind as a very desirable tool.

Is there a way that i can set up a mySQL database using my game's installer  that won't have to run all the time? Perhaps a database with a small footprint that i can start up when i start the game and close when i close the game?

Any help would be much appreciated.

[oNyx]

Check this one:
http://sourceforge.net/projects/hsqldb

[aran]

WOW! that looks like just what i want!

Although i'm not very experienced with databases not running on webservers, i can gather from the description of it that it's a small mySQL engine that is quite fast and can be run and closed without having to have some type of service up all the time?

I'm going to have to do some research about this, but thanks a lot!

Games published by our own members! Check 'em out!

[Jeff]

WHy not go pure java?

http://db.apache.org/derby/

[erikd]

WHy not go pure java?

http://db.apache.org/derby/

Derby looks interesting, but hsqldb (as oNyx suggested) is pure java too, no?

[blahblahblahh]

I hope you have a source for the data . Most such databases are heavily guarded (because they represent huge time and effort), and in the UK they're protected by a specific law - it's illegal to use someone else's statistical data, you have to *prove* you went and read all the magazines / game-results by hand and compiled the entire DB by hand (IIRC the US doesn't have such a direct law, so it's looser, but similar sentiments exist covered by other laws). I'm guesisng you've got some free open DB that someone's compiled and makes available, but if not I'd suggest just making up your own data by random generation

[cfmdobbie]

Although i'm not very experienced with databases not running on webservers, i can gather from the description of it that it's a small mySQL engine that is quite fast and can be run and closed without having to have some type of service up all the time?

Just to be picky, it's a implementation of a SQL engine - MySQL's a different implementation, as is SQL Server, Oracle etc.

Yes, HSQLDB can be run in-memory very easily.  Try something like the following:

1   2   3   4   5   6   7   8   9

Class.forName("org.hsqldb.jdbcDriver" );    // Open connection   Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mydatabase", "sa", "");     // Use database   Statement s = c.createStatement();    s.execute("CREATE TABLE users (username VARCHAR, password VARCHAR)");    s.close();    // Finish up   c.close();

If you want to persist the database, use a connection string of the form "jdbc:hsqldb:file:/path/to/file" and I think you'll need to run an s.execute("SHUTDOWN") to clean up properly.

[Jeff]

AH, so it is (hsql is pure java.)

So hsql == hypersonic.

Derby == Cloudscape.

One difference I see is that I believe Derby is a complete SQL92 implementation.