Search this site:

2009-12-28

Fast batch insert to SQLite database


Recently I am working with SQLite. Like many of you do, I sometimes need to insert large amount of data to the database. There may be thousands, or even more, of records waiting for you. Inserting them to a SQLite database could be a nightmare that it would work like forever. Asking Google about this you will be probably instructed to fine tune the SQLite parameters. Yes, this is one way but if you are not doing this too frequently, I may have another, somehow dirty, suggestion for you.

Here is what I did:

  1. Somehow you prepared the insert statements in text file (e.g. insert.sql). I got it from my colleague who dumped the whole database with tons of records into SQL format using the .dump command.
  2. Use sqlite3.exe to load your database, to which the data is inserting:
    C:\> sqlite3.exe mydb.db

  3. Backup the database using the .backup command into a file (e.g. original.db):
    sqlite> .backup original.db

  4. If the target tables are not empty, dump them into SQL format using the .dump command too (e.g. data.sql). Better dump other related tables if you have set certain kinds of constrains (e.g. foreign key) too. Then you may quit the utility:
    sqlite> .output data.sql
    sqlite> .dump table1 tabe2
    sqlite> .output stdout

  5. Run sqlite3.exe again without specifying the database file, so that a memory database will be used:
    C:\> sqlite3.exe

  6. Restore from your database backup (original.db in the example) using .restore command:
    sqlite> .restore original.db

  7. If you have done step 4, drop those target tables (and related ones). Load and execute the table data backup with .read command:
    sqlite> DROP TABLE1;
    sqlite> DROP TABLE2;
    sqlite> .read data.sql

  8. Now load and execute the insert statements with .read command. Hey, don't plan to go for tea, it will finish shortly:
    sqlite> .read insert.sql

  9. Backup the database using the .backup command:
    sqlite> .backup new.db

  10. Now you can use to restore the backup to anywhere you like. I restored to a new database file and then replaced my existing one.
As you can see, the idea here is actually very simple: insert to a memory database instead of a file, but this does improve the performance dramatically.

BTW, later I found out that it may be related to the transactions. Making the whole batch as a single transaction may help as suggested in the FAQ. Journal file may also take some time . So temporarily turning off the journal with the PRAGMA journal_mode = OFF; statement before inserting might actually help too. I didn't verified these... Feel free to try it out :p

2009-12-26

Windows 7 Shortcuts...


Yes. I have switched to Windows 7.

The Master List of New Windows 7 Shortcuts

Really a useful list. I only know some of them before.

Among the full list. below are those I think to be a must know:

  • Win + Home: Hide all windows except the active one. Same effect as you drag and and shake a window. Useful in place like... your office? Hah
  • Win + Space: Temporary shows your desktop by temporarily making all windows transparent. Same effect as putting your mouse cursor on the bottom right corner of your task bar
  • Win + Left/Right: Dock the window to left/right
  • Double clicking the top window browder/Shift+Win+Up: Maximizing the window vertically
  • Shift + Win + Left/Right: Moving window between monitors
  • Shift + (Win + number) or left click: Starting a new instance of n-th application application on task bar
  • Shift + Ctrl + Win + number) or left click: Starting a new instance of n-th application application on task bar as administrator
  • Ctrl + Shift + N: Create new folder in Windows Explorer
  • Shift + Right click on something: Give you extra useful items in menu
  • Win + (plus or minus): Launch magnifier to zoom in/out. First launch need press Win + plus twice.
Also some other tricks you should know:
  • The application switching dialog and the fancy switching screen now support mouse click :)
  • Ctrl + (Alt/Win) + Tab: Can make the application switching dialog or the fancy switching screen stay after you releasing the keys, and allow you to use mouse scroll to go through them

And I think here are some useful shortcuts/feature Windows 7 is still lacking:
  • Multiple desktop workspace like OS X and Linux does.
  • Shortcut to switch between windows of the same application
  • Shortcut to expose all windows, or windows of the current application :)

2009-11-13

SOLVED: Problem adding fingerprint reader to T60


As reported earlier, I attempted to install a fingerprint reader to my old friend T60 by replacing the palm rest. There was a problem for my OS and BIOS to detect the newly added fingerprint reader module.

After a week of discussion on the lenovo forum, I finally took my notebook back to the shop where I bought the palm rest from for inspection tonight. Unfortunately, I arrived a bit late and the opening hours was passed. I was disappointing until an really helpful and nice guy helped me in troubleshooting. At last, we discovered that one of the cable was wrongly attached. The lovely fingerprint options appeared in BIOS right after fixing the cable.

