Can WebLoginPE handle multi-select[ ] fields in a normalized db?

After thinking about this, I’m seriously considering simply letting WebLoginPE handle all user input into its tables (e.g. web_user_attributes_extended), then simply writing a back-end cron job that copies that information over to my normalized database. That way I can make WebLoginPE work for me without too many modifications (or plugins) and I don’t have to deal with tweaking my php classes to handle WebLoginPE’s input. It’s arguably more secure, because then MODx’s database handle won’t have access to my normalized database (which it can’t really read anyway). A Perl script using the DBI module would be really nice for this, especially if the normalized DB were using something other than MySQL... mmm....

E.g. my backend script could read WLPE’s ’||’ separated multi-values, and then loop through them to insert new rows into my normalized schema.

This option may not work if user data is being inserted into the database from something other than MODx, because then you’d have to have a syncing function to resolve redundancies or incomplete record sets.

Anyhow, this new approach seems easier and quicker, so I’m going to try it. I could even trigger the transfer script using a plugin... then I wouldn’t have complex plugin code.

Anyone have thoughts?

What about using stored procedures triggered on update/insert/delete of the extended attributes table?
http://database-programming.suite101.com/article.cfm/mysql_stored_procedures_and_triggers

The stored procedures could update the normalised tables with the data inserted into the extended attributes table, then delete it from the original to avoid redundancy.

Or you could bite the bullet and write a plugin to do it (although you may need to hack the wlpe event hooks in the the class to get it to work).

Yeah, I thought about using stored procedures... the only problem with this particular case is that given how WebLoginPE stores mult-select values (e.g. blue||black||gray), it would require the use of regular expressions and loops, and while POSSIBLE in MySQL, they’re more easily implemented and debugged in code (PHP or Perl).

I think there’s also the possibility that this runs on a server running MySQL 4... and I don’t think version 4 supports stored procedures.