Code-Bin #928 - SQL import

SQL import
Posted by: h4x0rz88 | October 31, 2010 @ 3:33pm

C++ Code

[Download]

/* 
 * File:   main.cpp
 * Author: josko
 *
 * Created on October 31, 2010, 5:29 PM
 */

#include <cstdlib>
#include <cstdio>
#include <string>
#include <vector>
#include <fstream>
#include <algorithm>

#include "sqlite3.h"

using namespace std;

// GLOBALS

sqlite3 *db;
vector<string> Row;

// HELPER FUNCTIONS

inline int Step( sqlite3_stmt *Statement )
{
	int RC = sqlite3_step( Statement );

if( RC == SQLITE_ROW )
	{
		Row.clear( );

for( int i = 0; i < sqlite3_column_count( Statement ); ++i )
		{
			char *ColumnText = (char *)sqlite3_column_text( Statement, i );

if( ColumnText )
				Row.push_back( ColumnText );
			else
				Row.push_back( string( ) );
		}
	}

return RC;
}

inline vector<string> Tokenize( string s, char delim )
{
	vector<string> Tokens;
	string Token;

for( string :: iterator i = s.begin( ); i != s.end( ); ++i )
	{
		if( *i == delim )
		{
			if( Token.empty( ) )
				continue;

Tokens.push_back( Token );
			Token.clear( );
		}
		else
			Token += *i;
	}

if( !Token.empty( ) )
		Tokens.push_back( Token );

return Tokens;
}

inline bool BanCheck( string server, string user, string ip )
{
	transform( user.begin( ), user.end( ), user.begin( ), (int(*)(int))tolower );
        bool Ban = false;
        sqlite3_stmt *Statement;

if( ip.empty( ) )
            sqlite3_prepare_v2( db, "SELECT name, ip, date, gamename, admin, reason FROM bans WHERE server=? AND name=?", -1, (sqlite3_stmt **)&Statement, NULL );
	else
            sqlite3_prepare_v2( db, "SELECT name, ip, date, gamename, admin, reason FROM bans WHERE (server=? AND name=?) OR ip=?", -1, (sqlite3_stmt **)&Statement, NULL );

if( Statement )
	{
		sqlite3_bind_text( Statement, 1, server.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 2, user.c_str( ), -1, SQLITE_TRANSIENT );

if( !ip.empty( ) )
			sqlite3_bind_text( Statement, 3, ip.c_str( ), -1, SQLITE_TRANSIENT );

int RC = Step( Statement );

if( RC == SQLITE_ROW )
		{
			if( Row.size( ) == 6 )
				Ban = true;
			else
				printf( "[SQLITE3] error checking ban - row doesn't have 6 columns\n" );
		}
		else if( RC == SQLITE_ERROR )
			printf( "[SQLITE3] error checking ban\n" );

sqlite3_finalize( Statement );
	}
	else
		printf( "[SQLITE3] prepare error checking ban\n" );

return Ban;
}

inline void BanAdd( string server, string user, string ip, string gamename, string admin, string reason )
{
	transform( user.begin( ), user.end( ), user.begin( ), (int(*)(int))tolower );
	sqlite3_stmt *Statement;
        sqlite3_prepare_v2( db, "INSERT INTO bans ( server, name, ip, date, gamename, admin, reason ) VALUES ( ?, ?, ?, date('now'), ?, ?, ? )", -1, (sqlite3_stmt **)&Statement, NULL );

if( Statement )
	{
		sqlite3_bind_text( Statement, 1, server.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 2, user.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 3, ip.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 4, gamename.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 5, admin.c_str( ), -1, SQLITE_TRANSIENT );
		sqlite3_bind_text( Statement, 6, reason.c_str( ), -1, SQLITE_TRANSIENT );

int RC = Step( Statement );

if( RC == SQLITE_DONE )
                    return;
		else if( RC == SQLITE_ERROR )
                    printf( "[SQLITE3] error adding ban\n" );

sqlite3_finalize( Statement );
	}
	else
		printf( "[SQLITE3] prepare error adding ban\n" );
}

// MAIN

int main(int argc, char** argv)
{
    if( argc > 2 )
    {
        if( sqlite3_open_v2( argv[1], (sqlite3 **)&db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL ) != SQLITE_OK )
                printf( "Couldn't open SQLite3 database\n" );

ifstream in;
        in.open( argv[2] );
        
        if( !in.fail( ) )
        {
            string Line;
            unsigned int BanCheckCounter = 0, BanAddCounter = 0;

while( !in.eof( ) )
            {
                getline( in, Line );

if( Line.empty( ) || Line[0] != '(' )
                    continue;

// SQL DUMP EXAMPLE:
                // (3467, 1, 'europe.battle.net', 'micheal1', '80.216.136.90', '2010-08-18 00:00:00.0', 'CF! rr2 na fow mp2 fi ur 2v2!', 'Tikka7', 'liiveri'),

Line = Line.substr( 1, Line.size() - 1 ); // 3467, 1, 'europe.battle.net', 'micheal1', '80.216.136.90', '2010-08-18 00:00:00.0', 'CF! rr2 na fow mp2 fi ur 2v2!', 'Tikka7', 'väkisin sitten'

vector<string> Parts = Tokenize( Line, ',' );

string server, user, ip, date, gamename, admin, reason;

server = Parts[2].substr( 2, Parts[2].size( ) - 2 );
                user = Parts[3].substr( 2, Parts[3].size( ) - 2 );
                ip = Parts[4].substr( 2, Parts[4].size( ) - 2 );

// printf( "DEBUG: \"%s\" \"%s\" \"%s\"\n", server.c_str(), user.c_str(), ip.c_str() );

++BanCheckCounter;

if( !BanCheck( server, user, ip ) )
                {
                    // date = Parts[5].substr( 2, Parts[5].size( ) - 2 );
                    gamename = Parts[6].substr( 2, Parts[6].size( ) - 2 );
                    admin = Parts[7].substr( 2, Parts[7].size( ) - 2 );
                    reason = Parts[8].substr( 2, Parts[8].size( ) - 2 );

printf( "BAN ADD: \"%s\" \"%s\" \"%s\"\n", gamename.c_str(), admin.c_str(), reason.c_str() );

BanAdd( server, user, ip, gamename, admin, reason );
                    ++BanAddCounter;
                }
                else
                    continue;
            }

printf( "Total bans checked: %d\nTotal bans added: %d", BanCheckCounter, BanAddCounter );

sqlite3_close( db );
        }
        else
            printf( "Couldn't read SQL file\n" );
        
    }
    else    
        printf( "Usage: sql_import <database> <sql file>\n" );
        
    return 0;
}

Syntax Highlighting

[Open in new window]

Author Comments

none

Rating

4.43 / 8
42 Votes