Databasing

[TokenHerbz]

I don't have access to my database anymore which used SQL. is there a free service or a way to create a database which uses specifically SQL to play around with using my java programs.

Please let me know.

[QuantumPhysics]

This free service you speak of is known world wide - known as MySQL - You can get a free version here:

http://www.freesqldatabase.com/freemysqldatabase/

RE: Additional charges apply for the full version. Can you not set up a server with MSVS?

[DemonWasp]

There are plenty of free databases. Assuming you can install them on your own computer, look up one of the following: MySQL, PostgreSQL, JavaDB or Apache Derby, HSQL; or, go looking for "free SQL database" yourself.

I generally prefer PostgreSQL (mature, production-grade technology with more features than you can shake a stick at). HSQL has the advantage of being pure Java. MySQL has been the go-to database for a long time, but has some drawbacks (the most notable one, as far as beginners are concerned, is that it is prone to allowing data mangling; for example, it is possible to insert values that should not be allowed by constraints). JavaDB / Apache Derby can be embedded directly into your Java application.

There are also free editions of the pay-for databases, though they're usually quite crippled by missing features. These include MSSQL (Microsoft) and Oracle's DB (which is also just called Oracle, I think), as well as the Sybase family of products and IBM's DB2.

All of these use at least some of the SQL standard, including all of the "core" part of the specification (which is probably everything you're using). Most of them include some subset of the "optional" features, though I don't know of any that support all of the "optional" features. Many include their own extensions that aren't part of the SQL standard.

If you can't install any software on your own computer, then either you can use free database hosting (like the link QuantumPhysics posted) or you can use one of the databases that doesn't require an "install", such as Derby or HSQL (I think).

[Zren]

There are many different flavours of SQL. A key example for Postgres would be the lack of a Upsert (Update if already exists; if not Insert) baked in. The syntax isn't the same between all the different implementations either (though there is some common elements). So next time specify which you were using / wanting to use.

SQLite is a fairly common backend that works with just a single file for the database. All it requires is the java wrapper for SQLite. You can also browse a db file with SQLiteAdmin (http://sqliteadmin.orbmu2k.de/).

Personally I toy around with MySQL. I found the PhpMyAdmin software is pretty useful, which is why I installed WAMP (http://www.wampserver.com/en/) solely for local testing purposes. It's basically a server bundle with Apache (webserver), MySQL, and PHP. It's also quite useful to play around with a website's locally without the hours of setting up and configuring the softwares independently. You might also want to take a look at the MySQL workbench (https://www.mysql.com/products/workbench/) for designing tables. Though that might not help for learning the syntax very much.

[rdrake]

Putting your data into MySQL is like throwing the hard drive into a wood chipper.

[DemonWasp]

rdrake @ Fri Nov 09, 2012 1:55 pm wrote:

Putting your data into MySQL is like throwing the hard drive into a wood chipper.

I didn't want to be so blunt, but yes, MySQL is awful. The bigger problem is that the awful bits don't really appear until you're already firmly attached to MySQL as your solution.

Case in point, I had a simple 30GB database, hosted on a Pentium4, ~1GB RAM. The largest table was something like 15GB, or half the database (~30M rows). You'd expect queries to be slow-ish, but even a simple count(*) took over a minute on the larger tables. Running nested queries (especially unrelated ones) typically trashed performance so badly it was better to call the inner query, process the results, then send back the outer query with the results from the inner query (!).

Semantically-identical queries (such as a join of two tables versus an equivalent subquery) would often take radically different times to process; typically the join would take a few seconds while the subquery would take several days.

The optimization engine is so bad that "straight_join" means "join the tables in the order I gave them, because you're actually too dumb to do it better than me", whereas "join" meant "please take your time".

Often, it was impossible to close long-running queries. MySQL workbench would just crash, leaving the server doing something dumb forever (and preventing reconnection). The database had to be power-cycled frequently (every couple weeks) to keep it working.

Simple operations like replication were a nightmare (literally 3 days to set up replication, though half of that was Windows fault).

This is in addition to the ignored constraints, invalid data, duplicate data, etc.

MySQL: Not even once.

[mirhagk]

MS SQL is a pretty good database server, but I'm not sure about the connection to Java (only used C# with it, which has AMAZING integration).

I've used MS SQL for about a year or 2 now on a day to day basis and the only complaints I've ever really had have been complaints fundamental to the SQL language itself, and there were ways around it always.

@DemonWasp I don't have any experience with MySQL, but I do have plenty of experience with giant databases and how long they take to do things (but most of the time the slow down was for pretty complicated stuff, like taking data, transforming it, and duplicating it to another database)

[md]

DemonWasp @ 2012-11-09, 4:46 pm wrote:

rdrake @ Fri Nov 09, 2012 1:55 pm wrote:

Putting your data into MySQL is like throwing the hard drive into a wood chipper.

I didn't want to be so blunt, but yes, MySQL is awful. The bigger problem is that the awful bits don't really appear until you're already firmly attached to MySQL as your solution.

Case in point, I had a simple 30GB database, hosted on a Pentium4, ~1GB RAM. The largest table was something like 15GB, or half the database (~30M rows). You'd expect queries to be slow-ish, but even a simple count(*) took over a minute on the larger tables. Running nested queries (especially unrelated ones) typically trashed performance so badly it was better to call the inner query, process the results, then send back the outer query with the results from the inner query (!).

Semantically-identical queries (such as a join of two tables versus an equivalent subquery) would often take radically different times to process; typically the join would take a few seconds while the subquery would take several days.

The optimization engine is so bad that "straight_join" means "join the tables in the order I gave them, because you're actually too dumb to do it better than me", whereas "join" meant "please take your time".

Often, it was impossible to close long-running queries. MySQL workbench would just crash, leaving the server doing something dumb forever (and preventing reconnection). The database had to be power-cycled frequently (every couple weeks) to keep it working.

Simple operations like replication were a nightmare (literally 3 days to set up replication, though half of that was Windows fault).

This is in addition to the ignored constraints, invalid data, duplicate data, etc.

MySQL: Not even once.

Not to say that MySQL isn't attrocious, but it could be that part of your problem is not having the right indexes or partitioning. The EXPLAIN statement (works with MysQL and PostgreSQL; not sure if it's standard or not (and don't care)) works really well for debugging slow queries and making them faster. At a certain point though you end up having to throw more memory at MySQL until you've got everything cached in RAM in order to get reasonable speed on large tables.

[DemonWasp]

While what you say is true, there were indexes on all the joined / queried columns. The "explain" output usually showed it was using indexes. The problem is that the optimizer would do one of these things:

1) Look in the biggest table first, meaning it had to save a ridiculous amount of data that would later be filtered to a few rows.
2) Decide that an unrelated inner query was related, so it had to be re-run for every element found by the outer query.
3) Ignore an existing index in favor of table-scan.

There were political considerations preventing us from just replacing the machine with something that could hold the whole DB in RAM. Our options were constrained to "use what you have" or "relocate the database to the far end of the continent, but with a faster physical device".

[TokenHerbz]

Just a nice little website i found for hosting, offers more then others, Perhaps worth investing a few mins and checking it out, reading over the ToS, etc.

http://zymic.com/