Tag Archives: C/C++

My sqltofile cheat sheet

Ever wanted to go directly from an sql to xml files. I do 🙂 Here is a little tool I built a while back. It takes an xml (or any character structure) and an sql and returns one file per row returned result.
For example:

sqltofile -t "<?xml version='1.0'?>%user%%timestamp%" -s "SELECT user, timestamp FROM user" -h mysqlite.db

Will output (one file per row):

row0:
<?xml version='1.0'?>niklas1363615417
row1:
<?xml version='1.0'?>miew1363615493
row2:
<?xml version='1.0'?>emma1363615604
...

The words surrounded with % – signs in the -t argument above gets substituted by the values in the columns with the same name (minus the % -sign ).

Options to use:
-t Is the template string that contains the substitution variables surrounded by % – signs
-s This is the sql to run agains the database (* can be used)
-h Is the filename of the sqlite3 database to run the query agains
-o Outputs the result to stdout (optional)
-b Writes all lines to one file called row0 (optional)

You can find the source and an brief explanation of sqltofile here

From SQL to text file using my own sqltofile tool

sqltofile is a little tool I created a while back to solve the problem of creating XML files fast and efficient from the result of a SQL query. It is build in C++ and currently supports Sqlite3. In the source there is also an Interface class for building support for more databases in the future. I will here explain briefly how it is built

We start with the interface class

#ifndef DATABASEINTERFACE_H
#define DATABASEINTERFACE_H

#include <vector>
#include <string>

using namespace std;

class DatabaseInterface {
			
   public:		
      virtual ~DatabaseInterface(){}
		
      virtual int connect(string &filename, string &user, string &password) = 0;
      virtual vector<vector<pair<string, string> > > query(string &sql) = 0;		
      virtual int disconnect() = 0;

};
#endif

The connect function takes hostname/filename, username and password as arguments. This function should be called first to establish a connection to the database
The query function runs the SQL against the database and returns a vector of vectors of pairs where the ‘first’ member variable contains the column name och the ‘second’ has the column value
The disconnect function is to be called last to clean up and disconnect from the database

And here is the main program

#include <string>
#include <stdio.h>
#include <stdlib.h>
#include <fstream>
#include <iostream>

#include <getopt.h>

#include "database.h"

// Forward declarations
void findAndReplace(string &subject, const string &search, const string &replace);
void saveToFile(string str, int row);
void printUsage();

int main(int argc, char const *argv[]) {
	
	// Variables
	string searchchr = "%";
	string searchstr = "";
	string batchstr = "";
	string tmplstr = "";
	string sql = "";
	string host = "";
	string user = "";
	string password = "";
	string result = "";
	
	bool batch = false;
	bool output = false;
	
	int option = 0;
	int conn;

	// Objects
	Database *db = new Database();
	vector<vector<pair<string, string> > > data;
	
	if (argc < 3) {
		cout << "Too few arguments" << endl;
		printUsage();
		exit(EXIT_FAILURE);
	}

	// Handle arguments
	while ((option = getopt(argc, (char **) argv, "bot:s:h:")) != -1) {		
        switch (option) {
            case 'b' : batch = true;
                break;
            case 'o' : output = true;
                break;
            case 't' : tmplstr = optarg; 
                break;
            case 's' : sql = optarg;
                break;
            case 'h' : host = optarg; 
            	break;	   
            default: printUsage(); 
                exit(EXIT_FAILURE);
        }
    }
        // Connect to database	
	conn = db->connect(host, user, password);

        // Query database
	data = db->query(sql);

	// For every result row
	for (size_t n = 0; n < data.size(); n++) {
		result = tmplstr;
		// For every column in result row
		for (size_t m = 0; m < data[n].size(); m++) {
			searchstr = searchchr + data[n][m].first + searchchr;		
			findAndReplace(result, searchstr, data[n][m].second);				
		}

		// If batch save row to string (newline at end)
		if (batch) {			
			batchstr += result;
			batchstr += "\n";
		}
		else { // If not batch write to new file			
			saveToFile(result, n);
		}

		// If output print to screen
		if (output) {
			cout << result << endl;
		}
	}
        // If batch mode and we have recieved any rows - write to file
	if (batch && batchstr.length() > 0) {
		saveToFile(batchstr, 0);
	}
       
        // Disconnect from database and clean up
	db->disconnect();
		
	return 0;
}

