Return to sql.c CVS log

Up to [Public] / rpm2html

Started playing adding a MySQL database, Daniel.

/*
 * sql.c: front-end for MySQL database
 */

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include "sql.h"

/************************************************************************
 * 									*
 * 		Generic inititialisation/close of the DB		*
 * 									*
 ************************************************************************/

static MYSQL *sql = NULL;

int init_sql(const char *base, const char *user, const char *passwd) {
    if (base == NULL)
	base = "rpmfind";
    if (passwd == NULL)
	passwd = getenv("MySQL_PASS");
    if (user == NULL)
	user = getenv("MySQL_USER");
    if (user == NULL)
	user = getenv("USER");
    if (user == NULL)
	user = getenv("USERNAME");
    if (user == NULL)
	user = getenv("LOGNAME");
    sql = mysql_init(NULL);
    if (mysql_errno(sql)) {
	fprintf(stderr, "mysql_init failed: %s\n", mysql_error(sql));
	return(-1);
    }
    mysql_real_connect(sql, "localhost", user, passwd, base, 0, NULL, 0);
    if (mysql_errno(sql)) {
	fprintf(stderr, "mysql: connect as %s to %s failed: %s\n",
		user, base, mysql_error(sql));
	return(-1);
    }
    return(0);
}

int close_sql(void) {
    mysql_close(sql);
    if (mysql_errno(sql)) {
	fprintf(stderr, "mysql_close failed: %s\n", mysql_error(sql));
	return(-1);
    }
    return(0);
}

/************************************************************************
 * 									*
 * 		Generic functions to access the tables			*
 * 									*
 ************************************************************************/


int sql_update_id(const char *table, int id,
	          const char *field, const char *value) {
    MYSQL_RES *result;
    char query[1000];
    int nb_fields = 0;

    if ((table == NULL) ||
	(field == NULL) || (value == NULL))
	return(-1);

    snprintf(query, 999, "UPDATE %s SET %s='%s' WHERE ID=%d",
	     table, field, value, id);
    query[1000] = 0;
    if (mysql_query(sql, query)) {
	printf("sql_update_id: UPDATE %s %d failed: %s\n",
	       table, id, mysql_error(sql));
	return(-1);
    }
    result = mysql_store_result(sql);
    if (result != NULL) {
	nb_fields = mysql_num_fields(result);
	mysql_free_result(result);
    } else {
	nb_fields = 1;
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "sql_update_id UPDATE error: %s\n",
		 mysql_error(sql));
	return(-1);
    }
    return(nb_fields);
}

int sql_blind_insert(const char *table, const char *key,
                     const char *value, int id) {
    MYSQL_RES *result;
    char query[1000];

    if ((table == NULL) || (id <= 0) ||
	(key == NULL) || (value == NULL))
	return(-1);

    /*
     * Search first for the ID if it already exists
     */
    snprintf(query, 999, "INSERT INTO %s (ID, %s) VALUES (%d, '%s')",
	     table, key, id, value);
    query[1000] = 0;
    if (mysql_query(sql,query)) {
	fprintf(stderr, "sql_blind_insert Error: %s\n", mysql_error(sql));
	return(-1);
    }
    result = mysql_store_result(sql);
    if (result) {
	mysql_free_result(result);
	return(1);
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "sql_blind_insert Error: %s\n", mysql_error(sql));
	return(-1);
    }
    return(0);
}

int sql_update(const char *table, const char *name,
	       const char *field, const char *value) {
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[1000];
    int id;
    int nb_fields = 0;

    if ((name == NULL) || (table == NULL) ||
	(field == NULL) || (value == NULL))
	return(-1);

    /*
     * Search first for the ID if it already exists
     */
    snprintf(query, 999, "SELECT ID FROM %s WHERE Name='%s'", table, name);
    query[1000] = 0;
    if (mysql_query(sql,query)) {
	printf("sql_update: SELECT failed\n");
	return(-1);
    }

    result = mysql_use_result(sql);
    if (result) {
	while((row = mysql_fetch_row(result)))
	{
	    if (row[0] == NULL) {
		printf("sql_update: select ID for %s returns NULL !\n", name);
		return(-1);
	    }
	    if (sscanf(row[0], "%d", &id) != 1) {
		printf("sql_update: ID non numeric %s\n", row[0]);
		return(-1);
	    }
	    snprintf(query, 999, "UPDATE %s SET %s='%s' WHERE ID=%d LIMIT 1",
		     table, field, value, id);
	    query[1000] = 0;
	    mysql_free_result(result);
	    if (mysql_query(sql, query)) {
		printf("sql_update: UPDATE failed\n");
		return(-1);
	    }
	    result = mysql_store_result(sql);
	    if (result != NULL) {
		nb_fields = mysql_num_fields(result);
		mysql_free_result(result);
	    } else {
		fprintf(stderr, "sql_update UPDATE error: %s\n",
		       	mysql_error(sql));
		return(-1);
	    }
	    /* Do not loop ... only the first */
	    return(nb_fields);
	}
	mysql_free_result(result);
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "sql_update Error: %s\n", mysql_error(sql));
	return(-1);
    }
    return(nb_fields);
}

