github.com/authzed/spicedb@v1.32.1-0.20240520085336-ebda56537386/internal/datastore/mysql/migrations/zz_migration.0001_initial_schema.go (about)

     1  package migrations
     2  
     3  import "fmt"
     4  
     5  func createMigrationVersion(t *tables) string {
     6  	// we need the additional primary key column because github.com/github/gh-ost requires a shared, not-null
     7  	// key between the _to_ and _from_ table schemas to perform a schema migration.
     8  	// -- https://github.com/github/gh-ost/blob/master/doc/shared-key.md
     9  	return fmt.Sprintf(`CREATE TABLE %s (
    10  		id int(11) NOT NULL PRIMARY KEY,
    11  		_meta_version_ VARCHAR(255) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
    12  		t.migrationVersion(),
    13  	)
    14  }
    15  
    16  // namespace max size: https://buf.build/authzed/api/file/main/authzed/api/v0/core.proto#L29
    17  func createNamespaceConfig(t *tables) string {
    18  	return fmt.Sprintf(`CREATE TABLE %s (
    19  		namespace VARCHAR(128) NOT NULL,
    20  		serialized_config BLOB NOT NULL,
    21  		created_transaction BIGINT NOT NULL,
    22  		deleted_transaction BIGINT NOT NULL DEFAULT '9223372036854775807',
    23  		CONSTRAINT pk_namespace_config PRIMARY KEY (namespace, created_transaction),
    24  		CONSTRAINT uq_namespace_living UNIQUE (namespace, deleted_transaction)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
    25  		t.Namespace(),
    26  	)
    27  }
    28  
    29  // relationship max size: https://buf.build/authzed/api/file/main:authzed/api/v1/core.proto#L33
    30  // object id max size: https://buf.build/authzed/api/file/main:authzed/api/v1/core.proto#L45
    31  func createRelationTuple(t *tables) string {
    32  	return fmt.Sprintf(`CREATE TABLE %s (
    33  		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    34  		namespace VARCHAR(128) NOT NULL,
    35  		object_id VARCHAR(128) NOT NULL,
    36  		relation VARCHAR(64) NOT NULL,
    37  		userset_namespace VARCHAR(128) NOT NULL,
    38  		userset_object_id VARCHAR(128) NOT NULL,
    39  		userset_relation VARCHAR(64) NOT NULL,
    40  		created_transaction BIGINT NOT NULL,
    41  		deleted_transaction BIGINT NOT NULL DEFAULT '9223372036854775807',
    42  		PRIMARY KEY (id),
    43  		CONSTRAINT uq_relation_tuple_namespace UNIQUE (namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction),
    44  		CONSTRAINT uq_relation_tuple_living UNIQUE (namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, deleted_transaction),
    45          INDEX ix_relation_tuple_by_subject (userset_object_id, userset_namespace, userset_relation, namespace, relation),
    46          INDEX ix_relation_tuple_by_subject_relation (userset_namespace, userset_relation, namespace, relation),
    47          INDEX ix_relation_tuple_by_deleted_transaction (deleted_transaction)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
    48  		t.RelationTuple(),
    49  	)
    50  }
    51  
    52  func createRelationTupleTransaction(t *tables) string {
    53  	return fmt.Sprintf(`CREATE TABLE %s (
    54  		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    55  		timestamp DATETIME(6) DEFAULT NOW(6) NOT NULL,
    56  		PRIMARY KEY (id),
    57          INDEX ix_relation_tuple_transaction_by_timestamp (timestamp)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;`,
    58  		t.RelationTupleTransaction(),
    59  	)
    60  }
    61  
    62  func init() {
    63  	mustRegisterMigration("initial", "", noNonatomicMigration,
    64  		newStatementBatch(
    65  			createMigrationVersion,
    66  			createNamespaceConfig,
    67  			createRelationTuple,
    68  			createRelationTupleTransaction,
    69  		).execute,
    70  	)
    71  }