dialect.go

v1.3.0

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

dialect.go

Source Files