/* OpenWebSpider Macro
 *
 *  Author:                       Stefano Alimonti aka Shen139
 *  Version:                      0.2
 *  For OpenWebSpider Version:    0.6
 *  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("  -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];
unsigned int bCreateTab=0;
unsigned int bReindex=0;
unsigned int bFlushTempTab=0;
unsigned int bFlushHostList=0;
unsigned int bOptimize=0;
int c;

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

	/* ...un-safe... ?!? */
	strcpy(db1,"hosts");
	strcpy(db2,"spiderdb");
	strcpy(db3,"temptables");


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

    while ((c = getopt(argc, argv, "suporthqcd")) != -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;

        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 CHARSET=latin1; ",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 CHARSET=latin1;",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 table : 'wordlist'...");
		sprintf(sqlQuery,"CREATE TABLE %s.wordlist ("
						"     `id` int(11) NOT NULL auto_increment,"
						"     `word` varchar(50) NOT NULL,"
						"  PRIMARY KEY  (`id`),"
						"  UNIQUE KEY `word` (`word`)) "
						" 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 table : 'ii'...");
		sprintf(sqlQuery,"CREATE TABLE %s.ii ("
						"     `wordid` int(11) NOT NULL,"
						"     `pageid` int(11) NOT NULL,"
						"     `position` int(11) NOT NULL default '0')"
						" 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");
    }

    mysql_close(&mysql);

return 1;
}