How to handle logging-data

[hberg539]

Hello,

i'm currently building an access control system where people can physically authenticate at a door with a dongle. If the user has access to a specific door, the door can be opened.

Every access to a door should be logged with information about time, user and dongle. Normally, i would just create a mysql table and put the data in there. Of course the table can get quite large over time.

Are there any best-cases for handling this type of data in processwire? Should i just create a template with fields like user, dongle, time and create pages for each authentication? And then search / select with proccesswire selectors?

Thanks!

Best regards,

Kevin
 

[Zeka]

There are several possible ways.

1. Create template as you have proposed.

2. Create custom field type with custom DB table schema. 

3. Use Table field from Profields.

4. Use wire $log (actually, don't think that it's an option here)

Options 2 and 3 are more efficient from how they store data in DB. 

[wbmnfktr]

• Who will use these data entries?
• What will happen with all these data entries?
• How many data entries will there be each minute/hour/day/week?
• How long will they be stored?
• Will they be archived/deleted?
• Will someone create charts from it?
• Will they be exported to another programm/system?
• Will there be JSON/XML/RSS exports/strems/feeds or even an API?
• Will ProcessWire provide those access details for each dongle/user/door?

There are so many best ways to go depending these paramters and variables.

 

[hberg539]

The actual size of the database doesn't matter that much, but the speed in how the data can be accessed matters.

Zeka, is there any difference in speed with option 1. and 2.?

 

wbmnfktr, i try to answer some of the questions you mentioned:

The data is for history reasons (who opened the door where and at what time) and the data should be stored at least half a year. After that, the data can be truncated or be archived to an csv file. I project around 120.000 logging entries per month. Charts should be created (e.g. door access activitiy in the last 30 minutes). Processwire will provide an API to the door access system, so each door access request will be controlled by processwire.

I'm currently a beginner in processwire (but really do like it), so a simple solution which doesn't result in a bad bottleneck one or two months later would be great.

Thank you for your help.

Edited April 26, 2018 by hberg539

[flydev]

I have a similar setup where around 100k pages per months are created. Even if your setup if more complex due to a lot of door, etc, I hope you will get the idea.

You could structure your tree like the following setup :

+ Root

|- - Building (building template)

|- - - - 2018 (year template)

|- - - - - - April (month template)

|- - - - - - - - opened_door_entry (door template)

|- - - - - - - - opened_door_entry (door template)

|- - - - - - - - [...]

|- - - - - - - - Reports

|- - - - - - - - - - day_1_report (report template)

|- - - - - - - - - - day_2_report (report template)

|- - - - - - - - - - day_3_report (report template)

|- - [...] another building/year/month/data tree

 

The "door template" will contain the information about an unique entry (user id, dongle id, door id, access time etc).

Each night, you

• generate a report for the current day
• update the monthly report

And to access the data, you will have three options :

1. access a global report for a given date or door_id, user_id... (speed: 1-2seconds)
2. access a report for a given day and/or door_id, user_id... (speed: 1-2seconds)
3. build a custom report from the entries (speed: fast with custom query, see below)

With theses options, you can do some COUNT(), SUM(), etc on the fetched pages fields if required.

For the option #3, you must go with custom SQL queries. A module is born recently (thanks @bernhard) which will help you to query your data, take a look at this thread:

 

 

Or you can begin and modify this module for your needs :  https://modules.processwire.com/modules/pages-sum/

 

Do not hesitate to ask precisions or discuss about your interesting project ✌️

Hope it help!

[bernhard]

hi @hberg539, welcome to the forum and to the world of ProcessWire

very interesting project, indeed! ProcessWire is awesome for making interfaces for managing your database in an easy, and (by everybody) understandable way (gui). The problem that comes with all the flexibility is that regular SQL queries are WAY harder to do (just take a look at the database. Every field is a single table, so you need to join all tables before you can actually use them for a useful query). So if you are used to having your database tables and doing some SELECT this FROM that... then you'll have a much harder time. That's why I built the linked RockFinder (that is still alpha, so be careful - there might be some breaking changes in the sooner future).

 

Using RockFinder it will be very easy to use the PW API (which is great, of course), plus - and that's the awesome part - do everything you want via SQL. See this post how you can easily combine both worlds. Basically you just let RockFinder create the complex SQL query for you and then you build another SQL select around it:

$finder = new RockFinder("template=dooraction", [...fields...]);
$sql = $finder->getSQL();

$database->query("SELECT foo, sum(bar) FROM ($sql) as mytable WHERE DATE_FORMAT(...)");

There are also some performance tests in the linked topic. Hope you share a great showcase with us once you are done  

Good luck and lots of fun!

[hberg539]

There are really many options with processwire

I will try / go with the simplest version (just a page for each door access) and see how it works out.

Thank you for all the recommendations!

 

[bernhard]

3 hours ago, hberg539 said:

I will try / go with the simplest version (just a page for each door access) and see how it works out.

As long as you use regular selectors and don't loop over thousands of pages it will be perfectly fine