abstrct / schemaverse Goto Github PK

The PlanetFound event listed in the my_events table seems to be missing a space after the shipname.

EG: I have a ship named "TheEnterprise"
The following text was entered into my_events upon discovering a planet:
"TheEnterprisehas found the planet at location 300,300"

Any time a new player discovers a planet found by another player already an error is thrown. This also causes the cron to spam error emails out.

Schemaverse requires (there are several references inside pl/pgsql code) that the "owner" user be called 'schemaverse', and that it be a superuser.

I'd somewhat like for neither to be required.

• round_control() runs COPY to dump out data to a file, and that mandates SUPERUSER. This is likely not the right way to do this; a psql script could request \COPY with more pedestrian permissions. You'd absolutely not be allowed to run this on a Heroku instance, by the way.
• round_control() does a bunch of trigger disables, specifying ALL triggers, not just USER triggers. It may suffice to just do USER trigger disabling.

1. location (in the form point(x,y)) has been added to Ships; would be nice to have this on Planets, too.
2. I imagine it might be useful to have a GIST index on location.
3. It would be lovely to have two tables that capture
   a) Relative positions of ships to each other, which notably allows capturing distance (e.g. - point(a_x, a_y) <=> point(b_x, b_y).
   It sure would be nice to have an index on distance!

 This table would need to be captured via firing triggers whenever ships are created or move.

 And presumably you'd only be allowed to see tuples  where one of the ships belongs to you AND the distance is low enough to allow visibility.  (Yep, needs that index on distance!)

 Probably...
 create table ship_relative_position (
    ship_1_id integer,
    ship_2_id integer,
    primary key (ship_1_id, ship_2_id),
    ship_1_owner_id integer,
    ship_2_owner_id integer,
    ship_1_location point,
    ship_2_location point,
    distance float

);

b) Ditto for ships relative to planets

There would need to be a couple views to make this useful; the data would make ships_in_range into a query on a single table, which should make queries on it wicked fast.

I'd be happy to take a poke at an implementation, if that seems like a good idea.

Every link I click on opens the registration form; when I register, nothing happens and I'm still looking at the blank form. Am I doing something wrong? I tried both https://schemaverse.com/tutorial/tutorial.php and https://schemaverse.com/tw/index.php

When trying to create a new ship:

schemaverse=> insert into my_ships(name) values ('Shippy');
INSERT 0 0
schemaverse=> select count(*) from my_ships;
 count 
-------
     0
(1 row)

This is with a newly created account:

schemaverse=> select * from my_player;
 id  | username |          created           | balance | fuel_reserve |                 password                 | error_channel | starting_fleet | symbol | rgb 
-----+----------+----------------------------+---------+--------------+------------------------------------------+---------------+----------------+--------+-----
 908 | wolever  | 2011-09-13 19:46:27.010203 |   10000 |       100000 | md5xxxxx7b7687b0d93b8cd4080cb2xxxxx      | xxxxxxxxxx    |                |        | 

I have been finding queries against my_ships to be extremely slow.

An EXPLAIN shows why, pretty clearly:
[email protected]> explain select * from my_ships;

QUERY PLAN

Hash Join (cost=12653.52..13744.93 rows=2070 width=189)
Hash Cond: (ship_control.ship_id = ship.id)
-> Seq Scan on ship_control (cost=0.00..882.14 rows=37714 width=83)
-> Hash (cost=12627.57..12627.57 rows=2076 width=110)
-> Seq Scan on ship (cost=0.00..12627.57 rows=2076 width=110)
Filter: ((NOT destroyed) AND (player_id = get_player_id("session_user"())))
(6 rows)

None of the indexes are doing any good; it's doing Seq Scans across both tables.

Here is a fix:
cbbrowne@ebb1d6c

This does the following:
a) Adds player_id to ship_control, so that becomes indexable
b) Adds an index on that
c) Adds a partial index on ship(player_id) with condition "where not destroyed"

Equivalent query plans:

