1  //go:build kvdb_postgres || (kvdb_sqlite && !(windows && (arm || 386)) && !(linux && (ppc64 || mips || mipsle || mips64)))
 2  
 3  package sqlbase
 4  
 5  import (
 6  	"fmt"
 7  	"strings"
 8  )
 9  
10  // SQLiteCmdReplacements is a one to one mapping of sqlite keywords that should
11  // be replaced by the mapped strings in any command. Note that the sqlite
12  // keywords to be replaced are case-sensitive.
13  type SQLiteCmdReplacements map[string]string
14  
15  func newKVSchemaCreationCmd(table, schema string,
16  	replacements SQLiteCmdReplacements) string {
17  
18  	var (
19  		tableInSchema = table
20  		finalCmd      string
21  	)
22  	if schema != "" {
23  		finalCmd = fmt.Sprintf(
24  			`CREATE SCHEMA IF NOT EXISTS ` + schema + `;`,
25  		)
26  
27  		tableInSchema = fmt.Sprintf("%s.%s", schema, table)
28  	}
29  
30  	// Construct the sql statements to set up a kv table in postgres. Every
31  	// row points to the bucket that it is one via its parent_id field. A
32  	// NULL parent_id means that the key belongs to the uppermost bucket in
33  	// this table. A constraint on parent_id is enforcing referential
34  	// integrity.
35  	//
36  	// Furthermore, there is a <table>_p index on parent_id that is required
37  	// for the foreign key constraint.
38  	//
39  	// Finally, there are unique indices on (parent_id, key) to prevent the
40  	// same key being present in a bucket more than once (<table>_up and
41  	// <table>_unp). In postgres, a single index wouldn't enforce the unique
42  	// constraint on rows with a NULL parent_id. Therefore, two indices are
43  	// defined.
44  	//
45  	// The replacements map can be used to replace any sqlite keywords.
46  	// Callers should note that the sqlite keywords are case-sensitive.
47  	finalCmd += fmt.Sprintf(`
48  CREATE TABLE IF NOT EXISTS ` + tableInSchema + `
49  (
50      key BLOB NOT NULL,
51      value BLOB,
52      parent_id BIGINT,
53      id INTEGER PRIMARY KEY,
54      sequence BIGINT,
55      CONSTRAINT ` + table + `_parent FOREIGN KEY (parent_id)
56          REFERENCES ` + tableInSchema + ` (id)
57          ON UPDATE NO ACTION
58          ON DELETE CASCADE
59  );
60  CREATE INDEX IF NOT EXISTS ` + table + `_p
61      ON ` + tableInSchema + ` (parent_id);
62  CREATE UNIQUE INDEX IF NOT EXISTS ` + table + `_up
63      ON ` + tableInSchema + `
64      (parent_id, key) WHERE parent_id IS NOT NULL;
65  CREATE UNIQUE INDEX IF NOT EXISTS ` + table + `_unp 
66      ON ` + tableInSchema + ` (key) WHERE parent_id IS NULL;
67  `)
68  
69  	for from, to := range replacements {
70  		finalCmd = strings.Replace(finalCmd, from, to, -1)
71  	}
72  
73  	return finalCmd
74  }