Database Schema

Tables

mem_*

For Managing memberships and ACL

mem_user

User accounts

Column	Key	Null	Type	Length	Description
user_id	PK	NN	VARCHAR	32
email	UQ		VARCHAR	255	email address
password			VARCHAR	255	password for login (SHA-256)
name			VARCHAR	128	user name
company			VARCHAR	128	Deprecated: Not used
telephone			VARCHAR	32	Deprecated: Not used
department			VARCHAR	128	Deprecated: Not used
url			VARCHAR	255	Deprecated: Not used
location			TEXT		Deprecated: Not used
gravatar			VARCHAR	255	gravatar account email address, Deprecated: Not used
act_key	UQ		VARCHAR	255	account activation key
status			TINYINT	1	0=pending, 1=approved, 2=rejected, 3=password reset
type			TINYINT	1	0=user, 1=admin
uid	UQ		INT UNSIGNED		Deprecated: replace with user_id
last_login_time			DATETIME		last login time
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

mem_group

Groups (Not used now)

Column	Key	Null	Type	Length	Description
group_id	PK	NN	VARCHAR	32
name			VARCHAR	255
owner_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
user_data			TEXT
gid			INT UNSIGNED		Deprecated: replace with group_id
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

mem_group_user

Relations with group and user (Not used now)

Column	Key	Null	Type	Length	Description
group_id	PK	NN	VARCHAR	32
user_id	PK	NN	VARCHAR	32
create_time			DATETIME		creation time

mem_subject

Subjects for ACL

Column	Key	Null	Type	Length	Description
subject_id	PK	NN	VARCHAR	32	same with user_id or group_id
type		NN	CHAR	1	'u'=user, 'g'=group
uid			INT UNSIGNED		Deprecated: replace with user_id or group_id
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

mem_policy

Policies for ACL

Column	Key	Null	Type	Length	Description
policy_id	PK	NN	VARCHAR	32
name			VARCHAR	255	policy name
owner_id	FK	NN	VARCHAR	32	owner user id (mem_user.user_id)
effect			VARCHAR	8	'allow' or 'deny'
action			TEXT		json to represent actions
resource			TEXT		json to represent target resources
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

mem_policy_subject

Relations with policy and subject

Column	Key	Null	Type	Length	Description
policy_id	PK	NN	VARCHAR	32
subject_id	PK	NN	VARCHAR	32
create_time			DATETIME		creation time

mem_temp_key

Temporary key to modify password

Column	Key	Null	Type	Length	Description
key_id	PK	NN	VARCHAR	32
user_id	FK		VARCHAR	32
key			VARCHAR	255
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

webida_*

For webida's extra features

webida_app

Deployed app created by webida deploy service

Column	Key	Null	Type	Length	Description
app_id	PK	NN	VARCHAR	32
key			VARCHAR	255	Deprecated: replace with app_id
name	NN		VARCHAR	255
domain			VARCHAR	255	sub domain or sub path to access to deployed app
type			VARCHAR	8	'html' or 'nodejs'
process_id			INT
port			INT
desc			TEXT		description
owner_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
source_url			VARCHAR	255	url to source (e.g. git address - deployFromGit())
is_deployed			TINYINT	1	0=not deployed, 1=deployed
status			VARCHAR	8	'running' or 'stop'
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

webida_key_store

Key store files that support mobile developement

Column	Key	Null	Type	Length	Description
key_store_id	PK	NN	VARCHAR	32
wfs_id	FK		VARCHAR	32	wfs id (fs_wfs.wfs_id)
user_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
alias			VARCHAR	255
file_name			VARCHAR	255
key_password			VARCHAR	255
key_store_password			VARCHAR	255
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

webida_gcm_info

GCM informations that supoort mobile development

Column	Key	Null	Type	Length	Description
gcm_info_id	PK	NN	VARCHAR	32
user_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
reg_id			VARCHAR	255	registration id
info			VARCHAR	255
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

oauth_*

For oauth feature

oauth_client

Clients for OAuth

Column	Key	Null	Type	Length	Description
client_id	PK	NN	VARCHAR	32
name			VARCHAR	255	client name
oauth_client_id			VARCHAR	255	client id
oauth_client_secret			VARCHAR	255	client secret (Not used)
is_system			TINYINT	1	0=client, 1=system client
redirect_url			VARCHAR	255	redirect url after authentication
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

oauth_code

Codes for OAuth's code grant authentication (Not used now)

Column	Key	Null	Type	Length	Description
code_id	PK	NN	VARCHAR	32
code			VARCHAR	255	code
oauth_client_id			VARCHAR	255	client id
redirect_url			VARCHAR	255	redirect url
user_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
expire_time			DATETIME		expiration time
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

oauth_token

Tokens for OAuth

Column	Key	Null	Type	Length	Description
token_id	PK	NN	VARCHAR	32
token			VARCHAR	255	token string
user_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
oauth_client_id			VARCHAR	255	Deprecated: Only write, no read
validity_period			INT		0=infinite, n>0=period in sec
expire_time			DATETIME		expration time
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

fs_*

For webida's fs services

fs_alias

Short names for each paths to some directory

Column	Key	Null	Type	Length	Description
alias_id	PK	NN	VARCHAR	32
key		NN	VARCHAR	255	alias name used in url (same with alias_id)
url			VARCHAR	255	acccessable url
wfs_id	FK		VARCHAR	32	wfs id (fs_wfs.wfs_id)
path			VARCHAR	255	path to source in wfs
owner_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
validity_period			INT		0=infinite, n>0=period in sec
expire_time			DATETIME		expiration time
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

fs_download_link

Temporary link for downloading (only for UIP)

Column	Key	Null	Type	Length	Description
download_link_id	PK	NN	VARCHAR	32
key			VARCHAR	255
wfs_id	FK		VARCHAR	32	wfs id (fs_wfs.wfs_id)
path			VARCHAR	255
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

fs_lock

File locks

Column	Key	Null	Type	Length	Description
lock_id	PK	NN	VARCHAR	32
user_id	FK		VARCHAR	32	user id (mem_user.user_id)
email			VARCHAR	255	user email address.
wfs_id	FK		VARCHAR	32	wfs id (fs_wfs.wfs_id)
path			VARCHAR	255	path to the lock file
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

fs_wfs

Webida file systems information

Column	Key	Null	Type	Length	Description
wfs_id	PK	NN	VARCHAR	32
key			VARCHAR	255	used by wfs root directory name (same with wfs_id)
owner_id	FK		VARCHAR	32	owner user id (mem_user.user_id)
create_time			DATETIME		creation time
update_time			DATETIME		last modified time

fs_wfs_del

Webida file systems removed

Column	Key	Null	Type	Length	Description
wfs_id	PK	NN	VARCHAR	32
key			VARCHAR	255	used by wfs root directory name (same with wfs_id)
owner_id			VARCHAR	32	owner user id (mem_user.user_id) - might be removed already
delete_time			DATETIME		deleted time

Etc

sequence

Sequences ids for some tables

Currently, there are 3 spaces to be managed:

• guestid: for guest id (only used when guest mode is on)
• uid: for mem_user.uid or mem_group.gid (Deprecated)
• projectid: project id for XFS (only created when using XFS file system)

Column	Key	Null	Type	Length	Description
space	PK	NN	VARCHAR	32	space name for detecting sequence type
current_seq			INT UNSIGNED		current sequence number
max_seq			INT UNSIGNED		max value of sequence number (default: 4294967295)
create_time			DATETIME		creation time