SQLiteServer

 

By definition SQLite is a "C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine." /source/. It's biggest advantage is it's very compact - everything happens in a single file which you can easily move wherever you wish, unlike most SQL databases that work with separate server process. But what's the disadvantage? SQLite doesn't have its own user interface, fortunately there are quite good GUI tools available for free on the internet. SQLiteStudio is  one excellent example of such tool. 

Project purpose

The main reason I made this project was to build simple command line interface (CLI) on which to learn SQL syntax - one of the commands allows for executing SQL commands saved on external .sql file. The secondary reason - I wanted to build a simple user authentication system based on json file for some time ago, so I dicided to build it as a feature of this project. The CLI prompts you if you want to register or login and if there are no users at all "users.json" is automatically created. This file stores a dictionary that stores user details, i.e. each user is stored as a separate dictionary with keys "username" and "password". For storing the password I've used something called asymmetric cryptography - the password is transformed into 256 symbol hashed string that can not be decrypted back to the password entered.

Commands

Below is a list of the commands that can be used after successful registration: 

• exit - type this every time you want to close the CLI;
• refresh db list - this is refreshing the file list when you paste new db file in the folder where SQLiteServer.exe is stored; 
• print root - prints the path to the folder where the exe file is working;
• open root - opens the folder in Windows Explorer (this works correctly in Windows environment, I havent tested it under Linux/Unix);
• show dbs - shows the full list of SQLite files available in the folder to which you can connect; 
• connect to [db file name] - this is connecting you to a db file; for that purpose you have to write full file name after connect to (without the square brackets);
• close connection - this is closing the connection if you are connected to any db file;
• show tables - you need to be connected to a db file first; it displaying all table names of the connected tables;
• show all from [table name] - you need to be connected to a db file first; shows all records from the specified table;
• database in use - as command name suggests, it is printing the name of the db to which you're connected;
• execute from file [query file name] - this is the command mentioned at the beginning of this article, used to execute SQL commands saved in a file which name should be specified; 
• describe table [table name] - this is displaying basic information (Number of records/Number of columns/Columns mentioned by name) for a table with correctly specified name;

How to use SQLiteServer.exe?

You can download it from here , then save the .exe file to a folder of your choice. Then double click on the exe file and you will be prompted to choose between login and registration. After successful registration you will be logged in automatically and the CLI will allow you to use the commands from above. The zip file contains the Python code before compiling the exe, the .exe file itself, example db file and a very simple example query file that can be used for initial testing purposes.