Recent

Author Topic: Smart way to get changes on Db table.  (Read 2443 times)

rvk

  • Hero Member
  • *****
  • Posts: 6199
Re: Smart way to get changes on Db table.
« Reply #15 on: February 13, 2024, 11:09:30 am »
Of course you can do that since in SQLite you can query the meta data and take snapshots from that. And I guess that works. SQLite <> IB
You still didn't understand the opening question.

HOW do you determine which FIELD changed when posting a record.

This has nothing to do with snapshots itself. It is about determining WHAT FIELD changed. So the process of determining the changed fields itself.

Can you please explain HOW you can get SQLite to spit out WHICH FIELD has changed??

Thaddy

  • Hero Member
  • *****
  • Posts: 14614
  • Sensorship about opinions does not belong here.
Re: Smart way to get changes on Db table.
« Reply #16 on: February 13, 2024, 11:25:42 am »
step 1:
Query the metadata:
Code: SQL  [Select][+][-]
  1. SELECT name, SQL FROM sqlite_master WHERE TYPE='table' ORDER BY name;
step 2: Create a snapshot from sqlite_master table
step 3: start a new transaction
step 4:
Code: SQL  [Select][+][-]
  1.  ALTER COLUMN ... etc whatever colunn you want TO CHANGE
step 5: commit
step 6: take a new snapshot from the  sqlite_master table
step 7: use the same differnce between the two snapshots

It may require to create a new table before the name change that already has the new name and copy all data to the new table. But I guess that should work.
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Smart way to get changes on Db table.
« Reply #17 on: February 13, 2024, 11:29:42 am »
step 1:
Query the metadata:
Code: SQL  [Select][+][-]
  1. SELECT name, SQL FROM sqlite_master WHERE TYPE='table' ORDER BY name;
step 2: Create a snapshot from sqlite_master table
step 3: start a new transaction
step 4:
Code: SQL  [Select][+][-]
  1.  ALTER COLUMN ... etc whatever colunn you want TO CHANGE
step 5: commit
step 6: take a new snapshot from the  sqlite_master table
step 7: use the same differnce between the two snapshots

It may require to create a new table before the name change that already has the new name and copy all data to the new table. But I guess that should work.
Uh, Thaddy,
TS wants to know which Column changed its VALUE, not the Column-Name (or whatever else)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

rvk

  • Hero Member
  • *****
  • Posts: 6199
Re: Smart way to get changes on Db table.
« Reply #18 on: February 13, 2024, 11:29:53 am »
step 4:
Code: SQL  [Select][+][-]
  1.  ALTER COLUMN ... etc whatever colunn you want TO CHANGE
You STILL don't understand the question  >:D
Please read the opening question:
after commuting changes, Is there any way to know that only the name field changed.

So TS is after how (s)he can determine the columns of your "etc whatever colunn you want to change" in a 'smart' way.
Nothing else.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Smart way to get changes on Db table.
« Reply #19 on: February 13, 2024, 11:32:07 am »
step 4:
Code: SQL  [Select][+][-]
  1.  ALTER COLUMN ... etc whatever colunn you want TO CHANGE
You STILL don't understand the question  >:D
Please read the opening question:
after commuting changes, Is there any way to know that only the name field changed.

So TS is after how (s)he can determine the columns of your "etc whatever colunn you want to change" in a 'smart' way.
Nothing else.
I think Thaddy's confusion comes from the fact, that TS has a column named "name".....
Quote
Hello.
Let say that I have an sqlite3 table
Code: Pascal

    t1 (id integer, name varchar(20), address varchar(50) )
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Thaddy

  • Hero Member
  • *****
  • Posts: 14614
  • Sensorship about opinions does not belong here.
Re: Smart way to get changes on Db table.
« Reply #20 on: February 13, 2024, 12:35:59 pm »
Would this work, given my first example:
Code: SQL  [Select][+][-]
  1. SELECT
  2.     CASE WHEN snapshot1.rollno <> snapshot2.rollno THEN 'rollno' ELSE NULL END AS changed_rollno,
  3.     CASE WHEN snapshot1.name <> snapshot2.name THEN 'name' ELSE NULL END AS changed_name
  4.     FROM snapshot1 JOIN snapshot2 ON snapshot1.primary_key = snapshot2.primary_key;
not able to test now. Will do that later. Note the query can become cumbersome on tables with many fields. You have to repeat the CASE for every field in the table. It should return the changed fields for each row.
« Last Edit: February 13, 2024, 12:43:53 pm by Thaddy »
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Smart way to get changes on Db table.
« Reply #21 on: February 13, 2024, 12:43:22 pm »
Would this work, given my first example;
Code: SQL  [Select][+][-]
  1. SELECT
  2.     CASE WHEN snapshot1.rollno <> snapshot2.rollno THEN 'rollno' ELSE NULL END AS changed_rollno,
  3.     CASE WHEN snapshot1.name <> snapshot2.name THEN 'name' ELSE NULL END AS changed_name
  4.     FROM snapshot1 JOIN snapshot2 ON snapshot1.primary_key = snapshot2.primary_key;
not able to test now. Will do that later. Note the query can become cumbersome on tables with many fields. You have to repeat the CASE for every field in the table. I should return the changed fields.
Again: Snapshot ONLY WORKS with Journal-mode WAL

Checking for changed VALUE's for a Field is usually done via Auditing-mechanism, which has to be implemented by yourself.
A journal-mode WAL in SQLite is comparable to the binary log in MySQL, but even there a dba can decide: Nope, binary log, only goes back to the last successful Transaction.

That said:
There is only one "smart way" to get what TS wants: Implement an auditing mechanism yourself.
Easily done with Triggers.
Period!
It's not dependant on journal-mode or whatever else, and you're flexible to the nth degree (add/remove columns/tables, whatever, during the lifetime of the program/database)
« Last Edit: February 13, 2024, 12:47:57 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Thaddy

  • Hero Member
  • *****
  • Posts: 14614
  • Sensorship about opinions does not belong here.
Re: Smart way to get changes on Db table.
« Reply #22 on: February 13, 2024, 01:08:37 pm »
Then it will still work on local databases in wal mode and exclusive locking.
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Smart way to get changes on Db table.
« Reply #23 on: February 13, 2024, 01:17:10 pm »
Then it will still work on local databases in wal mode and exclusive locking.
True, but you have to ask yourself: What's easier?
Rely on a journal-mode, that might change in the future, or the way i described?

And don't forget: The "journal" is a separate file.....
... i'm doing spring cleaning on my harddrive......
.... what's that? a "journal"? don't need that... let's delete it.....
....
KABOOM
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Thaddy

  • Hero Member
  • *****
  • Posts: 14614
  • Sensorship about opinions does not belong here.
Re: Smart way to get changes on Db table.
« Reply #24 on: February 13, 2024, 03:02:03 pm »
well, store the snapshots in a database table?
bitrate is always calculated like this:sample rate * bitdepth * number of channels.

Zvoni

  • Hero Member
  • *****
  • Posts: 2364
Re: Smart way to get changes on Db table.
« Reply #25 on: February 13, 2024, 03:05:28 pm »
well, store the snapshots in a database table?

quoting myself
Quote
There is only one "smart way" to get what TS wants: Implement an auditing mechanism yourself.
Easily done with Triggers.
Still no need for any specific journal-mode
Even works with InMemory-Databases (as "unusual" that might be)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018