I’m starting new DB based project i need to get done ASAP and am planning to use PostgresSQL and/or SQLite.
I was never an SQL expert, and what SQL I do know is rusty.
I have mostly used SQLite for pretty simple stuff and never used Posgress outside of fooling around with it a little 10+ years ago. (so will likely have more question about it later)
I could easily do what I need to in Xojo code (which is how I handled things in the past), but I know it really should be in the DB
So I was wondering if anyone can help me with how to do this in Postgres and Sqlite
I would have a Parent table:
MaterialTable
Key0: Int64
Type: Single Character
LotCount: Int64 - Count of lots created for THIS material. Used to make the lot ID
[other fields]
And child table:
LotTable
Key0: Int64
MaterialKey: int64 - Key0 of material table
LotNo: String - Formed from MaterialTable.Type and MaterialTable.LotCount
[other fields]
When I insert a record in the Lot table for a material:
I need MaterialTable.LastLotCount to be incremented
LotTable.LotNo to be formed from:
MaterialTable.Type + 4 Digit Hex Number of MaterialTable.LastLotCount
In Sqlite I assume this could be done by an insert trigger, but I am unclear on the details.
I am also sure it could be done in Postgres with an Insert trigger too but have no idea of the details there at all.
I would really appreciate some example SQL for each DB to get me going!
Also is there a good (easy to use for a non-expert) free Mac GUI Postgres DB creation app?
From the research I have been doing this can’t be done directly in Sqlite… (hopefully it can be in PostgreSQL)
As far as I can see it can not be done in SQLite using:
An Insert Trigger because you can’t assign anything to the fields in the new record
Generated (Computed) Fields which SQLite now has, because in them you can’t do a Select from another table … You can only use the field values from other fields in that record
AND Sqlite does not have stored Procedures
The only answer I found on line to Store the value in a separate table via the Insert trigger and then use a View to make it look like it is in the same table… But I want to avoid that.
Are you sure about this @KarenA ? You have the new. and old. field prefixes to reference values. Additionally there is BEFORE and AFTER INSERT triggers that give you the right timing opportunity to do what you want potentially ?
From what I read on-line for SQLite, it looked like New.Field is not assignable either before or after for Insert triggers at least, and old.Field is only available for Update triggers.
These two columns are throwing some red flags for me.
MaterialTable.LotCount: Absent a very good performance reason, which I doubt applies here, LotCount should not be a static, stored column. It should be a virtual generated column (Postgres terminology), or part of a view, or just part of the select statement when it’s needed.
LotTable.LotNo: It looks to me like LotNo is, or is going to be, some form of human reference. I would probably make it MaterialLotNo Int64 and just set it to MaterialTable.LotCount+1 at insert. (Again, LotCount should be generated or from a view.) If it’s going to form the basis of a human identifier (MaterialTable.Type + 4 Digit Hex), I would then generate that value via a virtual generated column, a view, or in the select statement when it’s needed.
If you are not trying to compute/update multiple columns at insert, but just a LotNo or MaterialLotNo column in the record you are inserting, then I think you fall back behind the line where you ‘must’ use a trigger. A trigger might be preferable. But if you can’t use one because of a SQLite limitation, your insert statement could use a select to get MaterialTable.LotCount+1.
How so? It depends on how many of lots of that specific material have been created. A select to find the count for highest material Lot ID in the Table won’t work if a for some reason the previously created Lot for that material was deleted for…
That is why I decided to store a count independent of the of the lot.
A supporting table for incrementing and then using a view would work, but I wanted to avoid a view for simplicity as well as the need for a table that Just stores the MaterialID with the Lot Count when it could be in the Material table.
[quote]LotTable.LotNo: It looks to me like LotNo is, or is going to be, some form of human reference.
[/quote]
Yes Exactly.
I wanted to have that ID creation logic reside in the DB to ensure consistency as I am thinking of writing few types of Client apps. True it does not HAVE to be that way… but it would be best if it could be.
I would love to use a generated Column but I don’t see how that can be accomplished in SQLite which limits you to only using field values from that record … (though it sounds like you are saying it could be done in PosgresSQL).
A select to find the count for highest material Lot ID in the Table won’t work if a for some reason the previously created Lot for that material was deleted for…
Since you’re showing the value in a human readable ID I assumed it was historical, i.e. that a LotTable record could not be deleted. If LotTable records can be deleted then a count of records by material type cannot serve as the basis for an ID. But in that case the number in the human readable ID is not necessarily valid either, depending on what you’re trying to convey to the person reading it. It may link back to the correct record but mislead them as to the number of lots for said material. So…
If you just need an incrementing, unique number for the human readable ID that is not related to physical counts in the real world, use an auto incrementing field in LotTable (which could be Key0). For your final human readable ID you might have A1 and A257 because of B2-B256, but who cares? The auto incrementing field solves this problem in the database, regardless of database type, and should never present a concurrency issue.
If you want that number to reflect lot counts by material, we come back to the question of deletion and what that number really means to a human. But if after considering this you want an incrementing value by material type, AND you will be using SQLite…I would try having a MaterialLotNo Int64 column in LotTable. This way I could select max(MaterialLotNo)+1 where MaterialKey = keyValue as part of the Insert statement.
I wanted to have that ID creation logic reside in the DB to ensure consistency as I am thinking of writing few types of Client apps. True it does not HAVE to be that way… but it would be best if it could be.
If you have to jump through hoops to work around SQLite’s trigger limitations it may not be worth it. That said, I’m struggling to think of the last time I had to do something like this, much less do it in SQLite, so someone else might have a better solution that works as a SQLite trigger.
First, look at Valentina Studio. It has some really nice tools for visualizing and updating a database.
There are two ways to get what you want, and which you choose really comes down to how many records you expect to query at any one time. If it’s going to a be a lot, storing the values statically via a trigger is the better way as you can index the values. Otherwise, a View will give you more flexibility.
Since you asked about the former though, these are the steps.
Create the tables with a foreign key reference.
Create function for the trigger. Be sure it handles both INSERT and DELETE.
CREATE OR REPLACE FUNCTION child_table_trigger ()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
DECLARE
use_rec LotTable;
is_insert BOOLEAN;
inc BIGINT;
lot_count BIGINT;
material_type TEXT;
BEGIN
IF TG_OP = 'INSERT' THEN
inc = 1;
is_insert := true;
use_rec := NEW;
ELSE
inc = -1;
is_insert := false;
use_rec := OLD;
END;
UPDATE
MaterialTable
SET
LotCount = LotCount + inc
WHERE
Key0 = use_rec.MaterialKey
RETURNING LotCount, Type INTO lot_count, material_type;
IF is_insert THEN
use_rec.LotNo := material_type || lot_count;
END IF;
RETURN use_rec;
END $$;
Then assign the trigger to the child table:
CREATE TRIGGER child_table_trigger
BEFORE INSERT OR DELETE
ON LotTable
FOR EACH ROW
EXECUTE PROCEDURE child_table_trigger();
(None of this is tested, just off the top of my head.)
Keep in mind that, with this technique, you can get duplicate LotNo.
Insert into LotTable for material X and get a LotNo “X100”.
Insert another for “X101”
Delete the first record, reducing the count to 100.
Insert another, and it’s again “X101”.
You’re better off using the LotTable.Key0 to form the LotNo.
Thank you Daniel. I understand what you are saying…
I should explain why I want to create lot numbers this way. If all the data entry was via barcode after the lot was created, I would have just taken the route you (and Kem) suggest…
But that is not the case here… This is for a stopgap system for use until we get big enough justify the cost for a fully electronic commercial software package… Which could be a few years.
The reason the LotID is based on the number of lots created for that material, is not to indicate the actual # of lots previously created for that material (most people can’t read hex!), but to keep the number of digits for lot numbers to a minimum.
This way the Lot numbers are unique within a material ID, but not across materials so they require fewer digits then they would If the lot# was based on the total # of lots created for all materials (the Key0 of the lot table). What is unique here is the combination MaterialID and Lot#
The reason for doing it this way is to minimize data entry errors … which tend to increase with increasing number of digits.
The lot numbers need to be hand written on paper batch records, which will specify the Material ID for the material to be used. If we had electronic batch records it would be a different story!
I want to make life easier for those who are doing the process and compiling the data afterwards (some of which I have to do!), while also minimizing the chance for transcription errors.
In any case, for Sqlite it is looking like I will need to use a view or create the Lot ID in the Xojo code.
Thanks Kem, The will really help when I get to the Postgres version!
I use the free version for Sqlite DBs I create… I like the diagrams it can create!
I did not realize you could use it with Postgres for free - I thought that was only for Sqlite.
BTW For Sqlite it looks like Valentina Studio does not yet directly support the relatively new SQLite Generated (Computed) column feature. I wound up creating a table that uses that feature in SQL myself
-Karen
I turns out I was wrong… I can do it with an SLQlite after Insert Trigger, just not the way I thought it would work. In either before or after Insert triggers I could not do:
Set New.Fieldname = SomeValue
But in an AFTER Insert trigger I can do:
UPDATE TableName Set FieldName = SomeValue WHERE key0 = New.Key0
So Between this and what @Kem_Tekinay posted ,I know I can do what I want to in both SQLite and Postgres…
I’ guess I’m getting old… I should have realized yesterday!
I’ve come back from using triggers. especially between multiple “same” databases.
think of the future modifications you may have to do in your triggers.
if you have more tables it becomes very difficult to not forget one trigger somewhere in one database…
I would better make the trigger code into one xojo method, that you call from when you write the records from sqlite or postgres. that way if you have to modify it, you have to do it at ONE place.
that’s fondamental for future maintenance.
