SQL Worksheet

[fowlplay4]

If anyone has ever worked with Oracle's SQL Worksheet this should bring back some memories.



Recently Stefan added SQLite support to GS2 and it's pretty great but when you're testing your queries it gets tedious constantly updating the scripts on your server.

This tool should save you less scripting frustration and more time developing the queries before altering your script for them.

PHP Code:


/**
     Coded by fowlplay4
     Feel free to alter the script, hell you can even say you wrote it
     but I'd really appreciate it if you left my name at the top.
*/

function onActionServerSide(command, message) {
  switch (command) {
    case "execute":
      // Break apart multiple queries.
      temp.queries = message.tokenize(";");
      // Perform Queries
      for (temp.query: queries) {
        this.trigger("PerformQuery", query);
      }
      break;
  }
}

function onPerformQuery(query) {
  // Custom Worksheet Commands
  if (query.lower().starts("echo")) {
    player.triggerclient(this.name, "echo", query.substring("echo ".length()));
    return;
  }
  // Query the Database
  temp.results = requestsql(query, true);
  if (results.error != "") {
    // Display Error if Exists
    player.triggerclient(this.name, "error", results.error);
  }
  else if (results.rows.size() > 0) {
    // Determine Column Names
    temp.columns = results.rows[0].getdynamicvarnames();
    for (temp.i = 0; i < results.rows.size(); i++) {
      // Pools Data Accordingly
      temp.data = "";
      for (temp.column: columns) {
        temp.data.add(results.rows[i].(@column));
      }
      // Add Data to Rows
      temp.rows.add(temp.data);
    }
    // Send Data to Client
    player.triggerclient(this.name, "results", {columns,rows});
  }
  else if (results.affectedrows > 0) {
    player.triggerclient(this.name, "affected", results.affectedrows);
  }
  else player.triggerclient(this.name, "completed", query.tokenize()[0]);  
}

//#CLIENTSIDE

// Opens GUI with chat command 
function onPlayerChats() {
  if (player.chat == "/sql") {
    player.chat = "";
    onCreateGUI();
  }
}

// Creates Worksheet GUI
function onCreateGUI() {
  new GuiWindowCtrl("SQL") {
    profile = GuiBlueWindowProfile;
    clientrelative = true;
    clientextent = "498,470";
    canmaximize = false;
    canmove = true;
    canresize = false;
    closequery = false;
    destroyonhide = true;
    text = "Graal SQL Worksheet";
    x = (screenwidth - width) / 2;
    y = (screenheight - height) / 2;

    new GuiScrollCtrl("SQL_MultiLineEdit1_Scroll") {
      profile = GuiBlueScrollProfile;
      height = 273;
      hscrollbar = "dynamic";
      vscrollbar = "dynamic";
      width = 405;
      x = 90;
      y = 6;

      new GuiMLTextEditCtrl("SQLQuery") {
        profile = GuiBlueMLTextEditProfile;
        height = 16;
        horizsizing = "width";
        text = "";
        width = 380;
      }
    }
    new GuiButtonCtrl("SQL_Button1") {
      profile = GuiBlueButtonProfile;
      text = "Execute Query";
      width = 80;
      x = 6;
      y = 5;
    }
    new GuiScrollCtrl("SQL_MultiLine1_Scroll") {
      profile = GuiBlueScrollProfile;
      height = 186;
      hscrollbar = "dynamic";
      vscrollbar = "dynamic";
      width = 405;
      x = 90;
      y = 281;

      new GuiMLTextCtrl("SQLConsole") {
        profile = GuiBlueMLTextProfile;
        height = 16;
        horizsizing = "width";
        text = "";
        width = 380;
      }
    }
    new GuiButtonCtrl("SQL_Button2") {
      profile = GuiBlueButtonProfile;
      text = "Clear Query";
      width = 80;
      x = 6;
      y = 39;
    }
    new GuiButtonCtrl("SQL_Button3") {
      profile = GuiBlueButtonProfile;
      text = "Clear Console";
      width = 80;
      x = 6;
      y = 73;
    }
  }
  sqlecho("SQL Worksheet - Scripted by fowlplay4");
}

// Prints Results from Server
function onActionClientside(command, message) {
  switch (command) {
    case "error":
      sqlecho("SQL Error:" SPC message @ "\n");
      break;
    case "results":
      temp.columns = cuteArray(message[0]);
      sqlecho("SQL Query returned" SPC message[1].size() @ " rows\n" @ columns @ "\n==============");
      for (temp.row: message[1]) {
        sqlecho(cuteArray(row));
      }
      sqlecho("");
      break;
    case "affected":
      sqlecho("SQL Query affected" SPC message SPC "rows!");
      break;
    case "completed":
      sqlecho("SQL" SPC message.upper() SPC "Query completed successfully.\n");
      break;
    case "echo":
      sqlecho("SQL: " @ message);
      break;
  }
}

// Readable Array Text Format
function cuteArray(array) {
  if (array.size() == 1) return array[0];
  else for (temp.element: array) {
    temp.result @= element @ ",";
  }
  return result.substring(0,result.length()-1);
}

// Outputs to Worksheet Console
function sqlecho(text) {
  SQLConsole.addtext(text @ "\n", false);
  SQLConsole.scrolltobottom();
}

function SQL_Button1.onAction() {
  // Button "Execute Query" has been pressed
  temp.query = "";
  for (temp.line: SQLQuery.getlines()) {
    temp.query @= line SPC "";
  }
  temp.query = temp.query.trim();
  if (query != "")
    triggerserver("gui", name, "execute", query);
}

function SQL_Button2.onAction() {
  // Button "Clear Query" has been pressed
  SQLQuery.settext("");
}

function SQL_Button3.onAction() {
  // Button "Clear Console" has been pressed
  SQLConsole.settext("");
} 


Now I could spend the time explaining the small feature set but it's best shown, so feel free to upload the script onto your server and copy and paste the following set of queries into the GUI and click Execute.

PHP Code:

DROP TABLE test;

CREATE TABLE test (
   account varchar not null default '' primary key, 
   age int not null default 1
);

INSERT INTO test VALUES ('fowlplay4', 18);
INSERT INTO test VALUES ('Stefan', 33);
INSERT INTO test VALUES ('Graal', 11);
INSERT INTO test VALUES ('Unixmad', 42);

SELECT *
FROM test;

UPDATE test
SET account = 'Graalonline'
WHERE account = 'Graal';

SELECT account AS 'Graal Name', age AS 'Years'
FROM test;

ECHO Completed; 


After executing you should see the following in the console. If you get a weird/no response you might not have SQL enabled, just restart your npcserver.

PHP Code:

// You may get a table doesn't exist error here, just ignore it.
SQL DROP Query completed successfully.

SQL CREATE Query completed successfully.

SQL INSERT Query completed successfully.

SQL INSERT Query completed successfully.

SQL INSERT Query completed successfully.

SQL INSERT Query completed successfully.

SQL Query returned 4 rows
account,age
==============
fowlplay4,18
Stefan,33
Graal,11
Unixmad,42

SQL UPDATE Query completed successfully.

SQL Query returned 4 rows
Graal Name,Years
==============
fowlplay4,18
Stefan,33
Graalonline,11
Unixmad,42 


Now if you're already using databases, be careful with this tool. One wrong query could wipe your database clean :P

If you have any issues/concerns I'll answer them as best as I can, as SQL Support is updated I'll update the tool to include support for the new features added.

Enjoy.