Datenbanktabellen

Tabellennamen	Hinzugefügt
user_stats	06.10.2023
user_goals	06.10.2023
pokemon_list	24.09.2023
pokemon_types	24.09.2023
phonetic	06.10.2023
geo_main	08.10.2023

user_stats

colum_name	data_type	character_maximum_length	column_default
id	smallint		nextval('user_stats_id_seq'::regclass)
user_id	character varying	255
goal_id	integer
progress	integer

CREATE TABLE IF NOT EXISTS user_stats (
    id smallserial,
    user_id character varying(255),
    goal_id integer,
    progress integer
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'user_stats';

user_goals

colum_name	data_type	character_maximum_length	column_default
id	smallint		nextval('user_goals_id_seq'::regclass)
game	character varying	255
name	character varying	255
max_value	integer

CREATE TABLE IF NOT EXISTS user_goals (
    id smallserial,
    game character varying(255),
    name character varying(255),
    max_value integer
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'user_goals';

pokemon_list

colum_name	data_type	character_maximum_length	column_default
id	smallint
name_de	character varying	20
name_en	character varying	20
primarytyp	smallint
secondarytyp	smallint
gen	smallint
image	character varying	255

CREATE TABLE IF NOT EXISTS pokemon_list (
    id smallint,
    name_de character varying(20),
    name_en character varying(20),
    primarytyp smallint,
    secondarytyp smallint,
    gen smallint,
    image character varying(255)
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'pokemon_list';

pokemon_types

colum_name	data_type	character_maximum_length	column_default
id	smallint
name_de	character varying	20
name_en	character varying	20
strength_1	smallint
strength_2	smallint
strength_3	smallint
strength_4	smallint
strength_5	smallint
weakness_1	smallint
weakness_2	smallint
weakness_3	smallint
weakness_4	smallint
weakness_5	smallint
weakness_6	smallint
weakness_7	smallint
immune_1	smallint

CREATE TABLE IF NOT EXISTS pokemon_types (
    id smallint,
    name_de character varying(20),
    name_en character varying(20),
    strength_1 smallint,
    strength_2 smallint,
    strength_3 smallint,
    strength_4 smallint,
    strength_5 smallint,
    weakness_1 smallint,
    weakness_2 smallint,
    weakness_3 smallint,
    weakness_4 smallint,
    weakness_5 smallint,
    weakness_6 smallint,
    weakness_7 smallint,
    immune_1 smallint
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'pokemon_types';

phonetic

colum_name	data_type	character_maximum_length	column_default
id	character varying	3
de_din5009	character varying	25
de_post1950	character varying	25
au_a1081	character varying	25
ch	character varying	25
icao	character varying	25

CREATE TABLE IF NOT EXISTS phonetic (
    id character varying(3),
    de_din5009 character varying(25),
    de_post1950 character varying(25),
    au_a1081 character varying(25),
    ch character varying(25),
    icao character varying(25)
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'phonetic';

geo_main

colum_name	data_type	character_maximum_length	column_default
id	smallint		nextval('geo_main_id_seq'::regclass)
country	character varying	50
continent	character varying	50
domain	character varying	5
language	character varying	50
alphabet	character varying	20
phonenumber	character varying	5
license_plate	smallint

CREATE TABLE IF NOT EXISTS geo_main (
    id smallint,
    country character varying(50),
    continent character varying(50),
    domain character varying(5),
    language character varying(50),
    alphabet character varying(20),
    phonenumber character varying(50),
    license_plate smallint
);

SELECT column_name, data_type, character_maximum_length, column_default
FROM information_schema.columns
WHERE table_name = 'geo_main';

später einpflegen

CREATE TABLE IF NOT EXISTS game_list (
  id smallserial primary key not null,
  category character varying(50) not null,
  game character varying(50) not null,
  gamemode character varying(50) not null
);


CREATE TABLE IF NOT EXISTS game_goals (
    id smallserial primary key not null,
    game_id integer references game_list(id) not null,
    name character varying(255) not null,
    max_value integer not null
    );




CREATE TABLE IF NOT EXISTS game_stats (
    user_id character varying(255) not null,
    goal_id integer references game_goals(id) not null,
    progress integer not null
);

Inhalt einer Tabelle löschen

DELETE FROM name_der_tabelle;

Tabelle final löschen

DROP TABLE name_der_tabelle;