123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270 |
- -- create database
- CREATE DATABASE if not exists milvus_meta CHARACTER SET utf8mb4;
- /*
- create tables script
- Notices:
- 1. id, tenant_id, is_deleted, created_at, updated_at are 5 common columns for all collections.
- 2. Query index in community version CANNOT includes tenant_id, since tenant_id is not existed and will miss query index.
- */
- -- collections
- CREATE TABLE if not exists milvus_meta.collections (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- collection_id BIGINT NOT NULL,
- collection_name VARCHAR(256),
- description VARCHAR(2048) DEFAULT NULL,
- auto_id BOOL DEFAULT FALSE,
- shards_num INT,
- start_position TEXT,
- consistency_level INT,
- status INT NOT NULL,
- properties VARCHAR(512),
- ts BIGINT UNSIGNED DEFAULT 0,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_collection_id_ts (tenant_id, collection_id, ts)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- collection aliases
- CREATE TABLE if not exists milvus_meta.collection_aliases (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- collection_id BIGINT NOT NULL,
- collection_alias VARCHAR(128),
- ts BIGINT UNSIGNED DEFAULT 0,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_collection_alias_ts (tenant_id, collection_alias, ts),
- INDEX idx_tenant_id_collection_id_ts (tenant_id, collection_id, ts)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- channels
- CREATE TABLE if not exists milvus_meta.collection_channels (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- collection_id BIGINT NOT NULL,
- virtual_channel_name VARCHAR(256) NOT NULL,
- physical_channel_name VARCHAR(256) NOT NULL,
- removed BOOL DEFAULT FALSE,
- ts BIGINT UNSIGNED DEFAULT 0,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_collection_id_virtual_channel_name_ts (tenant_id, collection_id, virtual_channel_name, ts),
- INDEX idx_tenant_id_collection_id_ts (tenant_id, collection_id, ts)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- fields
- CREATE TABLE if not exists milvus_meta.field_schemas (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- field_id BIGINT NOT NULL,
- field_name VARCHAR(256) NOT NULL,
- is_primary_key BOOL NOT NULL,
- description VARCHAR(2048) DEFAULT NULL,
- data_type INT UNSIGNED NOT NULL,
- type_params VARCHAR(2048),
- index_params VARCHAR(2048),
- auto_id BOOL NOT NULL,
- collection_id BIGINT NOT NULL,
- ts BIGINT UNSIGNED DEFAULT 0,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_collection_id_field_name_ts (tenant_id, collection_id, field_name, ts),
- INDEX idx_tenant_id_collection_id_field_id_ts (tenant_id, collection_id, field_id, ts)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- partitions
- CREATE TABLE if not exists milvus_meta.`partitions` (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- partition_id BIGINT NOT NULL,
- partition_name VARCHAR(256),
- partition_created_timestamp bigint unsigned,
- collection_id BIGINT NOT NULL,
- status INT NOT NULL,
- ts BIGINT UNSIGNED DEFAULT 0,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_collection_id_partition_name_ts (tenant_id, collection_id, partition_name, ts),
- INDEX idx_tenant_id_collection_id_partition_id_ts (tenant_id, collection_id, partition_id, ts)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- indexes
- CREATE TABLE if not exists milvus_meta.`indexes` (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- field_id BIGINT NOT NULL,
- collection_id BIGINT NOT NULL,
- index_id BIGINT NOT NULL,
- index_name VARCHAR(256),
- index_params VARCHAR(2048),
- user_index_params VARCHAR(2048),
- is_auto_index BOOL DEFAULT FALSE,
- create_time bigint unsigned,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- INDEX idx_tenant_id_collection_id_index_id (tenant_id, collection_id, index_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- index file paths
- CREATE TABLE if not exists milvus_meta.index_file_paths (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- index_build_id BIGINT NOT NULL,
- index_file_path VARCHAR(256),
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- INDEX idx_tenant_id_index_build_id (tenant_id, index_build_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- segments
- CREATE TABLE if not exists milvus_meta.segments (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- segment_id BIGINT NOT NULL,
- collection_id BIGINT NOT NULL,
- partition_id BIGINT NOT NULL,
- num_rows BIGINT NOT NULL,
- max_row_num INT COMMENT 'estimate max rows',
- dm_channel VARCHAR(128) NOT NULL,
- dml_position TEXT COMMENT 'checkpoint',
- start_position TEXT,
- compaction_from VARCHAR(4096) COMMENT 'old segment IDs',
- created_by_compaction BOOL,
- segment_state TINYINT UNSIGNED NOT NULL,
- last_expire_time bigint unsigned COMMENT 'segment assignment expiration time',
- dropped_at bigint unsigned,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- INDEX idx_tenant_id_collection_id_segment_id (tenant_id, collection_id, segment_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- segment indexes
- CREATE TABLE if not exists milvus_meta.segment_indexes (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- collection_id BIGINT NOT NULL,
- partition_id BIGINT NOT NULL,
- segment_id BIGINT NOT NULL,
- field_id BIGINT NOT NULL,
- index_id BIGINT NOT NULL,
- index_build_id BIGINT,
- enable_index BOOL NOT NULL,
- create_time bigint unsigned,
- index_file_keys VARCHAR(4096),
- index_size BIGINT UNSIGNED,
- `version` INT UNSIGNED,
- is_deleted BOOL DEFAULT FALSE COMMENT 'as mark_deleted',
- recycled BOOL DEFAULT FALSE COMMENT 'binlog files truly deleted',
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- UNIQUE KEY uk_tenant_id_segment_id_index_id (tenant_id, segment_id, index_id),
- INDEX idx_tenant_id_collection_id_segment_id_index_id (tenant_id, collection_id, segment_id, index_id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- binlog files info
- CREATE TABLE if not exists milvus_meta.binlogs (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- field_id BIGINT NOT NULL,
- segment_id BIGINT NOT NULL,
- collection_id BIGINT NOT NULL,
- log_type SMALLINT UNSIGNED NOT NULL COMMENT 'binlog、stats binlog、delta binlog',
- num_entries BIGINT,
- timestamp_from BIGINT UNSIGNED,
- timestamp_to BIGINT UNSIGNED,
- log_path VARCHAR(256) NOT NULL,
- log_size BIGINT NOT NULL,
- is_deleted BOOL DEFAULT FALSE,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- INDEX idx_tenant_id_segment_id_log_type (tenant_id, segment_id, log_type)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- users
- CREATE TABLE if not exists milvus_meta.credential_users (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- username VARCHAR(128) NOT NULL,
- encrypted_password VARCHAR(256) NOT NULL,
- is_super BOOL NOT NULL DEFAULT false,
- is_deleted BOOL NOT NULL DEFAULT false,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- PRIMARY KEY (id),
- INDEX idx_tenant_id_username (tenant_id, username)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- role
- CREATE TABLE if not exists milvus_meta.role (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- name VARCHAR(128) NOT NULL,
- is_deleted BOOL NOT NULL DEFAULT false,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- INDEX idx_role_tenant_name (tenant_id, name, is_deleted),
- PRIMARY KEY (id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- user-role
- CREATE TABLE if not exists milvus_meta.user_role (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- user_id BIGINT NOT NULL,
- role_id BIGINT NOT NULL,
- is_deleted BOOL NOT NULL DEFAULT false,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- INDEX idx_role_mapping_tenant_user_role (tenant_id, user_id, role_id, is_deleted),
- PRIMARY KEY (id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- grant
- CREATE TABLE if not exists milvus_meta.grant (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- role_id BIGINT NOT NULL,
- object VARCHAR(128) NOT NULL,
- object_name VARCHAR(128) NOT NULL,
- is_deleted BOOL NOT NULL DEFAULT false,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- INDEX idx_grant_principal_resource_tenant (tenant_id, role_id, object, object_name, is_deleted),
- PRIMARY KEY (id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- -- grant-id
- CREATE TABLE if not exists milvus_meta.grant_id (
- id BIGINT NOT NULL AUTO_INCREMENT,
- tenant_id VARCHAR(128) DEFAULT NULL,
- grant_id BIGINT NOT NULL,
- grantor_id BIGINT NOT NULL,
- privilege VARCHAR(128) NOT NULL,
- is_deleted BOOL NOT NULL DEFAULT false,
- created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update current_timestamp,
- INDEX idx_grant_id_tenant_grantor (tenant_id, grant_id, grantor_id, is_deleted),
- PRIMARY KEY (id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|