File Inventory - A Hybrid Version Control System

• Download source code - 467 KB

Introduction

Occasionally I need to know the files in a directory that have been added or removed recently. For example, I want to know if an image file has been deleted or added in a picture directory. It is similar to the functionalities of a Version Control System without the cumbersome of setup, check-in, check-out, and separate storage; something simple and easy to use. Window does not include such a tool and I could not find it mentioned anywhere.

Armed with knowledge of nowadays programming technology and Google Search, it seemed not too difficult to write one.

In this article, I will show how I:

• Convert idea into design
• Write an Inventory class to inventory files
• Save persistent data to a SQLite database
• Call a .bat which runs different SQL scripts each time
• Discuss future enhancements and variety to design

Background

There are already a number of CodeProject and MSDN articles on programming with SQLite, execution of another program within a program, and saving persistent data. Those articles have discussed the individual technique in detail; my writing is to demonstrate the combination of these techniques into an interesting application.

Prerequisites for following this article

I have developed code under Microsoft Visual C# 2010 Express. If you are using the previous version, then copy paste Form1 code into your Form1, delete Form1.Desinger.cs, and add Inventory.cs to the project.

If you have trouble embedding my code, read the example code section in msdn.microsoft.com flowlayoutpanel[^]: “Paste the code into the Form1 source file. If your project contains a file named Form1.Designer.cs, remove that file from the project. You may need to click the Show All Files button in Solution Explorer to see the designer file.”

The SQLite file should be placed in the Debug directory along with the program exe and bat files.

I am skipping the details of how to embed SQLite. If you are unfamiliar with SQLite, read “Using SQLite in your C# Application by Chayan”. SQLiteCSharp[^]

Programming interface design

When the application starts, users need to enter a directory to inventory. Therefore, I use a TextBox to store the directory text. The application displays all files in the directory including all sub-directories in a ListBox. It queries the database and displays the previous inventory logs in a ListBox.

If the search does find logs, then users can select an item in the ListBox, a previous inventory to compare. The result shows delete, no change, and new displayed in tabPage2.

The following SQL script will select the modified, new, and deleted records:

select 'modified' as optype, a.item, a.fdt as 'filedate'
from temptbl a, CD_TEMP b
where a.item = b.item and a.fdt != b.fdt and b.ckey = '_2012 02/25 14:36:45'
UNION
select 'new' as optype, a.item, a.fdt as 'filedate'
from temptbl a 
where a.item not in (select item from CD_TEMP where ckey = '_2012 02/25 14:36:45')
UNION
select 'deleted' as optype, b.item, b.fdt as 'filedate'
from CD_TEMP b 
where b.ckey = '_2012 02/25 14:36:45' and b.item not in (select item from temptbl);

Users can press the Save Inventory button to inventory files. When inventory is finished, it displayed the inventory catalog in a ListBox. I recommend that you search on a small directory first as the large directory might take a while to finish.

TAB2 is used to store the files to compare statistics, such as total of files in a directory, number of files deleted, and number of new files.

TAB3 is to store the application configuration setting. It has not yet been implemented.

Persistent data store design

For this application, I could have saved data in .txt or a comma-separated .csv type file, but it would be difficult to store more complex data such as attributes as file date or file size were to be included in the future, or become too difficult to perform any sorting and ordering.

I use SQLite for data storage for the following reasons:

• Can save multiple attributes (fields)
• Can query to obtain results and statistics
• SQL is standard
• Other functions and benefits from a database perspective

The Inventory.db database consists of an index table plus many tables; each table is unique to store a directory’s inventory data. path_table_map is the index table which maps the inventory directory name to the table name, because the directory name consists of \ and : that needs to be converted to another character or strip off.

Inventory for the directory will be store in an individual table. It uses the DateTime format to generate a key or catalog name to inventory as an invoice number to an invoice in the Microsoft sample database Invoice table.

I store in the format of YYYYMMDDHHmmss, year-month-day-hour-minute-second, and file name with the full path.

The program automatically creates and executes the following SQL scripts when it first starts and the inventory.db does not exist. The scripts will be saved in sqlstmt_history.txt to ease debugging.

--******** create inventory.db ********* 
create table path_table_map(
    no INTEGER PRIMARY KEY,  
    dirpath varchar(200),
    tblname varchar(200));

As the user starts to inventory a directory, the program creates the following SQL script for the table to hold the inventory data and inserts a record into the index table.

