best way to query the db in plugins

[quenenni]

Hello,

How is the best way to query the DB (mysql in my case).

I can see in several plugins that the easy way to query the db was like this:

 var  db = require('../../src/node/db/DB').db,
...
db.get("emailSubscription:"+context.message.data.padId, function(err, userIds)
(or)
db.get("session:" + sessionID, function (err, session)

But it only works for the table 'store'.

How can I ask to query another table, like users, groups,..?
(I couldn't find a param to do that)

The plugin I'm trying to fix (ep_user_pad) manages it this way:

var mysql = require('mysql');
...
var connection = mysql.createConnection(dbAuthParams);
connection.connect(connectFkt);

exports.expressCreateServer = function (hook_name, args, cb) {
  ...
  var userSql = "Select * from User where User.name = ?";
  var queryUser = connection.query(userSql, [username]);
  ...
}

But that way, etherpad crashes every x min (depending of the directive 'wait_timeout' of mysql)

[ERROR] console - Error: Connection lost: The server closed the connection.
    at Protocol.end (/home/etherpad/node_modules/ep_user_pad/node_modules/mysql/lib/protocol/Protocol.js:73:13)
    at Socket.onend (stream.js:66:10)
    at Socket.EventEmitter.emit (events.js:123:20)
    at TCP.onread (net.js:417:51)

I tried to fix the problem by closing the connection at the end of "exports.expressCreateServer", but then, it doesn't have an active connection on the next call and that creates a problem.

Maybe it's possible to create and destroy the connection on each call, but it's a big file that manage lots of "args.app.get" & "args.app.post" calls.

As, by using the DB lib instead of mysql lib, etherpad doesn't crash because of the wait_timeout directive, I was wondering if it's possible to choose the table on which you want to make the query?

Thanks

[JohnMcLear]

You should not access the db directly or assume there is multiple tables or databases.

You should use the db object provided

----- Reply message -----
From: "quenenni" notifications@github.com
To: "ether/etherpad-lite" etherpad-lite@noreply.github.com
Subject: [etherpad-lite] best way to query the db in plugins (#2240)
Date: Thu, Sep 18, 2014 23:06

Hello,

How is the best way to query the DB (mysql in my case).

I can see in several plugins that the easy way to query the db was like this:

var db = require('../../src/node/db/DB').db,
...
db.get("emailSubscription:"+context.message.data.padId, function(err, userIds)
(or)
db.get("session:" + sessionID, function (err, session)

But it only works for the table 'store'.

How can I ask to query another table, like users, groups,..?
(I couldn't find a param to do that)

The plugin I'm trying to fix (ep_user_pad) manages it this way:

var mysql = require('mysql');
...
var connection = mysql.createConnection(dbAuthParams);
connection.connect(connectFkt);

exports.expressCreateServer = function (hook_name, args, cb) {
...
var userSql = "Select * from User where User.name = ?";
var queryUser = connection.query(userSql, [username]);
...
}

But that way, etherpad crashes every x min (depending of the directive 'wait_timeout' of mysql)

[ERROR] console - Error: Connection lost: The server closed the connection.
at Protocol.end (/home/etherpad/node_modules/ep_user_pad/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:66:10)
at Socket.EventEmitter.emit (events.js:123:20)
at TCP.onread (net.js:417:51)

I tried to fix the problem by closing the connection at the end of "exports.expressCreateServer", but then, it doesn't have an active connection on the next call and that creates a problem.

Maybe it's possible to create and destroy the connection on each call, but it's a big file that manage lots of "args.app.get" & "args.app.post" calls.

As, by using the DB lib instead of mysql lib, etherpad doesn't crash because of the wait_timeout directive, I was wondering if it's possible to choose the table on which you want to make the query?

Thanks

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/2240.

[quenenni]

I agree to that.

But when using "../../src/node/db/DB", it then calls "var db = new ueberDB.database" that load "node_modules/ueberDB/mysql_db.js", and there, you can only query the table 'store', it's hardcoded in the functions get, set, findKeys, ..

So how can I use DB and choose the table I need?

[JohnMcLear]

Don't do that. Use the table provided else your plugin won't be supported
----- Reply message -----
From: "quenenni" notifications@github.com
To: "ether/etherpad-lite" etherpad-lite@noreply.github.com
Cc: "John McLear" John@mclear.co
Subject: [etherpad-lite] best way to query the db in plugins (#2240)
Date: Thu, Sep 18, 2014 23:15

I agree to that.

But when using "../../src/node/db/DB", it then calls "var db = new ueberDB.database" that load "node_modules/ueberDB/mysql_db.js", and there, you can only query the table 'store', it's hardcoded in the functions get, set, findKeys, ..

So how can I use DB and choose the table I need?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/2240#issuecomment-56111147.

[quenenni]

Well.. it's not that easy.
It's not my plugin, I'm only trying to fix it as the author gave it up.

It's a really nice plugin that manages users & groups to have public / private pads.

He creates several tables to manage the users & groups credentials.
Personally, I find it right to use other tables than 'store' to keep that kind of informations.
'store' is about the pads, not the access infos.

I know there is a project by.. hmm.. a french organization I forgot momentarily the name... to create a similar plugin more robust and official (hopefully), but in the meantime, I would like to have this one working as, except this error because of the wait_timeout, it works very nicely.
And that's also why I don't want to remake it completely.

So, is it possible, by using DB & ueberDB to query other tables?

[JohnMcLear]

The store table should be used for everything for portability.

They made a poor design decision.

[quenenni]

Well, after having taking some times to think about this, I must say that I understand and agree completely with your point of view.

I found a fork to that plugin that fixed the error.
Unfortunately, they also use the mysql lib instead of the Db lib.

If the plugin from the french organization is not what I hope it will be, I guess I'll create a new fork to do it with the Db lib (the owner of the fork is not intereted by portability it seems)