int sql_get_key(const char *table, const char *name) {
    int id;
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[200];

    if ((table == NULL) || (name == NULL))
	return(-1);

    /*
     * Search first for the ID if it already exists
     */
    snprintf(query, 199, "SELECT ID FROM %s WHERE Name='%s'", table, name);
    query[200] = 0;
    if (mysql_query(sql,query)) {
	printf("sql_create: SELECT %s failed %s\n", name, mysql_error(sql));
	return(-1);
    }

    result = mysql_use_result(sql);
    if (result) {
	while((row = mysql_fetch_row(result)))
	{
	    /*
	     * Lookup the first ID and return it
	     */
	    if (row[0] == NULL) {
		mysql_free_result(result);
		printf("sql_create: select returns NULL !\n");
		return(-1);
	    }
	    if (sscanf(row[0], "%d", &id) != 1) {
		mysql_free_result(result);
		printf("sql_create: ID non numeric %s\n", row[0]);
		return(-1);
	    }
	    mysql_free_result(result);
	    return(id);
	}
	mysql_free_result(result);
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "Error: %s\n", mysql_error(sql));
	return(-1);
    }

    /*
     * Do a creation
     */

    snprintf(query, 199, "INSERT INTO %s (Name) VALUES ('%s')", table, name);
    query[200] = 0;
    if (mysql_query(sql,query)) {
	printf("add_distrib: INSERT %s failed %s\n", name, mysql_error(sql));
	return(-1);
    }
    id = mysql_insert_id(sql);
    result = mysql_store_result(sql);
    if (result != NULL)
	mysql_free_result(result);
    return(id);
}

int sql_read_key(const char *table, const char *name) {
    int id;
    MYSQL_RES *result;
    MYSQL_ROW row;
    char query[200];

    if ((table == NULL) || (name == NULL))
	return(-1);

    /*
     * Search for the ID it has to exist
     */
    snprintf(query, 199, "SELECT ID FROM %s WHERE Name='%s'", table, name);
    query[200] = 0;
    if (mysql_query(sql,query)) {
	printf("sql_create: SELECT %s failed %s\n", name, mysql_error(sql));
	return(-1);
    }

    result = mysql_use_result(sql);
    if (result) {
	while((row = mysql_fetch_row(result)))
	{
	    /*
	     * Lookup the first ID and return it
	     */
	    if (row[0] == NULL) {
		mysql_free_result(result);
		printf("sql_create: select returns NULL !\n");
		return(-1);
	    }
	    if (sscanf(row[0], "%d", &id) != 1) {
		mysql_free_result(result);
		printf("sql_create: ID non numeric %s\n", row[0]);
		return(-1);
	    }
	    mysql_free_result(result);
	    return(id);
	}
	mysql_free_result(result);
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "Error: %s\n", mysql_error(sql));
	return(-1);
    }
    return(-1);
}

/************************************************************************
 *									*
 *				Tables handling				*
 *									*
 ************************************************************************/

int sql_rebuild_vendors(void) {
    const char *query =
"CREATE TABLE Vendors ( \n\
    ID int(11) NOT NULL auto_increment, \n\
    Name varchar(255) NOT NULL, \n\
    URL varchar(255), \n\
    Key1 text, \n\
    Key2 text, \n\
    Key3 text, \n\
    Description text, \n\
    PRIMARY KEY (ID), \n\
    KEY Name (Name(10)) \n\
)";

    if (mysql_query(sql,query)) {
	printf("add_distrib: CREATE TABLE Vendors failed %s\n",
	       mysql_error(sql));
	return(-1);
    }
    return(0);
}

