Database Integration

This section details the different objects (tables) present in the database and the features you can use in order to support methods to insert, update and delete information from it.

users

Represents a user. All information and definitions related to the user is stored in this table

Field	Type	Description
id	int 64	changeable record identifier of a user. In iDSecure, each user edit results in an insertion into the Users table, with a new `id`.
inactive	bool	Indicates whether a user is inactive (If 0, it is active and if 1, it is inactive). Inactive users are still listed in iDSecure but have no access permissions.
Contingency	bool	Indicates whether a user is in contingency (1 indicates it is in contingency and 0 otherwise). This value is only meaningful in the Enterprise version of iDSecure. In contingency mode, user information is kept on the devices, so that users in contingency continue to have unrestricted access even if the server or network is down.
deleted	bool	Indicates whether a user is deleted (1 indicates deleted and 0 otherwise). Deleted users are not listed in iDSecure and are kept in the database only in order to record the history of changes to a user for auditing purposes.
idDevice	int	Unique and immutable identifier of a user. This value is persisted during edits of a user and corresponds to the user's "ID" information displayed in the iDSecure web interface.
idType	bool	Indicates the type of user:Person or Visitor. If it is 0 it is person and 1 is visitor.
idArea	int	Identifier of the user's current area (`id` column, from Areas table).
dataLastLog	int	Integer representing the date and time (Unix timestamp) of the last access by the user.
timeOfRegistration	int	Integer representing the date and time (Unix timestamp) of the user registration.

User Maintenance

As per the description of the id field of the Users table, iDSecure's behavior when updating a user is to mark the old record with the flag deleted = 1 and insert a new record with a new value in the id column and the updated user values. This is done in order to keep track of the history of changes to a user's information and is not required. It is important to be careful not to duplicate records in the Users table with the same id.

Example of modifying a user (updating an existing record)

UPDATE Users SET name = "Walter H. White" WHERE id = 1000001;

Example of modifying a user (creating a new record and preserving the existing one)

This behavior is similar to what iDSecure does when editing a user.

INSERT INTO Users
SELECT MAX(_u.id) + 1, "Walter H. White", _u.registration, _u.pis, _u.senha, _u.barras, _u.cpf, _u.rg, _u.phone, _u.email, _u.emailAcesso, _u.hash, _u.salt, _u.admin, _u.inativo, _u.contingency, _u.deleted, _u.idDevice, _u.endereco, _u.bairro, _u.cidade, _u.cep, _u.cargo, _u.admissao, _u.telefone, _u.ramal, _u.pai, _u.mae, _u.nascimento, _u.sexo, _u.estadoCivil, _u.nacionalidade, _u.naturalidade, _u.idResponsavel, _u.responsavelNome, _u.veiculo_marca, _u.veiculo_modelo, _u.veiculo_cor, _u.veiculo_placa, _u.idType, _u.dateLimit, _u.visitorCompany, _u.blackList, _u.dateStartLimit, _u.pisAnterior, _u.comments, _u.allowParkingSpotCompany, _u.idArea, _u.dataLastLog, _u.timeOfRegistration
FROM Users _u 
WHERE id = 1000001;

UPDATE Users SET deleted = 1 WHERE id = 1000001;

AccessRules

Access Rules Organization

An access rule is the set of information that determines whether a certain user has access to a location, at a certain time. In addition to the "Who", "When" and "Where" information, you can also specify some additional logic, such as re-entry blocking, escorting, etc. The access rules table is AccessRules and contains only the "How" information a user can have access, corresponding to the additional logics mentioned above.

The locations and times are defined in dedicated tables for this, Areas and Scheduls, respectively.
What associates areas and times with an access rule are relational tables, such as AreaAccessRules and SchedulAccessRules. Below is a description of the tables mentioned:

This table contains the information of AccessRules.

Field	Type	Description
id	int 64	Access rule identifier.
name	bool	Indicates whether a user is inactive (If 0, it is active and if 1, it is inactive). Inactive users are still listed in iDSecure but have no access permissions.
idType	string	Type of rule, regarding user or vehicle access control (Can contain the values 'users' or 'vehicles')

Areas

This table contains the information of Areas.

Field	Type	Description
id	int 64	Area identifier.
name	string	Area name.
external	bool	indicates whether the area is external. The definition of an external area is important for the use of some features related to a user leaving the premises. For example: download cards and expiration of Visitors when leaving a company.
ade	bool	Indicates if the area has Anti-Double Entrance enabled. The Anti-Double Entry (ADE) is a feature used in areas with controlled entry and exit, preventing one user from granting entry to another at turnstiles, for example.

AreaAccessRules

This table contains the relationships of Area to Access Rules.

Field	Type	Description
id	int 64	Identifier of the relationship between an area and an access rule.
idAccessRule	int	Identifier of the access rule (column `id`, from table AccessRules).
idArea	int	Area identifier (`id` column, from the Areas table).

Scheduls

This table contains the information for Schedules definitions.

Field	Type	Description
id	int 64	Schedule definition identifier.
name	string	Name of schedule definition.
sundayStart	int	Release /office start time, expressed in seconds from 0:00.
sundayStart	int	Release /office end time, expressed in seconds since 0:00.

