-
Notifications
You must be signed in to change notification settings - Fork 12
/
schema.go
320 lines (296 loc) · 7.64 KB
/
schema.go
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
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
// Schema describes a table and all indexes on that table.
// Both indexes from the `CREATE TABLE` and from any relevant `CREATE INDEX`-es
// are processed.
// It knows the SQLite conventions how tables and indexes are used, such as the
// names for internal indexes, when a column is stored in the rowid, &c.
package db
import (
"errors"
"fmt"
"reflect"
"strings"
"github.com/alicebob/sqlittle/sql"
)
type Schema struct {
Table string
WithoutRowid bool
Columns []TableColumn
Indexes []SchemaIndex
PK []IndexColumn // only set for non-rowid tables
PrimaryKey string // only set for rowid tables: name of the index
RowidPK bool // only set for rowid tables: whether we have a 'integer primary key' column, which means there is no separate index for the primary key
}
type TableColumn struct {
Column string
Type string // as given in the CREATE TABLE
Null bool
Default interface{}
Collate string
Rowid bool
}
type SchemaIndex struct {
Index string
Columns []IndexColumn
}
type IndexColumn struct {
Column string
Expression string
Collate string
SortOrder sql.SortOrder
}
func newSchema(table string, master []sqliteMaster) (*Schema, error) {
var createSQL string
n := strings.ToLower(table)
for _, m := range master {
if m.typ == "table" && m.name == n {
createSQL = m.sql
break
}
}
if createSQL == "" {
return nil, fmt.Errorf("no such table: %q", table)
}
t, err := sql.Parse(createSQL)
if err != nil {
return nil, err
}
ct, ok := t.(sql.CreateTableStmt)
if !ok {
return nil, errors.New("unsupported CREATE TABLE statement")
}
st := newCreateTable(ct)
for _, m := range master {
if m.typ == "index" && m.tblName == n && m.sql != "" {
// silently ignore indexes we don't understand
if t, err := sql.Parse(m.sql); err == nil {
if ci, ok := t.(sql.CreateIndexStmt); ok {
st.addCreateIndex(ci)
}
}
}
}
return st, nil
}
// transform a `create table` statement into a Schema, which knows which
// indexes are used
func newCreateTable(ct sql.CreateTableStmt) *Schema {
st := &Schema{
Table: ct.Table,
WithoutRowid: ct.WithoutRowid,
}
autoindex := 1
for _, c := range ct.Columns {
col := TableColumn{
Column: c.Name,
Type: c.Type,
Null: c.Null,
Default: c.Default,
Collate: c.Collate,
Rowid: false,
}
if c.PrimaryKey {
col.Rowid = (!ct.WithoutRowid) && isRowid(false, c.Type, c.PrimaryKeyDir)
col.Null = !ct.WithoutRowid && c.Null // w/o rowid forces not null
name := fmt.Sprintf("sqlite_autoindex_%s_%d", st.Table, autoindex)
if ct.WithoutRowid {
name = ""
}
if ct.WithoutRowid {
// non-rowid primary keys have a special place
st.setPK([]IndexColumn{
{
Column: c.Name,
SortOrder: c.PrimaryKeyDir,
},
})
autoindex++
} else {
if col.Rowid {
st.RowidPK = true
} else if st.addIndex(
true,
name,
[]IndexColumn{
{
Column: c.Name,
SortOrder: c.PrimaryKeyDir,
},
},
) {
autoindex++
}
}
}
if c.Unique {
if st.addIndex(
false,
fmt.Sprintf("sqlite_autoindex_%s_%d", st.Table, autoindex),
[]IndexColumn{
{
Column: c.Name,
SortOrder: sql.Asc,
},
},
) {
autoindex++
}
}
st.Columns = append(st.Columns, col)
}
constraint:
for _, c := range ct.Constraints {
switch c := c.(type) {
case sql.TablePrimaryKey:
if !ct.WithoutRowid && len(c.IndexedColumns) == 1 {
// is this column an alias for the rowid?
col := st.column(c.IndexedColumns[0].Column)
if isRowid(true, col.Type, c.IndexedColumns[0].SortOrder) {
col.Rowid = true
st.RowidPK = true
continue constraint
}
}
if ct.WithoutRowid {
for _, co := range c.IndexedColumns {
st.column(co.Column).Null = false
}
st.setPK(st.toIndexColumns(c.IndexedColumns))
autoindex++
continue
}
name := fmt.Sprintf("sqlite_autoindex_%s_%d", st.Table, autoindex)
if st.addIndex(true, name, st.toIndexColumns(c.IndexedColumns)) {
autoindex++
}
case sql.TableUnique:
name := fmt.Sprintf("sqlite_autoindex_%s_%d", st.Table, autoindex)
if st.addIndex(false, name, st.toIndexColumns(c.IndexedColumns)) {
autoindex++
}
}
}
return st
}
// add `CREATE INDEX` statement to a table
// Does not check for duplicate indexes.
func (st *Schema) addCreateIndex(ci sql.CreateIndexStmt) {
st.Indexes = append(st.Indexes, SchemaIndex{
Index: ci.Index,
Columns: st.toIndexColumns(ci.IndexedColumns),
})
}
// change sql index columns to schema index column. Looks up defaults from the
// column definitions
func (st *Schema) toIndexColumns(ci []sql.IndexedColumn) []IndexColumn {
var cs []IndexColumn
for _, col := range ci {
c := IndexColumn{
Column: col.Column,
Expression: col.Expression,
SortOrder: col.SortOrder,
}
if col.Column != "" {
// not an expression column
base := st.column(col.Column)
if base != nil {
collate := base.Collate
if col.Collate != "" {
collate = col.Collate
}
c.Collate = collate
}
}
cs = append(cs, c)
}
return cs
}
// add an index. This is a noop if an equivalent index already exists. Returns
// whether the indexed got added.
func (st *Schema) addIndex(pk bool, name string, cols []IndexColumn) bool {
if reflect.DeepEqual(st.PK, cols) {
return false
}
for _, ind := range st.Indexes {
if reflect.DeepEqual(ind.Columns, cols) {
if pk {
st.PrimaryKey = ind.Index
}
return false
}
}
st.Indexes = append(st.Indexes, SchemaIndex{
Index: name,
Columns: cols,
})
if pk {
st.PrimaryKey = name
}
return true
}
// sets the PK key (for non-rowid tables). Deletes any duplicate indexes.
func (st *Schema) setPK(cols []IndexColumn) {
st.PK = cols
for i, ind := range st.Indexes {
if reflect.DeepEqual(ind.Columns, cols) {
st.Indexes = append(st.Indexes[:i], st.Indexes[i+1:]...)
if len(st.Indexes) == 0 {
st.Indexes = nil // to make test diffs easier
}
}
}
}
// Returns the index of the named column, or -1.
func (st *Schema) Column(name string) int {
u := strings.ToLower(name)
for i, col := range st.Columns {
if strings.ToLower(col.Column) == u {
return i
}
}
return -1
}
func (st *Schema) column(name string) *TableColumn {
n := st.Column(name)
if n < 0 {
return nil // you're asking for non-exising columns and for trouble
}
return &st.Columns[n]
}
// NamedIndex returns the index with the name (case insensitive)
func (st *Schema) NamedIndex(name string) *SchemaIndex {
u := strings.ToUpper(name)
for i, ind := range st.Indexes {
if strings.ToUpper(ind.Index) == u {
return &st.Indexes[i]
}
}
return nil
}
// Returns the index of the named column, or -1.
func (si *SchemaIndex) Column(name string) int {
u := strings.ToUpper(name)
for i, col := range si.Columns {
if strings.ToUpper(col.Column) == u {
return i
}
}
return -1
}
// A primary key can be an alias for the rowid iff:
// - this is not a `WITHOUT ROWID` table (not tested here)
// - it's a single column of type 'INTEGER'
// - ASC and DESC are fine for table constraints:
// CREATE TABLE foo (a integer, primary key (a DESC))
// but in a column statement it only works with a ASC:
// CREATE TABLE foo (a integer primary key)
// invalid:
// ~~CREATE TABLE foo (a integer primary key DESC)~~
// If the row is an alias for the rowid it won't be stored in the datatable;
// all values will be null.
// See https://sqlite.org/lang_createtable.html#rowid
func isRowid(tableConstraint bool, typ string, dir sql.SortOrder) bool {
if strings.ToUpper(typ) != "INTEGER" {
return false
}
return tableConstraint || dir == sql.Asc
}