Database Rant
Plain text files and spreadsheets are too limited for necessary data storage for story information. I’ve tried working out a text file format that I can load into a custom-written program to work with the data, but that just hasn’t worked out. I decided to check out database software.
I’m already using GRAMPS for storing data about characters for some stories, and it was a lot of trouble getting GRAMPS to look to a folder of my choosing to store its data. And it can only have one data location. I hoped to find a better solution.
First up? OpenOffice.org’s “Base”, its database software. Stores data in a file. Or, at least, it stores a file. The data doesn’t seem to be stored in the file. That’s a failure for me, as I need to be able to easily back-up this data and share it between my laptop and a PC.
Okay, next up is Kexi. Uses SQLite for saving a file. Good, good. Create a table, and populate it. Looking nice. Let’s add a column to the table. I can add more as I need them. Except…adding a column or removing one deletes everything in the table…
Maybe it’s because I’m using SQLite? I tried to avoid the need to run a database server (this is on a laptop which often is running off of its battery), but I’ll go with MySQL. Re-create the table, re-populate the data, add a column, and…it requires deleting everything in the table.
Let’s just add a column via PHPMyAdmin, which is not a decent interface for modifying a lot of data, but should work for adding a column. Column added. And Kexi won’t see that column at all, no matter what…
All right, back to OO.o Base. Set up a MySQL connection, and…I need to connect either using ODBC or JDBC, and I can contact my system administrator if I’m confused. ODBC looks like it wants me to select a file, but I get an error about an .so file not being installed. JDBC wants the database name and server URL. All right, I know the name, and “localhost” should suffice for the server. Maybe the port number won’t need to be touched. And…the JDBC driver cannot be loaded.
By this time, I’m an hour into just trying to have a way to easily sort data so I can start fiction writing with my characters well organized.
Reading into it more, JDBC has limitations which would require PHPMyAdmin to work around, but ODBC is more work to get set up. ODBC is is, then. I’ve manage to configure ODBC by adding a new System DNS, only to get an error about not being able to construct a property list.
At this point, thoughts turn to wondering how difficult is would be to install the SQLite driver for Base. Thankfully there’s a .deb file for the software that bridges SQLite with ODBC (I use Kubuntu on my laptop). This time, configuring ODBC isn’t an issue, as SQLite drivers already populate the Server DNS information. I simply had to set up a Server DNS using the SQLite ODBC driver, and set the database file to save to.
Using Base, randomly fonts are a little scrunched, and sometimes the cursor icon doesn’t display. I did not have these issues when I tested it earlier… I have to save a file separate from the SQLite database file, which is understandable as Base wants to store extra information somewhere. And…I can’t edit a database table in Base once I’ve added data. Make this is an SQLite limitation?
I can use PHPMyAdmin to painstakingly input data, but that doesn’t help me write up queries for easy access of data. Maybe it’s time to read up more on Qt’s SQLite support…
And it’s only taken me two hours to find no suitable database tool for me to use.
