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.

Setting up Google Play on Mi5X

I always have problem with Google Play using Mi5X. It is not available when shipped. Installed Google Play Apk from websites and it can launch Google Play.

One of it works just fine but the other one will prompt "Server Busy" on and off. Following netizen advice to totally remove Google Services and Google Play store. The result is that I cannot restore Google Play at all despite installing Google Services and Google Play apk from sites that provide the apk. Even my account information does not list the Mi5X as a device.

While looking for ways to restore back Google Play by searching for "Google" in Mi Store, I notice Mi Store has a prompt saying something like it is not able to list Google products due to limitation. Thus whatever advice on the web does not apply.

Searching for "MiUi Google Play" turn out something like a installer for Google Play. Tried to go to Mi Store again and look for "谷歌安装器”。 It still turn out nothing.

This calls for drastic measures. I notice there is a "去豌豆荚搜索“ in Mi Store when searching for "Google". Tried that and it  actually shows one app called "谷歌安装器”. It is not by Google but look rather like a "Google Play Installer".

Followed instruction to allow for "unknown sources" in Settings.Additional Settings,Privacy. The apk was installed successfully. Running it and it prompts for installation of a number of Google Apps. Upon completion, Google Play can start without issue.

The next thing to do is just wait and see if "server busy" prompt will show again.

Cantonese as a Language

Recently there are a spat of talks about Cantonese being a Language. To be fair, it is a language distinct from Mandarin (Putonghua).  Cantonese exists since before Qin Dynasty and later developed into NanYue in 204BC.  During the Tang dynasty, intermix between the Han and Guangdong, Guangxi people reached critical mass. These people refers to themselves as Tang people.  As a group who spoke a distinctive language from the Middle Chinese, it is deemed to be a language.

It is quite difficult to define Cantonese as a dialect as it is very distinguished from Mandarin. They are mutually unintelligible with each other. Cantonese used to say that their Language is older than PuTongHua (the official Chinese Language). There's a story about Cantonese almost become the official language of China after the XinHai Revolution in 1912 but was beaten by a small margin (Wikipedia). Whether the story is true is still arguable. Since the communist takeover in 1949, Putonghua is the common language. But since Hong Kong and Macao has not returned to China, Cantonese is the common language there.

Canton province since the Qing dynasty has been the only opening to the world, it is not surprising that many of the emigrants are Cantonese. Thus there is always an urge to define Cantonese as a language.

People in Hong Kong has always strive to distinguish themselves from Chinese. It is therefore not surprising that they actively promotes Cantonese as a Language thereby distinguishing themselves from Mandarin speaking Mainland Chinese.

There are many arguments for Cantonese as a Language.

1. Cantonese is older than Putonghua. Actually, they all developed from Middle Chinese on a different path. Putonghua is indeed developed later.

2. Tang poems are written in Cantonese. Tang poems are based on Middle Chinese. Some may have sounded more correctly in Cantonese while others simple does not sound right. Mandarin obviously does not really fit well too since Middle Chinese is used then.

3. UN has defined Cantonese as a language. UN did list Cantonese as a language together with other major spoken language of China. In the order of number of people speaking specific language in the world, Mandarin is the most (14%).  Wu (Shanghainese) gets 1.2%. Yue (including Cantonese) only gets 0.89% (Wikipedia). It is undeniable that it is a language listed by UN. But saying that UN recognize it as a language (not a dialect) is a bit far fetched.

4. There are claims that UN define Cantonese as one of the six "leading language in daily use" (粵語為日常六種主要用語). It is very much doubtful this is real. Yue (including Cantonese) is 24th in the list of world's most spoken language according to Wikipedia. Wu language has even higher usage than Yue. There is no definitive link to this saying in UN.

