Wrapper MySQL

21 septembre 2007
c sql

Classe : MySQL wrapper

Une classe qui utilise dlopen (équivalent de LoadLibrary sous Win32) pour ne pas avoir à lier libmysqlclient à un programme. Comme dans n'importe quel wrapper : il n'y a rien ici de bien compliqué ou de bien intéressant. C'est juste un bout de code utile pour se simplifier la vie.

#include "dlfcn.h"
#include <mysql.h>

class   MySQL
{
    public:
        MySQL()
        {
            handle = NULL;
            LibHandle = dlopen("/usr/local/mysql/lib/mysql/libmysqlclient.so", RTLD_LAZY);
            //Tous les dlsym dont on a besoin
            MySQL_Init = (MYSQL* (*)(MYSQL*))dlsym(LibHandle, "mysql_init");
            MySQL_Close = (void (*)(MYSQL*))dlsym(LibHandle, "mysql_close");
            MySQL_Real_Connect = (MYSQL* (*)(MYSQL *, const char *, const char *, const char *, const char *, unsigned int, const char *, unsigned long))dlsym(LibHandle, "mysql_real_connect");
            MySQL_Error = (const char*(*)(MYSQL*)) dlsym(LibHandle, "mysql_error");
            MySQL_Real_Query = (int (*)(MYSQL *, const char *, unsigned long)) dlsym(LibHandle, "mysql_real_query");
            MySQL_Use_Result = (MYSQL_RES* (*)(MYSQL*)) dlsym(LibHandle, "mysql_use_result");
            MySQL_Store_Result = (MYSQL_RES* (*)(MYSQL*)) dlsym(LibHandle, "mysql_store_result");
            MySQL_Fetch_Row = (MYSQL_ROW (*)(MYSQL_RES *)) dlsym(LibHandle, "mysql_fetch_row");
            MySQL_Free_Result = (void (*)(MYSQL_RES*))dlsym(LibHandle, "mysql_free_result");
            MySQL_End_Library = (void (*)())dlsym(LibHandle, "mysql_library_end");
            MySQL_Server_Init = (void (*)())dlsym(LibHandle, "mysql_server_init");
            MySQL_Server_End = (void (*)())dlsym(LibHandle, "mysql_server_end");
        }
        ~MySQL()
        {
            MySQL_Close(handle);
            MySQL_Server_End();
            dlclose(LibHandle);
        }
        bool    init()
        {
            handle = MySQL_Init(NULL);
            return handle != NULL;
        }
        void    serverInit()                {   MySQL_Server_Init();    }
        bool    connect(char* dbName, char* userName, char* passwd)
        {
            MYSQL*  Handle = MySQL_Real_Connect(handle, NULL, userName, passwd, dbName, 0, NULL, 0);
            return Handle == handle;
        }
        bool    query(const char* query)
        {
            bool    Ret = MySQL_Real_Query(handle, query, strlen(query)) == 0;
            if (!Ret)       return false;
            rows = MySQL_Store_Result(handle);
            return rows != NULL;
        }
        bool    freeResult()                {   MySQL_Free_Result(rows);    }
        bool    fetchRow(MYSQL_ROW& row)
        {
            row = MySQL_Fetch_Row(rows);
            return !(row == NULL);
        }

    protected:
        void*   LibHandle;
        MYSQL*  handle;
        MYSQL_RES*  rows;
        //Les pointeurs vers les fonctions de la lib MySQL
        MYSQL*  (*MySQL_Init)(MYSQL*);
        void    (*MySQL_Close)(MYSQL*);
        MYSQL* (*MySQL_Real_Connect)(MYSQL *, const char *, const char *, const char *, const char *, unsigned int , const char *, unsigned long );
        const char* (*MySQL_Error)(MYSQL*);
        int (*MySQL_Real_Query)(MYSQL *, const char *, unsigned long);
        MYSQL_RES* (*MySQL_Store_Result)(MYSQL*);
        MYSQL_RES* (*MySQL_Use_Result)(MYSQL*);
        MYSQL_ROW (*MySQL_Fetch_Row)(MYSQL_RES *);
        void    (*MySQL_Free_Result)(MYSQL_RES*);
        void    (*MySQL_End_Library)();
        void    (*MySQL_Server_Init)();
        void    (*MySQL_Server_End)();
};

Le pays d'une adresse IP

19 août 2007
server sql geoloc

Obtenir le pays correspondant à une adresse IP

Vous vous souvenez de GeoIp, produit de la société MaxMind ? Ils ont un exécutable à installer sur un serveur pour obtenir le pays correspondant à une adresse IP.

J'avais deux soucis quand j'utilisais ce produit :

  • d'une part j'avais affaire à un binaire dont je n'avais pas les sources, et faire tourner quelque chose d'opaque sur un serveur est une invitation au hack pour des escrocs russes et
  • d'autre part j'étais tributaire de leurs mises à jour des données également opaques.