int sql_rebuild_mirrors(void) {
    const char *query =
"CREATE TABLE Mirrors ( \n\
    ID int(11), \n\
    URL varchar(255) NOT NULL, \n\
    Country int(11), \n\
    UNIQUE(URL) \n\
)";

    if (mysql_query(sql,query)) {
	printf("add_distrib: CREATE TABLE Mirrors failed %s\n",
	       mysql_error(sql));
	return(-1);
    }
    return(0);
}

int sql_rebuild_distribs(void) {
    const char *query =
"CREATE TABLE Distribs ( \n\
    ID int(11) NOT NULL auto_increment, \n\
    Name varchar(255) NOT NULL, \n\
    Vendor int(11), \n\
    Path varchar(100) NOT NULL, \n\
    URL varchar(255), \n\
    URLSrc varchar(255), \n\
    Key1 text, \n\
    Key2 text, \n\
    Description text, \n\
    PRIMARY KEY (ID), \n\
    KEY Name (Name(10)) \n\
)";

    if (mysql_query(sql,query)) {
	printf("add_distrib: CREATE TABLE Distribs failed %s\n",
	       mysql_error(sql));
	return(-1);
    }
    return(0);
}


int sql_check_tables(void) {
    const char *query = "SHOW TABLES";
    MYSQL_RES *result;
    MYSQL_ROW row;
    int distribs = 0;
    int vendors = 0;
    int mirrors = 0;
    int rebuilt = 0;

    if (mysql_query(sql,query)) {
	printf("add_distrib: SHOW TABLES failed %s\n",
	       mysql_error(sql));
	return(-1);
    }

    result = mysql_use_result(sql);
    if (result) {
	while((row = mysql_fetch_row(result)))
	{
	    if (row[0] == NULL) {
		mysql_free_result(result);
		printf("sql_check_tables: SHOW TABLES returns NULL !\n");
		return(-1);
	    }
	    if (!strcmp(row[0], "Distribs"))
		distribs = 1;
	    if (!strcmp(row[0], "Vendors"))
		vendors = 1;
	    if (!strcmp(row[0], "Mirrors"))
		mirrors = 1;
	}
	mysql_free_result(result);
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "Error: %s\n", mysql_error(sql));
	return(-1);
    }

    if (!vendors) {
	fprintf(stderr, "Table Vendors disapeared: rebuilding it\n");
	if (!sql_rebuild_vendors())
	    rebuilt++;
    }
    if (!distribs) {
	fprintf(stderr, "Table Distribs disapeared: rebuilding it\n");
	if (!sql_rebuild_distribs())
	    rebuilt++;
    }
    if (!mirrors) {
	fprintf(stderr, "Table Mirrors disapeared: rebuilding it\n");
	if (!sql_rebuild_mirrors())
	    rebuilt++;
    }
    return(rebuilt);
}

/************************************************************************
 *									*
 *			Specific rpm2html functions			*
 *									*
 ************************************************************************/

int add_dist_mirror(int distrib, const char *URL, int country) {
    if (URL == NULL)
	return(-1);
    if (distrib < 0)
	return(-1);
    return(sql_blind_insert("Mirrors", "URL", URL, distrib));
}

int add_mirror(const char *Name, const char *URL, int country) {
    int distrib;

    if ((Name == NULL) || (URL == NULL))
	return(-1);
    distrib = sql_read_key("Distribs", Name);
    if (distrib < 0)
	return(distrib);
    return(sql_blind_insert("Mirrors", "URL", URL, distrib));
}

int add_distrib(const char *Name, const char *Vendor,
	const char *Path, const char *URL,
	const char *URLSrc, const char *Description) {
    int id, vendor;
    int nb_fields = 0;
    char VendorStr[15];

    if (Name == NULL)
	return(-1);

    id = sql_get_key("Distribs", Name);
    nb_fields = 1;
    if (Vendor != NULL) {
	vendor = sql_get_key("Vendors", Vendor);
	sprintf(VendorStr, "%d", vendor);
	nb_fields += sql_update_id("Distribs", id, "Vendor", VendorStr);
    }
    if (Path != NULL)
	nb_fields += sql_update_id("Distribs", id, "Path", Path);
    if (URL != NULL)
	nb_fields += sql_update_id("Distribs", id, "URL", URL);
    if (URLSrc != NULL)
	nb_fields += sql_update_id("Distribs", id, "URLSrc", URLSrc);
    if (Description != NULL)
	nb_fields += sql_update_id("Distribs", id,
				   "Description", Description);
    
    return(nb_fields);
}

