SQLDisplay - a simple visual database query program

[wraggster]

News/release from Dragonminded: (http://forum.gbadev.org/viewtopic.php?t=17112&sid=f8bdb9cfdf7120d4cc7823ae4fe12c3b)

So I got tired of scrolling through a flat text file to see if I had a particular game before buying it at a swap meet. A few weeks ago I buckled down and threw together a program that can load SQL dumps and display them on the DS. Included is the ability to load multiple tables and filter down based on the text of one or more columns. Since everything visual is gathered from the database it should be compatible with just about any SQL dump you throw at it.

Should support:
- CREATE, INSERT instructions
- As many entries or strings until memory runs out
- SQL style character escapes ('' not \')

Known issues:
- No scrolling for more than 9 tables in a database.
- Only handles "text" (should handle integers but I never tested it).
- Hacky UI.
- No support for multiple databases.
- "Contains" option is not implemented.

Possible additions or updates:
- Contains, group select, greater/less than support
- Better string handling to take less memory and run faster
- Your idea here!

To use this, DLDI patch with your particular card driver. Create a directory named database at the root (can be hidden if you want). Every file inside /database/ will be loaded and treated as a SQL table dump.

The file itself is available here. http://www.dragonminded.com/ndsdev/SQLDisplay.nds

[MrZehl]

Great. I was looking for a database application for the DS. Good idea to use SQL dumps, but does using dumps perform well? Can't use indices that way.
Great that CREATE and INSERT are supported. Don't need it for my idea (just having a database as reference), but it really makes this a nifty tool with many possibilities.

Have been a programmer for years, and now for years I'm not anymore, so it's a bit rusty. :)
Of course optimalisation for strings is important and the support of greater/less then must be included.
But I've some ideas for the interface and the support of different databases.
In the database folder you could have subfolders for every database. At startup SQLDisplay asks which database to open. So not multiple databases at the same time, but choosing 1 of your databases would be great.

Next... the interface. It would be great to if you can define some queries to have available for your database at startup. So you have to have a way define some queries which are useful for your database. If you have an xml file for each database, you could do that. In the database folder with the same name as the subdirectory of the database)
So..
Actually the folder should be called databases if there are more. In that folder for example:
- MyRecords (dir)
- MyBooks (dir)
- Dictionary (dir)
- MyRecords.xml
- MyBooks.xml
- Dictionary.xml

In the xml file could be definitions of queries and defined which values the user should enter. And which labels should be shown on the screen, which kind of value (boolean, integer, string) which control should be shown and with which values. A bolean would be a checkbox, if an integer can have only the values 1, 2 or 3 a dropdownbox would be great. For straters a simple textbox would be great, but this is what can happen after a while. Just define the xml structure in a way that all this can be done in that structure and SQLDisplay will be backwards compatible as new options will be added.

After starting SQLDisplay and choosing your database, SQLDisplay will look for an xml file. If it's found the names (defined in the xmlfile) of the queries will be shown.
Selecting a resulting record, would bring you to the detail screen. Would be great if you can define in the xml file which queries could be done with this result.

Hmm.. so there could be 2 types of queries in the xml file. main queries and follow up queries.
So for each query in the xml file you have at least, a name, a display name, a sql query, which values to fill in the query by user, possible follow up queries by name.
Of course a follow up query can be a main query too. Like deleting a record. But when you already have the record all the values the user normally fills can be filled automatic. So the query doesn't have to be defined twice. Just have to clear the cache evrytime you enter the mainscreen. You would delete the record you just searched, going back to the main screen and want to delete an other record.

Well... just an idea, but when it wordks like this anybody can create his own database by setting up a structure and an xml file. So it will be a platform for many applications and reference databases. That would be great.

Is the source available? I would love to look into it. Maybe I can help. Is it GPL'ed?