我正在写一个二十一点模拟器,将游戏历史存储在数据库中,每个运行生成一组新的表.表格更像是模板,因为每个游戏都有自己的3个可变表格(玩家,手和比赛).这里是布局,其中suff是用于当前运行的用户指定的后缀:
- cards - id INTEGER PRIMARY KEY - cardValue INTEGER NOT NULL - suit INTEGER NOT NULL - players_suff - whichPlayer INTEGER PRIMARY KEY - aiType TEXT NOT NULL - hands_suff - id BIGSERIAL PRIMARY KEY - whichPlayer INTEGER REFERENCES players_suff(whichPlayer) * - whichHand BIGINT NOT NULL - thisCard INTEGER REFERENCES cards(id) - matches_suff - id BIGSERIAL PRIMARY KEY - whichGame INTEGER NOT NULL - dealersHand BIGINT NOT NULL - whichPlayer INTEGER REFERENCES players_suff(whichPlayer) - thisPlayersHand BIGINT NOT NULL ** - playerResult INTEGER NOT NULL --AKA who won
仅创建一张卡片表,因为它的值不变.
所以运行模拟器之后你可能有两次:
hands_firstrun players_firstrun matches_firstrun hands_secondrun players_secondrun matches_secondrun
如果您对这两个运行使用相同的AI参数(即,player_firstrun和players_secondrun完全相同),我希望能够组合这些表.问题是,我插入手的方式使这真的很混乱:哪个不能是一个BIGSERIAL,因为hands_suff行与“实际手”的关系是很多的:1. matches_suff以相同的方式处理,因为二十一点“游戏”实际上由一组游戏组成:每组玩家与经销商的一组对.所以对于3名玩家,你实际上每轮有3排.
目前我选择了表中最大的那个,并添加1,然后插入一只手的所有行.我担心这个“查询插入”会很慢,如果我合并了可能是任意大的2个表.
当我合并表时,我觉得我应该能够(完全在sql中)查询最大的值,其中Hand和whichGame一次然后使用它们组合表,为每个唯一的whichHand和表中的哪个Game合并,增加它们.
(我看到this question,但它不处理在2个不同的地方使用生成的ID).我正在使用Postgres,如果答案是具体的,它可以.
*可悲的是,postgres不允许使用参数化的表名,因此必须通过手动字符串替换完成.不是世界的尽头,因为程序不是面向Web的,除了我以外,没有人可能会打扰它,但是sql注入漏洞并没有让我开心.
** matches_suff(whichPlayersHand)原来是引用hands_suff(whichHand)而是foreign keys must reference unique values.HandHand并不是唯一的,因为手是由多行组成的,每行都是“保存”一个卡.要查询一个手,您可以选择所有这些行中具有相同值的其中Hand.我不能想到一个更优雅的方式来做到这一点,而不诉诸数组.
编辑:
这就是我现在所说的:
thomas=# \dt List of relations Schema | Name | Type | Owner --------+----------------+-------+-------- public | cards | table | thomas public | hands_first | table | thomas public | hands_second | table | thomas public | matches_first | table | thomas public | matches_second | table | thomas public | players_first | table | thomas public | players_second | table | thomas (7 rows) thomas=# SELECT * FROM hands_first thomas-# \g id | whichplayer | whichhand | thiscard ----+-------------+-----------+---------- 1 | 0 | 0 | 6 2 | 0 | 0 | 63 3 | 0 | 0 | 41 4 | 1 | 1 | 76 5 | 1 | 1 | 23 6 | 0 | 2 | 51 7 | 0 | 2 | 29 8 | 0 | 2 | 2 9 | 0 | 2 | 92 10 | 0 | 2 | 6 11 | 1 | 3 | 101 12 | 1 | 3 | 8 (12 rows) thomas=# SELECT * FROM hands_second thomas-# \g id | whichplayer | whichhand | thiscard ----+-------------+-----------+---------- 1 | 0 | 0 | 78 2 | 0 | 0 | 38 3 | 1 | 1 | 24 4 | 1 | 1 | 18 5 | 1 | 1 | 95 6 | 1 | 1 | 40 7 | 0 | 2 | 13 8 | 0 | 2 | 84 9 | 0 | 2 | 41 10 | 1 | 3 | 29 11 | 1 | 3 | 34 12 | 1 | 3 | 56 13 | 1 | 3 | 52 thomas=# SELECT * FROM matches_first thomas-# \g id | whichgame | dealershand | whichplayer | thisplayershand | playerresult ----+-----------+-------------+-------------+-----------------+-------------- 1 | 0 | 0 | 1 | 1 | 1 2 | 1 | 2 | 1 | 3 | 2 (2 rows) thomas=# SELECT * FROM matches_second thomas-# \g id | whichgame | dealershand | whichplayer | thisplayershand | playerresult ----+-----------+-------------+-------------+-----------------+-------------- 1 | 0 | 0 | 1 | 1 | 0 2 | 1 | 2 | 1 | 3 | 2 (2 rows)
我想把它们结合起来:
hands_combined table: id | whichplayer | whichhand | thiscard ----+-------------+-----------+---------- 1 | 0 | 0 | 6 --Seven of Spades 2 | 0 | 0 | 63 --Queen of Spades 3 | 0 | 0 | 41 --Three of Clubs 4 | 1 | 1 | 76 5 | 1 | 1 | 23 6 | 0 | 2 | 51 7 | 0 | 2 | 29 8 | 0 | 2 | 2 9 | 0 | 2 | 92 10 | 0 | 2 | 6 11 | 1 | 3 | 101 12 | 1 | 3 | 8 13 | 0 | 4 | 78 14 | 0 | 4 | 38 15 | 1 | 5 | 24 16 | 1 | 5 | 18 17 | 1 | 5 | 95 18 | 1 | 5 | 40 19 | 0 | 6 | 13 20 | 0 | 6 | 84 21 | 0 | 6 | 41 22 | 1 | 7 | 29 23 | 1 | 7 | 34 24 | 1 | 7 | 56 25 | 1 | 7 | 52 matches_combined table: id | whichgame | dealershand | whichplayer | thisplayershand | playerresult ----+-----------+-------------+-------------+-----------------+-------------- 1 | 0 | 0 | 1 | 1 | 1 2 | 1 | 2 | 1 | 3 | 2 3 | 2 | 4 | 1 | 5 | 0 4 | 3 | 6 | 1 | 7 | 2
“thiscard”的每个值代表范围[1..104] – 52张扑克牌的扑克牌,额外的位代表如果它正面朝上或面朝下.由于空间原因,我没有发布实际的表.
所以在第一场比赛中,球员0(也就是经销商)手中有(七个黑桃,空格之王,三个俱乐部).
解决方法
此外,我不知道什么参数化的表名称(我从来没有看到任何类似于任何RDBMS),但Postgresql确实允许一些完美的:记录集返回函数.
CREATE TYPE card_value AS ENUM ('1','2','3','4','5','6','7','8','9','10','J','Q','K'); CREATE TYPE card_suit AS ENUM ('Clubs','Diamonds','Hearts','Spades'); CREATE TYPE card AS (value card_value,suit card_suit,face_up bool); CREATE TABLE runs ( run_id bigserial NOT NULL PRIMARY KEY,run_date timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE players ( run_id bigint NOT NULL REFERENCES runs,player_no int NOT NULL,-- 0 can be assumed as always the dealer ai_type text NOT NULL,PRIMARY KEY (run_id,player_no) ); CREATE TABLE matches ( run_id bigint NOT NULL REFERENCES runs,match_no int NOT NULL,match_no) ); CREATE TABLE hands ( hand_id bigserial NOT NULL PRIMARY KEY,run_id bigint NOT NULL REFERENCES runs,hand_no int NOT NULL,UNIQUE (run_id,match_no,hand_no),FOREIGN KEY (run_id,match_no) REFERENCES matches,player_no) REFERENCES players ); CREATE TABLE deals ( deal_id bigserial NOT NULL PRIMARY KEY,hand_id bigint NOT NULL REFERENCES hands,card card NOT NULL ); CREATE OR REPLACE FUNCTION players(int) RETURNS SETOF players AS $$ SELECT * FROM players WHERE run_id = $1 ORDER BY player_no; $$LANGUAGE sql; CREATE OR REPLACE FUNCTION matches(int) RETURNS SETOF matches AS $$ SELECT * FROM matches WHERE run_id = $1 ORDER BY match_no; $$LANGUAGE sql; CREATE OR REPLACE FUNCTION hands(int) RETURNS SETOF hands AS $$ SELECT * FROM hands WHERE run_id = $1 ORDER BY match_no,hand_no; $$LANGUAGE sql; CREATE OR REPLACE FUNCTION hands(int,int) RETURNS SETOF hands AS $$ SELECT * FROM hands WHERE run_id = $1 AND match_no = $2 ORDER BY hand_no; $$LANGUAGE sql; CREATE OR REPLACE FUNCTION winner_player (int,int) RETURNS int AS $$ SELECT player_no FROM hands WHERE run_id = $1 AND match_no = $2 ORDER BY hand_no DESC LIMIT 1 $$LANGUAGE sql; CREATE OR REPLACE FUNCTION next_player_no (int) RETURNS int AS $$ SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN COALESCE((SELECT MAX(player_no) FROM players WHERE run_id = $1),0) + 1 END $$LANGUAGE sql; CREATE OR REPLACE FUNCTION next_match_no (int) RETURNS int AS $$ SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN COALESCE((SELECT MAX(match_no) FROM matches WHERE run_id = $1),0) + 1 END $$LANGUAGE sql; CREATE OR REPLACE FUNCTION next_hand_no (int) RETURNS int AS $$ SELECT CASE WHEN EXISTS (SELECT 1 FROM runs WHERE run_id = $1) THEN COALESCE((SELECT MAX(hand_no) + 1 FROM hands WHERE run_id = $1),0) END $$LANGUAGE sql; CREATE OR REPLACE FUNCTION card_to_int (card) RETURNS int AS $$ SELECT ((SELECT enumsortorder::int-1 FROM pg_enum WHERE enumtypid = 'card_suit'::regtype AND enumlabel = ($1).suit::name) * 13 + (SELECT enumsortorder::int-1 FROM pg_enum WHERE enumtypid = 'card_value'::regtype AND enumlabel = ($1).value::name) + 1) * CASE WHEN ($1).face_up THEN 2 ELSE 1 END $$LANGUAGE sql; -- SELECT card_to_int(('3','Spades',false)) CREATE OR REPLACE FUNCTION int_to_card (int) RETURNS card AS $$ SELECT ((SELECT enumlabel::card_value FROM pg_enum WHERE enumtypid = 'card_value'::regtype AND enumsortorder = ((($1-1)%13)+1)::real),(SELECT enumlabel::card_suit FROM pg_enum WHERE enumtypid = 'card_suit'::regtype AND enumsortorder = (((($1-1)/13)::int%4)+1)::real),$1 > (13*4))::card $$LANGUAGE sql; -- SELECT i,int_to_card(i) FROM generate_series(1,13*4*2) i CREATE OR REPLACE FUNCTION deal_cards(int,int,int[]) RETURNS TABLE (player_no int,hand_no int,card card) AS $$ WITH hand AS ( INSERT INTO hands (run_id,player_no,hand_no) VALUES ($1,$2,$3,next_hand_no($1)) RETURNING hand_id,mydeals AS ( INSERT INTO deals (hand_id,card) SELECT hand_id,int_to_card(card_id)::card AS card FROM hand,UNNEST($4) card_id RETURNING hand_id,deal_id,card ) SELECT h.player_no,h.hand_no,d.card FROM hand h,mydeals d $$LANGUAGE sql; CREATE OR REPLACE FUNCTION deals(int) RETURNS TABLE (deal_id bigint,player_no int,card int) AS $$ SELECT d.deal_id,h.player_no,card_to_int(d.card) FROM hands h JOIN deals d ON (d.hand_id = h.hand_id) WHERE h.run_id = $1 ORDER BY d.deal_id; $$LANGUAGE sql; INSERT INTO runs DEFAULT VALUES; -- Add first run INSERT INTO players VALUES (1,'Dealer'); -- dealer always zero INSERT INTO players VALUES (1,next_player_no(1),'Player 1'); INSERT INTO matches VALUES (1,next_match_no(1)); -- First match SELECT * FROM deal_cards(1,1,ARRAY[6,63,41]); SELECT * FROM deal_cards(1,ARRAY[76,23]); SELECT * FROM deal_cards(1,ARRAY[51,29,2,92,6]); SELECT * FROM deal_cards(1,ARRAY[101,8]); INSERT INTO matches VALUES (1,next_match_no(1)); -- Second match SELECT * FROM deal_cards(1,ARRAY[78,38]); SELECT * FROM deal_cards(1,ARRAY[24,18,95,40]); SELECT * FROM deal_cards(1,ARRAY[13,84,ARRAY[29,34,56,52]); SELECT * FROM deals(1); -- This is the output you need (hands_combined table) -- This view can be used to retrieve the list of all winning hands CREATE OR REPLACE VIEW winning_hands AS SELECT DISTINCT ON (run_id,match_no) * FROM hands ORDER BY run_id,hand_no DESC; SELECT * FROM winning_hands;