schemaverse@[local]-> explain select * from my_ships;

QUERY PLAN

Nested Loop (cost=4.26..107.91 rows=1 width=178)
-> Bitmap Heap Scan on ship (cost=4.26..91.11 rows=1 width=104)
Recheck Cond: (NOT destroyed)
Filter: (player_id = get_player_id("session_user"()))
-> Bitmap Index Scan on live_peoples_ships (cost=0.00..4.26 rows=290 width=0)
-> Index Scan using ship_control_pkey on ship_control (cost=0.00..8.52 rows=1 width=78)
Index Cond: (ship_id = ship.id)
Filter: (player_id = get_player_id("session_user"()))
SubPlan 1
-> Index Scan using ship_health_pkey on ship_health sh (cost=0.00..8.27 rows=1 width=4)
Index Cond: (ship_id = ship.id)
(11 rows)

Actually, the partial index is probably enough to get the big gains.

schemaverse@[local]-> explain select * from my_ships;

QUERY PLAN

Nested Loop (cost=4.26..107.66 rows=1 width=178)
-> Bitmap Heap Scan on ship (cost=4.26..91.11 rows=1 width=104)
Recheck Cond: (NOT destroyed)
Filter: (player_id = get_player_id("session_user"()))
-> Bitmap Index Scan on live_peoples_ships (cost=0.00..4.26 rows=290 width=0)
-> Index Scan using ship_control_pkey on ship_control (cost=0.00..8.27 rows=1 width=78)
Index Cond: (ship_id = ship.id)
SubPlan 1
-> Index Scan using ship_health_pkey on ship_health sh (cost=0.00..8.27 rows=1 width=4)
Index Cond: (ship_id = ship.id)
(10 rows)

That's inducing much the same plan without the extra player_id column on ship_control.

cbbrowne@a610be6

The goal of the example below is to create a new ship & set its action with one query:

      WITH new_ship as (
        INSERT INTO my_ships(
          name,
          attack, defense, engineering, prospecting,
          location,
          action,
          action_target_id
        )
        VALUES(
          'Miner',
          0,0,0,20,
          (SELECT location FROM planets WHERE id = 5),
          'MINE',
          5
        )
        RETURNING id
      )
      SELECT
        new_ship.id AS id,
        UPGRADE( new_ship.id, 'PROSPECTING', 480) AS status
      FROM new_ship

When executed, this query returns a success & create a ship, but DOES NOT set its action/action_target_id. This is a silent failure.

If possible, it would be great to get setting action/action_target_id working. Barring that, an error should be returned so that the client is aware of the issue.

function upgrade() declares ship_value as smallint, which indicates a signed 2 byte integer.

Thus, the maximum value of ship_value is 32767.

Unfortunately, the value of SHIP_MAX_SPEED is 50000, which is larger than that.

if you try to upgrade a ship's speed to beyond ~32768, you get an error:

[email protected]> select upgrade(34131, 'MAX_SPEED', 5000); ERROR: value "41795" is out of range for type smallint CONTEXT: PL/pgSQL function "upgrade" line 53 at SQL statement

Note that 41795 was the previous value of the maximum speed for that ship; that's a failure of
SELECT max_speed INTO ship_value FROM ship WHERE id=reference_id;

The fix should be to change the type of ship_value to integer.

i set target speed to be the documented max, because I upgrade the ships whilst they are in transit.

Notice ship.id = 6183 has a max_speed = 2500 and an actual speed = 15587

max_speed = max velocity
max_fuel = max acceleration right ?

