Add an sql exportation

[jefleponot]

For many reasons, it could be useful to save navigation tests in a database system.

So I add qt Libraries (QtSql), sql.cpp and sql.js in my phantomjs version.
So I can save data to mysql or postgre or ...

If somebody want this code, i will commit it.

Have a nice day

JEF

example code :

var mysql = require('sql');
var connection = mysql.createConnection({
    host    : 'my_host',
    user    : 'jef',
    password: 'leponot',
    database: 'tests'
});

connection.connect(function(err){
   if (err) {
    console.log('error connecting: ' + err.stack);
    return;
   }
   console.log('connection ok');
});

connection.query("SELECT id_scenario FROM tb_scenario WHERE name=?","scenario_test_authentification",function(data){
      connection.query("INSERT INTO replay ('id_replay','id_scenario','date','state') VALUES('',?,Now(),?);",[data.id_scenario,"OK"],function(insert){
    connection.end();
   });
});

phantom.exit();

[ariya]

This is outside the scope of PhantomJS. If there is a need to save some data to any database, that should be done through the usual IPC or file I/O.

[jefleponot]

Hi,

Thank you Ariya for your answer.
you are absolutely right a browser never needs to connect to a database.

I just need it to save states for supervision web features and it 's so easy to add in your source code with Qt :-)
Perhaps somebody else could need this code....

