DB Tables and Unique or duplicate values

Ignition
1

Hi all,

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…

image
image301×597 28.6 KB

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.

Thanks, Steven

2

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.

3

@bpreston,

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.

Thanks, Steven

4

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.

5

I guess the question still remains, what is the best way to prevent creating duplicate binder.page entries?

6

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.

7

This is what database Unique indices are for. That is the only reliable way. Your DB insert/update will fail if the unique index is violated.

2 Likes
8

@pturmel

Here in lies my problem. My entry for the page_names will actually be an id(serial/unique), a page_name, and a topic_id.

The page name cannot be unique because the same name could be used with many topic_id.

The row will be unique but not the name or the topic_id.

Am I looking at it wrong?

I can easily compare the new page name to to the table prior to writing it, but I was curious if my table structure could be improved.

Thanks for the help,

Steven

9

A Unique index in a DB can have multiple columns–the combination must be unique.

1 Like
10

A unique index can be on a combination of columns – that won’t prevent duplicates in any single column, but it will prevent that combination from being duplicated.

2 Likes
11

@pturmel & @KathyApplebaum,

I was wondering that after I posted. SO, how do I do that? That would be a useful feature and perfect for this case.

Thanks, Steven

12

Look up your database’s CREATE INDEX statement. Syntax varies a bit from DB brand to brand.

Consider reading your DB’s online documentation cover-to-cover. Not for retention, but to give you an overview of what’s possible, so you can use appropriate search terms later. (:

1 Like
13

Thanks @pturmel. I am spending a LOT of time going through manuals!! Between Ignition, PostgreSQL/SQL, and Python there is a never ending amount of knowledge.

I made it through the IU course in 10 days but still find myself going back through it looking for things I remember seeing but forgot how to do.

I am used to building and installing equipment, not programming it!! But times are a changing and I will as well.

Thanks for all the help and suggestions. I really appreciate it.

Thanks, Steven

1 Like
14

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...
message tab...
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.

details tab...
GatewayException: 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.
caused by Exception: 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.

Ignition v8.0.16 (b2020082513)
Java: Azul Systems, Inc. 11.0.7

I do not need the data from the error as my popup will be generic "you cannot have duplicate names".

Do I just assume that if I get an error it is a duplicate error?

Again, thanks for all the help!!

Steven

15

I used this at one point to capture that error message for myself. You could modify this to output something to the user.

except Exception as e:
    msg = str(e.getCause())
    logger.info('except javalang: {}'.format( msg)
    if 'Cannot insert duplicate key' in msg:
        logger.info( 'duplicate')
16

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.

17

@ryanjmclaughlin,

I thought about doing this. Would be easy enough but I thought I would ask the question in case there were easier or more "standard" way of doing it.

In this case, I like @tyrel.parker idea for two reasons.

  1. I have multiple INSERT INTO to check. If I capture the error I can use the same code for each one.

  2. I need to learn about Logger's and how to capture and repackage.

I am creating my first project just to learn so this is great stuff!!

Thanks for the help,

Steven

18

@tyrel.parker,

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?

Thanks so much for the help,

Steven

19

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.

20

@PGriffith

Ok,...not sure what all that means but I am going to give it a test drive and see what happens. Looks like what @tyrel.parker laid out will work so I am going to try it.

Thanks!!

Steven