SQL_Query.sh - Makes SQL Queries a breeze!

[Benevolent27]

What is it?
It's a bash shell script that performs a SQL Query for you, reformatting the data in a much easier to read and parse way. It also has a bunch of added support to it for scripters, including custom wrapping of the data, a config file to store your presets into, and it does error handling for StarNet.jar results. The script was developed on Ubuntu 16.04. If you have any issues with it, feel free to let me know.

Download Link: http://lvdserver.b33r.us/files/shared/sql_query.sh (Right click and hit "Save As" and then save it to your scripting folder)
Version: 2

~Simplicity in use~
- To get help using the script, just run ./sql_query.sh
- Though the script requires StarNet.jar to run, all you gotta do is place it in the same folder as your script. It will automatically find and then use it.
- You can also store the super admin password, ip, and port that you want to use into a configuration file (named sql_query.conf) OR edit the script itself and change the values at the top. Here is a sample "sql_query.conf" file contents:

superAdminPassword=PutYourSuperAdminPasswordHere
serverIP=127.0.0.1
serverPort=4242

Note: Change the values to be appropriate for your server. Normally you'd leave the IP and port alone and just set the super admin password.

​

~ Easy To Read Results ~

Normally to do a SQL query, you have to run a command that looks like this:

java -jar StarNet.jar 127.0.0.1:4242 SUPERADMINPASSWORD "\"SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;\""

- You must include escaped quotes in the query or it will not work right and you will hammer your head against the wall for a while trying to figure out what you are doing wrong.
- This is a bit complicated to read, especially if there are a lot of results. All the columns will vary quite a bit in length, depending on what is in each column. Any scripting that parses the data also will not be able to easily separate data out from each line. Using grep and sed just won't work right.
- It requires the super admin password to be used for every query.
- All text is output on STDERR, meaning if you pipe the output anywhere, it won't work! You have to pipe your STDERR to STDOUT first before doing any parsing of the text! How annoying!
- And if anything goes wrong, StarNet.jar will not produce any errorlevel codes, so any scripting you have that needs to pull SQL data will need to parse through all the data and determine for itself if any error happened (such as a failure to connect to the server or too many results being returned overflowing the buffer, etc).

Now here is the same SQL Query being run by SQL_Query.sh:

./sql_query.sh "SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;"

Notice that all values are separated simply with just a comma and a space. Every value is labeled to be easily read. This makes using regex pattern matching on the result (such as grep and sed easy) peasey. Also, notice that I didn't have to specify the IP, PORT, or SUPERADMIN password? The script can either be edited to have this information OR it can use a "sql_query.conf" file to store the information.

And here is a another example of using StarNet.jar directly:

Look at all that gobbidlygook.

And here is the formatted result from sql_query.sh:

Much easier to read!

~Customizeability~

But, what if you don't want comma separated values? What if you want it to look different?

You can do this by setting the delimiters however you want.

Here I have it use a | character instead of the comma. (note that for bash symbols like this, you usually need to escape them with a \ character):

./sql_query.sh -delimiter=\| "SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;"

Or what if I want a delimiter to be used in the front and back as well? No problem! Let's turn those on.

./sql_query.sh -delimiter=\| -begindelimiter -enddelimiter "SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;"

Super simple.

But what if we want custom begin and end delimiters so we encapsulte each value? No problem, just add a = sign and specify what you want them to be.

