cron'able command line utility to download access control lists managed by a database to UHPPOTE
UTO311-L0x access controller boards.
Supported operating systems:
- Linux
- MacOS
- Windows
- ARM
- ARM7
Supported databases:
- sqlite3
- Microsoft SQL Server
- MySQL
- PostgreSQL
-
Please note that this README describes the current development version and the command line arguments have changed to support additional drivers. If you are using v0.8.5, please see README-v0.8.5 for a description of the command line arguments.
-
The sqlite3 implementation has also changed to expect the ACL table to include an ON CONFLICT REPLACE clause when creating the table rather than implementing an UPSERT.
v0.8.9 - 2024-09-06
- Added TCP/IP support.
- Updated to Go 1.23.
Executables for all the supported operating systems are packaged in the releases. The provided archives contain the executables for all the operating systems - OS specific tarballs can be found in the uhppoted releases.
Installation is straightforward - download the archive and extract it to a directory of your choice and then place the executable in a directory in your PATH. The uhppoted-app-db utility requires the following additional
files:
uhppoted.conf
uhppoted.conf is the communal configuration file shared by all the uhppoted project modules and is (or will
eventually be) documented in uhppoted. uhppoted-app-db requires the
devices section to resolve non-local controller IP addresses and door to controller door identities.
A sample uhppoted.conf file is included in the uhppoted distribution.
Assuming you have Go and make installed:
git clone https://github.com/uhppoted/uhppoted-app-db.git
cd uhppoted-app-db
make build
If you prefer not to use make:
git clone https://github.com/uhppoted/uhppoted-app-db.git
cd uhppoted-app-db
mkdir bin
go build -trimpath -o bin ./...
The above commands build the 'uhppoted-app-db executable to the bin directory.
| Dependency | Description |
|---|---|
| com.github/uhppoted/uhppote-core | Device level API implementation |
| com.github/uhppoted-lib | Shared application library |
Usage: uhppoted-app-db <command> <options>
Supported commands:
load-aclstore-aclcompare-aclget-aclput-aclget-eventsversionhelp
The uhppoted-app-db commands require a DSN command line argument to specify the database connection.
-
For sqlite3, this takes the form
sqlite3://<filepath>, where the file path is the path to the sqlite3 database file.
e.g.sqlite3://../db/ACL.db -
For Microsoft SQL Server, DSN is any DSN accepted by the Microsoft SQL Server driver, as specified in the official documentation. Typically a SQL Server DSN takes the form
sqlserver://<uid>:<password>@<host>?database=<database>.
e.g.sqlserver://sa:UBxNxrQiKWsjncow7mMx@localhost?database=uhppoted -
For MySQL the DSN takes the form
mysql://<DSN>where the DSN is a MySQL DSN, typically of the form[username[:password]@][protocol[(address)]]/<dbname>
e.g.mysql://qwerty:uiop@tcp(127.0.0.1:3306)/uhppoted. The database name is required, the other parameters are optional:
e.g. a minimal MySQL DSN would bemysql:///uhppotedand would use the default connection, user and password to connect to database uhppoted. -
For PostgreSQL, DSN is the standard PostgreSQL DSN
postgres://{user}:{password}@{hostname}:{port}/{database-name}, e.g.postgresql://uhppoted:qwerty@localhost:5432/uhppoted
The ACL table is expected to have the following structure:
| Column | Data Type | Description |
|---|---|---|
| CardNumber | INTEGER | Valid card number |
| PIN | INTEGER | Optional keypad PIN code in the range 0-99999. Only required for the --with-pin option. |
| StartDate | DATE or TEXT | Date from which the card is valid (YYYY-mm-dd) |
| EndDate | DATE or TEXT | Date after which the card is no longer valid (YYYY-mm-dd) |
| <door 1> | INTEGER | Access privilege for door 1 (0 none, 1 full access and 2-254 correspond to a time profile) |
| <door 2> | INTEGER | Access privilege for door 2 (0 none, 1 full access and 2-254 correspond to a time profile) |
| ... | INTEGER | Access privilege for door N (0 none, 1 full access and 2-254 correspond to a time profile) |
A Name column is optional and ignored.
e.g.:
| Name | CardNumber | PIN | StartDate | EndDate | GreatHall | Gryffindor | HufflePuff | Ravenclaw | Slytherin | Kitchen | Dungeon | Hogsmeade |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Albus Dumbledore | 10058400 | 7531 | 2023-01-01 | 2023-12-31 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Rubeus Hagrid | 10058401 | 0 | 2023-01-01 | 2023-12-31 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 1 |
| Dobby The Elf | 10058402 | 0 | 2023-01-01 | 2023-12-31 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 |
| Harry Potter | 10058403 | 0 | 2023-01-01 | 2023-12-31 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 29 |
| Hermione Grainger | 10058404 | 82953 | 2023-01-01 | 2023-12-31 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 29 |
| Crookshanks | 10058405 | 1397 | 2023-01-01 | 2023-12-31 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 1 |
The audit trail table is optional but if specified on the command line with the--table:audit option it is expected to
have the following structure:
| Column | Data Type | Description |
|---|---|---|
| Timestamp | DATETIME | DEFAULT value should be the current date/time |
| Operation | string | 'compare', 'load', etc. VARCHAR(64) (or equivalent) |
| Controller | uint32 | Controller ID. INT (or equivalent) |
| CardNumber | uint32 | Card number. INT (or equivalent) |
| Status | string | Card status. VARCHAR(64) (or equivalent) |
| Card | string | Optional card details. VARCHAR(255) (or equivalent) |
Notes:
- The table can have either or both of the CardNumber or the Card columns.
- For sqlite3, SQL Server and MySQL the Timestamp column is expected to be filled automatically with the CURRENT_TIMESTAMP.
The operations log table is optional but if specified on the command line with the--table:log option it is expected to
have the following structure:
| Column | Data Type | Description |
|---|---|---|
| Timestamp | DATETIME | DEFAULT value should be the current date/time |
| Operation | string | 'compare', 'load', etc. VARCHAR(64) (or equivalent) |
| Controller | uint32 | Controller ID. Nullable INT (or equivalent) |
| Detail | string | Operation summary. VARCHAR(255) (or equivalent) |
Notes:
- For sqlite3 and SQL Server the Timestamp column is expected to be filled automatically.
Fetches an ACL file from the configured database and downloads it to the configured UHPPOTE controllers. Intended for use
in a cron task that routinely updates the controllers on a scheduled basis.
A list of the changes made to the controllers can optionally be stored in an audit trail and a summary of the operation can optionally be stored in a log table.
Command line:
uhppoted-app-db load-acl --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] load-acl [--with-pin] --dsn <DSN> [--table:ACL <table>] [--table:audit <table>] [--table:log <table>]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) ACL table. Defaults to _ACL_.
--table:audit <table> (optional) audit trail table. Defaults to no audit trail.
--table:log <table> (optional) log table. Defaults to no log.
--with-pin Includes the card keypad PIN code when updating the access controllers
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db load-acl --dsn sqlite3://./db/ACL.db --table:ACL ACL2
uhppoted-app-db --debug --config .uhppoted.conf load-acl --with-pin --dsn sqlite3://./db/ACL.db
Fetches the cards stored in the set of configured access controllers, creates a matching ACL from the access controller
configuration and stores it in a database table. Intended for use in a cron task that routinely audits the cards stored
on the controllers against an authoritative source.
A summary of the operation can optionally be appended to stored in a log table.
Command line:
uhppoted-app-db store-acl --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] store-acl [--with-pin] --dsn <DSN> [--table:ACL <table>] [--table:log <table>]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) ACL table. Defaults to _ACL_.
--table:audit <table> (optional) audit trail table. Defaults to no audit trail.
--table:log <table> (optional) log table. Defaults to no log.
--with-pin Includes the card keypad PIN code in the information retrieved from the access controllers
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db store-acl --dsn sqlite3://./db/ACL.db --table:ACL ACL2
uhppoted-app-db --debug --config .uhppoted.conf store-acl --with-pin --dsn sqlite3://./db/ACL.db
Fetches an ACL file from a database and compares it to the cards stored in the configured UHPPOTE controllers. Intended for
use in a cron task that routinely audits the controllers against an authoritative source.
A list of the differences can optionally be stored in an audit trail and a summary of the operation can optionally be stored in a log table.
Command line:
uhppoted-app-db compare-acl --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] compare-acl [--with-pin] [--file <file>] --dsn <DSN> [--table:ACL <table> [--table:audit <table> [--table:log <table>]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) ACL table. Defaults to _ACL_.
--table:audit <table> (optional) audit trail table. Defaults to no audit trail.
--table:log <table> (optional) log table. Defaults to no log.
--with-pin Includes the card keypad PIN code when comparing card records from the access controllers
--file Optional file path for the compare report. Defaults to displaying the ACL on the console.
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db compare-acl --dsn sqlite3://./db/ACL.db
uhppoted-app-db --debug --config .uhppoted.conf compare-acl --with-pin --dsn sqlite3://./db/ACL.db
Fetches tabular data from a database table and stores it to a TSV file. Intended for use in a cron task that routinely
retrieves the ACL from the database for use by scripts on the local host managing the access control system.
A summary of the operation can optionally be stored in a log table.
Command line:
uhppoted-app-db get-acl --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] get-acl [--with-pin] [--file <TSV>] --dsn <DSN> [--table:ACL <table>] [--table:log <table>]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) ACL table. Defaults to _ACL_.
--table:log <table> (optional) log table. Defaults to no log.
--with-pin Includes the card keypad PIN code when retrieving the cards from the access controllers
--file Optional file path for the destination TSV file. Defaults to displaying the ACL on
the console.
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db get-acl --dsn sqlite3://./db/ACL.db
uhppoted-app-db get-acl --dsn sqlite3://./db/ACL.db --table:ACL ACL2 --with-pin
uhppoted-app-db --debug --config .uhppoted.conf get-acl --dsn sqlite3:./db/ACL.db --with-pin --file ACL.tsv
Uploads an ACL from a TSV file to a database table. Intended for use in a cron task that routinely transfers information
to the database from scripts on the local host.
A summary of the operation can optionally be stored in a log table.
Command line:
uhppoted-app-db put-acl --file <TSV> --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] put-acl [--with-pin] --file <TSV> --dsn <DSN> [--table:ACL <table>] [--table:log <table>]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) ACL table. Defaults to _ACL_.
--table:log <table> (optional) log table. Defaults to no log.
--with-pin Includes the card keypad PIN code in the uploaded data
--file (required) File path for the TSV file to be uploaded to the database
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db put-acl --with-pin --file ACL.tsv --dsn sqlite3://./db/ACL.db --table:ACL ACL2
uhppoted-app-db --debug --config .uhppoted.conf put-acl --wih-pin --file ACL.tsv --dsn sqlite3://./db/ACL.db
Retrieves events from the set of configured controllers and stores them in a database table, incrementally filling any gaps in the event list for each controller.
A summary of the operation can optionally be stored in a log table.
Command line:
uhppoted-app-db get-events --dsn <DSN>
uhppoted-app-db [--debug] [--config <file>] get-events --dsn <DSN> [--table:events <table>] [--table:log <table>] [--batch-size]
--dsn <DSN> (required) DSN for database as described above.
--table:ACL <table> (optional) Events table. Defaults to _Events_.
--table:log <table> (optional) log table. Defaults to no log.
--batch-size Maximum number of events to retrieve (per controller) per invocation. Defaults to 128.
--config Sets the uhppoted.conf file to use for controller configurations
--debug Displays verbose debugging information such as the internal structure of the ACL and the
communications with the UHPPOTE controllers
Examples:
uhppoted-app-db get-events --dsn sqlite3://./db/ACL.db
uhppoted-app-db --debug --config .uhppoted.conf get-events --dsn sqlite3://./db/ACL.db --table:events Events2 --batch-size 64