We are trying to figure out how to enforce serialization uniqueness per part type across multiple machines/stations making multiple parts.
My thought on how to approach this was to have globalId, partSerial and partType in a main table, and let my business logic actively pre-generate serials and do duplicate checking.
Any associated data for that part would be in secondary tables that join off the globalId column. The global Id would be entirely hidden from users, they would only see Part Serial and Part Type when interacting.
The idea would be that Ignition would pre-queue/generate some ID's based on our serial generation rules and hold them in a buffer. Part of the generation process would be to double check that they do not exist for that part type in the main table.
When a machine requests a serial for a part, ignition would hand it the next available serial in the queue for that part type. First come first serve. Once the queue got below a predefined amount, the system would generate some more serials and place them at the end of the queue.
Possibly near the end of the day, I could preload a 'nextDay' serial queue and have that ready to be pulled from as soon as the time rolls over. (Our serialization method is partially based on the date)
On confirmation of handoff to the machine, Ignition would then take that serial and insert it into the main table along with the type and insert any associated data available into the appropriate tables.
Any data from further in the process would be able to insert off either the type-serial combo or the globalId.
Main issues with this is I need some way to sync these queues between backup and master, and handle fail over without introducing a duplicate value.
This definitely feels like its reaching a bit beyond what Ignition is supposed to be doing but we don't actively have any other system available to handle this. If anyone has any suggestions I'm all ears.
Make the database do it on demand, via insert to a new part serial table to which the requester supplies origin information and the DB returns a primary key. Competent databases have actual sequence types for this purpose. Use the DB's own technology for clustering/replication/availability outside Ignition.
You simply cannot make an Ignition redundant pair do this.
If you need a multi-column primary key, you will want to use Oracle or PostgreSQL and my Toolkit's system.db.runPrepInsert().
Against my will we are stuck with MSSQL. What options do I have there.
Though, this might be enough ammo to force them to give us a Postgres DB (Our main cluster is MSSQL so that might be interesting. Our IT has leaned heavily into MS and AD)
edit: MS looks like they have composite primary key support, so maybe not completely screwed.
In such a case, I'd make a separate table for each part type for the sole purpose of treating its auto-increment column as the serial. Use an insert trigger on each part type table to copy the whole row + constant part type ID to the main table. Carry on with the main table. (Can prune the separate tables after the main table is updated. Perhaps place the part type tables in their own schema to avoid misuse.)
It isn't the support for composite primary keys themselves, but being able to retrieve them automatically. Their JDBC driver only returns one column, and coerces it to double. Utter Fail.
outstanding. How safe/unsafe is it for me to start transaction, insert in one query, and immediately fetch top key in another, then close transaction. Or is it just full stop can't fetch the composite key at all?
I'm gonna go on a limb and say this is not compatible with serials generated according to a specific format, correct? Our default is 5 digit julian date (YYDDD) + zero padded item number (ie, 12th part made today is 000012).
Or at least I would have to keep the entire table around to allow it to do a uniqueness check. (not the worst, just serial and datetime)
Anyways, I'll leave it until Next Week™ but I'll try to rattle some cages to get a competent database configured for this. (I'm personally leaning towards PostgreSQL, only because I've played with it a bit).
If I cannot convince them to switch or configure one, I'm following this:
Zero padded auto-incrementing number per table. Pack typeId (numeric) will go in front and be separated with a - for easier delineation when on a barcode or sticker on the floor. Might also include timestamp of record generation, if I have the space.
Building on that option, is there any safe way to program the automatic addition of a table and associated handling if we add a part type down the line? Or will it always require engineer configuration?
I don't see why not. I don't know of any DB brand that would allow table creation to race. You could cache table details from information_schema to check for part-specific table existence in the fast-path.
What would the safest way to associate types with their actual DB table names be. I'm cagey about the PLC side being programmed or configured with the table names and would prefer it to be abstracted to a number for the type Id.
I'm guessing a helper table that just has incrementing column for type id and a second column for the full table name as it appears in the schema? Possibly updating on any change to the separated schema with the serial generation tables? This table would be 100% read only from business logic side.
yeah, secondary helper table seems like the traditional/normalized way to do this, then you can even just set up a view for your DB that joins type to name so you see name in those queries
Against my will we are stuck with MSSQL. What options do I have there.
outstanding. How safe/unsafe is it for me to start transaction, insert in one query, and immediately fetch top key in another, then close transaction. Or is it just full stop can't fetch the composite key at all?