Closed Klensor closed 4 years ago
Update: ignore previous db changes! Use this instead...
CREATE TABLE esrcuser
.wh_systemstatics
(
System
VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
StaticType
VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (System
,StaticType
)
) ENGINE = InnoDB;
I don't really like this. Not Klensor's fault. But the page already goes to the database twice for each system, for wh_info and for wh_tpe and now we add a third roundtrip to get wh_statics - This is a db structure issue right? should we not get all of this info (and more if we need it) in one single request?
I need to look at the database.
Orsel, Klensor, am I seeing this wrong?
From my point of view it's fine with three selects:
The current implementation with concat if more CPU intense than doing an extra select cost. This result can not be cached by the db server normally. And this adds a logical binding between code and database.
The extra select will be known (execution plan) and data is cached already by the db server. The round trip through the existing connection should not be an issue.
My shared hosters DB reports:
Query took 0.0002 seconds.
The query is:
select * from wh_types types, wh_systemstatics statics where statics.System = 'J001694' and statics.StaticType = types.name;
@Klensor
change 'name' to 'type' and add a primary key on 'type' would make the columns more descriptive:
CREATE TABLE wh_types ( type VARCHAR(4) NOT NULL , static BOOLEAN NOT NULL , destination VARCHAR(10) NOT NULL , lifetime INT NULL , size INT NULL, PRIMARY KEY (type)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;
renaming 'wh_systemstatics' to 'wh_statics'
the function function getShipSizeLimit($size)
feels to be more a function of the systems-class. It releates to the class somehow. But I'm fine to have it in the current location at the moment. Can be moved later if necessary.
Would be nive if @adeadparrot can respond before you change the PR.
Required DB changes
new column for wh_systems table:
ALTER TABLE
wh_systems
ADDStatics
VARCHAR(25) CHARACTER SET latin1 COLLATE latin1_general_ci NULL AFTERPlanetCount
;new table "wh types":
CREATE TABLE
esrcuser
.wh_types
(name
VARCHAR(4) NOT NULL ,static
BOOLEAN NOT NULL ,destination
VARCHAR(10) NOT NULL ,lifetime
INT NULL ,size
INT NULL ) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci;See attached Insert / Update scripts for data:
whStaticsDbData.zip