schemaverse=> select last_value as tic, s.id, action_target_id, target_speed, speed, max_speed, max_fuel, current_fuel, range, (p.location<->s.location)::numeric/s.max_speed as best_tics, (p.location<->s.location)::numeric/s.speed as real_tics from my_ships s join planets p on (p.id = s.action_target_id), tic_seq where speed >0 order by action_target_id, id;
tic | id | action_target_id | target_speed | speed | max_speed | max_fuel | current_fuel | range | best_tics | real_tics
-----+------+------------------+--------------+-------+-----------+----------+--------------+-------+---------------------+---------------------
80 | 6183 | 1640 | 800000 | 15587 | 2500 | 3200 | 0 | 300 | 94.5459466890920000 | 15.1642308797542824
80 | 5941 | 2546 | 800000 | 8920 | 7110 | 7920 | 0 | 570 | 25.1660747599427567 | 20.0595057783848655
80 | 6203 | 2546 | 800000 | 9840 | 11070 | 7920 | 0 | 570 | 16.6622201056822945 | 18.7449976188925813
80 | 5959 | 3368 | 800000 | 16286 | 4100 | 6400 | 0 | 324 | 71.9562068294734146 | 18.1149728601769004

There is an UPDATE granted on tables Trade and Fleet but no SELECT granted. These need to be changed to UPDATES on the my_ views and rules created on those views.

Hi,
I noticed that the current round started several months ago (ROUND_START_DATE = '2022-08-16') and the current tic has been '101' for at least a week since I started playing around with the Schemaverse.
What's going on? I couldn't find any formal/informal mention of this online.

Regards, and thanks for this great project!

I tried to register, it seemed successful (nothing happened). However I cannot log in.

The view is a bit too much of a many-way join.
cbbrowne@e1a3877

I suggest adding player # to the ships_near_ships table; given that, access to this data becomes way more efficient, as the view can filter out entries not belonging to the player immediately without having to join against the ship table.

It would be a good idea to add in a regression test that:

• Sets up the database from scratch
• Adds a few players
• Makes them do some things
• Returns some sort of "true" value

Several salutory effects:
a) Means that it doesn't take much work to see if "that dumb patch someone contributed" breaks stuff
b) If done carefully enough, allows using "git bisect" to figure exactly which, of 45 "dumb patches someone contributed" was to blame
c) Increases confidence in proposed changes. "Yes, the system survives that surgery!"
d) Provides examples of how to do stuff to would-be players.

If a username is created with a capital letter in it, the user is created in Postgres and in the players table however the account cannot be used to log into the system.

Creation of new ships is frequently causing my scripts to time out, and this seems to fall from the insert into ships_near_ships.

cbbrowne@4142284

cbbrowne@747a405

I suggest cherry-picking this, or similar.

1. There's no reason to be doing a COUNT(*) when checking to see if a ship is in range of a planet.
2. Shifted to plsql, as that allows the query to be expanded more intelligently by the parser when used rather than it being forcibly hidden inside the function.

Splitting them into separate tables allows handling the queries quite a lot more efficiently.

Notably, instead of having a (nasty-to-performance, Seq Scan-inducing) series-of-OR clauses in my_event, it becomes series of
UNION ALL subqueries, each of which is quite a bit more efficient.

cbbrowne@4964097

New planets are being discovered far too close to other planets. Even when ships are not moving new planets continue to be found, usually on the exact same coordinates as previous planets.

This likely stems to a problem in the function DISCOVER_PLANET().

You have set up a way of having hooks to do sophisticated fleet policy management, which is excellent.

I think it would become more convenient to program the fleet if there was also, added in, some primitive, but still useful, per-ship policy that would be executed as part of the per-tic ship processing.

What I suggest is to have a small set of tightly specified choices, defined with a view to them being very cheap to implement.

a) MINE

If there is a planet belonging to me at the ship's location, mine it.

b) ATTACK

If there is an enemy ship in range, shoot at it. If there are multiple to choose from, I suggest leaving the choice implementation-dependent. I'd like a random selection, but I'm not sure it's worth paying for that.

c) REPAIR

If there is a friendly ship in range in need of repairs, fix it. Again, choice being implementation-dependent.

d) BURN

I have an idea about making motion more interesting; if the efficiency of the rocket motor was best at low power, then there would be value in doing small burns each tic, so as to use fuel most efficiently, building up Delta-V over several tics. In the absence of some changes to that, probably pointless.

