/* OpenWebSpider Macro
 *
 *  Author:                       Stefano Alimonti aka Shen139
 *  Version:                      0.1
 *  For OpenWebSpider Version:    0.5
 *  Mail:                         shen139 [at] openwebspider (dot) org
 *
 *  Compile with: $ make ows_macro
 *
 * This file is part of OpenWebSpider
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 */
 
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#ifdef WIN32
  #include <windows.h>
  #pragma comment(lib,"libmySQL.lib")
  #include "E:\\Programmi\\MySQL\\MySQL Server 5.0\\include\\mysql.h"
  #include "..\\getopt.c"
#else
  #include <mysql/mysql.h>
  #include "../getopt.h"
#endif

char db1[100];
char db2[100];
char db3[100];

int sqlConnect(char* hostname, char* username, char* password, char* table,MYSQL* rMysql)
{

    mysql_init(rMysql);

    mysql_options(rMysql,MYSQL_READ_DEFAULT_GROUP,"OpenWebSpider");
    mysql_options(rMysql,MYSQL_OPT_COMPRESS,0);

    if (!mysql_real_connect(rMysql,hostname,username,password,table,0,NULL,0))
        return 0;

return 1;
}

int usage(char* error)
{
    printf("\nows_macro\n");
    printf(" Arguments:\n");
    printf(" =Mysql=\n");
    printf("  -s <server>      : (needed)\n");
    printf("  -u <username>    : (needed)\n");
    printf("  -p <password>    : (needed)\n");
	printf("  -d <db1> <db2> <db3> : Set the names of the databases! Default: hosts spiderdb temptables\n");
    printf("  -q \"<SQL-Query>\" : Executes an SQL-Query\n");
    printf(" =Macro=\n");
	printf("  -c               : Creates all tables needed by OpenWebSpider\n");
    printf("  -r               : Set all hosts in hostlist to be Re-Indexed\n");
    printf("  -t               : Flushes and drop Temporary tables older than 3 days\n");
    printf("  -h               : Flushes all hosts in scanning for more than 3 days\n");
	printf("  -a               : Converts DB from version 0.4 to 0.5\n");
    printf("  -o               : Optimizes tables/indexes (Use with caution! It may take a very long time)\n");
	printf(" =Examples=\n");
	printf("  ows_macro -s localhost -u username -p password -c\n");
	printf("   ^ Creates all the databases and tables needed by openwebspider with the default names\n");
	printf("  ows_macro -s localhost -u username -p password -d newsite_hosts newsite_spiderdb newsite_temptables -c\n");
	printf("   ^ Creates all the databases and tables needed by openwebspider with the specified names\n");
	printf("  ows_macro -s localhost -u username -p password -q \"UPDATE hosts.hostlist SET status=1;\"\n");
	printf("   ^ Executes the query that set all hosts in hostlist with status=1 (host indexed)");
    printf("\n\n=ERROR=\n %s\n\n", error);

exit(0);
}