-- use two tables, temptbl and CD_TEMP (c:\temp); replace ':' with 'D', '\' with '_' 
-- 1st table is used to store the current selected directory info 
create table temptbl(no INTEGER PRIMARY KEY, ckey  varchar(20), fdt  varchar(16), item varchar(400));
delete from temptbl;
insert into temptbl(ckey, fdt, item) values ('_2012 02/25 14:36:21', '2012 02/25 14:11', 
                    'C:\TEMP\6032\2011-09-24 Danny Take\421379126g_6324792.png');
insert into temptbl(ckey, fdt, item) values ('_2012 02/25 14:36:21', '2011 09/27 05:38', 
                    'C:\TEMP\6032\2011-09-24 Danny Take\BACKYARD_6367905.JPG');
insert into temptbl(ckey, fdt, item) values ('_2012 02/25 14:36:21', '2011 09/27 05:43', 
                    'C:\TEMP\6032\2011-09-24 Danny Take\BEDROOM 21_6367928.JPG');
insert into temptbl(ckey, fdt, item) values ('_2012 02/25 14:36:21', '2011 09/27 05:41', 
                    'C:\TEMP\6032\2011-09-24 Danny Take\DINING ROOM_6367935.JPG');

-- 2nd table is used to store the inventory catalog info 
create table CD_TEMP(no INTEGER PRIMARY KEY, ckey  varchar(20), fdt  varchar(16), item varchar(400));
insert into path_table_map (dirpath, tblname) values ('C:\TEMP', 'CD_TEMP');

When the Inventory button is clicked, the following SQL scripts are inserted into the directory inventory table.

insert into CD_TEMP (ckey, fdt, item) select '_2012 02/25 14:36:45', fdt, item from temptbl; 

Using the Inventory class

I designed the Inventory class in inventory.cs to interface with SQLite, to generate file names, store and retrieve records.

class Inventory</p>
{
 // following are Public List objects available for caller to access
    ckeys  //inventory catalog
    list1     //current files list
    llist2    //selected inventory files list to compare
    listNew  //new files list
    listDel    //deleted files list
    listReport  //report files list with '+' or '-' prefix for add, delete
    sqls   //store sql statements

// following are Public functions
   Inventory(string sDir)  // inventory object instantiated
   compareDirectories(string catalogdate) // compare button clicked
   saveRec(string sDir) // inventory button clicked
}

Walking a directory includes all sub-directories recursively to get the list of file names.

private void listDirFiles(string sDir)
{
    try
    {   //list all the files in directory
        foreach (string f in Directory.GetFiles(sDir))
        {
            Console.WriteLine(f);
            list1.Add(f);
        }
        // do this for every sub-dir
        foreach (string d in Directory.GetDirectories(sDir))
        {
            listDirFiles(d);
        }
    }

    catch (System.Exception excpt)
    {
        Console.WriteLine(excpt.Message);
    }
}

Ease debug by spawning a Process to run a Batch program

Often times, it is difficult to spot an error in SQL statements. Especially when you have to follow SQL syntax into a SQL command, you could have missed a quotation mark to enclose a string.

It is easier to write every SQL statement to a text file, then execute through a batch command. This also saves execution time as it reduces unnecessary open-close database operations and eases debugging. Therefore I added a module runBatchJob in the Inventory class.

To execute the following command from the DOS Command Prompt:

sqlite3 inventory.db < sqlstmt.sql

use the following code:

runBatchJob("batchjob.bat", "inventory.db  sqlstmt.sql");

and batchjob.bat contains the following:

sqlite3 %1 < %2

The runBatchJob module in the Inventory class:

private void runBatchJob(string prog, string args)
{ 
   string setupProg = prog;  // program to run
    if (File.Exists(prog))
    {
        System.Diagnostics.Process proc = new System.Diagnostics.Process();
        proc.StartInfo.FileName = prog;
        proc.StartInfo.Arguments = args; //"inventory.db setup.sql";
        proc.StartInfo.RedirectStandardError = false;
        proc.StartInfo.RedirectStandardOutput = false;
        proc.StartInfo.UseShellExecute = false;  // run in background (invisible)
        proc.StartInfo.CreateNoWindow = true;
        proc.Start();
        proc.WaitForExit();

Conclusion

This article documents how I converted an idea to a programming practice, to design a user interface, to use a persistent store, to use a SQL query, and to call a batch program. As I tried to code it fast, it is a rudimentary design and can be improved in many ways.

Addendum

If you download the code, it is best you delete and recreate the batchjob.bat file (save the content of the bat file to a txt file before you delete it). Otherwise you will get an annoying security confirmation prompt every time the program calls out to run the batch job. Window has a security system that detects if a batch file has not originated from your own system and warns the user of the danger.

History

• 14-Jan-2012 - First version.
• 25-Feb-2012 - Second version - Added file date attribute and modified compare logic to display new, modified, and deleted records as well as the capability to search the whole logical drive.