So previously we were using Data Set tags to store are user specific data but we ran into a problem where is we wanted to add a new setting for are users we ether needed to go back and edit all of are scripts that looked at the Data set tag to included what ever new row was added so ive found that that can be fixed with using documentation tags witch are just a JSON file from what i can tell but im wondering if anyone has any input on why this might not be a good idea or any better ways to make something like what im talking about.
Save to/Load from DB and stuff the data in a perspective session custom property. Load on session startup or login. Write on change, or maybe on session shutdown.
1 Like
Hmm ok so you recommended to save that data to like are SQL data base and pull it in from there Not a bad idea but im not vary familiar with that type of data manipulation and how I would store it and call it to be used so its probably better i stick with just using something that is on a tag.
Don’t let an unfamiliar topic scare you off.
This is actually a perfect application to learn the way the DB works and how it is applicable here.
For beginners I would recommend using Named Queries to insert data into and get data out of a DB.
If you are already using a dataset for your scripts, you will soon realise that the DB queries can easily spit out a dataset containing exactly the data you want in a dataset, then you can use your existing scripts to read this dataset.
Consider setting up a SQLite DB:
It's basically a file that is typically stored on the same filesystem as the Ignition gateway. Very easy to set up, very easy to interface with, and easy enough to migrate to other databases in the future.
Heed the warnings in the link above...try not to use SQLite for a production historian, etc. However, can be a great first step in migrating from dataset tags to a DB.
This. Do not leave anything production-related in SQLite. Use it to learn SQL, but for production, learn to install and use a real database. I recommend PostgreSQL and its derivatives, but even MS SQL Server (boooo! hissss!) is better than SQLite.
2 Likes
Well we are currently using MySQL for are history so I assume I would just make a folder on are MySQL server to store this all to. I can spend a bit of time messing around with it to see if I can get an idea of how to get that working and stored properly/ where to even start with trying to use SQL. Thanks for the recommendation.
No, you would create a database and in that database you would create the required tables. There are endless articles and video tutorials on the web.
A version of this..yes. Except, 'folders' do not exist in the context of the DB. Instead...
As @Transistor alluded to, it would be beneficial to separate "user-entered data" from other "Ignition Historian-entered data". You could do this by creating a separate database on the same server, where you have more flexibility in creating the tables without risk of stepping on anything that Ignition Historian has already created (or will want to create, in the indefinite future).
Spend time on how to best-structure the data that you have (develop a schema) to prevent future issues like you mentioned in your original post:
If you are comfortable with it, post your findings (desired schema) here before implementing to get insight from members of this forum.
Alright thanks im still trying to figure out how I would even layout the Schema to be able to hold the data for each of are users without needing multiple schemas per user well leaving it open to add more columns later when needed.
If you share a bit more of what you are wanting to store and what you do with it in ignition you may get some more specific help here.
I do not believe you would need separate schema(s) for each user, but rather probably just one and then create a few tables within that schema, you may only need a single table for the user settings.
I really like the tutorials on W3schools and suggest you give some of those a look.
Ok after a bit more looking into how the table work and if you are able to add column to a table after its been created without it braking everything that is already stored i think they are the right way to go and yes i believe i should just need one table per user when storing there data if im able to make list withing a column which it looks like im able to do. ill include a photo of how i have my documentation tag setup that im trying to mimic but i believe i have figured out how to structure the table to i will only need one per user
No, you create one database table for all users. You need something like the following data structure:
- Username String
- AssetObject String (not sure what this level is for)
- AssetName String
- AssetGeneration Int
- canEdit Boolean
- isVisible Boolean
- canConfigure Boolean
- isNotified Boolean
This way each record is one line in the database and you can retrieve any single or multiple records based on a query and display them in a Perspective table or however you wish.
Tip: Please write properly on the forum with correct capitalisation, punctuation and spelling (brake/break, there/their, im/I'm, ill/I'll). The Jython interpreter wouldn't tolerate it; why should we? Not doing so makes your posts difficult to read and gives a very poor impression to your readers.
Sorry about the bad spelling I have dyslexia so i try my best but this online forums are hard for me to participate in for that exactly. ill take a look closer to what you have said and see if i can figure out how to have all the assets in one line per user as each asset needs there own value for the isNotified isVisable and so on.
2 Likes
Not 1 line per user, 1 line per equipment, per user. So if you had 20 users and 20 machines, you would have 400 rows in your table. Fairly standard tall table format. Fetch all rows with the logged in user's username to pull into Ignition.
Unfortunately, everything we've recommended so far doesn't fix your original issue...it just sets you up for success in the future. Officially, you should be able to accomplish what you're after with either a dataset tag, a document tag, or a SQL query. That said, there will need to be 'something / somewhere' which defines how to display the data that is stored somewhere. Adding a column to a table should not be difficult with any of these methods. If you are persistent on using existing tags for storing user data, then perhaps we need to focus more on the specifics of how you are using the data.
Would you be able to share a screenshot of a couple of rows of your dataset tag? Feel free to redact any sensitive data therein before sharing.
Additionally, one (or more) of the scripts that are looking at the dataset tag, and what an output from the script should look like.
I'm especially curious if there any lines that are referencing columns by their index instead of their name..
At this point in time ive decided and been give the go ahead to learn enough about SQL to use it so I will be move on in that direction and at this point in time it seems like the next big step is just getting a layout for the data base and start messing around with it thanks for all the ideas and suggestions on how to properly store user data.
2 Likes