First we have a bunch of variable declarations. I’m mostly using the std:string here for easy string handling. Then I take care of all the arguments using the getopt function – all pretty basic stuff. Now we get to the core of the program. It works something like this:

  1. Call connect() with host/filename, user and password details. Since my database class is made for Sqlite the connect function calls sqlite3_open_v2() with the SQLITE_OPEN_READONLY option (sqltofile only supports SELECT – statements)
  2. Call query() with our SQL query. Results are returned as an vector of vectors of pairs. This might need some more explanations. I use std:pair to hold the column name (first member variable) and value (second member variable). Every pair is then put into a vector representing a result row, and finally the result row is placed into another vector representing the whole result set
  3. Loop thought the result set and perform variable substitutions. In the templete string there should be “variables” surrounded by % -signs. These get substitutet with the value that belongs to the column with the same name as the “variable” without the % – sign
  4. If batch is true we place the result row into a container to write to file later (all rows into a single file)
  5. If batch is not true (default) we immediately write the row to a file (one file per row)
  6. If output is true the result row is printed to stdout
  7. Disconnect from database and clean up all handles

That was a brief description of sqltofile. The full source code can be found here

Tested on OSX 10.7.5 with gcc version 4.2.1 and Sqlite 3.7.11

My tree command for POSIX systems in C

I like the tree command. It’s nice to have when working with lots of directories and it brings an ‘graphic’ element to the command line 🙂 I’m here going to show a simple tree solution in C that I usually use as a base for more complicated implementations.

#include <string>
#include <dirent.h>

void rec_walker(const char *path, int level);
char* writer(int level);

int main(int argc, char const *argv[]) {

	DIR        *dir;
	const char *path;

	path = argv[1];

	// If no path set current path
	if (path == NULL) {
		path = ".";
	}

	// Test if dir  	
  	dir = opendir(path);
  	
  	if(dir) {  		
    	    rec_walker(path, 0);
  	}
  	else {
  		printf("%s is not a directory\n", argv[1]);
  		return 1;
  	}

	return 0;
}

Im here using the standard <string> library for common string operations and the dirent.h for easy access to the filesystem. First we have to define our external (not in main()) functions, rec_walker() and the writer() function (more about these later).
Then we set the path that we want to traverse. If no path from argv[] we set current directory as path. Now that we have a path we test to see if it is a directory and if it is we call the rec_walker() function:

void rec_walker(const char *path, int level){
	struct dirent *file;
	DIR *directory;
	
	chdir(path);
	
	directory = opendir(".");

	if(directory){
		while ((file = readdir(directory)) != NULL) {
	
			if (strcmp( file->d_name, "." ) != 0 && strcmp( file->d_name, ".." ) != 0) {			

				if (file->d_type == DT_DIR) {
					printf("%s%s:\n", writer(level + 1), file->d_name);
					rec_walker(file->d_name, level + 1);
				}
				else{
	      			printf("%s%s\n", writer(level), file->d_name);
	      		}
	      	}	
    	}
    	closedir(directory);
    }	

}

The rec_walker() function takes the path (as string) and a level (as int). The level variable is uses for a little ‘pretty print’. The rec_walker() function lists all files in the directory, and if the ‘file’ is a directory (DT_DIR) it will recursively call itself with the new path and an incremented level. It continues to do this until no directories are left.

Now we add a little ‘pretty print’ with the writer() function:

char* writer(int level){

	const char characters[] = "-----------------------------------";
	char result[] = "";

	// Max levels 
	if (level > 18)	{
		level = 18;
	}

	return strncat(result, characters, level * 2);

}

This function simply returns some lines to ‘indent’ the different levels to make it easier to read (very simple)

Example output from the above code:

--test:
--test_file.txt
----test_lvl2:
----test_file_lvl2.txt
treeplus
treeplus.c

Tested on gcc 4.2.1 and OSX 10.7.5