NAME

WWW::Suffit::AuthDB::Model - WWW::Suffit::AuthDB model (store) class

SYNOPSIS

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

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

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

    die($model->error) unless $model->status;

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
    );

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
        PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

METHODS

new

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

Creates DBI object

error

    my $error = $model->error;

Returns error message

    my $status = $model->error( "Error message" );

Sets error message if argument is provided. This method in "set" context returns status of the operation as status() method.

dbi

    my $dbi = $model->dbi;

Returns CTK::DBI object of current database connection

dsn

    my $dsn = $model->dsn;

Returns DSN string of current database connection

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

init

Initialize DB instance. This method for internal use only

is_mysql

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

Returns true if type of current database is MySQL

is_oracle

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

Returns true if type of current database is Oracle

is_pg

    print $model->is_pg ? "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);

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 => "value") or die($model->error);

Set pair - key and value

ping

    $model->ping ? 'OK' : 'Database session is expired';

Checks the connection to database

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

reconnect

    $model->reconnect;

This method performs reconnecting to database and returns model object

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_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_release

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

Releases the route (removes relation with realm) by realmname

route_assign

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

Assignees the realm for route by routename

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_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 statistic information by address and username

stat_set

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

Sets statistic information by address and username

status

    my $status = $model->status;
    my $status = $model->status( 1 ); # Sets the status value and returns it

Gets or sets the BOOL status of the operation

token_add

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

Adds new token for user

token_del

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

Delete record 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',
    ) or die($model->error);

Update record 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 hash)

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

error

dbi

dsn

group_add

group_del

group_get

group_getall

group_members

group_set

grpusr_add

grpusr_del

grpusr_get

init

is_mysql

is_oracle

is_pg

is_sqlite

meta_del

meta_get

meta_set

ping

realm_add

realm_del

realm_get

realm_getall

realm_requirement_add

realm_requirement_del

realm_requirements

realm_routes

realm_set

reconnect

route_add

route_del

route_release

route_assign

route_get

route_getall

route_search

route_set

stat_get

stat_set

status

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