How do you debug the JBW2 database?

Subscribe to How do you debug the JBW2 database? 3 post(s), 2 voice(s)

Avatar Laurence Pas... 59 post(s)

Hi Morgan,

I’ve thoroughly confused myself because I forgot that you’d implemented a database backend for JBW2. Consequently my naive attempts at moving files in and out of ~/.jbidwatcher is having no effect on the auctions being tracked! How do you debug the MySQL database component? This is an even more optimistic question: is the schema documented anywhere? :) Thanks.

Avatar Morgan Schweers Administrator 1,204 post(s)

The database is actually Derby (aka Cloudscape, aka JavaDB), and the schema is ‘documented’ in jbidwatcher.sql, and db/*.sql. The db/*.sql files are ‘migrations’, basically each one is an upgrade of the schema. (Basically as soon as I started handing it to people other than me, I needed to be able to upgrade their databases, so the main jbidwatcher.sql file ‘froze’, and the upgrades get run.)

I think I designed it right, so I could (although I haven’t tried yet) merge all the changes into the jbidwatcher.sql file, and set the schema version to bypass all the lower numbered migrations, so jbidwatcher.sql would be the actual schema, and the migrations would exist just to bring prior versions up to current. Right now, though, you have to read jbidwatcher.sql, and the migrations, to know the exact schema.

Theoretically I could allow users to edit the jdbc string, and connect to a different database. I haven’t tried that either, and I imagine I’m using some Derby-specific database types or something…

I’m pretty sure there’s an interactive-mode Derby, similar to the MySQL client, that can connect to the database and you can run queries against it. I actually did some cool stuff with building a JRails (JRuby on Rails) app which talked to the JBidwatcher DB using the JDBC Active Record adapter, and was a pretty good way to walk through the data. Mostly, though, if I really need to debug the data in the database, I run it under my debugger, and issue calls against the running code. :) The Scripting window is another way to do similar things, although neither method is nearly as nice as the MySQL interactive client…

— Morgan Schweers, Cyber*FOX*!

Avatar Morgan Schweers Administrator 1,204 post(s)

Here’s the best way to debug JBidwatcher’s database…

Download the Derby binary ( ) and uncompress it.

Now run ‘ij’ in Derby’s bin directory. On a Windows box, I run it like so:
<pre>"C:\Program Files\db-derby-\bin\ij" ij version 10.4 ij&gt; connect 'jdbc:derby:c:\documents and settings\mrs\.jbidwatcher\jbdb' </pre>

You’ll want to replace ‘/mrs/’ with your own directory. I’m at work, so I can’t double-check, but on Mac OS X, you would go something like (presuming you uncompressed derby in the ~/oss directory):

<pre>~/oss/db-derby- ij version 10.4 ij&gt; connect 'jdbc:derby:/Users/mrs/Library/Preferences/JBidwatcher/jbdb' </pre>

From there you can issue SQL statements against the database, such as:
<pre>ij&gt; select count(*) as auctions from user1.auctions; AUCTIONS ----------- 370

1 row selected
ij> select count(*) as entries from user1.entries;

1 row selected

The schema name is ‘user1’, so all the tables are prefixed with that. (I.e. user1.auctions, user1.entries, etc…)

The above query shows that 370 auctions were saved, but JBidwatcher expected there to be 382, so 12 auctions got lost somehow. I still haven’t figured out how. :(

— Morgan Schweers, CyberFOX!

p.s. Note that because it’s an embedded database, you can’t be running ‘ij’ and JBidwatcher at the same time, only one can access the database at a time.