Skip to content

Module.user

Jump to bottom Edit New page
Taiwen Jiang edited this page Aug 11, 2013 · 10 revisions

User module schema and API design

User schema setup

User model schema

# User account and authentication data
CREATE TABLE `{account}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `identity`        varchar(32)     NOT NULL,
  `credential`      varchar(255)    NOT NULL default '',    # Credential hash
  `salt`            varchar(255)    NOT NULL default '',    # Hash salt
  `email`           varchar(64)     NOT NULL,
  `name`            varchar(255)    NOT NULL default '',
  `active`          tinyint(1)      NOT NULL default '0',

  PRIMARY KEY  (`id`),
  UNIQUE KEY `identity` (`identity`),
  UNIQUE KEY `email` (`email`)
);

# Profile schema for basic fields
CREATE TABLE `{profile}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `uid`             int(10)         unsigned    NOT NULL,
  `gender`          varchar(255)    NOT NULL default '',
  `fullname`        varchar(255)    NOT NULL default '',
  `birthdate`       varchar(10)     NOT NULL default '',    # YYYY-mm-dd
  `location`        varchar(255)    NOT NULL default '',
  `signature`       varchar(255)    NOT NULL default '',
  `bio`             text,
  `avatar`          text,           # Link to avatar image, or email for gravatar

  PRIMARY KEY  (`id`),
  UNIQUE KEY `user` (`uid`)
);

# Entity meta for custom user profile fields
CREATE TABLE `{custom}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `uid`             int(10)         unsigned    NOT NULL,
  `field`           varchar(64)     NOT NULL,   # Custom field name
  `value`           text,

  PRIMARY KEY  (`id`),
  UNIQUE KEY  `field` (`uid`, `field`)
);

# Entity meta for all profile fields: account, basic profile and custom fields
CREATE TABLE `{profile_field}` (
  `id`              smallint(5)     unsigned    NOT NULL    auto_increment,
  `name`            varchar(64)     NOT NULL,
  `module`          varchar(64)     NOT NULL default '',
  `title`           varchar(255)    NOT NULL default '',
  `edit`            text,           # callback options for edit
  `filter`          text,           # callback options for output filtering

  `type`            ENUM('custom', 'account', 'profile'),   # Field type, default as custom
  `is_edit`         tinyint(1)      NOT NULL default '1',   # Is editable
  `is_search`       tinyint(1)      NOT NULL default '1',   # Is searchable
  `is_display`      tinyint(1)      NOT NULL default '1',   # Display on profile page
  `active`          tinyint(1)      NOT NULL default '1',   # Is active

  PRIMARY KEY  (`id`),
  UNIQUE KEY  `name` (`name`)
);

# Timeline meta
CREATE TABLE `{timeline_type}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `name`            varchar(64)     NOT NULL    default '',
  `title`           varchar(255)    NOT NULL    default '',
  `module`          varchar(64)     NOT NULL    default '',
  `icon`            text,

  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`module`, `name`)
);

# Activity meta
CREATE TABLE `{activity}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `name`            varchar(64)     NOT NULL    default '',
  `title`           varchar(255)    NOT NULL    default '',
  `module`          varchar(64)     NOT NULL    default '',
  `link`            text,
  `icon`            text,

  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`module`, `name`)
);

# Quicklinks
CREATE TABLE `{quicklink}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `name`            varchar(64)     NOT NULL    default '',
  `title`           varchar(255)    NOT NULL    default '',
  `module`          varchar(64)     NOT NULL    default '',
  `link`            text,
  `icon`            text,

  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`module`, `name`)
);

# Timeline for user activities
CREATE TABLE `{timeline}` (
  `id`              int(10)         unsigned    NOT NULL    auto_increment,
  `uid`             int(10)         unsigned    NOT NULL,
  `type`            varchar(64)     NOT NULL    default '',
  `module`          varchar(64)     NOT NULL    default '',
  `message`         text,
  `link`            text,
  `time`            int(11)         unsigned    NOT NULL,

  PRIMARY KEY  (`id`)
);

Account fields and basic profile fields registry in user module config/profile.php

  • The entities will be inserted into profile_field table with type as account or profile
  • User account data and profile data for the fields will be inserted into account and profile respectively
    array(
        'account_field' => array(
            <field-name>    => array(
                // Edit element specs
                'edit'          => <form-element-filter-validator>,
                // Filter for value processing for output
                'filter'        => <output-filter>
                // Is editable by admin, default as true
                'is_edit'       => <bool>,
                // Display on user profile page, default as true
                'is_display'    => <bool>,
                // Search user by this field, default as true
                'is_search'     => <bool>,
            ),
            <...>,
        ),
        'profile_field' => array(
            <field-name>    => array(
                // Edit element specs
                'edit'          => <form-element-filter-validator>,
                // Filter for value processing for output
                'filter'        => <output-filter>
                // Is editable by admin, default as true
                'is_edit'       => <bool>,
                // Display on user profile page, default as true
                'is_display'    => <bool>,
                // Search user by this field, default as true
                'is_search'     => <bool>,
            ),
            <...>,
        ),
    ),

