dialect.go

v1.4.3

package store

import "fmt"

// dialect encapsulates SQL differences between database backends.
type dialect struct {
	driverName string

	migrationSQL string

	// Query templates.
	resolveModuleSQL string
	addModuleSQL     string
	deleteModuleSQL  string
	updateModuleSQL  string
	renameModuleSQL  string
	addPatternSQL    string
	updatePatternSQL string
	deletePatternSQL string

	// Import queries (INSERT OR IGNORE equivalent).
	importModuleSQL  string
	importPatternSQL string

	// Credential queries.
	addCredentialSQL    string
	updateCredentialSQL string
	deleteCredentialSQL string
	getCredentialSQL    string
	listCredentialsSQL  string

	// Sumdb queries.
	insertSumdbRecordSQL string
	insertSumdbHashSQL   string
	getRecordsSQL        string // SELECT ... WHERE id >= ? AND id < ?
	lookupRecordSQL      string // SELECT id FROM sumdb_records WHERE key = ?
	recordCountSQL       string
}

var sqliteDialect = dialect{
	driverName: "sqlite",

	migrationSQL: `
		CREATE TABLE IF NOT EXISTS modules (
			name       TEXT PRIMARY KEY,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);

		CREATE TABLE IF NOT EXISTS module_patterns (
			id         INTEGER PRIMARY KEY AUTOINCREMENT,
			pattern    TEXT NOT NULL UNIQUE,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			priority   INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);

		CREATE TABLE IF NOT EXISTS credentials (
			name       TEXT PRIMARY KEY,
			type       TEXT NOT NULL,
			data       TEXT NOT NULL,
			created_at TEXT NOT NULL DEFAULT (datetime('now'))
		);

		CREATE TABLE IF NOT EXISTS sumdb_records (
			id   INTEGER PRIMARY KEY,
			key  TEXT NOT NULL,
			data BLOB NOT NULL
		);

		CREATE TABLE IF NOT EXISTS sumdb_hashes (
			id   INTEGER PRIMARY KEY,
			hash BLOB NOT NULL
		);

		CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
	`,

	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = ?",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
	updateModuleSQL:  "UPDATE modules SET vcs = ?, repo = ?, web = ?, private = ?, credential_name = ? WHERE name = ?",
	renameModuleSQL:  "UPDATE modules SET name = ? WHERE name = ?",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = ?",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
	updatePatternSQL: "UPDATE module_patterns SET pattern = ?, vcs = ?, repo = ?, web = ?, private = ?, priority = ?, credential_name = ? WHERE id = ?",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",

	importModuleSQL:  "INSERT OR IGNORE INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
	importPatternSQL: "INSERT OR IGNORE INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",

	addCredentialSQL:    "INSERT INTO credentials (name, type, data) VALUES (?, ?, ?)",
	updateCredentialSQL: "UPDATE credentials SET type = ?, data = ? WHERE name = ?",
	deleteCredentialSQL: "DELETE FROM credentials WHERE name = ?",
	getCredentialSQL:    "SELECT name, type, data, created_at FROM credentials WHERE name = ?",
	listCredentialsSQL:  "SELECT name, type, data, created_at FROM credentials ORDER BY name",

	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES (?, ?, ?)",
	insertSumdbHashSQL:   "INSERT INTO sumdb_hashes (id, hash) VALUES (?, ?)",
	getRecordsSQL:        "SELECT data FROM sumdb_records WHERE id >= ? AND id < ? ORDER BY id",
	lookupRecordSQL:      "SELECT id FROM sumdb_records WHERE key = ?",
	recordCountSQL:       "SELECT COUNT(*) FROM sumdb_records",
}

