C#: Avoiding Performance Issues with Inserts in SQLite

Posted on December 15 2014 08:49 PM by jatten in SQLite, Database, C#, CodeProject   ||   Comments (0)

Coronado-Island Parking-Meter-320

If you are new to SQLite, you may well run across one of the most confounding of its implementation details the moment you attempt to do some sort of bulk or batch processing of inserts or updates.

What you will discover is that unless properly implemented, inserting or updating multiple records in a SQLite database can seem abysmally slow. Slow to the point of unsuitability in certain cases.

Not to fear, this has to do with some default (and not entirely improper) design choices in SQLite, for which there is an easy workaround.

Image by Lance McCord  |  Some Rights Reserved

SQLite is a wonderfully simple to use, cross-platform/open source database with terrific performance specs. It is a mature product, and, if we are to believe the estimates of SQLite.org, is the most widely deployed SQL database in the world.

SQLite manages to cram a host of mature, well-developed features into a compact and well-documented package, including full transaction support.

This transaction support, and the way it is implemented, has a significant impact on certain performance characteristics of SQLite.

Transactions by Default in SQLite

As stated previously, one of the selling points of SQLite, despite it being a simple, file-based database, is that it is fully transactional. What does this mean?

From Wikipedia:

A transaction comprises a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. Transactions in a database environment have two main purposes:

  1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a database remain uncompleted, with unclear status.
  2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the program's outcome are possibly erroneous.

A database transaction, by definition, must be atomic, consistent, isolated and durable.[1] Database practitioners often refer to these properties of database transactions using the acronym ACID.

Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further, the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

SQLite is not alone, of course, in implementing transactions - in fact, transactions are a core concept in database design. However, the implementation of SQLite proposes that, unless otherwise specified, each individual write action against your database (any action through which you modify a record) is treated as an individual transaction.

In other words, if you perform multiple INSERTs (or UPDATEs, or DELETEs) in a "batch," each INSERT will be treated as a separate transaction by SQLite.

The trouble is, transactions carry processing overhead. When we decide we need to perform multiple INSERTs in a batch, we can run into some troubling performance bottlenecks.

Batch Processing in SQLite - A Console Example

If we are using SQLite from the SQLite Console, we can see exactly what I am talking about by running an easy insert script, and seeing how things go. For this example, I borrowed a few lines from the Chinook Database to create and populate a table of Artists. If you don't have the SQLite Command Line Console on your machine, install it now (see Installing and Using SQLite on Windows for details). Then copy the SQL script from my Gist on Github, paste it into a text file, and save the file in your user folder as create-insert-artists.sql.

The script should look like this in the text file before you save:

Paste the SQL Script Into a Text File and Save:
DROP TABLE IF EXISTS [Artist];
 
CREATE TABLE [Artist]
(
    [ArtistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Artist] PRIMARY KEY  ([ArtistId])
);
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (1, 'AC/DC');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (2, 'Accept');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (3, 'Aerosmith');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (4, 'Alanis Morissette');
 
