Best Way to do this in SQL for PosgresSQL and Sqlite

[Karen]

I’m starting new Xojo 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:

1. I need MaterialTable.LastLotCount to be incremented
2. 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?

Thanks
Karen

[npalardy]

Also is there a good (easy to use for a non-expert) free Mac GUI Postgres DB creation app?
DBeaver - its fugly ish but connects to everything on the planet since its java based and jdbc adapters exist for pretty much every known db on the planet

In sqlite you can use an autoincrementing field with newer versions

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
     .....
);

In Postgresql a SERIAL does this (it ends up creating some other things that tie the column to an autoincrementing sequence)

CREATE TABLE table_name (
   column1 SERIAL,
   ....
);

[Karen]

@npalardy

DBeaver - its fugly ish but connects to everything on the planet since its java based and jdbc adapters exist for pretty much every known db on the planet

Thanks, for that suggestion.

Not sure why you mentioned AutoIncrement… I always use that for my Primary key but it does not help with what I am trying to do, which I think you misread.

After doing some research it looks like I can’t do it with an insert trigger as I can’t modify a field in the new record.

I also can’t use the relatively new SQLite Generated (computed) field capability, as you can only use other fields in that record and can’t select a value from another table based on a field in the new record.

On Stack Overflow the only way suggested to do that was using a view- which I would rather not do.

-Karen

[npalardy]

Very possibly misread bits

When I insert a record in the Lot table for a material:

1. I need MaterialTable.LastLotCount to be incremented

serial or autoincrement on that column
you could also derive it by a query (ie. query for the same material key0 & type and make the insert use LotCount + 1) but thats kind of messy

2. LotTable.LotNo to be formed from:
   MaterialTable.Type + 4 Digit Hex Number of MaterialTable.LastLotCount

when you insert into “MaterialTable” you want a new row inserted into LotTable ?
That can be done in triggers
Probably need an AFTER trigger so the new row exists in Material, then the trigger adds a new row to LotTable based on that data
Something like this for sqlite

create TRIGGER after_material_insert after insert on material 
      for each row BEGIN  
           insert into lottable ( materialKey, lotno ) values ( new.key0, new.type || new.lotCount ) ;
      end ;

But I’ll confess I have not tried this so syntax could be off

Something similar can be done for postgresql
see PostgreSQL: Documentation: 13: CREATE TRIGGER
again an “after” trigger probably works best

[Karen]

@npalardy

when you insert into “MaterialTable” you want a new row inserted into LotTable ?
That can be done in triggers
Probably need an AFTER trigger so the new row exists in Material, then the trigger adds a new row to LotTable based on that data
Something like this for sqlite

No, when I Insert a a row into the LOT table , I want to

1. Increment the MATERIALTable.LastLotCount for THAT material
2. Use that value and MaterialTable.Type to form the lot ID

It turns out in SQLite I can what I want in an After Insert trigger on the lot table… just not the way i expected it would be done.

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

I guess I’m getting old… I should have realized yesterday!

-Karen

[npalardy]

what I get for not reading closely
but yeah you could use an after trigger on LOT to insert as you describe

I have always heard that best practice was to put as much of the logic as possible into the database.

Is the no longer considered the case?

Welllllll … it DOES tie you to a specific DB & implementation
So in many cases its better to have a multi tier config
Your app talks to “business logic server” that then talks to the DB which is JUST a data repository
As IF you EVER have to port to a different DB you dont have to rewrite tons of triggers & stored procs etc

BUT you DO want the DB to do what it does best - deal with relations and sets of data