Tag Archives: SQLiteDatabaseManager

Building an Offline Program with Online Syncing

As I become more and more experienced with software development, I am very quickly discovering the limitations of the web. Having a desktop application that constantly needs to retrieve large amounts of data from the Internet is very, very slow. Sometimes, when network speeds are spotty, a simple interaction can take up to 30 seconds, which is definitely unacceptable for the end-user.

In order to solve this problem, I decided to restrict myself from looking at online solutions. I wanted to create a solution on my own.

First, I wanted to create a robust system for managing a database for an offline program. I looked into several different services, including Microsoft’s SQL Stack and a local MongoDB server. However, I found that most of these solutions were either too difficult to learn (I was used to setting up a MySQL server using cPanel and phpMyAdmin) or that they over-complicated the issue. Then, I came across SQLite, which happened to be exactly what I was looking for. It was a lightweight, speedy, local replica of MySQL. After spending about an hour reading the documentation, I realized that it was also super easy to setup. I began working on a library that would make working with a local SQLite database super easy.

Thus, SQLiteDatabaseManager was born. The project was meant to house functionality that would be used in all of my projects, and here are some of the key features that were packed into it:

  • The ability to quickly create a local database
  • The ability to quickly insert, delete, and update records knowing the table names, the fields, and the new values.
  • The ability to have a small table-verification system that allowed the user to define table schemas and check the existing SQLite tables against these schemas for integrity verification.
  • The ability to retry operations when they fail (the fail could be a result of a spontaneous locked file and a retry could fix everything)

SQLiteDatabaseManager worked well because all you needed to do was create a new instance of it, and everything worked out of the box. A new SQLite database was setup and managed by the library. After rigorous testing, this worked perfectly. Of course, the library still needs major work and many more features, but it does what it was intended to do at the moment. My offline data management system worked.

Now the problem was not just managing data offline, but getting it to sync to an online counter-part when a network was available (In a perfect world, all operations would be done on the local SQLite database (fast) and then they would be replicated on the duplicate remote MySQL database (slower) on a background thread). The idea was simple.
Local SQL and Online SQLThe SQLite database and the MySQL database would communicate with eachother via a simple REST API. The databases would be identical and the ID numbers created by the SQLite database would simply be moved to the MySQL database.


Creating the REST API for the remote MySQL portion of this project was rather simple. Relying on the methods of the LAMP stack, I used a set of PHP files that would receive GET, POST and DELETE requests so that data could be managed and manipulated fairly easily. Using PHP’s json_encode() function also proved very useful for creating readable responses.

Thus, with the creation of a working SQLite local database and a working and accessible remote MySQL database, I went ahead with the first iteration of implementing this idea. So far, I have run into a few problems.


An easily-solvable problem arose when thinking about how to handle many, many clients. Since there was only one remote MySQL database and many, many instances of the local SQLite database, it was a pain tracking when the local client’s changes actually had to be applied or if the remote version was actually newer. This problem was solved by creating a dateAltered field in both the remote and local databases that would contain the last date of modification of the data that it pertained to. Thus, the data with the latest dateAltered value was put permanently into the remote MySQL database.

But what about when there are many, many clients creating information. Surely, some of the client’s SQLite databases will assign ID numbers that coincide with other clients’ SQLite databases. Thus, when information is uploaded to the remote server, different pieces of information may have the same ID numbers. But since all data is modifiable, how is this detectable? This is a problem that I have not yet worked out a solution to since the first implementation of this design has a one-client one-server relationship. However, at the moment, I am hypothesizing that each data-entry contain a unique hash of its original state. Thus, database operations can be performed by referencing the ID of the element, but conflict-resolutions will use this hashed code. Thus, we will always be able to track if a certain piece of information has been modified from its original form or not. Thus, when uploading to the remote server, the server will manage some conflict resolutions and return the ID number of the object. The local clients will then reassign the ID number of the object to that ID number in order to match with the remote MySQL database.


I will continue to work on this project in the coming weeks, and I plan on making some detailed blog posts about it, so stay tuned! Eventually, I hope to have this process wrapped up in an easy-to-install and easy-to-implement library. We will see how that turns out.


The Change to Open Source

When I first started programming around six years ago, I held a huge belief that all the code that I created was mine. Everything that I compiled was mine. Everything that I made was mine. All of this stuff was to be maintained, distributed, and advertised by me. Everything was mine. Recently, however, this attitude has changed.

After installing Linux on my laptop and using it exclusively for the past year, I have come to love the world of open-source. It’s a beautiful place. It’s a world of free software, free code, and a very, very friendly and helpful community. Something doesn’t work in your software? Why not take a look at the code, edit it until it works, and then submit the changes to help others around the world? It’s a really, really beautiful system. It’s also a system that I really want to be a part of.

Because of this, I have started uploading most of my projects to GitHub. Now, anyone who is interested in the projects that I am working on can download my code, fix my bugs, and publish it to the world. What’s the reward for doing this? In my opinion, besides personal satisfaction, it’s the fame.

There’s a great feeling behind having your name on code. You start to become widely known throughout the coding community and your contributions on projects start to become valued. People get excited to have your code in their projects. On top of that feeling, it’s simply awesome to have a huge list of projects that you’ve contributed to.

What about the money, though? What about the commercialization of software? Well to that I have a very interesting relationship. I feel that if your code is so groundbreaking and so revolutionary that it can make you a large sum of money, go ahead and privatize it. However, if this revolutionary code would be of benefit to other projects around the world, I am under the strong opinion that the code should be open-sourced. This allows everyone to use this new ideas in their own projects, benefitting the entire computer science community as a whole.

However, there are problems with this. A prime example of these problems is Gabriel Cirulli’s 2048 . After a post on hacker news, Cirulli’s game became extremely popular. The game, being open source, spawned several hackers who wanted to figure out how to solve the game. Soon enough, 2048 AI, exploits, glitches, and tricks were all exposed. There is no problem with this. The problem is due to the popularity of the game. Because of the popularity, a few people took the source code of the game and ported it to mobile platforms, which many people downloaded. Instead of retaining credit to Gabriel, however, these programmers decided to take the credit and put ads into the game, capturing large amounts of revenue for themselves as unknowing mobile users downloaded these copies of the game.

If we want the open-source world to flourish, we must not allow events like the 2048 disaster to occur. There must be an unsigned agreement among programmers about the ownership of code. Although people can edit it in any way they want, people should give credit where it is due. Since the aspect of code-fame is one of the most appealing things about open-source, I think that the disrespect of ownership is our primary problem with open-source code. I believe this is what is preventing us from moving forward.

In order to show my support for open-source, however, I plan on open-sourcing most of my projects from here on out. This can be seen with my recent GitHub additions update.net and SQLiteDatabaseManager – Both of these projects originated as supporting libraries for an application that I am making for a client. I figured that it would be in everyone’s best interest if these small supporting libraries are open-sourced. The idea is simple. I open-source the libraries, other people may use them. Other people fix any bugs in the library, my client benefits since they will always be getting the latest versions of the libraries.

I really hope to see the open-source movement only gain more popularity as time goes on.