SQLite syncing with remote data to act as local data

There is a need to get a set of data from a remote server and distribute to a group of local computers. The data must not be deleted then inserted. The data also may not be available on remote server due to it being closed and removed.

If the data cannot be deleted (truncated) then the only way is to do "insert or ignore" so that existing data is either updated if it exists or inserted if it is not. Meanwhile, there is a problem of remote data does not exist while local data exists.

Well, it is quite easy. Two tables are used. One for the main table with a key index field and the other one only the key index field. Both table index uses the same field.

When importing data, both tables are inserted with the remote data. The second table was truncated before the import starts. This means the main table contains new and old data while the second table only contains the new data. At the end of importing, just simply delete from the main table where the index field not exists in second table. Thus only the imported data remains in the main table.

Example of the "insert or ignore" goes like this

Insert or ignore on maintable (f1, f2,f3....) values ('x1', 'x2, 'x3'...);update maintable set f2='x2', f3='x3' where f1='x1'

It seems that the above will insert and then update if the index is not found but it is still better than using two sql statements to insert or update plus one statement to check whether the index exists before doing either insert or update statement.

Example of the delete command goes like this

delete from maintable where  f1 not in (select f1 from secondtable)

The main table can then be access by local users.

Triggers in SQLite

Although triggers are common in databases. I seldom use it. Recently there is a need to pre-check before an insertion whether the number of records in a certain condition is within the quota set. Also, there is a need to check whether the specific field value exists already. Finally, there is a need to check whether one field content is in the list of another.

Under normal condition, I would set up three queries to check for it before doing the insertion of records. It will be untidy to code if this is done the normal way. Triggers then is the more cleaner way to do so.

The setting up is quite straight forward.

Create trigger before insert on for each row begin select

It then followed by the list of conditions

case when ((select....) > ) then raise(abort, "error message")
when ((select from where = NEW. ) is not null) then  raise(abort, "error message")
when ((select from where = NEW.) is not null) then raise(abort, "error message") end;
end;

In the script we just need a normal insert SQL with a exception check, (PHP try and catch) to get the error message. Just get the different message and act accordingly. It is very much cleaner in coding without much coding in the application.

This trigger can be inserted externally using normal SQL procedure. It can also be removed the same way. Obviously, same name trigger must be removed first before insert.

There are much more type of triggers. This one only talks about INSERT BEFORE.

Picasa Substitute

I used to take photos and add captions and tags to the photos using Google Picasa. Unfortunately it is no longer supported. I have a hard time finding substitute for it. Recently tried a new windows app called digiKam. It is quite suitable for use. The following are some similarities and dissimilarities.

1. Edit Geolocation.

Both can edit geolocation. Both are able to update multiple files at one time. digiKam have additional functionality to do GPS correlator (loading a GPX file and interpolate the geolocation into the picture). Currently uses GeoSetter to do it.

2. Edit Caption/IPTC/XMP

Used the menu item of digiKam to edit IPTC. Although it can edit individual but it is a hassle if multiple files of the same group need to be edited.  Later, it was discovered that the right hand column has "Captions" can do multiple file update. Picasa has an advantage in that you could type in the keywords and it will auto suggest. digiKam provide a list of tags for you to select. Each has its own merits.

3. Search for tags.

DigiKam provide a tag listing where you can instantly apply a filter by choosing the tags. Picasa does it by typing into a search field. The latter can just type partially and the list will automatically match the partial words. I would say the latter is more flexible.

4. Grouping.

Picasa has a "starred" feature that allows a selection of pictures to be listed by choosing a set. digiKam does not have it but since I have been using tags to provide a list like "special", "flowers" etc. It is a matter of adding the "starred" tag and use it for listing under tags. However, it is slightly inconvenient as I have to fish the tag out from thousands of tags. Maybe I should use the star setting provided by digiKam instead.

5. External change of the tags.

DigiKam seems to be unable to respond to newly changed tags outside of its app (unless it is restarted). Picasa does not have this problem.

6. Maps

Picasa has maps but since the support is no longer available, it has not been working. Any individual change to the geolocation has to use GeoSetter. GeoSetter is kind of slow as it does a lot of background work. digiKam can show the icon/bubble on OpenStreetMap and can add the geolocation by dragging the file into the map.

7. Album listing.

Picasa can scroll through the albums. digiKam can only show within the same album. It is a disadvantage as I sometimes have to scroll through a list of albums to identify the observations.

8. Maps grouping - In the catalog setting there is another maps facility. This one allows to select by geolocation. A map is displayed on all pictures that have geolocation. You can select an area and it will display all pictures in the location. Very convenient tool to identify species in the same region.

In conclusion, no two app works exactly the same. Some will have advantages and disadvantages over others. digiKam is one app that is quite close to what I need to manage plants and animals photos with location info and tags by Gendre and Family for easy reference and research.