1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/lfapi/main.js	Sat Sep 10 23:31:20 2011 +0200
     1.3 @@ -0,0 +1,1808 @@
     1.4 +var api_version = '0.2.0';
     1.5 +
     1.6 +// creates a random string with the given length
     1.7 +function randomString(number_of_chars) {
     1.8 +  var charset, rand, i, ret;
     1.9 +  charset = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    1.10 +  random_string = '';
    1.11 +
    1.12 +  for (var i = 0; i < number_of_chars; i++) {
    1.13 +    random_string += charset[parseInt(Math.random() * charset.length)]
    1.14 +  }
    1.15 +  return random_string;
    1.16 +}
    1.17 +
    1.18 +var fields = require('./fields.js');
    1.19 +
    1.20 +var general_params = require('./general_params.js');
    1.21 +
    1.22 +var config = general_params.config;
    1.23 +exports.config = config;
    1.24 +
    1.25 +var db = require('./db.js');
    1.26 +exports.db = db;
    1.27 +
    1.28 +var selector = db.selector;
    1.29 +
    1.30 +var email = require('mailer');
    1.31 +
    1.32 +
    1.33 +// check if current session has at least given access level, returns error to client if not.
    1.34 +// used by request handlers below
    1.35 +function requireAccessLevel(conn, req, res, access_level, callback) {
    1.36 +  switch (access_level) {
    1.37 +    case 'anonymous':
    1.38 +      if (req.current_access_level == 'anonymous') { callback(); return; };
    1.39 +    case 'pseudonym':
    1.40 +      if (req.current_access_level == 'pseudonym') { callback(); return; };
    1.41 +    case 'full':
    1.42 +      if (req.current_access_level == 'full') { callback(); return; };
    1.43 +    case 'member':
    1.44 +      if (req.current_member_id) { callback(); return; };
    1.45 +    default:
    1.46 +      respond('json', conn, req, res, 'forbidden', { error: 'Access denied' });
    1.47 +  }
    1.48 +};
    1.49 +
    1.50 +// callback function, encoding result and sending it to the client
    1.51 +function respond(mode, conn, req, res, status, object, err) {
    1.52 +  var http_status = 500;
    1.53 +  var command;
    1.54 +
    1.55 +  if (status == 'ok') {
    1.56 +    command = 'COMMIT';
    1.57 +  } else {
    1.58 +    command = 'ROLLBACK';
    1.59 +  };
    1.60 +  
    1.61 +  switch (status) {
    1.62 +    case 'ok':
    1.63 +      http_status = 200;
    1.64 +      break;
    1.65 +    case 'forbidden':
    1.66 +      //http_status = 403;
    1.67 +      break;
    1.68 +    case 'notfound':
    1.69 +      http_status = 404;
    1.70 +      break;
    1.71 +    case 'unprocessable':
    1.72 +      //http_status = 422;
    1.73 +      break;
    1.74 +    case 'conflict':
    1.75 +      //http_status = 409;
    1.76 +      break;
    1.77 +  };
    1.78 +  
    1.79 +  var query;
    1.80 +  if (mode == 'json' && ! err) query = 'SELECT null';
    1.81 +  db.query(conn, req, res, query, function(result, conn) {
    1.82 +    db.query(conn, req, res, command, function (result, conn) {
    1.83 +      
    1.84 +      if (conn && typeof(conn) != 'string') conn.drain();
    1.85 +
    1.86 +      if (mode == 'json') {
    1.87 +        if (! object) object = {};
    1.88 +      } else if (mode == 'html') {
    1.89 +        if (! object) object = 'no content';
    1.90 +        if (err) object = "Error: " + err;
    1.91 +      }
    1.92 +             
    1.93 +      object.status = status;
    1.94 +      object.error = err;
    1.95 +
    1.96 +      if (mode == 'json') {
    1.97 +        var body = JSON.stringify(object);
    1.98 +        var content_type = 'application/json';
    1.99 +        if (req.params && req.params.callback) {
   1.100 +          body = req.params.callback + '(' + body + ')';
   1.101 +          content_type = 'text/javascript';
   1.102 +        }
   1.103 +        res.writeHead(
   1.104 +          http_status, 
   1.105 +          {
   1.106 +            'Content-Type': content_type,
   1.107 +            //'Content-Length': body.length
   1.108 +          }
   1.109 +        );
   1.110 +        res.end(body);
   1.111 +      } else if (mode == 'html') {
   1.112 +        var body = ['<html><head><title>lfapi</title><style>body { font-family: sans-serif; }</style></head><body>']
   1.113 +        body.push(object)
   1.114 +        body.push('</body></html>')
   1.115 +        body = body.join('');
   1.116 +        res.writeHead(
   1.117 +          http_status, 
   1.118 +          {
   1.119 +            'Content-Type': 'text/html',
   1.120 +            'Content-Length': body.length
   1.121 +          }
   1.122 +        );
   1.123 +        res.end(body);
   1.124 +      }
   1.125 +    })
   1.126 +  });
   1.127 +};
   1.128 +
   1.129 +exports.respond = respond;
   1.130 +db.error_handler = respond;
   1.131 +
   1.132 +// add requested related data for requests with include_* parameters
   1.133 +function addRelatedData(conn, req, res, result, includes) {
   1.134 +  if (includes.length > 0) {
   1.135 +    var include = includes.shift();
   1.136 +    var class = include.class;
   1.137 +    var objects = result[include.objects];
   1.138 +
   1.139 +    var query;
   1.140 +
   1.141 +    if (objects) {
   1.142 +      var objects_exists = false;
   1.143 +      query = new selector.Selector();
   1.144 +      var ids_hash = {};
   1.145 +      if (typeof(objects) == 'array') {
   1.146 +        if (objects.length > 0) {
   1.147 +          objects_exists = true;
   1.148 +          objects.forEach( function(object) {
   1.149 +            if (object[class + "_id"]) {
   1.150 +              ids_hash[object[class + "_id"]] = true;
   1.151 +            };
   1.152 +          });
   1.153 +        }
   1.154 +      } else {
   1.155 +        for (var key in objects) {
   1.156 +          objects_exists = true;
   1.157 +          var object = objects[key];
   1.158 +          if (object[class + "_id"]) {
   1.159 +            ids_hash[object[class + "_id"]] = true;
   1.160 +          };
   1.161 +        };
   1.162 +      };
   1.163 +      
   1.164 +      if (objects_exists) {
   1.165 +        var ids = [];
   1.166 +        for (key in ids_hash) {
   1.167 +          ids.push(key)
   1.168 +        }
   1.169 +
   1.170 +        query.from(class);
   1.171 +        query.addWhere([class + '.id IN (??)', ids]);
   1.172 +        fields.addObjectFields(query, class);
   1.173 +      };
   1.174 +    };
   1.175 +
   1.176 +    db.query(conn, req, res, query, function (result2, conn) {
   1.177 +      // add result to main result, regarding correct pluralization
   1.178 +      var tmp = {};
   1.179 +      if (result2) {
   1.180 +        result2.rows.forEach( function(row) {
   1.181 +          tmp[row.id] = row;
   1.182 +        });
   1.183 +      };
   1.184 +             
   1.185 +      if (class == 'policy') {
   1.186 +        result['policies'] = tmp;
   1.187 +      } else {
   1.188 +        result[class + 's'] = tmp;
   1.189 +      }
   1.190 +      addRelatedData(conn, req, res, result, includes);
   1.191 +    });
   1.192 +  } else {
   1.193 +    respond('json', conn, req, res, 'ok', result);
   1.194 +  };
   1.195 +    
   1.196 +};
   1.197 +
   1.198 +function lockMemberById(conn, req, res, member_id, callback) {
   1.199 +  var query = new selector.Selector('member');
   1.200 +  query.addField('NULL');
   1.201 +  query.addWhere(['member.id = ?', member_id]);
   1.202 +  query.forUpdate();
   1.203 +  db.query(conn, req, res, query, callback);
   1.204 +};
   1.205 +
   1.206 +function requireUnitPrivilege(conn, req, res, unit_id, callback) {
   1.207 +  var query = new selector.Selector('privilege');
   1.208 +  query.addField('NULL');
   1.209 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   1.210 +  query.addWhere(['privilege.unit_id = ?', unit_id ]);
   1.211 +  query.addWhere('privilege.voting_right');
   1.212 +  query.forShareOf('privilege');
   1.213 +  db.query(conn, req, res, query, function(result, conn) {
   1.214 +    if (result.rows.length != 1) {
   1.215 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for this unit.');
   1.216 +      return;
   1.217 +    }
   1.218 +    callback();
   1.219 +  });
   1.220 +};    
   1.221 +
   1.222 +function requireAreaPrivilege(conn, req, res, area_id, callback) {
   1.223 +  var query = new selector.Selector('privilege');
   1.224 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   1.225 +  query.addField('NULL');
   1.226 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   1.227 +  query.addWhere(['area.id = ?', area_id ]);
   1.228 +  query.addWhere('privilege.voting_right');
   1.229 +  query.forShareOf('privilege');
   1.230 +  db.query(conn, req, res, query, function(result, conn) {
   1.231 +    if (result.rows.length != 1) {
   1.232 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for areas in this unit.');
   1.233 +      return;
   1.234 +    }
   1.235 +    callback();
   1.236 +  });
   1.237 +};    
   1.238 +
   1.239 +function requireIssuePrivilege(conn, req, res, issue_id, callback) {
   1.240 +  var query = new selector.Selector('privilege');
   1.241 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   1.242 +  query.join('issue', null, 'issue.area_id = area.id');
   1.243 +  query.addField('NULL');
   1.244 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   1.245 +  query.addWhere(['issue.id = ?', issue_id ]);
   1.246 +  query.addWhere('privilege.voting_right');
   1.247 +  query.forShareOf('privilege');
   1.248 +  db.query(conn, req, res, query, function(result, conn) {
   1.249 +    if (result.rows.length != 1) {
   1.250 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for issues in this unit.');
   1.251 +      return;
   1.252 +    }
   1.253 +    callback();
   1.254 +  });
   1.255 +};    
   1.256 +
   1.257 +function requireInitiativePrivilege(conn, req, res, initiative_id, callback) {
   1.258 +  var query = new selector.Selector('privilege');
   1.259 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   1.260 +  query.join('issue', null, 'issue.area_id = area.id');
   1.261 +  query.join('initiative', null, 'initiative.issue_id = issue.id');
   1.262 +  query.addField('NULL');
   1.263 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   1.264 +  query.addWhere(['initiative.id = ?', initiative_id ]);
   1.265 +  query.addWhere('privilege.voting_right');
   1.266 +  query.forShareOf('privilege');
   1.267 +  db.query(conn, req, res, query, function(result, conn) {
   1.268 +    if (result.rows.length != 1) {
   1.269 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for initiatives in this unit.');
   1.270 +      return;
   1.271 +    }
   1.272 +    callback();
   1.273 +  });
   1.274 +};    
   1.275 +
   1.276 +function requireIssueState(conn, req, res, issue_id, required_states, callback) {
   1.277 +  var query = new selector.Selector('issue');
   1.278 +  query.addField('NULL');
   1.279 +  query.addWhere(['issue.id = ?', issue_id]);
   1.280 +  query.addWhere(['issue.state IN (??)', required_states]);
   1.281 +  query.forUpdateOf('issue');
   1.282 +  db.query(conn, req, res, query, function(result, conn) {
   1.283 +    if (result.rows.length != 1) {
   1.284 +      respond('json', conn, req, res, 'forbidden', null, 'Issue is in wrong state.');
   1.285 +      return;
   1.286 +    }
   1.287 +    callback();
   1.288 +  });
   1.289 +};
   1.290 +
   1.291 +function requireIssueStateForInitiative(conn, req, res, initiative_id, required_states, callback) {
   1.292 +  var query = new selector.Selector('issue');
   1.293 +  query.join('initiative', null, 'initiative.issue_id = issue.id');
   1.294 +  query.addField('NULL');
   1.295 +  query.addWhere(['initiative.id = ?', initiative_id]);
   1.296 +  query.addWhere(['issue.state IN (??)', required_states]);
   1.297 +  query.forUpdateOf('issue');
   1.298 +  db.query(conn, req, res, query, function(result, conn) {
   1.299 +    if (result.rows.length != 1) {
   1.300 +      respond('json', conn, req, res, 'forbidden', null, 'Issue is in wrong state.');
   1.301 +      return;
   1.302 +    }
   1.303 +    callback();
   1.304 +  });
   1.305 +}
   1.306 +
   1.307 +function requireContingentLeft(conn, req, res, is_initiative, callback) {
   1.308 +  var query = new selector.Selector('member_contingent_left');
   1.309 +  query.addField('NULL');
   1.310 +  query.addWhere(['member_contingent_left.member_id = ?', req.current_member_id]);
   1.311 +  query.addWhere('member_contingent_left.text_entries_left >= 1');
   1.312 +  if (is_initiative) {
   1.313 +    query.addWhere('member_contingent_left.initiatives_left >= 1');
   1.314 +  }
   1.315 +  db.query(conn, req, res, query, function(result, conn) {
   1.316 +    if (result.rows.length != 1) {
   1.317 +      respond('json', conn, req, res, 'forbidden', null, 'Contingent empty.');
   1.318 +      return;
   1.319 +    }
   1.320 +    callback();
   1.321 +  });
   1.322 +}
   1.323 +
   1.324 +// ==========================================================================
   1.325 +// GETT methods
   1.326 +// ==========================================================================
   1.327 +
   1.328 +
   1.329 +exports.get = {
   1.330 +
   1.331 +  // startpage (html) for users
   1.332 +  // currently used for implementing public alpha test
   1.333 +  '/': function (conn, req, res, params) {
   1.334 +    
   1.335 +    var html = [];
   1.336 +    html.push('<h2>welcome to lfapi public developer alpha test</h2>');
   1.337 +    html.push('<p>This service is provided for testing purposes and is <i><b>dedicated to developers interested in creating applications</b></i> based on LiquidFeedback.</p>');
   1.338 +    html.push('<h2>how to use</h2>');
   1.339 +    html.push('<p>The programming interface is described in the <a href="http://dev.liquidfeedback.org/trac/lf/wiki/API">LiquidFeedback API specification</a>.</p>')
   1.340 +    html.push('<p>The current implementation status of lfapi is published at the <a href="http://dev.liquidfeedback.org/trac/lf/wiki/lfapi">LiquidFeedback API server</a> page in our Wiki.</p>');
   1.341 +    html.push('<p><b><i>Neither the API specification nor the implementation of lfapi is finished yet.</i></b> This public test should enable developers to join the specification process of the programming interface and makes it possible to start creating applications.</p>');
   1.342 +    html.push('<h2>questions and suggestions</h2>');
   1.343 +    html.push('<p>Please use our <a href="http://dev.liquidfeedback.org/cgi-bin/mailman/listinfo/main">public mailing list</a> if you have any questions or suggestions.</p>');
   1.344 +    html.push('<h2>developer registration</h2>');
   1.345 +    html.push('<p>To register as developer and receive an account, please submit the following form. You\'ll receive an email with instructions to complete the registration process by verifying your email address.<br />');
   1.346 +    html.push('<form action="register_test" method="POST">');
   1.347 +    html.push('<label for="name">Your name:</label> <input type="text" id="name" name="name" /> &nbsp; &nbsp; ');
   1.348 +    html.push('<label for="email">Email address:</label> <input type="text" id="email" name="email" /> &nbsp; &nbsp; ');
   1.349 +    html.push('<label for="location">Location:</label> <select name="location" id="location"><option value="earth">Earth</option><option value="moon">Moon</option><option value="mars">Mars</option></select>');
   1.350 +    html.push('<br />');
   1.351 +    html.push('<br />');
   1.352 +    html.push('<div style="border: 2px solid #c00000; background-color: #ffa0a0; padding: 1ex;">');
   1.353 +    html.push('<b>WARNING:</b> All data you entered above and all data you enter later while using the system and all data you are submitting via the programming interface will be stored in the LiquidFeedback database and published. Every access to the system is subject of tracing and logging for development purposes.<br />Please notice, this is a <b>public alpha test dedicated to developers</b>: serious errors can happen, private data unintentionally published or even <a href="http://en.wikipedia.org/wiki/Grey_goo"> grey goo</a> can appear without further warning. Everything is <b>ON YOUR OWN RISK</b>!');
   1.354 +    html.push('<br />');
   1.355 +    html.push('<br />');
   1.356 +    html.push('<input type="checkbox" name="understood" value="understood" /> I understand the previous warning  and I understand that everything is on my own risk.<br />');
   1.357 +    html.push('</div>');
   1.358 +    html.push('<br />');
   1.359 +    html.push('<input type="submit" value="Register account" />');
   1.360 +    respond('html', null, req, res, 'ok', html.join(''));
   1.361 +  },
   1.362 +  
   1.363 +  // temporary method to implement public alpha test
   1.364 +  '/register_test_confirm': function (conn, req, res, params) {
   1.365 +    var secret = params.secret;
   1.366 +
   1.367 +    var query = new selector.Selector('member');
   1.368 +    query.addField('member.id, member.notify_email_unconfirmed');
   1.369 +    query.addWhere(['member.notify_email_secret = ?', secret]);
   1.370 +    db.query(conn, req, res, query, function (result, conn) {
   1.371 +      var member = result.rows[0];
   1.372 +      if (member) {
   1.373 +        var query = new selector.SQLUpdate('member');
   1.374 +        query.addValues({
   1.375 +          notify_email: member.notify_email_unconfirmed,
   1.376 +          notify_email_secret: null,
   1.377 +          notify_email_unconfirmed: null,
   1.378 +          active: true,
   1.379 +          activated: 'now',
   1.380 +          active: true,
   1.381 +          last_activity: 'now',
   1.382 +          locked: false
   1.383 +        });
   1.384 +        query.addWhere(['id = ?', member.id]);
   1.385 +        db.query(conn, req, res, query, function (err, result) {
   1.386 +          respond('html', conn, req, res, 'ok', 'Account activated: ');
   1.387 +        });
   1.388 +      } else {
   1.389 +        respond('html', conn, req, res, 'forbidden', 'Secret not valid or already used.');
   1.390 +      }
   1.391 +    })
   1.392 +  },
   1.393 +  
   1.394 +  '/info': function (conn, req, res, params) {
   1.395 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.396 +      var query = new selector.Selector();
   1.397 +      query.from('"liquid_feedback_version"');
   1.398 +      query.addField('"liquid_feedback_version".*');
   1.399 +      db.query(conn, req, res, query, function (result, conn) {
   1.400 +        var liquid_feedback_version = result.rows[0];
   1.401 +        respond('json', conn, req, res, 'ok', {
   1.402 +          core_version: liquid_feedback_version.string,
   1.403 +          api_version: api_version,
   1.404 +          current_access_level: req.current_member_id ? 'member' : req.current_access_level,
   1.405 +          current_member_id: req.current_member_id,
   1.406 +          settings: config.settings
   1.407 +        });
   1.408 +      });
   1.409 +    });
   1.410 +  },
   1.411 +  
   1.412 +  '/member_count': function (conn, req, res, params) {
   1.413 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.414 +      var query = new selector.Selector();
   1.415 +      query.from('"member_count"');
   1.416 +      query.addField('"member_count".*');
   1.417 +      db.query(conn, req, res, query, function (result, conn) {
   1.418 +        var member_count = result.rows[0];
   1.419 +        respond('json', conn, req, res, 'ok', {
   1.420 +          member_count: member_count.total_count,
   1.421 +          member_count_calculated: member_count.calculated
   1.422 +        });
   1.423 +      });
   1.424 +    });
   1.425 +  },
   1.426 +  
   1.427 +  '/contingent': function (conn, req, res, params) {
   1.428 +    requireAccessLevel(conn, req, res, 'anonymous', function() {          
   1.429 +      var query = new selector.Selector();
   1.430 +      query.from('"contingent"');
   1.431 +      query.addField('"contingent".*');
   1.432 +      db.query(conn, req, res, query, function (result, conn) {
   1.433 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.434 +      });
   1.435 +    });
   1.436 +  },
   1.437 +  
   1.438 +  '/contingent_left': function (conn, req, res, params) {
   1.439 +    requireAccessLevel(conn, req, res, 'member', function() {          
   1.440 +      var query = new selector.Selector();
   1.441 +      query.from('"member_contingent_left"');
   1.442 +      query.addField('"member_contingent_left".text_entries_left');
   1.443 +      query.addField('"member_contingent_left".initiatives_left');
   1.444 +      query.addWhere(['member_id = ?', req.current_member_id]);
   1.445 +      db.query(conn, req, res, query, function (result, conn) {
   1.446 +        respond('json', conn, req, res, 'ok', { result: result.rows[0] });
   1.447 +      });
   1.448 +    });
   1.449 +  },
   1.450 +  
   1.451 +  '/member': function (conn, req, res, params) {
   1.452 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.453 +      var query = new selector.Selector();
   1.454 +      query.from('"member"');
   1.455 +      if (req.current_access_level == 'pseudonym' && !req.current_member_id ) {
   1.456 +        fields.addObjectFields(query, 'member', 'member_pseudonym');
   1.457 +      } else {
   1.458 +        fields.addObjectFields(query, 'member');
   1.459 +      }
   1.460 +      general_params.addMemberOptions(req, query, params);
   1.461 +      query.addOrderBy('"member"."id"');
   1.462 +      general_params.addLimitAndOffset(query, params);
   1.463 +      db.query(conn, req, res, query, function (result, conn) {
   1.464 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.465 +      });
   1.466 +    });
   1.467 +  },
   1.468 +  
   1.469 +  '/member_history': function (conn, req, res, params) {
   1.470 +    requireAccessLevel(conn, req, res, 'full', function() {
   1.471 +      var query = new selector.Selector();
   1.472 +      query.from('"member_history" JOIN "member" ON "member"."id" = "member_history"."member_id"');
   1.473 +      query.addField('"member_history".*');
   1.474 +      general_params.addMemberOptions(req, query, params);
   1.475 +      query.addOrderBy('member_history.id');
   1.476 +      general_params.addLimitAndOffset(query, params);
   1.477 +      db.query(conn, req, res, query, function (member_history_result, conn) {
   1.478 +        var result = { result: member_history_result.rows }
   1.479 +        includes = [];
   1.480 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   1.481 +        addRelatedData(conn, req, res, result, includes);
   1.482 +      });
   1.483 +    });
   1.484 +  },
   1.485 +  
   1.486 +  '/member_image': function (conn, req, res, params) {
   1.487 +    requireAccessLevel(conn, req, res, 'full', function() {
   1.488 +      var query = new selector.Selector();
   1.489 +      query.from('"member_image" JOIN "member" ON "member"."id" = "member_image"."member_id"');
   1.490 +      query.addField('"member_image".*');
   1.491 +      query.addWhere('member_image.scaled');
   1.492 +      general_params.addMemberOptions(req, query, params);
   1.493 +      query.addOrderBy = ['member_image.member_id, member_image.image_type'];
   1.494 +      db.query(conn, req, res, query, function (result, conn) {
   1.495 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.496 +      });
   1.497 +    });
   1.498 +  },
   1.499 +  
   1.500 +  '/contact': function (conn, req, res, params) {
   1.501 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.502 +      var query = new selector.Selector();
   1.503 +      query.from('contact JOIN member ON member.id = contact.member_id');
   1.504 +      query.addField('"contact".*');
   1.505 +      if (req.current_member_id) {
   1.506 +        // public or own for members
   1.507 +        query.addWhere(['"contact"."public" OR "contact"."member_id" = ?', req.current_member_id]);
   1.508 +      } else {
   1.509 +        // public for everybody
   1.510 +        query.addWhere('"contact"."public"');
   1.511 +      }
   1.512 +      general_params.addMemberOptions(req, query, params);
   1.513 +      query.addOrderBy('"contact"."id"');
   1.514 +      general_params.addLimitAndOffset(query, params);
   1.515 +      db.query(conn, req, res, query, function (result, conn) {
   1.516 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.517 +      });
   1.518 +    });
   1.519 +  },
   1.520 +
   1.521 +  '/privilege': function (conn, req, res, params) {
   1.522 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.523 +      var query = new selector.Selector();
   1.524 +      query.from('privilege JOIN member ON member.id = privilege.member_id JOIN unit ON unit.id = privilege.unit_id');
   1.525 +      query.addField('privilege.*');
   1.526 +      general_params.addUnitOptions(req, query, params);
   1.527 +      general_params.addMemberOptions(req, query, params);
   1.528 +      query.addOrderBy('privilege.unit_id, privilege.member_id');
   1.529 +      general_params.addLimitAndOffset(query, params);
   1.530 +      db.query(conn, req, res, query, function (privilege_result, conn) {
   1.531 +        var result = { result: privilege_result.rows }
   1.532 +        includes = [];
   1.533 +        if (params.include_units) includes.push({ class: 'unit', objects: 'result'});
   1.534 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   1.535 +        addRelatedData(conn, req, res, result, includes);
   1.536 +      });
   1.537 +    });
   1.538 +  },
   1.539 +  
   1.540 +  '/policy': function (conn, req, res, params) {
   1.541 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.542 +      var query = new selector.Selector();
   1.543 +      query.from('"policy"');
   1.544 +      query.addField('"policy".*');
   1.545 +      general_params.addPolicyOptions(req, query, params);
   1.546 +      query.addOrderBy('"policy"."index"');
   1.547 +      general_params.addLimitAndOffset(query, params);
   1.548 +      db.query(conn, req, res, query, function (result, conn) {
   1.549 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.550 +      });
   1.551 +    });
   1.552 +  },
   1.553 +  
   1.554 +  '/unit': function (conn, req, res, params) {
   1.555 +    requireAccessLevel(conn, req, res, 'anonymous', function() {          
   1.556 +      var query = new selector.Selector();
   1.557 +      query.from('"unit"');
   1.558 +      fields.addObjectFields(query, 'unit');
   1.559 +      general_params.addUnitOptions(req, query, params);
   1.560 +      query.addOrderBy('unit.id');
   1.561 +      general_params.addLimitAndOffset(query, params);
   1.562 +      db.query(conn, req, res, query, function (result, conn) {
   1.563 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.564 +      });
   1.565 +    });
   1.566 +  },
   1.567 +  
   1.568 +  '/area': function (conn, req, res, params) {
   1.569 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.570 +      var query = new selector.Selector();
   1.571 +      query.from('area JOIN unit ON area.unit_id = unit.id');
   1.572 +      fields.addObjectFields(query, 'area');
   1.573 +      general_params.addAreaOptions(req, query, params);
   1.574 +      query.addOrderBy('area.id');
   1.575 +      general_params.addLimitAndOffset(query, params);
   1.576 +      db.query(conn, req, res, query, function (area_result, conn) {
   1.577 +        var result = { result: area_result.rows }
   1.578 +        includes = [];
   1.579 +        if (params.include_units) includes.push({ class: 'unit', objects: 'result'});
   1.580 +        addRelatedData(conn, req, res, result, includes);
   1.581 +      });
   1.582 +    });
   1.583 +  },
   1.584 +  
   1.585 +  '/allowed_policy': function (conn, req, res, params) {
   1.586 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.587 +    var query = new selector.Selector();
   1.588 +    query.from('allowed_policy');
   1.589 +    query.join('area', null, 'area.id = allowed_policy.area_id');
   1.590 +    query.join('unit', null, 'unit.id = area.unit_id');
   1.591 +    query.addField('allowed_policy.*');
   1.592 +    general_params.addAreaOptions(req, query, params);
   1.593 +    query.addOrderBy('allowed_policy.area_id, allowed_policy.policy_id');
   1.594 +    general_params.addLimitAndOffset(query, params);
   1.595 +    db.query(conn, req, res, query, function (allowed_policy_result, conn) {
   1.596 +      var result = { result: allowed_policy_result.rows }
   1.597 +      includes = [];
   1.598 +      if (params.include_policies) includes.push({ class: 'policy', objects: 'result'});
   1.599 +      if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   1.600 +      if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.601 +      addRelatedData(conn, req, res, result, includes);
   1.602 +    });
   1.603 +  }); },
   1.604 +  
   1.605 +  '/membership': function (conn, req, res, params) {
   1.606 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.607 +      var query = new selector.Selector();
   1.608 +      query.from('membership JOIN member ON membership.member_id = member.id JOIN area ON area.id = membership.area_id JOIN unit ON unit.id = area.unit_id');
   1.609 +      query.addField('membership.*');
   1.610 +      general_params.addAreaOptions(req, query, params);
   1.611 +      general_params.addMemberOptions(req, query, params);
   1.612 +      query.addOrderBy('membership.area_id, membership.member_id');
   1.613 +      general_params.addLimitAndOffset(query, params);
   1.614 +      db.query(conn, req, res, query, function (membership_result, conn) {
   1.615 +        var result = { result: membership_result.rows }
   1.616 +        includes = [];
   1.617 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   1.618 +        if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   1.619 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.620 +        addRelatedData(conn, req, res, result, includes);
   1.621 +      });
   1.622 +    });
   1.623 +  },
   1.624 +  
   1.625 +  '/issue': function (conn, req, res, params) {
   1.626 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.627 +      var query = new selector.Selector()
   1.628 +      query.from('issue JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.629 +      fields.addObjectFields(query, 'issue');
   1.630 +      general_params.addIssueOptions(req, query, params);
   1.631 +      query.addOrderBy('issue.id');
   1.632 +      general_params.addLimitAndOffset(query, params);
   1.633 +      db.query(conn, req, res, query, function (issue_result, conn) {
   1.634 +        var result = { result: issue_result.rows }
   1.635 +        includes = [];
   1.636 +        if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   1.637 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.638 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'result' });
   1.639 +        addRelatedData(conn, req, res, result, includes);
   1.640 +      });
   1.641 +    });
   1.642 +  },
   1.643 +
   1.644 +  '/interest': function (conn, req, res, params) {
   1.645 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.646 +      var query = new selector.Selector();
   1.647 +      query.from('interest JOIN member ON member.id = interest.member_id JOIN issue on interest.issue_id = issue.id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.648 +      query.addField('interest.*');
   1.649 +      general_params.addMemberOptions(req, query, params);
   1.650 +      general_params.addIssueOptions(req, query, params);
   1.651 +      query.addOrderBy('interest.issue_id, interest.member_id');
   1.652 +      general_params.addLimitAndOffset(query, params);
   1.653 +      db.query(conn, req, res, query, function (interest_result, conn) {
   1.654 +        var result = { result: interest_result.rows }
   1.655 +        includes = [];
   1.656 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   1.657 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   1.658 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.659 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.660 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.661 +        addRelatedData(conn, req, res, result, includes);
   1.662 +      });
   1.663 +    });
   1.664 +  },
   1.665 +  
   1.666 +  '/issue_comment': function (conn, req, res, params) {
   1.667 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.668 +      var query = new selector.Selector();
   1.669 +      query.from('issue_comment JOIN member ON member.id = issue_comment.member_id JOIN issue on issue_comment.issue_id = issue.id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.670 +      query.addField('issue_comment.*');
   1.671 +      general_params.addMemberOptions(req, query, params);
   1.672 +      general_params.addIssueOptions(req, query, params);
   1.673 +      query.addOrderBy('issue_comment.issue_id, issue_comment.member_id');
   1.674 +      general_params.addLimitAndOffset(query, params);
   1.675 +      db.query(conn, req, res, query, function (issue_comment_result, conn) {
   1.676 +        var result = { result: issue_comment_result.rows }
   1.677 +        includes = [];
   1.678 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   1.679 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   1.680 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.681 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.682 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.683 +        addRelatedData(conn, req, res, result, includes);
   1.684 +      });
   1.685 +    });
   1.686 +  },
   1.687 +  
   1.688 +  '/initiative': function (conn, req, res, params) {
   1.689 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.690 +      var query = new selector.Selector();
   1.691 +      query.from('initiative JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.692 +      fields.addObjectFields(query, 'initiative');
   1.693 +      general_params.addInitiativeOptions(req, query, params);
   1.694 +      query.addOrderBy('initiative.issue_id, initiative.id');
   1.695 +      general_params.addLimitAndOffset(query, params);
   1.696 +      db.query(conn, req, res, query, function (initiative_result, conn) {
   1.697 +        var result = { result: initiative_result.rows }
   1.698 +        includes = [];
   1.699 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   1.700 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.701 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.702 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.703 +        addRelatedData(conn, req, res, result, includes);
   1.704 +      });
   1.705 +    });
   1.706 +  },
   1.707 +
   1.708 +  '/initiator': function (conn, req, res, params) {
   1.709 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.710 +      var fields = ['initiator.initiative_id', 'initiator.member_id'];
   1.711 +      var query = new selector.Selector();
   1.712 +      query.from('initiator JOIN member ON member.id = initiator.member_id JOIN initiative ON initiative.id = initiator.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.713 +      query.addWhere('initiator.accepted');
   1.714 +      fields.forEach( function(field) {
   1.715 +        query.addField(field, null, ['grouped']);
   1.716 +      });
   1.717 +      general_params.addMemberOptions(req, query, params);
   1.718 +      general_params.addInitiativeOptions(req, query, params);
   1.719 +      query.addOrderBy('initiator.initiative_id, initiator.member_id');
   1.720 +      general_params.addLimitAndOffset(query, params);
   1.721 +      db.query(conn, req, res, query, function (initiator, conn) {
   1.722 +        var result = { result: initiator.rows }
   1.723 +        includes = [];
   1.724 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.725 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.726 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.727 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.728 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.729 +        addRelatedData(conn, req, res, result, includes);
   1.730 +      });
   1.731 +    });
   1.732 +  },
   1.733 +
   1.734 +
   1.735 +  '/supporter': function (conn, req, res, params) {
   1.736 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.737 +      var fields = ['supporter.issue_id', 'supporter.initiative_id', 'supporter.member_id', 'supporter.draft_id'];
   1.738 +      var query = new selector.Selector();
   1.739 +      query.from('supporter')
   1.740 +      query.join('member', null, 'member.id = supporter.member_id JOIN initiative ON initiative.id = supporter.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.741 +      fields.forEach( function(field) {
   1.742 +        query.addField(field, null, ['grouped']);
   1.743 +      });
   1.744 +      general_params.addMemberOptions(req, query, params);
   1.745 +      general_params.addInitiativeOptions(req, query, params);
   1.746 +      query.addOrderBy('supporter.issue_id, supporter.initiative_id, supporter.member_id');
   1.747 +      general_params.addLimitAndOffset(query, params);
   1.748 +      db.query(conn, req, res, query, function (supporter, conn) {
   1.749 +        var result = { result: supporter.rows }
   1.750 +        includes = [];
   1.751 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.752 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.753 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.754 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.755 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.756 +        addRelatedData(conn, req, res, result, includes);
   1.757 +      });
   1.758 +    });
   1.759 +  },
   1.760 +  
   1.761 +  '/battle': function (conn, req, res, params) {
   1.762 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.763 +      var query = new selector.Selector();
   1.764 +      query.from('battle JOIN initiative ON initiative.id = battle.winning_initiative_id OR initiative.id = battle.losing_initiative_id JOIN issue ON issue.id = battle.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.765 +      query.addField('battle.*');
   1.766 +      general_params.addInitiativeOptions(req, query, params);
   1.767 +      query.addOrderBy('battle.issue_id, battle.winning_initiative_id, battle.losing_initiative_id');
   1.768 +      general_params.addLimitAndOffset(query, params);
   1.769 +      db.query(conn, req, res, query, function (result, conn) {
   1.770 +        var result = { result: result.rows }
   1.771 +        includes = [];
   1.772 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.773 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.774 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.775 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.776 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.777 +        addRelatedData(conn, req, res, result, includes);
   1.778 +      });
   1.779 +    });
   1.780 +  },
   1.781 +  
   1.782 +  '/draft': function (conn, req, res, params) {
   1.783 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.784 +      var fields = ['draft.initiative_id', 'draft.id', 'draft.formatting_engine', 'draft.content', 'draft.author_id'];
   1.785 +      var query = new selector.Selector();
   1.786 +      query.from('draft JOIN initiative ON initiative.id = draft.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.787 +      fields.forEach( function(field) {
   1.788 +        query.addField(field, null, ['grouped']);
   1.789 +      });
   1.790 +      if (req.current_access_level != 'anonymous' || req.current_member_id) {
   1.791 +        query.addField('draft.author_id');
   1.792 +      }
   1.793 +      if (params.draft_id) {
   1.794 +        query.addWhere('draft.id = ?', params.draft_id);
   1.795 +      }
   1.796 +      if (params.current_draft) {
   1.797 +        query.join('current_draft', null, 'current_draft.initiative_id = initiative.id AND current_draft.id = draft.id')
   1.798 +      }
   1.799 +      general_params.addInitiativeOptions(req, query, params);
   1.800 +      query.addOrderBy('draft.initiative_id, draft.id');
   1.801 +      general_params.addLimitAndOffset(query, params);
   1.802 +      db.query(conn, req, res, query, function (result, conn) {
   1.803 +        var result = { result: result.rows }
   1.804 +        includes = [];
   1.805 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.806 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.807 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.808 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.809 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.810 +        addRelatedData(conn, req, res, result, includes);
   1.811 +      });
   1.812 +    });
   1.813 +  },
   1.814 +  
   1.815 +  '/suggestion': function (conn, req, res, params) {
   1.816 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.817 +      var query = new selector.Selector();
   1.818 +      query.from('suggestion JOIN initiative ON initiative.id = suggestion.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.819 +      if (req.current_access_level == 'anonymous' && !req.current_member_id ) {
   1.820 +        fields.addObjectFields(query, 'suggestion', 'suggestion_pseudonym');
   1.821 +      } else {
   1.822 +        fields.addObjectFields(query, 'suggestion');
   1.823 +      }
   1.824 +      general_params.addSuggestionOptions(req, query, params);
   1.825 +      query.addOrderBy('suggestion.initiative_id, suggestion.id');
   1.826 +      general_params.addLimitAndOffset(query, params);
   1.827 +      db.query(conn, req, res, query, function (result, conn) {
   1.828 +        var result = { result: result.rows }
   1.829 +        includes = [];
   1.830 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.831 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.832 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.833 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.834 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.835 +        addRelatedData(conn, req, res, result, includes);
   1.836 +      });
   1.837 +    });
   1.838 +  },
   1.839 +    
   1.840 +  '/opinion': function (conn, req, res, params) {
   1.841 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.842 +      var fields = ['opinion.initiative_id', 'opinion.suggestion_id', 'opinion.member_id', 'opinion.degree', 'opinion.fulfilled']
   1.843 +      var query = new selector.Selector();
   1.844 +      query.from('opinion JOIN member ON member.id = opinion.member_id JOIN suggestion ON suggestion.id = opinion.suggestion_id JOIN initiative ON initiative.id = suggestion.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.845 +      fields.forEach( function(field) {
   1.846 +        query.addField(field, null, ['grouped']);
   1.847 +      });
   1.848 +      general_params.addMemberOptions(req, query, params);
   1.849 +      general_params.addSuggestionOptions(req, query, params);
   1.850 +      query.addOrderBy = ['opinion.initiative_id, opinion.suggestion_id, opinion.member_id'];
   1.851 +      general_params.addLimitAndOffset(query, params);
   1.852 +      db.query(conn, req, res, query, function (result, conn) {
   1.853 +        var result = { result: result.rows }
   1.854 +        includes = [];
   1.855 +        if (params.include_suggestions) includes.push({ class: 'suggestion', objects: 'result'});
   1.856 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'suggestions'});
   1.857 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   1.858 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.859 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.860 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.861 +        addRelatedData(conn, req, res, result, includes);
   1.862 +      });
   1.863 +    });
   1.864 +  },
   1.865 +  
   1.866 +  '/delegation': function (conn, req, res, params) {
   1.867 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.868 +      var fields = ['delegation.id', 'delegation.truster_id', 'delegation.trustee_id', 'delegation.scope', 'delegation.area_id', 'delegation.issue_id', 'delegation.unit_id'];
   1.869 +      var query = new selector.Selector();
   1.870 +      query.from('delegation LEFT JOIN issue on delegation.issue_id = issue.id LEFT JOIN policy ON policy.id = issue.policy_id LEFT JOIN area ON area.id = issue.area_id OR area.id = delegation.area_id LEFT JOIN unit ON area.unit_id = unit.id OR unit.id = delegation.unit_id');
   1.871 +      fields.forEach( function(field) {
   1.872 +        query.addField(field, null, ['grouped']);
   1.873 +      });
   1.874 +      if (params.direction) {
   1.875 +        switch (params.direction) {
   1.876 +          case 'in':
   1.877 +            query.join('member', null, 'member.id = delegation.trustee_id');
   1.878 +            break;
   1.879 +          case 'out':
   1.880 +            query.join('member', null, 'member.id = delegation.truster_id');
   1.881 +            break;
   1.882 +          default:
   1.883 +            respond('json', conn, req, res, 'unprocessable', 'Direction must be "in" or "out" if set.');
   1.884 +        }
   1.885 +      } else {
   1.886 +        query.join('member', null, 'member.id = delegation.truster_id OR member.id = delegation.trustee_id');
   1.887 +      }
   1.888 +      general_params.addMemberOptions(req, query, params);
   1.889 +      general_params.addIssueOptions(req, query, params);
   1.890 +      if (params.scope) {
   1.891 +        query.addWhere(['delegation.scope IN (??)', params.scope.split(',')]);
   1.892 +      };
   1.893 +      query.addOrderBy = ['delegation.id'];
   1.894 +      general_params.addLimitAndOffset(query, params);
   1.895 +      db.query(conn, req, res, query, function (result, conn) {
   1.896 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.897 +      });
   1.898 +    });
   1.899 +  },
   1.900 +
   1.901 +  '/vote': function (conn, req, res, params) {
   1.902 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   1.903 +      var query = new selector.Selector();
   1.904 +      query.from('vote JOIN member ON member.id = vote.member_id JOIN initiative ON initiative.id = vote.initiative_id JOIN issue ON issue.id = initiative.issue_id JOIN policy ON policy.id = issue.policy_id JOIN area ON area.id = issue.area_id JOIN unit ON area.unit_id = unit.id');
   1.905 +      query.addField('vote.*');
   1.906 +      query.addWhere('issue.closed_at NOTNULL');
   1.907 +      general_params.addMemberOptions(req, query, params);
   1.908 +      general_params.addInitiativeOptions(req, query, params);
   1.909 +      general_params.addLimitAndOffset(query, params);
   1.910 +      db.query(conn, req, res, query, function (result, conn) {
   1.911 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   1.912 +      });
   1.913 +    });
   1.914 +  },
   1.915 +  
   1.916 +  '/event': function (conn, req, res, params) { requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.917 +    var fields = ['event.id', 'event.occurrence', 'event.event', 'event.member_id', 'event.issue_id', 'event.state', 'event.initiative_id', 'event.draft_id', 'event.suggestion_id'];
   1.918 +    var query = new selector.Selector();
   1.919 +    query.from('event LEFT JOIN member ON member.id = event.member_id LEFT JOIN initiative ON initiative.id = event.initiative_id LEFT JOIN issue ON issue.id = event.issue_id LEFT JOIN policy ON policy.id = issue.policy_id LEFT JOIN area ON area.id = issue.area_id LEFT JOIN unit ON area.unit_id = unit.id');
   1.920 +    fields.forEach( function(field) {
   1.921 +      query.addField(field, null, ['grouped']);
   1.922 +    });
   1.923 +    general_params.addMemberOptions(req, query, params);
   1.924 +    general_params.addInitiativeOptions(req, query, params);
   1.925 +    query.addOrderBy('event.id');
   1.926 +    general_params.addLimitAndOffset(query, params);
   1.927 +    db.query(conn, req, res, query, function (events, conn) {
   1.928 +      var result = { result: events.rows }
   1.929 +      includes = [];
   1.930 +      if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   1.931 +      if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   1.932 +      if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   1.933 +      if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   1.934 +      if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   1.935 +      addRelatedData(conn, req, res, result, includes);
   1.936 +    });
   1.937 +  }); },
   1.938 +  
   1.939 +  // TODO add interfaces for data structure:
   1.940 +  // event requireAccessLevel(conn, req, res, 'member');
   1.941 +  // ignored_member requireAccessLevel(conn, req, res, 'member');
   1.942 +  // ignored_initiative requireAccessLevel(conn, req, res, 'member');
   1.943 +  // setting requireAccessLevel(conn, req, res, 'member');
   1.944 +
   1.945 +};
   1.946 +
   1.947 +// ==========================================================================
   1.948 +// POST methods
   1.949 +// ==========================================================================
   1.950 +
   1.951 +
   1.952 +
   1.953 +exports.post = {
   1.954 +  
   1.955 +  '/echo_test': function (conn, req, res, params) { requireAccessLevel(conn, req, res, 'anonymous', function() {
   1.956 +    respond('json', conn, req, res, 'ok', { result: params });
   1.957 +  }); },
   1.958 +  
   1.959 +  '/register_test': function (conn, req, res, params) {
   1.960 +    var understood = params.understood;
   1.961 +    var member_login = randomString(16);
   1.962 +    var member_name = params.name;
   1.963 +    var member_password = randomString(16);
   1.964 +    var member_notify_email = params.email;
   1.965 +    var member_notify_email_secret = randomString(24);
   1.966 +    var api_key_member = randomString(24);
   1.967 +    var api_key_full = randomString(24);
   1.968 +    var api_key_pseudonym = randomString(24);
   1.969 +    var api_key_anonymous = randomString(24);
   1.970 +    
   1.971 +    if (understood != 'understood') {
   1.972 +      respond('html', conn, req, res, 'unprocessable', null, 'You didn\'t checked the checkbox! Please hit back in your browser and try again.');
   1.973 +      return;
   1.974 +    }
   1.975 +    
   1.976 +    // add member
   1.977 +    var query = new selector.SQLInsert('member');
   1.978 +    query.addValues({ 
   1.979 +      login: member_login,
   1.980 +      password: member_password, // TODO hashing of password
   1.981 +      notify_email_unconfirmed: member_notify_email,
   1.982 +      notify_email_secret: member_notify_email_secret,
   1.983 +      name: member_name
   1.984 +    });
   1.985 +    query.addReturning('id');
   1.986 +    db.query(conn, req, res, query, function (result, conn) {
   1.987 +      var member_id = result.rows[0].id;
   1.988 +
   1.989 +      // add privilege for root unit
   1.990 +      var query = new selector.SQLInsert('privilege');
   1.991 +      query.addValues({ unit_id: 1, member_id: member_id, voting_right: true });
   1.992 +      db.query(conn, req, res, query, function (result, conn) {
   1.993 +
   1.994 +        var location = params.location;
   1.995 +        var unit_id;
   1.996 +        switch(location) {
   1.997 +          case 'earth':
   1.998 +            unit_id = 3;
   1.999 +            break;
  1.1000 +          case 'moon': 
  1.1001 +            unit_id = 4;
  1.1002 +            break;
  1.1003 +          case 'mars':
  1.1004 +            unit_id = 5;
  1.1005 +            break;
  1.1006 +        }
  1.1007 +        
  1.1008 +        // add privilege for selected planet
  1.1009 +        var query = new selector.SQLInsert('privilege');
  1.1010 +        query.addValues({ unit_id: unit_id, member_id: member_id, voting_right: true });
  1.1011 +        db.query(conn, req, res, query, function (result, conn) {
  1.1012 +
  1.1013 +          // add application key
  1.1014 +          var query = new selector.SQLInsert('member_application');
  1.1015 +          query.addValues({ 
  1.1016 +            member_id: member_id,
  1.1017 +            name: 'member',
  1.1018 +            comment: 'access_level member',
  1.1019 +            access_level: 'member',
  1.1020 +            key: api_key_member
  1.1021 +          });
  1.1022 +          query.addReturning('id');
  1.1023 +          
  1.1024 +          db.query(conn, req, res, query, function (result, conn) {
  1.1025 +
  1.1026 +            // send email to user
  1.1027 +            email.send({
  1.1028 +              host :          config.mail.smtp_host,
  1.1029 +              port:           config.mail.smtp_port,
  1.1030 +              ssl:            config.mail.smtp_ssl,
  1.1031 +              domain:         config.mail.smtp_domain,
  1.1032 +              authentication: config.mail.smtp_authentication,
  1.1033 +              username:       config.mail.smtp_username,
  1.1034 +              password:       config.mail.smtp_password,
  1.1035 +              from:           config.mail.from,
  1.1036 +              subject:        config.mail.subject_prefix + "Your LiquidFeedback API alpha test account needs confirmation",
  1.1037 +              to:             member_notify_email,
  1.1038 +              body: "\
  1.1039 +Hello " + member_name + ",\n\
  1.1040 +\n\
  1.1041 +thank you for registering at the public alpha test of the LiquidFeedback\n\
  1.1042 +application programming interface. To complete the registration process,\n\
  1.1043 +you need to confirm your email address by opening the following URL:\n\
  1.1044 +\n\
  1.1045 +" + config.public_url_path + "register_test_confirm?secret=" + member_notify_email_secret + "\n\
  1.1046 +\n\
  1.1047 +\n\
  1.1048 +After you've confirmed your email address, your account will be automatically\n\
  1.1049 +activated.\n\
  1.1050 +\n\
  1.1051 +Your account name is:     " + member_name + "\n\
  1.1052 +\n\
  1.1053 +\n\
  1.1054 +You will need the following login and password to register and unregister\n\
  1.1055 +applications for your account later. This function is currently not\n\
  1.1056 +implemented, but please keep the credentials for future use.\n\
  1.1057 +\n\
  1.1058 +Account ID:               " + member_id + "\n\
  1.1059 +Login:                    " + member_login + "\n\
  1.1060 +Password:                 " + member_password + "\n\
  1.1061 +\n\
  1.1062 +\n\
  1.1063 +To make you able to actually access the API interface, we added the following\n\
  1.1064 +application key with full member access privileges to your account:\n\
  1.1065 +\n\
  1.1066 +API Key:                  " + api_key_member + "\n\
  1.1067 +\n\
  1.1068 +\n\
  1.1069 +The base address of the public test is: " + config.public_url_path + "\n\
  1.1070 +\n\
  1.1071 +The programming interface is described in the LiquidFeedback API\n\
  1.1072 +specification: http://dev.liquidfeedback.org/trac/lf/wiki/API\n\
  1.1073 +\n\
  1.1074 +The current implementation status of lfapi is published at the LiquidFeedback\n\
  1.1075 +API server page: http://dev.liquidfeedback.org/trac/lf/wiki/lfapi\n\
  1.1076 +\n\
  1.1077 +If you have any questions or suggestions, please use our public mailing list\n\
  1.1078 +at http://dev.liquidfeedback.org/cgi-bin/mailman/listinfo/main\n\
  1.1079 +\n\
  1.1080 +For issues regarding your test account, contact us via email at\n\
  1.1081 +lqfb-maintainers@public-software-group.org\n\
  1.1082 +\n\
  1.1083 +\n\
  1.1084 +Sincerely,\n\
  1.1085 +\n\
  1.1086 +Your LiquidFeedback maintainers",
  1.1087 +            },
  1.1088 +            function(err, result){
  1.1089 +              if(err){ console.log(err); }
  1.1090 +            });        
  1.1091 +                
  1.1092 +            respond('html', conn, req, res, 'ok', 'Account created. Please check your mailbox!<br /><br /><br /><a href="/">Back to start page</a>');
  1.1093 +          });
  1.1094 +        });
  1.1095 +      });
  1.1096 +    });
  1.1097 +  },
  1.1098 +  
  1.1099 +  /*
  1.1100 +  '/register': function (conn, req, res, params) {
  1.1101 +    var invite_key = params.invite_key;
  1.1102 +    var login = params.login;
  1.1103 +    var password = params.password;
  1.1104 +    var name = params.name;
  1.1105 +    var notify_email = params.notify_email;
  1.1106 +    if (!invite_key) {
  1.1107 +      respond('json', conn, req, res, 'unprocessable', null, 'No invite_key supplied.');
  1.1108 +      return;
  1.1109 +    };
  1.1110 +    if (!login) {
  1.1111 +      respond('json', conn, req, res, 'unprocessable', null, 'No login supplied.');
  1.1112 +      return;
  1.1113 +    };
  1.1114 +    if (!password) {
  1.1115 +      respond('json', conn, req, res, 'unprocessable', null, 'No password supplied.');
  1.1116 +      return;
  1.1117 +    };
  1.1118 +    if (!name) {
  1.1119 +      respond('json', conn, req, res, 'unprocessable', null, 'No name supplied.');
  1.1120 +      return;
  1.1121 +    };
  1.1122 +    if (!notify_email) {
  1.1123 +      respond('json', conn, req, res, 'unprocessable', null, 'No notify_email supplied.');
  1.1124 +      return;
  1.1125 +    };
  1.1126 +    // check if akey is valid and get member_id for akey
  1.1127 +    db.query(conn, req, res, { select: ['member.id'], from: ['member'], where: ['NOT member.activation AND member.invite_key = ' + db.pgEncode(invite_key)] }, function (result, conn) {
  1.1128 +      if (result.rows.length != 1) {
  1.1129 +        respond('json', conn, req, res, 'forbidden', null, 'Supplied invite_key is not valid.');
  1.1130 +        return;
  1.1131 +      };
  1.1132 +      var member_id = result.rows[0].id;
  1.1133 +      // check if name is available
  1.1134 +      db.query(conn, req, res, { select: ['NULL'], from: ['member'], where: ['member.name = ' + db.pgEncode(name)] }, function (result, conn) {
  1.1135 +        if (result.rows.length > 0) {
  1.1136 +          respond('json', conn, req, res, 'forbidden', null, 'Login name is not available, choose another one.');
  1.1137 +          return;
  1.1138 +        };
  1.1139 +        // check if login is available
  1.1140 +        db.query(conn, req, res, { select: ['NULL'], from: ['member'], where: ['member.login = ' + db.pgEncode(login)] }, function (result, conn) {
  1.1141 +          if (result.rows.length > 0) {
  1.1142 +            respond('json', conn, req, res, 'forbidden', null, 'Name is not available, choose another one.');
  1.1143 +            return;
  1.1144 +          };
  1.1145 +          var query = { update: 'member', set: { activation: 'now', active: true, } };
  1.1146 +          
  1.1147 +        });
  1.1148 +      });
  1.1149 +    });
  1.1150 +  },
  1.1151 +  */
  1.1152 +  
  1.1153 +  '/session': function (conn, req, res, params) {
  1.1154 +    var key = params.key;
  1.1155 +    if (!key) {
  1.1156 +      respond('json', conn, req, res, 'unprocessable', null, 'No application key supplied.');
  1.1157 +      return;
  1.1158 +    };
  1.1159 +    var query = new selector.Selector();
  1.1160 +    query.from('member');
  1.1161 +    query.join('member_application', null, 'member_application.member_id = member.id');
  1.1162 +    query.addField('member.id');
  1.1163 +    query.addWhere(['member.active AND member_application.key = ?', key]);
  1.1164 +    if (params.interactive) {
  1.1165 +      query.forUpdateOf('member');
  1.1166 +    }
  1.1167 +    db.query(conn, req, res, query, function (result, conn) {
  1.1168 +      if (result.rows.length != 1) {
  1.1169 +        respond('json', conn, req, res, 'forbidden', null, 'Supplied application key is not valid.');
  1.1170 +        return;
  1.1171 +      };
  1.1172 +      var member_id = result.rows[0].id;
  1.1173 +      var session_key = randomString(16);
  1.1174 +      req.sessions[session_key] = member_id;
  1.1175 +      var query;
  1.1176 +      if (params.interactive) {
  1.1177 +        query = new selector.SQLUpdate('member');
  1.1178 +        query.addWhere(['member.id = ?', member_id]);
  1.1179 +        query.addValues({ last_activity: 'now' });
  1.1180 +      }
  1.1181 +      db.query(conn, req, res, query, function (result, conn) {
  1.1182 +        respond('json', conn, req, res, 'ok', { session_key: session_key });
  1.1183 +      });
  1.1184 +    });
  1.1185 +  },
  1.1186 +  
  1.1187 +  '/member': function (conn, req, res, params) {
  1.1188 +    var fields = ['organizational_unit', 'internal_posts', 'realname', 'birthday', 'address', 'email', 'xmpp_address', 'website', 'phone', 'mobile_phone', 'profession', 'external_memberships', 'external_posts', 'statement']
  1.1189 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1190 +      var query = new selector.SQLUpdate('member');
  1.1191 +      query.addWhere(['member.id = ?', req.current_member_id]);
  1.1192 +      fields.forEach( function(field) {
  1.1193 +        if (typeof(params[field]) != 'undefined') {
  1.1194 +          query.addValues({ field: params[field] });
  1.1195 +        } else {
  1.1196 +          query.addValues({ field: null });
  1.1197 +        }
  1.1198 +      });
  1.1199 +      db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1200 +    });
  1.1201 +  },
  1.1202 +
  1.1203 +  '/membership': function (conn, req, res, params) {
  1.1204 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1205 +
  1.1206 +      // check if area_id is set
  1.1207 +      var area_id = parseInt(params.area_id);
  1.1208 +      if (!area_id) {
  1.1209 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an area_id.');
  1.1210 +        return;
  1.1211 +      }
  1.1212 +
  1.1213 +      // delete membership
  1.1214 +      if (params.delete) {
  1.1215 +        var query;
  1.1216 +        query = new selector.SQLDelete('membership');
  1.1217 +        query.addWhere(['area_id = ?', area_id]);
  1.1218 +        query.addWhere(['member_id = ?', req.current_member_id]);
  1.1219 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1220 +
  1.1221 +      // add membership
  1.1222 +      } else {
  1.1223 +        
  1.1224 +        // lock member for upsert
  1.1225 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1226 +
  1.1227 +          // check and lock privilege
  1.1228 +          requireAreaPrivilege(conn, req, res, area_id, function() {
  1.1229 +
  1.1230 +            // upsert membership
  1.1231 +            var query = new selector.Upserter('membership', ['area_id', 'member_id']);
  1.1232 +            query.addValues({ area_id: area_id, member_id: req.current_member_id });
  1.1233 +            db.query(conn, req, res, query, function(result) { 
  1.1234 +              respond('json', conn, req, res, 'ok');
  1.1235 +            });
  1.1236 +          });
  1.1237 +        });
  1.1238 +      }
  1.1239 +    });
  1.1240 +  },
  1.1241 +    
  1.1242 +  '/interest': function (conn, req, res, params) {
  1.1243 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1244 +      var query;
  1.1245 +
  1.1246 +      // check if issue_id is set
  1.1247 +      var issue_id = parseInt(params.issue_id);
  1.1248 +      if (!issue_id) {
  1.1249 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  1.1250 +        return;
  1.1251 +      }
  1.1252 +
  1.1253 +      // lock member for upsert
  1.1254 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1255 +
  1.1256 +        // delete interest
  1.1257 +        if (params.delete) {
  1.1258 +
  1.1259 +          // check issue state
  1.1260 +          requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  1.1261 +
  1.1262 +            // delete interest
  1.1263 +            query = new selector.SQLDelete('interest');
  1.1264 +            query.addWhere(['issue_id = ?', issue_id]);
  1.1265 +            query.addWhere(['member_id = ?', req.current_member_id]);
  1.1266 +            db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1267 +          });
  1.1268 +
  1.1269 +        // add interest
  1.1270 +        } else {
  1.1271 +
  1.1272 +          // check and lock privilege
  1.1273 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  1.1274 +
  1.1275 +            // check issue state
  1.1276 +            requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  1.1277 +
  1.1278 +              // upsert interest
  1.1279 +              var query = new selector.Upserter('interest', ['issue_id', 'member_id']);
  1.1280 +              query.addValues({ issue_id: issue_id, member_id: req.current_member_id });
  1.1281 +              db.query(conn, req, res, query, function(result) { 
  1.1282 +                respond('json', conn, req, res, 'ok');
  1.1283 +              });
  1.1284 +            });
  1.1285 +          });
  1.1286 +        };
  1.1287 +      });
  1.1288 +    });
  1.1289 +  },
  1.1290 +
  1.1291 +  '/issue_comment': function (conn, req, res, params) {
  1.1292 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1293 +      
  1.1294 +      var issue_id = parseInt(params.issue_id);
  1.1295 +      var formatting_engine = params.formatting_engine
  1.1296 +      var content = params.content;
  1.1297 +
  1.1298 +      if (!issue_id) {
  1.1299 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  1.1300 +        return;
  1.1301 +      }
  1.1302 +
  1.1303 +      // delete issue comment
  1.1304 +      if (params.delete) {
  1.1305 +        var query;
  1.1306 +        query = new selector.SQLDelete('issue_comment');
  1.1307 +        query.addWhere(['issue_id = ?', params.issue_id]);
  1.1308 +        query.addWhere(['member_id = ?', req.current_member_id]);
  1.1309 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1310 +
  1.1311 +      // upsert issue comment
  1.1312 +      } else {
  1.1313 +
  1.1314 +        // check if formatting engine is supplied and valid
  1.1315 +        if (!formatting_engine) {
  1.1316 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting engine supplied.');
  1.1317 +          return;
  1.1318 +        } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  1.1319 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  1.1320 +          return;
  1.1321 +        };
  1.1322 +
  1.1323 +        // check if content is supplied
  1.1324 +        if (!content) {
  1.1325 +          respond('json', conn, req, res, 'unprocessable', null, 'No content supplied.');
  1.1326 +          return;
  1.1327 +        }
  1.1328 +        
  1.1329 +        // lock member for upsert
  1.1330 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1331 +
  1.1332 +          // check and lock privilege
  1.1333 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  1.1334 +
  1.1335 +            // upsert issue comment
  1.1336 +            var query = new selector.Upserter('issue_comment', ['issue_id', 'member_id']);
  1.1337 +            query.addValues({
  1.1338 +              issue_id: issue_id,
  1.1339 +              member_id: req.current_member_id,
  1.1340 +              changed: 'now',
  1.1341 +              formatting_engine: formatting_engine,
  1.1342 +              content: content
  1.1343 +            });
  1.1344 +
  1.1345 +            db.query(conn, req, res, query, function(result) { 
  1.1346 +              respond('json', conn, req, res, 'ok');
  1.1347 +            });
  1.1348 +
  1.1349 +          });
  1.1350 +        });
  1.1351 +
  1.1352 +      }
  1.1353 +      
  1.1354 +    });
  1.1355 +  },
  1.1356 +
  1.1357 +   '/voting_comment': function (conn, req, res, params) {
  1.1358 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1359 +      
  1.1360 +      var issue_id = parseInt(params.issue_id);
  1.1361 +      var formatting_engine = params.formatting_engine
  1.1362 +      var content = params.content;
  1.1363 +
  1.1364 +      if (!issue_id) {
  1.1365 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  1.1366 +        return;
  1.1367 +      }
  1.1368 +
  1.1369 +        
  1.1370 +      // delete voting comment
  1.1371 +      if (params.delete) {
  1.1372 +        var query;
  1.1373 +        query = new selector.SQLDelete('voting_comment');
  1.1374 +        query.addWhere(['issue_id = ?', params.issue_id]);
  1.1375 +        query.addWhere(['member_id = ?', req.current_member_id]);
  1.1376 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1377 +
  1.1378 +      // upsert voting comment
  1.1379 +      } else {
  1.1380 +
  1.1381 +        // check if formatting engine is supplied and valid
  1.1382 +        if (!formatting_engine) {
  1.1383 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting engine supplied.');
  1.1384 +          return;
  1.1385 +        } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  1.1386 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  1.1387 +          return;
  1.1388 +        };
  1.1389 +
  1.1390 +        // check if content is supplied
  1.1391 +        if (!content) {
  1.1392 +          respond('json', conn, req, res, 'unprocessable', null, 'No content supplied.');
  1.1393 +          return;
  1.1394 +        }
  1.1395 +          
  1.1396 +        // lock member for upsert
  1.1397 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1398 +
  1.1399 +          // check and lock privilege
  1.1400 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  1.1401 +
  1.1402 +            // check issue state
  1.1403 +            requireIssueState(conn, req, res, issue_id, ['voting', 'finished_with_winner', 'finished_without_winner'], function() {
  1.1404 +
  1.1405 +              // upsert voting comment
  1.1406 +              var query = new selector.Upserter('voting_comment', ['issue_id', 'member_id']);
  1.1407 +              query.addValues({
  1.1408 +                issue_id: issue_id,
  1.1409 +                member_id: req.current_member_id,
  1.1410 +                changed: 'now',
  1.1411 +                formatting_engine: formatting_engine,
  1.1412 +                content: content
  1.1413 +              });
  1.1414 +
  1.1415 +              db.query(conn, req, res, query, function(result) { 
  1.1416 +                respond('json', conn, req, res, 'ok');
  1.1417 +              });
  1.1418 +
  1.1419 +            });
  1.1420 +          });
  1.1421 +        })
  1.1422 +      };
  1.1423 +    });
  1.1424 +  },
  1.1425 +
  1.1426 +  '/supporter': function (conn, req, res, params) {
  1.1427 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1428 +      var initiative_id = parseInt(params.initiative_id);
  1.1429 +      var draft_id = parseInt(params.draft_id);
  1.1430 +
  1.1431 +      // check if needed arguments are supplied
  1.1432 +      if (!initiative_id) {
  1.1433 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an initiative_id.');
  1.1434 +        return;
  1.1435 +      }
  1.1436 +
  1.1437 +      if (!draft_id) {
  1.1438 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an draft_id.');
  1.1439 +        return;
  1.1440 +      }
  1.1441 +
  1.1442 +      // lock member for upsert
  1.1443 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1444 +
  1.1445 +        // delete supporter
  1.1446 +        if (params.delete) {
  1.1447 +          
  1.1448 +          // check issue state
  1.1449 +          requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion', 'verification'], function() {
  1.1450 +
  1.1451 +            // delete supporter
  1.1452 +            var query = new selector.SQLDelete('supporter');
  1.1453 +            query.addWhere(['initiative_id = ?', initiative_id]);
  1.1454 +            query.addWhere(['member_id = ?', req.current_member_id]);
  1.1455 +            db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1456 +
  1.1457 +          });
  1.1458 +          
  1.1459 +        // upsert supporter
  1.1460 +        } else {
  1.1461 +
  1.1462 +          // check and lock privilege
  1.1463 +          requireInitiativePrivilege(conn, req, res, initiative_id, function() {
  1.1464 +
  1.1465 +            // check issue state
  1.1466 +            requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion', 'verification'], function() {
  1.1467 +
  1.1468 +              // check if given draft is the current one
  1.1469 +              var query = new selector.Selector('current_draft');
  1.1470 +              query.addField('NULL');
  1.1471 +              query.addWhere(['current_draft.initiative_id = ?', initiative_id]);
  1.1472 +              query.addWhere(['current_draft.id = ?', draft_id]);
  1.1473 +              
  1.1474 +              db.query(conn, req, res, query, function(result) { 
  1.1475 +                if (result.rows.length != 1) {
  1.1476 +                  respond('json', conn, req, res, 'conflict', null, 'The draft with the supplied draft_id is not the current one anymore!');
  1.1477 +                  return;
  1.1478 +                }
  1.1479 +                
  1.1480 +                // upsert supporter
  1.1481 +                var query = new selector.Upserter('supporter', ['initiative_id', 'member_id']);
  1.1482 +                query.addValues({
  1.1483 +                  initiative_id: initiative_id,
  1.1484 +                  member_id: req.current_member_id,
  1.1485 +                  draft_id: draft_id
  1.1486 +                });
  1.1487 +
  1.1488 +                db.query(conn, req, res, query, function(result) { 
  1.1489 +                  respond('json', conn, req, res, 'ok');
  1.1490 +                });
  1.1491 +
  1.1492 +              });
  1.1493 +            });
  1.1494 +          });
  1.1495 +        };
  1.1496 +      });
  1.1497 +    });
  1.1498 +  },
  1.1499 +
  1.1500 +  '/draft': function (conn, req, res, params) {
  1.1501 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1502 +      var area_id = parseInt(params.area_id);
  1.1503 +      var policy_id = parseInt(params.policy_id);
  1.1504 +      var issue_id = parseInt(params.issue_id);
  1.1505 +      var initiative_id = parseInt(params.initiative_id);
  1.1506 +      var initiative_name = params.initiative_name;
  1.1507 +      var initiative_discussion_url = params.initiative_discussion_url;
  1.1508 +      var formatting_engine = params.formatting_engine;
  1.1509 +      var content = params.content;
  1.1510 +
  1.1511 +      if (!initiative_discussion_url) initiative_discussion_url = null;
  1.1512 +
  1.1513 +      // check parameters
  1.1514 +      if (!formatting_engine) {
  1.1515 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting_engine supplied.');
  1.1516 +          return;
  1.1517 +      } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  1.1518 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  1.1519 +          return;
  1.1520 +      };
  1.1521 +
  1.1522 +      if (!content) {
  1.1523 +        respond('json', conn, req, res, 'unprocessable', null, 'No draft content supplied.');
  1.1524 +        return;
  1.1525 +      };
  1.1526 +
  1.1527 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1528 +        
  1.1529 +        // new draft in new initiative in new issue
  1.1530 +        if (area_id && !issue_id && !initiative_id) {
  1.1531 +
  1.1532 +          // check parameters for new issue
  1.1533 +          if (!policy_id) {
  1.1534 +            respond('json', conn, req, res, 'unprocessable', null, 'No policy supplied.');
  1.1535 +            return;
  1.1536 +          }
  1.1537 +          
  1.1538 +          if (!initiative_name) {
  1.1539 +            respond('json', conn, req, res, 'unprocessable', null, 'No initiative name supplied.');
  1.1540 +            return;
  1.1541 +          }
  1.1542 +          
  1.1543 +          requireAreaPrivilege(conn, req, res, area_id, function() {
  1.1544 +
  1.1545 +            // check if policy is allowed in this area and if area and policy are active
  1.1546 +            var query = new selector.Selector();
  1.1547 +            query.from('allowed_policy');
  1.1548 +            query.join('area', null, 'area.id = allowed_policy.area_id AND area.active');
  1.1549 +            query.join('policy', null, 'policy.id = allowed_policy.policy_id AND policy.active');
  1.1550 +            query.addField('NULL');
  1.1551 +            query.addWhere(['area.id = ? AND policy.id = ?', area_id, policy_id]);
  1.1552 +            db.query(conn, req, res, query, function (result, conn) {
  1.1553 +              if (result.rows.length != 1) {
  1.1554 +                respond('json', conn, req, res, 'unprocessable', null, 'Area and/or policy doesn\'t exist, area and/or policy is not active or policy is not allowed in this area.');
  1.1555 +                return;
  1.1556 +              };
  1.1557 +
  1.1558 +              // check contingent
  1.1559 +              requireContingentLeft(conn, req, res, true, function() {
  1.1560 +                
  1.1561 +                // insert new issue
  1.1562 +                var query = new selector.SQLInsert('issue');
  1.1563 +                query.addValues({
  1.1564 +                  area_id: area_id,
  1.1565 +                  policy_id: policy_id
  1.1566 +                });
  1.1567 +                query.addReturning('id');
  1.1568 +                db.query(conn, req, res, query, function(result) {
  1.1569 +                  var issue_id = result.rows[0].id;
  1.1570 +
  1.1571 +                  // insert new initiative
  1.1572 +                  var query = new selector.SQLInsert('initiative');
  1.1573 +                  query.addValues({
  1.1574 +                    issue_id: issue_id,
  1.1575 +                    name: initiative_name,
  1.1576 +                    discussion_url: initiative_discussion_url
  1.1577 +                  });
  1.1578 +                  query.addReturning('id');
  1.1579 +                  db.query(conn, req, res, query, function(result) {
  1.1580 +                    var initiative_id = result.rows[0].id;
  1.1581 +                    
  1.1582 +                    // insert initiator
  1.1583 +                    var query = new selector.SQLInsert('initiator');
  1.1584 +                    query.addValues({ initiative_id: initiative_id, member_id: req.current_member_id, accepted: true });
  1.1585 +                    db.query(conn, req, res, query, function(result) {
  1.1586 +
  1.1587 +                      // insert new draft
  1.1588 +                      var query = new selector.SQLInsert('draft');
  1.1589 +                      query.addValues({
  1.1590 +                        initiative_id: initiative_id,
  1.1591 +                        author_id: req.current_member_id,
  1.1592 +                        formatting_engine: formatting_engine,
  1.1593 +                        content: content 
  1.1594 +                      });
  1.1595 +                      query.addReturning('id');
  1.1596 +                      db.query(conn, req, res, query, function (result, conn) {
  1.1597 +                        var draft_id = result.rows[0].id;
  1.1598 +
  1.1599 +                        respond('json', conn, req, res, 'ok', { issue_id: issue_id, initiative_id: initiative_id, draft_id: draft_id } );
  1.1600 +                      });
  1.1601 +                    });
  1.1602 +                  });
  1.1603 +                });
  1.1604 +              });
  1.1605 +            });
  1.1606 +          });
  1.1607 +
  1.1608 +        // new draft in new initiative in existant issue
  1.1609 +        } else if (issue_id && !area_id && !initiative_id) {
  1.1610 +
  1.1611 +          // check privilege
  1.1612 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  1.1613 +            
  1.1614 +            // check issue state
  1.1615 +            requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  1.1616 +            
  1.1617 +              // check contingent
  1.1618 +              requireContingentLeft(conn, req, res, true, function() {
  1.1619 +
  1.1620 +                // insert initiative
  1.1621 +                var query = new selector.SQLInsert('initiative');
  1.1622 +                query.addValues({
  1.1623 +                  issue_id: issue_id,
  1.1624 +                  name: initiative_name,
  1.1625 +                  discussion_url: initiative_discussion_url
  1.1626 +                });
  1.1627 +                query.addReturning('id');
  1.1628 +                db.query(conn, req, res, query, function(result) {
  1.1629 +                  var initiative_id = result.rows[0].id;
  1.1630 +                  
  1.1631 +                  // insert initiator
  1.1632 +                  var query = new selector.SQLInsert('initiator');
  1.1633 +                  query.addValues({
  1.1634 +                    initiative_id: initiative_id,
  1.1635 +                    member_id: req.current_member_id,
  1.1636 +                    accepted: true
  1.1637 +                  });
  1.1638 +                  db.query(conn, req, res, query, function(result) {
  1.1639 +
  1.1640 +                    // insert draft
  1.1641 +                    var query = new selector.SQLInsert('draft');
  1.1642 +                    query.addValues({
  1.1643 +                      initiative_id: initiative_id,
  1.1644 +                      author_id: req.current_member_id,
  1.1645 +                      formatting_engine: formatting_engine,
  1.1646 +                      content: content
  1.1647 +                    });
  1.1648 +                    query.addReturning('id');
  1.1649 +                    db.query(conn, req, res, query, function (result, conn) {
  1.1650 +
  1.1651 +                      var draft_id = result.rows[0].id;
  1.1652 +                      respond('json', conn, req, res, 'ok', { initiative_id: initiative_id, draft_id: draft_id } );
  1.1653 +                      
  1.1654 +                    });
  1.1655 +                  });
  1.1656 +                });
  1.1657 +              });
  1.1658 +            });
  1.1659 +          });
  1.1660 +
  1.1661 +        // new draft in existant initiative
  1.1662 +        } else if (initiative_id && !area_id && !issue_id ) {
  1.1663 +
  1.1664 +          // check privilege
  1.1665 +          requireInitiativePrivilege(conn, req, res, initiative_id, function() {
  1.1666 +            
  1.1667 +            // check issue state
  1.1668 +            requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion'], function() {
  1.1669 +            
  1.1670 +
  1.1671 +              // get initiator
  1.1672 +              var query = new selector.Selector();
  1.1673 +              query.from('initiator');
  1.1674 +              query.addField('accepted');
  1.1675 +              query.addWhere(['initiative_id = ? AND member_id = ?', initiative_id, req.current_member_id]);
  1.1676 +              db.query(conn, req, res, query, function (result, conn) {
  1.1677 +
  1.1678 +                // if member is not initiator, deny creating new draft
  1.1679 +                if (result.rows.length != 1) {
  1.1680 +                  respond('json', conn, req, res, 'forbidden', null, 'You are not initiator of this initiative and not allowed to update its draft.');
  1.1681 +                  return;
  1.1682 +                }
  1.1683 +                var initiator = result.rows[0];
  1.1684 +                if (!initiator.accepted) {
  1.1685 +                  respond('json', conn, req, res, 'forbidden', null, 'You have been invited as initiator, but haven\'t accepted invitation and you are not allowed to update this initiative.');
  1.1686 +                  return;
  1.1687 +                };
  1.1688 +
  1.1689 +                // check contingent
  1.1690 +                requireContingentLeft(conn, req, res, false, function() {
  1.1691 +
  1.1692 +                  // insert new draft
  1.1693 +                  var query = new selector.SQLInsert('draft');
  1.1694 +                  query.addValues({
  1.1695 +                    initiative_id: initiative_id,
  1.1696 +                    author_id: req.current_member_id,
  1.1697 +                    formatting_engine: formatting_engine,
  1.1698 +                    content: content
  1.1699 +                  });
  1.1700 +                  query.addReturning('id');
  1.1701 +                  db.query(conn, req, res, query, function (result, conn) {
  1.1702 +
  1.1703 +                    var draft_id = result.rows[0].id;
  1.1704 +                    respond('json', conn, req, res, 'ok', { draft_id: draft_id } );
  1.1705 +                  });
  1.1706 +                });
  1.1707 +              });
  1.1708 +            });
  1.1709 +          });
  1.1710 +
  1.1711 +        // none of them (invalid request)
  1.1712 +        } else {
  1.1713 +          respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of area_id, issue_id or initiative_id must be supplied!');
  1.1714 +        };
  1.1715 +        
  1.1716 +      });
  1.1717 +    });
  1.1718 +  },
  1.1719 +
  1.1720 +  '/suggestion': function (conn, req, res, params) {
  1.1721 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1722 +      // TODO
  1.1723 +    });
  1.1724 +  },
  1.1725 +  
  1.1726 +  '/opinion': function (conn, req, res, params) {
  1.1727 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1728 +      // TODO
  1.1729 +    });
  1.1730 +  },
  1.1731 +
  1.1732 +  '/delegation': function (conn, req, res, params) {
  1.1733 +    requireAccessLevel(conn, req, res, 'member', function() {
  1.1734 +      var unit_id = parseInt(params.unit_id);
  1.1735 +      var area_id = parseInt(params.area_id);
  1.1736 +      var issue_id = parseInt(params.issue_id);
  1.1737 +      var trustee_id;
  1.1738 +
  1.1739 +      if (params.trustee_id == '') {
  1.1740 +        trustee_id = null;
  1.1741 +      } else {
  1.1742 +        trustee_id = parseInt(params.trustee_id);
  1.1743 +      }
  1.1744 +      
  1.1745 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  1.1746 +        
  1.1747 +        if (params.delete) {
  1.1748 +          var query = new selector.SQLDelete('delegation')
  1.1749 +          if (unit_id && !area_id && !issue_id) {
  1.1750 +            query.addWhere(['unit_id = ?', unit_id]);
  1.1751 +          } else if (!unit_id && area_id && !issue_id) {
  1.1752 +            query.addWhere(['area_id = ?', area_id]);
  1.1753 +          } else if (!unit_id && !area_id && issue_id) {
  1.1754 +            query.addWhere(['issue_id = ?', issue_id]);
  1.1755 +          } else {
  1.1756 +            respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of unit, area_id, issue_id must be supplied!');
  1.1757 +            return;
  1.1758 +          } 
  1.1759 +          query.addWhere(['truster_id = ?', req.current_member_id]);
  1.1760 +          db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1761 +        } else {
  1.1762 +          var query = new selector.Upserter('delegation', ['truster_id']);
  1.1763 +          query.addValues({
  1.1764 +            truster_id: req.current_member_id,
  1.1765 +            trustee_id: trustee_id
  1.1766 +          });
  1.1767 +          if (unit_id && !area_id && !issue_id) {
  1.1768 +            
  1.1769 +            // check privilege
  1.1770 +            requireUnitPrivilege(conn, req, res, unit_id, function() {
  1.1771 +
  1.1772 +              query.addKeys(['unit_id'])
  1.1773 +              query.addValues({ unit_id: unit_id, scope: 'unit' });
  1.1774 +              db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1775 +            });
  1.1776 +            
  1.1777 +          } else if (!unit_id && area_id && !issue_id) {
  1.1778 +
  1.1779 +            // check privilege
  1.1780 +            requireAreaPrivilege(conn, req, res, area_id, function() {
  1.1781 +
  1.1782 +              query.addKeys(['area_id'])
  1.1783 +              query.addValues({ area_id: area_id, scope: 'area' });
  1.1784 +              db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1785 +            });
  1.1786 +
  1.1787 +          } else if (!unit_id && !area_id && issue_id) {
  1.1788 +
  1.1789 +            // check privilege
  1.1790 +            requireIssuePrivilege(conn, req, res, issue_id, function() {
  1.1791 +
  1.1792 +              // check issue state
  1.1793 +              requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification', 'voting'], function() {
  1.1794 +                
  1.1795 +                query.addKeys(['issue_id'])
  1.1796 +                query.addValues({ issue_id: issue_id, scope: 'issue' });
  1.1797 +                db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  1.1798 +              });
  1.1799 +            });
  1.1800 +          } else {
  1.1801 +            respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of unit_id, area_id, issue_id must be supplied!');
  1.1802 +            return;
  1.1803 +          } 
  1.1804 +        }
  1.1805 +        
  1.1806 +      });
  1.1807 +      
  1.1808 +    });
  1.1809 +  },
  1.1810 +
  1.1811 +};