User Database - Storing data in the Extra Properties table?

Ignition
1

When using Database Authentication for Users, the Ignition system creates this ‘user_ex’ properties table:

All of the users in our facility have badge numbers, and I need to store them somewhere. I’d like to use this table rather than create another table with essentially the same purpose as this table.
Two questions:

  1. Is it OK to store my own custom user properties here (like ‘BadgeNo’, or is this intended for the ignition system’s own internal use (i.e. if I put them here is their a chance the system will modify/delete/etc. the records?
  2. If I can use this table, are there any ‘reserved’ property names I should avoid?
  3. Are there any built-in functions in Ignition to pull properties from this table? (e.g. system.user.getProperty() ) or will I need to create the queries myself?

Thanks,
Mike

2

you can add a column to that table.

3

Note that the table appears to be designed as a key value store so you could just as easily just add a row for BadgeNo instead of adding badge no to every row that is added for a user:

INSERT INTO Ign_Users_user_ex (user_id, prop_name, prop_value) VALUES(###, 'BadgeNo', '1829031-000')

That said, vendor prefixed tables are generally not super safe to modify as the vendor reserves the right to alter or even drop that table at their discretion.

Conversely, if you do modify the table, consider prefixing your columns with something the vendor is unlikely to overwrite like:

ALTER TABLE Ign_Users_user_ex ADD mschell_BadgeNo varchar(50);
4

Hello,

I’m trying to use those tables as well. Did you manage to retreive your extra properties ? I can’t figure how-to with the system.user.* methods.

Regards.

5

I just got of off the phone with tech support. I was told that the Extra Properties Query parameter doesn’t really do anything. To gain access to the properties that would be in that table you will have to query the database directly, so the Extra Properties Query is pretty useless. There are no scripts or methods available that will allow you to get to those properties.

The Contact Info Query functions the same way as the Extra Properties Query. The table associated with the Contact Info Query can have as many values as you want in it and they do not have to be actual contact info. So you can set the (key,value) pairs you wanted to use in the Extra Properties Query within the Contact Info Query, I found that you don’t have to explicitly name your columns to contact_type, contact_value or the name of your table to user_contact for the query to work.

The system.user.getUser scripting function has a method called getContactInfo(). This method will return a list all of the (key,value) pairs in the table provided to the Contact Info Query parameter. I’ve provided an example script below that will convert this list into a Python dictionary.

contactInfoList =  system.user.getUser('Database','snngeru').getContactInfo()
print dict((str(row).split(': ')[0],str(row).split(': ')[1]) for row in contactInfoList)
6

Hi everyone. I was wondering if there have been any changes since Sep 2019 regarding Extra Properties Query for manual mode database user source. Does it do anything now, or is it still, in a sense, "useless" because it doesn't really do anything or is not used by Ignition? Thanks for this topic, though, because it got me using the Contact Info Query to accomplish what I wanted to accomplish.

7

Wondering the same. Never got it to work even with a ton of different test queries, I have a NQ workaround but this is kind of a silly thing to have left non-functional for 7 years.

8

The extra users property is not exactly "useless", as described in previous posts. Support may have been mislead or misunderstood what it actually does - which, to be fair, is pretty niche.

The "extra properties" query absolutely functions and does what it's supposed to.
The problem is that what it's supposed to do/the way it is written is not what you and others in this thread apparently expect.

Ignition's user source system is flexible/extensible by modules, allowing them to register custom properties they care about. The extra props query is run and checks against the gateway's list of known properties. If the name returned in the first column matches a known property, and the value in the second column can be coerced into that type, then that custom property will be attached to the actual user object and can be inspected at runtime using system.user methods as you'd expect.

The UserSource.DB_ManualMode logger on debug will even tell you about this:

log.debugf("Found property \"%s\" that doesn't exist for user \"%s\"", key, uname);

Here's where it's close to useless: There's only 7 'standard' user properties, and only 1 "custom" property registered by any first party modules:
Username, FirstName, LastName, Notes, Schedule, Language, Badge
Plus user.pin, registered by both the Twilio and voice notification modules.
So, yes, if you want totally arbitrary props you have to work around this limitation some way.
The contact info based approach posted earlier in the thread is reasonable, though maybe hard to follow for the next person maintaining your system.

Another option, at least in theory (I see no reason it wouldn't work in practice, but don't have time to try it) would be to do your own registration of custom properties via a gateway script calling in to the UserSourceManager methods:

image
image1556×736 91.3 KB

If you do that, then run system.user queries, the extra properties should show up on your user object.

9

Yeah I plan on just integrating my own extension through the Module SDK if possible, we already use it for components so if we can integrate that functionality as well it would be a good solution. It is just unfortunate that even trying to mimic Active Directory keys like 'displayName' is impossible int he DB source with the extra properties query, at least in a way as simple as I expected it to be.