Foreign keys improvements

[TiBarification]

Describe the bug Duplicate rows in tables. For now shop doesn't have any foreign links between tables, sqlite3 supports it, mysql - supports it too. So why it didn't used before, mystery 😮

To Reproduce If there are no primary key on shop_players, then each other table have multiple rows of the same player_id. (It works only if there are no primary key in shop_players)

Expected behavior Nothing will break in database, so we won't see 'Data is loading' information message.

Server (please complete the following information):

• OS: any
• Sourcemod version: 1.10+
• Version: 3.0D4

On sqlite i rewrote SQL query on table creation. shop_toggles

CREATE TABLE shop_toggles (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    player_id INTEGER NOT NULL
                      REFERENCES shop_players (id) ON DELETE CASCADE
                                                   ON UPDATE CASCADE,
    item_id   INTEGER NOT NULL
                      REFERENCES shop_items (id) ON DELETE CASCADE
                                                 ON UPDATE CASCADE,
    state     INTEGER NOT NULL
                      DEFAULT 0
);

CREATE UNIQUE INDEX toggles_player_item_uniq ON shop_toggles (
    player_id,
    item_id
);

shop_items - must handle future problems with 2 same items

CREATE UNIQUE INDEX items_category_item_uniq ON shop_items (
    category,
    item
);

shop_boughts

CREATE TABLE shop_boughts (
    player_id  NUMERIC NOT NULL
                       REFERENCES shop_players (id) ON DELETE CASCADE
                                                    ON UPDATE CASCADE,
    item_id    INTEGER NOT NULL
                       REFERENCES shop_items (id) ON DELETE SET NULL
                                                  ON UPDATE SET NULL,
    count      NUMERIC NOT NULL,
    duration   INTEGER NOT NULL,
    timeleft   NUMERIC NOT NULL,
    buy_price  INTEGER NOT NULL,
    sell_price NUMERIC NOT NULL,
    buy_time   NUMERIC NOT NULL
);

CREATE UNIQUE INDEX boughts_player_item_uniq ON shop_boughts (
    player_id,
    item_id
);

[TiBarification]

I found a problem with cascade updating/deleting, sourcemod doesn't hook any changes in database, so such changes can corrupt player inventories in game.

[R1KO]

I found a problem with cascade updating/deleting, sourcemod doesn't hook any changes in database, so such changes can corrupt player inventories in game.

дак плевать. просто в транзакции удалять и всё

[FrozDark]

Привязки ключей, в целом, не нужны особо, но для полноты описания, добавить можно. Лучше сделайте индексацию на поля, по которым ведется поиск в условиях WHERE Не знаю, в SQLite поддерживается ли это, а в MySQL это необходимо