Overwrite all records?

[Osmosis311]

First off, let me just say a massive thank you for writing all of these libraries. My organization is excited to begin using these controllers, and these libraries are fantastic. Thank you so much for the amazing work.

If you need help testing this one, please let me know, I'm very excited about this one in particular.

Quick question: is it the current plan that when the job runs to sync from the DB -> Controllers, that it will wipe out everything in the controller first, and then store everything from the DB? My preference is to overwrite; I don't want to have to worry about someone who used the desktop client (from the manufacturer) and made a change. I want to make sure that when I update from the DB, I know that the ONLY records in the controllers are the ones getting synced from the DB.

Thanks so much again!

[uhppoted]

Happy to hear you're finding it useful :-).

re. sync. The actual implementation reads back all the cards stored across the set of configured controllers, does a diff against the access records stored in the database and then does an add/update/delete on individual card records to bring it into sync with the database. So if somebody has added (or modified or deleted) a card using the manufacturer app their changes will be overwritten by the sync.

It's implemented this way because clearing all the records on the controllers and then reinstating them turns out to be quite a slow operation and it would mean that the controllers would effectively be offline while sync'ing which is not ideal in an access system.

BTW, the roadmap has adding support for MySQL this dev cycle but if you're using something else let me know and I'll put it on the list.

[Osmosis311]

Awesome! That's exactly what I was hoping you'd say! :-)

This is great. I happen to be using SQL Server, but I can make whatever you've got work. The hard part is the syncing; storing my data in whatever database is supported is the easy part ;-)

Thanks so much again.

[uhppoted]

There's actually a Microsoft maintained driver for Go so it looks like adding support for SQL Server should be relatively straightforward. I've added it to the list for this dev cycle and will post a note here when it's available - shouldn't take all that long actually.

[uhppoted]

Hi,

Just a quick note to let you know that a provisional SQL Server implementation has been added to the main branch. It still needs a bit more testing and the next full release is probably 6-8 weeks away but if you want to use it in the interim building it from source is straightforward.

[Osmosis311]

Awesome! Thank you!

I just built it and I'm trying to test it now but it doesn't seem to be passing in the dsn parameter correctly. It keeps saying it can't connect to localhost:1433

Am I executing the command incorrectly?

./uhppoted-app-db --debug --config uhppoted.conf load-acl --dsn "sqlserver://admin:password@servername?database=Access" --table:ACL AccessControl

[uhppoted]

Ooops!!! I had left a hardcoded DSN in place from some debugging :-(. Apologies - have pushed the fix to main so if you do a git pull --rebase it should hopefully be fixed.

[Osmosis311]

LOL I was just in the process of forking and doing a PR for the same change. Thank you for the fast response!

[Osmosis311]

Just did a test. Worked perfectly!

[uhppoted]

Oh great (and/or .. whew!!!). That would have been embarrassing :-) :-)

[Osmosis311]

Is it possible to configure it such that if I have two boards, the script can make changes to both of them, and the DB configuration would be something like:

Door1 = Controller1 / Door 1 Door2 = Controller1 / Door 2 Door3 = Controller1 / Door 3 Door4 = Controller1 / Door 4 Door5 = Controller2 / Door 1 Door6 = Controller2 / Door 2 Door7 = Controller2 / Door 3 Door8 = Controller2 / Door 4

If not, I assume I just need to keep separate tables in the database for each controller?

[uhppoted]

Yup, it's possible - the ACL table is expected to define the access privileges using just door names and the set of doors can span multiple controllers. The door names should match the configuration in the uhppoted.conf file, e.g.:

# DEVICES
UT0311-L0x.405419896.name = Alpha
UT0311-L0x.405419896.door.1 = Great Hall
UT0311-L0x.405419896.door.2 = Kitchen
UT0311-L0x.405419896.door.3 = Dungeon
UT0311-L0x.405419896.door.4 = Hogsmeade

UT0311-L0x.303986753.name = Beta
UT0311-L0x.303986753.door.1 = Gryffindor
UT0311-L0x.303986753.door.2 = Ravenclaw
UT0311-L0x.303986753.door.3 = Hufflepuff
UT0311-L0x.303986753.door.4 = Slytherin

Hmmm, thought I had documented this but apparently not - ok, there is a communal configuration file called uhppoted.conf which is just a text file with the common configuration for all the projects. It has multiple sections, but the only one used by uhppoted-app-db is the devices section which defines the controllers and the doors.

The default uhppoted.conf file is located in different places for different platforms:

• /etc/uhppoted/uhppoted.conf (Linux)
• /usr/local/etc/com.github.uhppoted/uhppoted.conf (MacOS)
• \Program Data\uhppoted\uhppoted.conf (Windows)

but you can specify a different file path using the --config option, e.g.:

uhppoted-app-db --config C:\uhppoted\uhppoted.conf ...

I've attached a sample file so that you can see what it looks like - just delete all the unnecessary bits and edit the devices section.

uhppoted.conf.zip

[uhppoted]

The ACL table for the above Devices section looks like this:	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