-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathmysql2sqlite_queries.ini.tmpl
210 lines (156 loc) · 10.9 KB
/
mysql2sqlite_queries.ini.tmpl
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
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
# https://github.com/WhyAskWhy/mysql2sqlite
###############################################
# Purpose:
###############################################
#
# List tables and the queries and options necessary to pull specific values
# for mirroring in local SQLite database files. The goal is to modularize
# the query process so only as much as is absolutely necessary is hardcoded
# in the script. While I am writing this to mirror a specific database
# I would like to be able to use this code for multiple projects and share
# with others. Contributions to make this process more flexible/generic
# are welcome.
#######################################################################
# References:
#######################################################################
#
# https://docs.python.org/3/library/configparser.html
# https://pymotw.com/3/configparser/
# https://www.sqlite.org/datatype3.html
# https://www.sqlite.org/lang_createindex.html
#
# There are five native types in SQLite: integer, real, text, blob, and null
#
# Postfix Access Tables
#
# http://www.postfix.org/access.5.html
# http://www.postfix.org/postconf.5.html#check_client_access
# http://www.postfix.org/postconf.5.html#check_recipient_access
# http://www.postfix.org/postconf.5.html#check_sender_access
#######################################################################
# Format of this file (ignoring comments):
#######################################################################
#
# [table_name]
#
# new = Query used to create the local SQLite database table
# read = Query used to read all rows from the MySQL database table
# write = Query used to write a row to the local SQLite database table
# index = Query used to create a new index for the local SQLite database table
#######################################################################
# Assumptions:
#######################################################################
#
# * The table names in the SQLite database match those in the source database.
#
# * The column types match as closely as possible the source table.
# ** The notable EXCEPTION is the lack of an auto-increment attribute for
# target "id" columns. This is in an effort to have consistent primary key
# values between the original source MySQL table and the SQLite table. This
# is because the source table might have rows that have been removed
# instead of simply disabled by a flag column. If we allow auto-incrementing
# id values for tables in the SQLite database there is a solid chance that
# the source/destination database tables will have id values out of sync.
# While it may not affect normal use of the SQLite database, it may make
# troublehshooting issues more difficult.
#
# * Postfix automatically folds all search strings to lowercase before
# performing a search. While current versions of MySQL default to
# case-insensitive collation, SQLite uses BINARY collation by default.
# This results in case-SENSITIVE string comparision, so we must explicitly
# override the default collation with NOCASE in order to properly handle
# queries from Postfix.
#
# * The script expects that there are substition placeholders used in
# the "write" queries.
#
##############################################################################
# Virtual domains that our servers are responsible for (primarily mail).
[virtual_domains]
##############################################################################
new = CREATE TABLE virtual_domains ( id int, name text COLLATE NOCASE, created date, ticket_number int, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM virtual_domains
write = INSERT INTO virtual_domains VALUES (?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS virtual_domains_idx ON virtual_domains(name COLLATE NOCASE)
##############################################################################
# Virtual Mailbox users. For now, we are NOT syncing the passwords locally.
# This means that for any apps which need that information to validate logins
# the apps will need to continue querying against the MySQL database.
# This also means that the "read" query below will need to be manually
# updated to reflect the current db schema used for the source MySQL db.
[virtual_users]
##############################################################################
new = CREATE TABLE virtual_users ( id int, domain_id int, password text, email text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT id, domain_id, NULL AS password, email, enabled, ticket_number, created, last_modified, comments FROM virtual_users
write = INSERT INTO virtual_users VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS virtual_users_idx ON virtual_users(email COLLATE NOCASE)
##############################################################################
[virtual_aliases]
##############################################################################
new = CREATE TABLE virtual_aliases ( id int, domain_id int, source text COLLATE NOCASE, destination text, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM virtual_aliases
write = INSERT INTO virtual_aliases VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS virtual_aliases_idx ON virtual_aliases(source COLLATE NOCASE, enabled)
##############################################################################
[local_aliases]
##############################################################################
new = CREATE TABLE local_aliases ( id int, source text COLLATE NOCASE, destination text, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM local_aliases
write = INSERT INTO local_aliases VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS local_aliases_idx ON local_aliases(source COLLATE NOCASE, enabled)
##############################################################################
[transport_maps]
##############################################################################
new = CREATE TABLE transport_maps ( id int, recipient text COLLATE NOCASE, transport text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM transport_maps
write = INSERT INTO transport_maps VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS transport_maps_idx ON transport_maps(recipient COLLATE NOCASE, enabled)
##############################################################################
[access_check_clients]
##############################################################################
new = CREATE TABLE access_check_clients ( id int, client text COLLATE NOCASE, action text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM access_check_clients
write = INSERT INTO access_check_clients VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS access_check_clients_idx ON access_check_clients(client COLLATE NOCASE, enabled)
##############################################################################
[access_check_recipients]
##############################################################################
new = CREATE TABLE access_check_recipients ( id int, recipient text COLLATE NOCASE, action text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM access_check_recipients
write = INSERT INTO access_check_recipients VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS access_check_recipients_idx ON access_check_recipients(recipient COLLATE NOCASE, enabled)
##############################################################################
[access_check_senders]
##############################################################################
new = CREATE TABLE access_check_senders ( id int, sender text COLLATE NOCASE, action text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM access_check_senders
write = INSERT INTO access_check_senders VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS access_check_senders_idx ON access_check_senders(sender COLLATE NOCASE, enabled)
##############################################################################
[mail_relay_whitelist]
##############################################################################
new = CREATE TABLE mail_relay_whitelist ( id int, client text COLLATE NOCASE, action text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM mail_relay_whitelist
write = INSERT INTO mail_relay_whitelist VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS mail_relay_whitelist_idx ON mail_relay_whitelist(client COLLATE NOCASE, enabled)
##############################################################################
[sender_bcc_maps]
##############################################################################
new = CREATE TABLE sender_bcc_maps ( id int, sender text COLLATE NOCASE, bcc_recipient text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM sender_bcc_maps
write = INSERT INTO sender_bcc_maps VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS sender_bcc_maps_idx ON sender_bcc_maps(sender COLLATE NOCASE, enabled)
##############################################################################
[recipient_bcc_maps]
##############################################################################
new = CREATE TABLE recipient_bcc_maps ( id int, original_recipient text COLLATE NOCASE, bcc_recipient text COLLATE NOCASE, enabled int, tickt_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM recipient_bcc_maps
write = INSERT INTO recipient_bcc_maps VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS recipient_bcc_maps_idx ON recipient_bcc_maps(original_recipient COLLATE NOCASE, enabled)
##############################################################################
[sender_dependent_default_transport_maps]
##############################################################################
new = CREATE TABLE sender_dependent_default_transport_maps ( id int, sender text COLLATE NOCASE, transport text COLLATE NOCASE, enabled int, ticket_number int, created date, last_modified date, comments text COLLATE NOCASE )
read = SELECT * FROM sender_dependent_default_transport_maps
write = INSERT INTO sender_dependent_default_transport_maps VALUES (?, ?, ?, ?, ?, ?, ?, ?)
index = CREATE INDEX IF NOT EXISTS sender_dependent_default_transport_maps_idx ON sender_dependent_default_transport_maps(sender COLLATE NOCASE, enabled)