Does anyone have or use a checklist for remembering to use some things
And what are yours?
Bold of you to assume I remember anything.
10 Likes
An older version. A lot of this stuff I have SQL queries for now.
NACs: Check for missing commanders (Commanders window, available NACs)
Civilian Admins:
Check for unassigned
Check for academies without leader (select FADM, then Academy Commandant list--look for non-grayed out lines)
Auto-reassign naval officers.
Save game, refresh spreadsheet
check ground surveys status
check all pops are on Pop sheet
Check Minerals sheet for missing production or stock
Check TFPlan and OrbMin sheet for missing rows
Update Yearly sheet
Update any ColCost manual overrides
Distance Overrides
sort DistanceOverride by Expires: any manual overrides need updating?
filter TFPlan Dst column by purple color. Add non-moons (filter IsMoon column) to DistanceOverride sheet
Intel:
Update NPR population data (EM/pop/inst).
Defense Fleet Audit:
colonies with 1M+ pop
colonies nearing 1M pop
colonies with orbital production
miners
harvs
formers
Research: any big changes coming?
Econ big picture: need to change spending/building?
Fuel: production vs consumption
MSP:
production vs consumption
turn on/off production anywhere?
Minerals: any shortages, now or soon?
Terraforming:
Change/move any? Anything need timers for midyear changes?
Fleets:
Check for
orbprod fleets with wrong ships (query Orbital Production Fleets Containing Incorrect Ships)
survey fleets that finished overhaul but haven't moved
gas/ss fleets without conditional orders (query Fleets Missing Conditional Orders)
gas/ss fleets at low capacity and not a target of a send message order from a tanker/supply ship (query Stations Needing Refuel or Resupply)
gas/ss/gate/sent fleets with ships that joined inadvertently (query Engined Ships in Station Fleets)
stationary fleets not in orbit or at jp (query Station Fleets not in Orbit or at JP)
stationary fleets with move orders (query Stationary Fleets with Orders)
1km/s fleets with orders (query Fleets with Orders at 1kms)
ret fleets with no orders (query Ret Fleets with No Orders)
check all idle fleet (query Idle Fleets)
orbital miners not at mining colonies (query Orbital Miners not Mining)
idle civ fleets (query CivilianShipsWithNoOrders).
ships not in correct NACs (write query)
survey
mining
tform
ships not in range of current NAC (query?)
Any colonies without ODF?
Any OM/TF/SH fleets without ODF?
SysDef fleet deployment: check and update
Low fuel?
Low MSP?
Over deployment?
Need ordnance?
Civilian Shipping:
Set more colonies as pop destination?
Build additional infra at nearby colonies?
Check civilian fuel harvesters.
Colonies:
check for
NEW:
CMC colonies:
a) set to tax but has governor assigned
b) set to buy but has no gov assigned
<100% usage of confacs, ordfacs, ftrfacs (query Populations with Partial Production)
populations with no governor
set colonizing status
set production
check minerals/msp/fuel
installation shipments to queue?
Orbital Mining:
Check for fleets remaining at empty bodies
Picks to move where
mds needed
Minerals to scoop?
CMC: change buy/sell orders?
Fleets:
Gas/Gates need refills?
Surveyors
Patrol ships
Intel
Diplo
Save again.
My current mode is to run these SQL queries and do the needed for any that have results:
3 Likes
What are SQL queries?
SQL = Structured Query Language
https://en.wikipedia.org/wiki/SQL
Okay, so you know how Aurora has a database? Ie the Aurora.db file? Well you can query the database using SQL commands; SQL commands can both alter the database and read from it. Now, you can’t directly open a database file right out of the box, if you haven’t tangled with SQL before you likely don’t have an application capable of opening a database file. I’d advise DB Browser for SQLite (https://sqlitebrowser.org/);
Now, before you do anything to the Aurora database file, you’re gonna wanna back it up (ie copy the file and paste it somewhere). There’s a lot to cover with SQL Commands, but lets give an example;
Lets say you opened the FCT_Game table in the database. The highest GameID in the leftmost column is your most recent game and if you look to the right you can see the name you gave to the game when you first started under GameName. Lets presume your GameID is 140. Lets get your RaceID; To do that we can go to FCT_Race and filter the GameID column by the GameID we saw in FCT_Game. Then we filter the NPR column with 0 (0 is player, 1 is NPR) and that leaves us with all Player Races for that game. Lets say your RaceID is 99.Now you might be wondering, where are the sql queries? Why are we getting this?
Well let’s say we want to check to see if we forgot to repair any of our ships after a major skirmish.
Well first, we go to the FCT_Fleet table and we need all Fleets that belong to our race for this game. Now we could manually filter out by entering the GameID and RaceID… or we could write a SQL Query.
‘SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99’
This does just that;
But wait-- that grabs civillian ships as well.
We can filter them out by adding CivilianFunction=0 to out SQL Query such that it becomes: ‘SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0’
So now we have a list of FleetIDs, but what now?
Well now we go to the FCT_Ship and we want to fetch every ship belonging to one of those FleetIDs.
‘SELECT ShipID FROM FCT_Ship WHERE FleetID IN (FleetID1, FleetID2, FleetID3, …)’
But wait.. that sounds inefficient having to manually enter it in… and you’d be right, we can skip this tedium by just taking the prior query and sticking that in the parenthesis.
So now we have: ‘SELECT ShipID FROM FCT_Ship WHERE FleetID IN (SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0)’
This gives us all ShipIDs, but we still don’t know which of these are damaged. Well, fortunately there is a table just for that, FCT_ArmourDamage.
Like before, we’ll chain queries. Since all we’re looking for is damage, we’ll simply check if a ShipID exists in the table.
‘SELECT DISTINCT ShipID FROM FCT_ArmourDamage WHERE ShipID IN (SELECT ShipID FROM FCT_Ship WHERE FleetID IN (SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0))’
(We add DISTINCT so we don’t get multiple entries for the same ship)
Now we have a list of ShipIDs that are damaged. But wait-- we don’t know what ships they are.
But we have a table just for that. Going back to FCT_Ship, we chain our query once more;
‘SELECT ShipName, FleetID FROM FCT_Ship WHERE ShipID IN (SELECT DISTINCT ShipID FROM FCT_ArmourDamage WHERE ShipID IN (SELECT ShipID FROM FCT_Ship WHERE FleetID IN (SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0)))’
That gives us the name of the ships… but what about the fleet they’re in?
‘SELECT fleettable.FleetName, shiptable.ShipName FROM FCT_Ship shiptable INNER JOIN FCT_Fleet fleettable ON shiptable.FleetID = fleettable.FleetID WHERE ShipID IN (SELECT DISTINCT ShipID FROM FCT_ArmourDamage WHERE ShipID IN (SELECT ShipID FROM FCT_Ship WHERE FleetID IN (SELECT FleetID FROM FCT_Fleet WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0)))’
(This took some finangling, so let me explain:
The initial sql query would’ve been ‘SELECT FleetName FROM FCT_Fleet WHERE FleetID IN (OurOtherQueriesHere)’
But here’s the thing, that would’ve just returned the FleetName. So it turns out we can do something called Joining which I absolutely don’t really understand as well as defining the two tables as variables we can compare and that lets us get a list with FleetNames on the left and shipnames on the right.
So now we have a list of ships that are damaged and the fleet they’re in. Alternatively, we could’ve stopped at getting the ShipName and simply looking at the class design window and double clicking on the ship name there to take us to the fleet (as it’d open the naval org window I believe with the fleet in question selected).
You can do a lot with SQL Commands as Skroomit’s extensive lists of SQL Queries can testify to.
If you’re still curious, I’d advise giving (SQLite Tutorial - GeeksforGeeks) a look and checking out each of the links on the page explaining what each sql command is.
4 Likes
You definitely need to learn about joins 
A relational database is called that because the tables are related to each other. For example, you could have a Ship table where all the information is held, so the Ship Name, Fleet Name, Race Name, Fleet Position, etc. but that would involve a lot of duplication.
So instead, you put all the fleet information in one table and give each fleet a Fleet ID. Then you put that Fleet ID in the ship table, so the ship can reference all the related fleet information. You do that by joining the tables together. Its also easier if you give each table a short alias after specifying it, so you don’t have to write the full table every time.
So the following…
Select * from Ship s
JOIN Fleet f on f.FleetID = s.FleetID
…will return all the ship data, plus all the fleet data for the ships in the ship table. You can also add the race table to return all the race data for each ship too.
Select * from Ship s
JOIN Fleet f on f.FleetID = s.FleetID
JOIN Race r on r.RaceID = s.RaceID
However, that will only return the ship if a fleet record exists to join to (also known as an INNER JOIN). If instead you wrote…
Select * from Ship s
LEFT JOIN Fleet f on f.FleetID = s.FleetID
That would give you every ship, plus the fleet information where a fleet exists. For ships without fleets, the fleet columns would be null.
SQL is a very deep rabbit hole, but unlike most languages you can accomplish a lot with very basic knowledge.
6 Likes
Because I’m a grumpy old hacker, please indulge me.
Your version, formatted:
SELECT
fleettable.FleetName
, shiptable.ShipName
FROM FCT_Ship shiptable
INNER JOIN FCT_Fleet fleettable ON shiptable.FleetID = fleettable.FleetID
WHERE ShipID IN
(
SELECT DISTINCT ShipID FROM FCT_ArmourDamage WHERE ShipID IN
(
SELECT ShipID FROM FCT_Ship WHERE FleetID IN
(
SELECT FleetID FROM FCT_Fleet
WHERE GameID=140 AND RaceID=99 AND CivilianFunction=0
)
)
)
You use a where clause with a subselect (that itself uses cascading subselects in where clauses) to connect the fleet and ship tables to the armor damage table, even though you already had the fleet and ship tables in your query.
And then you put your primary filters (gameid, raceid, CivilianFunction) at the end of the cascade of subselect where clauses.
In other words, you reached all the way around behind your back to scratch your ear.
Instead:
SELECT
fleettable.FleetName
, shiptable.ShipName
FROM FCT_Ship shiptable
INNER JOIN FCT_Fleet fleettable ON shiptable.FleetID = fleettable.FleetID
WHERE ShipID IN
(
SELECT DISTINCT ShipID FROM FCT_ArmourDamage
)
AND
fleettable.GameID=140
AND
fleettable.RaceID=99
AND
fleettable.CivilianFunction=0
Finally, if you take a slightly different approach (inner join to a subselect from the armour damage table, instead of using a simple subselect in the where clause), you can get back some useful information about the damage
SELECT
fleettable.FleetName
, shiptable.ShipName
, armourdamagetable.TotalDamage
FROM FCT_Ship shiptable
INNER JOIN FCT_Fleet fleettable ON shiptable.FleetID = fleettable.FleetID
INNER JOIN
(
SELECT ShipID, sum(damage) as TotalDamage FROM FCT_ArmourDamage group by ShipID
) as armourdamagetable on armourdamagetable.ShipID = shiptable.ShipID
WHERE
fleettable.GameID=140
AND
fleettable.RaceID=99
AND
fleettable.CivilianFunction=0
1 Like