#include <libpq-fe.h>
#include <iostream>
#include <cstring>
#include <iomanip>
#include <string>
using namespace std;
void AfficheInfos(PGconn *);
unsigned int separationTiret(unsigned int, unsigned int, unsigned int, PGresult *);
void AfficheErreur(ExecStatusType, char *);
unsigned int calculNombreCaractere(unsigned int, unsigned int, PGresult *);
std::string supprimmeChaine(char *, unsigned int);
void affichage_entete(PGresult *, unsigned int, unsigned int);
void affichage_donnees(PGresult *, unsigned int, unsigned int, unsigned int);
void affichage_tiret(unsigned int);
int main()
{
const char *infos_de_connexion = "host=postgresql.bts-malraux72.net port=5432 dbname=v.tazir user=v.tazir";
PGconn *retour_connexion;
retour_connexion = PQconnectdb(infos_de_connexion);
PGresult *requete = PQexec(retour_connexion, "SET SCHEMA 'si6'; SELECT \"Animal\".id,\"Animal\".nom,sexe,date_naissance AS date_de_naissance,commentaires,\"Race\".nom as race, description FROM \"Animal\" INNER JOIN \"Race\" ON \"Animal\".race_id = \"Race\".id WHERE sexe = 'Femelle' AND \"Race\".nom = 'Singapura';");
unsigned int nbresult = PQntuples(requete);
unsigned int nbcolonnes = PQnfields(requete);
ExecStatusType reussite = PQresultStatus(requete);
char *status_requete = PQresStatus(reussite);
if(PQstatus(retour_connexion) != CONNECTION_OK)
{
std::cerr << "La connexion au serveur de base de données '" << PQhost(retour_connexion) << "' à échoué." << std::endl;
}
else
{
AfficheInfos(retour_connexion);
//Question 4 et 6
if(reussite == PGRES_TUPLES_OK)
{
unsigned int caractere = 0, nbrSeparation = 0;
unsigned int nbrCaractere = calculNombreCaractere(caractere, nbcolonnes, requete);
unsigned int nbrSeparationTiret = separationTiret(nbcolonnes, nbrSeparation, nbrCaractere, requete);
affichage_tiret(nbrSeparationTiret);
std::cout << endl << "| ";
affichage_entete(requete, nbrCaractere, nbcolonnes);
std::cout << std::endl;
affichage_tiret(nbrSeparationTiret);
std::cout << std::endl;
affichage_donnees(requete, nbrCaractere, nbcolonnes, nbresult);
affichage_tiret(nbrSeparationTiret);
std::cout << "\nL'exécution de la requête SQL a retourné " << nbresult << " enregistrements." << std::endl;
}
else
{
AfficheErreur(reussite, status_requete);
}
}
return 0;
}
void AfficheInfos(PGconn *connexion)
{
char *host = PQhost(connexion);
char *user = PQuser(connexion);
char *password = PQpass(connexion);
char *database = PQdb(connexion);
char *port = PQport(connexion);
const char *encodage = PQparameterStatus(connexion, "server_encoding");
int protocole = PQprotocolVersion(connexion);
int version = PQserverVersion(connexion);
int SSL = PQsslInUse(connexion);
int versionbib = PQlibVersion();
std::cout << "La connexion au serveur de base de données '" << host << "' à été établie avec les paramètres suivants : " << std::endl;
std::cout << " * utilisateur : " << user << std::endl;
std::cout << " * mot de passe : ";
for(unsigned int i = 1 ; i <= std::strlen(password) ; i++)
{
std::cout << "*";
}
std::cout << std::endl << " * base de donnees : " << database << std::endl << " * port TCP : " << port << std::endl << " * chiffremment SSL : ";
if(SSL == 1)
{
std::cout << "true";
}
else
{
std::cout << "false";
}
std::cout << std::endl << " * encodage : " << encodage << std::endl << " * version du protocole : " << protocole << std::endl << " * version du serveur : " << version << std::endl << " * version de la bibliothèque 'libpq' du client : " << versionbib << std::endl ;
}
unsigned int separationTiret(unsigned int nbrColonnes, unsigned int nbSeparation, unsigned int nbCaractere, PGresult *requetes)
{
for(unsigned int p = 0; p < nbrColonnes; p++)
{
unsigned int champVide =nbCaractere - std::strlen(PQfname(requetes, p));
nbSeparation = nbSeparation + champVide + 3 + std::strlen(PQfname(requetes, p));
}
return nbSeparation;
}
unsigned int calculNombreCaractere(unsigned int nbCaractere, unsigned int nbrColonnes, PGresult *requetes)
{
for(unsigned int champ = 0; champ < nbrColonnes; champ++)
{
unsigned int longueurChamp = std::strlen(PQfname(requetes, champ));
if(longueurChamp > nbCaractere)
{
nbCaractere = longueurChamp;
}
}
return nbCaractere;
}
std::string supprimmeChaine(char *chaine, unsigned int maxCaractere)
{
std::string output;
if(std::strlen(chaine) > maxCaractere)
{
//ou alors
//string conversion(chaine);
//int calcul = (conversion.length() - maxCaractere);
//conversion.erase(conversion.length() - calcul, calcul);
for(unsigned int t = 0; t < maxCaractere-3; t++)
{
output.push_back(chaine[t]);
}
output.append("...");
}
else{
output.append(chaine);
}
return output;
}
void affichage_entete(PGresult *requetes, unsigned int nbCaractere, unsigned int nbColonnes)
{
for(unsigned int p = 0; p < nbColonnes; p++) //Affichage de l'en-tête
{
std::cout << std::left << std::setw(nbCaractere) << PQfname(requetes, p) << " | ";
}
}
void affichage_donnees(PGresult *requetes, unsigned int nbCaractere, unsigned int nbColonnes, unsigned int nbResult)
{
for(unsigned int l = 0; l < nbResult; l++)
{
std::cout << "| ";
for(unsigned int c = 0; c < nbColonnes; c++)
{
std::cout << std::left << std::setw(nbCaractere) << supprimmeChaine(PQgetvalue(requetes, l, c), nbCaractere);
std::cout << " | ";
}
std::cout << std::endl;
}
}
void affichage_tiret(unsigned int tiret)
{
for(unsigned int x = 0; x < tiret; x++)
{
std::cout << "-";
}
}
void AfficheErreur(ExecStatusType reussite, char *status_requete)
{ //Question 5
if(reussite == PGRES_FATAL_ERROR)
{
std::cerr << "Erreur Fatale : " << status_requete << std::endl;
}
else if(reussite == PGRES_BAD_RESPONSE)
{
std::cerr << "Erreur : La réponse du serveur n'a pas été comprise : " << status_requete << std::endl;
}
else if(reussite == PGRES_EMPTY_QUERY)
{
std::cerr << "Erreur : Aucune requête : " << status_requete << std::endl;
}
else if(reussite == PGRES_NONFATAL_ERROR)
{
std::cerr << "Erreur non Fatale : " << status_requete << std::endl;
}
else
{
std::cerr << "Début de l'envoi ou de la réception de données : " << status_requete << std::endl;
}
}