Hurray! Thanks so much, Mr. helpful guy! If you need some assistances on hardware problem, or if you are finding some cheap hardware components, or even if you have some old computer for trading in, you may reach him at a tiny shop called megabyte, 2/F, New Capital Computer Plaza, Shamshuipo, Hong Kong.

2009-11-06

Some problems with my T60...


Recently encounter some problems with my T60 hardware.

1) I replace the original palm rest with another one with a fingerprint reader, but it doesn't work:
Adding Fingerprint Reader to T60

2) I upgraded to BIOS version 2.25 and then the USB keyboard is no longer working...
USB keyboard not working in keyboard before the OS loads after upgrading BIOS on T60...

Wish they can be fixed soon...

2009-09-24

HTC Hero has no telnetd?


Since my HTC Hero has no QWERTY keyboard, accessing the local console with on screen keyboard is not too efficient. I thought there maybe some apps allowing remote accessing the Android like telnetd or sshd, but they cant be found in the Market.

Google then told me there is actually a telnetd at /system/bin of the phone, and that was actually a common way to "root" it. But unfortunate such binary was not found on my Hero...

Seems that I cannot find other people complain about this one the web... Did I miss something? Or is it removed by HTC or the service provider in order to prevent user from gaining root access? (I am using Smartone-Vodafone service in HK...)

Hello Android~


Have been damn busy recently, didn't update here for quite a long time...

Anyway, I just bought a HTC Hero, which is Android-based. This is my second "smart" phone, my last one was Motorola E680i, which was the first Linux-based phone in the market. You probably see my interests here, yeah, open source rocks~~~

Like other new born babies, Android is not yet perfect, but I see the bright future of it. Already downloaded the SDK and reading the documentations, looking forward to my first Android apps :) Any nice idea?

2009-07-29

Failed to install KB967143 for Visual Studio 2008 SP1


I need to use Silverlight for one of my project, so I tried to installed Silver Tool for Visual Studio 2008 SP1. But strangely enough, it fails with error code 0x80070643.

By extracting the downloaded file, and set up each components one by one, it was noticed that KB967143 hotfix to VS2008 was the one failing to install. The following event was logged:

Event Type: Error
Event Source: MsiInstaller
Event Category: None
Event ID: 11935
Date: 23/7/2009
Time: 2:01:19
User: x\kenneth
Computer: x
Description:
Product: Microsoft Visual Studio 2008 Professional Edition - ENU -- Error 1935.An error occurred during the installation of assembly 'Microsoft.VSDesigner,version="9.0.0.0",publicKeyToken="b03f5f7f11d50a3a",processorArchitecture="MSIL",fileVersion="9.0.30729.4141",culture="neutral"'. Please refer to Help and Support for more information. HRESULT: 0x80131018. assembly interface: IAssemblyCacheItem, function: Commit, component: {B77A1104-F3C2-4350-AFFB-16EAB249233D}

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 7b 44 37 44 41 44 31 45 {D7DAD1E
0008: 34 2d 34 35 46 34 2d 33 4-45F4-3
0010: 42 32 42 2d 38 39 39 41 B2B-899A
0018: 2d 45 41 37 32 38 31 36 -EA72816
0020: 37 45 43 34 46 7d 7EC4F}


Searched for many pages on the web like this. Even a complete uninstall all components of VS2008 did not help.

I tried to create a thread at the Silverlight Setup & Installation forum but received no reply. Then I created another thread at the Visual Studio Developer Center, and finally got some useful idea. There should be some problem with the assembly manifest of the Microsoft.VSDesigner.dll.

Unfortunately, I have no idea how to fix that yet. Anyone got some idea?

2009-06-11

SOLVED: Corrupted WinSxS Policies


I had my system partition screwed up last week. I spent nearly a week to fix the problem, that's unfortunately not as obvious as those which can be solved by automatic tools. Maybe I should share more on this later.

Anyway I believe I've made my computer working again, okay, mostly working.

Some of the files are corrupted, causing me a little bit trouble. This is the first message I saw when I booted into the system again:
Event Type: Error
Event Source: SideBySide
Event Category: None
Event ID: 58
Date: 10/6/2009
Time: 9:15:55
User: N/A
Computer: *****
Description:
Syntax error in manifest or policy file "C:\WINDOWS\WinSxS\Policies\x86_Policy.9.0.Microsoft.VC90.CRT_1fc8b3b9a1e18e3b_x-ww_b7353f75\9.0.30729.1.policy" on line 0.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


