SQL Explorer

[cbk1994]

I present to you SQL Explorer, an in-game SQL database manager for Graal.

For those of you who aren't familiar, SQLite is a "software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine". Basically, it's a really fast and efficient way to store data into tables. Data can range from logs to bank accounts to sparring stats. SQL Explorer is a multipurpose tool that makes it easy to view and manage all of this data.

Current Version: 2.2

Features:

• Create, drop, duplicate, and rename whole tables
• Create and drop indexes on tables
• Insert rows into tables
• Duplicate rows in tables
• Modify rows in tables
• Delete rows in tables
• Automatic pagination for table viewing
• "Execute SQL" tab that allows you to test and run SQL queries and view data in more advanced, customized ways
• Tables can be sorted directly from the table view by double clicking the column; double click again to toggle between ascending and descending.
• Filter tables directly from the table view using modes such as "=", "LIKE", and "contains".
• Work easily between multiple databases
• Beautiful interface for everything

Screenshots:


Instructions:
Upload to your server into a weapon script, add it to yourself, and press "n". If you want the databases tab to be fully functional, add r databases/*.db to the rights of (npcserver).

Note: Until a way is found/added to dynamically get a list of configured SQLite databases, you will have to put them into the script manually. The function, getDatabaseList(), is at the very top of the script. It should be pretty self-explanatory how to add them.

Thanks to:

• fowlplay4 for his SQL Worksheet, after which much of the "Execute SQL" tab was modeled, and for his number formatter.
• Inverness for putting up with my whiny questions about SQL
• The artists behind the graphics on the database tab. Credits are available at these links: 1 2 3

Disclaimer:
This is a tool for developers. It's not something that is completely bulletproof with security. In other words, if you're trying to mess something up or cause some kind of error, you probably can. It doesn't necessarily protect against SQL injection in all cases, so just be careful with what you do.

See also:
SQLite Database Browser - an external tool for viewing the database; it can be used in any instance where my tool fails for whatever reason


If you find any bugs/glitches, please report them; I use this tool on a daily basis, and any bugs you have will eventually effect me too. Comments, criticism, and suggestions are also welcome.

The latest version of SQL Explorer can always be found here.

[Deas_Voice]

awsome work chris! rep++!
(got to spread it first Z_z)

[Matt]

Very nice work.

[fowlplay4]

Very nice, will definitely manipulate my own tables with it.

[Seich]

Woah that's awesome.. =O, I'll be using this a lot.

[Switch]

This is some real quality work!

[cbk1994]

Updated to version 1.1 (thanks Tig); it contains a rights system (which can be enabled or disabled). See the first post for a description of how to use it (or disable it -- it's enabled by default).

[fowlplay4]

Originally Posted by cbk1994

Updated to version 1.1 (thanks Tig); it contains a rights system (which can be enabled or disabled). See the first post for a description of how to use it (or disable it -- it's enabled by default).

Interesting way to store rights for scripts, will remember that.

[WhiteDragon]

This probably deserves a sticky.

[coreys]

Are auto-incrementing constraints not available in the GS2 wrapper for sqlite? Because this tool reports an error any time I try to create a column with auto-increment set.

Edit:
This is just an observation due to the auto-increment option in the SQL Explorer, I don't actually need to make use of it since SQLite has an auto-incrementing ROWID set by default.

[cbk1994]

Originally Posted by coreys

Are auto-incrementing constraints not available in the GS2 wrapper for sqlite? Because this tool reports an error any time I try to create a column with auto-increment set.

You can use "PRIMARY KEY", but it will just be an alias for the rowid column.

EDIT: See this page as well.

[coreys]

Originally Posted by cbk1994

You can use "PRIMARY KEY", but it will just be an alias for the rowid column.

EDIT: See this page as well.

I know, but I'm saying that when attempting to use the option when creating a column through the GUI it gives an error.

[cbk1994]

Originally Posted by coreys

I know, but I'm saying that when attempting to use the option when creating a column through the GUI it gives an error.

That's probably my fault, I'll add that to my list of improvements.

[coreys]

Originally Posted by cbk1994

That's probably my fault, I'll add that to my list of improvements.

That's gotta be a short list <3

[fowlplay4]

A query generator would be cool too.

I.e: Select a few columns from a few tables, and so on.. click Generate Query.

PHP Code:

// Generated Query
function getQuery() {
  return "
    SELECT column
    FROM table
    WHERE condition = query
  ";
} 


[Admins]

NPCserver update: the next uploaded npcserver will include a new script command bool sqlitedefragment(host) for defragmenting and reducing the file size (host=="" for default database). May be there could a file panel or so where you see the database size and a button for optimizing it.

[coreys]

Just seeing the database size would be very useful. Nice addition to hear of, though, Stefan

[coreys]

Sorry for the double post. There seem to be SQL errors every now and then, such as this:

SQL Explorer Error: near ",": syntax error
Query: CREATE TABLE 'testitems' ('foo' TEXT DEFAULT '', 'bar' REAL DEFAULT , 'baz' INTEGER DEFAULT )

Also, it seems you can't insert rows through the GUI without there already being existing rows.

[cbk1994]

All good ideas, I'll try to update it over the weekend. Stefan: is there any advantage to the scripted functions as opposed to just calling "VACUUM"?

Originally Posted by coreys

Sorry for the double post. There seem to be SQL errors every now and then, such as this:

There shouldn't be single quotes around the column names... it's adding those by itself? Strange.... I'll fix that.

Also, it seems you can't insert rows through the GUI without there already being existing rows.

This is true, I'll fix this as well.

[cbk1994]

I've added quite a few things to the new version, mostly bug fixes, but also some other stuff. I went to add the database information, but ran into a wall: ".db" isn't one of the extensions the NPC-server can manipulate, which means I can't read the file size. I mentioned this to Stefan a few weeks or so again, so he may have fixed it in the new NPC-server, but I'm not sure. Until then, I'll leave it out (psst Stefan, would be great if you could fix it and let me know).

[coreys]

Originally Posted by cbk1994

I've added quite a few things to the new version, mostly bug fixes, but also some other stuff. I went to add the database information, but ran into a wall: ".db" isn't one of the extensions the NPC-server can manipulate, which means I can't read the file size. I mentioned this to Stefan a few weeks or so again, so he may have fixed it in the new NPC-server, but I'm not sure. Until then, I'll leave it out (psst Stefan, would be great if you could fix it and let me know).

This was updated to the original post, I assume?

[cbk1994]

Originally Posted by coreys

This was updated to the original post, I assume?

No, sorry... didn't mean to make it sound like I'd already released it. I'm still adding the filtering, will probably release it later tonight or tomorrow.

[cbk1994]

Sorry for the delay, updated to version 2.0; see first post (thanks again Tig)

Changelog:

• New Features and Enhancements
  • The "n" key now toggles instead of opening
    • Addition of the "TOGGLE_KEY" constant to change what key toggles the window (default "n"); see the clientside portion
  • Right-click a tab for a menu to refresh the table or to close the tab
  • You can now right-click the column headings to insert rows, letting you insert columns into empty tables.
  • You can now sort columns by double clicking. Double click once to sort ascending, double click again to sort descending.
  • You can now filter tables with various modes such as "contains", "=", and "LIKE", right from the table view.
• Fixes
  • Updated inaccurate title of "Tables" tab - used to be called "Databases" (oops)
  • Fixed the auto increment feature in table creation
  • Fixed a problem with table creation when leaving "Default" blank and selecting REAL or INT (now sets it to '0' if not specified)
  • Removed single quotes around columns when creating tables with the GUI and when adding columns with ALTER TABLE.

I'll release an updated version with database statistics when the new NPC-server is released.

Filter:


Sort:

[xXziroXx]

Nice, you implemented my suggestions.

[TSAdmin]

This is great. Well done, Chris. Rep++

[cbk1994]

Updated to version 2.1, thanks Supernanny. Here are some of the changes:

• Support for multiple databases for browsing, manipulating, and SQL execution. For information on adding databases see this thread. See the note below about adding databases to the list.
• Added a "toggle external window" button to toggle between internal and external windows. It remembers your preference for how to display the windows for next time. This only works with v6 clients.
• Changed the tables tab to instead show the structure of the configured SQL databases on the server; it works the same way, though.
• Fixed the max loop limit problem with the execute SQL tab.
• Closing tabs now works with v6 clients by right clicking tab.

Mostly minor improvements besides the change for multiple databases.

Right now there's no way to dynamically get a list of configured databases, so you will have to maintain the list yourself. There's a list with the default configuration near the top of the script. Add any additional databases there and you'll be good to go. The file of the database isn't used yet but will be in the future (probably).

[WhiteDragon]

Fantastic job, nice seeing things updated.

[Inverness]

This is an excellent tool.

[xAndrewx]

Hmm, I seem to be having some problems deleting a row from a table, I used the exact same code as you have posted but it seems to not find the table correctly... any suggestions?

[cbk1994]

Originally Posted by xAndrewx

Hmm, I seem to be having some problems deleting a row from a table, I used the exact same code as you have posted but it seems to not find the table correctly... any suggestions?

Are you using the table display (right click > delete row) or through a script (or the "Execute SQL" tab)?

[salesman]

Originally Posted by xAndrewx

Hmm, I seem to be having some problems deleting a row from a table, I used the exact same code as you have posted but it seems to not find the table correctly... any suggestions?

If you're not getting any errors, and your tables are configured properly, then the list just might not be refreshing.

I don't know if it was intentional or not, but with the newer version it doesn't update the list every time you make a change. I had the same problem thinking it wasn't deleting rows properly but closing the tabs, refreshing, and then reopening showed me that the row was actually deleted.

Edit: Hmm, I just tested and I'm actually having the same problem. Refreshing doesn't help, and the row is not deleted via right-click delete!

[cbk1994]

Sorry about that, I've attached a fixed version and I'll get the first post updated soon.

The only changes are the fixes for duplicating and deleting rows.

[xAndrewx]

Ah, so it's not just me *phew

thanks @ Chris, I thought I'd done something wrong

It's an amazing tool, I'd rep+++++++++++++++++++++++++ infinity!!

Works a charm- lovely thank you

[Switch]

External GUIs look so ugly.
But nice update.

[cbk1994]

I'm releasing version 2.2 of SQL Explorer (thanks Tig for updating the original post).



New Features:

• Added a "Databases" tab that lets you easily view information about the databases you've configured and provides a convenient button to defragment it. The NPC-server will need r databases/*.db for database size to show.
• Added the ability to select multiple rows (this is currently not fully working on the Mac client, but may work better on other platforms) and right-click to delete multiple rows at a time.
• The rights system has been removed. Instead, there is a check to make sure the account using SQL explorer is in staff= in server options and has NC. The latter can be disabled, the former cannot.
• Numbers are now formatted nicely with commas (thanks Jer).

Bugfixes:

• Now using escapestring2 for user input to prevent quotations and backslahes from being entered incorrectly.
• Dialog windows only open externally if you have SQL explorer set to external now (before they always opened externally).

Warnings to Upgraders:
The rights system has been removed. SQL Explorer is not meant to be given out as a staff right, which is what the former system was promoting. Now, access is limited to those in staff= in server options and who have the NPC-Control right. The latter can be disabled near the top of the script, but I don't recommend it.

SQL Explorer is no longer tested with Graal v5. If you're still using it, you should consider switching to v6.


Images for the databases tab are downloaded from the Wiki if you don't already have them. This means that, in theory, Stefan could tell when you open SQL Explorer and don't have the images downloaded. If for some reason you want to hide from him, you can download the images yourself and place them in your images folder.

You can find the new version of SQL Explorer here.

As always, suggestions and bug reports are welcome .

[WhiteDragon]

Excellent.

[xAndrewx]

Good stuff

[Tricxta]

very nice! rep+

[fatcat123]

This is amazing =o
rep++

[Emera]

The link that states the new versions of SQL can always be found at the specified link, is broken and gives me an error when trying to connect.

Not Found

The requested URL /sqlexplorer/ was not found on this server.

Apache/2.2.19 (Ubuntu) Server at code.graalcenter.org Port 80

If you could fix this, it would be great!