# SQLite Client Updates

**URL:** <https://forum.xojo.com/t/sqlite-client-updates/15907>\
**Category:** Databases\
**Created:** [April 21, 2014, 9:24pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907 "2014-04-21T21:24:18Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 21, 2014, 9:24pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/1 "2014-04-21T21:24:18Z")

</div>

I am looking for the best way to check if an SQLite database has been updated outside of my app. My app loads db when the file is opened but it could be modified outside my app. How do I check if it has been so I can reload it?

---

<div class="post-metadata">

**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\
**Post date:** [April 21, 2014, 10:05pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/2 "2014-04-21T22:05:52Z")

</div>

Could you store a last modified time stamp and check it against the modified date/time of the file?

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 21, 2014, 10:07pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/3 "2014-04-21T22:07:59Z")

</div>

Yes that was what i thought about doing. Is that the right way to do it. I wasn’t sure if there was a prescribed way of doing this.

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [April 21, 2014, 10:35pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/4 "2014-04-21T22:35:25Z")

</div>

are you looking to see if data has been altered ?  
or the schema ?  
And if thats an issue why not just use an encrypted db and that way it can’t be altered outside (at least not easily)

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 21, 2014, 10:42pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/5 "2014-04-21T22:42:43Z")

</div>

Its not an issue if changes are made but I want to reload the database if it is edited outside my app. Data and/or schema.

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [April 21, 2014, 10:57pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/6 "2014-04-21T22:57:20Z")

</div>

Sqlite doesn’t give you an easy way to know that it has been edited

Tables don’t inherently have a last timestamp property for edits / updates  
So you’d have to put one on every row, as an autofilled field  
However IF someone edits one of the rows they CAN just edit that value as well  
You’d need some way to fingerprint the entire table potentially  
The schema is similar

I’d create an encrypted database & keep it in the application data dir  
In there save whatever timestamps etc so it can’t be altered  
And if it gets tossed out then you recalculate & recreate it

OR save this same kind of data in a preferences file of some kind

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 21, 2014, 11:05pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/7 "2014-04-21T23:05:17Z")

</div>

> [@81403:@Norman Palardy](#):
>
> Sqlite doesn’t give you an easy way to know that it has been edited

I thought as much… Many thanks everyone

---

<div class="post-metadata">

**Author:** ![Peter\_Truskier](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_truskier/32/243_2.png) [@Peter\_Truskier](https://forum.xojo.com/u/Peter_Truskier)\
**Post date:** [April 21, 2014, 11:52pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/8 "2014-04-21T23:52:42Z")

</div>

> [@81403:@Norman Palardy](#):
>
> Sqlite doesn’t give you an easy way to know that it has been edited

Since SQLite is file-based, would’t checking the modification date of the file do the trick, or, are there cases in which SQLite client libraries might change the modification date of the file even if nothing has been modified in the database?

---

<div class="post-metadata">

**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\
**Post date:** [April 21, 2014, 11:54pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/9 "2014-04-21T23:54:35Z")

</div>

All that tells you is “something” may have changed (not for sure DID change) - touch will update the mod date without changing anything & there may be other ways to alter the mod date without making any actual changes

If you want to know what changed or if in fact something DID change it hasn’t really got a mechanism to do that

---

<div class="post-metadata">

**Author:** ![Peter\_Truskier](https://forum.xojo.com/user_avatar/forum.xojo.com/peter_truskier/32/243_2.png) [@Peter\_Truskier](https://forum.xojo.com/u/Peter_Truskier)\
**Post date:** [April 22, 2014, 12:21am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/10 "2014-04-22T00:21:00Z")

</div>

Well, OK, but given that folks don’t often run touch on their files, it seems to me it still might work. Correct me if I’m wrong, Mike, but I guessed that you just wanted to avoid re-loading the database every time you want to use it. Using the modification date of the file would at least limit the number of times you’d have to do this when it had not, in fact, been modified.

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 22, 2014, 7:48am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/11 "2014-04-22T07:48:16Z")

</div>

I am developing an SQLite editor. I notice with SQLite Professional (from MAS) that if I edit an sqlite database in another app SQLite Professional reloads it. It must be checking something to know its been edited.

---

<div class="post-metadata">

**Author:** ![Oliver\_Osswald](https://forum.xojo.com/user_avatar/forum.xojo.com/oliver_osswald/32/6972_2.png) [@Oliver\_Osswald](https://forum.xojo.com/u/Oliver_Osswald)\
**Post date:** [April 22, 2014, 8:20am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/13 "2014-04-22T08:20:13Z")

</div>

Damn! It is really difficult to use this forum from an iPad: I just posted to this thread and then I tried to edit my post. As the icons on the upper right corner do not appear, I have to guess where the edit icon would be, and - once again - I hit the delete button…

Anyway, I suggested to use triggers to log changes, time stamps etc

---

<div class="post-metadata">

**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\
**Post date:** [April 22, 2014, 8:22am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/14 "2014-04-22T08:22:51Z")

</div>

> [@81469:@Mike Charlesworth](#):
>
> I am developing an SQLite editor. I notice with SQLite Professional (from MAS) that if I edit an sqlite database in another app SQLite Professional reloads it. It must be checking something to know its been edited.

That’s a completely different animal altogether. What it’s doing is watching the filesystem for change notifications. You’d have to do that with declares. Basically, you’ll establish a callback method and pass the address of that method to the OS and say, “call this method if this file changes.”

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 22, 2014, 8:35am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/15 "2014-04-22T08:35:38Z")

</div>

> [@81477:@Tim Hare](#):
>
> That’s a completely different animal altogether. What it’s doing is watching the filesystem for change notifications. You’d have to do that with declares. Basically, you’ll establish a callback method and pass the address of that method to the OS and say, “call this method if this file changes.”

That sounds like what I need. I’ll look into this. Thanks Tim

---

<div class="post-metadata">

**Author:** ![Mike\_Charlesworth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/m/958977/32.png) [@Mike\_Charlesworth](https://forum.xojo.com/u/Mike_Charlesworth)\
**Post date:** [April 22, 2014, 11:19am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/16 "2014-04-22T11:19:51Z")

</div>

Tim, you were right, the approach I am taking uses FSEventStream and is available in MacOsLib. You can monitor for filesystem events down to file level so when I open the db I will start monitoring the folder item for events i.e. a change and then notify the user the file has been modified outside the app and requires a reload.

Thanks all

---

<div class="post-metadata">

**Author:** ![Grant\_Singleton](https://forum.xojo.com/letter_avatar_proxy/v4/letter/g/b38774/32.png) [@Grant\_Singleton](https://forum.xojo.com/u/Grant_Singleton)\
**Post date:** [May 19, 2014, 8:18am UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/17 "2014-05-19T08:18:12Z")

</div>

I’m looking for the essentially the same thing. I currently have a Valentina DB client/server setup working well and is quite stable, however, data can get updated from different PCs and therefore I need to know when a record gets updated via a callback or notification so each PC display the current updated data.

I’ve done a Google search and found that there is a such a mechanism listed for SQLlite databases. Check it out [here](http://www.sqlite.org/c3ref/update_hook.html).

Is such a mechanism available for Valentina? Has anyone else know of anything similar?

Cheers  
Grant

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 5:11pm UTC](https://forum.xojo.com/t/sqlite-client-updates/15907/18 "2020-10-29T17:11:27Z")

</div>


