适合课题组内的多人协同的文件表结构设计

 

 

1.适合场景：
课题组建了一个项目，里面有（张三、李四）协同开发这个项目，现在需要张三、李四都可以看到这个项目的结果文件，

用到了3张表
file_dir //文件目录表（文件夹+文件）主要用于展示
file_member //关联用户：标记文件的归属用户，用privilege_hash关联
file //文件基本信息表，记录了磁盘上的文件位置

1.文件夹+文件的虚拟目录结构
 

CREATE TABLE `file_dir` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '文件名称/目录名称',
`type` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '类型：1=>文件夹，2=>文件',
`name_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT '名称hash值',
`privilege_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT '权限HASH',
`unique_hash` CHAR(32) NOT NULL DEFAULT '',
`file_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT '真实文件',
`parent_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT '父HASH',
`salt` INT(10) NOT NULL DEFAULT '0' COMMENT '唯一标识',
`path` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '相对目录',
`level` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '层级',
`desc` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '描述',
`system_dir` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0=>普通，1=>项目，2=>任务',
`time` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '时间',
`file_size` BIGINT(20) NOT NULL DEFAULT '0',
`is_lock` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否锁定1=>锁定',
`search_title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '项目名或任务名',
PRIMARY KEY (`id`),
UNIQUE INDEX `unique_hash` (`unique_hash`),
INDEX `parent_hash` (`parent_hash`),
INDEX `privilege_hash` (`privilege_hash`),
INDEX `path` (`path`),
INDEX `level` (`level`)
)
COMMENT='文件目录'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=9093861;

 

id	type	name_hash	privilege_hash	unique_hash	file_hash	parent_hash	salt	path	level	desc	system_dir	time	file_size	is_lock	search_title
7224285	2	92eef0599f88881a99b1897baface193	ad942291f10de39e636d98ff1ddd1081	ffc40d27fb9a841a3a640ac39bc89821	8e18ee501cc4e7e542203c902852d509	da8caa2c188ba7cadfdb80810078e984	188	rerewrweset/files/188_5af3dc68d1df4/tsg_29727/workflow_results/03.map_stat/map_bam/sort_bams/	8		0	1526429152	40065841656	0
7262598	2	92eef0599f88881a99b1897baface193	ad942291f10de39e636d98ff1ddd1081	94704958e9b6796376c0be89e42f67da	f8ba62dad8e57cbe99476307bfa977f1	71bf16159a50f2c4f923e3eda32d4718	188	rerewrweset/files/188_5af3dc68d1df4/tsg_29869/workflow_results/03.map_stat/map_bam/sort_bams/	8		0	1526868474	39968390937	0
7360315	2	92eef0599f88881a99b1897baface193	ad942291f10de39e636d98ff1ddd1081	d35ff7740f183644dfd6672bcffeaaf4	3d88b5e8c420fc553d99313d34fa13ff	9eba85e56315452040fb6faca6c83f84	188	rerewrweset/files/188_5af3dc68d1df4/tsanger_30109/workflow_results/03.map_stat/map_bam/sort_bams/	8		0	1527824861	39968111883	0

 

说明：
        path:  rerewrweset/files/188_5af3dc68d1df4/tsg_29727/
                  rerewrweset：代表bucket
                  files:一个固定的文件夹
                  188_5af3dc68d1df4：项目编号
                  tsg_29727：任务编号
       因为是以项目为单位来达到文件共享的，所以第三层用于privilege_hash就特别有意义了：

       privilege_hash（权限HASH）：
                第三层及后面的子路径用的都是想同的privilege_hash,这样查询当前项目的文件的话，直接查询privilege_hash就行了，最大化的你用了索引缩小了查询的范围
                 构成:第三层的文件夹名称+salt(    md5(188_5af3dc68d1df4+'/'+188)         

     unique_hash(当前目录的唯一hash):

                 文件夹：md5(path+name+'/'+salt+'/1')
                 文件：    md5(path+name+'/'+salt+'/2')

     parent_hash就是上级的unique_hash

     file_hash:文件的hash值：文件路径的hash值，确保唯一性
    用hash的好处，可以达到批量插入数据，如果用传统的id关联只能一个个插入数据

   2.文件用户表
 

CREATE TABLE `file_member` (
`file_member_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`member_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '拥有人',
`privilege` TINYINT(1) UNSIGNED NOT NULL DEFAULT '3' COMMENT '默认管理权限',
`privilege_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT '文件权限HASH',
`is_creator` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否创建者',
`is_deleted` TINYINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`file_member_id`),
UNIQUE INDEX `unique` (`member_id`, `privilege_hash`),
INDEX `member_id` (`member_id`),
INDEX `privilege_hash` (`privilege_hash`)
)
COMMENT='文件用户'
COLLATE='utf8_general_ci'
ENGINE=InnoDB

file_member_id	member_id	privilege	privilege_hash	is_creator	is_deleted
4930	188	3	ad942291f10de39e636d98ff1ddd1081	1	0
5085	195	3	ad942291f10de39e636d98ff1ddd1081	0	0

 

 

这样就可以通过file_member与file_dir表关联去查对应的数据了

 

3.文件信息表

 

CREATE TABLE `file` (
`file_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文件ID',
`file_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '文件名称',
`file_size` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
`file_type_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '文件类型ID',
`file_url` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '文件URL',
`created_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '创建人ID',
`created_by` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '创建人',
`created_ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`region` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '区域 0=>磁盘',
`file_hash` CHAR(32) NOT NULL DEFAULT '' COMMENT 'md5(file_url)',   
`file_desc` VARCHAR(300) NULL DEFAULT '' COMMENT '文件描述',
PRIMARY KEY (`file_id`),
UNIQUE INDEX `file_hash` (`file_hash`),
)
COMMENT='上传文件信息表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB

file_id	file_name	file_type_id	file_size	file_url	created_id	created_by	created_ts	region	file_hash	file_desc
4969541	02Y_pseudogene.xls	69	934	rerewrweset/files/m_188/188_5b459756d0ec3/tsg_31373/interaction_results/Pseudogene/02Y_GCA_900079185_1_Pseudogene_20180928_085317083/02Y_pseudogene.xls	188		2018/9/28 9:48	0	dd9ce47683111032903d8cfeba75c198
4969540	02Y_pseudogene.xls	69	120	rerewrweset/files/m_188/188_5b459756d0ec3/tsg_31373/interaction_results/Pseudogene/02Y_GCA_002105175_1_Pseudogene_20180928_085922206/02Y_pseudogene.xls	188		2018/9/28 8:59	0	a643b7fe6eece298cf8170f0ef784279
4969539	unigene_anno_detail.xls	0	21201168	rerewrweset/files/m_188/188_5ba44756e0752/tsg_32109/interaction_results/AnnotationStat_latest_20180927_102356/AnnoQuery/unigene_anno_detail.xls	188		2018/9/27 18:10	0	e46c090baacacd6445b56620fe0caccc
4969538	transcript_anno_detail.xls	0	33150858	rerewrweset/files/m_188/188_5ba44756e0752/tsg_32109/interaction_results/AnnotationStat_latest_20180927_102356/AnnoQuery/transcript_anno_detail.xls	188		2018/9/27 18:10	0	4b8aea337a609101582bfba27051e547