5. Cantonese is an official language in Hong Kong, Macao. Chinese constitution 《中华人民共和国宪法》19.5 states that Putonghua is promoted as common language throughout the country.  《中华人民共和国国家通用语言文字法》section 2 states that the common language is Putonghua. Section 8 of the same article indicates each race has the freedom to develop and use their own language. Even Putonghua is only stated as national common language not "Official language". How did Cantonese being defined as "official language"? (https://www.zhihu.com/question/22600638).

It is undeniable that Cantonese is indeed a language. There is no point citing unclear or untrue facts about it as a language.

Printing Receipt Using PHP again

Last blog on this subject is at Printing Receipt. It talks about a single use printing where there is only one printer for one purpose. There are requirements to use same type of printer for different receipts. Like for example, one company wants to print queue numbers, receipts and barcode travellers using the same type of printer.

It would be easy to just create three PHP page to do the work. Here the attempt is to use a single PHP page like a driver that can print to different printer for different purpose. It becomes like a class file. Also the print codes stays with the app that does the printing so that you do not need to look for specific PHP page for the printing code.

The PHP code is like the previous blog with a slight change. The printer is a Brother QL720NW which does not work with fwrite_stream. A simple fputs will work fine.

All that is required is to send the printer IP and the print code to the page as parameter using POST. Now the twist is that POST only sends AlphaNumeric not the full ASCII. Escape codes usually includes ASCII less than 32. The trick is to convert the print code into Base64 first before sending. At the PHP, just use base64_decode to convert it back before printing. The code is as below

$ipAddress = $_POST["IP"];
$printCode = $_POST['PrintCode"];
$printCode = Base64_decode($printCode);
$fp = fsockopen($ipAddress, 9100);
fputs($fp, $printCode, strlen($printCode));
fclose($fp)

With this, you can send to different printer with a different print code using the same PHP page. The advantage is that any app that can do a POST will be able to do printing.

Most programming codes will have base64_encode (or similar). If not then there are probably programmers who wrote a code library in the same programming language. If it does not have then you may have to create the code yourself. The trick is to convert the byte into Binary then take every 6 bit and convert it back to number to match against a table see https://en.wikipedia.org/wiki/Base64. to get the ASCII character. It is not that difficult.

Victoria Park Hong Kong

Right after Typhoon Manghkut, had a few hours of free time before flight, went to Victoria Park to see the aftermath of the typhoon. It is really a mess. Fallen trees, leaves are all over the place. Still managed to get some good photos. of flora and fauna.

At the entrance, there is a map readable by Visual Impaired people. I wonder how much can they make use of it.

The first tree I see has many branches on the ground. This one is Queen Crape Myrtle, 大花紫薇。

You can see the path is all covered with leaves from trees. At some places, you can't even see the path but that does not deter morning exercise people.

Next plant on the ground is 異葉南洋杉, Norfolk Island Pine.

Among the fallen branches I found male and female Red-whiskered Bulbul. Their nest probably destroyed.

I just need to look at the ground for plant species. This one is 枫香.

Next one is Eucalyptus robusta.

Black-collared Starling seems to be lost.

A Oriental Magpie-Robin seems to be quite active.

Black Rock Pigeon seems to be lost too.

A burst pipe and a fallen tree trunk.

Another view of the devastation.

Yet another Black-collared Starling foraging.

Did not take photos of fallen trees. It is almost like walking in a jungle trail.

Since it is just in a park, no maps or albums. The trip is just 3 KM.

Mongolia Trip

Went to Mongolia to attend World Blind Union AP Mid-Term General Assembly. In between, went out to have some picture taking session. On the last day, went for a one day tour.

Reached UlaanBaatar Chinggis Khaan International on 9/9/2018.

While waiting for other members of the WBU, Took a few pictures of the plants in the carpark. First one is Heteropappus altaicus. It has another surprising view which I will show later.

Next is Lupine Clover. I cannot confirm its Scientific name.

Next one is not focus properly as I am only using my hand phone to take picture. My camera is still in the luggage. I can't figure out what plant is it.

One other plant is Russian Knapweed. It is also not focus properly but at least it still captures the plant flower in the background.

Took a wide angle photo of the grass patch. The flowers are wild.

Walked further down and took a photo of Scots pine. Still focused wrongly.

Settled down in Holiday Inn Ulaanbaatar. Next morning, went out to have a quick shot of the garden in front of the hotel. It is a long and narrow garden in the middle of the road.

Took a picture of a unknown tree. Unable to identify.

There are no variety of plants in the garden till the end where there is a small patch of flowered plants. Common plants are Petunia.

Managed to get a focused shot of the Scots Pine this time.

Went further down to the junction and saw a mini flower garden. Plenty of Petunias.

One more flower I can't recognize.

At least I recognize this as Silver Ragwort.

Another flower I can't recognize as these are naturalized plants from other countries.

A photo of the tree that  I can't recognize too. Its trunk is painted to prevent frost I think.

Walked across to the further side of the road and saw this Yellow Sweet clover.

Can't recognize this one too.

Unfortunately, I cannot find a lot of plant photos on Mongolia. Many plants are just new to me. This one is a Alfalfa.

This one is Russian Knapweed. The close up is blurred due to wrong focus (auto focus as my eyes are as blur) so it is discarded.

This grass is also not recognized. Focus is out due to wind.

Another unrecognized grass.

Finally one plant that I can recognize. It is a Purple Shamrock. I guess it is not native.

A partially blooming flower. Seems to see it before but can't find in my library.

One more pine tree.

White Campion is one flower that I have searched for its name long ago as the flower pattern is unique.

This single Viola seems to be out of nowhere.

Running out of time so have to head back to hotel for the conference. Took a couple of picture in the conference for fun.

Before the hosted dinner, took a couple of photo on the mongolian tent from the hotel window. Like all cities, traffic jam can be seen below.

A few photos of the dinner entertainment performers. They are performing local music and songs.

Next morning, sneaked out again to venture around. Most of the plants are not recognized except specifically stated. The first one which is a Redroot Pigweed.

Mohilev Mallow is a climber.

This one is a Shrubby Cinquefoil. Notice I wear gloves. The weather is quite cold.

This one is probably a type of Maple.

This one is a Tufted Vetch

That evening went out with the group to Sukhbaatar Square before ending in Modern Nomads restaurant for a second taste of Mongolian food.

The next day is the one day tour. On the way, stopped by to snap some photo of the outskirt of Ulaanbaatar.

Managed to take picture of the Mongolian horse while the vehicle stopped due to traffic.

First stop is the Chinggis Khaan Statue. It is a huge statue with a hall below and a mini museum underground.

Of course I cannot miss those plants there. First one is unknown, the next one is a common dandelion. Last one is the same Heteropappus altaicus at the airport.

Next stop is Gorkhi Telelj National Park. First stop is lunch. Saw a Russian van, a sort of SUV.

After lunch, went to the nearby Mongolian tent hotel to see the inside of it.

Of course cannot miss the flora in the area. Three unidentified plants were there. There is a large fly on one of it.

The next plant is a Edelweiss.

Another one is very hard to identify. Most likely is a Caraway.

Next stop Turtle Rock. We stop a a photo taking spot where there is a souvenir shop with two fox skins outside.

Cannot miss this Greater Plantain plant growing by the road side.

We go further in the park for a horse ride. On the way snap a photo of the rare trees in the mountains.
Can't tell what tree is it. Probably pine trees.

We got a shock when the vehicle we are on goes down a stream and is about to cross a river before the driver decided that it is too deep to cross.

You can see the depth when a horse rider crosses the river.

The tour guide cum driver finally called a SUV to come over to fetch us.

In the ranch, there are two dogs, a Bankhar dog and a Husky. The Husky is extra friendly. It keep trying to play with visitors.

Finally, we get to ride a horse (on tow).

Feel very insecure on the horse thus did not take any picture during the ride. The tour ends after the ride. The driver bring us back to Ulaanbaatar shopping mall for a last minute shopping. Managed to snap photos of plants around the place.

Had a good night sleep before flying back to Hong Kong at 4:30 am.

Wikiloc map is here https://www.wikiloc.com/car-trails/mongolia-tour-28638102

Album is not only Mongolia, it include Hong Kong trips also so skip it.