Custom profile field registry in a module config/user.php with key `profile_field

  • The entities will be inserted into profile_field table with:
    • Field name will be canonized by prefixing module name: -
    • Field type is set: type as custom
  • User profile data for active fields will be inserted into profile_entity table
    array(
        'profile_field' => array(
            <field-name>    => array(
                // Edit element specs
                'edit'          => <form-element-filter-validator>,
                // Filter for value processing for output
                'filter'        => <output-filter>
                // Is editable, default as true
                'is_edit'       => <bool>,
                // Display on user profile page, default as true
                'is_display'    => <bool>,
                // Search user by this field, default as true
                'is_search'     => <bool>,
            ),
            <...>,
        ),
    ),

Custom profile fields by user module

  • Load profile_field from extra/user/config/user.php if file available, otherwise
  • Load profile_field from module/user/config/user.php

User module API

API class

use Module\User\Api\User as UserApi;

Canonize meta data

  • Call by UserApi::canonizeMeta($rawData, $action = null)
  • Return array for account, profile, custom
$result = array(
    'account'   => array(),
    'profile'   => array(),
    'custom'    => array(),
);

foreach ($result as $type => &$data) {
    $fields = Pi::service('user')->getMeta($type, $action);
    foreach ($fields as $field) {
        if (in_array($field, $rawData)) {
            $data[] = $field;
        }
    }
}

return $result;

Canonize user data

  • Call by UserApi::canonizeUser($rawData)
  • Return associative array of account, profile, custom
$result = array(
    'account'   => array(),
    'profile'   => array(),
    'custom'    => array(),
);

foreach ($result as $type => &$data) {
    $fields = Pi::service('user')->getMeta($type);
    foreach ($fields as $field) {
        if (array_key_exists($field, $rawData)) {
            $data[$key][$field] = $rawData[$field];
        }
    }
}

return $result;

Add user

  • Call by UserApi::add(array $data) return <uid> 
$data = UserApi::canonizeUser($data);
$uid = UserApi::addAccount($data['account']);

$id = UserApi::addProfile($data['profile'], $uid);

$results = UserApi::addCustom($data['custom'], $uid);

return $uid;
  • Add user account UserApi::addAccount(array $account) return <uid> 
$row = Pi::model('account', 'user')->createRow($account);
$uid = $row->save();

return $uid;
  • Add user profile UserApi::addProfile(array $profile, $uid) return <profile-id> 
$profile['uid'] = $uid;
$row = Pi::model('profile', 'user')->createRow($profile);
$id = $row->save();

return $id;
  • Add user custom entities UserApi::addCustom($custom, $uid) return <bool[]> 
$result = array();
foreach (custom as $key => $value) {
    $id = UserApi::addEntity($key, $value, $uid);
    $result[$key] = $id;
}

return $result;
  • Add user custom entity UserApi::addEntity($name, $value, $uid) return <entity-id> 
$entity = array(
    'uid'   => $uid,
    'field' => $name,
    'value' => $value,
);
$row = Pi::model('custom', 'user')->createRow($entity);
$id = $row->save();

return $id;

Update user

  • Call by UserApi::update(array $data, $uid) return <bool> 
$data = UserApi::canonizeUser($data);
$status = UserApi::updateAccount($data['account'], $uid);
$status = UserApi::updateProfile($data['profile'], $uid);
$status = UserApi::updateCustom($data['custom'], $uid);

return $status;
  • Update user account UserApi::updateAccount(array $account, $uid) return <bool> 
$status = Pi::model('account', 'user')->update($account, array('id' => $uid));
  • Update user profile UserApi::updateProfile(array $profile, $uid) return <bool> 
$status = Pi::model('profile', 'user')->update($profile, array('uid' => $uid));
  • Update user custom profile UserApi::updateCustom(array $custom, $uid) return <bool> 
foreach (custom as $key => $value) {
    $status = UserApi::updateEntity($key, $value, $uid);
}
  • Update user custom entity UserApi::updateEntity($name, $value, $uid) return <bool> 
$status = Pi::model('profile_custom', 'user')->update(
    array('value' => $value),
    array(
        'field' => $name,
        'uid'   => $uid,
    )
);

Delete user

  • Call by UserApi::delete($uid) return <bool> 
$status = UserApi::deleteAccount($uid);
$status = UserApi::deleteProfile($uid);
$status = UserApi::deleteCustom($uid);

return $status;
  • Delete user account UserApi::deleteAccount($uid) return <bool> 
$status = Pi::model('account', 'user')->delete(array('id' => $uid));
  • Delete user profile UserApi::deleteProfile($uid) return <bool> 
$status = Pi::model('profile', 'user')->delete(array('uid' => $uid));
  • Delete user custom profile UserApi::deleteCustom($uid) return <bool> 
$status = Pi::model('custom', 'user')->delete(array('uid' => $uid));
  • Delete user custom entity UserApi::deleteEntity($name, $uid) return <bool> 
$status = Pi::model('profile_custom', 'user')->delete(
    array(
        'field' => $name,
        'uid'   => $uid,
    )
);

Get user data with specific fields

  • Call by UserApi::get($uid, $action = null, array $fields = null) return associative array
$account = array();
$profile = array();
$custom = array();

$account = UserApi::getAccount($uid, $action, $fields);
$profile = UserApi::getProfile($uid, $action, $fields);
$Custom = UserApi::getCustom($uid, $action, $fields);

$result = $account + $profile + $custom;
  • Get user account UserApi::getAccount($uid, $action = null, array $fields = null) 
$type = 'account';
$model = Pi::model('account', 'user');
$where = array('id' = $uid);

$result = array();
$columns = Pi::service('registry')->handler('profile', 'user')->read($type, $action);

if ($fields) {
    foreach (array_keys($columns) as $field) {
        if (!in_array($field, $fields)) {
            unset(columns[$field]);
        }
    }
}
if (!$columns) {
    return $result;
}
$select = $model->select()->where($where)->columns($columns);
$rowset = $model->selectWith($select);
$result = $rowset->current()->toArray();

if ('display' == $action) {
    foreach ($result as $key => &$value) {
        if ($columns[$key]['filter']) {
            $value = call_user_func($columns[$key]['filter'], $value);
        }
    }
}
  • Get user profile UserApi::getProfile($uid, $action = null, array $fields = null) 
$type = 'account';
$model = Pi::model('account', 'user');
$where = array('uid' = $uid);

// Same as account
<...>
  • Get user custom profile UserApi::getCustom($uid, $action = null, array $fields = null) 
$type = 'custom';
$model = Pi::model('custom', 'user');
$where = array('uid' = $uid);

$result = array();
$columns = Pi::service('registry')->handler('profile', 'user')->read($type, $action);

if ($fields) {
    foreach (array_keys($columns) as $field) {
        if (!in_array($field, $fields)) {
            unset(columns[$field]);
        }
    }
}
if (!$columns) {
    return $result;
}
$where['field'] = array_keys($columns);

$select = $model->select()->where($where)->columns($columns);
$rowset = $model->selectWith($select);
foreach ($rowset as $row) {
    $result[$row->field] = $row->value;
}

if ('display' == $action) {
    foreach ($result as $key => &$value) {
        if ($columns[$key]['filter']) {
            $value = call_user_func($columns[$key]['filter'], $value);
        }
    }
}

Get user profile field

  • Call by UserApi::getField($uid, $field, $action = '', $type = '') 
$columns = Pi::service('registry')->handler('profile', 'user')->read($type, $action);
if (!isset($columns[$field])) {
    return null;
}
$type = $type ?: $columns[$field]['type];
$method = 'get' . ucfirst($type);
$data = UserApi::{$method}($uid, (array) $field, $action);
if (isset($data[$field])) {
    $result = $data[$field];
} else {
    $result = null;
}

Cache registry

Load profile fields with type and action

  • Call by Pi::service('registry')->handler('profile', 'user')->read(<type>, <action>) return array of fields as string[]
  • Type: account, profile, custom
  • action: display, edit, search
$columns = array();
$where = array('active' => 1);
if ($type) {
    $where['type'] = $type;
}

switch ($action) {
    case 'display':
        $columns = array('name', 'title', 'filter');
        $where['is_display'] = 1;
        break;
    case 'edit':
        $columns = array('name', 'title', 'edit');
        $where['is_edit'] = 1;
        break;
    case 'search':
        $columns = array('name', 'title');
        $where['is_search'] = 1;
        break;
    default:
        break;
}
$select = Pi::model('profile_field', 'user')->select()->where($where);
if ($columns) {
    $select->columns($columns);
}
$rowset = Pi::model('profile_field', 'user')->selectWith($select);
foreach ($rowset as $row) {
    $result[$row->name] = $row->toArray();
}

Use cases

Load fields for edit by admin

$fields = Pi::service('registry')->handler('profile', 'user')->read('', 'edit');

Load fields for edit by user

$fields = Pi::service('registry')->handler('profile', 'user')->read('', 'edit');
foreach ($fields as $key => $data) {
    if (!$data['edit']) {
        unset($fields[$key]);
    }
}

Load full data of a user

$user = array(
    'uid'   => $id,
);

$registryService = Pi::service('registry')->handler('profile', 'user');
$fields = $registryService->read('account');
$row = Pi::model('account', 'user')->find($id);
$account = array(
    'id'    => $row->id,
);
foreach (array_keys($fields) as $key) {
    $account[$key] = $row->{$key};
}
$user += $account;

$fields = $registryService->read('profile');
$row = Pi::model('profile', 'user')->find($id);
$profile = array(
    'id'    => $row->id,
    'uid'   => $row->uid,
);
foreach (array_keys($fields) as $key) {
    $profile[$key] = $row->{$key};
}
$user += $profile;

$fields = $registryService->read('custom');
$rowset = Pi::model('custom', 'user')->select(array(
    'uid'   => $uid,
    'field' => array_keys($fields),
));
$custom = array(
    'uid'   => $row->uid,
);

foreach ($rowset as $row) {
    $custom[$row->field] = $row->value;
}
$user += $custom;

hiii 2

Add a custom sidebar
Clone this wiki locally