快捷導(dǎo)航

PostgreSQL使用SQL實(shí)現(xiàn)俄羅斯方塊的示例

更新時(shí)間：2025年04月01日 15:54:32 作者：螞蟻ailing

基于PostgreSQL實(shí)現(xiàn)的俄羅斯方塊游戲項(xiàng)目Tetris-SQL,通過純SQL代碼和數(shù)據(jù)庫操作重構(gòu)了經(jīng)典游戲邏輯,展現(xiàn)了SQL語言的圖靈完備性和技術(shù)潛力,本文介紹PostgreSQL使用SQL實(shí)現(xiàn)俄羅斯方塊的示例,感興趣的朋友一起看看吧

1.游戲安裝

1. 先安裝一個PostgreSQL數(shù)據(jù)庫
[postgres@pghost ~]$psql
psql (15.8)
2.安裝 psycopg2
yum install postgresql-devel python3-devel
yum install -y python3-psycopg2
pip3 install psycopg2
pip3 install psycopg2-binary
3.游戲代碼克隆
git clone https://github.com/nuno-faria/tetris-sql
chmod +x ./tetris-sql/input.py

2.游戲使用

1.本地運(yùn)行 Python
python3 input.py \
[-h] [-H HOST] [-P PORT] \
[-d DB] [-u USER] [-p PASSWORD]
2.另一個終端運(yùn)行以下查詢
psql -U postgres -f tetris-sql/game.sql
說明：
game.sql 用于實(shí)現(xiàn)游戲邏輯，主要涉及通用表表達(dá)式（CTE）實(shí)現(xiàn)游戲循環(huán)；
input.py 用于捕獲鍵盤輸入。

3.PL/pgSQL能做什么？

其實(shí)上面這個游戲PL/pgSQL（PostgreSQL的過程化語言）為核心，結(jié)合數(shù)據(jù)庫表、查詢語句和存儲過程實(shí)現(xiàn)游戲邏輯，核心代碼僅500余行。雖然包含少量Python腳本，但僅用于將用戶輸入（如鍵盤操作）轉(zhuǎn)換為SQL命令，不參與核心邏輯處理。

PL/pgSQL的功能其實(shí)非常的強(qiáng)大，讓我們一起來回顧下

3.1 敏感數(shù)據(jù)自動加密

通過觸發(fā)器在數(shù)據(jù)寫入前加密，讀取時(shí)解密，例如為users表的phone字段添加加密邏輯：

CREATE TRIGGER encrypt_phone BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION encrypt_data();

3.2 定時(shí)數(shù)據(jù)清理

結(jié)合pg_cron擴(kuò)展，定期清理日志表：

CREATE OR REPLACE FUNCTION clean_old_logs()
RETURNS VOID AS $$
BEGIN
   DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;
-- 每天凌晨執(zhí)行
SELECT cron.schedule('0 0 * * *', 'SELECT clean_old_logs()');

3.3 大數(shù)據(jù)遷移與轉(zhuǎn)換

使用游標(biāo)逐批處理千萬級數(shù)據(jù)遷移：

CREATE OR REPLACE FUNCTION migrate_legacy_data()
RETURNS VOID AS $$
DECLARE
   batch_size INT := 1000;
   c CURSOR FOR SELECT * FROM legacy_table;
   r RECORD;
BEGIN
   OPEN c;
   LOOP
      FETCH c INTO r;
      EXIT WHEN NOT FOUND;
      INSERT INTO new_table 
      VALUES (r.id, transform_data(r.raw_content));
      IF (count % batch_size = 0) THEN COMMIT; END IF;
   END LOOP;
   CLOSE c;
END;
$$ LANGUAGE plpgsql;

3.4 與Linux交互備份表

通過PL/pgSQL結(jié)合COPY命令實(shí)現(xiàn)PostgreSQL表數(shù)據(jù)備份的存儲過程腳本，包含動態(tài)參數(shù)、異常處理和文件路徑驗(yàn)證功能：

CREATE OR REPLACE PROCEDURE backup_table(
    table_name TEXT, 
    file_path TEXT DEFAULT '/tmp/backup/'
) 
LANGUAGE plpgsql 
AS $$
DECLARE
    full_path TEXT;
    backup_cmd TEXT;
    file_suffix TEXT := to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HH24MISS');
BEGIN
    -- 驗(yàn)證表是否存在[7](@ref)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_name = $1 
          AND table_schema = 'public'
    ) THEN
        RAISE EXCEPTION 'Table % does not exist', table_name;
    END IF;
    -- 生成帶時(shí)間戳的文件路徑
    full_path := file_path || table_name || '_' || file_suffix || '.csv';
    -- 構(gòu)建COPY命令（自動處理列名）[6](@ref)
    backup_cmd := format(
        'COPY (SELECT * FROM %I) TO %L WITH (FORMAT CSV, HEADER, ENCODING ''UTF8'')', 
        table_name, 
        full_path
    );
    -- 執(zhí)行備份命令
    BEGIN
        EXECUTE backup_cmd;
        RAISE NOTICE 'Table % backed up to %', table_name, full_path;
    EXCEPTION 
        WHEN insufficient_privilege THEN
            RAISE EXCEPTION 'Permission denied for path: %', file_path;
        WHEN others THEN
            RAISE EXCEPTION 'Backup failed: %', SQLERRM;
    END;
END;
$$;

PL/pgSQL通過將邏輯貼近數(shù)據(jù)存儲，顯著降低了網(wǎng)絡(luò)延遲和代碼冗余。其與PostgreSQL生態(tài)的深度集成（如PostGIS、pg_cron），使其成為企業(yè)級數(shù)據(jù)處理的核心工具。