var postgresDialect = dialect{
	driverName: "postgres",

	migrationSQL: `
		CREATE TABLE IF NOT EXISTS modules (
			name       TEXT PRIMARY KEY,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			created_at TIMESTAMP NOT NULL DEFAULT NOW()
		);

		CREATE TABLE IF NOT EXISTS module_patterns (
			id         BIGSERIAL PRIMARY KEY,
			pattern    TEXT NOT NULL UNIQUE,
			vcs        TEXT NOT NULL DEFAULT 'git',
			repo       TEXT NOT NULL,
			web        TEXT NOT NULL DEFAULT '',
			private    INTEGER NOT NULL DEFAULT 0,
			priority   INTEGER NOT NULL DEFAULT 0,
			created_at TIMESTAMP NOT NULL DEFAULT NOW()
		);

		CREATE TABLE IF NOT EXISTS credentials (
			name       TEXT PRIMARY KEY,
			type       TEXT NOT NULL,
			data       TEXT NOT NULL,
			created_at TIMESTAMP NOT NULL DEFAULT NOW()
		);

		CREATE TABLE IF NOT EXISTS sumdb_records (
			id   BIGINT PRIMARY KEY,
			key  TEXT NOT NULL,
			data BYTEA NOT NULL
		);

		CREATE TABLE IF NOT EXISTS sumdb_hashes (
			id   BIGINT PRIMARY KEY,
			hash BYTEA NOT NULL
		);

		CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
	`,

	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = $1",
	addModuleSQL:     "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6)",
	updateModuleSQL:  "UPDATE modules SET vcs = $1, repo = $2, web = $3, private = $4, credential_name = $5 WHERE name = $6",
	renameModuleSQL:  "UPDATE modules SET name = $1 WHERE name = $2",
	deleteModuleSQL:  "DELETE FROM modules WHERE name = $1",
	addPatternSQL:    "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7)",
	updatePatternSQL: "UPDATE module_patterns SET pattern = $1, vcs = $2, repo = $3, web = $4, private = $5, priority = $6, credential_name = $7 WHERE id = $8",
	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",

	importModuleSQL:  "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING",
	importPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT DO NOTHING",

	addCredentialSQL:    "INSERT INTO credentials (name, type, data) VALUES ($1, $2, $3)",
	updateCredentialSQL: "UPDATE credentials SET type = $1, data = $2 WHERE name = $3",
	deleteCredentialSQL: "DELETE FROM credentials WHERE name = $1",
	getCredentialSQL:    "SELECT name, type, data, created_at FROM credentials WHERE name = $1",
	listCredentialsSQL:  "SELECT name, type, data, created_at FROM credentials ORDER BY name",

	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES ($1, $2, $3)",
	insertSumdbHashSQL:   "INSERT INTO sumdb_hashes (id, hash) VALUES ($1, $2)",
	getRecordsSQL:        "SELECT data FROM sumdb_records WHERE id >= $1 AND id < $2 ORDER BY id",
	lookupRecordSQL:      "SELECT id FROM sumdb_records WHERE key = $1",
	recordCountSQL:       "SELECT COUNT(*) FROM sumdb_records",
}

func dialectFor(driver string) (dialect, error) {
	switch driver {
	case "sqlite":
		return sqliteDialect, nil
	case "postgres":
		return postgresDialect, nil
	default:
		return dialect{}, fmt.Errorf("unsupported database driver: %q (use sqlite or postgres)", driver)
	}
}