Tout peut se résoudre en utilisant le site ip-to-country. Leur méthode ? Faire que le pays d'une adresse IP soit obtenu par une simple requête SQL, ce qui donne :

  • qu'il n'y a plus de binaire douteux à installer sur mon précieux serveur de prod
  • et que je bénéficie de toutes les facilités du language SQL et de ses implémentations de mon choix (MySQL et PosgreSQL).

Pour fixer les idées la requête en question est presque aussi simple que celle-ci :

select countryName from ip2Country where \$MonIP<=highLimitIP and \$MonIP>=lowLimitIP;

Malheureusement les opérateurs <= et >= ne fonctionnent pas nativement sur les bases de données entre adresse IPs classiques. On va donc devoir les convertir en adresses IP numériques, c'est-à-dire en simples nombres entiers. Ainsi l'adresse 192.168.1.1 deviendra 3 136 235 777 = 192*2^24 + 168*2^16 + 1*2^8 + 1*2^0.

La source des données

C'est un simple fichier CSV téléchargeable depuis le site principal et régulièrement mis à jour. Voici un extrait pour examiner le format d'une ligne type :

...
"202385408","202385919","PR","PRI","PUERTO RICO"
"202385920","202706431","US","USA","UNITED STATES"
"202706432","202706943","PR","PRI","PUERTO RICO"
"202706944","203197063","US","USA","UNITED STATES"
...

Les deux premiers champs définissent un intervalle d'adresses IP au format numérique (par opposition au format séparé par des points) tel qu'une IP comprise dans cet intervalle est réputée représenter une machine installée dans le pays indiqué par les champs qui suivent. Les trois champs en question sont : le code ISO 3166-1 alpha 2 à deux lettres du pays, son code ISO 3166-1 alpha 3 à trois lettres et son nom complet.

Avant d'insérer les données dans le moteur de base de données, il faut nettoyer le fichier d'entrée pour que MySQL puisse le traiter sans complications :

cat ip-to-country.csv | sed 's/\"//g;s/\r//g' > ip2Country.csv
On élimine les guillemets et \\r

Ensuite, à partir d'un prompt MySQL et avec le fichier csv nettoyé et dans le path, on peut créer la table qui va héberger nos données :

create table ip2Country
    (
        lowIP int unsigned,
        highIP int unsigned,
        code2 varchar(2),
        code3 varchar(3),
        name varchar(60),
        INDEX(lowIP)
    ) ENGINE=INNODB;
Attention : un int ne suffit pas pour les IPs numériques, il faut des int non-signés

On peut terminer cette phase d'installation en important les données du fichier transformé dans la table MySQL que l'on vient de créer avec la commande suivante :

load data local infile './ip2Country.csv' into table ip2Country
    fields terminated by ","
    lines terminated by "\n"
    (lowIP, highIP, code2, code3, name);

Le résultat

Il reste donc l'essentiel et le plus simple à faire : interroger la base de données avec une IP quelconque pour en déduire le pays dans lequel la machine correspondante est installée.

Ainsi, si on utilise PHP on peut donc avoir la fonction suivante et triviale qui renvoie le pays correspondant à une adresse IP passée en paramètre :

function    IPToCountry($IP)
{
    $NumIP = ip2long($IP);      //PHP possède une fonction de conversion pour les adresses IP
    $Result = mysql_query("select * from ip2Country where lowIP<=$NumIP and highIP>=$NumIP;");
    $Result = mysql_fetch_array($Result);
    return $Result[0];
}

Tant qu'on y est, puisque j'aime le Bash, voici un oneliner qui fait la même chose depuis la ligne de commande :

mysql --silent --execute
     "SELECT name FROM ip2Country WHERE lowIP<=inet_aton('$IP') AND highIP>=inet_aton('$IP')"

L'intérêt de cette solution réside donc à la fois dans la simplicité de sa mise en oeuvre et dans son ouverture totale. On a vu qu'en quelques minutes on dispose d'une solution fonctionnelle, duplicable, d'un niveau demandant de faibles compétences techniques et à moindre coût.

Les vins en bases de données

7 avril 2007
web server sql

Les vins

On décrit ici les différentes tables d'une base de données modélisant des 'vins'. Ce terme est un peu vague quand on se penche dessus : ça peut être une bouteille donnée ou un vin d'un producteur ou encore le vin d'une région. 

Les utilisateurs
    Ils peuvent se connecter en administrateur sur le site et peuvent modifier le contenu, poser leurs critiques etc.

Les vins
    Un chateau-margot grand-cru de chez Rossignol de 1964 à base de Chardonnay.
    Cette table
    Type du vin : blanc, rouge, rosé

