Local database SQLite

I just move on with Kopstt. Firstly, I decided to start just with local database based on SQLite. Is it good? I think to my purpose yeah. Maybe somebody will say it sucks and I should use something else. Hmm …. maybe, feel free to comment. Tough to say but because lack of time I decided not to write unit tests right now, but give you some useful content. Don’t be terrified of my code, it is stored in one file for now, but I will refactor it 🙂

Local database

Why local? I wanted to give users choice to use local or remote database. Not everyone want to share personal data, tasks etc. on remote storage. Some people trust application providers, some don’t. It took me much time to pick up good database provider. I was thinking about PostgreSQL, MySQL or some full-fledged database engine. But then I realised that not everyone can properly configure database. Not good right? What with such kind of users? That’s why SQLite. Simple, light and I’m planning to make it easy  to move to a full-fledged database. What with security? For now I just made file hidden to prevent accidentally delete Our database.

Creating database

I focused on simplicity. You can set name of database, and create few databases. I put checkbox to pick current database which You want to use. Interface is simple. Of course database setting will be required on first time application start, but I’d like to give opportunity to change it every time we want to.

database_init

But what is behind? As I mention at the beginning code doesn’t look pretty good but after refactoring it will.

Intialize and connect

Firstly we need to create private variable which will define our SQLConnection. For now we don’t need to define nothing in class constructor, because we will be able to create few databases. Maybe if we wouldn’t plan to create more than one it could be good. Hmmm …. this thing is to be thought over. Maybe I will change it. Ok, so how does it look?

During init we need to define db_name file check if it exists and if so we skip creating to avoid exception.

Next we need to connect to our database. Firstly, we need to check as in above method whether database file exists. If not, then we can display user custom message such as “No database file”.

Great we can easy create database and connect to it. Now we have to create structure.

In both methods db_name is just sql lite file name. We need to delegate it just because handling picking database file.

Database structure

I don’t have yet mechanism to create database structure. I need to create file structure in separate files so that it can be easily moveed to e.g. PostgreSQL. It has to be cross platform. But I can show how it looks now.

For now we don’t need to return anything (maybe just checking if command executed properly) so we use ExecuteNonQuery method. If we want to return something we need to use ExecuteReader. But that we will discuss in next post.

Ok, for checking if our database works properly we can create simple table. Then we check if file db_name.sqlite is not empty. If it contain some data everything works well.

If you have some questions or suggestions fell free to comment. I will appreciate all tips.