USERS Module

Database

SQL script

In order to use USERS module, you need to have a MySQL database and the following tables in it:

-- ----------------------------------------------------------------------------
-- User tables
-- ----------------------------------------------------------------------------

-- users

create table users (
    id integer auto_increment primary key,
    login char(30) character set utf8 not null,
    login_u char(30) character set utf8 not null,  -- uppercase version
    email char(120) character set utf8,
    email_u char(120) character set utf8,  -- uppercase version
    name char(60) character set utf8,
    passwd1 char(30) not null,
    passwd2 char(30) not null,
    about varchar(250) character set utf8,
    status tinyint not null,        -- 0 = inactive, 1 = active
    created datetime not null,
    last_login datetime,
    visits integer not null,
    settings integer not null,
    ula_time datetime,              -- unsuccessful login attempt time
    ula_cnt integer not null,       -- and count
    deleted char(1) not null        -- 'Y' / 'N'
);

create index users_login on users (login_u);
create index users_email on users (email_u);


-- user settings

create table users_settings (
    user_id integer,
    us_key char(15),
    us_val varchar(250) character set utf8,
    primary key (user_id, us_key)
);


-- user logins

create table users_logins (
    sesid char(15) primary key,
    uagent varchar(120) not null,
    ip char(15) not null,
    user_id integer not null,
    created datetime not null,
    last_used datetime not null
);


-- account activations

create table users_activations (
    linkkey char(30) primary key,
    user_id integer not null,
    created datetime not null
);


-- password resets

create table users_p_resets (
    linkkey char(30) primary key,
    user_id integer not null,
    created datetime not null,
    tries integer not null
);

create index users_p_resets_uid on users_p_resets (user_id);


-- messages

create table users_messages (
    user_id integer,
    msg_id integer,
    email varchar(120) character set utf8,
    message text character set utf8,    -- 64 kB limit
    created datetime not null,
    primary key (user_id, msg_id)
);

How to create a database in MySQL

Let's suppose we want to create a database for Toy Facebook.

1. You need to have the path to MySQL bin directory added to your environment. In the command line you need to be able to invoke mysql command.
2. Log in to mysql command line tool:

mysql -u root --password=mysqlrootpassword

3. Create user:

create user 'tfb'@'localhost' identified by 'tfb';
grant all privileges on * . * to 'tfb'@'localhost';
quit

4. Go to mysql again, as tfb user:

mysql -u tfb --password=tfb

5. Create database:

create database tfb;
quit

6. Create tables. Save the SQL script from above to users_tables.sql file, and:

mysql -u tfb --password=tfb tfb < users_tables.sql

Compilation script

Both DBMYSQL and USERS compilation switches must be present. Also, you need to add silgy_usr.c to compilation, and MySQL library path and name, so your m script would look like this:

#!/bin/sh

echo Making silgy_app...

g++ silgy_app.cpp silgy_eng.c silgy_lib.c silgy_usr.c \
-s -O3 \
-D DBMYSQL -D USERS \
-I/usr/include/mysql55 \
-L/usr/lib64/mysql -lmysqlclient \
-o ../bin/silgy_app

At least this script works on AWS AMI distro, you may need to verify your paths if using something else.

Sample code

You can download Toy Facebook project as a template for a typical web application with users.

Logged in user sessions in Silgy

Successful silgy_usr_login() call does the following:

• Sets the uses array record values (accessible via US macro),
• Adds record to users_logins table,
• Adds ls cookie to the response,
• Updates users.visits and users.last_login.

Keep Me Logged In

Adding keep=on to the login request will set ls cookie expiration to date to +30 days. Therefore — until you call silgy_usr_logout() — every subsequent request with valid ls cookie and the same User Agent as in the initial request, will automatically mark session as "logged in". Note that silgy_usr_logout() removes all records that belong to the user so it will log them out from every device.

Otherwise

Without keep=on cookie does not have expiration time, so by default it will expire by the end of the current browser session.

Caching

Logged in sessions are cached for 30 minutes after last activity. Within this range there is no need to query users_logins table.

Unsuccessful logins

Unsuccessful login count and time is stored in users.ula_cnt and users.ula_time. User can try 3 times without negative consequences. Then, to prevent brute-force attacks, the fourth attempt requires waiting for 1 minute, fifth — for 10 minutes, sixth — for 1 hour.