* As you might expect, there is a pair of xStart and xEnd columns for each day of the week, starting with Sunday (sunday) and going all the way through to Saturday (saturday). * The name of this table contains a typo, and for compatibility reasons its name should not be changed.

SchedulAccessRules

This table contains the relationships of SchedulAccessRules definitions with Access Rules.

Field	Type	Description
id	int 64	Identifier of the relationship between a schedule definition and an access rule.
idAccessRule	int	Identifier of the access rule (`id` column, from AccessRules table).
idSchedul	int	Schedule definition identifier (`id` column, from Scheduls table).

UserAccessRules

This table contains the relationships of People and Visitors with Access Rules.

Field	Type	Description
id	int 64	Identifier of the relationship between a user belonging to an access rule.
idAccessRule	int	Identifier of the access rule (column `id`, from table AccessRules).
idUser	int	user identifier (`id` column, from the Users table).
isEscort	int	Indicates whether the department or group contains escorts, for use with the escort functionality (Check completion of the `EscortEnabled` and `EscortPeriod` columns of the AccessRules table).

Groups

This table contains the definition information for Departments, Groups and Companies.

Field	Type	Description
id	int 64	Identifier of the relationship between a user belonging to a department, group or company.
idUser	int	Identifier of the access rule (`id` column, from the AccessRules table).
disableADE	bool	Indicates whether Anti-Double Entry settings are disabled for this department, group or company.
idType	int	Type of the record, indicating whether it is a department, group or company (Possible values: 0=Department, 1=Group, 2=Company).

UserGroups

This table contains the relationships of People and Visitors to Departments, Groups and Companies.

Field	Type	Description
id	int 64	Department, group or company identifier.
idUser int*	int	Department, group or company name.
idGroup	int	department, group or company identifier (`id` column of the Groups table).
isVisitor	bool	Indicates whether the group is a visitor group.

GroupAccessRules

This table contains the relationships of Departments, Groups and Companies with Access Rules.

Field	Type	Description
id	int 64	Identifier of the relationship between a department, group or company belonging to an access rule.
idAccessRule	int	Identifier of the user (`id` column, from the Users table).
idGroup	int	Department, group or company identifier (`id` column, from Groups table).
isEscort	bool

Example of associating a user to an access rule, by user ID

Inserts user ID number 1000001 into access rule ID 1 ("Always Free" rule by default).

INSERT INTO UserAccessRules (idAccessRule, idUser) VALUES (1, 1000001);

Example of associating a user to a department, group, or company

Inserts user ID number 1000001 into ID group 1 ("Standard Department" by default).

INSERT INTO Groups (idGroup, idUser) VALUES (1, 1000001);

You can create departments and access rules using iDSecure's own user interface, configuring them as needed, and just insert users into the appropriate departments.

Logs

All user access logs are stored in the Logs table of the iDSecure database.

Field	Type	Description
id	int 64	Access Log Identifier.
idDevice	int	Device identifier of the device accessed (`id` column of the Devices table).
deviceName	string	Name of the device being accessed (`name` column of the Devices table)
time*	int	Integer representing the date and time (Unix timestamp) of the access
event	int	Type of access event. Possible events: 0=Error; 1=Invalid equipment; 2=Invalid identification rule parameters; 3=Not identified; 4=Pending identification;5=Timeout on identification; 6=Access denied; 7=Access allowed; 8=Pending access; 11=Opening by pushbutton; 12=Opening by Web interface (or "Open door" command); 13=Drop out;
idArea	int	Identifier of the accessed area (`id` column of the Areas table).
area	string	Name of the accessed area (`name` column of the Areas table).
reader	string	Reader through which the access was performed (reader column of the DeviceRelays table).
idUser	int	Identifier of the user who performed the access (id column of the Users table).

Example of a query of the last 10 authorized accesses of a user

SELECT u.id, u.name, l.idDevice, l.deviceName, l.reader, l.idArea, l.area, l.event, l.time
FROM Logs l
INNER JOIN Users u ON l.idUser = u.id
WHERE l.idUser = 1000001 AND l.event = 7
ORDER BY time DESC
LIMIT 10;

Cards

This table contains information about the registered cards.

Field	Type	Description
id	int 64	Card identifier.
idUser	int	ID of the user to whom the card belongs. It corresponds to the "id" field of the Users table.
number	int	Card code in numeric format. In case of cards in "area,code" and hexadecimal format, this value is converted to numeric format and stored in this field.
idType	int	Integer representing whether the tag is intended for a person or a vehicle, if it has the value 1 = person, if it has the value 2 = vehicle
type	int	Card technology: "0" for ASK/125kHz, "1" for Mifare and "2" for QR-Code.
numberStr	string	Card code in String format. Useful for cards whose code contains hexadecimal or area characters and code.

Devices

This table contains information about registered devices.

Field	Type	Description
id	int 64	Device identifier.
name	string	Device Definition Name.
host	int	String containing the device's host.
port	int	Integer representing the port on which the device is listening.
serial	string	String containing the device's serial number.
versão	string	String containing the software version.