n24MHTAy

1#!/usr/bin/env python
2
3import MySQLdb
4import psycopg2
5import subprocess
6
7NUM_PARTITIONS = 100
8HANDLES_PER_PARTITION = 1000
9
10def partition_low(idx):
11    return idx * HANDLES_PER_PARTITION
12
13def partition_high(idx):
14    return partition_low(idx) + (HANDLES_PER_PARTITION - 1)
15
16print "I: Connecting."
17connections = {
18    'psql': psycopg2.connect('dbname=g4mer'),
19    'mysql': MySQLdb.connect(
20        host='89.16.176.155',
21        user='playfire',
22        db='g4mer',
23    ),
24}
25
26cursors = dict((k, v.cursor()) for k, v in connections.items())
27
28cursors['psql'].execute("""BEGIN""")
29
30print "I: Dropping existing gaminglog_entry3"
31cursors['psql'].execute("""
32    DROP TABLE IF EXISTS "gaminglog_entry3" CASCADE
33""")
34
35print "I: Creating gaminglog_entry3"
36cursors['psql'].execute("""
37    CREATE TABLE "gaminglog_entry3" (
38      "id" serial NOT NULL PRIMARY KEY,
39      "time" double precision NOT NULL,
40      "type" integer NOT NULL,
41      "handle_id" integer NOT NULL,
42      "data" bytea NOT NULL,
43      "game_id" integer DEFAULT NULL
44    );
45""")
46
47print "I: Creating %d partitions" % NUM_PARTITIONS
48
49for x in range(NUM_PARTITIONS):
50    cursors['psql'].execute("""
51        CREATE TABLE "gaminglog_entry3_%(current)d" (
52            CHECK (handle_id BETWEEN %(low)d AND %(high)d)
53        ) INHERITS (gaminglog_entry3);
54    """ % {
55        'low': partition_low(x),
56        'high': partition_high(x),
57        'current': x,
58        'num_partitions': NUM_PARTITIONS,
59    })
60
61print "I: Adding INSERT trigger function"
62def insert_trigger_fn():
63    sql = """
64    CREATE OR REPLACE FUNCTION gaminglog_entry3_insert_trigger()
65    RETURNS TRIGGER AS $$
66    DECLARE
67        partition int;
68    BEGIN
69        partition := NEW.handle_id / %d;
70        CASE partition
71    """ % HANDLES_PER_PARTITION
72
73    for x in range(NUM_PARTITIONS):
74        sql += """
75            WHEN %(current)d THEN
76                INSERT INTO gaminglog_entry3_%(current)d VALUES (NEW.*);
77        """ % {
78            'current': x,
79        }
80
81    sql += """
82        ELSE
83            RAISE EXCEPTION 'No partition #% for handle_id %', partition, NEW.handle_id;
84        END CASE;
85        RETURN NULL;
86    END;
87    $$
88    LANGUAGE plpgsql;
89    """
90    return sql
91cursors['psql'].execute(insert_trigger_fn())
92
93cursors['psql'].execute("""
94    CREATE TRIGGER insert_gaminglog_entry3_trigger
95        BEFORE INSERT ON gaminglog_entry3
96            FOR EACH ROW EXECUTE PROCEDURE gaminglog_entry3_insert_trigger();
97""")
98
99print "I: Performing main SELECT"
100cursors['mysql'].execute("""
101    SELECT
102        id, time, type, handle_id, hex(data), game_id
103    FROM
104        gaminglog_entry3
105    LIMIT 10000
106""")
107
108print "I: Inserting rows"
109for id_, time, type, handle_id, data, game_id in cursors['mysql'].fetchall():
110    cursors['psql'].execute("""
111        INSERT INTO
112            gaminglog_entry3
113            (id, time, type, handle_id, data, game_id)
114        VALUES
115            (%s, %s, %s, %s, decode(%s, 'hex'), %s)
116    """, (id_, time, type, handle_id, data, game_id))
117
118print "I: Creating indices"
119for x in range(NUM_PARTITIONS):
120    cursors['psql'].execute("""
121        CREATE INDEX
122            gaminglog_entry3_%(current)d_handle_time
123        ON
124            gaminglog_entry3_%(current)d
125            (handle_id, time);
126    """ % {'current': x})
127
128
129print "I: COMMIT"
130cursors['psql'].execute("""COMMIT""")