Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Bird: inconsistent between tables.json and database schema #166

Open
tshu-w opened this issue Oct 26, 2024 · 0 comments
Open

Bird: inconsistent between tables.json and database schema #166

tshu-w opened this issue Oct 26, 2024 · 0 comments

Comments

@tshu-w
Copy link

tshu-w commented Oct 26, 2024

The european_football_2 in tables only contains the following tables while the schema of database file is more complicated.

        "db_id": "european_football_2",
        "table_names_original": [
            "Player_Attributes",
            "Player",
            "League",
            "Country",
            "Team",
            "Team_Attributes",
            "Match"
        ],
        "table_names": [
            "Player Attributes",
            "Player",
            "League",
            "Country",
            "Team",
            "Team Attributes",
            "Match"
        ],

> .schema
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "Player_Attributes" (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`player_fifa_api_id`	INTEGER,
	`player_api_id`	INTEGER,
	`date`	TEXT,
	`overall_rating`	INTEGER,
	`potential`	INTEGER,
	`preferred_foot`	TEXT,
	`attacking_work_rate`	TEXT,
	`defensive_work_rate`	TEXT,
	`crossing`	INTEGER,
	`finishing`	INTEGER,
	`heading_accuracy`	INTEGER,
	`short_passing`	INTEGER,
	`volleys`	INTEGER,
	`dribbling`	INTEGER,
	`curve`	INTEGER,
	`free_kick_accuracy`	INTEGER,
	`long_passing`	INTEGER,
	`ball_control`	INTEGER,
	`acceleration`	INTEGER,
	`sprint_speed`	INTEGER,
	`agility`	INTEGER,
	`reactions`	INTEGER,
	`balance`	INTEGER,
	`shot_power`	INTEGER,
	`jumping`	INTEGER,
	`stamina`	INTEGER,
	`strength`	INTEGER,
	`long_shots`	INTEGER,
	`aggression`	INTEGER,
	`interceptions`	INTEGER,
	`positioning`	INTEGER,
	`vision`	INTEGER,
	`penalties`	INTEGER,
	`marking`	INTEGER,
	`standing_tackle`	INTEGER,
	`sliding_tackle`	INTEGER,
	`gk_diving`	INTEGER,
	`gk_handling`	INTEGER,
	`gk_kicking`	INTEGER,
	`gk_positioning`	INTEGER,
	`gk_reflexes`	INTEGER,
	FOREIGN KEY(`player_fifa_api_id`) REFERENCES `Player`(`player_fifa_api_id`),
	FOREIGN KEY(`player_api_id`) REFERENCES `Player`(`player_api_id`)
);
CREATE TABLE `Player` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`player_api_id`	INTEGER UNIQUE,
	`player_name`	TEXT,
	`player_fifa_api_id`	INTEGER UNIQUE,
	`birthday`	TEXT,
	`height`	INTEGER,
	`weight`	INTEGER
);
CREATE TABLE `League` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`country_id`	INTEGER,
	`name`	TEXT UNIQUE,
	FOREIGN KEY(`country_id`) REFERENCES `country`(`id`)
);
CREATE TABLE `Country` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`name`	TEXT UNIQUE
);
CREATE TABLE IF NOT EXISTS "Team" (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`team_api_id`	INTEGER UNIQUE,
	`team_fifa_api_id`	INTEGER,
	`team_long_name`	TEXT,
	`team_short_name`	TEXT
);
CREATE TABLE `Team_Attributes` (
	`id`	INTEGER PRIMARY KEY AUTOINCREMENT,
	`team_fifa_api_id`	INTEGER,
	`team_api_id`	INTEGER,
	`date`	TEXT,
	`buildUpPlaySpeed`	INTEGER,
	`buildUpPlaySpeedClass`	TEXT,
	`buildUpPlayDribbling`	INTEGER,
	`buildUpPlayDribblingClass`	TEXT,
	`buildUpPlayPassing`	INTEGER,
	`buildUpPlayPassingClass`	TEXT,
	`buildUpPlayPositioningClass`	TEXT,
	`chanceCreationPassing`	INTEGER,
	`chanceCreationPassingClass`	TEXT,
	`chanceCreationCrossing`	INTEGER,
	`chanceCreationCrossingClass`	TEXT,
	`chanceCreationShooting`	INTEGER,
	`chanceCreationShootingClass`	TEXT,
	`chanceCreationPositioningClass`	TEXT,
	`defencePressure`	INTEGER,
	`defencePressureClass`	TEXT,
	`defenceAggression`	INTEGER,
	`defenceAggressionClass`	TEXT,
	`defenceTeamWidth`	INTEGER,
	`defenceTeamWidthClass`	TEXT,
	`defenceDefenderLineClass`	TEXT,
	FOREIGN KEY(`team_fifa_api_id`) REFERENCES `Team`(`team_fifa_api_id`),
	FOREIGN KEY(`team_api_id`) REFERENCES `Team`(`team_api_id`)
);
CREATE TABLE IF NOT EXISTS "Match"
(
    id               INTEGER
        primary key autoincrement,
    country_id       INTEGER
        references Country,
    league_id        INTEGER
        references League,
    season           TEXT,
    stage            INTEGER,
    date             TEXT,
    match_api_id     INTEGER
        unique,
    home_team_api_id INTEGER
        references Team (team_api_id),
    away_team_api_id INTEGER
        references Team (team_api_id),
    home_team_goal   INTEGER,
    away_team_goal   INTEGER,
    home_player_X1   INTEGER,
    home_player_X2   INTEGER,
    home_player_X3   INTEGER,
    home_player_X4   INTEGER,
    home_player_X5   INTEGER,
    home_player_X6   INTEGER,
    home_player_X7   INTEGER,
    home_player_X8   INTEGER,
    home_player_X9   INTEGER,
    home_player_X10  INTEGER,
    home_player_X11  INTEGER,
    away_player_X1   INTEGER,
    away_player_X2   INTEGER,
    away_player_X3   INTEGER,
    away_player_X4   INTEGER,
    away_player_X5   INTEGER,
    away_player_X6   INTEGER,
    away_player_X7   INTEGER,
    away_player_X8   INTEGER,
    away_player_X9   INTEGER,
    away_player_X10  INTEGER,
    away_player_X11  INTEGER,
    home_player_Y1   INTEGER,
    home_player_Y2   INTEGER,
    home_player_Y3   INTEGER,
    home_player_Y4   INTEGER,
    home_player_Y5   INTEGER,
    home_player_Y6   INTEGER,
    home_player_Y7   INTEGER,
    home_player_Y8   INTEGER,
    home_player_Y9   INTEGER,
    home_player_Y10  INTEGER,
    home_player_Y11  INTEGER,
    away_player_Y1   INTEGER,
    away_player_Y2   INTEGER,
    away_player_Y3   INTEGER,
    away_player_Y4   INTEGER,
    away_player_Y5   INTEGER,
    away_player_Y6   INTEGER,
    away_player_Y7   INTEGER,
    away_player_Y8   INTEGER,
    away_player_Y9   INTEGER,
    away_player_Y10  INTEGER,
    away_player_Y11  INTEGER,
    home_player_1    INTEGER
        references Player (player_api_id),
    home_player_2    INTEGER
        references Player (player_api_id),
    home_player_3    INTEGER
        references Player (player_api_id),
    home_player_4    INTEGER
        references Player (player_api_id),
    home_player_5    INTEGER
        references Player (player_api_id),
    home_player_6    INTEGER
        references Player (player_api_id),
    home_player_7    INTEGER
        references Player (player_api_id),
    home_player_8    INTEGER
        references Player (player_api_id),
    home_player_9    INTEGER
        references Player (player_api_id),
    home_player_10   INTEGER
        references Player (player_api_id),
    home_player_11   INTEGER
        references Player (player_api_id),
    away_player_1    INTEGER
        references Player (player_api_id),
    away_player_2    INTEGER
        references Player (player_api_id),
    away_player_3    INTEGER
        references Player (player_api_id),
    away_player_4    INTEGER
        references Player (player_api_id),
    away_player_5    INTEGER
        references Player (player_api_id),
    away_player_6    INTEGER
        references Player (player_api_id),
    away_player_7    INTEGER
        references Player (player_api_id),
    away_player_8    INTEGER
        references Player (player_api_id),
    away_player_9    INTEGER
        references Player (player_api_id),
    away_player_10   INTEGER
        references Player (player_api_id),
    away_player_11   INTEGER
        references Player (player_api_id),
    goal             TEXT,
    shoton           TEXT,
    shotoff          TEXT,
    foulcommit       TEXT,
    card             TEXT,
    "cross"          TEXT,
    corner           TEXT,
    possession       TEXT,
    B365H            REAL,
    B365D            REAL,
    B365A            REAL,
    BWH              REAL,
    BWD              REAL,
    BWA              REAL,
    IWH              REAL,
    IWD              REAL,
    IWA              REAL,
    LBH              REAL,
    LBD              REAL,
    LBA              REAL,
    PSH              REAL,
    PSD              REAL,
    PSA              REAL,
    WHH              REAL,
    WHD              REAL,
    WHA              REAL,
    SJH              REAL,
    SJD              REAL,
    SJA              REAL,
    VCH              REAL,
    VCD              REAL,
    VCA              REAL,
    GBH              REAL,
    GBD              REAL,
    GBA              REAL,
    BSH              REAL,
    BSD              REAL,
    BSA              REAL
);
CREATE TABLE IF NOT EXISTS "sets"
(
    id      INTEGER not null
        primary key,
    name    TEXT,
    type    TEXT,
    series  TEXT,
    codes   TEXT,
    logo    TEXT,
    tcg_code TEXT,
    name_jp TEXT
);
CREATE TABLE `temp` (
  `t`
);
CREATE VIEW "playdribblingbuildup"
(
    date_ranked,
    rank,
    isbn13,
    subtitle_en,
    subtitle_ja
)
AS
SELECT T1.date_ranked, T1.rank, T1.isbn13, T1.subtitle_en, T1.subtitle_ja
FROM `comics_sales_rank` AS T1
JOIN `volumes` AS T2 ON T2.isbn13 = T1.isbn13
JOIN `chapters` AS T3 ON T3.isbn13 = T2.isbn13
WHERE T3.chapter = 1 AND T3.pages = 4 AND T2.series_id = 'LEI' AND T1.date = '2015-09-10';
CREATE TABLE IF NOT EXISTS "users" (
 `id` INTEGER PRIMARY KEY AUTOINCREMENT ,
 `votes` INTEGER DEFAULT NULL ,
 `rating` REAL DEFAULT NULL ,
 `registration` DATE DEFAULT NULL ,
 `name` TEXT UNIQUE
);
CREATE TABLE POS
(
    PositionID
);
CREATE TABLE IF NOT EXISTS "user"
(
    user_id         TEXT not null
        primary key,
    name            TEXT,
    review_count    INTEGER,
    helpfulness     INTEGER,
    thank you_count INTEGER,
    star_average    REAL,
    fans             INTEGER
);
CREATE TABLE IF NOT EXISTS "Player_Statistics"
(
    id             INTEGER not null
            primary key,
    stat_date      TEXT,
    league_id      INTEGER,
    tour_id        INTEGER,
    round          TEXT,
    match_day       INTEGER,
    season         TEXT,
    playoff        TEXT,
    home Away      TEXT,
    team_id        INTEGER,
    team_name      TEXT,
    opp_id        INTEGER,
    opp_name      TEXT,
    result         TEXT,
    minutes        INTEGER,
    shoots_total   INTEGER,
    shoots_on      INTEGER,
    opp_shoots_total INTEGER,
    opp_shoots_on  INTEGER,
    possession    INTEGER,
    off_wins      TEXT,
    off_losses     TEXT,
    foul_committed INTEGER,
    foul_received  INTEGER,
    player_id      INTEGER,
    player_name    TEXT,
    goals         INTEGER,
    assists        INTEGER,
    rebounds       INTEGER,
    turnovers     INTEGER,
    personal_fouls INTEGER,
    tech_fouls    INTEGER,
   doubles_fouls  INTEGER,
     power_play   TEXT,
     min_weather_temperature TEXT,
     max_weather_temperature TEXT,
     weather      TEXT,
     surface      TEXT,
     light        TEXT
);
CREATE TABLE IF NOT EXISTS "Team_Avg"
(
    id                 INTEGER      not null
        primary key,
    year               INTEGER      not null,
    team_id            INTEGER      not null,
    team_name          TEXT         not null,
    team_nametext      TEXT         not null,
    conf_id           INTEGER      not null,
    conf_name         INTEGER      not null,
    conf_name_text    TEXT         not null,
    div_id            INTEGER      not null,
    div_name          INTEGER      not null,
    div_name_text     TEXT         not null,
    league_id         INTEGER      not null,
    league_name       TEXT         not null,
    league_name_text  TEXT         not null,
    avg_g              TEXT        null,
    avg ga            TEXT        null,
    avg_xpm           TEXT        null,
    avg_xpa           TEXT        null,
    avg_ypr_l         TEXT        null,
    avg_ypr_r         TEXT        null,
    avg_yrd_l         TEXT        null,
    avg_yrd_r         TEXT        null,
    avg_yrt_l         TEXT        null,
    avg_yrt_r         TEXT        null,
    avg_fgm           TEXT        null,
    avg_fgpm         TEXT        null,
    avg_fgpa         TEXT        null,
    avg_ftpm          TEXT        null,
    avg_ftpp         TEXT        null,
    avg_ortg         TEXT        null,
    avg_drtg         TEXT        null,
    avgasts          TEXT        null,
    avg_stl           TEXT        null,
    avg_blk           TEXT        null,
    avg_tov           TEXT        null,
    avg_oreb         TEXT        null,
    avg_dreb         TEXT        null,
    avg_reb          TEXT        null,
    avg_pf            TEXT        null,
    avg_pts100       TEXT        null,
    avg_mpm          TEXT        null,
    avg_tm3pm        TEXT        null,
    avg_tm3pa        TEXT        null,
    avg_tm3pp        TEXT        null,
    avg_tm2pm        TEXT        null,
    avg_tm2pa        TEXT        null,
    avg_tm2pp        TEXT        null,
    avg_fta           TEXT        null
);
CREATE TABLE IF NOT EXISTS "technology_platform" ("technology_id" INTEGER not null, "platform_id" INTEGER not null, primary key ("technology_id", "platform_id"), foreign key ("technology_id") references "technology_provider" ("id"), foreign key ("platform_id") references "technology_platform" ("id"));
CREATE VIEW "play_passing_greater_than_70" AS SELECT ATT_VAR_CLASSES_CLASSES.class FROM ATT_VAR_CLASSES_CLASSES INNER JOIN Attributes ON ATT_VAR_CLASSES_CLASSES.CLASS_ID = Attributes.ATT_VAR_CLASSES_CLASS_ID WHERE Attributes.Min_Score > 70;
CREATE VIEW question_4 as SELECT T2.model_text FROM runs AS T1 INNER JOIN results AS T2 ON T1.run_at = T2.run_at WHERE T1.run_at LIKE '2011-02-22%' AND T2.win_loss_id = 1 AND T2.result = 1;

Similar issues: #150
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@tshu-w