Les terroirs
    Chablis etc
    Map binaire de coordonnées GPS (c'est peut-être un peu extrème mais ça m'intéresse)

Les cépages
    Chardonnay, Pinot noir, Chablis blanc etc
    Liés à un terroir
    Petit descriptif
    Type de raisin : blanc/noir
    Q : peut-il y avoir un même cépage dans plusieurs terroirs ? A priori oui, mais je veux une vision claire...

Vins<-->Cépages
    Many2many pour indiquer les cépages entrant dans la composition d'un vin donné

Terroir<-->Cépage = Parcelle

Les années : table représentant la bouteille sur une table (enfin !)
    Lien vers un vin
    Lien vers un producteur/récoltant

Récolte
    Date de début et de fin
    Nombre d'ouvriers

Les producteurs/récoltants/coopérative
    Nom, date de la première vendange
    Si récoltant :
        cépages récoltés et/ou utilisés (il peut acheter du raisin ailleurs et mélanger avec sa propre récolte)

Adresses
    Lien vers un producteur

Les critiques
    Une critique peut être formulée par un utilisateur ou pas

Proximité des villes françaises

17 décembre 2006
c server bash sql geoloc

SIG - Les villes de France et leurs proximité

J'ai pour but d'analyser les 38052 communes de France par rapport à une ville "pivot", et parmi celles qui comptent plus de 5000 habitants, d'en déduire une liste triée des villes les plus proches. Bien évidemment les données sont stockées dans une base de données (MySQL pour ne pas la nommer), que j'ai reprise depuis ce site très intéressant et la volonté ici est d'être le plus performant possible, d'où mon choix du C++.

Tout d'abord il me faut présenter la structure de la base de données, dans un pseudo-langage de description qui m'est propre mais simple à comprendre :

<component name='erpCity' basedOn="qbase" language="FR" fullName="Villes" Gender="female" tableName='erpCities'>
    <fields>
        <shorttext name='inseeCode' formHeader='Code INSEE'/>
        <shorttext name='name' formHeader='Nom de la ville'/>
        <float name='latitude' formHeader='Latitude (en radians)'/>
        <float name='longitude' formHeader='Longitude (en radians)'/>
        <shorttext name='zipCode' formHeader='Code postal'/>
        <integer name='pop' formHeader='Population'/>
        <float name='density' formHeader='Densité de population'/>
    </fields>
</component>

L'insertion de ces données dans une base MySQL se fait de façon directe en PHP :

<html>
<head>
    <title>Création de la base de données des villes</title>
    <meta http-equiv='Content-Type' value='test/html; charset=utf-8'/>
</head>
<body>
<?

include_once "./dir.php";
include_once $gIncludeDir."/__helpers.php";
include_once "component.php";

    $Datas = file_get_contents("villes.csv");
    $FieldNames = array("Insee"=>"inseeCode", "Nom"=>"name",
                        "LatitudeRadian"=>"latitude", "LongitudeRadian"=>"longitude",
                        "CodePostal"=>"zipCode", "NombreHabitants"=>"pop", "Densite"=>"density");
    $Lines = explode("\n", $Datas);
    $NbLines = count($Lines);
    echo "Nombre de communes : $NbLines<br/>\n";
    $Comp = new Component("city");
    $Comp->CreateTable();       //On réinitialise la table à chaque fois
    $GlobalRet = true;
    for($i=0; $i<$NbLines; $i++)        {
        if (!$i)        continue;       //On ignore la première ligne
        $Values = explode(";", $Lines[$i]);
        //
        $Query = "insert into qt_erpCities (id, inseeCode, name, latitude, longitude, zipCode, pop, density) values (0, \"$Values[0]\", \"$Values[1]\", $Values[2], $Values[3], \"$Values[4]\", $Values[5], $Values[6]);";
        if (!($Ret = DB_ExecQuery($Query)))     echo "Problème d'insertion : <blockquote>".mysql_error()."</blockquote>\n";
        $GlobalRet &= $Ret;
    }
    if (!$GlobalRet)        echo "<p>Il y a eu au moins un problème d'insertion.</p>";

?>

</body>
</html>

L'exécutable qui tournera sur le serveur utilise un tri Radix et un wrapper MySQL pour simplifier l'écriture :

