From 728efcefa13f09d029d569f3013a9dc3b744fce4 Mon Sep 17 00:00:00 2001
From: Norbert Moutarde <norbert.moutarde@nothing2do.eu>
Date: Mon, 29 Jul 2013 11:14:49 +0200
Subject: [PATCH] I've begined to add connect() (getpass SQL, connect() C++)

---
 alias.sql      | 24 ++++++++++++++----------
 credential.sql |  1 +
 main.cpp       |  9 +++++----
 sqldb.cpp      | 50 +++++++++++++++++++++++++++++++++++---------------
 sqldb.h        |  5 +++--
 tables1.sql    | 14 ++++++++++++--
 6 files changed, 70 insertions(+), 33 deletions(-)
 create mode 100644 credential.sql

diff --git a/alias.sql b/alias.sql
index 9852214..b15d644 100644
--- a/alias.sql
+++ b/alias.sql
@@ -1,19 +1,23 @@
-insert into alias (nom, cmd, argc, help) values ('tables', 'select tablename from pg_tables where tableowner=current_user', 0, 'list table (every table...)');
+insert into alias (nom, cmd, argc, help) values ('tables', 'select tablename from pg_tables where tableowner=current_user;', 0, 'list table (every table...)');
 INSERT INTO alias (nom, cmd, argc, help) VALUES ('ls', 'select id,texte from raw order by date asc;', 0, 'list id,text of every records');
 insert into alias (nom, cmd, argc, help) values ('ls', 'select * from raw where id=$1 order by date asc;', 1, 'print id,date,text of one record');
 insert into alias (nom, cmd, argc, help) values ('ls', 'select * from raw where date between $1 and $2 order by date asc;', 2, 'liste record with date between $1 and $2');
 INSERT INTO alias (nom, cmd, argc, help) VALUES ('search', 'select id,texte from raw where texte ~ $1 order by date asc;', 1, 'search record who contains your arg (only one arg)');
 INSERT INTO alias (nom, cmd, argc, help) VALUES ('rm', 'DELETE FROM raw WHERE id =$1;', 1, 'delete record by ID');
 INSERT INTO alias (nom, cmd, argc, help) VALUES ('rmm', 'DELETE FROM memo WHERE id =$1;', 1, 'delete record by ID');
-insert into alias (nom, cmd, argc, help) values ('var', 'select nom from var', 0, 'print avery var');
-insert into alias (nom, cmd, argc, help) values ('var', 'select value from var where nom=$1', 1, 'print named var content');
-insert into alias (nom, cmd, argc, inf, help) values ('var', 'insert into var (nom, value) values ($1, $2)', 2, '1', 'add new var and it''s content');
+insert into alias (nom, cmd, argc, help) values ('var', 'select nom from var;', 0, 'print avery var');
+insert into alias (nom, cmd, argc, help) values ('var', 'select value from var where nom=$1;', 1, 'print named var content');
+insert into alias (nom, cmd, argc, help) values ('var', 'insert into var (nom, value) values ($1, $2);', 2, 'add new var and it''s content');
+insert into alias (nom, cmd, argc, help) values ('uvar', 'update var set value=$2 where nom=$1;', 2, 'update var''s content');
 insert into alias (nom, cmd, argc, inf, help) values ('memo', 'insert into memo (word, rem) values ($1, $2);', 2, '1', 'insert new memo');
-insert into alias (nom, cmd, argc, help) values ('memo', 'select distinct word from memo', 0, 'list all word with memo attached');
-insert into alias (nom, cmd, argc, help) values ('memo', 'select id,rem from memo where word=$1 order by id', 1, 'print memo content');
-insert into alias (nom, cmd, argc, help) values ('help', 'select distinct nom from alias', 0, 'help itself (add "cmd" to get specific help)');
-insert into alias (nom, cmd, argc, help) values ('help', 'select argc from alias where nom=$1 order by argc', 1, 'print argc of named alias by ID');
-insert into alias (nom, cmd, argc, help) values ('help', 'select help from alias where nom=$1 and argc=$2', 2, 'print help for only one alias (as "nom" and "argc" is a key)');
-insert into alias (nom, cmd, argc, help) values ('print', 'select cmd,inf from alias where nom=$1 and argc=$2', 2, 'print SQL command of an alias (...)');
+insert into alias (nom, cmd, argc, help) values ('memo', 'select distinct word from memo;', 0, 'list all word with memo attached');
+insert into alias (nom, cmd, argc, help) values ('memo', 'select id,rem from memo where word=$1 order by id;', 1, 'print memo content');
+insert into alias (nom, cmd, argc, help) values ('help', 'select distinct nom from alias;', 0, 'help itself (add "cmd" to get specific help)');
+insert into alias (nom, cmd, argc, help) values ('help', 'select argc from alias where nom=$1 order by argc;', 1, 'print argc of named alias by ID');
+insert into alias (nom, cmd, argc, help) values ('help', 'select help from alias where nom=$1 and argc=$2;', 2, 'print help for only one alias (as "nom" and "argc" is a key)');
+insert into alias (nom, cmd, argc, help) values ('print', 'select cmd,inf from alias where nom=$1 and argc=$2;', 2, 'print SQL command of an alias (...)');
 insert into alias (nom, cmd, argc, inf, help) values ('default', 'INSERT INTO raw (date, texte) VALUES ($1::timestamp, $2::text);', 2 , '1', 'default command');
 insert into alias (nom, cmd, argc, inf, help) values ('aliasadd', 'insert into alias (nom, argc, cmd) values ( $1, $2, $3 );', 3, '1', 'add alias (with form : aliasadd "name" "argc" "SQL")');
