WWW-Suffit-AuthDB
view release on metacpan or search on metacpan
lib/WWW/Suffit/AuthDB/Model.pm view on Meta::CPAN
package WWW::Suffit::AuthDB::Model;
use strict;
use utf8;
=encoding utf8
=head1 NAME
WWW::Suffit::AuthDB::Model - WWW::Suffit::AuthDB model class
=head1 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;
=head1 DESCRIPTION
This module provides model methods
=head2 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
);
=head2 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` (
lib/WWW/Suffit/AuthDB/Model.pm view on Meta::CPAN
=head2 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
=head2 token_get
my %data = $model->token_get( 123 );
Returns data from database by id
=head2 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
=head2 token_getall
my @table = $model->token_getall();
Returns all tokens
=head2 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
=head2 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
=head2 user_del
$model->user_del("admin") or die($model->error);
Delete record by username
=head2 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
=head2 user_get
my %data = $model->user_get("admin");
Returns data from database by username
=head2 user_getall
my @table = $model->user_getall();
Returns pure data from database (array of hashes)
=head2 user_groups
my @groups = $model->user_groups( "admin" );
Returns groups of specified user
=head2 user_passwd
$model->user_passwd(
username => "admin",
password => "8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918",
) or die($model->error);
Changes password for user
=head2 user_search
my @users = $model->user_search( "ad" );
Performs search user by specified fragment and returns list of found users
=head2 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
=head2 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
=head2 user_tokens
my @table = $model->user_tokens($username);
Returns all tokens for user
=head1 HISTORY
See C<Changes> file
=head1 TO DO
See C<TODO> file
=head1 SEE ALSO
L<WWW::Suffit::AuthDB>, L<Acrux::DBI>
=head1 AUTHOR
Serż Minus (Sergey Lepenkov) L<https://www.serzik.com> E<lt>abalama@cpan.orgE<gt>
=head1 COPYRIGHT
Copyright (C) 1998-2026 D&D Corporation
=head1 LICENSE
This program is distributed under the terms of the Artistic License Version 2.0
See the C<LICENSE> file or L<https://opensource.org/license/artistic-2-0> for details
=cut
use parent 'Acrux::DBI';
use Acrux::Util qw/ touch /;
use Acrux::RefUtil qw/ is_integer is_array_ref is_hash_ref isnt_void /;
our $VERSION = '1.01';
our $DEBUG //= !!$ENV{WWW_SUFFIT_AUTHDB_MODEL_DEBUG};
use constant {
DEFAULT_ALGORITHM => 'SHA256',
SCHEMA_NAME => 'authdb',
SCHEMA_SECTION_FORMAT => 'schema_%s',
SCHEMA_PATCHES => {
# version => label
'0.01' => 'initial', # Initial version
'1.00' => 'v100',
'1.01' => 'v101',
},
};
# Meta DMLs
use constant DML_META_ADD => <<'DML';
INSERT INTO `meta`
(`key`,`value`)
VALUES
(?,?)
DML
use constant DML_META_GET => <<'DML';
SELECT `key`,`value`
FROM `meta`
WHERE `key` = ?
DML
use constant DML_META_GETALL => <<'DML';
SELECT `key`,`value`
FROM `meta`
ORDER BY `key` ASC
DML
use constant DML_META_SET => <<'DML';
UPDATE `meta`
SET `value` = ?
WHERE `key` = ?
DML
use constant DML_META_DEL => <<DML;
DELETE FROM `meta`
WHERE `key` = ?
DML
# Stat DMLs
use constant DML_STAT_GET => <<'DML';
SELECT `id`,`address`,`username`,`dismiss`,`updated`
FROM `stats`
WHERE `address` = ? AND `username` = ?
DML
use constant DML_STAT_ADD => <<'DML';
INSERT INTO `stats` (`address`,`username`,`dismiss`,`updated`)
VALUES (?,?,?,?)
DML
use constant DML_STAT_SET => <<'DML';
UPDATE `stats`
SET `address` = ?, `username` =?, `dismiss` = ?, `updated` = ?
WHERE `id` = ?
DML
# User DMLs
use constant DML_USER_ADD => <<'DML';
INSERT INTO `users`
(`username`,`name`,`email`,`password`,`algorithm`,`role`,`flags`,`created`,
`not_before`,`not_after`,`public_key`,`private_key`,`attributes`,`comment`
)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
DML
use constant DML_USER_GET => <<'DML';
SELECT `id`,`username`,`name`,`email`,`password`,`algorithm`,`role`,`flags`,`created`,
`not_before`,`not_after`,`public_key`,`private_key`,`attributes`,`comment`
FROM `users`
WHERE `username` = ?
DML
use constant DML_USER_SET => <<'DML';
UPDATE `users`
SET `name` = ?, `email` = ?, `password` = ?, `algorithm` = ?, `role` = ?, `flags` = ?,
`not_before` = ?, `not_after` = ?, `public_key` = ?, `private_key` = ?,
`attributes` = ?, `comment` = ?
WHERE `username` = ?
DML
use constant DML_USER_DEL => <<'DML';
DELETE FROM `users` WHERE `username` = ?
DML
use constant DML_USER_GETALL => <<'DML';
SELECT `id`,`username`,`name`,`email`,`password`,`algorithm`,`role`,`flags`,`created`,
`not_before`,`not_after`,`public_key`,`private_key`,`attributes`,`comment`
FROM `users`
ORDER BY `username` ASC
DML
use constant DML_PASSWD => <<'DML';
UPDATE `users`
SET `password` = ?
WHERE `username` = ?
DML
use constant DML_USER_SEARCH => <<'DML';
SELECT `id`,`username`,`name`,`role`
FROM `users`
WHERE 1 = 1
%s
ORDER BY `username` ASC
LIMIT 10
DML
use constant DML_USER_GROUPS => <<'DML';
SELECT
groups.`id` AS `id`,
groups.`groupname` AS `groupname`,
groups.`description` AS `description`
FROM
grpsusrs
LEFT OUTER JOIN groups ON (groups.`groupname` = grpsusrs.`groupname`)
WHERE 1 = 1
AND grpsusrs.`username` = ?
ORDER BY
grpsusrs.`groupname` ASC
DML
use constant DML_USER_EDIT => <<'DML';
UPDATE `users`
SET `name` = ?, `email` = ?, `role` = ?, `comment` = ?
WHERE `id` = ?
DML
use constant DML_USER_SETKEYS => <<'DML';
UPDATE `users`
SET `public_key` = ?, `private_key` = ?
WHERE `id` = ?
DML
# Group DMLs
use constant DML_GROUP_ADD => <<'DML';
INSERT INTO `groups` (`groupname`,`description`)
VALUES (?,?)
DML
use constant DML_GROUP_GET => <<'DML';
SELECT `id`,`groupname`,`description`
FROM `groups`
WHERE `groupname` = ?
DML
use constant DML_GROUP_SET => <<'DML';
UPDATE `groups`
SET `description` = ?
WHERE `groupname` = ?
DML
use constant DML_GROUP_DEL => <<'DML';
DELETE FROM `groups` WHERE `groupname` = ?
DML
use constant DML_GROUP_GETALL => <<'DML';
SELECT `id`,`groupname`,`description`
FROM `groups`
ORDER BY `groupname` ASC
DML
use constant DML_GROUP_MEMBERS => <<'DML';
SELECT
users.`id` AS `id`,
users.`username` AS `username`,
users.`name` AS `name`,
users.`role` AS `role`
FROM
users
LEFT OUTER JOIN grpsusrs ON (grpsusrs.`username` = users.`username`)
WHERE 1 = 1
AND grpsusrs.`groupname` = ?
ORDER BY
grpsusrs.`username` ASC
DML
# Group-User DMLs
use constant DML_GRPUSR_ADD => <<'DML';
INSERT INTO `grpsusrs`
(`groupname`,`username`)
VALUES
(?,?)
DML
use constant DML_GRPUSR_GET_BY_ID => <<'DML';
SELECT `id`,`groupname`,`username`
FROM `grpsusrs`
WHERE `id` = ?
DML
use constant DML_GRPUSR_GET_BY_GROUP_USER => <<'DML';
SELECT `id`,`groupname`,`username`
FROM `grpsusrs`
WHERE `groupname` = ? AND `username` = ?
DML
use constant DML_GRPUSR_GET_BY_GROUP => <<'DML';
SELECT `id`,`groupname`,`username`
lib/WWW/Suffit/AuthDB/Model.pm view on Meta::CPAN
unless (defined($data{username}) && length($data{username})) {
$self->error("No username specified");
return 0;
}
# Get existed data
my %cur = $self->stat_get($data{address}, $data{username});
return 0 if $self->error;
# Add/Update
if ($cur{id}) {
# Set (update)
$self->query(DML_STAT_SET, $data{address}, $data{username},
$data{dismiss} || 0, $data{updated} || time, $cur{id}
) or return 0;
} else {
# Add (insert)
$self->query(DML_STAT_ADD, $data{address}, $data{username},
$data{dismiss} || 0, $data{updated} || time
) or return 0;
}
# Ok
return 1;
}
sub stat_get {
my $self = shift;
my $address = shift // '';
my $username = shift // '';
return () unless $self->ping;
unless (length($address)) {
$self->error("No address specified");
return ();
}
unless (length($username)) {
$self->error("No username specified");
return ();
}
# Get data
if (my $res = $self->query(DML_STAT_GET, $address, $username)) {
my $r = $res->hash;
return (%$r) if is_hash_ref($r);
}
return ();
}
# User CRUDs
sub user_add {
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
# Add
$self->query(DML_USER_ADD,
$data{username}, $data{name}, $data{email}, $data{password},
uc($data{algorithm} || DEFAULT_ALGORITHM), $data{role}, $data{flags},
$data{created} || time(), $data{not_before} || time(), $data{not_after},
$data{public_key}, $data{private_key}, $data{attributes},
$data{comment},
) or return 0;
# Ok
return 1;
}
sub user_set { # set by username
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
unless (length($data{username} // '')) {
$self->error("No username specified");
return 0;
}
# Set
$self->query(DML_USER_SET,
$data{name}, $data{email}, $data{password},
uc($data{algorithm} || DEFAULT_ALGORITHM), $data{role}, $data{flags},
$data{not_before} || time(), $data{not_after},
$data{public_key}, $data{private_key}, $data{attributes},
$data{comment},
$data{username},
) or return 0;
# Ok
return 1;
}
sub user_edit { # set by id
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
unless ($data{id}) {
$self->error("No id of user specified");
return 0;
}
# Set
$self->query(DML_USER_EDIT,
$data{name}, $data{email}, $data{role}, $data{comment},
$data{id},
) or return 0;
# Ok
return 1;
}
sub user_del {
my $self = shift;
my $username = shift // '';
return 0 unless $self->ping;
unless (length($username)) {
$self->error("No username specified");
return 0;
}
# Del
$self->query(DML_USER_DEL, $username) or return 0;
# Ok
return 1;
}
sub user_get {
my $self = shift;
my $username = shift // '';
return () unless $self->ping;
unless (length $username) {
$self->error("No username specified");
return ();
}
# Get data
if (my $res = $self->query(DML_USER_GET, $username)) {
my $r = $res->hash;
return (%$r) if is_hash_ref($r);
}
return ();
}
sub user_getall {
my $self = shift;
return () unless $self->ping;
lib/WWW/Suffit/AuthDB/Model.pm view on Meta::CPAN
}
sub user_groups {
my $self = shift;
my $username = shift // '';
return () unless $self->ping;
unless (length $username) {
$self->error("No username specified");
return ();
}
# Get data
if (my $res = $self->query(DML_USER_GROUPS, $username)) {
my $r = $res->hashes;
return (@$r) if is_array_ref($r);
}
return ();
}
sub user_tokens {
my $self = shift;
my $username = shift // '';
return () unless $self->ping;
unless (length $username) {
$self->error("No username specified");
return ();
}
# Get data
if (my $res = $self->query(DML_TOKEN_GET_BY_USERNAME, $username)) {
my $r = $res->hashes;
return (@$r) if is_array_ref($r);
}
return ();
}
sub user_passwd {
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
unless (length($data{username} // '')) {
$self->error("No username specified");
return 0;
}
# Passwd
$self->query(DML_PASSWD, $data{password}, $data{username}) or return 0;
# Ok
return 1;
}
sub user_setkeys {
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
unless ($data{id}) {
$self->error("No id of user specified");
return 0;
}
# Set
$self->query(DML_USER_SETKEYS, $data{public_key}, $data{private_key}, $data{id}) or return 0;
# Ok
return 1;
}
# Group CRUDs
sub group_add {
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
# Add
$self->query(DML_GROUP_ADD, $data{groupname}, $data{description}) or return 0;
# Ok
return 1;
}
sub group_set {
my $self = shift;
my %data = @_;
return 0 unless $self->ping;
unless (length($data{groupname} // '')) {
$self->error("No groupname specified");
return 0;
}
# Set
$self->query(DML_GROUP_SET, $data{description}, $data{groupname}) or return 0;
# Ok
return 1;
}
sub group_del {
my $self = shift;
my $groupname = shift // '';
return 0 unless $self->ping;
unless (length($groupname)) {
$self->error("No groupname specified");
return 0;
}
# Del
$self->query(DML_GROUP_DEL, $groupname) or return 0;
# Ok
return 1;
}
sub group_get {
my $self = shift;
my $groupname = shift // '';
return () unless $self->ping;
unless (length $groupname) {
$self->error("No groupname specified");
return ();
}
# Get data
if (my $res = $self->query(DML_GROUP_GET, $groupname)) {
my $r = $res->hash;
return (%$r) if is_hash_ref($r);
lib/WWW/Suffit/AuthDB/Model.pm view on Meta::CPAN
sub token_get_cond {
my $self = shift;
my $cond = shift // '';
my %data = @_;
return () unless $self->ping;
my $res;
# Username and ClientID
if ($cond eq 'session') { # username and clinetid
$res = $self->query(DML_TOKEN_GET_BY_USERNAME_AND_CLIENTID, $data{username}, $data{clientid});
} elsif ($cond eq 'api') { # username and jti
$res = $self->query(DML_TOKEN_GET_BY_USERNAME_AND_JTI, $data{username}, $data{jti});
} else {
$self->error("No any conditions specified");
return ();
}
# Result
if ($res) {
my $r = $res->hash;
return (%$r) if is_hash_ref($r);
}
return ();
}
sub _get_patches {
my $self = shift;
my $from = shift // $VERSION; # start from version
my $patches = SCHEMA_PATCHES;
my @labels = ();
foreach my $v (sort keys %$patches) {
push @labels, $patches->{$v} if ($v * 1) > ($from * 1);
}
return [@labels];
}
1;
# !! Not forget add any new patch label to SCHEMA_PATCHES !!
__DATA__
@@ schema_sqlite
-- # main
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 "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 "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 "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 "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" CHAR(32) DEFAULT NULL, -- Clientid as md5 (User-Agent . Remote-Address)
"iat" INTEGER DEFAULT NULL, -- Issue time
"exp" INTEGER DEFAULT NULL, -- Expiration time
"address" CHAR(40) DEFAULT NULL -- IPv4/IPv6 client address
) ;
CREATE TABLE IF NOT EXISTS "meta" (
"key" CHAR(255) NOT NULL UNIQUE PRIMARY KEY,
"value" TEXT DEFAULT NULL
)
-- # initial
INSERT INTO `meta` (`key`,`value`) VALUES ("schema.version", "0.01")
-- # v101
ALTER TABLE "tokens" ADD COLUMN "description" TEXT DEFAULT NULL;
UPDATE `meta` SET `value` = "1.01" WHERE `key` = "schema.version"
@@ schema_mysql
-- # main
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 `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;
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;
-- # initial
INSERT INTO `meta` (`key`,`value`) VALUES ("schema.version", "0.01")
-- # v101
ALTER TABLE `tokens` ADD COLUMN `description` TEXT DEFAULT NULL;
UPDATE `meta` SET `value` = "1.01" WHERE `key` = "schema.version"
@@ schema_postgresql
-- # main
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
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 DEFAULT 0, -- Flags
created INT DEFAULT NULL, -- Created at
not_before INT DEFAULT NULL, -- Not Before
not_after INT 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),
CONSTRAINT username UNIQUE (username)
) ;
CREATE TABLE IF NOT EXISTS groups (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
groupname VARCHAR(64) NOT NULL, -- Group name
description TEXT DEFAULT NULL, -- Description
PRIMARY KEY (id),
CONSTRAINT groupname UNIQUE (groupname)
) ;
CREATE TABLE IF NOT EXISTS realms (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
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),
CONSTRAINT realmname UNIQUE (realmname)
) ;
CREATE TABLE IF NOT EXISTS routes (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
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)
) ;
CREATE TABLE IF NOT EXISTS requirements (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
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)
) ;
CREATE TABLE IF NOT EXISTS grpsusrs (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
groupname VARCHAR(64) DEFAULT NULL, -- Group name
username VARCHAR(64) DEFAULT NULL, -- User name
PRIMARY KEY (id)
) ;
CREATE TABLE IF NOT EXISTS stats (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
address VARCHAR(40) DEFAULT NULL, -- IPv4/IPv6 client address
username VARCHAR(64) DEFAULT NULL, -- User name
dismiss INT DEFAULT 0, -- Dismissal count
updated INT DEFAULT NULL, -- Update date
PRIMARY KEY (id)
) ;
CREATE TABLE IF NOT EXISTS tokens (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
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)
( run in 1.069 second using v1.01-cache-2.11-cpan-39bf76dae61 )