Seafile 批量导入账号名称及电话号码

前言

seafile 可以使用 CSV 导入用户名密码来新建用户,但是只能导入用户名和密码,名称则无法导入,但是用户名只能用邮箱这点不是很方便,故此需要找到一个批量导入账号名称的办法.

数据库结构

seafile 的账号和密码均存储在 mysql 的 ccnet-db 数据库内,但账号的详细信息例如名称、电话、部门这些信息是存储在 seahub-db 数据库里面.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
MariaDB [(none)]> use seahub-db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [seahub-db]> show tables;
+-------------------------------------------+
| Tables_in_seahub-db |
+-------------------------------------------+
| api2_token |
| api2_tokenv2 |
| avatar_avatar |
| avatar_groupavatar |
| base_clientlogintoken |
| base_commandslastcheck |
| base_devicetoken |
| base_filecomment |
| base_filediscuss |
| base_groupenabledmodule |
| base_innerpubmsg |
| base_innerpubmsgreply |
| base_userenabledmodule |
| base_userlastlogin |
| base_userstarredfiles |
| captcha_captchastore |
| constance_config |
| contacts_contact |
| django_content_type |
| django_migrations |
| django_session |
| group_groupmessage |
| group_messageattachment |
| group_messagereply |
| group_publicgroup |
| institutions_institution |
| institutions_institutionadmin |
| invitations_invitation |
| message_usermessage |
| message_usermsgattachment |
| message_usermsglastcheck |
| notifications_notification |
| notifications_usernotification |
| options_useroptions |
| organizations_orgmemberquota |
| post_office_attachment |
| post_office_attachment_emails |
| post_office_email |
| post_office_emailtemplate |
| post_office_log |
| profile_detailedprofile |
| profile_profile |
| registration_registrationprofile |
| share_anonymousshare |
| share_fileshare |
| share_orgfileshare |
| share_privatefiledirshare |
| share_uploadlinkshare |
| sysadmin_extra_userloginlog |
| termsandconditions_termsandconditions |
| termsandconditions_usertermsandconditions |
| two_factor_phonedevice |
| two_factor_staticdevice |
| two_factor_statictoken |
| two_factor_totpdevice |
| wiki_groupwiki |
| wiki_personalwiki |
+-------------------------------------------+
57 rows in set (0.00 sec)

其中profile_profile是存放个人资料的表,而profile_detailedprofile是存放电话号码的表格,他们均以一个字段user(用户名邮箱)来做标识达成一一对应.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
MariaDB [(none)]> use seahub-db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [seahub-db]> desc profile_profile;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(254) | NO | UNI | NULL | |
| nickname | varchar(64) | NO | | NULL | |
| intro | longtext | NO | | NULL | |
| lang_code | longtext | YES | | NULL | |
| login_id | varchar(225) | YES | UNI | NULL | |
| contact_email | varchar(225) | YES | MUL | NULL | |
| institution | varchar(225) | YES | MUL | NULL | |
+---------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
MariaDB [seahub-db]> desc profile_detailedprofile;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user | varchar(255) | NO | MUL | NULL | |
| department | varchar(512) | NO | | NULL | |
| telephone | varchar(100) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

那么我们只需要将我们的信息导入到这两个表就可以了

导入数据

比如现在我们需要导入用户名:zhangsan@domain.com;名称:张三;电话:12345678.
名称是在profile_profile里面字段是nickname,而电话是在profile_detailedprofile里面,字段是telephone.

1
2
3
4
# 名称导入
insert into profile_profile (user,nickname) values ('zhangsan@domain.com','张三')
# 电话号码导入
insert into profile_detailedprofile (user,telephone) values ('zhangsan@domain.com','12345678')

如果多条数据导入可以这样写

1
2
3
4
# 名称导入
insert into profile_profile (user,nickname) values ('zhangsan@domain.com','张三'),('lisi@domain.com','李四')
# 电话号码导入
insert into profile_detailedprofile (user,telephone) values ('zhangsan@domain.com','12345678'),('lisi@domain.com','87654321')

导入完成后打开用户页面就能见到用户名称和电话号码,其他字段的属性导入也是差不多

参考文献

mysql批量插入(insert)与批量更新(update)的例子

修改记录

  1. 2016年11月7日:经 Seafile 中文论坛兄弟(skipto)提醒,数据库 id 字段是自动添加,无需导入,把 id 导入字段删除.