int main(int argc, char*argv[])
{
MYSQL mysql;
char sMysqlServer[200];
char sMysqlUsername[20];
char sMysqlPassword[20];
char sSQLQuery[10000];    //~10Kb
unsigned int bCreateTab=0;
unsigned int bReindex=0;
unsigned int bFlushTempTab=0;
unsigned int bFlushHostList=0;
unsigned int bOptimize=0;
unsigned int bAlterTable=0;
int c;

    memset(sMysqlServer,0,200);
    memset(sMysqlUsername,0,20);
    memset(sMysqlPassword,0,20);
    memset(sSQLQuery,0,10000);

	strcpy(db1,"hosts");
	strcpy(db2,"spiderdb");
	strcpy(db3,"temptables");


    if(argc<8)
        usage("Too few arguments");

    while ((c = getopt(argc, argv, "suporthqcda")) != -1)
    switch (c)
    {
		case 'd':
			if(optind+2>=argc)
                usage("Database name needed");

			strcpy(db1,argv[optind++]);
			strcpy(db2,argv[optind++]);
			strcpy(db3,argv[optind]);

		break;
        case 's':                    //mysql Server hostname or IP (needed)
            if(optind>=argc)
                usage("Mysql server needed");
           
            if(strlen(argv[optind])>200-1)
                usage("Mysql server too long");
           
            strncpy(sMysqlServer,argv[optind],200-1);
           
            optind++;
			
        break;
        case 'u':                   //Username (needed)
            if(optind>=argc)
                usage("Username needed");
           
            if(strlen(argv[optind])>20-1)
                usage("Username too long");
           
            strncpy(sMysqlUsername,argv[optind],20-1);
           
            optind++;

        break;
        case 'p':                   //Password (needed)
            if(optind>=argc)
                usage("Password needed");
           
            if(strlen(argv[optind])>20-1)
                usage("Username too long");
           
            strncpy(sMysqlPassword,argv[optind],20-1);
           
            optind++;

        break;
        case 'q':                   //SQL-Query
            if(optind>=argc)
                usage("SQL-Query needed");
           
            if(strlen(argv[optind])>10000-1)
                usage("SQL-Query too long");
           
            strncpy(sSQLQuery,argv[optind],10000-1);
           
            optind++;

        break;
        case 'c':                   //Create all tables needed by OpenWebSpider
            bCreateTab=1;
        break;

        case 'r':                   //set all hosts in hostlist with status=0 (to Reindex all hosts)
            bReindex=1;
        break;
        case 't':                   //flush outdated Temporary Tables (older than 3 days)
            bFlushTempTab=1;
        break;
        case 'h':                   //flush outdated Hosts in Hostlist (hosts in status=2 for more than 3 days)
            bFlushHostList=1;
        break;
        case 'o':                   //Optimize tables/indexes
            bOptimize=1;
        break;
        case 'a':                   //Optimize tables/indexes
            bAlterTable=1;
        break;


        default:
            usage("\nWrong argument\n");
        break;
    }

    if(!(sMysqlServer[0] && sMysqlUsername[0] && sMysqlPassword[0]))
        usage("Mysql server and username and password needed");

	printf("Server      : %s\n",sMysqlServer);
	printf("Username    : %s\n",sMysqlUsername);
	printf("Db1         : %s\n",db1);
	printf("Db2         : %s\n",db2);
	printf("Db3         : %s\n\n",db3);

    printf("Connecting to mysql...");
   
    if(sqlConnect(sMysqlServer, sMysqlUsername, sMysqlPassword, "",&mysql)==0)
    {
        printf("ERROR(%s)\n",mysql_error(&mysql));
        mysql_close(&mysql);
        return -1;
    }
   
    printf("OK\n\n");

    if(bCreateTab)
    {
    int ret;
	char sqlQuery[800];
       
        printf("+ Creating database '%s'...",db1);
   
		sprintf(sqlQuery,"CREATE DATABASE %s;",db1);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");

		printf(" - Creating table : 'hostlist'...");
		
		sprintf(sqlQuery,"CREATE TABLE %s.hostlist (       `hostname` varchar(100) NOT NULL default '', `port` int(6) NOT NULL default '80',            `status` int(11) NOT NULL default '0',            `lastvisit` varchar(10) default NULL,            `pages` int(11) NOT NULL default '0',            `priority` int(11) NOT NULL default '0',            PRIMARY KEY  (`hostname`)          ) ENGINE=MyISAM; ",db1);
		ret=mysql_query(&mysql, sqlQuery);
		if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
		
		printf(" - Creating table : 'rels'...");
		sprintf(sqlQuery,"CREATE TABLE %s.rels (`host` varchar(100) NOT NULL, `page` varchar(255) NOT NULL,`linkedhost` varchar(100) NOT NULL,`linkedpage` varchar(255) NOT NULL,UNIQUE KEY `unique_index` (`host`,`page`,`linkedhost`,`linkedpage`)) ENGINE=MyISAM;",db1);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");

		printf("+ Creating database '%s'...",db2);
   
		sprintf(sqlQuery,"CREATE DATABASE %s;",db2);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
		
		printf(" - Creating table : 'pagelist'...");

		sprintf(sqlQuery,"CREATE TABLE %s.pagelist (`idpage` int(11) NOT NULL auto_increment,`hostname` varchar(100) NOT NULL default '',`page` varchar(255) NOT NULL default '',`description` varchar(255) NOT NULL default '',`html` LONGTEXT NOT NULL,`htmlcache` LONGBLOB, `version` int(11) NOT NULL default '0',`level` int(11) NOT NULL default '0',`rank` int(11) NOT NULL default '0',`date` varchar(10) NOT NULL default '',`time` varchar(10) NOT NULL default '',PRIMARY KEY  (`idpage`),FULLTEXT KEY `description` (`description`,`html`,`hostname`,`page`),FULLTEXT KEY `desc` (`description`)) ENGINE=MyISAM CHARSET=utf8 ;",db2);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");

		printf("+ Creating database '%s'...",db3);
   		
		sprintf(sqlQuery,"CREATE DATABASE %s;",db3);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
		
		printf(" - Creating table : 'tablelist'...");

		sprintf(sqlQuery,"CREATE TABLE %s.tablelist ( `idtable` int(11) NOT NULL auto_increment, `name` varchar(20) NOT NULL default '', `status` int(2) unsigned NOT NULL default '0', `strdate` varchar(10) NOT NULL default '', PRIMARY KEY  (`idtable`)) ENGINE=MyISAM;",db3);
		ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");

    }

    if(sSQLQuery[0])
    {
    int ret;
       
        printf("Executing SQL-Query...");
   
        ret=mysql_query(&mysql, sSQLQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
    }

    if(bReindex)
    {
    int ret;
	char sqlQuery[200];
       
        printf("Setting all hosts in hostlist as 'to reindex'...");

		sprintf(sqlQuery,"UPDATE %s.hostlist SET status=0;",db1);
        ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
    }

    if(bFlushTempTab)
    {
    int ret;
    MYSQL_RES* tRes;
    MYSQL_ROW row;
    char sqlQuery[200];

        printf("+ Flushing Temporary Tables\n");
   
		sprintf(sqlQuery,"SELECT name FROM %s.tablelist WHERE DATE_SUB(CURDATE(),INTERVAL 3 DAY)>strdate;",db3);
        ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
        {

            tRes=mysql_store_result(&mysql);
            if(tRes)
            {
                while ((row = mysql_fetch_row(tRes))!=NULL)
                {
                char tmpQuery[200];

                    printf(" - Flushing table: %s...",row[0]);
                   
                    sprintf(tmpQuery,"INSERT DELAYED INTO %s.pagelist (hostname,page,description,html,htmlcache,version,date,time,level) select hostname,page,description,html,htmlcache,version,date,time,level from %s.%s;",db2,db3,row[0]);
                   
                    ret=mysql_query(&mysql, tmpQuery);
                    if(ret)
                    {
                        printf("ERROR(%s)\n",mysql_error(&mysql));
                        continue;
                    }

                    sprintf(tmpQuery,"DROP TABLE %s.%s",db3,row[0]);

                    ret=mysql_query(&mysql, tmpQuery);
                    if(ret)
                    {
                        printf("ERROR(%s)\n",mysql_error(&mysql));
                        continue;
                    }
                    else
                        printf("OK\n");

                }    //while ((row = mysql_fetch_row(tRes))!=NULL)

            }    // if(tRes)

            printf("+ Deleting Temporary Tables...");
			sprintf(sqlQuery,"DELETE FROM %s.tablelist WHERE DATE_SUB(CURDATE(),INTERVAL 3 DAY)>strdate;",db3);
            ret=mysql_query(&mysql, sqlQuery);
            if(ret)
                printf("ERROR(%s)\n",mysql_error(&mysql));
            else
                printf("OK\n");

            mysql_free_result(tRes);
        }
    }

    if(bFlushHostList)
    {
    int ret;
	char sqlQuery[200];
       
        printf("Flushing hosts...");
   
		sprintf(sqlQuery,"UPDATE %s.hostlist SET status=0 WHERE DATE_SUB(CURDATE(),INTERVAL 3 DAY)>lastvisit AND status =2;",db1);
        ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
    }

    if(bOptimize)
    {
    int ret;
	char sqlQuery[200];
   
        printf("Optimizing tables...");
   
		sprintf(sqlQuery,"OPTIMIZE TABLE %s.pagelist, %s.rels, %s.hostlist;",db2,db1,db1);
        ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
       
        mysql_free_result(mysql_use_result(&mysql));    //Commands out of sync; you can't run this command now
       
        printf("Optimizing indexes...");
   
		sprintf(sqlQuery,"REPAIR TABLE %s.pagelist QUICK;",db2);
        ret=mysql_query(&mysql,sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
            printf("OK\n");
    }

	if(bAlterTable)
	{
    int ret;
	char sqlQuery[200];
       
        printf("Converting OpenWebSpider DB from version 0.4 to 0.5...");
   
		sprintf(sqlQuery,"ALTER TABLE `%s`.pagelist ,ADD COLUMN `htmlcache` LONGBLOB   NULL  after `html`;",db2);
        ret=mysql_query(&mysql, sqlQuery);
        if(ret)
            printf("ERROR(%s)\n",mysql_error(&mysql));
        else
        {
			sprintf(sqlQuery,"CREATE TABLE %s.rels (`host` varchar(100) NOT NULL, `page` varchar(255) NOT NULL,`linkedhost` varchar(100) NOT NULL,`linkedpage` varchar(255) NOT NULL,UNIQUE KEY `unique_index` (`host`,`page`,`linkedhost`,`linkedpage`)) ENGINE=MyISAM;",db1);
			ret=mysql_query(&mysql, sqlQuery);
			if(ret)
				printf("ERROR(%s)\n",mysql_error(&mysql));
			else
				printf("OK\n");
		}    
	}
   
    mysql_close(&mysql);

return 1;
}