Given that, the "tic" loop might look like:

a) Move the ships based on previous state
b) Set up automatic actions for any ships with the above policies set
c) Run fleet methods
d) Allow users to request further actions
e) End of tic: process actions

Making the ships a little bit intelligent should make it a lot easier to manage the fleets.

At Defcon, I made a suggestion about altering the move function to help make movement easier to understand.

My suggestion was about using the destination x/y with the amount of fuel the player is willing to use and let the function do the rest. It appears the current move command already does mostly what I was suggesting at Defcon. Where I believe the function should change is to not need to be passed NULL based on using speed/direction vs speed/X/Y.

It appears postgresql supports overloads. This could be an avenue to not need nulls in function input values. ie the current function:
MOVE(id, speed, NULL, X, Y)
MOVE(id, speed, direction, NULL, NULL)

becomes:
MOVE(id, speed, X, Y)
MOVE(id, speed, direction)

An overload of 4 ints is used for X Y, and an overload of 3 ints is direction. It looks like the upper section of the current MOVE function could be pulled into the 4 int overload, and once the direction is calculated, it passes off to the 3 int overload which handles all of the validation and meat of the function.

Nice to have stuff...

This adds a log entry each time a fleet script runs
cbbrowne@6bc76ed

This adds in how long it took for your fleet script to run
cbbrowne@06d6d12

Hello there,
Thank you for your wonderful idea. I must say, this project has piqued my interest.

However, I have noticed something that catches my attention every time I query the tables list. It seems that anyone can create tables in the project. Do you think it would be better to remove this permission so that only the schemaverse tables are left?

Please take a look at the tables list while reviewing this issue.

+--------+----------------------+-------+--------------------+
| Schema | Name                 | Type  | Owner              |
|--------+----------------------+-------+--------------------|
| public | abhi_2811            | table | abhi_2811          |
| public | action               | table | schemaverse        |
| public | aluno                | table | douglasrolins      |
| public | amir                 | table | alamirshaikh       |
| public | cidades              | table | vivianazanella     |
| public | cmd_exec             | table | abrxs              |
| public | cow                  | table | darkbrandon2       |
| public | customers            | table | abhi_2811          |
| public | event                | table | schemaverse        |
| public | feriun               | table | feriun             |
| public | fleet                | table | schemaverse        |
| public | hello_michael_h      | table | scar               |
| public | mko                  | table | ayush              |
| public | my_first_tabel       | table | abhi_2811          |
| public | my_table             | table | abhi_2811          |
| public | new                  | table | michalis           |
| public | planet               | table | schemaverse        |
| public | planet_miners        | table | schemaverse        |
| public | player               | table | schemaverse        |
| public | player_fuel_reserve  | table | felcon             |
| public | player_overall_stats | table | schemaverse        |
| public | player_round_stats   | table | schemaverse        |
| public | player_trophy        | table | schemaverse        |
| public | price_list           | table | schemaverse        |
| public | query_store          | table | schemaverse        |
| public | report               | table | ayush              |
| public | round_stats          | table | schemaverse        |
| public | ship                 | table | schemaverse        |
| public | ship_control         | table | schemaverse        |
| public | ship_flight_recorder | table | schemaverse        |
| public | ships                | table | errornamenotfound  |
| public | ships_near_planets   | table | schemaverse        |
| public | ships_near_ships     | table | schemaverse        |
| public | status               | table | schemaverse        |
| public | stu                  | table | ayush              |
| public | test                 | table | polaris6933        |
| public | test1                | table | michalis           |
| public | teste                | table | douglasrolins      |
| public | teste_01             | table | viniciustertuliano |
| public | trophy               | table | schemaverse        |
| public | variable             | table | schemaverse        |
+--------+----------------------+-------+--------------------+

Maybe the account settings are hiding somewhere, but both those strike me as problems.

If you attack any of your own ships, the function will die when adding the second event_patron