Need not to explain much. My system was complaining that it cannot understand a file that it was supposed to know. But what the hell is that? Google told me that it's something related to Windows Side by Side, a feature in Windows XP that keeps different versions of the same DLL for different applications to use. Ooops, that could be an headache. It probably meaned that some files related to the unknown dll was corrupted.

I didn't really pay attention to this problem until I failed to run my Live Messenger to chitchat with my friends, as well as the Visual Studio 2008 for working. Repairing/re-installing Live Messenger didn't help, and I definitely didn't want to launch the time consuming repair/re-installation process of Visual Studio unless I had no other choice...

These are some addition error messages I got in event log:
Event Type: Error
Event Source: SideBySide
Event Category: None
Event ID: 59
Date: 11/6/2009
Time: 0:28:16
User: N/A
Computer: *****
Description:
Generate Activation Context failed for C:\Program Files\Windows Live\Messenger\msnmsgr.exe. Reference error message: The operation completed successfully.
.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


Event Type: Error
Event Source: SideBySide
Event Category: None
Event ID: 59
Date: 11/6/2009
Time: 0:28:16
User: N/A
Computer: *****
Description:
Resolve Partial Assembly failed for Microsoft.VC90.CRT. Reference error message: Manifest Parse Error : An Invalid character was found in text content.
.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


Event Type: Error
Event Source: SideBySide
Event Category: None
Event ID: 59
Date: 11/6/2009
Time: 0:41:58
User: N/A
Computer: *****
Description:
Generate Activation Context failed for C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe. Reference error message: The operation completed successfully.
.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


The path of the file gave us some hints that it might be those Visual C++ libraries not working, so I downloaded the redistributable packages and installed immediately, hoping that the problem could be fixed easily. Life is always not that easy. The problem couldn't be fixed. I then noticed that the file name part of the file "9.0.30729.1.policy" looked like the standard MS version strings (X.X.X.X), so I asked Google again on the version "9.0.30729.1". Well. it's the SP1 of VC 9.0. I downloaded the Visual C++ 2008 SP1 Redistributable Package and install again without hesitation, and then, hurray! The problem has been solved. File closed.

2009-05-26

SOLVED: Acrobat Reader: "Cannot find or create [font_name]"


My girlfriend got a music score PDF file somewhere but the Acrobat Reader failed to read it probably, reporting error like "Cannot find or create [font_name] ! Some text may not be displayed correctly". Something like that.

Obviously that's a problem of missing font. Thanks to the powerful Google, I find a free download of that font and a potential solution.

Here is what I have done:

  1. Download and install the font by copying the TTF file to %windir%\FONTS (normally that means C:\Windows\FONTS). If I am right, you can also access that folder through the Control Panel.
  2. Close all Acrobat Reader windows, and then reopen the file.

It may already work magically. But in order to make sure it works, I also followed what suggested in the potential solution page by deleting the font list before 2. This force Acrobat Reader to rebuild the font list cache. To do so, browse to the folder C:\Documents and Settings\Local Settings\Application Data\Adobe\Acrobat\9.0\Cache on XP, or C:\Users\AppData\Local\Adobe\Acrobat\9.0\Cache on Vista. Note that the folder is hidden, but simply copy the path here, replace with your username and paste to the location bar of your Windows Explorer should work. The path may also be slightly different if you are using different version of Acrobat Reader. Finally you just delete the file named AcroFnt09.lst. To play safe you may also consider renaming the file instead of deleting it. You will see a new file of the same name created after relauching Acrobat Reader.

The major difference in my case to the potential solution page mentioned above is that I didn't restart the computer, and I wasn't prompted for installation when I reopen Acrobat Reader.

Hope this helps.

2009-04-28

Google Calendar is offline.


...

I only got this message when accessing my Google Calendar, all my events have gone...

Oh my god. It seems that the Google Calendar back end got some problems. Please fix soon as I would like to check my schedule.

There is a thread on the Google Calendar Help discussing the issue.