-- . . . A bunch more artist records . . . 
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (273, 'C. Monteverdi, Nigel Rogers 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (274, 'Nash Ensemble');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (275, 'Philip Glass Ensemble');

 

If we open a new database in the SQLite Console (navigate to your User folder to do this for our purposes here) and read the script, we can see how long it takes. There are 275 Artist records in the script to be INSERTED.

Run SQLite3, Open a New Database, and Read the Artists Script:
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Users\John>sqlite3
SQLite version 3.8.7.3 2014-12-05 22:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open txdemo.db
sqlite> .read create-insert-artists.sql

 

We can see that (depending on your machine - your mileage may vary) executing the script takes roughly 10 seconds. Inserting 275 records should NOT take 10 seconds. Ever.

Console Output from Running Script (Took Way Too Long!):

run-create-insert-artists-script-no-transactions

As mentioned previously, unless we tell it otherwise, SQLite will treat each of those INSERT commands as an individual transaction, which slows things WAAAYYYY DOOOOWWWWN. We can do better. We tell SQLite to override this behavior by explicitly specifying our own transaction, beginning before the INSERT batch, and committing after each INSERT batch.

Add Transactions to SQLite Scripts Using BEGIN and COMMIT

When we are executing batches of INSERTs, UPDATEs, or DELETEs in a script, wrap all the writes against each table up in a transaction using the BEGIN and COMMIT SQLite Keywords. Modify the create-insert-artists.sql script in out text file by adding a BEGIN before the table INSERTs, and a COMMIT after the table inserts (for scripts involving more than one table, do this for the INSERTs for each table):

Modified Script Wraps INSERTs in single transaction:
DROP TABLE IF EXISTS [Artist];
 
CREATE TABLE [Artist]
(
    [ArtistId] INTEGER  NOT NULL,
    [Name] NVARCHAR(120),
    CONSTRAINT [PK_Artist] PRIMARY KEY  ([ArtistId])
);
 
BEGIN;
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (1, 'AC/DC');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (2, 'Accept');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (3, 'Aerosmith');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (4, 'Alanis Morissette');
 
-- . . . A bunch more artist records . . . 
 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (273, 'C. Monteverdi, Nigel Rogers 
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (274, 'Nash Ensemble');
INSERT INTO [Artist] ([ArtistId], [Name]) VALUES (275, 'Philip Glass Ensemble');
COMMIT;

 

If we re-run our script now, we see a significant performance boost. In fact, the script execution is nearly immediate.

Re-Run the Script in the SQLite Console (this time, with a Transaction):

run-create-insert-artists-script-with-transaction

The above will apply to all INSERTs, UPDATEs, and DELETEs when you execute scripts in the SQLite console.

Improve SQLite Performance in Your .NET Application Using Transactions

We see a similar problem when we use SQLite in a .NET application, and the solution is conceptually the same, although the implementation is necessarily a little different. If you are new to using SQLite (and many .NET developers are, at some point), this is exactly the type of confounding quirk that can have you running back to yet another "integrated" Microsoft database solution before giving this great database a chance. "I tried SQLite, but the inserts and updates were too damn slow . . ."

Sample .NET Application - The Slow, Hard Way

Consider the following Console application example. It is a small, simplistic example, and has no exception handling, but you get the idea. The Main() method performs some basic set-up, then builds a List<User> which is passed to the AddUsers() method.

Program to Insert a List of Users Using System.Data.SQLite:
class Program
{
    static string _connectionString;
    static void Main(string[] args)
    {
        // 'Data' directory in the current directory ( ..\bin\Debug\):
        string dbDirectory = Environment.CurrentDirectory;
        string dbName = "test.db";
 
        // Add System.IO to the using statements at the top of your code file:
        string dbPath = Path.Combine(dbDirectory, dbName);
        _connectionString = string.Format("Data Source = {0}", dbPath);
 
        CreateDbIfNotExists(dbPath);
        CreateUsersTable();
 
        int qtyToAdd = 100;
 
        // Load some users into a list...
        var usersToAdd = new List<User>();
        for(int i = 0; i < qtyToAdd; i++)
        {
            usersToAdd.Add(new User { Name = "User #" + i });
        }
 
        // And THEN add them:
        var sw = new System.Diagnostics.Stopwatch(); ;
        sw.Start();
        int qtyAdded = AddUsers(usersToAdd);
        sw.Stop();
 
        Console.WriteLine("Added {0} Users successfully in {1} ms", 
        	qtyAdded, sw.ElapsedMilliseconds);
 
        var allUsers = ReadUsers();
 
        Console.WriteLine("Read {0} Users from SQLite", allUsers.Count());
        Console.Read();
    }
 
 
    static void CreateDbIfNotExists(string dbPath)
    {
        string directory = Path.GetDirectoryName(dbPath);
        if (!File.Exists(dbPath))
        {
            // Creates directory if it doesn't already exist:
            Directory.CreateDirectory(directory);
 
            // Creates file if it doesn't already exist:
            SQLiteConnection.CreateFile(dbPath);
        }
    }
 
 
    static SQLiteConnection CreateConnection()
    {
        return new SQLiteConnection(_connectionString);
    }
 
 
    static void CreateUsersTable()
    {
        string sqlTestTable =
            @"CREATE TABLE IF NOT EXISTS Users 
            ( 
                Id INTEGER PRIMARY KEY AUTOINCREMENT, 
                Name TEXT NOT NULL 
            )";
 
        using (var cn = new SQLiteConnection(_connectionString))
        {
            using (var cmd = new SQLiteCommand(sqlTestTable, cn))
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
    }
 
 
    class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
 
 
    static int AddUsers(IEnumerable<User> users)
    {
        var results = new List<int>();
        string sqlInsertUsers =
            @"INSERT INTO Users (Name) VALUES (@0);";
 
        using (var cn = new SQLiteConnection(_connectionString))
        {
            // Open the connection, and also atransaction:
            cn.Open();
            using(var transaction = cn.BeginTransaction())
            {
                foreach (var user in users)
                {
                    using (var cmd = cn.CreateCommand())
                    {
                        cmd.CommandText = sqlInsertUsers;
                        cmd.Parameters.AddWithValue("@0", user.Name);
                        results.Add(cmd.ExecuteNonQuery());
                    }
                }
                transaction.Commit();
            }
            cn.Close();
        }
        return results.Sum();
    }
}

 

The AddUsers() method creates a connection and a command, opens the connection, and then iterates over the IEnumerable<User>, successively inserting the user data for each into the SQLite database. We are using a System.Diagnostics.Stopwatch to time the execution of the call to AddUsers() from Main().

It looks like we've done everything right here - we set up the connection only once, open it only once (opening and closing connections for each loop iteration causes its own performance hit). However, it still takes upwards of four seconds to insert only 100 users. We can see the results in our console output.

Console Output from Example Program Inserting 100 Users:

add-users-no-transaction-dotnet

Pretty lame, but not surprising, given what we have learned about transactionality defaults in SQLite. but, once again, we can do better.

Wrap SQLite Batch Operations in an ADO Transaction in Your .NET Application

Similar to using the SQLite console, the solution here is also to use a transaction. We can modify the code in the AddUsers() method as follows:

Modified Code for AddUsers() Method Wrapping Command Execution in a Transaction:
static int AddUsers(IEnumerable<User> users)
{
    var results = new List<int>();
    string sqlInsertUsers =
        @"INSERT INTO Users (Name) VALUES (@0);";
 
    using (var cn = new SQLiteConnection(_connectionString))
    {
        // Open the connection, and also atransaction:
        cn.Open();
        using(var transaction = cn.BeginTransaction())
        {
            foreach (var user in users)
            {
                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = sqlInsertUsers;
                    cmd.Parameters.AddWithValue("@0", user.Name);
                    results.Add(cmd.ExecuteNonQuery());
                }
            }
            transaction.Commit();
        }
        cn.Close();
    }
    return results.Sum();
}

 

With that, if we run our application again, we see an order of magnitude performance improvement:

Improved SQLite Insert Performance Using Transaction in .NET:

add-users-with-transaction-dotnet

Yep. 52 milliseconds, down from over 4,000 milliseconds.

Be Cautious with Your Transactions in SQLite

We've seen how we can realize some serious performance wins in SQLite by using transactions to wrap up bulk operations. However, let's not put the cart before the horse without thinking it through. Sometimes, you actually need a more granular level of transaction to ensure data integrity.

It simply would not do to maximize performance of a banking application if transactions were implemented only at the top level of a batch operation. After all, transactions in the world of relational databases are first and foremost about creating assurance that an operation succeed in its entirety, or not at all.

Additional Resources and Items of Interest

 

Posted on December 15 2014 08:49 PM by jatten     

Comments (0)

Installing and Using SQLite on Windows

Posted on December 7 2014 04:14 PM by jatten in SQLite, Database, CodeProject   ||   Comments (0)

3835365695_5e515a3492_mWhen one is developing in .NET with Visual Studio and other Microsoft tools, it is easy to lose sight of alternative solutions to common problems. MS does a competent job of creating a tightly integrated development tool chain, where available MS products (both free and paid) offer reasonable default choices which generally get the job done.

Given this, .NET devs often fail to explore outside this arena, or try on alternate solutions which might acquit themselves equally as well, or better, to the problem at hand. Also, of course, there is always a learning curve to new choices, and we often choose the familiar out of simple expediency.

Image by shinichi  |  Some Rights Reserved

Some Background

SQLite is an awesome, open source, cross-platform, freely available file-based relational database. Database files created on Windows will move seamlessly to OSX or Linux OSes. The tools (in particular the SQLite3 Command Line CLI we examine here) work the same from one environment to the next.

It is also not new. If you have been around for a while, you doubtless know SQLite has been in active and open development for well over a decade, and is widely used in many different scenarios and operating environments. In fact, SQLite.org estimates that SQLite is in fact the most widely deployed SQL database solution in the world. Their most recent figures (albeit from 2006) would indicate that there are over 500 million deployments of SQLite (this number is no doubt higher by now). 

SQLite documentation is also widely regarded as above average in completeness and usability, providing both new and experienced users a well-developed canonical resource for learning and troubleshooting.

SQLite was originally designed by D. Richard Hipp in 2000 for the U.S. Navy, with the goal of allowing SQLite-based programs to function without installing a database management system, and without requiring a system administrator (from Wikipedia). These design requirements result in, as the SQLite site describes it, "a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine."

Until recently, I had not spent much time with SQLite. However, in developing the Biggy project, we decided that the core supported database systems would be cross-platform and open source. We wanted both a full-on client/server option, as well as a file-based relational database option. For our file-based relational database we chose SQLite, after exploring other alternatives.

In fitting SQLite into the Biggy workflow, I got to the chance to familiarize myself with SQLite, it strengths, some weaknesses, some peculiarities to watch for, and some tips and tricks for getting the most out of the product.

In this post, we will get familiar with the basics of using the database in a Windows environment. Next post, we will explore integration with .NET development, and Visual Studio. But, learn the hard way first always say, so… let''s get our command line on.

Getting Started - Using SQLite on Windows

Before we look at using SQLite in Visual Studio, let's walk through the basics of using SQLite in a Windows environment outside the IDE.

First, download the pre-compiled binaries from the SQLite Downloads page. At a minimum you will want the binaries for the Win32 x86 SQLite dll, and for the SQLite x86 Command Shell. Unzip the contents of the files in a folder named C:\SQLite3 (or whatever other location suits your needs). Then add C:\SQLite3 to your PATH variable so that you can invoke the SQLite Command Shell right from the Windows console.

In your new directory C:\SQLite3 you should now have the following items:

  • sqlite3.def
  • sqlite3.dll
  • sqlite3.exe

If we run the sqlite3.exe, we are greeted with a Console Application designed to allow us to work with SQLite databases:

The SQLite Console:

Command Prompt - open - sqlite3

The command prompt is easy to use. Text entered without the "." qualifier will be treated as SQL (and succeed or fail accordingly). There are a set of commands preceded with the "." qualified which are application commands. An example is shown in the console window above, where we are instructed to use the .open command to open a database file.

The complete list of SQLite console commands is beyond the scope of this article, but we will walk through a list of the most useful here.

Open a New Database and Create Some Tables from the SQLite3 Console

The SQLite3 Console will open in the current directory (or in the directory in which the .exe is found, if you double-click in the GUI). Let's start by opening a new Windows terminal (which should generally open in our home directory), create a new sub-directory named sqlite_data, and navigating into that folder:

Create a new Directory and Navigate Into the New Directory:
C:\Users\John> mkdir sqlite_databases
C:\Users\John> cd sqlite_databases

 

Next, let's try on that .open command. Open sqlite3 and open a new database in the directory we just created:

Open SQlite3.exe and Open a New Database File:
C:\Users\John\sqlite_databases>sqlite3
sqlite> .open test.sqlite

 

Your console output should now look like this:

Console Output after Opening SQLite3 and Creating a New Database File:

open-new-database

Next, let's create a few tables to play with.

Entering SQL in the SQLite Console

Recall that plain text entered without the "." qualifier will be interpreted by the SQLite console as SQL. There are a few additional things to bear in mind:

  • SQL text may span multiple lines - the enter key will not cause the text following the prompt to execute until it is ended with a semi-colon.
  • You can create multi-line SQL statements simply by hitting the Enter key without ending the statement with a semi-colon.
  • SQLite uses either square brackets or double-quotes as delimiters for object names, in cases where the literal column name would not be allowed. For example, Last Name would NOT be a valid column name, but will work as [Last Name] . Likewise, the keyword Group is not allowed as a column name, but "Group" will work.
  • SQLite is not case-sensitive. Unlike some other databases (most notably Postgresql), casing in both the SQL syntax, and in object names, is ignored.

So, with that said, let's create a table or two.We will keep this really basic, since we are interested in how the console works, more so that a SQLite SQL syntax tutorial.

Create a Table in a Single-Line Statement:

create-table-users-single-line

Above, we just kept typing our whole SQL statement, and allowed the console to wrap the text when it needed to (that lovely Windows console, with its under-developed display characteristics…). Kinda ugly and hard to read. Let's try a multi-line statement.

Create a Table Using a Multi-Line Statement:

create-table-groups-multi-line

Aside from the ugliness that is the Windows Console, that's a little more readable.

Now let's add a few records.

Insert Records into Test Database:

insert-beatles

Notice how the case of my SQL doesn't matter in the above? And, yes, as a matter of fact, that IS a syntax error in the midst of things there. I accidentally used an angle bracket instead of a paren…

So now, we have added a little data. Let's read it back:

Select Data from Users Table:

select-beatles

Here we see that for unrelated reasons (ahem… I closed the wrong window…), I had to exit the application, and then go back in. However, once I opened our test.sqlite database, I was able to enter a standard SELECT statement, and return the data.

See that ...>? That was the result of me forgetting to add the semi-colon at the end of my SELECT statement. If you do that (and you WILL…), simply add a semi-colon on the continued line, and the statement will execute (remember, until SQLite3 sees a semi-colon, it will continue to interpret text input as more SQL).

Formatting the Console Output

We can tell SQLite3 how we would like our data displayed. For example, we may prefer to see a more tabular display, with columns and headers. To accomplish this, we use a few of those application commands, prefixed with a period:

Change the Display Mode for the SQLite Console

We can use the following two commands to change the display mode and use columns and headers in our console output:

Use Column Display Mode with Headers in SQLite3
sqlite> .mode column
sqlite> .headers on

 

If we run our SELECT statement again, the output looks like this:

Console Output Using Columns and Headers:

display-with-columns

Executing Script Files from the SQLite Console Using the .Read Command

Of course, typing in SQL in the console can become painful. While it is fine for quick-and-diry queries and maintenance tasks, doing a lot of work is better accomplished by scripting out what you need in a text file, and then executing that from the Console.

To see this in action, we will download my personal favorite test database, the Chinook database. Chinook has a database script for most of the popular database platforms, providing a handy way to use the same data set for evaluating multiple platforms (among other things). Download the Chinook Database, extract the .zip file, and locate the Chinook_Sqlite_AutoIncrementPKs.sql file. To keep things simple, drop a copy of it into your sqlite_databases folder, so it is in the current directory. Then, also to keep out typing down, rename the file you just moved to simply "Chinook.sql".

We can execute SQL scripts using the SQLite .read command. To illustrate, we will read in the Chinook database.

You will notice a couple things when we do this. First,  the console may show an error (which you can see in the image below), but the script is still running - errors are logged out to the console.

Second, executing this script in its current form is SLOOOOWWWW. This is due to a peculiarity with SQLite we will address momentarily, but was not addressed by the creators of the Chinook Database script.

Execute SQL Script from the SQLite Console Using the .Read Command
sqlite> .read Chinook.sql

 

The script may run for a good number of minutes, so go grab a cup of coffee or something. your computer has not seized up. The Console will return when the script is finished (really, this took about 10 minutes on my machine, but we're going to fix that...

<Coffeee Brake . . .>

Ok. Now that the script has finished running, let's use the .tables command to see a list of the tables in our database. If everything worked as we expect, we should see our own users and groups tables, as well as a bunch of new ones populated with Chinook data:

List Tables Using the .Tables Command:
sqlite> .tables

 

We should see something like this:

Console Output from .Tables Command:

list-tables-after-chinook-import

Now, why the hell did it take so long to run that script??!!

Wrap Multiple Actions in Transactions for Instant Performance Boost

SQLite is inherently transaction-based. Meaning, unless you specify otherwise, each statement will be treated as an individual transaction, which must succeed, or be rolled back.

Transactions are a key feature of relational databases, and critical in the big scheme of things. However, individually, transactions add significant performance overhead, and when we are inserting (or updating, or otherwise modifying) thousands of records in multiple tables, treating each insert as an individual transaction slows things WAAAAYYYY DOWWN.

If we go through the Chinook.sql script and place a BEGIN; statement before the inserts for each table, and a COMMIT; statement at the end of the INSERTs for each table, we will see several orders magnitude better performance from this script.

We can skip wrapping the DROP and CREATE table statements in transactions for our purposes here. As an example, open the file in your favorite text editor, go through and find the beginning of the INSERTs for the Genre table. Add a BEGIN and COMMIT clause like so:

Wrap Table Inserts in Transactions:
BEGIN;
INSERT INTO [Genre] ([Name]) VALUES ('Rock');
INSERT INTO [Genre] ([Name]) VALUES ('Jazz');
... Etc ...
INSERT INTO [Genre] ([Name]) VALUES ('Alternative');
INSERT INTO [Genre] ([Name]) VALUES ('Classical');
INSERT INTO [Genre] ([Name]) VALUES ('Opera');
COMMIT;

 

Now scroll on down, and do the same for each table. When you are done, let's create a dedicated Chinook database to try it out.

Open the Windows Console, navigate back to sqlite_databases directory, run sqlite3, and open a new database named chinook.db. Then use .read to execute the chinook.sql script again:

Read Chinook Script into Chinook.db:
C:\Users\John>cd sqlite_databases
C:\Users\John\sqlite_databases>sqlite3
sqlite> .open chinook.db
sqlite> .read chinook.sql

Next, use the .tables command again to see that all the tables were created. The console output should look like this:

Console Output from Execution after Wrapping Table Inserts in Transactions:

fast-script-execution-with-transactions

We see there is still a little error bugaboo at Line 1 (most likely due to some unicode issue at the beginning of the file - welcome to the world of scripts). However, we can see if our data imported fairly easily:

Select Artists from the Chinook Artists Table:select-from-artist-table

 

GUI-Based Tools

We've covered enough here that we can explore what SQLite has to offer from the Windows console, and become familiar with this fantastic little database. Of course, there are other tools available to work with SQLite databases, including a terrific multi-platform GUI-based interface, SQLiteBrowser, which is a very competent management interface for SQLite databases.

As mentioned previously, the documentation available at SQL.org is first-rate, and there are a host of other resources out there as well.

SQLite is a handy, mature, highly performant database which is easy to use, and works on all the major OS platforms. Database files created on a Windows machine can move seamlessly between OSX and *Nix OSes, as can most of the tools designed to work with them.

I like to start everything with the most fundamental tools available, and then once I have developed a solid understanding of the system, move on up to more advanced tools. Take some time and get to know SQLite from the basic CLI interface. You won't regret it.

Additional Resources and Items of Interest

 

Posted on December 7 2014 04:14 PM by jatten     

Comments (0)

Adding and Editing PATH Environment Variables in Windows

Posted on December 7 2014 11:25 AM by jatten in Windows   ||   Comments (0)

smaller-1-windows-cliSometimes we need to tell Windows where to look for a particular executable file or script. Windows provides a means to do this through the Path Environment Variable. The Path Environment Variable essentially provides the OS with a list of directories to look in for a particular .exe or file when the simple name of the file is entered at the command prompt.

For example, the Notepad.exe application resides in the C:\Windows\system32 directory. However, if we wish to open the Notepad application via the Windows Command Line, we need only type:

Opening Notepad.exe From the Windows Command Line:
C:\Users\John> notepad

 

This works because the Path variable on Windows by default contains a list of directories where application files and scripts are likely to be located. Each directory in the list is separated by a semi-colon.

Similarly, there is another environment variable, PATHEXT which specifies a list of file extensions which might be found when searching for the proper file within the paths in the Path variable. This is why we are able to type simply "Notepad" at the command prompt, instead of Notepad.exe.

Windows will first search the current directory (where the command prompt is a the time the command is executed) to find a name matching the one typed into the terminal, and then search the directories in the Path variable in order, beginning with the most likely locations, and continue until either a matching file name is located, or else return the "… is not recognized blah blah" message at the terminal.

Once a file with a matching name is located, Windows attempts to match the file extension (if one is present), again in the order specified in the PATHEXT variable. If a match is found, the file is processed accordingly.

There are both User-specific and machine-level PATH variables. Machine Path variables are available globally across the machine, and can only be modified by administrators.  User Environment variables can be modified by both administrators, and the user with which the current profile is associated.

Adding a Directory to the User Path Variable from the Command Line

Any user can modify their own PATH variable from the Command Line (unless they have been specifically denied this ability by an administrator).

For example, when we wish to use SQLite from the Windows Command Line, we download the SQLite binaries, and place them in the directory of choice. However, in order to use the SQLite Command Line application without either navigating directly to the folder in which we placed it, or entering the  full file path into our Windows Command Line, we need to add the directory containing the SQLite.exe to our User or System PATH environment variable.

Let's say a user has downloaded the sqlite3.dll and sqlite3.exe binaries and located them in the directory C:\SQLite.

Now, in order to invoke the sqlite3.exe from the command line, we need to add the C:\SQLite directory to our PATH environment variable. We can do this from the command line by using the setx command:

The setx Command - Syntax:
C:\Users\John> setx "%path%;C:\SQLite"

 

When we modify environment variables using setx, the changes are not available in the current Console session - in other words, in order to see our changes, we need to exit, and open a new Console window. Then, we can use the following technique:

We can examine the contents of the PATH variable by typing:

Output PATH Variable to the Console:
C:\Users\John> echo %PATH%

 

Which gives the output:

Results of Echo %PATH% Command:
C:\Users\John>echo %PATH%
C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\Wind
owsPowerShell\v1.0\;C:\Program Files (x86)\Windows Kits\8.1\Windows Performance
Toolkit\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\
Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\1
10\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\Manage
mentStudio\;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Priv
ateAssemblies\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;C:\Prog
ram Files (x86)\Common Files\Acronis\SnapAPI\;C:\Program Files (x86)\Windows Liv
e\Shared;C:\Program Files\Calibre2\;C:\Program Files\Microsoft\Web Platform Inst
aller\;C:\Users\John\AppData\Roaming\npm;C:\Program Files (x86)\nodejs\;C:\Progr
am Files (x86)\Microsoft SDKs\Windows Azure\CLI\wbin;C:\Program Files (x86)\GtkS
harp\2.12\bin;C:\Program Files (x86)\Microsoft SDKs\TypeScript\1.0\;C:\SQLite

 

We can see here that C:\SQLite has now been added to the paths available to the current user.

Adding a Directory to the System Path Variable from the Command Line

In the previous section, we used the setx command to add a directory to the current user's Path variable. Sometimes, we want to make variables available at the system, or machine level. In that case, we use the same setx command in conjunction with the /m flag. However, we need to run the Command Terminal as Administrator for this to work:

Add a Directory the the System PATH Variable Using the /m Flag:
C:\Users\John> setx /m path "%path%;C:\SQLite"

 

Adding a Directory to the Path Variable from the GUI

Or, we can do this using the GUI by navigating to Control Panel => All Control Panel Items => System, and then selecting the "Advanced System Settings" link:

Locate Advanced System Settings in Control Panels:

Control PanelAll Control Panel ItemsSystem

Then locate the "Environment Variables" button:

Open Environment Variables:

System Properties

Opening Environment Variables, we see the following:

Editing Environment Variables:

Environment Variables

Notice in the image above, there are two sections, User Variables for<Current User>, and System Variables.

Also note, there is not currently a Path variable for me, the current user. We will need to add one, and then add our new path to it:

Adding a User Path Variable in the Windows GUI:

add-path-variable

Once we hit OK, We see we have the single item added to our user path variable.

Added Path Variable to User Environment Variables:

New Environment Variables

For some reason, this works differently than when we do this from the Command Line, when we use the setx command from the terminal, the entirety of the system path variable is copied into the user path variable, including the new entry.

If we have Administrator permissions on our machine, we can do the same for the System PATH variable if we so choose.

Removing Directories from the PATH Variable

In some cases, we may need to remove a directory from our PATH variable. In these cases it is recommended to use the GUI, or edit the registry. It's easiest to simply open the GUI, copy the contents of the PATH variable (either the User Path or the System Path) to a text editor, and remove the entries you want to delete. Then paste the remaining text back into the Edit Path window, and save.

Additional Resources and Items of Interest

 

Posted on December 7 2014 11:25 AM by jatten     

Comments (0)

About the author

My name is John Atten, and my "handle" on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, JavaScript/Node, and databases of many flavors. Actively learning always. I dig web development. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

jatten at typecastexception dot com

Web Hosting by