./sql_query.sh -delimiter=},{ -begindelimiter={ -enddelimiter=} "SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;"

Also notice that the delimiters can be more than 1 character long! This kind of output looks a lot more more like the output you'd expect from StarMade style results.

Customize your sql_query.conf how you'd like. Here is a full list of values you can set, with some alternate delimiters:

StarNet=~/scripts/StarNet.jar
superAdminPassword=PutYourSuperAdminPasswordHere
serverIP=127.0.0.1
serverPort=4242
delimiter='"}{"'
begindelimiter='{"'
enddelimiter='"}'
equalscharacter='"="'

~ Error handling ~

Normally StarNet.jar does not return any error code, even when if it fails to connect or has some sort of java error. So you can never be 100% certain that you are getting valid results back when performaing a SQL query within one of your scripts! Argh! But wait! SQL_Query.sh will read the results and determine if any error happened for you and then return an error code upon exiting if there was an error.

Spoiler: ERROR CODES

# 0 = There were no errors
# 1 = a java exception from starnet.jar occurred
# 2 = StarNet.jar could not be found
# 3 = No SuperAdminPassword was set or it was blank
# 4 = A SuperAdminPassword was attempted but the server returned that it was wrong
# 5 = Invalid SQL query specified

# Codes from invalid arguments specified on the command line
# 10 = Invalid IP specified when setting via -ip=
# 11 = Invalid port specified when setting via -port=
# 12 = Invalid Super Admin Password specified when setting via -superadminpassword=
# 13 = No delimiter specified when setting via -delimiter=
# 14 = No end delimiter specified when setting via -enddelimiter=
# 15 = No begin delimiter specified when setting via -begindelimiter=
# 16 = No equals character specified when setting via -equalscharacter=
# 20 = no arguments specified - this will have displayed the help text

# Exit codes caused by invalid settings in the sql_query.conf file:
# 30 = Invalid IP address specified in config file
# 31 = Invalid port specified in config file
# 32 = Invalid Super Admin Password specified in config file
# 33 = Delimiter specified in config file, but it was blank
# 34 = End delimiter specified in config file, but it was blank
# 35 = Begin delimiter specified in config file, but it was blank
# 36 = Equals character specified in config file, but it was blank
# 37 = The path to StarNet.jar was set in the config file, but it was blank.
# 38 = The path to StarNet.jar was set in the config file, but it was invalid or the file was not found.

So, for example, let's say we try a query that is far too expansive and it overflows the buffer for StarNet.jar. Normally we would get a bunch of results and then a java error when the buffer overflows. But with SQL_Query.sh, instead we get just the error, the details, and an errorcode is returned by the script.



So, you can then write your script to be intelligent and only process the results when there are no errors. It might look something like this:

#!/bin/bash
results=$(./sql_query.sh "SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;"
errorlevel="$?"
If [ "${errorlevel}" == "0" ]; then
    echo "The results were found!"
    -- Process The Results Here--
else
  echo "SORRY GUY, THE SQL QUERY FAILED FOR SOME REASON!"
fi

Since an errorcode is returned when performing the query, it makes it easy to be confident that you are getting the results you need every time.

**Here is another example of an error where I specify the port.. but I provide the wrong port:**

./sql_query.sh -ip=58.23.5.56 -port=5252 -superadminpassword=ThisIsMyPassword "SELECT * FROM PUBLIC.PLAYERS WHERE NAME='benevolent27';"

No worries, an errorcode is generated. Though notice that it uses "1" as the catch-all for java.net errors right now. This is because I don't know all the java.net errors that are possible, therefore I don't want to piecemeal out the codes. You can read the code and figure it out for yourself if you want, but if you ever get an exit code of 1, you know the query did not succeed.

~ Need Help with the information from the SQL database? ~
Don't worry, I have documented the database for you, so it should be easy to grab the data you need. My guide includes starter tutorials on how to perform SQL queries, and a comprehensive breakdown on what information you can expect to retrieve from the world file. Check out my 'HSQL DATABASE DOCUMENTATION PROJECT' or head to the StarDock page, The StarMade World File and SQL Command Reference.

~ Addons ~
Below I will include support scripting which requires the sql_query.sh script. You can also use these as examples of scripting to base your own off of.

Script: findShipsByCreatedByPlayer.sh
Version: 0.3
Synopsis: This script will find all ships created by a specific player, no matter where they are in the universe.
Usage: ./findShipsByCreatedByPlayer.sh (-simple) [PlayerName] (columns,to,include)
Note: Values in [brackets] are mandatory, whereas values in (parenthesis) are optional.
Example 1: ./findShipsByCreatedByPlayer.sh Benevolent27
This will return all ships created by the player Benevolent27 and all the data for each ship.
Example 2: ./findShipsByCreatedByPlayer.sh NaStral FACTION,X,Y,Z,NAME
This will return all ships created by the player, NaStral, but only include the columns for FACTION, X, Y, Z, and NAME.
Prerequisites: Requires sql_query.sh to be in the same folder (or you can modify the script to point to the directory it is in).

Script: getProtectionNumberForSector.sh
Version: 1.0
Synopsis: This grabs the protection number for a sector. See the PROTECTION section from PUBLIC.SECTORS for a breakdown of how this number works. This scripting is needed because normal admin commands that rely on the admin command, “/sector_info,” can only see the “protected” or “peace” status of a sector. If we want to see other statuses, such as no-indications, no-enter, no-exit, etc. then we have to run a SQL query and get this number.
Usage: ./getProtectionNumberForSector.sh [X] [Y] [Z]
Example: ./getProtectionNumberForSector.sh 2 2 2
Output: 23

Script: convertSectorProtectionNumberToProtections.sh
Version: 1.0
Synopsis: This converts a sector protection number to the individual protections the sector has. It will return multiple lines, with each protection on it’s own line. Note that this script does not require sql_query.sh to function since it just does some math to determine what the number is.
Usage: ./convertSectorProtectionNumberToProtections.sh [Number]
Example:
./convertSectorProtectionNumberToProtections.sh 23
Output:
+noindications
+noenter
+protect
+peace

 

[Benevolent27]

Just a quick update:
I am have released version 2.0 of the script, which was mostly re-written and is about 4 times faster. I was able to get these improvements by doing several things:

1. I switched from pre-processing to stream processing. As a result, the scripting will return results as they come in, rather than waiting till everything has been processed and then dumping all at once. This will also save on memory buffers since everything isn't being stored in the buffer anymore.
2. I avoided using sed to remove quotes from the text and instead am using bash substitution. This was probably the biggest speed increase.
3. I streamlined the filtering by reducing the number of steps each line takes to process.

Better argument handling and error handling:
1. Arguments specified on the command line now supercede the sql_query.conf values
2. if a path to StarNet.jar is specified if a sql_query.conf file but does not exist, it will now produce a more exact error code.
3. The scripting now verifies any arguments in the sql_query.conf. If any invalid setting is found, it will exit with new error codes.