lfapi

changeset 0:ce6f95d23e1c

Initial checkin
author bsw
date Sat Sep 10 23:31:20 2011 +0200 (2011-09-10)
parents
children 9fe872cc376d
files LICENSE README config.js lfapi.js lfapi/db.js lfapi/fields.js lfapi/general_params.js lfapi/main.js lib/selector.js lib/stringthesizer.js
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/LICENSE	Sat Sep 10 23:31:20 2011 +0200
     1.3 @@ -0,0 +1,20 @@
     1.4 +Copyright (c) 2011 Public Software Group e. V., Berlin, Germany
     1.5 +
     1.6 +Permission is hereby granted, free of charge, to any person obtaining a
     1.7 +copy of this software and associated documentation files (the "Software"),
     1.8 +to deal in the Software without restriction, including without limitation
     1.9 +the rights to use, copy, modify, merge, publish, distribute, sublicense,
    1.10 +and/or sell copies of the Software, and to permit persons to whom the
    1.11 +Software is furnished to do so, subject to the following conditions:
    1.12 +
    1.13 +The above copyright notice and this permission notice shall be included in
    1.14 +all copies or substantial portions of the Software.
    1.15 +
    1.16 +THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    1.17 +IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    1.18 +FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    1.19 +AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    1.20 +LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
    1.21 +FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
    1.22 +DEALINGS IN THE SOFTWARE.
    1.23 +
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/README	Sat Sep 10 23:31:20 2011 +0200
     2.3 @@ -0,0 +1,3 @@
     2.4 +Installation instruction are available at
     2.5 +
     2.6 +http://dev.liquidfeedback.org/trac/lf/wiki/lfapi
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/config.js	Sat Sep 10 23:31:20 2011 +0200
     3.3 @@ -0,0 +1,39 @@
     3.4 +// ==========================================================================
     3.5 +// configuration of lfapi
     3.6 +// --------------------------------------------------------------------------
     3.7 +// Please read this file carefully and adjust settings for your system
     3.8 +// --------------------------------------------------------------------------
     3.9 +
    3.10 +// the interface address the service should bind to (0.0.0.0 for ALL)
    3.11 +exports.bind_address = '0.0.0.0';
    3.12 +
    3.13 +// the port number the service should bind to (usually 80)
    3.14 +exports.bind_port = 25520;
    3.15 +
    3.16 +// access level for not logged in users (may be 'full', 'pseudonym',
    3.17 +// 'anonymous', 'none' or 'devel')
    3.18 +// never set access level to 'devel' on a productive installation!
    3.19 +exports.public_access_level = 'full';
    3.20 +
    3.21 +// connection string to access the LiquidFeedback Core database
    3.22 +exports.connectionString = 'pg://localhost/liquid_feedback';
    3.23 +
    3.24 +// public base url (including trailing slash)
    3.25 +exports.public_url_path = 'http://lf.example.org/api/';
    3.26 +
    3.27 +// mail server, email sender and subject settings
    3.28 +exports.mail = {
    3.29 +  smtp_host:           'localhost',
    3.30 +  smtp_port:           '25',
    3.31 +  smtp_ssl:            false,
    3.32 +  smtp_domain:         'localhost',
    3.33 +  //smtp_authentication: 'login',
    3.34 +  //smtp_username:       'username',
    3.35 +  //smtp_password:       'password',
    3.36 +  from:                'Sender name <senderaddress@example.org>',
    3.37 +  subject_prefix:      '[email subject prefix] '
    3.38 +};
    3.39 +
    3.40 +exports.settings = {
    3.41 +  result_row_limit: { max: 1001, default: 101 }
    3.42 +}
     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/lfapi.js	Sat Sep 10 23:31:20 2011 +0200
     4.3 @@ -0,0 +1,116 @@
     4.4 +#!/usr/bin/env node
     4.5 +
     4.6 +// --------------------------------------------------------------------------
     4.7 +// end of configuration
     4.8 +// ==========================================================================
     4.9 +
    4.10 +var main = require('./lfapi/main.js');
    4.11 +
    4.12 +var config = main.config;
    4.13 +var db = main.db;
    4.14 +
    4.15 +//var config = require('./config.js');
    4.16 +
    4.17 +var http = require('http');
    4.18 +var url = require('url');
    4.19 +var qs = require('querystring');
    4.20 +
    4.21 +// Add includes method to Arrays
    4.22 +Array.prototype.includes = function (val) {
    4.23 +  for (var i=0; i < this.length; i++) if (this[i] === val) return true;
    4.24 +  return false;
    4.25 +};
    4.26 +
    4.27 +// Member sessions, stored value is member_id
    4.28 +var sessions = { };
    4.29 +
    4.30 +// create http server
    4.31 +var server = http.createServer(function (req, res, params) {
    4.32 +  req.setEncoding('utf8');
    4.33 +  
    4.34 +  // parse get params
    4.35 +  var url_info = url.parse(req.url, true);
    4.36 +  var params = url_info.query;
    4.37 +  req.params = params;
    4.38 +
    4.39 +  req.current_access_level = config.public_access_level;
    4.40 +  req.current_member_id;
    4.41 +
    4.42 +  req.sessions = sessions;
    4.43 +  
    4.44 +  // session handling
    4.45 +  if (params.session_key) {
    4.46 +    if (sessions[params.session_key]) {
    4.47 +      req.current_member_id = sessions[params.session_key];
    4.48 +      req.current_access_level = 'member'
    4.49 +    } else {
    4.50 +      main.respond('json', null, req, res, 'forbidden', 'Invalid session key');
    4.51 +    }
    4.52 +  }
    4.53 +  
    4.54 +  // pick cookies from http headers
    4.55 +  var cookies = {};
    4.56 +  if (req.headers.cookie) {
    4.57 +    req.headers.cookie.split(';').forEach(function (cookie) {
    4.58 +      var parts = cookie.split('=');
    4.59 +      cookies[parts[0].trim()] = (parts[1] || '' ).trim();
    4.60 +    });
    4.61 +  };
    4.62 +  
    4.63 +  console.log(req.socket._idleStart, req.socket.remoteAddress, req.current_member_id, req.current_access_level, url_info.pathname, url_info.query);
    4.64 +
    4.65 +  var body = '';
    4.66 +  req.on('data', function (data) {
    4.67 +      body += data;
    4.68 +  });
    4.69 +  req.on('end', function () {
    4.70 +    var post_params = qs.parse(body);
    4.71 +    for (key in post_params) {
    4.72 +      params[key] = post_params[key];
    4.73 +    };
    4.74 +
    4.75 +    if (['POST', 'DELETE'].includes(params.http_method)) {
    4.76 +      req.method = params.http_method;
    4.77 +    }
    4.78 +    
    4.79 +    var routes;
    4.80 +    
    4.81 +    switch(req.method) {
    4.82 +      case 'GET':
    4.83 +        routes = main.get;
    4.84 +        break;
    4.85 +        
    4.86 +      case 'DELETE':
    4.87 +        // delete requests are handled like post request with parameter delete=1
    4.88 +        params.delete = '1';
    4.89 +
    4.90 +      case 'POST':
    4.91 +        routes = main.post;
    4.92 +        break;
    4.93 +        
    4.94 +      default:
    4.95 +        main.respond('json', null, req, res, 'not found');
    4.96 +        break;
    4.97 +        
    4.98 +    };
    4.99 +
   4.100 +    // dispatch request based on method and url
   4.101 +    
   4.102 +    var routing_target = routes[url_info.pathname]
   4.103 +    if (routing_target) {
   4.104 +      db.query(config.connectionString, req, res, 'START TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE', function (result, conn) {
   4.105 +        routing_target.apply(this, [conn, req, res, params]);
   4.106 +      });
   4.107 +    } else {
   4.108 +      main.respond('json', null, req, res, 'not found');
   4.109 +    };
   4.110 +    
   4.111 +  });
   4.112 +  
   4.113 +// actually connect the http server to a network interface
   4.114 +}).listen(config.bind_port, config.bind_address);
   4.115 +
   4.116 +console.log('LiquidFeedback API server started with ' + config.public_access_level + ' public access at ' + config.bind_address + ':' + config.bind_port);
   4.117 +
   4.118 +
   4.119 +
     5.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     5.2 +++ b/lfapi/db.js	Sat Sep 10 23:31:20 2011 +0200
     5.3 @@ -0,0 +1,62 @@
     5.4 +pg = require('pg');
     5.5 +exports.pg = pg;
     5.6 +
     5.7 +selector = require('../lib/selector.js');
     5.8 +exports.selector = selector;
     5.9 +
    5.10 +
    5.11 +// ==========================================================================
    5.12 +// Database access helper function
    5.13 +// --------------------------------------------------------------------------
    5.14 +
    5.15 +
    5.16 +// executes a db query and call given callback function if suceeded
    5.17 +exports.query = function (conn, req, res, query_object, callback) {
    5.18 +  if (!conn || !query_object) {
    5.19 +    callback(null, conn);
    5.20 +    return;
    5.21 +  };
    5.22 +  if (typeof(conn) == 'string') {
    5.23 +    // connect to database
    5.24 +    conn = pg.connect(conn, function(err, conn) {
    5.25 +      // TODO error handling
    5.26 +      if(err) {
    5.27 +        console.log(err);
    5.28 +      };
    5.29 +      exports.query(conn, req, res, query_object, callback);
    5.30 +    });
    5.31 +    return;
    5.32 +  };
    5.33 +  if (query_object instanceof selector.Upserter) {
    5.34 +    exports.query(conn, req, res, query_object.getSelector(), function(result, conn) {
    5.35 +      if (result.rows.length > 1) {
    5.36 +        exports.error_handler('json', conn, req, res, "error", null, "Multiple rows found for primary key.");
    5.37 +      } else if (result.rows.length == 1) {
    5.38 +        exports.query(conn, req, res, query_object.getSQLUpdate(), callback);
    5.39 +      } else {
    5.40 +        exports.query(conn, req, res, query_object.getSQLInsert(), callback);
    5.41 +      }
    5.42 +      
    5.43 +    });
    5.44 +    
    5.45 +  } else {
    5.46 +    var query;
    5.47 +
    5.48 +    if (typeof(query_object) == 'string') {
    5.49 +      query = { cmd: query_object, args: {} };
    5.50 +    } else {
    5.51 +      query = query_object.assemble(function(i) { return ('$' + (i + 1)); });
    5.52 +    }
    5.53 +    
    5.54 +    console.log('  > SQL: ', query.cmd, query.args ? query.args : '');
    5.55 +    
    5.56 +    conn.query(query.cmd, query.args, function(err, result) {
    5.57 +      if (err) {
    5.58 +        console.log(err);
    5.59 +        exports.error_handler('json', conn, req, res, "error", null, err.message);
    5.60 +      } else {
    5.61 +        callback(result, conn);
    5.62 +      }
    5.63 +    });
    5.64 +  }
    5.65 +}
     6.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     6.2 +++ b/lfapi/fields.js	Sat Sep 10 23:31:20 2011 +0200
     6.3 @@ -0,0 +1,22 @@
     6.4 +// ==========================================================================
     6.5 +// fields of main data structures
     6.6 +// --------------------------------------------------------------------------
     6.7 +
     6.8 +exports.member =  ['id', 'name', 'identification', 'organizational_unit', 'internal_posts', 'realname', 'birthday', 'address', 'email', 'xmpp_address', 'website', 'phone', 'mobile_phone', 'profession', 'external_memberships', 'external_posts', 'statement', 'active', 'locked', 'created', 'last_activity'];
     6.9 +exports.member_pseudonym = ['id', 'name'];
    6.10 +exports.policy = ['id', 'index', 'active', 'name', 'description', 'admission_time', 'discussion_time', 'verification_time', 'voting_time', 'issue_quorum_num', 'issue_quorum_den', 'initiative_quorum_num', 'initiative_quorum_den', 'direct_majority_num', 'direct_majority_den', 'direct_majority_strict', 'direct_majority_positive', 'direct_majority_non_negative', 'indirect_majority_num', 'indirect_majority_den', 'indirect_majority_strict', 'indirect_majority_positive', 'indirect_majority_non_negative', 'no_reverse_beat_path', 'no_multistage_majority'];
    6.11 +exports.unit = ['id', 'parent_id', 'active', 'name', 'description', 'member_count'];
    6.12 +exports.area = ['id', 'unit_id', 'active', 'name', 'description', 'direct_member_count', 'member_weight'];
    6.13 +exports.issue = ['id', 'area_id', 'policy_id', 'state', 'created', 'accepted', 'half_frozen', 'fully_frozen', 'closed', 'ranks_available', 'cleaned', 'admission_time', 'discussion_time', 'verification_time', 'voting_time', 'snapshot', 'latest_snapshot_event', 'population', 'voter_count', 'status_quo_schulze_rank'];
    6.14 +exports.initiative = ['issue_id', 'id', 'name', 'discussion_url', 'created', 'revoked', 'revoked_by_member_id', 'suggested_initiative_id', 'admitted', 'supporter_count', 'informed_supporter_count', 'satisfied_supporter_count', 'satisfied_informed_supporter_count', 'positive_votes', 'negative_votes', 'rank', 'direct_majority', 'indirect_majority', 'schulze_rank', 'better_than_status_quo', 'worse_than_status_quo', 'reverse_beat_path', 'multistage_majority', 'eligible', 'winner'];
    6.15 +exports.suggestion = ['initiative_id', 'id', 'created', 'author_id', 'name', 'formatting_engine', 'content', 'minus2_unfulfilled_count', 'minus2_fulfilled_count', 'minus1_unfulfilled_count', 'minus1_fulfilled_count', 'plus1_unfulfilled_count', 'plus1_fulfilled_count', 'plus2_unfulfilled_count', 'plus2_fulfilled_count'];
    6.16 +exports.suggestion_pseudonym = ['initiative_id', 'id', 'created', 'name', 'description', 'minus2_unfulfilled_count', 'minus2_fulfilled_count', 'minus1_unfulfilled_count', 'minus1_fulfilled_count', 'plus1_unfulfilled_count', 'plus1_fulfilled_count', 'plus2_unfulfilled_count', 'plus2_fulfilled_count'];
    6.17 +
    6.18 +// add fields of a data structure to where AND group by clause of a query
    6.19 +exports.addObjectFields = function (query, class, field_class) {
    6.20 +  if (!field_class) { field_class = class };
    6.21 +  exports[field_class].forEach(function(field) {
    6.22 +    query.addField('"' + class + '"."' + field + '"', null, ['grouped']);
    6.23 +    //query.addGroupBy('"' + class + '"."' + '"' + field + '"');
    6.24 +  });
    6.25 +};
    6.26 \ No newline at end of file
     7.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     7.2 +++ b/lfapi/general_params.js	Sat Sep 10 23:31:20 2011 +0200
     7.3 @@ -0,0 +1,201 @@
     7.4 +config = require('../config.js');
     7.5 +exports.config = config;
     7.6 +
     7.7 +// ==========================================================================
     7.8 +// Handle generic request parameters
     7.9 +// --------------------------------------------------------------------------
    7.10 +
    7.11 +
    7.12 +exports.addLimitAndOffset = function (query, params) {
    7.13 +  var limit = params.limit ? parseInt(params.limit) : config.settings.result_row_limit.default
    7.14 +  if (limit > config.settings.result_row_limit.max) {
    7.15 +    limit = config.settings.result_row_limit.default;
    7.16 +  }
    7.17 +  query.limit(limit);
    7.18 +  query.offset(params.offset ? parseInt(params.offset) : 0);
    7.19 +}
    7.20 +
    7.21 +// add member related options to a db query according to parameters given by client
    7.22 +exports.addMemberOptions = function (req, query, params, relation) {
    7.23 +  var table_name = 'member';
    7.24 +  if (relation) {
    7.25 +    table_name = relation + '_member';
    7.26 +  };
    7.27 +  var member_id = params[relation ? relation + '_member_id' : 'member_id'];
    7.28 +  var member_disabled = params[relation ? relation + '_member_disabled' : 'member_disabled'] ? true : false;
    7.29 +  var member_search = params[relation ? relation + '_member_search' : 'member_search'];
    7.30 +  var member_order_by_name = parseInt(params[relation ? relation + '_member_order_by_name' : 'member_order_by_name']);
    7.31 +  var member_order_by_created = parseInt(params[relation ? relation + '_member_order_by_created' : 'member_order_by_created']);
    7.32 +  
    7.33 +  if (member_id) {
    7.34 +    query.addWhere(['"' + table_name + '"."id" IN (??)', member_id.split(',')]);
    7.35 +  };
    7.36 +  if (member_disabled) {
    7.37 +    query.addWhere('"' + table_name + '"."active" = FALSE');
    7.38 +  } else {
    7.39 +    query.addWhere('"' + table_name + '"."active" = TRUE OR "' + table_name + '"."active" ISNULL');
    7.40 +  };
    7.41 +  if (member_search) {
    7.42 +    query.addWhere(['"' + table_name + '"."text_search_data" @@ text_search_query(?)', member_search]);
    7.43 +  };
    7.44 +  if (member_order_by_name) {
    7.45 +    query.addOrderBy('"' + table_name + '"."name"')
    7.46 +  }
    7.47 +  if (member_order_by_created) {
    7.48 +    query.addOrderBy('"' + table_name + '"."created" DESC')
    7.49 +  }
    7.50 +};
    7.51 +
    7.52 +// add policy related options to a db query according to parameters given by client
    7.53 +exports.addPolicyOptions = function (req, query, params) {
    7.54 +  if (params.policy_id) {
    7.55 +    query.addWhere(['policy.id IN (??)', params.policy_id.split(',')]);
    7.56 +  }
    7.57 +  if (params.policy_order_by_name) {
    7.58 +    query.addOrderBy('"policy"."name"')
    7.59 +  }
    7.60 +};
    7.61 +
    7.62 +// add unit related options to a db query according to parameters given by client
    7.63 +exports.addUnitOptions = function (req, query, params) {
    7.64 +  if (params.unit_id) {
    7.65 +    query.addWhere(['"unit"."id" IN (??)', params.unit_id.split(',')]);
    7.66 +  }
    7.67 +  if (params.unit_parent_id) {
    7.68 +    query.addWhere(['"unit"."parent_id" = ?', params.unit_parent_id]);
    7.69 +  }
    7.70 +  if (params.unit_without_parent) {
    7.71 +    query.addWhere('"unit"."parent_id" ISNULL');
    7.72 +  }
    7.73 +  if (params.unit_disabled) {
    7.74 +    query.addWhere('"unit"."active" = FALSE');
    7.75 +  } else {
    7.76 +    query.addWhere('"unit"."active" = TRUE');
    7.77 +  }
    7.78 +  if (params.unit_order_by_name) {
    7.79 +    query.addOrderBy('"unit"."name"')
    7.80 +  }
    7.81 +}
    7.82 +
    7.83 +// add area related options to a db query according to parameters given by client
    7.84 +exports.addAreaOptions = function (req, query, params) {
    7.85 +  exports.addUnitOptions(req, query, params);
    7.86 +  if (params.area_id) {
    7.87 +    query.addWhere(['"area"."id" IN (??)', params.area_id.split(',')]);
    7.88 +  }
    7.89 +  if (params.area_disabled) {
    7.90 +    query.addWhere('"area"."active" = FALSE');
    7.91 +  } else {
    7.92 +    query.addWhere('"area"."active" = TRUE');
    7.93 +  }
    7.94 +  if (req.current_access_level == 'member' && params.area_my) {
    7.95 +    query.addWhere(['"area"."id" IN (SELECT "area_id" FROM "membership" WHERE "member_id" = ?)', req.current_member_id]);
    7.96 +  }
    7.97 +  if (params.area_order_by_name) {
    7.98 +    query.addOrderBy('"area"."name"')
    7.99 +  }
   7.100 +}
   7.101 +
   7.102 +// add issue related options to a db query according to parameters given by client
   7.103 +exports.addIssueOptions = function (req, query, params) {
   7.104 +  exports.addAreaOptions(req, query, params);
   7.105 +  exports.addPolicyOptions(req, query, params);
   7.106 +  
   7.107 +  if (params.issue_id) query.addWhere(['issue.id IN (??)', params.issue_id.split(',')]);
   7.108 +
   7.109 +  if (params.issue_state) {
   7.110 +    var issue_state_string;
   7.111 +    if (params.issue_state == 'open') {
   7.112 +      issue_states = ['admission', 'discussion', 'verification', 'voting'];
   7.113 +    } else if (params.issue_state == 'closed') {
   7.114 +      issue_state_string = ['canceled_revoked_before_accepted', 'canceled_issue_not_accepted', 'canceled_after_revocation_during_discussion', 'canceled_after_revocation_during_verification', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner'];
   7.115 +    } else {
   7.116 +      issue_states = params.issue_state.split(',');
   7.117 +    }
   7.118 +    query.addWhere('"issue"."state" IN (??)', issue_states);
   7.119 +  };
   7.120 +  
   7.121 +  if (params.issue_accepted) query.addWhere('"issue"."accepted" NOTNULL');
   7.122 +  if (params.issue_half_frozen) query.addWhere('"issue"."half_frozen" NOTNULL');
   7.123 +  if (params.issue_fully_frozen) query.addWhere('"issue"."fully_frozen" NOTNULL');
   7.124 +  if (params.issue_closed) query.addWhere('"issue"."closed" NOTNULL');
   7.125 +  if (params.issue_cleaned) query.addWhere('"issue"."cleaned" NOTNULL');
   7.126 +  
   7.127 +  if (params.issue_created_after) query.addWhere(['"issue"."created" >= ?', params.issue_created_after]);
   7.128 +  if (params.issue_created_before) query.addWhere(['"issue"."created" < ?', params.issue_created_before]);
   7.129 +  if (params.issue_accepted_after) query.addWhere(['"issue"."accepted" >= ?', params.issue_accepted_after]);
   7.130 +  if (params.issue_accepted_before) query.addWhere(['"issue"."accepted" < ?', params.issue_accepted_before]);
   7.131 +  if (params.issue_half_frozen_after) query.addWhere(['"issue"."half_frozen" >= ?', params.issue_half_frozen_after]);
   7.132 +  if (params.issue_half_frozen_before) query.addWhere(['"issue"."half_frozen" < ?', params.issue_half_frozen_before]);
   7.133 +  if (params.issue_fully_frozen_after) query.addWhere(['"issue"."fully_frozen" >= ?', params.issue_fully_frozen_after]);
   7.134 +  if (params.issue_fully_frozen_before) query.addWhere(['"issue"."fully_frozen" < ?', params.issue_fully_frozen_before]);
   7.135 +  if (params.issue_closed_after) query.addWhere(['"issue"."closed" >= ?', params.issue_closed_after]);
   7.136 +  if (params.issue_closed_before) query.addWhere(['"issue"."closed" < ?', params.issue_closed_before]);
   7.137 +  if (params.issue_cleaned_after) query.addWhere(['"issue."cleaned" >= ?', params.issue_cleaned_after]);
   7.138 +  if (params.issue_cleaned_before) query.addWhere(['"issue"."cleaned" < ?', params.issue_cleaned_before]);
   7.139 +  
   7.140 +  if (params.issue_state_time_left_below) {
   7.141 +    query.addWhere(['(case issue.state when \'admission\' then issue.created + issue.admission_time when \'discussion\' then issue.accepted + issue.discussion_time when \'verification\' then issue.half_frozen + issue.verification_time when \'voting\' then issue.fully_frozen + issue.voting_time end) - now() < ?', params.issue_state_time_left_below]);
   7.142 +  };
   7.143 +  
   7.144 +  if (params.issue_order_by_id) query.addOrderBy('"issue"."id"');
   7.145 +}
   7.146 +
   7.147 +// add initiative related options to a db query according to parameters given by client
   7.148 +exports.addInitiativeOptions = function (req, query, params) {
   7.149 +  exports.addIssueOptions(req, query, params);
   7.150 +  if (params.initiative_id) {
   7.151 +    query.addWhere(['initiative.id IN (??)', params.initiative_id.split(',')]);
   7.152 +  }
   7.153 +  // TODO
   7.154 +  //query.from.push('JOIN initiator AS initiative_initiator ON initiative_initiator.initiative_id = initiative.id AND initiative_initiator.accepted JOIN member AS initiator_member ON initiator_member.id = initiative_initiator.member_id');  
   7.155 +  //query.from.push('JOIN supporter AS initiative_supporter ON initiative_supporter.initiative_id = initiative.id JOIN member AS supporter_member ON supporter_member.id = initiative_supporter.member_id');  
   7.156 +  //exports.addMemberOptions(query, params, 'initiator');
   7.157 +  //exports.addMemberOptions(query, params, 'supporter');
   7.158 +  
   7.159 +  if (params.initiative_revoked) query.addWhere('initiative.revoked NOTNULL');
   7.160 +  if (params.initiative_revoked_after) query.addWhere(['initiative.revoked >= ?', params.initiative_revoked_after]);
   7.161 +  if (params.initiative_revoked_before) query.addWhere(['initiative.revoked < ?', params.initiative_revoked_before]);
   7.162 +  if (params.initiative_revoked_by_member_id) query.addWhere(['initiative.revoked_by_member_id = ?', params.initiative_revoked_by_member_id]);
   7.163 +  if (params.initiative_suggested_initiative_id) query.addWhere(['initiative.suggested_initiative_id = ?', params.initiative_suggested_initiative_id]);
   7.164 +
   7.165 +  if (params.initiative_admitted) query.addWhere('initiative.admitted NOTNULL');
   7.166 +  if (params.initiative_created_after) query.addWhere(['initiative.created >= ?', params.initiative_created_after]);
   7.167 +  if (params.initiative_created_before) query.addWhere(['initiative.created < ?',params.initiative_created_before]);
   7.168 +  if (params.initiative_admitted_after) query.addWhere(['initiative.admitted >= ?', params.initiative_admitted_after]);
   7.169 +  if (params.initiative_admitted_before) query.addWhere(['initiative.admitted < ?', params.initiative_admitted_before]);
   7.170 +
   7.171 +  if (params.initiative_supporter_count_below) query.addWhere(['initiative.supporter_count < ?', params.initiative_supporter_count_below]);
   7.172 +  if (params.initiative_supporter_count_above) query.addWhere(['initiative.supporter_count >= ?', params.initiative_supporter_count_above]);
   7.173 +
   7.174 +  if (params.initiative_attainable) query.addWhere('initiative.attainable');
   7.175 +  if (params.initiative_favored) query.addWhere('initiative.favored');
   7.176 +  if (params.initiative_unfavored) query.addWhere('initiative.unfavored');
   7.177 +
   7.178 +  if (params.initiative_max_preliminary_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_preliminary_rank]);
   7.179 +  if (params.initiative_max_final_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_final_rank]);
   7.180 +
   7.181 +  if (params.initiative_disqualified) query.addWhere('initiative.disqualified');
   7.182 +  if (params.initiative_winner) query.addWhere('initiative.winner');
   7.183 +
   7.184 +  if (params.initiative_search) {
   7.185 +    query.addWhere(['initiative.text_search_data @@ text_search_query(?)', params.initiative_search]);
   7.186 +  };
   7.187 +  if (params.initiative_order_by_id) {
   7.188 +    query.addOrderBy('initiative.id');
   7.189 +  }
   7.190 +}
   7.191 +
   7.192 +// add suggestion related options to a db query according to parameters given by client
   7.193 +exports.addSuggestionOptions = function (req, query, params) {
   7.194 +  exports.addInitiativeOptions(req, query, params);
   7.195 +  if (params.suggestion_id) {
   7.196 +    query.addWhere(['suggestion.id IN (??)', params.suggestion_id]);
   7.197 +  }
   7.198 +  if (params.suggestion_search) {
   7.199 +    query.addWhere(['suggestion.text_search_data @@ text_search_query(?)', params.suggestion_search]);
   7.200 +  };
   7.201 +  if (params.include_suggestion) {
   7.202 +    addObjectFields(query, 'suggestion');
   7.203 +  };
   7.204 +}
     8.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     8.2 +++ b/lfapi/main.js	Sat Sep 10 23:31:20 2011 +0200
     8.3 @@ -0,0 +1,1808 @@
     8.4 +var api_version = '0.2.0';
     8.5 +
     8.6 +// creates a random string with the given length
     8.7 +function randomString(number_of_chars) {
     8.8 +  var charset, rand, i, ret;
     8.9 +  charset = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    8.10 +  random_string = '';
    8.11 +
    8.12 +  for (var i = 0; i < number_of_chars; i++) {
    8.13 +    random_string += charset[parseInt(Math.random() * charset.length)]
    8.14 +  }
    8.15 +  return random_string;
    8.16 +}
    8.17 +
    8.18 +var fields = require('./fields.js');
    8.19 +
    8.20 +var general_params = require('./general_params.js');
    8.21 +
    8.22 +var config = general_params.config;
    8.23 +exports.config = config;
    8.24 +
    8.25 +var db = require('./db.js');
    8.26 +exports.db = db;
    8.27 +
    8.28 +var selector = db.selector;
    8.29 +
    8.30 +var email = require('mailer');
    8.31 +
    8.32 +
    8.33 +// check if current session has at least given access level, returns error to client if not.
    8.34 +// used by request handlers below
    8.35 +function requireAccessLevel(conn, req, res, access_level, callback) {
    8.36 +  switch (access_level) {
    8.37 +    case 'anonymous':
    8.38 +      if (req.current_access_level == 'anonymous') { callback(); return; };
    8.39 +    case 'pseudonym':
    8.40 +      if (req.current_access_level == 'pseudonym') { callback(); return; };
    8.41 +    case 'full':
    8.42 +      if (req.current_access_level == 'full') { callback(); return; };
    8.43 +    case 'member':
    8.44 +      if (req.current_member_id) { callback(); return; };
    8.45 +    default:
    8.46 +      respond('json', conn, req, res, 'forbidden', { error: 'Access denied' });
    8.47 +  }
    8.48 +};
    8.49 +
    8.50 +// callback function, encoding result and sending it to the client
    8.51 +function respond(mode, conn, req, res, status, object, err) {
    8.52 +  var http_status = 500;
    8.53 +  var command;
    8.54 +
    8.55 +  if (status == 'ok') {
    8.56 +    command = 'COMMIT';
    8.57 +  } else {
    8.58 +    command = 'ROLLBACK';
    8.59 +  };
    8.60 +  
    8.61 +  switch (status) {
    8.62 +    case 'ok':
    8.63 +      http_status = 200;
    8.64 +      break;
    8.65 +    case 'forbidden':
    8.66 +      //http_status = 403;
    8.67 +      break;
    8.68 +    case 'notfound':
    8.69 +      http_status = 404;
    8.70 +      break;
    8.71 +    case 'unprocessable':
    8.72 +      //http_status = 422;
    8.73 +      break;
    8.74 +    case 'conflict':
    8.75 +      //http_status = 409;
    8.76 +      break;
    8.77 +  };
    8.78 +  
    8.79 +  var query;
    8.80 +  if (mode == 'json' && ! err) query = 'SELECT null';
    8.81 +  db.query(conn, req, res, query, function(result, conn) {
    8.82 +    db.query(conn, req, res, command, function (result, conn) {
    8.83 +      
    8.84 +      if (conn && typeof(conn) != 'string') conn.drain();
    8.85 +
    8.86 +      if (mode == 'json') {
    8.87 +        if (! object) object = {};
    8.88 +      } else if (mode == 'html') {
    8.89 +        if (! object) object = 'no content';
    8.90 +        if (err) object = "Error: " + err;
    8.91 +      }
    8.92 +             
    8.93 +      object.status = status;
    8.94 +      object.error = err;
    8.95 +
    8.96 +      if (mode == 'json') {
    8.97 +        var body = JSON.stringify(object);
    8.98 +        var content_type = 'application/json';
    8.99 +        if (req.params && req.params.callback) {
   8.100 +          body = req.params.callback + '(' + body + ')';
   8.101 +          content_type = 'text/javascript';
   8.102 +        }
   8.103 +        res.writeHead(
   8.104 +          http_status, 
   8.105 +          {
   8.106 +            'Content-Type': content_type,
   8.107 +            //'Content-Length': body.length
   8.108 +          }
   8.109 +        );
   8.110 +        res.end(body);
   8.111 +      } else if (mode == 'html') {
   8.112 +        var body = ['<html><head><title>lfapi</title><style>body { font-family: sans-serif; }</style></head><body>']
   8.113 +        body.push(object)
   8.114 +        body.push('</body></html>')
   8.115 +        body = body.join('');
   8.116 +        res.writeHead(
   8.117 +          http_status, 
   8.118 +          {
   8.119 +            'Content-Type': 'text/html',
   8.120 +            'Content-Length': body.length
   8.121 +          }
   8.122 +        );
   8.123 +        res.end(body);
   8.124 +      }
   8.125 +    })
   8.126 +  });
   8.127 +};
   8.128 +
   8.129 +exports.respond = respond;
   8.130 +db.error_handler = respond;
   8.131 +
   8.132 +// add requested related data for requests with include_* parameters
   8.133 +function addRelatedData(conn, req, res, result, includes) {
   8.134 +  if (includes.length > 0) {
   8.135 +    var include = includes.shift();
   8.136 +    var class = include.class;
   8.137 +    var objects = result[include.objects];
   8.138 +
   8.139 +    var query;
   8.140 +
   8.141 +    if (objects) {
   8.142 +      var objects_exists = false;
   8.143 +      query = new selector.Selector();
   8.144 +      var ids_hash = {};
   8.145 +      if (typeof(objects) == 'array') {
   8.146 +        if (objects.length > 0) {
   8.147 +          objects_exists = true;
   8.148 +          objects.forEach( function(object) {
   8.149 +            if (object[class + "_id"]) {
   8.150 +              ids_hash[object[class + "_id"]] = true;
   8.151 +            };
   8.152 +          });
   8.153 +        }
   8.154 +      } else {
   8.155 +        for (var key in objects) {
   8.156 +          objects_exists = true;
   8.157 +          var object = objects[key];
   8.158 +          if (object[class + "_id"]) {
   8.159 +            ids_hash[object[class + "_id"]] = true;
   8.160 +          };
   8.161 +        };
   8.162 +      };
   8.163 +      
   8.164 +      if (objects_exists) {
   8.165 +        var ids = [];
   8.166 +        for (key in ids_hash) {
   8.167 +          ids.push(key)
   8.168 +        }
   8.169 +
   8.170 +        query.from(class);
   8.171 +        query.addWhere([class + '.id IN (??)', ids]);
   8.172 +        fields.addObjectFields(query, class);
   8.173 +      };
   8.174 +    };
   8.175 +
   8.176 +    db.query(conn, req, res, query, function (result2, conn) {
   8.177 +      // add result to main result, regarding correct pluralization
   8.178 +      var tmp = {};
   8.179 +      if (result2) {
   8.180 +        result2.rows.forEach( function(row) {
   8.181 +          tmp[row.id] = row;
   8.182 +        });
   8.183 +      };
   8.184 +             
   8.185 +      if (class == 'policy') {
   8.186 +        result['policies'] = tmp;
   8.187 +      } else {
   8.188 +        result[class + 's'] = tmp;
   8.189 +      }
   8.190 +      addRelatedData(conn, req, res, result, includes);
   8.191 +    });
   8.192 +  } else {
   8.193 +    respond('json', conn, req, res, 'ok', result);
   8.194 +  };
   8.195 +    
   8.196 +};
   8.197 +
   8.198 +function lockMemberById(conn, req, res, member_id, callback) {
   8.199 +  var query = new selector.Selector('member');
   8.200 +  query.addField('NULL');
   8.201 +  query.addWhere(['member.id = ?', member_id]);
   8.202 +  query.forUpdate();
   8.203 +  db.query(conn, req, res, query, callback);
   8.204 +};
   8.205 +
   8.206 +function requireUnitPrivilege(conn, req, res, unit_id, callback) {
   8.207 +  var query = new selector.Selector('privilege');
   8.208 +  query.addField('NULL');
   8.209 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   8.210 +  query.addWhere(['privilege.unit_id = ?', unit_id ]);
   8.211 +  query.addWhere('privilege.voting_right');
   8.212 +  query.forShareOf('privilege');
   8.213 +  db.query(conn, req, res, query, function(result, conn) {
   8.214 +    if (result.rows.length != 1) {
   8.215 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for this unit.');
   8.216 +      return;
   8.217 +    }
   8.218 +    callback();
   8.219 +  });
   8.220 +};    
   8.221 +
   8.222 +function requireAreaPrivilege(conn, req, res, area_id, callback) {
   8.223 +  var query = new selector.Selector('privilege');
   8.224 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   8.225 +  query.addField('NULL');
   8.226 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   8.227 +  query.addWhere(['area.id = ?', area_id ]);
   8.228 +  query.addWhere('privilege.voting_right');
   8.229 +  query.forShareOf('privilege');
   8.230 +  db.query(conn, req, res, query, function(result, conn) {
   8.231 +    if (result.rows.length != 1) {
   8.232 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for areas in this unit.');
   8.233 +      return;
   8.234 +    }
   8.235 +    callback();
   8.236 +  });
   8.237 +};    
   8.238 +
   8.239 +function requireIssuePrivilege(conn, req, res, issue_id, callback) {
   8.240 +  var query = new selector.Selector('privilege');
   8.241 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   8.242 +  query.join('issue', null, 'issue.area_id = area.id');
   8.243 +  query.addField('NULL');
   8.244 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   8.245 +  query.addWhere(['issue.id = ?', issue_id ]);
   8.246 +  query.addWhere('privilege.voting_right');
   8.247 +  query.forShareOf('privilege');
   8.248 +  db.query(conn, req, res, query, function(result, conn) {
   8.249 +    if (result.rows.length != 1) {
   8.250 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for issues in this unit.');
   8.251 +      return;
   8.252 +    }
   8.253 +    callback();
   8.254 +  });
   8.255 +};    
   8.256 +
   8.257 +function requireInitiativePrivilege(conn, req, res, initiative_id, callback) {
   8.258 +  var query = new selector.Selector('privilege');
   8.259 +  query.join('area', null, 'area.unit_id = privilege.unit_id');
   8.260 +  query.join('issue', null, 'issue.area_id = area.id');
   8.261 +  query.join('initiative', null, 'initiative.issue_id = issue.id');
   8.262 +  query.addField('NULL');
   8.263 +  query.addWhere(['privilege.member_id = ?', req.current_member_id]);
   8.264 +  query.addWhere(['initiative.id = ?', initiative_id ]);
   8.265 +  query.addWhere('privilege.voting_right');
   8.266 +  query.forShareOf('privilege');
   8.267 +  db.query(conn, req, res, query, function(result, conn) {
   8.268 +    if (result.rows.length != 1) {
   8.269 +      respond('json', conn, req, res, 'forbidden', null, 'You have no voting right for initiatives in this unit.');
   8.270 +      return;
   8.271 +    }
   8.272 +    callback();
   8.273 +  });
   8.274 +};    
   8.275 +
   8.276 +function requireIssueState(conn, req, res, issue_id, required_states, callback) {
   8.277 +  var query = new selector.Selector('issue');
   8.278 +  query.addField('NULL');
   8.279 +  query.addWhere(['issue.id = ?', issue_id]);
   8.280 +  query.addWhere(['issue.state IN (??)', required_states]);
   8.281 +  query.forUpdateOf('issue');
   8.282 +  db.query(conn, req, res, query, function(result, conn) {
   8.283 +    if (result.rows.length != 1) {
   8.284 +      respond('json', conn, req, res, 'forbidden', null, 'Issue is in wrong state.');
   8.285 +      return;
   8.286 +    }
   8.287 +    callback();
   8.288 +  });
   8.289 +};
   8.290 +
   8.291 +function requireIssueStateForInitiative(conn, req, res, initiative_id, required_states, callback) {
   8.292 +  var query = new selector.Selector('issue');
   8.293 +  query.join('initiative', null, 'initiative.issue_id = issue.id');
   8.294 +  query.addField('NULL');
   8.295 +  query.addWhere(['initiative.id = ?', initiative_id]);
   8.296 +  query.addWhere(['issue.state IN (??)', required_states]);
   8.297 +  query.forUpdateOf('issue');
   8.298 +  db.query(conn, req, res, query, function(result, conn) {
   8.299 +    if (result.rows.length != 1) {
   8.300 +      respond('json', conn, req, res, 'forbidden', null, 'Issue is in wrong state.');
   8.301 +      return;
   8.302 +    }
   8.303 +    callback();
   8.304 +  });
   8.305 +}
   8.306 +
   8.307 +function requireContingentLeft(conn, req, res, is_initiative, callback) {
   8.308 +  var query = new selector.Selector('member_contingent_left');
   8.309 +  query.addField('NULL');
   8.310 +  query.addWhere(['member_contingent_left.member_id = ?', req.current_member_id]);
   8.311 +  query.addWhere('member_contingent_left.text_entries_left >= 1');
   8.312 +  if (is_initiative) {
   8.313 +    query.addWhere('member_contingent_left.initiatives_left >= 1');
   8.314 +  }
   8.315 +  db.query(conn, req, res, query, function(result, conn) {
   8.316 +    if (result.rows.length != 1) {
   8.317 +      respond('json', conn, req, res, 'forbidden', null, 'Contingent empty.');
   8.318 +      return;
   8.319 +    }
   8.320 +    callback();
   8.321 +  });
   8.322 +}
   8.323 +
   8.324 +// ==========================================================================
   8.325 +// GETT methods
   8.326 +// ==========================================================================
   8.327 +
   8.328 +
   8.329 +exports.get = {
   8.330 +
   8.331 +  // startpage (html) for users
   8.332 +  // currently used for implementing public alpha test
   8.333 +  '/': function (conn, req, res, params) {
   8.334 +    
   8.335 +    var html = [];
   8.336 +    html.push('<h2>welcome to lfapi public developer alpha test</h2>');
   8.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>');
   8.338 +    html.push('<h2>how to use</h2>');
   8.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>')
   8.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>');
   8.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>');
   8.342 +    html.push('<h2>questions and suggestions</h2>');
   8.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>');
   8.344 +    html.push('<h2>developer registration</h2>');
   8.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 />');
   8.346 +    html.push('<form action="register_test" method="POST">');
   8.347 +    html.push('<label for="name">Your name:</label> <input type="text" id="name" name="name" /> &nbsp; &nbsp; ');
   8.348 +    html.push('<label for="email">Email address:</label> <input type="text" id="email" name="email" /> &nbsp; &nbsp; ');
   8.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>');
   8.350 +    html.push('<br />');
   8.351 +    html.push('<br />');
   8.352 +    html.push('<div style="border: 2px solid #c00000; background-color: #ffa0a0; padding: 1ex;">');
   8.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>!');
   8.354 +    html.push('<br />');
   8.355 +    html.push('<br />');
   8.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 />');
   8.357 +    html.push('</div>');
   8.358 +    html.push('<br />');
   8.359 +    html.push('<input type="submit" value="Register account" />');
   8.360 +    respond('html', null, req, res, 'ok', html.join(''));
   8.361 +  },
   8.362 +  
   8.363 +  // temporary method to implement public alpha test
   8.364 +  '/register_test_confirm': function (conn, req, res, params) {
   8.365 +    var secret = params.secret;
   8.366 +
   8.367 +    var query = new selector.Selector('member');
   8.368 +    query.addField('member.id, member.notify_email_unconfirmed');
   8.369 +    query.addWhere(['member.notify_email_secret = ?', secret]);
   8.370 +    db.query(conn, req, res, query, function (result, conn) {
   8.371 +      var member = result.rows[0];
   8.372 +      if (member) {
   8.373 +        var query = new selector.SQLUpdate('member');
   8.374 +        query.addValues({
   8.375 +          notify_email: member.notify_email_unconfirmed,
   8.376 +          notify_email_secret: null,
   8.377 +          notify_email_unconfirmed: null,
   8.378 +          active: true,
   8.379 +          activated: 'now',
   8.380 +          active: true,
   8.381 +          last_activity: 'now',
   8.382 +          locked: false
   8.383 +        });
   8.384 +        query.addWhere(['id = ?', member.id]);
   8.385 +        db.query(conn, req, res, query, function (err, result) {
   8.386 +          respond('html', conn, req, res, 'ok', 'Account activated: ');
   8.387 +        });
   8.388 +      } else {
   8.389 +        respond('html', conn, req, res, 'forbidden', 'Secret not valid or already used.');
   8.390 +      }
   8.391 +    })
   8.392 +  },
   8.393 +  
   8.394 +  '/info': function (conn, req, res, params) {
   8.395 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.396 +      var query = new selector.Selector();
   8.397 +      query.from('"liquid_feedback_version"');
   8.398 +      query.addField('"liquid_feedback_version".*');
   8.399 +      db.query(conn, req, res, query, function (result, conn) {
   8.400 +        var liquid_feedback_version = result.rows[0];
   8.401 +        respond('json', conn, req, res, 'ok', {
   8.402 +          core_version: liquid_feedback_version.string,
   8.403 +          api_version: api_version,
   8.404 +          current_access_level: req.current_member_id ? 'member' : req.current_access_level,
   8.405 +          current_member_id: req.current_member_id,
   8.406 +          settings: config.settings
   8.407 +        });
   8.408 +      });
   8.409 +    });
   8.410 +  },
   8.411 +  
   8.412 +  '/member_count': function (conn, req, res, params) {
   8.413 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.414 +      var query = new selector.Selector();
   8.415 +      query.from('"member_count"');
   8.416 +      query.addField('"member_count".*');
   8.417 +      db.query(conn, req, res, query, function (result, conn) {
   8.418 +        var member_count = result.rows[0];
   8.419 +        respond('json', conn, req, res, 'ok', {
   8.420 +          member_count: member_count.total_count,
   8.421 +          member_count_calculated: member_count.calculated
   8.422 +        });
   8.423 +      });
   8.424 +    });
   8.425 +  },
   8.426 +  
   8.427 +  '/contingent': function (conn, req, res, params) {
   8.428 +    requireAccessLevel(conn, req, res, 'anonymous', function() {          
   8.429 +      var query = new selector.Selector();
   8.430 +      query.from('"contingent"');
   8.431 +      query.addField('"contingent".*');
   8.432 +      db.query(conn, req, res, query, function (result, conn) {
   8.433 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.434 +      });
   8.435 +    });
   8.436 +  },
   8.437 +  
   8.438 +  '/contingent_left': function (conn, req, res, params) {
   8.439 +    requireAccessLevel(conn, req, res, 'member', function() {          
   8.440 +      var query = new selector.Selector();
   8.441 +      query.from('"member_contingent_left"');
   8.442 +      query.addField('"member_contingent_left".text_entries_left');
   8.443 +      query.addField('"member_contingent_left".initiatives_left');
   8.444 +      query.addWhere(['member_id = ?', req.current_member_id]);
   8.445 +      db.query(conn, req, res, query, function (result, conn) {
   8.446 +        respond('json', conn, req, res, 'ok', { result: result.rows[0] });
   8.447 +      });
   8.448 +    });
   8.449 +  },
   8.450 +  
   8.451 +  '/member': function (conn, req, res, params) {
   8.452 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.453 +      var query = new selector.Selector();
   8.454 +      query.from('"member"');
   8.455 +      if (req.current_access_level == 'pseudonym' && !req.current_member_id ) {
   8.456 +        fields.addObjectFields(query, 'member', 'member_pseudonym');
   8.457 +      } else {
   8.458 +        fields.addObjectFields(query, 'member');
   8.459 +      }
   8.460 +      general_params.addMemberOptions(req, query, params);
   8.461 +      query.addOrderBy('"member"."id"');
   8.462 +      general_params.addLimitAndOffset(query, params);
   8.463 +      db.query(conn, req, res, query, function (result, conn) {
   8.464 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.465 +      });
   8.466 +    });
   8.467 +  },
   8.468 +  
   8.469 +  '/member_history': function (conn, req, res, params) {
   8.470 +    requireAccessLevel(conn, req, res, 'full', function() {
   8.471 +      var query = new selector.Selector();
   8.472 +      query.from('"member_history" JOIN "member" ON "member"."id" = "member_history"."member_id"');
   8.473 +      query.addField('"member_history".*');
   8.474 +      general_params.addMemberOptions(req, query, params);
   8.475 +      query.addOrderBy('member_history.id');
   8.476 +      general_params.addLimitAndOffset(query, params);
   8.477 +      db.query(conn, req, res, query, function (member_history_result, conn) {
   8.478 +        var result = { result: member_history_result.rows }
   8.479 +        includes = [];
   8.480 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   8.481 +        addRelatedData(conn, req, res, result, includes);
   8.482 +      });
   8.483 +    });
   8.484 +  },
   8.485 +  
   8.486 +  '/member_image': function (conn, req, res, params) {
   8.487 +    requireAccessLevel(conn, req, res, 'full', function() {
   8.488 +      var query = new selector.Selector();
   8.489 +      query.from('"member_image" JOIN "member" ON "member"."id" = "member_image"."member_id"');
   8.490 +      query.addField('"member_image".*');
   8.491 +      query.addWhere('member_image.scaled');
   8.492 +      general_params.addMemberOptions(req, query, params);
   8.493 +      query.addOrderBy = ['member_image.member_id, member_image.image_type'];
   8.494 +      db.query(conn, req, res, query, function (result, conn) {
   8.495 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.496 +      });
   8.497 +    });
   8.498 +  },
   8.499 +  
   8.500 +  '/contact': function (conn, req, res, params) {
   8.501 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.502 +      var query = new selector.Selector();
   8.503 +      query.from('contact JOIN member ON member.id = contact.member_id');
   8.504 +      query.addField('"contact".*');
   8.505 +      if (req.current_member_id) {
   8.506 +        // public or own for members
   8.507 +        query.addWhere(['"contact"."public" OR "contact"."member_id" = ?', req.current_member_id]);
   8.508 +      } else {
   8.509 +        // public for everybody
   8.510 +        query.addWhere('"contact"."public"');
   8.511 +      }
   8.512 +      general_params.addMemberOptions(req, query, params);
   8.513 +      query.addOrderBy('"contact"."id"');
   8.514 +      general_params.addLimitAndOffset(query, params);
   8.515 +      db.query(conn, req, res, query, function (result, conn) {
   8.516 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.517 +      });
   8.518 +    });
   8.519 +  },
   8.520 +
   8.521 +  '/privilege': function (conn, req, res, params) {
   8.522 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.523 +      var query = new selector.Selector();
   8.524 +      query.from('privilege JOIN member ON member.id = privilege.member_id JOIN unit ON unit.id = privilege.unit_id');
   8.525 +      query.addField('privilege.*');
   8.526 +      general_params.addUnitOptions(req, query, params);
   8.527 +      general_params.addMemberOptions(req, query, params);
   8.528 +      query.addOrderBy('privilege.unit_id, privilege.member_id');
   8.529 +      general_params.addLimitAndOffset(query, params);
   8.530 +      db.query(conn, req, res, query, function (privilege_result, conn) {
   8.531 +        var result = { result: privilege_result.rows }
   8.532 +        includes = [];
   8.533 +        if (params.include_units) includes.push({ class: 'unit', objects: 'result'});
   8.534 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   8.535 +        addRelatedData(conn, req, res, result, includes);
   8.536 +      });
   8.537 +    });
   8.538 +  },
   8.539 +  
   8.540 +  '/policy': function (conn, req, res, params) {
   8.541 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.542 +      var query = new selector.Selector();
   8.543 +      query.from('"policy"');
   8.544 +      query.addField('"policy".*');
   8.545 +      general_params.addPolicyOptions(req, query, params);
   8.546 +      query.addOrderBy('"policy"."index"');
   8.547 +      general_params.addLimitAndOffset(query, params);
   8.548 +      db.query(conn, req, res, query, function (result, conn) {
   8.549 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.550 +      });
   8.551 +    });
   8.552 +  },
   8.553 +  
   8.554 +  '/unit': function (conn, req, res, params) {
   8.555 +    requireAccessLevel(conn, req, res, 'anonymous', function() {          
   8.556 +      var query = new selector.Selector();
   8.557 +      query.from('"unit"');
   8.558 +      fields.addObjectFields(query, 'unit');
   8.559 +      general_params.addUnitOptions(req, query, params);
   8.560 +      query.addOrderBy('unit.id');
   8.561 +      general_params.addLimitAndOffset(query, params);
   8.562 +      db.query(conn, req, res, query, function (result, conn) {
   8.563 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.564 +      });
   8.565 +    });
   8.566 +  },
   8.567 +  
   8.568 +  '/area': function (conn, req, res, params) {
   8.569 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.570 +      var query = new selector.Selector();
   8.571 +      query.from('area JOIN unit ON area.unit_id = unit.id');
   8.572 +      fields.addObjectFields(query, 'area');
   8.573 +      general_params.addAreaOptions(req, query, params);
   8.574 +      query.addOrderBy('area.id');
   8.575 +      general_params.addLimitAndOffset(query, params);
   8.576 +      db.query(conn, req, res, query, function (area_result, conn) {
   8.577 +        var result = { result: area_result.rows }
   8.578 +        includes = [];
   8.579 +        if (params.include_units) includes.push({ class: 'unit', objects: 'result'});
   8.580 +        addRelatedData(conn, req, res, result, includes);
   8.581 +      });
   8.582 +    });
   8.583 +  },
   8.584 +  
   8.585 +  '/allowed_policy': function (conn, req, res, params) {
   8.586 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.587 +    var query = new selector.Selector();
   8.588 +    query.from('allowed_policy');
   8.589 +    query.join('area', null, 'area.id = allowed_policy.area_id');
   8.590 +    query.join('unit', null, 'unit.id = area.unit_id');
   8.591 +    query.addField('allowed_policy.*');
   8.592 +    general_params.addAreaOptions(req, query, params);
   8.593 +    query.addOrderBy('allowed_policy.area_id, allowed_policy.policy_id');
   8.594 +    general_params.addLimitAndOffset(query, params);
   8.595 +    db.query(conn, req, res, query, function (allowed_policy_result, conn) {
   8.596 +      var result = { result: allowed_policy_result.rows }
   8.597 +      includes = [];
   8.598 +      if (params.include_policies) includes.push({ class: 'policy', objects: 'result'});
   8.599 +      if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   8.600 +      if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.601 +      addRelatedData(conn, req, res, result, includes);
   8.602 +    });
   8.603 +  }); },
   8.604 +  
   8.605 +  '/membership': function (conn, req, res, params) {
   8.606 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.607 +      var query = new selector.Selector();
   8.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');
   8.609 +      query.addField('membership.*');
   8.610 +      general_params.addAreaOptions(req, query, params);
   8.611 +      general_params.addMemberOptions(req, query, params);
   8.612 +      query.addOrderBy('membership.area_id, membership.member_id');
   8.613 +      general_params.addLimitAndOffset(query, params);
   8.614 +      db.query(conn, req, res, query, function (membership_result, conn) {
   8.615 +        var result = { result: membership_result.rows }
   8.616 +        includes = [];
   8.617 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   8.618 +        if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   8.619 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.620 +        addRelatedData(conn, req, res, result, includes);
   8.621 +      });
   8.622 +    });
   8.623 +  },
   8.624 +  
   8.625 +  '/issue': function (conn, req, res, params) {
   8.626 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.627 +      var query = new selector.Selector()
   8.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');
   8.629 +      fields.addObjectFields(query, 'issue');
   8.630 +      general_params.addIssueOptions(req, query, params);
   8.631 +      query.addOrderBy('issue.id');
   8.632 +      general_params.addLimitAndOffset(query, params);
   8.633 +      db.query(conn, req, res, query, function (issue_result, conn) {
   8.634 +        var result = { result: issue_result.rows }
   8.635 +        includes = [];
   8.636 +        if (params.include_areas) includes.push({ class: 'area', objects: 'result'});
   8.637 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.638 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'result' });
   8.639 +        addRelatedData(conn, req, res, result, includes);
   8.640 +      });
   8.641 +    });
   8.642 +  },
   8.643 +
   8.644 +  '/interest': function (conn, req, res, params) {
   8.645 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.646 +      var query = new selector.Selector();
   8.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');
   8.648 +      query.addField('interest.*');
   8.649 +      general_params.addMemberOptions(req, query, params);
   8.650 +      general_params.addIssueOptions(req, query, params);
   8.651 +      query.addOrderBy('interest.issue_id, interest.member_id');
   8.652 +      general_params.addLimitAndOffset(query, params);
   8.653 +      db.query(conn, req, res, query, function (interest_result, conn) {
   8.654 +        var result = { result: interest_result.rows }
   8.655 +        includes = [];
   8.656 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   8.657 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   8.658 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.659 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.660 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.661 +        addRelatedData(conn, req, res, result, includes);
   8.662 +      });
   8.663 +    });
   8.664 +  },
   8.665 +  
   8.666 +  '/issue_comment': function (conn, req, res, params) {
   8.667 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.668 +      var query = new selector.Selector();
   8.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');
   8.670 +      query.addField('issue_comment.*');
   8.671 +      general_params.addMemberOptions(req, query, params);
   8.672 +      general_params.addIssueOptions(req, query, params);
   8.673 +      query.addOrderBy('issue_comment.issue_id, issue_comment.member_id');
   8.674 +      general_params.addLimitAndOffset(query, params);
   8.675 +      db.query(conn, req, res, query, function (issue_comment_result, conn) {
   8.676 +        var result = { result: issue_comment_result.rows }
   8.677 +        includes = [];
   8.678 +        if (params.include_members) includes.push({ class: 'member', objects: 'result'});
   8.679 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   8.680 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.681 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.682 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.683 +        addRelatedData(conn, req, res, result, includes);
   8.684 +      });
   8.685 +    });
   8.686 +  },
   8.687 +  
   8.688 +  '/initiative': function (conn, req, res, params) {
   8.689 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.690 +      var query = new selector.Selector();
   8.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');
   8.692 +      fields.addObjectFields(query, 'initiative');
   8.693 +      general_params.addInitiativeOptions(req, query, params);
   8.694 +      query.addOrderBy('initiative.issue_id, initiative.id');
   8.695 +      general_params.addLimitAndOffset(query, params);
   8.696 +      db.query(conn, req, res, query, function (initiative_result, conn) {
   8.697 +        var result = { result: initiative_result.rows }
   8.698 +        includes = [];
   8.699 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   8.700 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.701 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.702 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.703 +        addRelatedData(conn, req, res, result, includes);
   8.704 +      });
   8.705 +    });
   8.706 +  },
   8.707 +
   8.708 +  '/initiator': function (conn, req, res, params) {
   8.709 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.710 +      var fields = ['initiator.initiative_id', 'initiator.member_id'];
   8.711 +      var query = new selector.Selector();
   8.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');
   8.713 +      query.addWhere('initiator.accepted');
   8.714 +      fields.forEach( function(field) {
   8.715 +        query.addField(field, null, ['grouped']);
   8.716 +      });
   8.717 +      general_params.addMemberOptions(req, query, params);
   8.718 +      general_params.addInitiativeOptions(req, query, params);
   8.719 +      query.addOrderBy('initiator.initiative_id, initiator.member_id');
   8.720 +      general_params.addLimitAndOffset(query, params);
   8.721 +      db.query(conn, req, res, query, function (initiator, conn) {
   8.722 +        var result = { result: initiator.rows }
   8.723 +        includes = [];
   8.724 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.725 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.726 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.727 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.728 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.729 +        addRelatedData(conn, req, res, result, includes);
   8.730 +      });
   8.731 +    });
   8.732 +  },
   8.733 +
   8.734 +
   8.735 +  '/supporter': function (conn, req, res, params) {
   8.736 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.737 +      var fields = ['supporter.issue_id', 'supporter.initiative_id', 'supporter.member_id', 'supporter.draft_id'];
   8.738 +      var query = new selector.Selector();
   8.739 +      query.from('supporter')
   8.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');
   8.741 +      fields.forEach( function(field) {
   8.742 +        query.addField(field, null, ['grouped']);
   8.743 +      });
   8.744 +      general_params.addMemberOptions(req, query, params);
   8.745 +      general_params.addInitiativeOptions(req, query, params);
   8.746 +      query.addOrderBy('supporter.issue_id, supporter.initiative_id, supporter.member_id');
   8.747 +      general_params.addLimitAndOffset(query, params);
   8.748 +      db.query(conn, req, res, query, function (supporter, conn) {
   8.749 +        var result = { result: supporter.rows }
   8.750 +        includes = [];
   8.751 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.752 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.753 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.754 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.755 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.756 +        addRelatedData(conn, req, res, result, includes);
   8.757 +      });
   8.758 +    });
   8.759 +  },
   8.760 +  
   8.761 +  '/battle': function (conn, req, res, params) {
   8.762 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.763 +      var query = new selector.Selector();
   8.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');
   8.765 +      query.addField('battle.*');
   8.766 +      general_params.addInitiativeOptions(req, query, params);
   8.767 +      query.addOrderBy('battle.issue_id, battle.winning_initiative_id, battle.losing_initiative_id');
   8.768 +      general_params.addLimitAndOffset(query, params);
   8.769 +      db.query(conn, req, res, query, function (result, conn) {
   8.770 +        var result = { result: result.rows }
   8.771 +        includes = [];
   8.772 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.773 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.774 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.775 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.776 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.777 +        addRelatedData(conn, req, res, result, includes);
   8.778 +      });
   8.779 +    });
   8.780 +  },
   8.781 +  
   8.782 +  '/draft': function (conn, req, res, params) {
   8.783 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.784 +      var fields = ['draft.initiative_id', 'draft.id', 'draft.formatting_engine', 'draft.content', 'draft.author_id'];
   8.785 +      var query = new selector.Selector();
   8.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');
   8.787 +      fields.forEach( function(field) {
   8.788 +        query.addField(field, null, ['grouped']);
   8.789 +      });
   8.790 +      if (req.current_access_level != 'anonymous' || req.current_member_id) {
   8.791 +        query.addField('draft.author_id');
   8.792 +      }
   8.793 +      if (params.draft_id) {
   8.794 +        query.addWhere('draft.id = ?', params.draft_id);
   8.795 +      }
   8.796 +      if (params.current_draft) {
   8.797 +        query.join('current_draft', null, 'current_draft.initiative_id = initiative.id AND current_draft.id = draft.id')
   8.798 +      }
   8.799 +      general_params.addInitiativeOptions(req, query, params);
   8.800 +      query.addOrderBy('draft.initiative_id, draft.id');
   8.801 +      general_params.addLimitAndOffset(query, params);
   8.802 +      db.query(conn, req, res, query, function (result, conn) {
   8.803 +        var result = { result: result.rows }
   8.804 +        includes = [];
   8.805 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.806 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.807 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.808 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.809 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.810 +        addRelatedData(conn, req, res, result, includes);
   8.811 +      });
   8.812 +    });
   8.813 +  },
   8.814 +  
   8.815 +  '/suggestion': function (conn, req, res, params) {
   8.816 +    requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.817 +      var query = new selector.Selector();
   8.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');
   8.819 +      if (req.current_access_level == 'anonymous' && !req.current_member_id ) {
   8.820 +        fields.addObjectFields(query, 'suggestion', 'suggestion_pseudonym');
   8.821 +      } else {
   8.822 +        fields.addObjectFields(query, 'suggestion');
   8.823 +      }
   8.824 +      general_params.addSuggestionOptions(req, query, params);
   8.825 +      query.addOrderBy('suggestion.initiative_id, suggestion.id');
   8.826 +      general_params.addLimitAndOffset(query, params);
   8.827 +      db.query(conn, req, res, query, function (result, conn) {
   8.828 +        var result = { result: result.rows }
   8.829 +        includes = [];
   8.830 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.831 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.832 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.833 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.834 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.835 +        addRelatedData(conn, req, res, result, includes);
   8.836 +      });
   8.837 +    });
   8.838 +  },
   8.839 +    
   8.840 +  '/opinion': function (conn, req, res, params) {
   8.841 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.842 +      var fields = ['opinion.initiative_id', 'opinion.suggestion_id', 'opinion.member_id', 'opinion.degree', 'opinion.fulfilled']
   8.843 +      var query = new selector.Selector();
   8.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');
   8.845 +      fields.forEach( function(field) {
   8.846 +        query.addField(field, null, ['grouped']);
   8.847 +      });
   8.848 +      general_params.addMemberOptions(req, query, params);
   8.849 +      general_params.addSuggestionOptions(req, query, params);
   8.850 +      query.addOrderBy = ['opinion.initiative_id, opinion.suggestion_id, opinion.member_id'];
   8.851 +      general_params.addLimitAndOffset(query, params);
   8.852 +      db.query(conn, req, res, query, function (result, conn) {
   8.853 +        var result = { result: result.rows }
   8.854 +        includes = [];
   8.855 +        if (params.include_suggestions) includes.push({ class: 'suggestion', objects: 'result'});
   8.856 +        if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'suggestions'});
   8.857 +        if (params.include_issues) includes.push({ class: 'issue', objects: 'initiatives'});
   8.858 +        if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.859 +        if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.860 +        if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.861 +        addRelatedData(conn, req, res, result, includes);
   8.862 +      });
   8.863 +    });
   8.864 +  },
   8.865 +  
   8.866 +  '/delegation': function (conn, req, res, params) {
   8.867 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.868 +      var fields = ['delegation.id', 'delegation.truster_id', 'delegation.trustee_id', 'delegation.scope', 'delegation.area_id', 'delegation.issue_id', 'delegation.unit_id'];
   8.869 +      var query = new selector.Selector();
   8.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');
   8.871 +      fields.forEach( function(field) {
   8.872 +        query.addField(field, null, ['grouped']);
   8.873 +      });
   8.874 +      if (params.direction) {
   8.875 +        switch (params.direction) {
   8.876 +          case 'in':
   8.877 +            query.join('member', null, 'member.id = delegation.trustee_id');
   8.878 +            break;
   8.879 +          case 'out':
   8.880 +            query.join('member', null, 'member.id = delegation.truster_id');
   8.881 +            break;
   8.882 +          default:
   8.883 +            respond('json', conn, req, res, 'unprocessable', 'Direction must be "in" or "out" if set.');
   8.884 +        }
   8.885 +      } else {
   8.886 +        query.join('member', null, 'member.id = delegation.truster_id OR member.id = delegation.trustee_id');
   8.887 +      }
   8.888 +      general_params.addMemberOptions(req, query, params);
   8.889 +      general_params.addIssueOptions(req, query, params);
   8.890 +      if (params.scope) {
   8.891 +        query.addWhere(['delegation.scope IN (??)', params.scope.split(',')]);
   8.892 +      };
   8.893 +      query.addOrderBy = ['delegation.id'];
   8.894 +      general_params.addLimitAndOffset(query, params);
   8.895 +      db.query(conn, req, res, query, function (result, conn) {
   8.896 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.897 +      });
   8.898 +    });
   8.899 +  },
   8.900 +
   8.901 +  '/vote': function (conn, req, res, params) {
   8.902 +    requireAccessLevel(conn, req, res, 'pseudonym', function() {
   8.903 +      var query = new selector.Selector();
   8.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');
   8.905 +      query.addField('vote.*');
   8.906 +      query.addWhere('issue.closed_at NOTNULL');
   8.907 +      general_params.addMemberOptions(req, query, params);
   8.908 +      general_params.addInitiativeOptions(req, query, params);
   8.909 +      general_params.addLimitAndOffset(query, params);
   8.910 +      db.query(conn, req, res, query, function (result, conn) {
   8.911 +        respond('json', conn, req, res, 'ok', { result: result.rows });
   8.912 +      });
   8.913 +    });
   8.914 +  },
   8.915 +  
   8.916 +  '/event': function (conn, req, res, params) { requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.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'];
   8.918 +    var query = new selector.Selector();
   8.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');
   8.920 +    fields.forEach( function(field) {
   8.921 +      query.addField(field, null, ['grouped']);
   8.922 +    });
   8.923 +    general_params.addMemberOptions(req, query, params);
   8.924 +    general_params.addInitiativeOptions(req, query, params);
   8.925 +    query.addOrderBy('event.id');
   8.926 +    general_params.addLimitAndOffset(query, params);
   8.927 +    db.query(conn, req, res, query, function (events, conn) {
   8.928 +      var result = { result: events.rows }
   8.929 +      includes = [];
   8.930 +      if (params.include_initiatives) includes.push({ class: 'initiative', objects: 'result'});
   8.931 +      if (params.include_issues) includes.push({ class: 'issue', objects: 'result'});
   8.932 +      if (params.include_areas) includes.push({ class: 'area', objects: 'issues'});
   8.933 +      if (params.include_units) includes.push({ class: 'unit', objects: 'areas'});
   8.934 +      if (params.include_policies) includes.push({ class: 'policy', objects: 'issues' });
   8.935 +      addRelatedData(conn, req, res, result, includes);
   8.936 +    });
   8.937 +  }); },
   8.938 +  
   8.939 +  // TODO add interfaces for data structure:
   8.940 +  // event requireAccessLevel(conn, req, res, 'member');
   8.941 +  // ignored_member requireAccessLevel(conn, req, res, 'member');
   8.942 +  // ignored_initiative requireAccessLevel(conn, req, res, 'member');
   8.943 +  // setting requireAccessLevel(conn, req, res, 'member');
   8.944 +
   8.945 +};
   8.946 +
   8.947 +// ==========================================================================
   8.948 +// POST methods
   8.949 +// ==========================================================================
   8.950 +
   8.951 +
   8.952 +
   8.953 +exports.post = {
   8.954 +  
   8.955 +  '/echo_test': function (conn, req, res, params) { requireAccessLevel(conn, req, res, 'anonymous', function() {
   8.956 +    respond('json', conn, req, res, 'ok', { result: params });
   8.957 +  }); },
   8.958 +  
   8.959 +  '/register_test': function (conn, req, res, params) {
   8.960 +    var understood = params.understood;
   8.961 +    var member_login = randomString(16);
   8.962 +    var member_name = params.name;
   8.963 +    var member_password = randomString(16);
   8.964 +    var member_notify_email = params.email;
   8.965 +    var member_notify_email_secret = randomString(24);
   8.966 +    var api_key_member = randomString(24);
   8.967 +    var api_key_full = randomString(24);
   8.968 +    var api_key_pseudonym = randomString(24);
   8.969 +    var api_key_anonymous = randomString(24);
   8.970 +    
   8.971 +    if (understood != 'understood') {
   8.972 +      respond('html', conn, req, res, 'unprocessable', null, 'You didn\'t checked the checkbox! Please hit back in your browser and try again.');
   8.973 +      return;
   8.974 +    }
   8.975 +    
   8.976 +    // add member
   8.977 +    var query = new selector.SQLInsert('member');
   8.978 +    query.addValues({ 
   8.979 +      login: member_login,
   8.980 +      password: member_password, // TODO hashing of password
   8.981 +      notify_email_unconfirmed: member_notify_email,
   8.982 +      notify_email_secret: member_notify_email_secret,
   8.983 +      name: member_name
   8.984 +    });
   8.985 +    query.addReturning('id');
   8.986 +    db.query(conn, req, res, query, function (result, conn) {
   8.987 +      var member_id = result.rows[0].id;
   8.988 +
   8.989 +      // add privilege for root unit
   8.990 +      var query = new selector.SQLInsert('privilege');
   8.991 +      query.addValues({ unit_id: 1, member_id: member_id, voting_right: true });
   8.992 +      db.query(conn, req, res, query, function (result, conn) {
   8.993 +
   8.994 +        var location = params.location;
   8.995 +        var unit_id;
   8.996 +        switch(location) {
   8.997 +          case 'earth':
   8.998 +            unit_id = 3;
   8.999 +            break;
  8.1000 +          case 'moon': 
  8.1001 +            unit_id = 4;
  8.1002 +            break;
  8.1003 +          case 'mars':
  8.1004 +            unit_id = 5;
  8.1005 +            break;
  8.1006 +        }
  8.1007 +        
  8.1008 +        // add privilege for selected planet
  8.1009 +        var query = new selector.SQLInsert('privilege');
  8.1010 +        query.addValues({ unit_id: unit_id, member_id: member_id, voting_right: true });
  8.1011 +        db.query(conn, req, res, query, function (result, conn) {
  8.1012 +
  8.1013 +          // add application key
  8.1014 +          var query = new selector.SQLInsert('member_application');
  8.1015 +          query.addValues({ 
  8.1016 +            member_id: member_id,
  8.1017 +            name: 'member',
  8.1018 +            comment: 'access_level member',
  8.1019 +            access_level: 'member',
  8.1020 +            key: api_key_member
  8.1021 +          });
  8.1022 +          query.addReturning('id');
  8.1023 +          
  8.1024 +          db.query(conn, req, res, query, function (result, conn) {
  8.1025 +
  8.1026 +            // send email to user
  8.1027 +            email.send({
  8.1028 +              host :          config.mail.smtp_host,
  8.1029 +              port:           config.mail.smtp_port,
  8.1030 +              ssl:            config.mail.smtp_ssl,
  8.1031 +              domain:         config.mail.smtp_domain,
  8.1032 +              authentication: config.mail.smtp_authentication,
  8.1033 +              username:       config.mail.smtp_username,
  8.1034 +              password:       config.mail.smtp_password,
  8.1035 +              from:           config.mail.from,
  8.1036 +              subject:        config.mail.subject_prefix + "Your LiquidFeedback API alpha test account needs confirmation",
  8.1037 +              to:             member_notify_email,
  8.1038 +              body: "\
  8.1039 +Hello " + member_name + ",\n\
  8.1040 +\n\
  8.1041 +thank you for registering at the public alpha test of the LiquidFeedback\n\
  8.1042 +application programming interface. To complete the registration process,\n\
  8.1043 +you need to confirm your email address by opening the following URL:\n\
  8.1044 +\n\
  8.1045 +" + config.public_url_path + "register_test_confirm?secret=" + member_notify_email_secret + "\n\
  8.1046 +\n\
  8.1047 +\n\
  8.1048 +After you've confirmed your email address, your account will be automatically\n\
  8.1049 +activated.\n\
  8.1050 +\n\
  8.1051 +Your account name is:     " + member_name + "\n\
  8.1052 +\n\
  8.1053 +\n\
  8.1054 +You will need the following login and password to register and unregister\n\
  8.1055 +applications for your account later. This function is currently not\n\
  8.1056 +implemented, but please keep the credentials for future use.\n\
  8.1057 +\n\
  8.1058 +Account ID:               " + member_id + "\n\
  8.1059 +Login:                    " + member_login + "\n\
  8.1060 +Password:                 " + member_password + "\n\
  8.1061 +\n\
  8.1062 +\n\
  8.1063 +To make you able to actually access the API interface, we added the following\n\
  8.1064 +application key with full member access privileges to your account:\n\
  8.1065 +\n\
  8.1066 +API Key:                  " + api_key_member + "\n\
  8.1067 +\n\
  8.1068 +\n\
  8.1069 +The base address of the public test is: " + config.public_url_path + "\n\
  8.1070 +\n\
  8.1071 +The programming interface is described in the LiquidFeedback API\n\
  8.1072 +specification: http://dev.liquidfeedback.org/trac/lf/wiki/API\n\
  8.1073 +\n\
  8.1074 +The current implementation status of lfapi is published at the LiquidFeedback\n\
  8.1075 +API server page: http://dev.liquidfeedback.org/trac/lf/wiki/lfapi\n\
  8.1076 +\n\
  8.1077 +If you have any questions or suggestions, please use our public mailing list\n\
  8.1078 +at http://dev.liquidfeedback.org/cgi-bin/mailman/listinfo/main\n\
  8.1079 +\n\
  8.1080 +For issues regarding your test account, contact us via email at\n\
  8.1081 +lqfb-maintainers@public-software-group.org\n\
  8.1082 +\n\
  8.1083 +\n\
  8.1084 +Sincerely,\n\
  8.1085 +\n\
  8.1086 +Your LiquidFeedback maintainers",
  8.1087 +            },
  8.1088 +            function(err, result){
  8.1089 +              if(err){ console.log(err); }
  8.1090 +            });        
  8.1091 +                
  8.1092 +            respond('html', conn, req, res, 'ok', 'Account created. Please check your mailbox!<br /><br /><br /><a href="/">Back to start page</a>');
  8.1093 +          });
  8.1094 +        });
  8.1095 +      });
  8.1096 +    });
  8.1097 +  },
  8.1098 +  
  8.1099 +  /*
  8.1100 +  '/register': function (conn, req, res, params) {
  8.1101 +    var invite_key = params.invite_key;
  8.1102 +    var login = params.login;
  8.1103 +    var password = params.password;
  8.1104 +    var name = params.name;
  8.1105 +    var notify_email = params.notify_email;
  8.1106 +    if (!invite_key) {
  8.1107 +      respond('json', conn, req, res, 'unprocessable', null, 'No invite_key supplied.');
  8.1108 +      return;
  8.1109 +    };
  8.1110 +    if (!login) {
  8.1111 +      respond('json', conn, req, res, 'unprocessable', null, 'No login supplied.');
  8.1112 +      return;
  8.1113 +    };
  8.1114 +    if (!password) {
  8.1115 +      respond('json', conn, req, res, 'unprocessable', null, 'No password supplied.');
  8.1116 +      return;
  8.1117 +    };
  8.1118 +    if (!name) {
  8.1119 +      respond('json', conn, req, res, 'unprocessable', null, 'No name supplied.');
  8.1120 +      return;
  8.1121 +    };
  8.1122 +    if (!notify_email) {
  8.1123 +      respond('json', conn, req, res, 'unprocessable', null, 'No notify_email supplied.');
  8.1124 +      return;
  8.1125 +    };
  8.1126 +    // check if akey is valid and get member_id for akey
  8.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) {
  8.1128 +      if (result.rows.length != 1) {
  8.1129 +        respond('json', conn, req, res, 'forbidden', null, 'Supplied invite_key is not valid.');
  8.1130 +        return;
  8.1131 +      };
  8.1132 +      var member_id = result.rows[0].id;
  8.1133 +      // check if name is available
  8.1134 +      db.query(conn, req, res, { select: ['NULL'], from: ['member'], where: ['member.name = ' + db.pgEncode(name)] }, function (result, conn) {
  8.1135 +        if (result.rows.length > 0) {
  8.1136 +          respond('json', conn, req, res, 'forbidden', null, 'Login name is not available, choose another one.');
  8.1137 +          return;
  8.1138 +        };
  8.1139 +        // check if login is available
  8.1140 +        db.query(conn, req, res, { select: ['NULL'], from: ['member'], where: ['member.login = ' + db.pgEncode(login)] }, function (result, conn) {
  8.1141 +          if (result.rows.length > 0) {
  8.1142 +            respond('json', conn, req, res, 'forbidden', null, 'Name is not available, choose another one.');
  8.1143 +            return;
  8.1144 +          };
  8.1145 +          var query = { update: 'member', set: { activation: 'now', active: true, } };
  8.1146 +          
  8.1147 +        });
  8.1148 +      });
  8.1149 +    });
  8.1150 +  },
  8.1151 +  */
  8.1152 +  
  8.1153 +  '/session': function (conn, req, res, params) {
  8.1154 +    var key = params.key;
  8.1155 +    if (!key) {
  8.1156 +      respond('json', conn, req, res, 'unprocessable', null, 'No application key supplied.');
  8.1157 +      return;
  8.1158 +    };
  8.1159 +    var query = new selector.Selector();
  8.1160 +    query.from('member');
  8.1161 +    query.join('member_application', null, 'member_application.member_id = member.id');
  8.1162 +    query.addField('member.id');
  8.1163 +    query.addWhere(['member.active AND member_application.key = ?', key]);
  8.1164 +    if (params.interactive) {
  8.1165 +      query.forUpdateOf('member');
  8.1166 +    }
  8.1167 +    db.query(conn, req, res, query, function (result, conn) {
  8.1168 +      if (result.rows.length != 1) {
  8.1169 +        respond('json', conn, req, res, 'forbidden', null, 'Supplied application key is not valid.');
  8.1170 +        return;
  8.1171 +      };
  8.1172 +      var member_id = result.rows[0].id;
  8.1173 +      var session_key = randomString(16);
  8.1174 +      req.sessions[session_key] = member_id;
  8.1175 +      var query;
  8.1176 +      if (params.interactive) {
  8.1177 +        query = new selector.SQLUpdate('member');
  8.1178 +        query.addWhere(['member.id = ?', member_id]);
  8.1179 +        query.addValues({ last_activity: 'now' });
  8.1180 +      }
  8.1181 +      db.query(conn, req, res, query, function (result, conn) {
  8.1182 +        respond('json', conn, req, res, 'ok', { session_key: session_key });
  8.1183 +      });
  8.1184 +    });
  8.1185 +  },
  8.1186 +  
  8.1187 +  '/member': function (conn, req, res, params) {
  8.1188 +    var fields = ['organizational_unit', 'internal_posts', 'realname', 'birthday', 'address', 'email', 'xmpp_address', 'website', 'phone', 'mobile_phone', 'profession', 'external_memberships', 'external_posts', 'statement']
  8.1189 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1190 +      var query = new selector.SQLUpdate('member');
  8.1191 +      query.addWhere(['member.id = ?', req.current_member_id]);
  8.1192 +      fields.forEach( function(field) {
  8.1193 +        if (typeof(params[field]) != 'undefined') {
  8.1194 +          query.addValues({ field: params[field] });
  8.1195 +        } else {
  8.1196 +          query.addValues({ field: null });
  8.1197 +        }
  8.1198 +      });
  8.1199 +      db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1200 +    });
  8.1201 +  },
  8.1202 +
  8.1203 +  '/membership': function (conn, req, res, params) {
  8.1204 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1205 +
  8.1206 +      // check if area_id is set
  8.1207 +      var area_id = parseInt(params.area_id);
  8.1208 +      if (!area_id) {
  8.1209 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an area_id.');
  8.1210 +        return;
  8.1211 +      }
  8.1212 +
  8.1213 +      // delete membership
  8.1214 +      if (params.delete) {
  8.1215 +        var query;
  8.1216 +        query = new selector.SQLDelete('membership');
  8.1217 +        query.addWhere(['area_id = ?', area_id]);
  8.1218 +        query.addWhere(['member_id = ?', req.current_member_id]);
  8.1219 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1220 +
  8.1221 +      // add membership
  8.1222 +      } else {
  8.1223 +        
  8.1224 +        // lock member for upsert
  8.1225 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1226 +
  8.1227 +          // check and lock privilege
  8.1228 +          requireAreaPrivilege(conn, req, res, area_id, function() {
  8.1229 +
  8.1230 +            // upsert membership
  8.1231 +            var query = new selector.Upserter('membership', ['area_id', 'member_id']);
  8.1232 +            query.addValues({ area_id: area_id, member_id: req.current_member_id });
  8.1233 +            db.query(conn, req, res, query, function(result) { 
  8.1234 +              respond('json', conn, req, res, 'ok');
  8.1235 +            });
  8.1236 +          });
  8.1237 +        });
  8.1238 +      }
  8.1239 +    });
  8.1240 +  },
  8.1241 +    
  8.1242 +  '/interest': function (conn, req, res, params) {
  8.1243 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1244 +      var query;
  8.1245 +
  8.1246 +      // check if issue_id is set
  8.1247 +      var issue_id = parseInt(params.issue_id);
  8.1248 +      if (!issue_id) {
  8.1249 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  8.1250 +        return;
  8.1251 +      }
  8.1252 +
  8.1253 +      // lock member for upsert
  8.1254 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1255 +
  8.1256 +        // delete interest
  8.1257 +        if (params.delete) {
  8.1258 +
  8.1259 +          // check issue state
  8.1260 +          requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  8.1261 +
  8.1262 +            // delete interest
  8.1263 +            query = new selector.SQLDelete('interest');
  8.1264 +            query.addWhere(['issue_id = ?', issue_id]);
  8.1265 +            query.addWhere(['member_id = ?', req.current_member_id]);
  8.1266 +            db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1267 +          });
  8.1268 +
  8.1269 +        // add interest
  8.1270 +        } else {
  8.1271 +
  8.1272 +          // check and lock privilege
  8.1273 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  8.1274 +
  8.1275 +            // check issue state
  8.1276 +            requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  8.1277 +
  8.1278 +              // upsert interest
  8.1279 +              var query = new selector.Upserter('interest', ['issue_id', 'member_id']);
  8.1280 +              query.addValues({ issue_id: issue_id, member_id: req.current_member_id });
  8.1281 +              db.query(conn, req, res, query, function(result) { 
  8.1282 +                respond('json', conn, req, res, 'ok');
  8.1283 +              });
  8.1284 +            });
  8.1285 +          });
  8.1286 +        };
  8.1287 +      });
  8.1288 +    });
  8.1289 +  },
  8.1290 +
  8.1291 +  '/issue_comment': function (conn, req, res, params) {
  8.1292 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1293 +      
  8.1294 +      var issue_id = parseInt(params.issue_id);
  8.1295 +      var formatting_engine = params.formatting_engine
  8.1296 +      var content = params.content;
  8.1297 +
  8.1298 +      if (!issue_id) {
  8.1299 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  8.1300 +        return;
  8.1301 +      }
  8.1302 +
  8.1303 +      // delete issue comment
  8.1304 +      if (params.delete) {
  8.1305 +        var query;
  8.1306 +        query = new selector.SQLDelete('issue_comment');
  8.1307 +        query.addWhere(['issue_id = ?', params.issue_id]);
  8.1308 +        query.addWhere(['member_id = ?', req.current_member_id]);
  8.1309 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1310 +
  8.1311 +      // upsert issue comment
  8.1312 +      } else {
  8.1313 +
  8.1314 +        // check if formatting engine is supplied and valid
  8.1315 +        if (!formatting_engine) {
  8.1316 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting engine supplied.');
  8.1317 +          return;
  8.1318 +        } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  8.1319 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  8.1320 +          return;
  8.1321 +        };
  8.1322 +
  8.1323 +        // check if content is supplied
  8.1324 +        if (!content) {
  8.1325 +          respond('json', conn, req, res, 'unprocessable', null, 'No content supplied.');
  8.1326 +          return;
  8.1327 +        }
  8.1328 +        
  8.1329 +        // lock member for upsert
  8.1330 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1331 +
  8.1332 +          // check and lock privilege
  8.1333 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  8.1334 +
  8.1335 +            // upsert issue comment
  8.1336 +            var query = new selector.Upserter('issue_comment', ['issue_id', 'member_id']);
  8.1337 +            query.addValues({
  8.1338 +              issue_id: issue_id,
  8.1339 +              member_id: req.current_member_id,
  8.1340 +              changed: 'now',
  8.1341 +              formatting_engine: formatting_engine,
  8.1342 +              content: content
  8.1343 +            });
  8.1344 +
  8.1345 +            db.query(conn, req, res, query, function(result) { 
  8.1346 +              respond('json', conn, req, res, 'ok');
  8.1347 +            });
  8.1348 +
  8.1349 +          });
  8.1350 +        });
  8.1351 +
  8.1352 +      }
  8.1353 +      
  8.1354 +    });
  8.1355 +  },
  8.1356 +
  8.1357 +   '/voting_comment': function (conn, req, res, params) {
  8.1358 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1359 +      
  8.1360 +      var issue_id = parseInt(params.issue_id);
  8.1361 +      var formatting_engine = params.formatting_engine
  8.1362 +      var content = params.content;
  8.1363 +
  8.1364 +      if (!issue_id) {
  8.1365 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an issue_id.');
  8.1366 +        return;
  8.1367 +      }
  8.1368 +
  8.1369 +        
  8.1370 +      // delete voting comment
  8.1371 +      if (params.delete) {
  8.1372 +        var query;
  8.1373 +        query = new selector.SQLDelete('voting_comment');
  8.1374 +        query.addWhere(['issue_id = ?', params.issue_id]);
  8.1375 +        query.addWhere(['member_id = ?', req.current_member_id]);
  8.1376 +        db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1377 +
  8.1378 +      // upsert voting comment
  8.1379 +      } else {
  8.1380 +
  8.1381 +        // check if formatting engine is supplied and valid
  8.1382 +        if (!formatting_engine) {
  8.1383 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting engine supplied.');
  8.1384 +          return;
  8.1385 +        } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  8.1386 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  8.1387 +          return;
  8.1388 +        };
  8.1389 +
  8.1390 +        // check if content is supplied
  8.1391 +        if (!content) {
  8.1392 +          respond('json', conn, req, res, 'unprocessable', null, 'No content supplied.');
  8.1393 +          return;
  8.1394 +        }
  8.1395 +          
  8.1396 +        // lock member for upsert
  8.1397 +        lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1398 +
  8.1399 +          // check and lock privilege
  8.1400 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  8.1401 +
  8.1402 +            // check issue state
  8.1403 +            requireIssueState(conn, req, res, issue_id, ['voting', 'finished_with_winner', 'finished_without_winner'], function() {
  8.1404 +
  8.1405 +              // upsert voting comment
  8.1406 +              var query = new selector.Upserter('voting_comment', ['issue_id', 'member_id']);
  8.1407 +              query.addValues({
  8.1408 +                issue_id: issue_id,
  8.1409 +                member_id: req.current_member_id,
  8.1410 +                changed: 'now',
  8.1411 +                formatting_engine: formatting_engine,
  8.1412 +                content: content
  8.1413 +              });
  8.1414 +
  8.1415 +              db.query(conn, req, res, query, function(result) { 
  8.1416 +                respond('json', conn, req, res, 'ok');
  8.1417 +              });
  8.1418 +
  8.1419 +            });
  8.1420 +          });
  8.1421 +        })
  8.1422 +      };
  8.1423 +    });
  8.1424 +  },
  8.1425 +
  8.1426 +  '/supporter': function (conn, req, res, params) {
  8.1427 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1428 +      var initiative_id = parseInt(params.initiative_id);
  8.1429 +      var draft_id = parseInt(params.draft_id);
  8.1430 +
  8.1431 +      // check if needed arguments are supplied
  8.1432 +      if (!initiative_id) {
  8.1433 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an initiative_id.');
  8.1434 +        return;
  8.1435 +      }
  8.1436 +
  8.1437 +      if (!draft_id) {
  8.1438 +        respond('json', conn, req, res, 'unprocessable', null, 'You need to supply an draft_id.');
  8.1439 +        return;
  8.1440 +      }
  8.1441 +
  8.1442 +      // lock member for upsert
  8.1443 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1444 +
  8.1445 +        // delete supporter
  8.1446 +        if (params.delete) {
  8.1447 +          
  8.1448 +          // check issue state
  8.1449 +          requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion', 'verification'], function() {
  8.1450 +
  8.1451 +            // delete supporter
  8.1452 +            var query = new selector.SQLDelete('supporter');
  8.1453 +            query.addWhere(['initiative_id = ?', initiative_id]);
  8.1454 +            query.addWhere(['member_id = ?', req.current_member_id]);
  8.1455 +            db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1456 +
  8.1457 +          });
  8.1458 +          
  8.1459 +        // upsert supporter
  8.1460 +        } else {
  8.1461 +
  8.1462 +          // check and lock privilege
  8.1463 +          requireInitiativePrivilege(conn, req, res, initiative_id, function() {
  8.1464 +
  8.1465 +            // check issue state
  8.1466 +            requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion', 'verification'], function() {
  8.1467 +
  8.1468 +              // check if given draft is the current one
  8.1469 +              var query = new selector.Selector('current_draft');
  8.1470 +              query.addField('NULL');
  8.1471 +              query.addWhere(['current_draft.initiative_id = ?', initiative_id]);
  8.1472 +              query.addWhere(['current_draft.id = ?', draft_id]);
  8.1473 +              
  8.1474 +              db.query(conn, req, res, query, function(result) { 
  8.1475 +                if (result.rows.length != 1) {
  8.1476 +                  respond('json', conn, req, res, 'conflict', null, 'The draft with the supplied draft_id is not the current one anymore!');
  8.1477 +                  return;
  8.1478 +                }
  8.1479 +                
  8.1480 +                // upsert supporter
  8.1481 +                var query = new selector.Upserter('supporter', ['initiative_id', 'member_id']);
  8.1482 +                query.addValues({
  8.1483 +                  initiative_id: initiative_id,
  8.1484 +                  member_id: req.current_member_id,
  8.1485 +                  draft_id: draft_id
  8.1486 +                });
  8.1487 +
  8.1488 +                db.query(conn, req, res, query, function(result) { 
  8.1489 +                  respond('json', conn, req, res, 'ok');
  8.1490 +                });
  8.1491 +
  8.1492 +              });
  8.1493 +            });
  8.1494 +          });
  8.1495 +        };
  8.1496 +      });
  8.1497 +    });
  8.1498 +  },
  8.1499 +
  8.1500 +  '/draft': function (conn, req, res, params) {
  8.1501 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1502 +      var area_id = parseInt(params.area_id);
  8.1503 +      var policy_id = parseInt(params.policy_id);
  8.1504 +      var issue_id = parseInt(params.issue_id);
  8.1505 +      var initiative_id = parseInt(params.initiative_id);
  8.1506 +      var initiative_name = params.initiative_name;
  8.1507 +      var initiative_discussion_url = params.initiative_discussion_url;
  8.1508 +      var formatting_engine = params.formatting_engine;
  8.1509 +      var content = params.content;
  8.1510 +
  8.1511 +      if (!initiative_discussion_url) initiative_discussion_url = null;
  8.1512 +
  8.1513 +      // check parameters
  8.1514 +      if (!formatting_engine) {
  8.1515 +          respond('json', conn, req, res, 'unprocessable', null, 'No formatting_engine supplied.');
  8.1516 +          return;
  8.1517 +      } else if (formatting_engine != 'rocketwiki' && formatting_engine != 'compat') {
  8.1518 +          respond('json', conn, req, res, 'unprocessable', null, 'Invalid formatting engine supplied.');
  8.1519 +          return;
  8.1520 +      };
  8.1521 +
  8.1522 +      if (!content) {
  8.1523 +        respond('json', conn, req, res, 'unprocessable', null, 'No draft content supplied.');
  8.1524 +        return;
  8.1525 +      };
  8.1526 +
  8.1527 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1528 +        
  8.1529 +        // new draft in new initiative in new issue
  8.1530 +        if (area_id && !issue_id && !initiative_id) {
  8.1531 +
  8.1532 +          // check parameters for new issue
  8.1533 +          if (!policy_id) {
  8.1534 +            respond('json', conn, req, res, 'unprocessable', null, 'No policy supplied.');
  8.1535 +            return;
  8.1536 +          }
  8.1537 +          
  8.1538 +          if (!initiative_name) {
  8.1539 +            respond('json', conn, req, res, 'unprocessable', null, 'No initiative name supplied.');
  8.1540 +            return;
  8.1541 +          }
  8.1542 +          
  8.1543 +          requireAreaPrivilege(conn, req, res, area_id, function() {
  8.1544 +
  8.1545 +            // check if policy is allowed in this area and if area and policy are active
  8.1546 +            var query = new selector.Selector();
  8.1547 +            query.from('allowed_policy');
  8.1548 +            query.join('area', null, 'area.id = allowed_policy.area_id AND area.active');
  8.1549 +            query.join('policy', null, 'policy.id = allowed_policy.policy_id AND policy.active');
  8.1550 +            query.addField('NULL');
  8.1551 +            query.addWhere(['area.id = ? AND policy.id = ?', area_id, policy_id]);
  8.1552 +            db.query(conn, req, res, query, function (result, conn) {
  8.1553 +              if (result.rows.length != 1) {
  8.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.');
  8.1555 +                return;
  8.1556 +              };
  8.1557 +
  8.1558 +              // check contingent
  8.1559 +              requireContingentLeft(conn, req, res, true, function() {
  8.1560 +                
  8.1561 +                // insert new issue
  8.1562 +                var query = new selector.SQLInsert('issue');
  8.1563 +                query.addValues({
  8.1564 +                  area_id: area_id,
  8.1565 +                  policy_id: policy_id
  8.1566 +                });
  8.1567 +                query.addReturning('id');
  8.1568 +                db.query(conn, req, res, query, function(result) {
  8.1569 +                  var issue_id = result.rows[0].id;
  8.1570 +
  8.1571 +                  // insert new initiative
  8.1572 +                  var query = new selector.SQLInsert('initiative');
  8.1573 +                  query.addValues({
  8.1574 +                    issue_id: issue_id,
  8.1575 +                    name: initiative_name,
  8.1576 +                    discussion_url: initiative_discussion_url
  8.1577 +                  });
  8.1578 +                  query.addReturning('id');
  8.1579 +                  db.query(conn, req, res, query, function(result) {
  8.1580 +                    var initiative_id = result.rows[0].id;
  8.1581 +                    
  8.1582 +                    // insert initiator
  8.1583 +                    var query = new selector.SQLInsert('initiator');
  8.1584 +                    query.addValues({ initiative_id: initiative_id, member_id: req.current_member_id, accepted: true });
  8.1585 +                    db.query(conn, req, res, query, function(result) {
  8.1586 +
  8.1587 +                      // insert new draft
  8.1588 +                      var query = new selector.SQLInsert('draft');
  8.1589 +                      query.addValues({
  8.1590 +                        initiative_id: initiative_id,
  8.1591 +                        author_id: req.current_member_id,
  8.1592 +                        formatting_engine: formatting_engine,
  8.1593 +                        content: content 
  8.1594 +                      });
  8.1595 +                      query.addReturning('id');
  8.1596 +                      db.query(conn, req, res, query, function (result, conn) {
  8.1597 +                        var draft_id = result.rows[0].id;
  8.1598 +
  8.1599 +                        respond('json', conn, req, res, 'ok', { issue_id: issue_id, initiative_id: initiative_id, draft_id: draft_id } );
  8.1600 +                      });
  8.1601 +                    });
  8.1602 +                  });
  8.1603 +                });
  8.1604 +              });
  8.1605 +            });
  8.1606 +          });
  8.1607 +
  8.1608 +        // new draft in new initiative in existant issue
  8.1609 +        } else if (issue_id && !area_id && !initiative_id) {
  8.1610 +
  8.1611 +          // check privilege
  8.1612 +          requireIssuePrivilege(conn, req, res, issue_id, function() {
  8.1613 +            
  8.1614 +            // check issue state
  8.1615 +            requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification'], function() {
  8.1616 +            
  8.1617 +              // check contingent
  8.1618 +              requireContingentLeft(conn, req, res, true, function() {
  8.1619 +
  8.1620 +                // insert initiative
  8.1621 +                var query = new selector.SQLInsert('initiative');
  8.1622 +                query.addValues({
  8.1623 +                  issue_id: issue_id,
  8.1624 +                  name: initiative_name,
  8.1625 +                  discussion_url: initiative_discussion_url
  8.1626 +                });
  8.1627 +                query.addReturning('id');
  8.1628 +                db.query(conn, req, res, query, function(result) {
  8.1629 +                  var initiative_id = result.rows[0].id;
  8.1630 +                  
  8.1631 +                  // insert initiator
  8.1632 +                  var query = new selector.SQLInsert('initiator');
  8.1633 +                  query.addValues({
  8.1634 +                    initiative_id: initiative_id,
  8.1635 +                    member_id: req.current_member_id,
  8.1636 +                    accepted: true
  8.1637 +                  });
  8.1638 +                  db.query(conn, req, res, query, function(result) {
  8.1639 +
  8.1640 +                    // insert draft
  8.1641 +                    var query = new selector.SQLInsert('draft');
  8.1642 +                    query.addValues({
  8.1643 +                      initiative_id: initiative_id,
  8.1644 +                      author_id: req.current_member_id,
  8.1645 +                      formatting_engine: formatting_engine,
  8.1646 +                      content: content
  8.1647 +                    });
  8.1648 +                    query.addReturning('id');
  8.1649 +                    db.query(conn, req, res, query, function (result, conn) {
  8.1650 +
  8.1651 +                      var draft_id = result.rows[0].id;
  8.1652 +                      respond('json', conn, req, res, 'ok', { initiative_id: initiative_id, draft_id: draft_id } );
  8.1653 +                      
  8.1654 +                    });
  8.1655 +                  });
  8.1656 +                });
  8.1657 +              });
  8.1658 +            });
  8.1659 +          });
  8.1660 +
  8.1661 +        // new draft in existant initiative
  8.1662 +        } else if (initiative_id && !area_id && !issue_id ) {
  8.1663 +
  8.1664 +          // check privilege
  8.1665 +          requireInitiativePrivilege(conn, req, res, initiative_id, function() {
  8.1666 +            
  8.1667 +            // check issue state
  8.1668 +            requireIssueStateForInitiative(conn, req, res, initiative_id, ['admission', 'discussion'], function() {
  8.1669 +            
  8.1670 +
  8.1671 +              // get initiator
  8.1672 +              var query = new selector.Selector();
  8.1673 +              query.from('initiator');
  8.1674 +              query.addField('accepted');
  8.1675 +              query.addWhere(['initiative_id = ? AND member_id = ?', initiative_id, req.current_member_id]);
  8.1676 +              db.query(conn, req, res, query, function (result, conn) {
  8.1677 +
  8.1678 +                // if member is not initiator, deny creating new draft
  8.1679 +                if (result.rows.length != 1) {
  8.1680 +                  respond('json', conn, req, res, 'forbidden', null, 'You are not initiator of this initiative and not allowed to update its draft.');
  8.1681 +                  return;
  8.1682 +                }
  8.1683 +                var initiator = result.rows[0];
  8.1684 +                if (!initiator.accepted) {
  8.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.');
  8.1686 +                  return;
  8.1687 +                };
  8.1688 +
  8.1689 +                // check contingent
  8.1690 +                requireContingentLeft(conn, req, res, false, function() {
  8.1691 +
  8.1692 +                  // insert new draft
  8.1693 +                  var query = new selector.SQLInsert('draft');
  8.1694 +                  query.addValues({
  8.1695 +                    initiative_id: initiative_id,
  8.1696 +                    author_id: req.current_member_id,
  8.1697 +                    formatting_engine: formatting_engine,
  8.1698 +                    content: content
  8.1699 +                  });
  8.1700 +                  query.addReturning('id');
  8.1701 +                  db.query(conn, req, res, query, function (result, conn) {
  8.1702 +
  8.1703 +                    var draft_id = result.rows[0].id;
  8.1704 +                    respond('json', conn, req, res, 'ok', { draft_id: draft_id } );
  8.1705 +                  });
  8.1706 +                });
  8.1707 +              });
  8.1708 +            });
  8.1709 +          });
  8.1710 +
  8.1711 +        // none of them (invalid request)
  8.1712 +        } else {
  8.1713 +          respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of area_id, issue_id or initiative_id must be supplied!');
  8.1714 +        };
  8.1715 +        
  8.1716 +      });
  8.1717 +    });
  8.1718 +  },
  8.1719 +
  8.1720 +  '/suggestion': function (conn, req, res, params) {
  8.1721 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1722 +      // TODO
  8.1723 +    });
  8.1724 +  },
  8.1725 +  
  8.1726 +  '/opinion': function (conn, req, res, params) {
  8.1727 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1728 +      // TODO
  8.1729 +    });
  8.1730 +  },
  8.1731 +
  8.1732 +  '/delegation': function (conn, req, res, params) {
  8.1733 +    requireAccessLevel(conn, req, res, 'member', function() {
  8.1734 +      var unit_id = parseInt(params.unit_id);
  8.1735 +      var area_id = parseInt(params.area_id);
  8.1736 +      var issue_id = parseInt(params.issue_id);
  8.1737 +      var trustee_id;
  8.1738 +
  8.1739 +      if (params.trustee_id == '') {
  8.1740 +        trustee_id = null;
  8.1741 +      } else {
  8.1742 +        trustee_id = parseInt(params.trustee_id);
  8.1743 +      }
  8.1744 +      
  8.1745 +      lockMemberById(conn, req, res, req.current_member_id, function() {
  8.1746 +        
  8.1747 +        if (params.delete) {
  8.1748 +          var query = new selector.SQLDelete('delegation')
  8.1749 +          if (unit_id && !area_id && !issue_id) {
  8.1750 +            query.addWhere(['unit_id = ?', unit_id]);
  8.1751 +          } else if (!unit_id && area_id && !issue_id) {
  8.1752 +            query.addWhere(['area_id = ?', area_id]);
  8.1753 +          } else if (!unit_id && !area_id && issue_id) {
  8.1754 +            query.addWhere(['issue_id = ?', issue_id]);
  8.1755 +          } else {
  8.1756 +            respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of unit, area_id, issue_id must be supplied!');
  8.1757 +            return;
  8.1758 +          } 
  8.1759 +          query.addWhere(['truster_id = ?', req.current_member_id]);
  8.1760 +          db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1761 +        } else {
  8.1762 +          var query = new selector.Upserter('delegation', ['truster_id']);
  8.1763 +          query.addValues({
  8.1764 +            truster_id: req.current_member_id,
  8.1765 +            trustee_id: trustee_id
  8.1766 +          });
  8.1767 +          if (unit_id && !area_id && !issue_id) {
  8.1768 +            
  8.1769 +            // check privilege
  8.1770 +            requireUnitPrivilege(conn, req, res, unit_id, function() {
  8.1771 +
  8.1772 +              query.addKeys(['unit_id'])
  8.1773 +              query.addValues({ unit_id: unit_id, scope: 'unit' });
  8.1774 +              db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1775 +            });
  8.1776 +            
  8.1777 +          } else if (!unit_id && area_id && !issue_id) {
  8.1778 +
  8.1779 +            // check privilege
  8.1780 +            requireAreaPrivilege(conn, req, res, area_id, function() {
  8.1781 +
  8.1782 +              query.addKeys(['area_id'])
  8.1783 +              query.addValues({ area_id: area_id, scope: 'area' });
  8.1784 +              db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1785 +            });
  8.1786 +
  8.1787 +          } else if (!unit_id && !area_id && issue_id) {
  8.1788 +
  8.1789 +            // check privilege
  8.1790 +            requireIssuePrivilege(conn, req, res, issue_id, function() {
  8.1791 +
  8.1792 +              // check issue state
  8.1793 +              requireIssueState(conn, req, res, issue_id, ['admission', 'discussion', 'verification', 'voting'], function() {
  8.1794 +                
  8.1795 +                query.addKeys(['issue_id'])
  8.1796 +                query.addValues({ issue_id: issue_id, scope: 'issue' });
  8.1797 +                db.query(conn, req, res, query, function(result) { respond('json', conn, req, res, 'ok'); });
  8.1798 +              });
  8.1799 +            });
  8.1800 +          } else {
  8.1801 +            respond('json', conn, req, res, 'unprocessable', null, 'Excactly one of unit_id, area_id, issue_id must be supplied!');
  8.1802 +            return;
  8.1803 +          } 
  8.1804 +        }
  8.1805 +        
  8.1806 +      });
  8.1807 +      
  8.1808 +    });
  8.1809 +  },
  8.1810 +
  8.1811 +};
     9.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     9.2 +++ b/lib/selector.js	Sat Sep 10 23:31:20 2011 +0200
     9.3 @@ -0,0 +1,422 @@
     9.4 +var stringthesizer = require('./stringthesizer.js');
     9.5 +
     9.6 +var quoteLiteral = function(str) {
     9.7 +  if (str.search(/^"[^"]*"/) >= 0) return str;
     9.8 +  else return str.replace(/"/g, '""').replace(/[^\.]+/g, '"$&"');
     9.9 +};
    9.10 +
    9.11 +function SQLQuery() {
    9.12 +}
    9.13 +SQLQuery.prototype.assemble = function(nextPlaceholder) {
    9.14 +  return stringthesizer.stringthesizer(
    9.15 +    {
    9.16 +      nextPlaceholder: nextPlaceholder,
    9.17 +      valueSeparator: ",",
    9.18 +      coerce: function(value) {
    9.19 +        if (value instanceof Selector) return value.toStructure();
    9.20 +        return value;
    9.21 +      }
    9.22 +    },
    9.23 +    this.toStructure()
    9.24 +  );
    9.25 +}
    9.26 +
    9.27 +function Selector(from) {
    9.28 +  this._with = [];
    9.29 +  this._fields = [];
    9.30 +  this._distinct = false;
    9.31 +  this._distinctOn = [];
    9.32 +  this._from = [];
    9.33 +  this._where = [];
    9.34 +  this._groupBy = [];
    9.35 +  this._having = [];
    9.36 +  this._combine = [];
    9.37 +  this._orderBy = [];
    9.38 +  this._limit = null;
    9.39 +  this._offset = null;
    9.40 +  this._readLock = [];
    9.41 +  this._readLockAll = false;
    9.42 +  this._writeLock = [];
    9.43 +  this._writeLockAll = false;
    9.44 +  if (from != null) this.from(from);
    9.45 +}
    9.46 +Selector.prototype = new SQLQuery();
    9.47 +Selector.prototype.addWith = function(expression, selector) {
    9.48 +  this._with.push(['$ AS ($)', expression, selector]);
    9.49 +  return this;
    9.50 +};
    9.51 +Selector.prototype.addDistinctOn = function(expression) {
    9.52 +  if (this._distinct) throw "Cannot combine DISTINCT with DISTINCT ON.";
    9.53 +  this._distinctOn.push(expression);
    9.54 +  return this;
    9.55 +};
    9.56 +Selector.prototype.setDistinct = function() {
    9.57 +  if (this._distinctOn.length > 0) throw "Cannot combine DISTINCT with DISTINCT ON.";
    9.58 +  this._distinct = true;
    9.59 +  return this;
    9.60 +};
    9.61 +Selector.prototype.addFrom = function(expression, alias, condition) {
    9.62 +  var first = this._from.length == 0;
    9.63 +  if (!first) {
    9.64 +    if (condition == null) this._from.push('CROSS JOIN')
    9.65 +    else this._from.push('INNER JOIN')
    9.66 +  }
    9.67 +  if (expression instanceof Selector) {
    9.68 +    if (alias == null) this._from.push(['($) AS "subquery"', expression]);
    9.69 +    else this._from.push(['($) AS "$"', expression, alias]);
    9.70 +  } else {
    9.71 +    if (alias == null) this._from.push(expression);
    9.72 +    else this._from.push(['$ AS "$"', expression, alias]);
    9.73 +  }
    9.74 +  if (condition != null) {
    9.75 +    if (first) {
    9.76 +      this.addWhere(condition);
    9.77 +    } else {
    9.78 +      this._from.push('ON');
    9.79 +      this._from.push(condition);
    9.80 +    }
    9.81 +  }
    9.82 +  return this;
    9.83 +};
    9.84 +Selector.prototype.addWhere = function(expression) {
    9.85 +  this._where.push(['($)', expression]);
    9.86 +  return this;
    9.87 +};
    9.88 +Selector.prototype.addGroupBy = function(expression) {
    9.89 +  this._groupBy.push(expression);
    9.90 +  return this;
    9.91 +};
    9.92 +Selector.prototype.addHaving = function(expression) {
    9.93 +  this._having.push(['($)', expression]);
    9.94 +  return this;
    9.95 +};
    9.96 +Selector.prototype.addCombine = function(expression) {
    9.97 +  this._combine.push(expression);
    9.98 +  return this;
    9.99 +};
   9.100 +Selector.prototype.addOrderBy = function(expression) {
   9.101 +  this._orderBy.push(expression);
   9.102 +  return this;
   9.103 +};
   9.104 +Selector.prototype.limit = function(count) {
   9.105 +  this._limit = count;
   9.106 +  return this;
   9.107 +};
   9.108 +Selector.prototype.offset = function(count) {
   9.109 +  this._offset = count;
   9.110 +  return this;
   9.111 +};
   9.112 +Selector.prototype.forShare = function() {
   9.113 +  this._readLockAll = true;
   9.114 +  return this;
   9.115 +};
   9.116 +Selector.prototype.forShareOf = function(expression) {
   9.117 +  this._readLock.push(expression);
   9.118 +  return this;
   9.119 +};
   9.120 +Selector.prototype.forUpdate = function() {
   9.121 +  this._writeLockAll = true;
   9.122 +  return this;
   9.123 +};
   9.124 +Selector.prototype.forUpdateOf = function(expression) {
   9.125 +  this._writeLock.push(expression);
   9.126 +  return this;
   9.127 +};
   9.128 +Selector.prototype.resetFields = function() {
   9.129 +  this._fields = [];
   9.130 +  return this;
   9.131 +};
   9.132 +Selector.prototype.addField = function(expression, alias, options) {
   9.133 +  var self = this;
   9.134 +  var processOption = function(option) {
   9.135 +    if (option == "distinct") {
   9.136 +      if (alias == null) self.addDistinctOn(expression);
   9.137 +      else self.addDistinctOn(['"$"', alias]);
   9.138 +    } else if (option == "grouped") {
   9.139 +      if (alias == null) self.addGroupBy(expression);
   9.140 +      else self.addGroupBy(['"$"', alias]);
   9.141 +    } else {
   9.142 +      throw "Unexpected option passed to addField(...).";
   9.143 +    }
   9.144 +  }
   9.145 +  if (alias == null) this._fields.push(expression);
   9.146 +  else this._fields.push(['$ AS "$"', expression, alias]);
   9.147 +  if (options != null) {
   9.148 +    if (options instanceof Array) options.forEach(processOption);
   9.149 +    else processOption(options);
   9.150 +  }
   9.151 +  return this;
   9.152 +};
   9.153 +Selector.prototype.join = Selector.prototype.addFrom;
   9.154 +Selector.prototype.from = function(expression, alias, condition) {
   9.155 +  if (this._from.length > 0) {
   9.156 +    error();
   9.157 +  }
   9.158 +  return this.addFrom(expression, alias, condition);
   9.159 +};
   9.160 +Selector.prototype.leftJoin = function(expression, alias, condition) {
   9.161 +  var first = this._from.length == 0;
   9.162 +  if (!first) this._from.push('LEFT OUTER JOIN');
   9.163 +  if (alias == null) this._from.push(expression);
   9.164 +  else this._from.push(['$ AS "$"', expression, alias]);
   9.165 +  if (condition != null) {
   9.166 +    if (first) {
   9.167 +      this.addWhere(condition);
   9.168 +    } else {
   9.169 +      this._from.push('ON');
   9.170 +      this._from.push(condition);
   9.171 +    }
   9.172 +  }
   9.173 +};
   9.174 +Selector.prototype.union = function(expression) {
   9.175 +  this.addCombine(['UNION $', expression]);
   9.176 +  return this;
   9.177 +};
   9.178 +Selector.prototype.unionAll = function(expression) {
   9.179 +  this.addCombine(['UNION ALL $', expression]);
   9.180 +  return this;
   9.181 +};
   9.182 +Selector.prototype.intersect = function(expression) {
   9.183 +  this.addCombine(['INTERSECT $', expression]);
   9.184 +  return this;
   9.185 +};
   9.186 +Selector.prototype.intersectAll = function(expression) {
   9.187 +  this.addCombine(['INTERSECT ALL $', expression]);
   9.188 +  return this;
   9.189 +};
   9.190 +Selector.prototype.except = function(expression) {
   9.191 +  this.addCombine(['EXCEPT $', expression]);
   9.192 +  return this;
   9.193 +};
   9.194 +Selector.prototype.exceptAll = function(expression) {
   9.195 +  this.addCombine(['EXCEPT ALL $', expression]);
   9.196 +  return this;
   9.197 +};
   9.198 +Selector.prototype.toStructure = function() {
   9.199 +  var parts = [];
   9.200 +  parts.push('SELECT');
   9.201 +  if (this._distinct) parts.push('DISTINCT');
   9.202 +  else if (this._distinctOn.length > 0)
   9.203 +    parts.push(['DISTINCT ON ($$)', ', ', this._distinctOn]);
   9.204 +  parts.push(["$$", ", ", this._fields]);
   9.205 +  if (this._from.length > 0)    parts.push(['FROM $$',     ' ',     this._from]);
   9.206 +  if (this._where.length > 0)   parts.push(['WHERE $$',    ' AND ', this._where]);
   9.207 +  if (this._groupBy.length > 0) parts.push(['GROUP BY $$', ', ',    this._groupBy]);
   9.208 +  if (this._having.length > 0)  parts.push(['HAVING $$',   ' AND ', this._having]);
   9.209 +  this._combine.forEach(function(entry) { parts.push(entry); });
   9.210 +  if (this._orderBy.length > 0) parts.push(['ORDER BY $$', ', ',    this._orderBy]);
   9.211 +  if (this._limit != null)      parts.push(['LIMIT ?',  this._limit]);
   9.212 +  if (this._offset != null)     parts.push(['OFFSET ?', this._offset]);
   9.213 +  if (this._writeLockAll) parts.push('FOR UPDATE');
   9.214 +  else {
   9.215 +    if (this._readLockAll) parts.push('FOR SHARE');
   9.216 +    else if (this._readLock.length > 0)
   9.217 +      parts.push(['FOR SHARE OF $$', ', ', this._readLock]);
   9.218 +    if (this._writeLock.length > 0)
   9.219 +      parts.push(['FOR UPDATE OF $$', ', ', this._writeLock]);
   9.220 +  }
   9.221 +  return ["$$", " ", parts];
   9.222 +};
   9.223 +
   9.224 +function SQLInsert(table) {
   9.225 +  this._with = [];
   9.226 +  if (table == null) this._table = null;
   9.227 +  else this._table = table;
   9.228 +  this._columns = [];
   9.229 +  this._values = [];
   9.230 +  this._query = null;
   9.231 +  this._returning = [];
   9.232 +}
   9.233 +SQLInsert.prototype = new SQLQuery();
   9.234 +SQLInsert.prototype.addWith = Selector.prototype.addWith;
   9.235 +SQLInsert.prototype.table = function(expression) {
   9.236 +  this._table = expression;
   9.237 +  return this;
   9.238 +};
   9.239 +SQLInsert.prototype.addValues = function(mapping) {
   9.240 +  if (this._query != null) throw "Cannot combine query with values.";
   9.241 +  for (key in mapping) {
   9.242 +    this._columns.push(key);
   9.243 +    this._values.push(['?', mapping[key]]);
   9.244 +  }
   9.245 +};
   9.246 +SQLInsert.prototype.addValueExpressions = function(mapping) {
   9.247 +  if (this._query != null) throw "Cannot combine query with values.";
   9.248 +  for (key in mapping) {
   9.249 +    this._columns.push(key);
   9.250 +    this._values.push(mapping[key]);
   9.251 +  }
   9.252 +};
   9.253 +SQLInsert.prototype.query = function(columns, expression) {
   9.254 +  if (this._values.length > 0) throw "Cannot combine query with values.";
   9.255 +  this._columns = columns;
   9.256 +  this._select = expression;
   9.257 +};
   9.258 +SQLInsert.prototype.addReturning = function(expression, alias) {
   9.259 +  if (alias == null) this._returning.push(expression);
   9.260 +  else this._returning.push(['$ AS "$"', expression, alias]);
   9.261 +};
   9.262 +SQLInsert.prototype.toStructure = function() {
   9.263 +  var parts = [];
   9.264 +  parts.push('INSERT INTO');
   9.265 +  if (this._table == null) throw "Missing table for INSERT.";
   9.266 +  parts.push(this._table);
   9.267 +  if (this._columns.length > 0) parts.push(['($$)', ', ', this._columns]);
   9.268 +  if (this._values.length > 0) parts.push(['VALUES ($$)', ', ', this._values]);
   9.269 +  else if (this._query == null) parts.push('DEFAULT VALUES');
   9.270 +  else parts.push(this._query);
   9.271 +  if (this._returning.length > 0)
   9.272 +    parts.push(['RETURNING $$', ', ', this._returning]);
   9.273 +  return ["$$", " ", parts];
   9.274 +};
   9.275 +
   9.276 +function SQLUpdate(table) {
   9.277 +  this._with = [];
   9.278 +  if (table == null) this._table = null;
   9.279 +  else this._table = table;
   9.280 +  this._columns = [];
   9.281 +  this._values = [];
   9.282 +  this._query = null;
   9.283 +  this._from = [];
   9.284 +  this._where = [];
   9.285 +  this._returning = [];
   9.286 +}
   9.287 +SQLUpdate.prototype = new SQLQuery();
   9.288 +SQLUpdate.prototype.addWith = Selector.prototype.addWith;
   9.289 +SQLUpdate.prototype.table = function(expression, alias) {
   9.290 +  if (alias == null) this._table = expression;
   9.291 +  else this._table = ['$ AS "$"', expression, alias];
   9.292 +  return this;
   9.293 +}
   9.294 +SQLUpdate.prototype.addValues = SQLInsert.prototype.addValues;
   9.295 +SQLUpdate.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   9.296 +SQLUpdate.prototype.query = SQLInsert.prototype.query;
   9.297 +SQLUpdate.prototype.addFrom = Selector.prototype.addFrom;
   9.298 +SQLUpdate.prototype.join = Selector.prototype.addFrom;
   9.299 +SQLUpdate.prototype.leftJoin = function(expression, alias, condition) {
   9.300 +  if (this._from.length == 0)
   9.301 +    throw "First join for UPDATE or DELETE must not be a left join.";
   9.302 +  this._from.push('LEFT OUTER JOIN');
   9.303 +  if (alias == null) this._from.push(expression);
   9.304 +  else this._from.push(['$ AS "$"', expression, alias]);
   9.305 +  if (condition != null) {
   9.306 +    this._from.push('ON');
   9.307 +    this._from.push(condition);
   9.308 +  }
   9.309 +};
   9.310 +SQLUpdate.prototype.addWhere = Selector.prototype.addWhere;
   9.311 +SQLUpdate.prototype.addReturning = SQLInsert.prototype.addReturning;
   9.312 +SQLUpdate.prototype.toStructure = function() {
   9.313 +  var parts = [];
   9.314 +  parts.push('UPDATE');
   9.315 +  if (this._table == null) throw "Missing table for UPDATE.";
   9.316 +  parts.push(this._table);
   9.317 +  parts.push('SET');
   9.318 +  if (this._columns.length == 0) throw "Missing columns for UPDATE.";
   9.319 +  if (this._query == null) {
   9.320 +    for (var i=0; i<this._columns.length; i++) {
   9.321 +      parts.push(
   9.322 +        [ (i==this._columns.length-1) ? '$ = $' : '$ = $,',
   9.323 +          this._columns[i],
   9.324 +          this._values[i]]
   9.325 +      );
   9.326 +    }
   9.327 +  } else {
   9.328 +    parts.push(['($$) = ($$)', ', ', this._columns, ', ', this._values]);
   9.329 +  }
   9.330 +  if (this._from.length > 0) parts.push(['FROM $$', ' ', this._from]);
   9.331 +  if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
   9.332 +  if (this._returning.length > 0)
   9.333 +    parts.push(['RETURNING $$', ', ', this._returning]);
   9.334 +  return ["$$", " ", parts];
   9.335 +};
   9.336 +
   9.337 +function SQLDelete(table) {
   9.338 +  this._with = [];
   9.339 +  if (table == null) this._table = null;
   9.340 +  else this._table = table;
   9.341 +  this._from = [];  // USING clause
   9.342 +  this._where = [];
   9.343 +  this._returning = [];
   9.344 +}
   9.345 +SQLDelete.prototype = new SQLQuery();
   9.346 +SQLDelete.prototype.addWith = Selector.prototype.addWith;
   9.347 +SQLDelete.prototype.table = SQLUpdate.prototype.table;
   9.348 +SQLDelete.prototype.addValues = SQLInsert.prototype.addValues;
   9.349 +SQLDelete.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   9.350 +SQLDelete.prototype.addFrom = Selector.prototype.addFrom;
   9.351 +SQLDelete.prototype.addUsing = Selector.prototype.addFrom;
   9.352 +SQLDelete.prototype.join = Selector.prototype.addFrom;
   9.353 +SQLDelete.prototype.leftJoin = SQLUpdate.prototype.leftJoin;
   9.354 +SQLDelete.prototype.addWhere = Selector.prototype.addWhere;
   9.355 +SQLDelete.prototype.addReturning = SQLInsert.prototype.addReturning;
   9.356 +SQLDelete.prototype.toStructure = function() {
   9.357 +  var parts = [];
   9.358 +  parts.push('DELETE FROM');
   9.359 +  if (this._table == null) throw "Missing table for DELETE.";
   9.360 +  parts.push(this._table);
   9.361 +  if (this._from.length > 0) parts.push(['USING $$', ' ', this._from]);
   9.362 +  if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
   9.363 +  if (this._returning.length > 0)
   9.364 +    parts.push(['RETURNING $$', ', ', this._returning]);
   9.365 +  return ["$$", " ", parts];
   9.366 +};
   9.367 +
   9.368 +function Upserter(table, keys) {
   9.369 +  if (table == null) this._table = null;
   9.370 +  else this._table = table;
   9.371 +  this._columns = [];
   9.372 +  this._values = [];
   9.373 +  this._keys = [];
   9.374 +  if (keys) this.addKeys(keys);
   9.375 +}
   9.376 +
   9.377 +Upserter.prototype.addValues = SQLInsert.prototype.addValues;
   9.378 +Upserter.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   9.379 +Upserter.prototype.addKeys = function(keys) {
   9.380 +  var self = this;
   9.381 +  keys.forEach(function(key) { self._keys.push(key); });
   9.382 +};
   9.383 +Upserter.prototype.applyWhere = function(sqlQuery) {
   9.384 +  for (var i=0; i<this._columns.length; i++) {
   9.385 +    var column = this._columns[i];
   9.386 +    var value = this._values[i];
   9.387 +    for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
   9.388 +    if (j<this._keys.length) sqlQuery.addWhere(['$ = $', column, value]);
   9.389 +  }
   9.390 +}
   9.391 +Upserter.prototype.getSelector = function() {
   9.392 +  var selector = new Selector(this._table).addField('NULL');
   9.393 +  this.applyWhere(selector);
   9.394 +  return selector;
   9.395 +}
   9.396 +Upserter.prototype.getSQLInsert = function() {
   9.397 +  var sqlInsert = new SQLInsert(this._table);
   9.398 +  sqlInsert._columns = this._columns;
   9.399 +  sqlInsert._values = this._values;
   9.400 +  return sqlInsert;
   9.401 +}
   9.402 +Upserter.prototype.getSQLUpdate = function() {
   9.403 +  var sqlUpdate = new SQLUpdate(this._table);
   9.404 +  for (var i =0; i<this._columns.length; i++) {
   9.405 +    var column = this._columns[i];
   9.406 +    var value = this._values[i];
   9.407 +    for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
   9.408 +    if (j==this._keys.length) {
   9.409 +      sqlUpdate._columns.push(column);
   9.410 +      sqlUpdate._values.push(value);
   9.411 +    }
   9.412 +  }
   9.413 +  if (sqlUpdate._columns.length == 0) return null;
   9.414 +  this.applyWhere(sqlUpdate);
   9.415 +  return sqlUpdate;
   9.416 +}
   9.417 +
   9.418 +
   9.419 +exports.SQLQuery = SQLQuery;
   9.420 +exports.Selector = Selector;
   9.421 +exports.Upserter = Upserter;
   9.422 +exports.SQLInsert = SQLInsert;
   9.423 +exports.SQLUpdate = SQLUpdate;
   9.424 +exports.SQLDelete = SQLDelete;
   9.425 +
    10.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
    10.2 +++ b/lib/stringthesizer.js	Sat Sep 10 23:31:20 2011 +0200
    10.3 @@ -0,0 +1,53 @@
    10.4 +function stringthesizer(options, struct) {
    10.5 +  // options:
    10.6 +  // nextPlaceholder (function)
    10.7 +  // valueSeparator (string)
    10.8 +  // coerce (function)
    10.9 +  var cmdParts = [];
   10.10 +  var args = [];
   10.11 +  var process = function(struct, skipCoercion) {
   10.12 +    if (struct instanceof Array) {
   10.13 +      var structIdx = 0;
   10.14 +      var next = function() { return struct[structIdx++]; };
   10.15 +      next().match(/[^?$]+|\?\??\??|\$\$?\$?/g).forEach(function(stringPart) {
   10.16 +        if (stringPart == "?") {
   10.17 +          cmdParts.push(options.nextPlaceholder(args.length));
   10.18 +          args.push(next())
   10.19 +        } else if (stringPart == "??") {
   10.20 +          var first = true;
   10.21 +          next().forEach(function(entry) {
   10.22 +            if (first) first = false;
   10.23 +            else cmdParts.push(options.valueSeparator);
   10.24 +            cmdParts.push(options.nextPlaceholder(args.length));
   10.25 +            args.push(entry)
   10.26 +          });
   10.27 +        } else if (stringPart == "???") {
   10.28 +          cmdParts.push("?");
   10.29 +        } else if (stringPart == "$") {
   10.30 +          process(next());
   10.31 +        } else if (stringPart == "$$") {
   10.32 +          var sep = next();
   10.33 +          var first = true;
   10.34 +          next().forEach(function(entry) {
   10.35 +            if (first) first = false;
   10.36 +            else cmdParts.push(sep);
   10.37 +            process(entry);
   10.38 +          });
   10.39 +        } else if (stringPart == "$$$") {
   10.40 +          cmdParts.push("$");
   10.41 +        } else {
   10.42 +          cmdParts.push(stringPart);
   10.43 +        }
   10.44 +      });
   10.45 +      if (structIdx != struct.length) { throw "Wrong argument count for stringthesizer"; }
   10.46 +    } else if (skipCoercion || typeof (struct) == 'string') {
   10.47 +      cmdParts.push(struct);
   10.48 +    } else {
   10.49 +      process(options.coerce(struct), true);
   10.50 +    }
   10.51 +  }
   10.52 +  process(struct);
   10.53 +  return { cmd: cmdParts.join(""), args: args };
   10.54 +}
   10.55 +
   10.56 +exports.stringthesizer = stringthesizer;
   10.57 \ No newline at end of file

Impressum / About Us