Create tables in database when new user is added

Ignition
1

I want to create some tables in database when new user is added using ‘User Management’ component. These tables will hold data for that particular user. As per manual, “extension function onUserSave() of ‘User Management’ is called when the save button is pressed when adding or editing a role. This code is executed in a background thread”. So I wrote system.db.runPrepUpdate("CREATE TABLE ? (Name VARCHAR(100) NOT NULL)", [tableName]) in this function, but it threw error. Is this because, “The “?” placeholder cannot reference column names, table names, or the underlying syntax of the query”

Is there any way I can add tables using code in background on new user addition?

Thanks!

2

Use this other function instead runPrepUpdate, and make the query string without placeholders and args. You can use string formatting or string concatenation to make your query.

Regards

3

The error has nothing to do with running the query for user management. The error message means ‘?’ placeholders can only be used for data that will go in a table. You have to construct the query string using normal python string methods or formatting to include your table name, including any required SQL identifier quoting. (That varies by database platform.) So, it wouldn’t be a Prep query in your case:system.db.runUpdateQuery("""CREATE TABLE "%s" (Name VARCHAR(100) NOT NULL)""" % tableName)

4

Thank a lot guys for your help. It worked!

I have couple of more questions about managing tables in MySQL.

  1. Since onUserSave() is called even when user information is updated, I want to be able to find if that table already exists. So I ran following code in onUserSave():[quote]query = “SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ‘new_schema’) AND (TABLE_NAME = %s)”
    values = system.db.runScalarQuery(query %(tableName))[/quote] But it threw error - Unknown column tableName in ‘where clause’. When I ran above query in workbench it returned ‘1’. How can I find if table already exists?

  2. I want to delete the table related to the user when user is deleted. There seems to be no extension function that is called when user is deleted. Can I use Custom Method for this?

Any help is greatly appreciated.

5

Well, the standard information_schema shows database structure as data. In other words, this would have been the place to use ‘?’ for tableName. Otherwise, you need to quote the table name with single quotes (because it’s a string constant in a comparison). Like so:query = "SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'new_schema') AND (TABLE_NAME = '%s')" values = system.db.runScalarQuery(query %(tableName))Or like so:query = "SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'new_schema') AND (TABLE_NAME = ?)" values = system.db.runScalarPrepQuery(query, [tableName])By the way, what requirement do you have that is driving the need for multiple tables? It really doesn’t scale – it’s a future maintenance nightmare. Why can’t you simply add a ‘userid’ column to the table structure? There’s almost certainly going to be a point where you’ll want to analyze whatever this data is across multiple users, and having it in one table makes it easy. (If you aren’t ever going to analyze it, why collect it?)

6

Sorry for late reply.

I want to store data like daily login/logout details, leaves, half days availed etc pertaining to each user. I was planning to create separate table for each type of data for every user. Now I will try your suggestion before moving ahead.

Thanks!