NAME

WWW::Suffit::AuthDB::Model - WWW::Suffit::AuthDB model class

SYNOPSIS

use WWW::Suffit::AuthDB::Model;

# SQLite
my $model = WWW::Suffit::AuthDB::Model->new(
    "sqlite:///tmp/test.db?RaiseError=0&PrintError=0&sqlite_unicode=1"
);

# MySQL
my $model = WWW::Suffit::AuthDB::Model->new(
    "mysql://user:pass@host/authdb?mysql_auto_reconnect=1&mysql_enable_utf8=1"
);

die($model->error) if $model->error;

DESCRIPTION

This module provides model methods

SQLITE DDL

CREATE TABLE IF NOT EXISTS "users" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "username"      CHAR(64) NOT NULL UNIQUE, -- User name
    "name"          CHAR(255) DEFAULT NULL, -- Full user name
    "email"         CHAR(255) DEFAULT NULL, -- Email address
    "password"      CHAR(255) NOT NULL, -- Password hash
    "algorithm"     CHAR(64) DEFAULT NULL, -- Password hash Algorithm (SHA256)
    "role"          CHAR(255) DEFAULT NULL, -- Role name
    "flags"         INTEGER DEFAULT 0, -- Flags
    "created"       INTEGER DEFAULT NULL, -- Created at
    "not_before"    INTEGER DEFAULT NULL, -- Not Before
    "not_after"     INTEGER DEFAULT NULL, -- Not After
    "public_key"    TEXT DEFAULT NULL, -- Public Key (RSA/X509)
    "private_key"   TEXT DEFAULT NULL, -- Private Key (RSA/X509)
    "attributes"    TEXT DEFAULT NULL, -- Attributes (JSON)
    "comment"       TEXT DEFAULT NULL -- Comment
);
CREATE TABLE IF NOT EXISTS "groups" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "groupname"     CHAR(64) NOT NULL UNIQUE, -- Group name
    "description"   TEXT DEFAULT NULL -- Description
);
CREATE TABLE IF NOT EXISTS "realms" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "realmname"     CHAR(64) NOT NULL UNIQUE, -- Realm name
    "realm"         CHAR(255) DEFAULT NULL, -- Realm string
    "satisfy"       CHAR(16) DEFAULT NULL, -- The satisfy policy (All, Any)
    "description"   TEXT DEFAULT NULL -- Description
);
CREATE TABLE IF NOT EXISTS "routes" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "realmname"     CHAR(64) DEFAULT NULL, -- Realm name
    "routename"     CHAR(64) DEFAULT NULL, -- Route name
    "method"        CHAR(16) DEFAULT NULL, -- HTTP method (ANY, GET, POST, ...)
    "url"           CHAR(255) DEFAULT NULL, -- URL
    "base"          CHAR(255) DEFAULT NULL, -- Base URL
    "path"          CHAR(255) DEFAULT NULL -- Path of URL (pattern)
);
CREATE TABLE IF NOT EXISTS "requirements" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "realmname"     CHAR(64) DEFAULT NULL, -- Realm name
    "provider"      CHAR(64) DEFAULT NULL, -- Provider name (user,group,ip and etc.)
    "entity"        CHAR(64) DEFAULT NULL, -- Entity (operand of expression)
    "op"            CHAR(2) DEFAULT NULL, -- Comparison Operator
    "value"         CHAR(255) DEFAULT NULL -- Test value
);
CREATE TABLE IF NOT EXISTS "grpsusrs" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "groupname"     CHAR(64) DEFAULT NULL, -- Group name
    "username"      CHAR(64) DEFAULT NULL -- User name
);
CREATE TABLE IF NOT EXISTS "meta" (
    "key"           CHAR(255) NOT NULL UNIQUE PRIMARY KEY,
    "value"         TEXT DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS "stats" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "address"       CHAR(40) DEFAULT NULL, -- IPv4/IPv6 client address
    "username"      CHAR(64) DEFAULT NULL, -- User name
    "dismiss"       INTEGER DEFAULT 0, -- Dismissal count
    "updated"       INTEGER DEFAULT NULL -- Update date
);
CREATE TABLE IF NOT EXISTS "tokens" (
    "id"            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "jti"           CHAR(32) DEFAULT NULL, -- Request ID
    "username"      CHAR(64) DEFAULT NULL, -- User name
    "type"          CHAR(20) DEFAULT NULL, -- Token type (session, refresh, api)
    "clientid"      CAHR(32) DEFAULT NULL, -- Clientid as md5 (User-Agent . Remote-Address)
    "iat"           INTEGER DEFAULT NULL, -- Issue time
    "exp"           INTEGER DEFAULT NULL, -- Expiration time
    "address"       CAHR(40) DEFAULT NULL, -- IPv4/IPv6 client address
    "description"   TEXT DEFAULT NULL -- Description
);