int add_vendor(const char *Name, const char *URL, const char *Description) {
    int id;
    int nb_fields = 0;

    if (Name == NULL)
	return(-1);

    id = sql_get_key("Vendors", Name);
    nb_fields = 1;
    if (URL != NULL)
	nb_fields += sql_update_id("Vendors", id, "URL", URL);
    if (Description != NULL)
	nb_fields += sql_update_id("Vendors", id,
				   "Description", Description);
    
    return(nb_fields);
}

void show_vendors(void) {
    MYSQL_RES *result;
    MYSQL_ROW row;


    mysql_query(sql,"SELECT Name, URL FROM Vendors");
    result = mysql_use_result(sql);

    while((row = mysql_fetch_row(result)))
    {
	if (row[0] == NULL)
	    printf("NULL !\n");
	else {
	    if (row[1] == NULL)
		printf("%s : no url\n", row[0]);
	    else
		printf("%s : %s\n", row[0], row[1]);
	}
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "Error: %s\n", mysql_error(sql));
    }

}

void show_distribs(void) {
    MYSQL_RES *result;
    MYSQL_ROW row;


    mysql_query(sql,"SELECT Name, Path, URL FROM Distribs");
    result = mysql_use_result(sql);

    while((row = mysql_fetch_row(result)))
    {
	if (row[0] == NULL)
	    printf("NULL !\n");
	else {
	    if (row[1] == NULL)
		printf("%s : no Path\n", row[0]);
	    else {
		if (row[2] == NULL)
		    printf("%s : %s : no url\n", row[0], row[1]);
		else
		    printf("%s : %s : %s\n", row[0], row[1], row[2]);
	    }
	}
    }
    if(mysql_errno(sql)) {
	fprintf(stderr, "Error: %s\n", mysql_error(sql));
    }

}

#ifdef STANDALONE
void usage(const char *name) {
    printf("%s: usage\n", name);
    printf("	vendors: list the registered vendors\n");
    printf("	distribs: list the registered distribs\n");
    printf("	add distrib name [vendor [path [url [ urlsrc [description]]]]]\n");
    printf("	add vendor name [url [description]]\n");
    printf("	add mirror distrib url\n");
    exit(1);
}

int main(int argc, char **argv) {
    int res;

    if (argc < 2)
        usage(argv[0]);

    if (init_sql(NULL, NULL, NULL) < 0)
	exit(1);

    res = sql_check_tables();
    if (res > 0) {
	printf("rebuilt %d tables\n", res);
    }

    if (!strcmp(argv[1], "vendors"))
	show_vendors();
    else if (!strcmp(argv[1], "distribs"))
	show_distribs();
    else if (!strcmp(argv[1], "add")) {
	if (argc < 5)
	    usage(argv[0]);
	if (!strcmp(argv[2], "distrib")) {
	    char *Name = NULL;
	    char *Path = NULL;
	    char *Vendor = NULL;
	    char *URL = NULL;
	    char *URLSrc = NULL;
            char *Description = NULL;
	    if ((argc > 3) && (argv[3][0] != 0))
		Name = argv[3];
	    if ((argc > 4) && (argv[4][0] != 0))
		Vendor = argv[4];
	    if ((argc > 5) && (argv[5][0] != 0))
		Path = argv[5];
	    if ((argc > 6) && (argv[6][0] != 0))
		URL = argv[6];
	    if ((argc > 7) && (argv[7][0] != 0))
		URLSrc = argv[7];
	    if ((argc > 8) && (argv[8][0] != 0))
		Description = argv[8];
	    res = add_distrib(Name, Vendor, Path, URL, URLSrc, Description);
	    printf("updated %d fields\n", res);
	} else if (!strcmp(argv[2], "vendor")) {
	    char *Name = NULL;
	    char *URL = NULL;
            char *Description = NULL;
	    if ((argc > 3) && (argv[3][0] != 0))
		Name = argv[3];
	    if ((argc > 4) && (argv[4][0] != 0))
		URL = argv[4];
	    if ((argc > 5) && (argv[5][0] != 0))
		Description = argv[5];
	    res = add_vendor(Name, URL, Description);
	    printf("updated %d fields\n", res);
	} else if (!strcmp(argv[2], "mirror")) {
	    char *Name = NULL;
	    char *URL = NULL;
	    if ((argc > 3) && (argv[3][0] != 0))
		Name = argv[3];
	    if ((argc > 4) && (argv[4][0] != 0))
		URL = argv[4];
	    res = add_mirror(Name, URL, 0);
	    printf("updated %d fields\n", res);
	}
    }

    if (close_sql() < 0)
	return(1);
    exit(0);
}

#endif