1	package store
2
3	import "fmt"
4
5	// dialect encapsulates SQL differences between database backends.
6	type dialect struct {
7	driverName string
8
9	migrationSQL string
10
11	// Query templates.
12	resolveModuleSQL string
13	addModuleSQL string
14	deleteModuleSQL string
15	updateModuleSQL string
16	renameModuleSQL string
17	addPatternSQL string
18	updatePatternSQL string
19	deletePatternSQL string
20
21	// Import queries (INSERT OR IGNORE equivalent).
22	importModuleSQL string
23	importPatternSQL string
24
25	// Credential queries.
26	addCredentialSQL string
27	updateCredentialSQL string
28	deleteCredentialSQL string
29	getCredentialSQL string
30	listCredentialsSQL string
31
32	// Sumdb queries.
33	insertSumdbRecordSQL string
34	insertSumdbHashSQL string
35	getRecordsSQL string // SELECT ... WHERE id >= ? AND id < ?
36	lookupRecordSQL string // SELECT id FROM sumdb_records WHERE key = ?
37	recordCountSQL string
38	}
39
40	var sqliteDialect = dialect{
41	driverName: "sqlite",
42
43	migrationSQL: `
44	CREATE TABLE IF NOT EXISTS modules (
45	name TEXT PRIMARY KEY,
46	vcs TEXT NOT NULL DEFAULT 'git',
47	repo TEXT NOT NULL,
48	web TEXT NOT NULL DEFAULT '',
49	private INTEGER NOT NULL DEFAULT 0,
50	created_at TEXT NOT NULL DEFAULT (datetime('now'))
51	);
52
53	CREATE TABLE IF NOT EXISTS module_patterns (
54	id INTEGER PRIMARY KEY AUTOINCREMENT,
55	pattern TEXT NOT NULL UNIQUE,
56	vcs TEXT NOT NULL DEFAULT 'git',
57	repo TEXT NOT NULL,
58	web TEXT NOT NULL DEFAULT '',
59	private INTEGER NOT NULL DEFAULT 0,
60	priority INTEGER NOT NULL DEFAULT 0,
61	created_at TEXT NOT NULL DEFAULT (datetime('now'))
62	);
63
64	CREATE TABLE IF NOT EXISTS credentials (
65	name TEXT PRIMARY KEY,
66	type TEXT NOT NULL,
67	data TEXT NOT NULL,
68	created_at TEXT NOT NULL DEFAULT (datetime('now'))
69	);
70
71	CREATE TABLE IF NOT EXISTS sumdb_records (
72	id INTEGER PRIMARY KEY,
73	key TEXT NOT NULL,
74	data BLOB NOT NULL
75	);
76
77	CREATE TABLE IF NOT EXISTS sumdb_hashes (
78	id INTEGER PRIMARY KEY,
79	hash BLOB NOT NULL
80	);
81
82	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
83	`,
84
85	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = ?",
86	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
87	updateModuleSQL: "UPDATE modules SET vcs = ?, repo = ?, web = ?, private = ?, credential_name = ? WHERE name = ?",
88	renameModuleSQL: "UPDATE modules SET name = ? WHERE name = ?",
89	deleteModuleSQL: "DELETE FROM modules WHERE name = ?",
90	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
91	updatePatternSQL: "UPDATE module_patterns SET pattern = ?, vcs = ?, repo = ?, web = ?, private = ?, priority = ?, credential_name = ? WHERE id = ?",
92	deletePatternSQL: "DELETE FROM module_patterns WHERE id = ?",
93
94	importModuleSQL: "INSERT OR IGNORE INTO modules (name, vcs, repo, web, private, credential_name) VALUES (?, ?, ?, ?, ?, ?)",
95	importPatternSQL: "INSERT OR IGNORE INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
96
97	addCredentialSQL: "INSERT INTO credentials (name, type, data) VALUES (?, ?, ?)",
98	updateCredentialSQL: "UPDATE credentials SET type = ?, data = ? WHERE name = ?",
99	deleteCredentialSQL: "DELETE FROM credentials WHERE name = ?",
100	getCredentialSQL: "SELECT name, type, data, created_at FROM credentials WHERE name = ?",
101	listCredentialsSQL: "SELECT name, type, data, created_at FROM credentials ORDER BY name",
102
103	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES (?, ?, ?)",
104	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES (?, ?)",
105	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= ? AND id < ? ORDER BY id",
106	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = ?",
107	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
108	}
109
110	var postgresDialect = dialect{
111	driverName: "postgres",
112
113	migrationSQL: `
114	CREATE TABLE IF NOT EXISTS modules (
115	name TEXT PRIMARY KEY,
116	vcs TEXT NOT NULL DEFAULT 'git',
117	repo TEXT NOT NULL,
118	web TEXT NOT NULL DEFAULT '',
119	private INTEGER NOT NULL DEFAULT 0,
120	created_at TIMESTAMP NOT NULL DEFAULT NOW()
121	);
122
123	CREATE TABLE IF NOT EXISTS module_patterns (
124	id BIGSERIAL PRIMARY KEY,
125	pattern TEXT NOT NULL UNIQUE,
126	vcs TEXT NOT NULL DEFAULT 'git',
127	repo TEXT NOT NULL,
128	web TEXT NOT NULL DEFAULT '',
129	private INTEGER NOT NULL DEFAULT 0,
130	priority INTEGER NOT NULL DEFAULT 0,
131	created_at TIMESTAMP NOT NULL DEFAULT NOW()
132	);
133
134	CREATE TABLE IF NOT EXISTS credentials (
135	name TEXT PRIMARY KEY,
136	type TEXT NOT NULL,
137	data TEXT NOT NULL,
138	created_at TIMESTAMP NOT NULL DEFAULT NOW()
139	);
140
141	CREATE TABLE IF NOT EXISTS sumdb_records (
142	id BIGINT PRIMARY KEY,
143	key TEXT NOT NULL,
144	data BYTEA NOT NULL
145	);
146
147	CREATE TABLE IF NOT EXISTS sumdb_hashes (
148	id BIGINT PRIMARY KEY,
149	hash BYTEA NOT NULL
150	);
151
152	CREATE INDEX IF NOT EXISTS idx_sumdb_records_key ON sumdb_records (key);
153	`,
154
155	resolveModuleSQL: "SELECT vcs, repo, web, private, credential_name FROM modules WHERE name = $1",
156	addModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6)",
157	updateModuleSQL: "UPDATE modules SET vcs = $1, repo = $2, web = $3, private = $4, credential_name = $5 WHERE name = $6",
158	renameModuleSQL: "UPDATE modules SET name = $1 WHERE name = $2",
159	deleteModuleSQL: "DELETE FROM modules WHERE name = $1",
160	addPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7)",
161	updatePatternSQL: "UPDATE module_patterns SET pattern = $1, vcs = $2, repo = $3, web = $4, private = $5, priority = $6, credential_name = $7 WHERE id = $8",
162	deletePatternSQL: "DELETE FROM module_patterns WHERE id = $1",
163
164	importModuleSQL: "INSERT INTO modules (name, vcs, repo, web, private, credential_name) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT DO NOTHING",
165	importPatternSQL: "INSERT INTO module_patterns (pattern, vcs, repo, web, private, priority, credential_name) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT DO NOTHING",
166
167	addCredentialSQL: "INSERT INTO credentials (name, type, data) VALUES ($1, $2, $3)",
168	updateCredentialSQL: "UPDATE credentials SET type = $1, data = $2 WHERE name = $3",
169	deleteCredentialSQL: "DELETE FROM credentials WHERE name = $1",
170	getCredentialSQL: "SELECT name, type, data, created_at FROM credentials WHERE name = $1",
171	listCredentialsSQL: "SELECT name, type, data, created_at FROM credentials ORDER BY name",
172
173	insertSumdbRecordSQL: "INSERT INTO sumdb_records (id, key, data) VALUES ($1, $2, $3)",
174	insertSumdbHashSQL: "INSERT INTO sumdb_hashes (id, hash) VALUES ($1, $2)",
175	getRecordsSQL: "SELECT data FROM sumdb_records WHERE id >= $1 AND id < $2 ORDER BY id",
176	lookupRecordSQL: "SELECT id FROM sumdb_records WHERE key = $1",
177	recordCountSQL: "SELECT COUNT(*) FROM sumdb_records",
178	}
179
180	func dialectFor(driver string) (dialect, error) {
181	switch driver {
182	case "sqlite":
183	return sqliteDialect, nil
184	case "postgres":
185	return postgresDialect, nil
186	default:
187	return dialect{}, fmt.Errorf("unsupported database driver: %q (use sqlite or postgres)", driver)
188	}
189	}
190

dialect.go

Source Files