The StarMade World File and SQL Command Reference

[Benevolent27]

Good afternoon folks!

So. the /sql_query command is HUGE, but you're not going to realize this till you know how to use SQL queries and also know what data is actually inside of the world file and also how FAST these queries are.

So, to help the public out a bit, I wrote some how-to guides and detailed out the entire world file for you (well, a good part of it). So far I've been using my reference guide to do custom /sql_query commands to the server I admin for and..
1. Generate lists of ships and their locations which were created by or modified by a specific player or by a specific faction.
2. Track fleets movements and see who owns what fleet ships.
3. I built a script that easily grabs spacial coordinates for all the ships in a sector without having to send individual queries per ship, so it is lightning fast.

In the future, I will be building sets of scripts that gather information that was not possible previously or to perform queries that used to be too slow to be practical. For example, a new command may tell you if the ship a player is in is currently docked to another entity and if so, what the UID is. Another command will tell you ALL of the statuses of a sector, such as noindications, noexit, noenter, and nofploss. Right now admin commands can only reveal protect and peace, since these are the only two displayed in the /sector_info command.

To give an example of speed, I created a script that was as optimized as possible to detect the star in a system. This would take 0.5 seconds for centered stars. Typically around 8-12 seconds for double_star systems, and 30-36 seconds for void sectors. With a SQL query command, however, it ALWAYS takes less than 0.5 seconds. This is useful for an event engine I'll be creating which will have events happen near players randomly, but only in sectors that are far enough from a star to be safe for the player to travel to.

So here's a link to the google doc.
StarMade HSQL DATABASE DOCUMENTATION PROJECT

And here are some screenies:

 

[Crusade]

I'm pinning this, because i believe this is super useful.

 

[Benevolent27]

I'm pinning this, because i believe this is super useful.

Thank you!

 

[Benevolent27]

I've begun including some scripts, including sql_query.sh. I created a new sub-section within the document just for scripts.

(There are no scripts for windows yet - this is just a placeholder - if you want to submit some, let me know).

For Linux Only (tested on Ubuntu 16.04):
The sql_query.sh script is the main script which all my other scripts will rely on. It simplifies doing SQL queries and reformats the output so that it is easier to read and parse.
For example, here is how a person might perform a SQL query from the command line by doing a direct query with StarNet.jar.
Command: java -jar StarNet.jar 127.0.0.1:4242 SuperAdminPasswordHere /sql_query “\”SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;\””

Look at all that complicated business. We have to include the server IP, port, super admin password, in every query! We also gotta make sure to do the double quotes just right. And then the output is all messy and difficult to make sense of AND it includes extraneous information that we don't need.

Here we do the same query with sql_query.sh.
Command: ./sql_query.sh “SELECT * FROM PUBLIC.SECTORS WHERE X=2 AND Y=2 AND Z=2;”

Look at that beautiful simplicity. Every line has all the values labeled and separated by commas and a space. This can be easily used by admins or for scripts that then parse the output.

As an example of a script that utilizes sql_query.sh, I included "findShipsByCreatedByPlayer.sh", which will return all ships in the entire universe that were created by a specific player.

To download these scripts, head on over to the reference file by clicking here.

 

[Benevolent27]

I'm going to post my SQL_query and other commands that rely on it under a different thread, because really it's a different project than the documentation project.

 

[Asvarduil]

Question - I tried the database client setup steps using DBeaver, a universal SQL client, but when I point a connection to a Universe's /index directory, I don't see any tables.

My URL I'm connecting to is variously either a universe in my Steam StarMade directory (there are spaces), or to a special folder off of my C:\ drive I called "SMResearch", where I just copied the index directory to. (URL would be: jdbc:hsqldb:file:C:\SMResearch\index)

I'm using SA as the username, no password. What am I doing wrong?

Also, necromancy, but hopefully legitimate necromancy!

 

[Benevolent27]

HSQL documentation has been updated. With weapons 3.0, there were 2 sections added:
1. PUBLIC.ID_GEN_TABLE
2. PUBLIC.MINES
[doublepost=1531726575,1531726518][/doublepost]

Question - I tried the database client setup steps using DBeaver, a universal SQL client, but when I point a connection to a Universe's /index directory, I don't see any tables.

My URL I'm connecting to is variously either a universe in my Steam StarMade directory (there are spaces), or to a special folder off of my C:\ drive I called "SMResearch", where I just copied the index directory to. (URL would be: jdbc:hsqldb:file:C:\SMResearch\index)

I'm using SA as the username, no password. What am I doing wrong?

Also, necromancy, but hopefully legitimate necromancy!

Sorry for the late reply.

I have no idea how to use "DBeaver". I'd suggest following up on their support forums IF the client supports HSQL databases. Otherwise I'd suggest you follow the instructions in the HSQL Documentation Project to set up the HSQL DB program, which is free and easy to use.

 

[Benevolent27]

Question - I tried the database client setup steps using DBeaver, a universal SQL client, but when I point a connection to a Universe's /index directory, I don't see any tables.

My URL I'm connecting to is variously either a universe in my Steam StarMade directory (there are spaces), or to a special folder off of my C:\ drive I called "SMResearch", where I just copied the index directory to. (URL would be: jdbc:hsqldb:file:C:\SMResearch\index)

I'm using SA as the username, no password. What am I doing wrong?

Also, necromancy, but hopefully legitimate necromancy!

Sorry for the very late response, but I wouldn't know how to use DBeaver. I suggest using the HSQL database reader and instructions I posted in the document. HSQL databases normally have a name that is used, but for StarMade, there is no such name. The HSQL database reader doesn't have a problem with this ,but perhaps DBeaver does? I don't know.