class   CityRanks : public Application
{
    struct  City
    {
        float   latitude, longitude;
        float   distance;
    };
    public:
        CityRanks()     {       }
        virtual     int run()
        {
            int     i;
            //On parse les paramètres de la ligne de commandes
            int     NbParamsToParse = _argc-1;
            if (NbParamsToParse < 2)        return EXIT_FAILURE;
            if (NbParamsToParse > 2)        NbParamsToParse = 2;
            int*    Params = new int[2];
            for(i=0; i<NbParamsToParse; i++)        sscanf(_argv[i+1], "%d", &Params[i]);
            //Paramètres de la ligne de commande :
            int Pivot = Params[0];
            int NbResults = Params[1];
            //
            MySQL*  DB = new MySQL();
            City**  Cities = new City*[1700];
            char**  CityNames = new char*[1700];
            memset(CityNames, 0, sizeof(char*)*1700);
            int     NbCities = 0;
            //
            DB->serverInit();
            if (DB->init() && DB->connect("Nom de la base de données", "Nom de l'utilisateur", "Mot de passe de l'utilisateur"))        {
                if (DB->query("select name,latitude,longitude from qt_erpCities where pop>=5000 group by inseeCode order by pop desc;"))        {
                    City*   CurCity = NULL;
                    MYSQL_ROW   Row;
                    while (DB->fetchRow(Row))       {
                        CurCity = Cities[NbCities] = new City;
                        string_affectCopy(&CityNames[NbCities], Row[0]);
                        sscanf(Row[1], "%f", &CurCity->latitude);
                        sscanf(Row[2], "%f", &CurCity->longitude);
                        NbCities++;
                    }
                    DB->freeResult();
                }   else    return EXIT_FAILURE;
            }   else    return EXIT_FAILURE;
            //Je me suis inspiré de http://en.wikipedia.org/wiki/Great-circle_distance
            float   EarthRadius = 6372.795f;
            float   PivotLongitude = Cities[Pivot]->longitude;
            for(i=0; i<NbCities; i++)       {
                //On calcule la distance entre la ville <i> et la ville témoin/pivot
                float   Lat1 = Cities[i]->latitude;
                float   Lat2 = Cities[Pivot]->latitude;
                float   dLat = Lat1 - Lat2;
                float   dLong = Cities[i]->longitude - PivotLongitude;
                float   SinLat = sin(dLat/2);
                float   SinLong = sin(dLong/2);
                float   dSigma = 2.0f * asin(sqrt(SinLat*SinLat+cos(Lat1)*cos(Lat2)*SinLong*SinLong));
                Cities[i]->distance = EarthRadius * dSigma;
            }
            //On trie les résultats en fonction de la distance
            //On prépare le tableau à trier
            int*    Distances = new int[NbCities*2];
            for(i=0; i<NbCities; i++)       {
                Distances[2*i] = (int) Cities[i]->distance;
                Distances[2*i+1] = i;       //La clé
            }
            int*    Result = new int[NbCities*2];
            Radix_sort(Distances, NbCities, 2, Result);
            printf("Distance par rapport à %s :\n", CityNames[Pivot]);
            for(i=1; i<NbResults+1; i++)        printf("%s : %d km\n", CityNames[Result[2*i+1]], Result[2*i]);
            Radix_release();
            ReleaseArray(Distances);
            ReleaseArray(Result);
            //
            Release(DB);
            for(i=0; i<NbCities; i++)       Release(Cities[i]);
            ReleaseArray(Cities);
            for(i=0; i<NbCities; i++)       string_release(&CityNames[i]);
            ReleaseArray(CityNames);
            //
            ReleaseArray(Params);
            //
            return EXIT_SUCCESS;
        }

};

FFW_MAINENTRY(CityRanks);

Le temp moyen d'exécution sur un Sempron 2200+, avec 768Mo de RAM, est de l'ordre de 120ms, sachant que plus de 99% (si, si) de ce temps est consacré à l'accès à la DB. A noter que je voulais avoir un exécutable le plus petit possible (on pourra certainement faire mieux que moi, mais je suis déjà très content !). Avec la commande ci-dessous, ce programme prend 7860 octets :

strip --strip-unneeded -R .comment -R .gnu.version cityquery

Pour exécuter la requête depuis un navigateur, qui est le but de cette entrée, le script PHP suivant se chargera du travail (qui n'est pas bien lourd) :

<html>
<head>
    <title>Requête de distance géographiques</title>
    <meta http-equiv='Content-Type' value='test/html; charset=utf-8'/>
</head>
<body>
<?
    $Pivot = isset($_GET["pivot"]) ? $_GET["pivot"] : "";
    $NbResults = isset($_GET["nbResults"]) ? $_GET["nbResults"] : 30;
    if (!strlen($Pivot) || !$NbResults)     die("");
    if (!is_numeric($Pivot)  || !is_numeric($NbResults))        die("");
    if ($Pivot > 38052 || $Pivot <= 0 || $NbResults > 100 || $NbResults <=0)        die("");
    $Output = shell_exec("/usr/local/bin/cityquery $Pivot $NbResults");
    echo str_replace("\n", "<br/>\n", $Output);
?>
</body>
</html>

On pourra consulter le résultat de tout ceci sur les images ci-dessous, en attendant que je donne accès à ce script directement depuis ce site (ça ne serait peut-être pas très prudent) :

Accueil1 2