From SQL to text file using my own sqltofile tool

Posted by Niklas on April 4, 2013 Leave a comment (0) Go to comments

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 &lt; 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:

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)
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
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
If batch is true we place the result row into a container to write to file later (all rows into a single file)
If batch is not true (default) we immediately write the row to a file (one file per row)
If output is true the result row is printed to stdout
Disconnect from database and clean up all handles