A feature required by browsers is "proxy exceptions" (or pac file) could you please add it (pull request : #12131)

Best regards
JEF

[mugbya]

@jefleponot

Yes,I need to persist data to the database. Can I get your code?

[jefleponot]

ok

add in

---

phantomjs.pro

---

QT += sql network webkit
HEADERS += sql.h \
SOURCES += sql.cpp
OTHER_FILES += modules/sql.js \

linux* {
#include library you need : I use MYSQL
INCLUDEPATH += /usr/lib64/mysql/
LIBS += -L/usr/lib64/mysql/ -lmysqlclient_r
}

---

module/sql.js

---

/_jslint sloppy: true, nomen: true */
/_global exports:true,phantom:true */

/*
This file is part of the PhantomJS project from Ofi Labs.

Copyright (C) 2011 Ariya Hidayat ariya.hidayat@gmail.com
Copyright (C) 2011 Ivan De Marino ivan.de.marino@gmail.com
Copyright (C) 2011 James Roe roejames12@hotmail.com
Copyright (C) 2011 execjosh, http://execjosh.blogspot.com
Copyright (C) 2011 Klarälvdalens Datakonsult AB, a KDAB Group company, info@kdab.com
Author: Milian Wolff milian.wolff@kdab.com

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright
  notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in the
  documentation and/or other materials provided with the distribution.
* Neither the name of the <organization> nor the
  names of its contributors may be used to endorse or promote products
  derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

function defineSqlSignalHandler(sql, handlers, handlerName, signalName) {
sql.defineSetter(handlerName, function (f) {
// Disconnect previous handler (if any)
if (!!handlers[handlerName] && typeof handlers[handlerName].callback === "function") {
try {
this[signalName].disconnect(handlers[handlerName].callback);
} catch (e) {}
}

    // Delete the previous handler
    delete handlers[handlerName];

    // Connect the new handler iff it's a function
    if (typeof f === "function") {
        // Store the new handler for reference
        handlers[handlerName] = {
            callback: f
        }
        this[signalName].connect(f);
    }
});

sql.__defineGetter__(handlerName, function() {
    return !!handlers[handlerName] && typeof handlers[handlerName].callback === "function" ?
        handlers[handlerName].callback :
        undefined;
});

}

function isArray(object)
{
if (object.constructor === Array) return true;
else return false;
}

function decorateNewSql(opts, sql) {
var handlers = {};
opts['driver'] = 'QMYSQL';

defineSqlSignalHandler(sql, handlers, "_onRowReceived", "rowReceived");
defineSqlSignalHandler(sql, handlers, "_onConnected", "connected")

sql._connect(opts);

sql.connect =function(arg1){
            thisSql = this;

            thisSql._onConnected = function(err) {
               thisSql._onConnected = null; //< Disconnect callback (should fire only once)
                if (err && typeof err.stack !== "undefined"){
                    arg1.call(thisSql, err);        //< Invoke the actual callback
                } else {
                    arg1.call(thisSql);
                }

            };
            return thisSql.open();
};

sql.query = function(req,arg1,arg2){
    thisSql = this;
    nbArg = arguments.length;
    arg2 = arguments[nbArg-1];
    param = {};

    if (nbArg >= 2 && typeof arg2 === 'function') {
        thisSql._onRowReceived = function(data) {
            thisSql._onRowReceived = null; //< Disconnect callback (should fire only once)
            arg2.call(thisSql, data);        //< Invoke the actual callback
        };
    }
    if (arg1 && isArray(arg1)) {
            for (i=0,l=arg1.length;i<l;i++)
            param[i]=arg1[i];
    } else if (arg1 && typeof arg1 === 'object'){
            param = arg1;
    } else if (arg1 && typeof arg1 !== 'function'){
        param = {"0":arg1};
    }

    this._query(req,param);
    return;
};

sql.end = function(){
            sql.close();
};

return sql;

}

exports.createConnection = function (opts) {
return decorateNewSql(opts, phantom.createSql());
};

---

sql.h

---

/*
This file is part of the PhantomJS project from Ofi Labs.

Copyleft (C) 2014 jef_le_ponot

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright
  notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in the
  documentation and/or other materials provided with the distribution.
* Neither the name of the <organization> nor the
  names of its contributors may be used to endorse or promote products
  derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

#ifndef SQL_H
#define SQL_H

#include

class Sql : public QObject
{
Q_OBJECT
Q_PROPERTY(QString coucou READ coucou)

public:
Sql(QObject *parent = 0);
QString coucou() const;

public slots:
//_createConnection
bool _connect(const QVariantMap &config);
bool open();
QString _query(const QString &request, const QVariantMap &bindValues);
bool close();

signals:
void rowReceived(const QVariant &data);
void connected(const QVariant &err);

private:
QString m_value;
QSqlDatabase *m_db;
};

#endif // SQL_H

---

sql.cpp

---

/*
This file is part of the PhantomJS project from Ofi Labs.

Copyright (C) 2011 Ivan De Marino ivan.de.marino@gmail.com

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright
  notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
  notice, this list of conditions and the following disclaimer in the
  documentation and/or other materials provided with the distribution.
* Neither the name of the <organization> nor the
  names of its contributors may be used to endorse or promote products
  derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/

#include "sql.h"
#include "phantom.h"
#include "config.h"
#include "cookiejar.h"
#include "terminal.h"

// FileSystem
// public:
Sql::Sql(QObject *parent)
: QObject(parent)
, m_db(0)
{
m_value = "toto";
}

// public slots:
QString Sql::coucou() const
{
return m_value;
}

bool Sql::_connect(const QVariantMap &config)
{
if (m_db) {
m_db->close();
}

if (config.contains("driver")) qDebug() << config["driver"].toString();
if (config.contains("host")) qDebug() << config["host"].toString();
if (config.contains("database")) qDebug() << config["database"].toString();
if (config.contains("user")) qDebug() << config["user"].toString();
if (config.contains("password")) qDebug() << config["password"].toString();
if (config.contains("flags"))

qDebug() << qApp->libraryPaths();

//QSqlDatabase db = QSqlDatabase::addDatabase (dataBase);


m_db = new QSqlDatabase(QSqlDatabase::addDatabase (config.value("driver").toString()));
/*m_db->addDatabase(dataBase);*/
if (config.contains("host")) m_db->setHostName(config.value("host").toString());
if (config.contains("database")) m_db->setDatabaseName(config.value("database").toString());
if (config.contains("user")) m_db->setUserName(config.value("user").toString());
if (config.contains("password")) m_db->setPassword(config.value("password").toString());
if (config.contains("flags")) {
    QStringList list = config.value("flags").toString().split(",", QString::SkipEmptyParts);
    QStringList::Iterator it = list.begin();
    QString flags = "CLIENT_"+*it;++it;
    for ( ; it != list.end(); ++it ) {
        flags += ";CLIENT_"+ *it ;
    }
    qDebug() << flags;
    m_db->setConnectOptions(flags);
}

return true;

}

bool Sql::open()
{
QVariantMap data;

if (!m_db->open()){
    data["stack"]=m_db->lastError().text();
    emit connected(data);
    m_db->setConnectOptions();
    return false;
}
emit connected(data);
return true;

}

