File: tables.sql

package info (click to toggle)
londiste-sql 3.8-6
  • links: PTS, VCS
  • area: main
  • in suites: sid, trixie
  • size: 560 kB
  • sloc: sql: 2,742; python: 309; makefile: 18; sh: 1
file content (174 lines) | stat: -rw-r--r-- 6,127 bytes parent folder | download | duplicates (4)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
-- ----------------------------------------------------------------------
-- Section: Londiste internals
--
--      Londiste storage: tables/seqs/fkeys/triggers/events.
--
-- Londiste event types:
--      I/U/D                   - partial SQL event from pgq.sqltriga()
--      I:/U:/D: <pk>           - urlencoded event from pgq.logutriga()
--      EXECUTE                 - SQL script execution
--      TRUNCATE                - table truncation
--      londiste.add-table      - global table addition
--      londiste.remove-table   - global table removal
--      londiste.update-seq     - sequence update
--      londiste.remove-seq     - global sequence removal
--
-- pgq.sqltriga() event:
--      ev_type     - I/U/D which means insert/update/delete
--      ev_data     - partial SQL
--      ev_extra1   - table name
--
--      Insert: ev_type = "I", ev_data = "(col1, col2) values (2, 'foo')", ev_extra1 = "public.tblname"
--
--      Update: ev_type = "U", ev_data = "col2 = null where col1 = 2", ev_extra1 = "public.tblname"
--
--      Delete: ev_type = "D", ev_data = "col1 = 2", ev_extra1 = "public.tblname"
--
-- pgq.logutriga() event:
--      ev_type     - I:/U:/D: plus comma separated list of pkey columns
--      ev_data     - urlencoded row columns
--      ev_extra1   - table name
--
--      Insert: ev_type = "I:col1", ev_data = ""
--
-- Truncate trigger event:
--      ev_type     - TRUNCATE
--      ev_extra1   - table name
--
-- Execute SQL event:
--      ev_type     - EXECUTE
--      ev_data     - SQL script
--      ev_extra1   - Script ID
--
-- Global table addition:
--      ev_type     - londiste.add-table
--      ev_data     - table name
--
-- Global table removal:
--      ev_type     - londiste.remove-table
--      ev_data     - table name
--
-- Global sequence update:
--      ev_type     - londiste.update-seq
--      ev_data     - seq value
--      ev_extra1   - seq name
--5)
-- Global sequence removal:
--      ev_type     - londiste.remove-seq
--      ev_data     - seq name
-- ----------------------------------------------------------------------
create schema londiste;

set default_with_oids = 'off';


-- ----------------------------------------------------------------------
-- Table: londiste.table_info
--
--      Info about registered tables.
--
-- Columns:
--      nr              - number for visual ordering
--      queue_name      - Cascaded queue name
--      table_name      - fully-qualified table name
--      local           - Is used locally
--      merge_state     - State for tables
--      custom_snapshot - remote snapshot for COPY command
--      dropped_ddl     - temp place to store ddl
--      table_attrs     - urlencoded dict of extra attributes
--
-- Tables merge states:
--      NULL            - copy has not yet happened
--      in-copy         - ongoing bulk copy
--      catching-up     - copy process applies events that happened during copy
--      wanna-sync:%    - copy process caught up, wants to hand table over to replay
--      do-sync:%       - replay process is ready to accept the table
--      ok              - in sync, replay applies events
-- ----------------------------------------------------------------------
create table londiste.table_info (
    nr                  serial not null,
    queue_name          text not null,
    table_name          text not null,
    local               boolean not null default false,
    merge_state         text,
    custom_snapshot     text,
    dropped_ddl         text,
    table_attrs         text,
    dest_table          text,

    primary key (queue_name, table_name),
    foreign key (queue_name)
      references pgq_node.node_info (queue_name)
      on delete cascade,
    check (dropped_ddl is null or merge_state in ('in-copy', 'catching-up'))
);


-- ----------------------------------------------------------------------
-- Table: londiste.seq_info
--
--      Sequences available on this queue.
--
-- Columns:
--      nr          - number for visual ordering
--      queue_name  - cascaded queue name
--      seq_name    - fully-qualified seq name
--      local       - there is actual seq on local node
--      last_value  - last published value from root
-- ----------------------------------------------------------------------
create table londiste.seq_info (
    nr                  serial not null,
    queue_name          text not null,
    seq_name            text not null,
    local               boolean not null default false,
    last_value          int8 not null,

    primary key (queue_name, seq_name),
    foreign key (queue_name)
      references pgq_node.node_info (queue_name)
      on delete cascade
);


-- ----------------------------------------------------------------------
-- Table: londiste.applied_execute
--
--      Info about EXECUTE commands that are ran.
--
-- Columns:
--      queue_name      - cascaded queue name
--      execute_file    - filename / unique id
--      execute_time    - the time execute happened
--      execute_sql     - contains SQL for EXECUTE event (informative)
-- ----------------------------------------------------------------------
create table londiste.applied_execute (
    queue_name          text not null,
    execute_file        text not null,
    execute_time        timestamptz not null default now(),
    execute_sql         text not null,
    execute_attrs       text,
    primary key (execute_file)
);


-- ----------------------------------------------------------------------
-- Table: londiste.pending_fkeys
--
--      Details on dropped fkeys.  Global, not specific to any set.
--
-- Columns:
--      from_table      - fully-qualified table name
--      to_table        - fully-qualified table name
--      fkey_name       - name of constraint
--      fkey_def        - full fkey definition
-- ----------------------------------------------------------------------
create table londiste.pending_fkeys (
    from_table          text not null,
    to_table            text not null,
    fkey_name           text not null,
    fkey_def            text not null,
    
    primary key (from_table, fkey_name)
);