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).

[WhiteDragon]

This probably deserves a sticky.

[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.

[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

[salesman]

Although it could easily be done under the "Execute Query" tab, searching would be a nice thing to add for when users are viewing a table.

Also sorting by column wouldn't be that hard to do, either. If you click a column header, it will sort the rows by that column...click it again and it will sort them in the opposite direction.