(Will that be another notice that we shouldn't rely too much on Google, or any other single vendor?)

BTW, the calendar of my Google Apps account is not affected.

2009-04-21

NO MORE WEB INSTALLER!!!


I really can't understand why so many apps force their valuable customers to first download a suck downloader just for downloading the installer? Why can't things be simple and direct?

Let me tell my story. I installed Windows Live Photo Gallery sometimes ago and I would like to uninstall it now. Interestingly I cannot find the entry in Add/Remove programs. I could see other Windows Live service, just not Windows Live Photo Gallery. Double checked at the installation folder (C:\Program Files\Windows Live\Photo Gallery) and all files are there. Read this KB and I don't think I went to a wrong place. Is Windows nowadays so intellengence that it can detect my wish for uninstallation and hide the relevant entry?! I then planned download the installer again, and hoped it was a MSI so that I can launch the uninstaller manually. Unfortunately what I got from the official sites wass only a CRAP web installer that only allows me to download new Windows Live services. What's installed is installed and cant be removed from that. I tried to search for a offline installation package but only unofficial sources were identified.

What the hell.

I understand that having such kinds of web installers helped the vendor to have better control on the download - they can use them to play to control the deduce the most suitable installer, find one the best mirror used, limit the download speed, or even some installer have P2P function that speed up the download a lot (especially for large files). But one like me can easier throw out tons of drawbacks for only providing web installers.

  1. The first reasons can't be simpler. There may be people without Internet connection, at least it may be the case when they plan to install the problem. Are they expected not to install the application? Someone may argue that if some apps' usages are highly depending on Internet (Windows Live service maybe?), there is no point for users without Internet connection to install that. Well, that's stupid argument. I don't think installation and usage should be considered as one. Even for those apps which requires online activation, the activation process should be able to be independent of the installation process. Afterall installation is just copying files and perform some preconfiguration, maybe.
  2. The seond reason is that some people may not give up installing the apps even they don't have Internet connection at the time of installation. Some one them will try to google for unofficial sources to download the offline installer. This is an extremely dangerous action as you will never know whether those unofficial sites are trustworthy or not. So providing only web installer actually pushing your valuable customers to risks.
  3. The third reason is there is case like me. Some "intellegence" web installers will only download installers of apps you haven't installed. Users can never uninstall the installed apps through the web installer.
  4. Forthly, user may have their own favorite downloader. Why should we force them to use yours? It's not only above user's convenience to download, but also there may be firewall blocking outgoing connections by default. Not all firewall (or their configuraiton) allow temporary connections nor ask the users for actions when new executables are trying to access Internet. Are you expecting all users to add firewall rules or modify its configuration just to download your product?
Well, I think I have said enough on this.

All in all, I really hate web installers. Please give me back my offline installer. Period.

[2009-06-11 Update]
BTW, I just managed to uninstall Windows Live Photo. In addition to uninstalling the "Windows Live Photo" from "Add or Remove Programs", you also need to click "Change/Uninstall" for the "Windows Live Essential" item, then you can choose to remove any Windows Live components in the pop-up uninstaller.

2009-03-24

Crash Memory Dump Analysis


I was reported a crash problem of a tool developed by me. While searching for relevant information on the web, i found some useful resources about crash memory dump analysis:

Crash Dump Analysis and Debugging Portal, and its blog (there are a few interesting books that I may want to buy later)
Debug Diagnostic Tool - a useful tool from Microsoft
If broken it is, fix it you should - a blog on MSDN about debugging

Have fun.

2009-03-22

Modify revision comments in SVN


As mentioned in the SVN book, transaction and revision properties are actually unversioned so it is possible to modify them. However this also means when you changed them the previous values are lost forever. That's why SVN has implemented some mechansim to protect these unversioned propertes.

In order to modify these properties, you need to add a Pre-revision property change hook (pre-revprop-change). You may follow general instructions at here (see the comments in the quoted source). If you are using VisualSVN then just right click on the repository, choose property and browse to the "Hooks" tab to make modification.

That is!

2009-03-21

Mozilla Crash Reports


Unfortunately your Firefox/Thunderbird just crashed, and you are prompted the Mozilla Crash Reporter. You are kind enough to fill in necessary information and report to Mozilla. Have you ever wondered where are these crash report sent? Can we check them out?

I just come up with the answer by chance: Mozilla Crash Reports

Have fun :D

2009-03-19

Feauter Request: Labels in Google Calendar


I think there is one really useful feature missing in Google Calendar: labeling. With labeling we can group events into categories, e.g. I can make one label for my dear, one for school events, and one for my working appointments. Colors or even icons may help visualizing these events nicely. Further enhancement maybe label-based assess level for different users (i.e. some users can only see the busy/free status and some may be able see the details of certain events).

I don't really don't see why Google still hasn't add this useful feature after requesting by so many people for so long. Do you also want this feature in Google Calendar? Come on, please tell Google here and here (i suppose the "color codes event" is the a similar idea?).

BTW, a To-Do list would also be a great value-add feature for Google Calendar.

2009-01-31

Issue: Abnormally High Memory Usage of Windows Live Messenger 2009


I upgraded my Windows Live Messenger to version 2009 (Build 14.0.8050.1202) a few days ago, the interface looks even more fancy then previous version, but I've encountered some issues.

When the software runs for a few hours, it may, not always though, take up 50% of my dual core CPU load (i.e. full loading of 1 CPU core) and hangs. I just had it restarted until the forth time the problem occurred. I was shocked when I looked at its memory consumption in task manager:


WOW!!!! A over 1.7G of memory consumption!!! How dare it!

My notebook has a 2GB of physical memory and memory paging occurs like crazy. I started the Process Explorer and wish that it was only some worker threads who are bugged so that I could kill them without restarting the Messenger (since I have some unread message...). Unfortunately it is the main thread who occupying the CPU cycles... I also tried to terminate the network traffic of the process in my personal firewall software and even disconnected my wireless network, hoping it was something related to the traffic but there is no help. So I can only kill the whole process and leave my unread messages mysteries...

Anyone has encountered similar issues or know what's going on?

Thanks.

2009-01-23

Problem signing certificate created in VisualSVN with Microsoft Certificate Service


I was helping my workplace to setup a subversion server and I found a good choice named VisualSVN. It is so convenient that everything is done with a few clicks. It is a bundled of Apache and SVN server with an user-friendly GUI (based on Microsoft Management Console). It even helps to you setup LDAP authentication and maintain the access control list so that I can integrate it to our active directory. Everything was going nicely except a minor problem on signing a certificate for the HTTPS service.

Since I don't want the server certificate to be self signed, I generated a certificate request in the VisualSVN console and attempted to sign in with our own Microsoft Certification Service instance. However, I was prompted the following error:

The request contains no certificate template information

From the text I know that the request generated by VisualSVN probably does not include the certificate template information (which is 100% normal...). This page has listed the simple solution by using the certreq utility:
certreq -submit -attrib "CertificateTemplate: WebServer" request.txt
Where request.txt is the certificate request file generated in VisualSVN.

Problem's solved and thanks! :D

BTW, I really do suggest you guys to try out VisualSVN if your server is Windows Platform. It saves you a LOT of time!

2009-01-22

Cool ICE from Microsoft


Recently I went to the top of Lion Rock in Hong Kong, stood on the head of the lion and enjoyed the great view there. Certainly photos were shot to create a panorama, I have did that once (check it out at my blog post here) on that night using Autostitch. The result is not bad.

I then come up with another tool from Microsoft Research named ICE - Image Composite Editor and created another panorama. The result is impressive. It is easy to use and I can have some minor tweak on the photo. It also allow users to chop the result photo (automatically or manually), which is really convenient - I mean most users does chop those black edges away from their panorama.

Here's the result photo:
360° Panorama at Lion Rock, Hong Kong (2)

Unlike last time, I didn't tune the color. But not bad already, right? You are recommended to view it in original resolution. If it is too large for your browser, feel free to download and view it in your favorite image browser :D

Globalization in Programing


The world is really that large, strange bugs often arise when a program goes global...

Recently I was prompted for a strange FormatException when a user from Europe test my program. It's was really strange the double.Parse("0.70") would throw such an exception. "What's wrong with this 0.70, isn't it a standard form of double? Is the problem caused by the tailing zero?" I wondered.

Certainly not. The .NET Framework shouldn't be that stupid not to be able to handle those trailing zeros. After some web search, I found the answer at here. Oh my god, the world is really that large, I don't know some countries like France use commas instead of periods for decimal points in numbers (i.e. "0,70" instead of "0.70")... The solution was also in that page, that I should tell the program not to use local culture when parsing parsing the number like this:

double.Parse(str, System.Globalization.CultureInfo.InvariantCulture);


Oops. I know that we can (and we should) supply a culture information to the Parse function whenever possible from the day I started using .NET Framwork. I am just too lazy to add it everytime. As I result I need to search for all ".Parse(" pattern in the pieces of code again to fix this problem. This story tell us again we should never be lazy when writing programes, espeically when it is up to certain scale... Same lesson learnt, again.

2009-01-07

Disclaimer

ALL CONTENTS AND INFORMATION IN THIS WEB SITE ARE PROVIDED "AS IT" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED. THE ACCURACY AND AVAILABILITY OF THE CONTENTS, INFORMATION AND THE WEB SITE ITSELF ARE NOT GUARANTEED. THE AUTHOR TAKES NO RESPONSIBILITIES ON ANY COSTS OR DAMAGES (DIRECT OR INDIRECT) ARISING OUT OF OR IN CONNECTION WITH THE ACCESS, USAGE OR INABILITY OF USAGE OF THIS WEB SITE.