New to Ignition and scripting / SQL. I am building a project to learn and I have run into a issue that I can solve with a query and an addition to a script, but I want to see if that is the best option.
To give you some background, here is my table structure…
So I have a table component that sorts notes by…
binder_names >> topic_names >> page_names
To create a new page name, I take text and INSERT it into binder_page table with a named query and a script on a button.
The binder_page table allows duplicates because there might be multiple “Page 2” with different topics_id. I did this so that as the binder_notes table grows, the selection of the topic and page will not require querying the binder_notes table. I am curious on thoughts about this choice.
Anyway, I now find that I can have duplicate page_names with the same topic_id. Now, I can add a SELECT query to the button script to check for duplicate names on that topic_id just before I actually create the entry, but I am wondering if there is a different / better / less code way.
Any thoughts would be greatly appreciated. I can see that SQL and scripting will be a large part of any work done in Ignitions so I am doing all I can to learn all at once.
My question would be, do you need to allow duplicates? For example your binder_page, why do you need two entries with the same page and a topic_id? I’m assuming your binder notes, the topic_id there links to the one in in your binder page, if you just linked to your id in binder_page then you can have less entries in your binder page and just join the two based on the id number from binder_page being stored as topic_id in your binder notes. This is the same with linking to other tables too. Your main table should store data that links to its related tables, but the related tables can have entries used more than once to limit the data you have to store.
Even as you use these in objects, your query that brings in the data to display can do the joins to display it the way you want then if you edit it, you can use what is displayed to look back and find out if you need to create a new entry or just link to an existing entry.
I don’t need a duplicate page with the same topic_id, but I do need a duplicate page_name with different topic_id. So topic 1 and topic 2 may both have page 1.
In the same manner further up, binder 1 and binder 2 may both have topic 1.
So the main table is binder.notes. Binder_notes.binder_id links to binder_names.id.
binder_notes.topic_id links to binder_topics.id
binder_notes.page_id links to binder_page.id
In this manner I can query the binder_names table to populate a dropdown.
I can then use the a query to populate the topic_name dropdown WHERE binder_topics.binder_id = binder_names.id
and finally I can populate the page-name dropdown WHERE binder_page.topics_id = binder_topics.id
I hope this makes what I have more clear. Do I have too many tables? I know the binder.topics & binder.page tables will have some duplicate _names, but no duplicate entries.
I wouldn’t say you have to many tables. I just wanted to make sure your using them efficiently if that makes sense. Typically with relational tables, if items will be used multiple times then I would but them in their own table and link to them using their id(serial). If they are unique to the row on the main table, then I would keep them in the main table instead of linking to another table. That is where I was thinking you could be duplicating thing when linking from binder_notes to binder_page and binder topics since they but have an id which would be unique to each entry but then they have another id that I assume you create.
I was assuming that from your binder notes, you would do a join pulling binder_notes.page_id = binder_page.id, this would allow you to return your value for the binder_page.page_name so I wasn’t sure where the binder_page.topics_id came into play. In the same way I had assumed that binder_notes.topic_id would be the same as binder_topics.id allowing you to return binder_topics.topic_name. I hope that makes sense.
That comes down to how you do the entry. Depending on how your doing it there are a few ways to do it.
If your doing the full entry at one time, when you do the entry you can have it look to see if the entry exists in your binder.page table and if it does, use the existing entry. If it doesn’t then create the new entry and log the id into your binder_notes.page_id.
If you want to allow a limited select to the users, you can have a table set up to view what exist. If you need to add a new one, you can have a button that creates a new blank entry, then you would have a script in your table onCellEdited that updates your database, when the entry is updated.
If I knew more about what you were picturing for a user interface, I could probably give a suggestion tailored to it.
Ok, I have done some reading & testing and came up with this…
CREATE UNIQUE INDEX topic_idx ON test_index ((lower(topic_name)), binder_id)
This created an index of topic_name and binder_id. I included the lower() expression to help create actual unique topic names and not just versions of.
So the next question is looking ahead to the project, is there a way to capture the the “duplicate” error so that I can create a better error popup for the user? I do not see a specific exception to use in error handling.
This is the error I get…
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “topic_idx” Detail: Key (lower(topic_name::text), binder_id)=(topic 30, 31) already exists.
This is where you get into more intricacies of application development. Generally in web development many follow a MVC (Model View Controller) architecture that separates out the logic of the View (GUI) from the Controller (logic programming) and the Model (objects/database). You could spend years reading different methods and philosophies on this.
I like to try to be proactive with my user interfaces to ensure that checks are run BEFORE the user submits to ensure that the data rules are not violated. Doing it this way you have a lot of control over the error. In your case as the user fills out the data, you might run a query in the DB to verify if the unique constraint is violated and prevent them from attempting the insert.
Another way would be to try to trap the specific error and reformatted it in a more friendly message to the user using try/except blocks like Tyrel pointed out.
I am trying to understand what is happening here. I read up on system.util.getLogger so it looks like you defined a Logger as “logger”. I do not see the getClause() anywhere. Where can I find this? I assume it captures the text of the error message.
I also assume the logger.info is the actual message box.
My “plan” is to create the logger in the Window script so it is created when the window is open. Then capture it in the try…except each place I need it. Sound about right?
getCause() is a method defined on java.lang.Throwable, the base class of all Java exceptions. Exceptions can be ‘chained’ via causes - that’s how you can get a rich stacktrace with many ‘Caused By’ lines.