MYSQL DDL

CREATE DATABASE `authdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;
CREATE TABLE IF NOT EXISTS `users` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `username`      VARCHAR(64) NOT NULL, -- User name
    `name`          VARCHAR(255) DEFAULT NULL, -- Full user name
    `email`         VARCHAR(255) DEFAULT NULL, -- Email address
    `password`      VARCHAR(255) NOT NULL, -- Password hash
    `algorithm`     VARCHAR(64) DEFAULT NULL, -- Password hash Algorithm (SHA256)
    `role`          VARCHAR(255) DEFAULT NULL, -- Role name
    `flags`         INT(11) DEFAULT 0, -- Flags
    `created`       INT(11) DEFAULT NULL, -- Created at
    `not_before`    INT(11) DEFAULT NULL, -- Not Before
    `not_after`     INT(11) DEFAULT NULL, -- Not After
    `public_key`    TEXT DEFAULT NULL, -- Public Key (RSA/X509)
    `private_key`   TEXT DEFAULT NULL, -- Private Key (RSA/X509)
    `attributes`    TEXT DEFAULT NULL, -- Attributes (JSON)
    `comment`       TEXT DEFAULT NULL, -- Comment
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `groups` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `groupname`     VARCHAR(64) NOT NULL, -- Group name
    `description`   TEXT DEFAULT NULL, -- Description
    PRIMARY KEY (`id`),
    UNIQUE KEY `groupname` (`groupname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `realms` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `realmname`     VARCHAR(64) NOT NULL, -- Realm name
    `realm`         VARCHAR(255) DEFAULT NULL, -- Realm string
    `satisfy`       VARCHAR(16) DEFAULT NULL, -- The satisfy policy (All, Any)
    `description`   TEXT DEFAULT NULL, -- Description
    PRIMARY KEY (`id`),
    UNIQUE KEY `realmname` (`realmname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `routes` (
    `id`            INT NOT NULL AUTO_INCREMENT,
    `realmname`     VARCHAR(64) DEFAULT NULL, -- Realm name
    `routename`     VARCHAR(64) DEFAULT NULL, -- Route name
    `method`        VARCHAR(16) DEFAULT NULL, -- HTTP method (ANY, GET, POST, ...)
    `url`           VARCHAR(255) DEFAULT NULL, -- URL
    `base`          VARCHAR(255) DEFAULT NULL, -- Base URL
    `path`          VARCHAR(255) DEFAULT NULL, -- Path of URL (pattern)
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `requirements` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `realmname`     VARCHAR(64) DEFAULT NULL, -- Realm name
    `provider`      VARCHAR(64) DEFAULT NULL, -- Provider name (user,group,ip and etc.)
    `entity`        VARCHAR(64) DEFAULT NULL, -- Entity (operand of expression)
    `op`            VARCHAR(2) DEFAULT NULL, -- Comparison Operator
    `value`         VARCHAR(255) DEFAULT NULL, -- Test value
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `grpsusrs` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `groupname`     VARCHAR(64) DEFAULT NULL, -- Group name
    `username`      VARCHAR(64) DEFAULT NULL, -- User name
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `meta` (
    `key`           VARCHAR(255) NOT NULL,
    `value`         TEXT DEFAULT NULL,
    PRIMARY KEY (`key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `stats` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `address`       VARCHAR(40) DEFAULT NULL, -- IPv4/IPv6 client address
    `username`      VARCHAR(64) DEFAULT NULL, -- User name
    `dismiss`       INT(11) DEFAULT 0, -- Dismissal count
    `updated`       INT(11) DEFAULT NULL, -- Update date
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE IF NOT EXISTS `tokens` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `jti`           VARCHAR(32) DEFAULT NULL, -- Request ID
    `username`      VARCHAR(64) DEFAULT NULL, -- User name
    `type`          VARCHAR(20) DEFAULT NULL, -- Token type (session, refresh, api)
    `clientid`      VARCHAR(32) DEFAULT NULL, -- Clientid as md5 (User-Agent . Remote-Address)
    `iat`           INT(11) DEFAULT NULL, -- Issue time
    `exp`           INT(11) DEFAULT NULL, -- Expiration time
    `address`       VARCHAR(40) DEFAULT NULL, -- IPv4/IPv6 client address
    `description`   TEXT DEFAULT NULL, -- Description
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

METHODS

This class inherits all methods from Acrux::DBI and implements the following new ones

new

my $model = WWW::Suffit::AuthDB::Model->new(
    "sqlite:///tmp/test.db?sqlite_unicode=1"
);

Create DBI object. See also Acrux::DBI

group_add

$model->group_add(
    groupname   => "wheel",
    description => "This administrator group added by default",
) or die($model->error);

Add new group recored

group_del

$model->group_del("wheel") or die($model->error);

Delete record by groupname

group_get

my %data = $model->group_get("wheel");

Returns data from database by groupname

group_getall

my @table = $model->group_getall();

Returns pure data from database

group_members

my @members = $model->group_members( "wheel" );

Returns members of specified group

group_set

$model->group_set(
    username    => "wheel",
    description => "This administrator group added by default",
) or die($model->error);

Update recored by groupname

grpusr_add

$model->grpusr_add(
    groupname   => "wheel",
    username    => "root",
) or die($model->error);

Add the user to the group

grpusr_del

$model->grpusr_del( id => 123 ) or die($model->error);
$model->grpusr_del( groupname => "wheel" ) or die($model->error);
$model->grpusr_del( username => "root" ) or die($model->error);

Delete members from groups by id, groupname or username

grpusr_get

my %data = $model->grpusr_get( id => 123 );
my @table = $model->grpusr_get( groupname => "wheel");
my @table = $model->grpusr_get( username => "root" );

Returns members of groups by id, groupname or username

initialize

$model = $model->initialize;

This method initializes DB schema before start using

is_initialized

print "Database is inialized" if $model->is_initialized;

This method checks of the schema initialization status

is_mariadb

print $model->is_mariadb ? "Is MariaDB" : "Is NOT MariaDB"

Returns true if type of current database is MariaDB

is_mysql

print $model->is_mysql ? "Is MySQL" : "Is NOT MySQL"

Returns true if type of current database is MySQL or MariaDB

is_oracle

print $model->is_oracle ? "Is Oracle" : "Is NOT Oracle"

Returns true if type of current database is Oracle

is_postgresql

print $model->is_postgresql ? "Is PostgreSQL" : "Is NOT PostgreSQL"

Returns true if type of current database is PostgreSQL

is_sqlite

print $model->is_sqlite ? "Is SQLite" : "Is NOT SQLite"

Returns true if type of current database is SQLite

meta_del

$model->meta_del("key") or die($model->error);
$model->meta_set(key => "foo") or die($model->error);

Delete record by key

meta_get

my %data = $model->meta_get("key");

Returns pair - key and value

my @table = $model->meta_get();

Returns all data from meta table

meta_set

$model->meta_set(key => "foo", value => "test") or die($model->error);

Set pair - key and value

$model->meta_set(key => "foo") or die($model->error);

Delete record by key

realm_add

$model->realm_add(
    realmname   => "root",
    realm       => "Root pages",
    satisfy     => "Any",
    description => "Index page",
) or die($model->error);

Add new realm recored

realm_del

$model->realm_del("root") or die($model->error);

Delete record by realmname

realm_get

my %data = $model->realm_get("root");

Returns data from database by realmname

realm_getall

my @table = $model->realm_getall();

Returns pure data from database

realm_requirement_add

$model->realm_requirement_add(
    realmname   => "root",
    provider    => "user",
    entity      => "username",
    op          => "eq",
    value       => "admin",
) or die($model->error);

Add the new requirement

realm_requirement_del

$model->realm_requirement_del("default") or die($model->error);

Delete requirements by realmname

realm_requirements

my @table = $model->realm_requirements("default");

Returns realm's requirements from database by realmname

realm_routes

my @table = $model->realm_routes( "realmname" );

Returns realm's routes from database by realmname

realm_set

$model->realm_set(
    realmname   => "root",
    realm       => "Root pages",
    satisfy     => "Any",
    description => "Index page (modified)",
) or die($model->error);

Update recored by realmname

route_add

$model->route_add(
    realmname   => "root",
    routename   => "root",
    method      => "GET",
    url         => "https://localhost:8695/foo/bar",
    base        => "https://localhost:8695/",
    path        => "/foo/bar",
) or die($model->error);

Add the new route to realm

route_assign

$model->route_add(
    realmname   => "default",
    routename   => "index",
) or die($model->error);

Assignees the realm for route by routename

route_del

$model->route_del(123) or die($model->error);

Delete record by id

$model->route_del("root") or die($model->error);

Delete record by realmname

route_get

my %data = $model->route_get(123);

Returns data from database by id

my @table = $model->route_get("root");

Returns data from database by realmname

route_getall

my @table = $model->route_getall();

Returns pure data from database

route_release

$model->route_release("default") or die($model->error);

Releases the route (removes relation with realm) by realmname

route_search

my @routes = $model->route_search( "ind" );

Performs search route by specified fragment and returns list of found routes

route_set

$model->route_set(
    id          => 123,
    realmname   => "root",
    routename   => "root",
    method      => "POST",
    url         => "https://localhost:8695",
    base        => "https://localhost:8695/",
    path        => "/foo/bar",
) or die($model->error);

Update record by id

stat_get

my %st = $model->stat_get($address, $username);

Returns the user statistic information by address and username

stat_set

$model->stat_set(
    address     => $address,
    username    => $username,
    dismiss     => 1,
    updated     => time,
) or die($model->error);

Sets the user statistic information by address and username

token_add

$model->token_add(
    type        => 'api',
    jti         => $jti,
    username    => $username,
    clientid    => 'qwertyuiqwertyui',
    iat         => time,
    exp         => time + 3600,
    address     => '127.0.0.1',
    description => "My API token",
) or die($model->error);

Add new token for user

token_del

$model->token_del( 123 ) or die($model->error);

Delete token by id

$model->token_del() or die($model->error);

Delete all expired tokens

token_get

my %data = $model->token_get( 123 );

Returns data from database by id

token_get_cond

my %data = $model->token_get_cond('api', username => $username, jti => $jti);
my %data = $model->token_get_cond('session', username => $username, clientid => $clientid);

Returns data from database by id jti or clientid

token_getall

my @table = $model->token_getall();

Returns all tokens

token_set

$model->token_set(
    id          => 123,
    type        => 'api',
    jti         => $jti,
    username    => $username,
    clientid    => 'qwertyuiqwertyui',
    iat         => time,
    exp         => time + 3600,
    address     => '127.0.0.1',
    description => "My API token",
) or die($model->error);

Update token by id

user_add

$model->user_add(
    username    => "admin",
    name        => "Administrator",
    email       => 'root@localhost',
    password    => "8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918",
    algorithm   => "SHA256",
    role        => "System administrator",
    flags       => 0,
    created     => time(),
    not_before  => time(),
    not_after   => undef,
    public_key  => "",
    private_key => "",
    attributes  => qq/{"disabled": 0}/,
    comment     => "This user added by default",
) or die($model->error);

Add new user recored

user_del

$model->user_del("admin") or die($model->error);

Delete record by username

user_edit

$model->user_edit(
    id          => 123,
    username    => $username,
    comment     => $comment,
    email       => $email,
    name        => $name,
    role        => $role,
) or die($model->error);

Edit user data by id

user_get

my %data = $model->user_get("admin");

Returns data from database by username

user_getall

my @table = $model->user_getall();

Returns pure data from database (array of hashes)

user_groups

my @groups = $model->user_groups( "admin" );

Returns groups of specified user

user_passwd

$model->user_passwd(
    username    => "admin",
    password    => "8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918",
) or die($model->error);

Changes password for user

user_search

my @users = $model->user_search( "ad" );

Performs search user by specified fragment and returns list of found users

user_set

$model->user_set(
    username    => "admin",
    name        => "Administrator",
    email       => 'root@localhost',
    password    => "8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918",
    algorithm   => "SHA256",
    role        => "System administrator",
    flags       => 0,
    not_before  => time(),
    not_after   => undef,
    public_key  => "",
    private_key => "",
    attributes  => qq/{"disabled": 0}/,
    comment     => "This user added by default",
) or die($model->error);

Update recored by username

user_setkeys

$model->user_setkeys(
    id          => 123,
    public_key  => $public_key,
    private_key => $private_key,
) or die($model->error);

Sets keys to user's data

user_tokens

my @table = $model->user_tokens($username);

Returns all tokens for user

HISTORY

See Changes file

TO DO

See TODO file

AUTHOR

Serż Minus (Sergey Lepenkov) https://www.serzik.com <abalama@cpan.org>

COPYRIGHT

LICENSE

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

See LICENSE file and https://dev.perl.org/licenses/

To install WWW::Suffit::AuthDB, copy and paste the appropriate command in to your terminal.

cpanm

cpanm WWW::Suffit::AuthDB

CPAN shell

perl -MCPAN -e shell
install WWW::Suffit::AuthDB

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

SQLITE DDL

MYSQL DDL

METHODS

new

group_add

group_del

group_get

group_getall

group_members

group_set

grpusr_add

grpusr_del

grpusr_get

initialize

is_initialized

is_mariadb

is_mysql

is_oracle

is_postgresql

is_sqlite

meta_del

meta_get

meta_set

realm_add

realm_del

realm_get

realm_getall

realm_requirement_add

realm_requirement_del

realm_requirements

realm_routes

realm_set

route_add

route_assign

route_del

route_get

route_getall

route_release

route_search

route_set

stat_get

stat_set

token_add

token_del

token_get

token_get_cond

token_getall

token_set

user_add

user_del

user_edit

user_get

user_getall

user_groups

user_passwd

user_search

user_set

user_setkeys

user_tokens

HISTORY

TO DO

SEE ALSO

AUTHOR

COPYRIGHT

LICENSE

Module Install Instructions