QString Sql::_query(const QString &request,const QVariantMap &bindValues)
{
QList rows;
QList cols;
QVariantMap data;
QVariantMap::ConstIterator ii;

QString str;

if (m_db->open()) {

    QSqlQuery qry;
    qry.prepare( request );

    for( ii = bindValues.constBegin(); ii != bindValues.constEnd(); ++ii ){
        qry.bindValue (ii.key(),ii.value());
    }

    if( !qry.exec() ){
          qDebug() << qry.lastError().text();
    } else {
        if (qry.isSelect()) { // SELECT COMMAND

            qDebug() << "Selected!" ;
            QSqlRecord rec = qry.record();
            int nbcols = rec.count();
            data["nbFields"] = nbcols;

            for( int c=0; c<nbcols; c++ ){
                qDebug() << QString( "Column %1: %2" ).arg( c ).arg( rec.fieldName(c) );
                str.append(QString("%1").arg(c));
                cols << rec.fieldName(c);
            }

            data["nbRows"] = qry.size();

            for( int r=0; qry.next(); r++ ){
                QList <QVariant> row;
                for( int c=0; c<nbcols; c++ ){

                    qDebug() << QString( "Row %1, %2: %3" ).arg( r ).arg( rec.fieldName(c) ).arg( qry.value(c).toString() );
                    row << QVariant(qry.value(c).toString());
                }
                rows << row;
            }
            data["rows"]=QVariant(rows);
            data["cols"]=cols;
        }else{
            data["affectedRows"] = qry.numRowsAffected();
            data["insertId"] = qry.lastInsertId();
        }
        data["sql"] = qry.lastQuery();
    }
}
emit rowReceived(data);
return "no-no";

}

bool Sql::close()
{
if (m_db) {
m_db->close();
}
return true;
}

---

add in phantomjs.cpp

---

#include "sql.h"
...
// private:
Phantom::Phantom(QObject *parent)
: QObject(parent)
, m_terminated(false)
, m_returnValue(0)
, m_filesystem(0)
, m_system(0)
, m_childprocess(0)
, m_sql(0)

...

QObject *Phantom::createSql()
{
qDebug() << "Phantom - createSql";
if (!m_sql) {
m_sql = new Sql(this);
}

return m_sql;

}

---

phamtonjs.h

---

#include "sql.h"
...
public slots:
QObject *createSql();
...
private:
Sql *m_sql;

---

Example :

---

connection.connect(function(err){
if (err) {
console.log('error connecting: ' + err.stack);
return;
}
//console.log('connection ok');
display('DATABASE','mysql://'+conf.user+':'+conf.password+'@'+conf.host+'/'+conf.database);
});

connection.query(SQL_REQUESTS['INSERT_REJEU']['SQL'],
[struct['id_scenario'],struct['id_application'],scenario.startTime.toMysqlFormat(),
scenario.totalTime/1000,0,1,scenario.state.code,scenario.state.message ],function(rejeu){
test(rejeu['insertId']!=null,"INSERT REJEU ("+(rejeu["insertId"]?rejeu["insertId"]:-1)+")"); });
});

connection.end();

[mugbya]

sorry .

sql.h is to be included QSqlDatabase ? You did not write it

and I spent a very long time, but have not been able to compile complete.

[jefleponot]

Hello,

I will integrate my code in the new version of phantomjs 2.0.0 and commit with git

Best Regards

Message du 16/12/14 à 17h07
De : "亦恕"
A : "ariya/phantomjs"

Copie à : "jef"
Objet : Re: [phantomjs] Add an sql exportation (#12302)

sorry .

sql.h is to be included QSqlDatabase ? You did not write it

and I spent a very long time, but have not been able to compile complete.

---

Reply to this email directly or view it on GitHub:
#12302 (comment)

---

Mode, hifi, maison,… J'achète malin. Je compare les prix avec Voila.fr http://shopping.voila.fr/

[mugbya]

thanks

I'm looking forward to

Best Regards too ^_^

[jefleponot]

https://github.com/jefleponot/phantomjs/tree/sql

you have to connect your own data base

[mugbya]

oh . I have some errors.

/usr/bin/ld: cannot find -lqcncodecs
/usr/bin/ld: cannot find -lqjpcodecs
/usr/bin/ld: cannot find -lqkrcodecs
/usr/bin/ld: cannot find -lqtwcodecs
/usr/bin/ld: cannot find -lqsqlmysql
collect2: error：ld return 1
Makefile.phantomjs:284: recipe for target '../bin/phantomjs' failed
make[1]: *** [../bin/phantomjs] Error 1
make[1]: Leaving directory '/development/git/github/other/phantomjs_jef/src'
Makefile:38: recipe for target 'sub-src-phantomjs-pro-make_first-ordered' failed
make: *** [sub-src-phantomjs-pro-make_first-ordered] Error 2

I do not have this directory of /usr/bin/ld

Other error is fixed, but this I do not know