Smart way to get changes on Db table. lazarus / 63-Database ---------------------------------------------------------------------- BSaidus · 2024-02-12 15:41 Hello. Let say that I have an sqlite3 table Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i t1 (id integer, name varchar(20), address varchar(50) ) Is there any smart way to know changed records after posting data ? I mean : Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   // let record 1 has a values       id          = 1,     name     = 'toto',   address  = '33 av. lebhayer' I changed the name value to Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   // let record 1 has a values       id          = 1,     name     = 'tata',   address  = '33 av. lebhayer' after commuting changes, Is there any way to know that only the name field changed. For now, I proceed like this.   when creating form, I get the initial value before any changes to variable, then after omitting changes I compare current values with the first ones I get in the FormCreate. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i    // get_Initial_values   // POST changes   // get_Current_value >> Diff >> get_Initial_values   Thank you ---------------------------------------------------------------------- cdbc · 2024-02-12 17:33 Hi I think SQLite has something called 'rows_affected' tucked away somewhere deeper then the dataset, maybe in TSQLite3Connection?!? Happy hunting Regards Benny ---------------------------------------------------------------------- Hartmut · 2024-02-12 18:07 What cdbc means, is function TSQLQuery.RowsAffected, if that is, what you was looking for. ---------------------------------------------------------------------- TRon · 2024-02-13 02:56 SQLite seem to support a so called snapshot  (when support for it was compiled in) which can be used to differentiate between two points in time. I do not have any experience with SQLite and snapshots though so can't provide practical example. ---------------------------------------------------------------------- Thaddy · 2024-02-13 07:10 Quote from: TRon on February 13, 2024, 03:56:50 amSQLite seem to support a so called snapshot  (when support for it was compiled in) which can be used to differentiate between two points in time. I do not have any experience with SQLite and snapshots though so can't provide practical example. That is an interesting feature that I did not know about in SQLite. I found a good example on the web. Suppose we have two tables, student and new_student. Then we can create a snapshot like so: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE SNAPSHOT snapshot1 ASSELECT student.rollno, student.name FROM studentUNION ALLSELECT new_student.rollno, new_student.name FROM new_student; Now suppose we have created two snapshots over time, we can query the difference like so:Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT * FROM snapshot1 EXCEPT SELECT * FROM snapshot2;That was really easy. I will use it more often. Tnx for the tip! ---------------------------------------------------------------------- TRon · 2024-02-13 07:26 Thank you for providing an easy to understand example Thaddy ---------------------------------------------------------------------- rvk · 2024-02-13 07:30 Quote from: BSaidus on February 12, 2024, 04:41:52 pmafter commuting changes, Is there any way to know that only the name field changed. What the other seem to miss about this question is that you want the changed FIELDS. Not if there are any changed records. No, there is no way to get that from SQL itself. The only way is to see if the field is changed before posting by looking at something like TDBEdit.Modified. But you would need to do that at the right time. ---------------------------------------------------------------------- TRon · 2024-02-13 07:51 Quote from: rvk on February 13, 2024, 08:30:44 amNo, there is no way to get that from SQL itself. Well, in case a snapshot is not helpful and with a bit of imagination you could end up with something like this. Overkill perhaps but imho so is the original question ..... QuoteBut you would need to do that at the right time. .. because if you do it at the right time (and thus avoid making things more complicated than they already are) then you would either take your approach or know beforehand what you are going to change. A simple check before posting is enough to set a state. And my SQL is a bit rusty but isn't it possible to manage that with a trigger ? ---------------------------------------------------------------------- rvk · 2024-02-13 07:58 Quote from: TRon on February 13, 2024, 08:51:48 amQuote from: rvk on February 13, 2024, 08:30:44 amNo, there is no way to get that from SQL itself. Well, in case a snapshot is not helpful and with a bit of imagination you could end up with something like this.This still doesn't answer the original question. What is an easy way to know which FIELD was changed. Given link will give you the multiple versions of the complete record but still doesn't tell you which FIELD was changed. There really isn't any other way than to check all the values. (you could loop the fields but you still need to do that through code, there is no magic this_field_changed property.) ---------------------------------------------------------------------- TRon · 2024-02-13 08:08 Quote from: rvk on February 13, 2024, 08:58:53 amThis still doesn't answer the original question. What is an easy way to know which FIELD was changed. Given link will give you the multiple versions of the complete record but still doesn't tell you which FIELD was changed. Pardon me in case I have it wrong but isn't name of column that was altered not the same as "which field" QuoteAn alternative approach is to store null for any column that didn’t change since the previous version. This saves on space, but introduces a new challenge: what if the user updated a column and set the new value to null? That change would be indistinguishable from no change at all. My solution then is to use this _mask column. Every column in the table gets a power-of-two number—1, 2, 4, 8 for id, name, age and weight respectively. The _mask then records the sum of those numbers as a bitmask. In this way, the _history row need only store information for columns that have changed, with an overhead of just four extra integer columns to record the metadata about that change. Besides that when you have two snapshots isn't it then possible to compare them in order to create a diff (of returning only the field- names that where changed) using SQL only ? I'll get a closer look at it myself but have to do it (much) later as I have other things on my plate right now. QuoteThere really isn't any other way than to check all the values. No argument from me there, especially not with regards to the link I posted and from which I quoted. ---------------------------------------------------------------------- rvk · 2024-02-13 08:15 Quote from: TRon on February 13, 2024, 09:08:25 amQuote from: rvk on February 13, 2024, 08:58:53 amThis still doesn't answer the original question. What is an easy way to know which FIELD was changed. Given link will give you the multiple versions of the complete record but still doesn't tell you which FIELD was changed. Pardon me in case I have it wrong but isn't name of column that was altered not the same as "which field"Yes, you are correct there. The _mask field is the one that is wanted. But the question is essentially how do you calculate that _mask field in an easy ("smart") way. The question wasn't even to store that record with _mask... but just to know which field is changed (so what the _mask would be). SQLite doesn't calculate that _mask field itself... you need to calculate it yourself and that's what TS is asking. (the diff method was already found by TS: "// get_Current_value >> Diff >> get_Initial_values" ) ---------------------------------------------------------------------- TRon · 2024-02-13 08:27 Quote from: rvk on February 13, 2024, 09:15:46 amBut the question is essentially how do you calculate that _mask field in an easy ("smart") way. Ok ok... well I imagined the suggestions made where at least a bit smartER. But fair enough  QuoteThe question wasn't even to store that record with _mask... but just to know which field is changed (so what the _mask would be). No indeed, that is true. Knowing beforehand is (still) the better approach. I stumbled upon this thread which essentially (the last answer) uses the same trigger mechanism but the first answer might perhaps be (more) interesting ? (although it would again boil down to a comparison) ---------------------------------------------------------------------- Zvoni · 2024-02-13 10:03 Right, to get to the Bottom of this. rvk has it right: There is no auto-magic to tell you which field has changed. What TS is after is called "auditing", and has to be implemented by the developer him/herself. SQLite (or any other DBMS i 've worked with) is not going to do it for him/her usually done with an auditing-table which gets filled AFTER INSERT, BEFORE/AFTER UPDATE and BEFORE DELETE (Now there are three hints how to do that). NotaBene: Auditing tables are usually done with using EAV Antipattern ---------------------------------------------------------------------- Thaddy · 2024-02-13 10:05 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 ---------------------------------------------------------------------- Zvoni · 2024-02-13 10:09 Quote from: Thaddy on February 13, 2024, 11:05:47 amOf 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 Which meta_data would that be? Since TRon quoted the SQLite-Documentation, then people should actually read it, because there is a very important detail: https://sqlite.org/c3ref/snapshot.html QuoteAn instance of the snapshot object records the state of a WAL mode database for some specific point in history.This "meta_data" is only available in one specific journal-mode --> "wal" For any other Journal-modes it's not available IF the transaction has been commited https://www.sqlite.org/pragma.html#pragma_journal_mode QuoteThe DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. ---------------------------------------------------------------------- rvk · 2024-02-13 10:09 Quote from: Thaddy on February 13, 2024, 11:05:47 amOf 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 · 2024-02-13 10:25 step 1: Query the metadata:Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  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. ---------------------------------------------------------------------- Zvoni · 2024-02-13 10:29 Quote from: Thaddy on February 13, 2024, 11:25:42 amstep 1: Query the metadata:Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  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) ---------------------------------------------------------------------- rvk · 2024-02-13 10:29 Quote from: Thaddy on February 13, 2024, 11:25:42 amstep 4: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  ALTER COLUMN ... etc whatever colunn you want TO CHANGE You STILL don't understand the question  Please read the opening question: Quote from: BSaidus on February 12, 2024, 04:41:52 pmafter 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 · 2024-02-13 10:32 Quote from: rvk on February 13, 2024, 11:29:53 amQuote from: Thaddy on February 13, 2024, 11:25:42 amstep 4: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  ALTER COLUMN ... etc whatever colunn you want TO CHANGE You STILL don't understand the question  Please read the opening question: Quote from: BSaidus on February 12, 2024, 04:41:52 pmafter 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"..... QuoteHello. Let say that I have an sqlite3 table Code: Pascal     t1 (id integer, name varchar(20), address varchar(50) ) ---------------------------------------------------------------------- Thaddy · 2024-02-13 11:35 Would this work, given my first example:Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT    CASE WHEN snapshot1.rollno <> snapshot2.rollno THEN 'rollno' ELSE NULL END AS changed_rollno,    CASE WHEN snapshot1.name <> snapshot2.name THEN 'name' ELSE NULL END AS changed_name    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. ---------------------------------------------------------------------- Zvoni · 2024-02-13 11:43 Quote from: Thaddy on February 13, 2024, 12:35:59 pmWould this work, given my first example;Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT    CASE WHEN snapshot1.rollno <> snapshot2.rollno THEN 'rollno' ELSE NULL END AS changed_rollno,    CASE WHEN snapshot1.name <> snapshot2.name THEN 'name' ELSE NULL END AS changed_name    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) ---------------------------------------------------------------------- Thaddy · 2024-02-13 12:08 Then it will still work on local databases in wal mode and exclusive locking. ---------------------------------------------------------------------- Zvoni · 2024-02-13 12:17 Quote from: Thaddy on February 13, 2024, 01:08:37 pmThen 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 ---------------------------------------------------------------------- Thaddy · 2024-02-13 14:02 well, store the snapshots in a database table? ---------------------------------------------------------------------- Zvoni · 2024-02-13 14:05 Quote from: Thaddy on February 13, 2024, 03:02:03 pmwell, store the snapshots in a database table? quoting myself QuoteThere 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)