SQL Explorer

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