+insert into alias (nom, cmd, argc, help) values ('getpass', 'select username,password from credential where service=$1;', 1, 'command to retrieve username and password with a service name (perhaps more than one)');
+insert into alias (nom, cmd, argc, help) values ('getpass', 'select distinct service from credential;', 0, 'get a list of service you have credential for');
+insert into alias (nom, cmd, argc, help) values ('getpass', 'select password from credential where service=$1 and username=$2;', 2, 'retrieve one saved password (as service and user has unique constraint)');
diff --git a/credential.sql b/credential.sql
new file mode 100644
index 0000000..a0fbb53
--- /dev/null
+++ b/credential.sql
@@ -0,0 +1 @@
+insert into credential (service, username, password) values ('pop', 'me', 'pass');
diff --git a/main.cpp b/main.cpp
index 23a1b99..ae75c2b 100644
--- a/main.cpp
+++ b/main.cpp
@@ -14,14 +14,15 @@ string date(string a)//transform any date format (in help) to "YYYY-MM-DD HH:MM:
 }
 int main(int argc, char *argv[])
 {
-    cout << "Début du programme '" << argv[0] << "'. Nombre de paramètres reçus : " << argc  << endl;
-    clog<<"===start===\n";
+    cout << "Début du programme '" << argv[0] << "'."  << endl;
+    clog<<"======================start=================================\n";
     sqlpg diary;
     string tmp, rep, shell="? ";
     int hm;
-    diary.getvar();
+
 do{
     clog<<"======================again=================================\n";
+    diary.getvar();// you can update var and it affect the actual session (I know, it's a useless thing)
     hm=diary.hmRecord();
     cout<<hm<<shell;
     getline(cin,rep,'\n');
@@ -37,7 +38,7 @@ do{
     else if (words[0]=="!") {
         string a=rep.substr(2);
         diary.exec(a);
-        diary.print();
+        //diary.print();
     }
     else if (diary.alias(words));
     else {
diff --git a/sqldb.cpp b/sqldb.cpp
index 6aeb730..6912e0c 100644
--- a/sqldb.cpp
+++ b/sqldb.cpp
@@ -13,12 +13,12 @@ using namespace std;
 sqlpg::sqlpg() {
 
     //connect = "host=postgresql1.alwaysdata.com port=5432 dbname=nothing2do.eu_diary user=nothing2do.eu password=x connect_timeout=10";
-    connect = "dbname=bob user=bob password=pass connect_timeout=10";
+    strconnect = "dbname=bob user=bob password=pass connect_timeout=10";
     //conn =  PQconnectStart(var.c_str());
-    conn =  PQconnectdb(connect.c_str());
+    conn =  PQconnectdb(strconnect.c_str());
     clog<<"prepare SQL statement\n";
     string cmd="select cmd,argc,inf from alias where ((nom = $1) and (argc = $2)) OR ((nom = $1) and (inf<>0) and (argc < $2));";
-    clog<<"SQL statement="<<cmd<<endl;
+    clog<<"SQL selectalias="<<cmd<<endl;
     res=PQprepare(conn, "selectalias", cmd.c_str(), 2, NULL);
     testSQL(res);
 }
@@ -158,14 +158,13 @@ int isdate(string a){
 int sqlpg::hmRecord(){
     string command;
     command="select date from raw;";
-    //res=PQexec(conn, command.c_str());
     exec(command);
-    a=PQntuples(res);
-    //clog<<"hmRecord()="<<a<<endl;
+    a=PQntuples(res2);
+    clog<<"hmRecord()->a="<<a<<endl;
     return a;
 }
 void sqlpg::printvar(){
-    cout<<"date="<<date<<", chaîne de connection: " + connect << endl;
+    cout<<"date="<<date<<", chaîne de connection: " + strconnect << endl;
     for(map<string, string>::iterator i=value.begin(); i!=value.end(); ++i){
         cout<<"value["<<i->first<<"]="<<i->second<<endl;
     }
@@ -189,11 +188,11 @@ for (int i=0; i<l; i++){
 }
 void sqlpg::exec(string a){
     clog<<"exec(string["<<a<<"])\n";
-    res=PQexecParams(conn, a.c_str(), 0, NULL, 0, NULL, NULL, 0);
-    if (!((PQresultStatus(res) == PGRES_COMMAND_OK)||(PQresultStatus(res) == PGRES_TUPLES_OK)))
+    res2=PQexecParams(conn, a.c_str(), 0, NULL, 0, NULL, NULL, 0);
+    if (!((PQresultStatus(res2) == PGRES_COMMAND_OK)||(PQresultStatus(res2) == PGRES_TUPLES_OK)))
     {
         cout<<"PQerrorMessage(conn) : "<< PQerrorMessage(conn)<<endl;
-        cout<<"PQresStatus(res)="<<PQresStatus(PQresultStatus(res))<<endl;
+        cout<<"PQresStatus(res2)="<<PQresStatus(PQresultStatus(res2))<<endl;
     };
 }
 void sqlpg::exec(vector<string> a){
@@ -239,7 +238,7 @@ inline int sqlpg::testSQL(PGresult* a){
 bool sqlpg::alias(vector<string> a){
     clog<<"alias start here\n";
     string nom=a[0], as=toString(a.size()-1), i;
-    int argca=2;
+    int argca=2;//it won't work if I use argca in place of 2 :-(
     bool inf=0;
     const char *valuea[2]={nom.c_str(), as.c_str()};
     int lengtha[2]={nom.size(), as.size()};
@@ -295,7 +294,7 @@ bool sqlpg::alias(vector<string> a){
     res=PQexecPrepared(conn, "execalias", argc, value, length, binary, 0);
     clog<<"PQexecPrepared done argc="<<argc<<endl;
     testSQL(res);
-    print();
+    print(); //I prefer to type 'p' (otherwise, I have to add a column in database)
     exec("DEALLOCATE execalias");
     clog<<"alias() end here\n";
     return 1;
@@ -335,16 +334,37 @@ void sqlpg::compulse(){
 };
 void sqlpg::getvar(){
     clog<<"getvar\n";
+    value.clear();
     exec("select nom,value from var;");
-    int l=PQntuples(res);
+    int l=PQntuples(res2);
     for (int i=0; i<l; i++){
         string a, b;
-        a=PQgetvalue(res, i, 0);
-        b=PQgetvalue(res, i, 1);
+        a=PQgetvalue(res2, i, 0);
+        b=PQgetvalue(res2, i, 1);
         value.insert(pair<string, string> (a, b) );
 
     };
     clog<<"value.size="<<value.size()<<endl;
 };
+void sqlpg::connect(string service, string name){
+    clog<<"connect start here\n";
+    string nom="connect", as=toString(2), i;
+    int argca=2;//it won't work if I use argca in place of 2 :-(
+    bool inf=0;
+    const char *valuea[2]={nom.c_str(), as.c_str()};
+    int lengtha[2]={nom.size(), as.size()};
+    int binarya[2]={0, 0};
+    res=PQexecPrepared(conn, "selectalias", argca, valuea, lengtha, binarya, 0);
+    testSQL(res);
+    if (PQntuples(res)!=1){
+        cerr<<"alias end pqntuples(res)="<<PQntuples(res)<<endl;
+        return 0;
+    }
+    string cmd2;
+    cmd2=PQgetvalue(res, 0, 0);
+    clog<<"alias()->commande : "<<cmd2<<endl;
+//res=PQprepare(conn, "pass", )
 
+
+}
 #endif
diff --git a/sqldb.h b/sqldb.h
index 4a78013..e9a230f 100644
--- a/sqldb.h
+++ b/sqldb.h
@@ -27,14 +27,15 @@ public:
     void exec(std::string);
     bool alias(std::vector<std::string>);
     void compulse();//populate table words
+    void connect(std::string, std::string);//connect with user and service (credential in database)
 
 private:
     PGconn *conn;
     PGresult *res, *res2, *res3;
     std::vector < std::vector<std::string> > ret;//useless ?
-    std::string b, date, connect;
+    std::string b, date, strconnect;
     int a;
-    std::map<std::string,std::string>value;
+    std::map<std::string,std::string> value;
 
 };
 
diff --git a/tables1.sql b/tables1.sql
index cc378c5..721369f 100755
--- a/tables1.sql
+++ b/tables1.sql
@@ -3,7 +3,8 @@
 create table IF NOT EXISTS words (
 id serial primary key,
 time integer,
-word text
+word text,
+constraint uc_wordsID UNIQUE (word)
 );
 
 create table IF NOT EXISTS alias(
@@ -12,7 +13,8 @@ cmd text,
 help text,
 argc integer,
 nom text,
-inf integer DEFAULT 0,
+inf boolean DEFAULT '0',
+print boolean default '1',
 CONSTRAINT uc_aliasID UNIQUE (nom, argc)
 );
 
@@ -35,3 +37,11 @@ nom text,
 value text,
 CONSTRAINT uc_varID UNIQUE (nom)
 );
+
+create table if not exists credential (
+id serial primary key,
+service text,
+username text,
+password text,
+constraint uc_credentialID UNIQUE (service, username)
+);
-- 
2.45.1