summaryrefslogtreecommitdiff
authorMichael Krelin <hacker@klever.net>2013-11-25 20:52:38 (UTC)
committer Michael Krelin <hacker@klever.net>2013-11-25 20:52:38 (UTC)
commit352f2216eec032efce4bdeddd2ffe5a9e326a99d (patch) (unidiff)
tree902272eaff4d42c0e732cc446baa3b5958cfae73
parentc79ee694f71f787e896fe3f954316723ef0f5544 (diff)
downloadclipperz-352f2216eec032efce4bdeddd2ffe5a9e326a99d.zip
clipperz-352f2216eec032efce4bdeddd2ffe5a9e326a99d.tar.gz
clipperz-352f2216eec032efce4bdeddd2ffe5a9e326a99d.tar.bz2
postgresql-based clipperz own session store
the use thereof is not mandatory, but may come in handy for the standalone deployment
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--backend/node/src/app.js20
-rw-r--r--backend/node/src/clipperz.js37
-rw-r--r--backend/node/src/clipperz.schema.sql7
3 files changed, 54 insertions, 10 deletions
diff --git a/backend/node/src/app.js b/backend/node/src/app.js
index d4d60c2..61c2c72 100644
--- a/backend/node/src/app.js
+++ b/backend/node/src/app.js
@@ -1,49 +1,51 @@
1var BUNYAN = require('bunyan'); 1var BUNYAN = require('bunyan');
2var LOGGER = BUNYAN.createLogger({ 2var LOGGER = BUNYAN.createLogger({
3 name: 'clipperz', 3 name: 'clipperz',
4 streams: [ 4 streams: [
5 { name: "console", stream:process.stderr,level:'trace'} 5 { name: "console", stream:process.stderr,level:'trace'}
6 ], 6 ],
7 serializers: { 7 serializers: {
8 req: BUNYAN.stdSerializers.req, 8 req: BUNYAN.stdSerializers.req,
9 res: BUNYAN.stdSerializers.res, 9 res: BUNYAN.stdSerializers.res,
10 err: BUNYAN.stdSerializers.err 10 err: BUNYAN.stdSerializers.err
11 }, 11 },
12 src: true 12 src: true
13}); 13});
14 14
15 15
16var EXPRESS = require('express'); 16var EXPRESS = require('express');
17var HTTP = require('http'); 17var HTTP = require('http');
18var PATH = require('path'); 18var PATH = require('path');
19 19
20
21var CLIPPERZ = require('./clipperz');
22var CONF = require('./conf');
23var clipperz = CLIPPERZ({
24 psql: CONF.psql||'postgresql:///clipperz',
25 logger: LOGGER,
26 dump_template: PATH.join(__dirname,'htdocs/beta/index.html')
27});
28
29
20var app = EXPRESS(); 30var app = EXPRESS();
21 31
22app.set('port', process.env.PORT || 3000); 32app.set('port', process.env.PORT || 3000);
23app.use(EXPRESS.logger('dev')); 33app.use(EXPRESS.logger('dev'));
24app.use(EXPRESS.urlencoded()); 34app.use(EXPRESS.urlencoded());
25app.use(EXPRESS.methodOverride()); 35app.use(EXPRESS.methodOverride());
26app.use(EXPRESS.cookieParser('your secret here')); 36app.use(EXPRESS.cookieParser('your secret here'));
27app.use(EXPRESS.session()); 37app.use(EXPRESS.session({secret:'99 little bugs in the code', key:'sid', store: clipperz.session_store() }));
28app.use(app.router); 38app.use(app.router);
29app.use(EXPRESS.static(PATH.join(__dirname, 'htdocs/'))); 39app.use(EXPRESS.static(PATH.join(__dirname, 'htdocs/')));
30if ('development' == app.get('env')) { 40if ('development' == app.get('env')) {
31 app.use(EXPRESS.errorHandler()); 41 app.use(EXPRESS.errorHandler());
32} 42}
33 43
34 44
35var CLIPPERZ = require('./clipperz');
36var CONF = require('./conf');
37var clipperz = CLIPPERZ({
38 psql: CONF.psql||'postgresql:///clipperz',
39 logger: LOGGER,
40 dump_template: PATH.join(__dirname,'htdocs/beta/index.html')
41});
42
43app.post('/json',clipperz.json); 45app.post('/json',clipperz.json);
44app.get('/beta/dump',clipperz.dump); 46app.get('/beta/dump',clipperz.dump);
45 47
46 48
47HTTP.createServer(app).listen(app.get('port'), function(){ 49HTTP.createServer(app).listen(app.get('port'), function(){
48 LOGGER.info({port:app.get('port')},"Listener established"); 50 LOGGER.info({port:app.get('port')},"Listener established");
49}); 51});
diff --git a/backend/node/src/clipperz.js b/backend/node/src/clipperz.js
index eebd5bf..73af0a0 100644
--- a/backend/node/src/clipperz.js
+++ b/backend/node/src/clipperz.js
@@ -1,127 +1,158 @@
1var FS = require('fs'); 1var FS = require('fs');
2var CRYPTO = require('crypto'); 2var CRYPTO = require('crypto');
3var BIGNUM = require('bignum'); 3var BIGNUM = require('bignum');
4var ASYNC = require('async'); 4var ASYNC = require('async');
5 5
6var express_store = require('express').session.Store;
7
6function clipperz_hash(v) { 8function clipperz_hash(v) {
7 return CRYPTO.createHash('sha256').update( 9 return CRYPTO.createHash('sha256').update(
8 CRYPTO.createHash('sha256').update(v).digest('binary') 10 CRYPTO.createHash('sha256').update(v).digest('binary')
9 ).digest('hex'); 11 ).digest('hex');
10}; 12};
11function clipperz_random() { 13function clipperz_random() {
12 for(var r = '';r.length<64;r+=''+BIGNUM(Math.floor(Math.random()*1e18)).toString(16)); 14 for(var r = '';r.length<64;r+=''+BIGNUM(Math.floor(Math.random()*1e18)).toString(16));
13 return r.substr(0,64); 15 return r.substr(0,64);
14}; 16};
17function clipperz_store(PG) {
18 var rv = function(o) { express_store.call(this,o); }
19 rv.prototype.get = function(sid,cb) { PG.Q(
20 "SELECT s_data FROM clipperz.thesession WHERE s_id=$1",[sid],
21 function(e,r) { cb(e,(e||!r.rowCount)?null:r.rows[0].s_data); }
22 ) };
23 rv.prototype.set = function(sid,data,cb) { PG.Q(
24 "UPDATE clipperz.thesession SET s_data=$1, s_mtime=current_timestamp"
25 +" WHERE s_id=$2",[data,sid], function(e,r) {
26 if(e) return cb(e);
27 if(r.rowCount) return cb();
28 PG.Q("INSERT INTO clipperz.thesession (s_id,s_data) VALUES ($1,$2)",[sid,data],cb);
29 }
30 ) };
31 rv.prototype.destroy = function(sid,cb) { PG.Q(
32 "DELETE FROM clipperz.thesession WHERE s_id=$1",[sid],cb
33 ) };
34 rv.prototype.length = function(cb) { PG.Q(
35 "SELECT count(*) AS c FROM clipperz.thesession", function(e,r) {
36 cb(e,e?null:r.rows[0].c);
37 }
38 ) };
39 rv.prototype.length = function(cb) { PQ.Q(
40 "DELETE FROM clipperz.thesession", cb
41 ) };
42 rv.prototype.__proto__ = express_store.prototype;
43 return rv;
44}
45
15var srp_g = BIGNUM(2); 46var srp_g = BIGNUM(2);
16var srp_n = BIGNUM("115b8b692e0e045692cf280b436735c77a5a9e8a9e7ed56c965f87db5b2a2ece3",16); 47var srp_n = BIGNUM("115b8b692e0e045692cf280b436735c77a5a9e8a9e7ed56c965f87db5b2a2ece3",16);
17var n123 = '112233445566778899aabbccddeeff00112233445566778899aabbccddeeff00'; 48var n123 = '112233445566778899aabbccddeeff00112233445566778899aabbccddeeff00';
18 49
19 50
20var CLIPPERZ = module.exports = function(CONFIG) { 51var CLIPPERZ = module.exports = function(CONFIG) {
21 52
22 var LOGGER = CONFIG.logger||{trace:function(){}}; 53 var LOGGER = CONFIG.logger||{trace:function(){}};
23 54
24 var PG = { 55 var PG = {
25 url: CONFIG.psql, 56 url: CONFIG.psql,
26 PG: require('pg').native, 57 PG: require('pg').native,
27 Q: function(q,a,cb) { 58 Q: function(q,a,cb) {
28 if('function'===typeof a) cb=a,a=[]; 59 if('function'===typeof a) cb=a,a=[];
29 LOGGER.trace({query:q,args:a},'SQL: %s',q); 60 LOGGER.trace({query:q,args:a},'SQL: %s',q);
30 PG.PG.connect(PG.url,function(e,C,D) { 61 PG.PG.connect(PG.url,function(e,C,D) {
31 if(e) return cb(e); 62 if(e) return cb(e);
32 var t0=new Date(); 63 var t0=new Date();
33 C.query(q,a,function(e,r) { 64 C.query(q,a,function(e,r) {
34 var t1=new Date(), dt=t1-t0; 65 var t1=new Date(), dt=t1-t0;
35 D(); 66 D();
36 LOGGER.trace({query:q,args:a,ms:dt,rows:r&&r.rowCount},"SQL query '%s' took %dms",q,dt); 67 LOGGER.trace({query:q,args:a,ms:dt,rows:r&&r.rowCount},"SQL query '%s' took %dms",q,dt);
37 cb(e,r); 68 cb(e,r);
38 }); 69 });
39 }); 70 });
40 }, 71 },
41 T: function(cb) { 72 T: function(cb) {
42 PG.PG.connect(PG.url,function(e,C,D) { 73 PG.PG.connect(PG.url,function(e,C,D) {
43 if(e) return cb(e); 74 if(e) return cb(e);
44 C.query('BEGIN',function(e){ 75 C.query('BEGIN',function(e){
45 if(e) return D(),cb(e); 76 if(e) return D(),cb(e);
46 cb(null,{ 77 cb(null,{
47 Q: function(q,a,cb) { 78 Q: function(q,a,cb) {
48 LOGGER.trace({query:q,args:a},'SQL: %s',q); 79 LOGGER.trace({query:q,args:a},'SQL: %s',q);
49 if(this.over) return cb(new Error('game over')); 80 if(this.over) return cb(new Error('game over'));
50 if('function'===typeof a) cb=a,a=[]; 81 if('function'===typeof a) cb=a,a=[];
51 var t0=new Date(); 82 var t0=new Date();
52 C.query(q,a,function(e,r) { 83 C.query(q,a,function(e,r) {
53 var t1=new Date(), dt=t1-t0; 84 var t1=new Date(), dt=t1-t0;
54 LOGGER.trace({query:q,args:a,ms:dt,rows:r&&r.rowCount},"SQL query '%s' took %dms",q,dt); 85 LOGGER.trace({query:q,args:a,ms:dt,rows:r&&r.rowCount},"SQL query '%s' took %dms",q,dt);
55 cb(e,r); 86 cb(e,r);
56 }); 87 });
57 }, 88 },
58 commit: function(cb) { 89 commit: function(cb) {
59 LOGGER.trace('SQL: commit'); 90 LOGGER.trace('SQL: commit');
60 if(this.over) return cb(new Error('game over')); 91 if(this.over) return cb(new Error('game over'));
61 return (this.over=true),C.query('COMMIT',function(e){D();cb&&cb(e)}); 92 return (this.over=true),C.query('COMMIT',function(e){D();cb&&cb(e)});
62 }, 93 },
63 rollback: function(cb) { 94 rollback: function(cb) {
64 LOGGER.trace('SQL: rollback'); 95 LOGGER.trace('SQL: rollback');
65 if(this.over) return cb(new Error('game over')); 96 if(this.over) return cb(new Error('game over'));
66 return (this.over=true),C.query('ROLLBACK',function(e){D();cb&&cb(e)}); 97 return (this.over=true),C.query('ROLLBACK',function(e){D();cb&&cb(e)});
67 }, 98 },
68 end: function(e,cb) { 99 end: function(e,cb) {
69 if(e) return LOGGER.trace(e,"rolling back transaction due to an error"),this.rollback(cb); 100 if(e) return LOGGER.trace(e,"rolling back transaction due to an error"),this.rollback(cb);
70 this.commit(cb); 101 this.commit(cb);
71 } 102 }
72 }); 103 });
73 }); 104 });
74 }); 105 });
75 } 106 }
76 }; 107 };
77 108
78 109
79 return { 110 var rv = {
80 111
81 json: function clipperz_json(req,res,cb) { 112 json: function clipperz_json(req,res,cb) {
82 var method = req.body.method, pp = JSON.parse(req.body.parameters).parameters; 113 var method = req.body.method, pp = JSON.parse(req.body.parameters).parameters;
83 var message = pp.message; 114 var message = pp.message;
84 var ppp = pp.parameters; 115 var ppp = pp.parameters;
85 res.res = function(o) { return res.json({result:o}) }; 116 res.res = function(o) { return res.json({result:o}) };
86 LOGGER.trace({method:method,parameters:pp},"JSON request"); 117 LOGGER.trace({method:method,parameters:pp},"JSON request");
87 118
88 switch(method) { 119 switch(method) {
89 case 'registration': 120 case 'registration':
90 switch(message) { 121 switch(message) {
91 case 'completeRegistration': return PG.Q( 122 case 'completeRegistration': return PG.Q(
92 "INSERT INTO clipperz.theuser" 123 "INSERT INTO clipperz.theuser"
93 +" (u_name, u_srp_s,u_srp_v, u_authversion,u_header,u_statistics,u_version,u_lock)" 124 +" (u_name, u_srp_s,u_srp_v, u_authversion,u_header,u_statistics,u_version,u_lock)"
94 +" VALUES ($1, $2,$3, $4,$5,$6,$7,$8)", 125 +" VALUES ($1, $2,$3, $4,$5,$6,$7,$8)",
95 [pp.credentials.C, pp.credentials.s, pp.credentials.v, 126 [pp.credentials.C, pp.credentials.s, pp.credentials.v,
96 pp.credentials.version,pp.user.header, pp.user.statistics, 127 pp.credentials.version,pp.user.header, pp.user.statistics,
97 pp.user.version, pp.user.lock], function(e,r) { 128 pp.user.version, pp.user.lock], function(e,r) {
98 if(e) return cb(e); 129 if(e) return cb(e);
99 res.res({lock:pp.user.lock,result:'done'}); 130 res.res({lock:pp.user.lock,result:'done'});
100 }); 131 });
101 } 132 }
102 break; 133 break;
103 134
104 case 'handshake': 135 case 'handshake':
105 switch(message) { 136 switch(message) {
106 case 'connect': return ASYNC.auto({ 137 case 'connect': return ASYNC.auto({
107 u: function(cb) { PG.Q( 138 u: function(cb) { PG.Q(
108 "SELECT u_id, u_srp_s, u_srp_v FROM clipperz.theuser WHERE u_name=$1", 139 "SELECT u_id, u_srp_s, u_srp_v FROM clipperz.theuser WHERE u_name=$1",
109 [ppp.C], function(e,r) { 140 [ppp.C], function(e,r) {
110 if(e) return cb(e); 141 if(e) return cb(e);
111 if(!r.rowCount) return cb(null,{u_id:null,u_srp_s:n123,u_srp_v:n123}); 142 if(!r.rowCount) return cb(null,{u_id:null,u_srp_s:n123,u_srp_v:n123});
112 cb(null,r.rows[0]); 143 cb(null,r.rows[0]);
113 }) }, 144 }) },
114 otp: ['u',function(cb,r) { 145 otp: ['u',function(cb,r) {
115 if(!req.session.otp) return cb(); 146 if(!req.session.otp) return cb();
116 if(req.session.u!=r.u.u_id) return cb(new Error('user/OTP mismatch')); 147 if(req.session.u!=r.u.u_id) return cb(new Error('user/OTP mismatch'));
117 PG.Q( 148 PG.Q(
118 "UPDATE clipperz.theotp AS otp" 149 "UPDATE clipperz.theotp AS otp"
119 +" SET" 150 +" SET"
120 +" otps_id=CASE WHEN s.otps_code='REQUESTED' THEN (" 151 +" otps_id=CASE WHEN s.otps_code='REQUESTED' THEN ("
121 +" SELECT ss.otps_id FROM clipperz.otpstatus AS ss WHERE ss.otps_code='USED'" 152 +" SELECT ss.otps_id FROM clipperz.otpstatus AS ss WHERE ss.otps_code='USED'"
122 +" ) ELSE otp.otps_id END," 153 +" ) ELSE otp.otps_id END,"
123 +" otp_utime=current_timestamp" 154 +" otp_utime=current_timestamp"
124 +" FROM clipperz.otpstatus AS s, clipperz.theotp AS o" 155 +" FROM clipperz.otpstatus AS s, clipperz.theotp AS o"
125 +" WHERE" 156 +" WHERE"
126 +" o.otp_id=otp.otp_id AND otp.otps_id=s.otps_id" 157 +" o.otp_id=otp.otp_id AND otp.otps_id=s.otps_id"
127 +" AND otp.otp_id=$1 AND otp.u_id=$2" 158 +" AND otp.otp_id=$1 AND otp.u_id=$2"
@@ -487,51 +518,55 @@ var CLIPPERZ = module.exports = function(CONFIG) {
487 "SELECT" 518 "SELECT"
488 +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime," 519 +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime,"
489 +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime" 520 +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime"
490 +" FROM" 521 +" FROM"
491 +" clipperz.therecord AS r" 522 +" clipperz.therecord AS r"
492 +" LEFT JOIN clipperz.therecordversion AS rv USING (r_id)" 523 +" LEFT JOIN clipperz.therecordversion AS rv USING (r_id)"
493 +" WHERE r.u_id=$1" 524 +" WHERE r.u_id=$1"
494 +" ORDER BY r.r_id ASC, rv.rv_id ASC", [req.session.u],function(e,r) { 525 +" ORDER BY r.r_id ASC, rv.rv_id ASC", [req.session.u],function(e,r) {
495 if(e) return cb(e); 526 if(e) return cb(e);
496 var rv = {}; 527 var rv = {};
497 r.rows.forEach(function(r) { 528 r.rows.forEach(function(r) {
498 if(!rv[r.r_ref]) rv[r.r_ref] = { 529 if(!rv[r.r_ref]) rv[r.r_ref] = {
499 data: r.r_data, version: r.r_version, 530 data: r.r_data, version: r.r_version,
500 creationDate: r.r_ctime.toString(), 531 creationDate: r.r_ctime.toString(),
501 updateDate: r.r_mtime.toString(), 532 updateDate: r.r_mtime.toString(),
502 accessDate: r.r_atime.toString(), 533 accessDate: r.r_atime.toString(),
503 versions: {} 534 versions: {}
504 }; 535 };
505 if(!r.rv_id) return; 536 if(!r.rv_id) return;
506 rv[r.r_ref].versions[rv[r.r_ref].currentVersion=r.rv_ref] = { 537 rv[r.r_ref].versions[rv[r.r_ref].currentVersion=r.rv_ref] = {
507 header: r.rv_header, data: r.rv_data, version: r.rv_version, 538 header: r.rv_header, data: r.rv_data, version: r.rv_version,
508 creationDate: r.rv_ctime.toString(), 539 creationDate: r.rv_ctime.toString(),
509 updateDate: r.rv_mtime.toString(), 540 updateDate: r.rv_mtime.toString(),
510 accessDate: r.rv_atime.toString() 541 accessDate: r.rv_atime.toString()
511 }; 542 };
512 }); 543 });
513 cb(null,rv); 544 cb(null,rv);
514 }); 545 });
515 }, 546 },
516 html: function(cb) { 547 html: function(cb) {
517 FS.readFile(CONFIG.dump_template,{encoding:'utf-8'},cb); 548 FS.readFile(CONFIG.dump_template,{encoding:'utf-8'},cb);
518 } 549 }
519 },function(e,r) { 550 },function(e,r) {
520 if(e) return cb(e); 551 if(e) return cb(e);
521 var d = new Date(); 552 var d = new Date();
522 res.attachment('Clipperz_'+d.getFullYear()+'_'+(d.getMonth()+1)+'_'+d.getDate()+'.html'); 553 res.attachment('Clipperz_'+d.getFullYear()+'_'+(d.getMonth()+1)+'_'+d.getDate()+'.html');
523 var ojs = { users: { 554 var ojs = { users: {
524 catchAllUser: { __masterkey_test_value__: 'masterkey', s: n123, v: n123 } 555 catchAllUser: { __masterkey_test_value__: 'masterkey', s: n123, v: n123 }
525 } }; 556 } };
526 r.u.d.records = r.records; 557 r.u.d.records = r.records;
527 ojs.users[r.u.u] = r.u.d; 558 ojs.users[r.u.u] = r.u.d;
528 res.send(r.html.replace('/*offline_data_placeholder*/', 559 res.send(r.html.replace('/*offline_data_placeholder*/',
529 "_clipperz_dump_data_="+JSON.stringify(ojs) 560 "_clipperz_dump_data_="+JSON.stringify(ojs)
530 +";" 561 +";"
531 +"Clipperz.PM.Proxy.defaultProxy = new Clipperz.PM.Proxy.Offline();" 562 +"Clipperz.PM.Proxy.defaultProxy = new Clipperz.PM.Proxy.Offline();"
532 +"Clipperz.Crypto.PRNG.defaultRandomGenerator().fastEntropyAccumulationForTestingPurpose();")); 563 +"Clipperz.Crypto.PRNG.defaultRandomGenerator().fastEntropyAccumulationForTestingPurpose();"));
533 }); 564 });
534 } 565 }
566
535 }; 567 };
568 rv.__defineGetter__('session_store',function(){ return function(o) { return new (clipperz_store(PG))(o) } });
569
570 return rv;
536 571
537}; 572};
diff --git a/backend/node/src/clipperz.schema.sql b/backend/node/src/clipperz.schema.sql
index ba6f482..1c2305c 100644
--- a/backend/node/src/clipperz.schema.sql
+++ b/backend/node/src/clipperz.schema.sql
@@ -13,48 +13,55 @@ CREATE TABLE clipperz.theuser (
13); 13);
14 14
15CREATE TABLE clipperz.therecord ( 15CREATE TABLE clipperz.therecord (
16 r_id serial PRIMARY KEY, 16 r_id serial PRIMARY KEY,
17 u_id integer NOT NULL REFERENCES clipperz.theuser(u_id) ON UPDATE CASCADE ON DELETE CASCADE, 17 u_id integer NOT NULL REFERENCES clipperz.theuser(u_id) ON UPDATE CASCADE ON DELETE CASCADE,
18 r_ref varchar NOT NULL UNIQUE, 18 r_ref varchar NOT NULL UNIQUE,
19 r_data varchar NOT NULL, 19 r_data varchar NOT NULL,
20 r_version varchar NOT NULL, 20 r_version varchar NOT NULL,
21 r_ctime timestamp NOT NULL DEFAULT current_timestamp, 21 r_ctime timestamp NOT NULL DEFAULT current_timestamp,
22 r_mtime timestamp NOT NULL DEFAULT current_timestamp, 22 r_mtime timestamp NOT NULL DEFAULT current_timestamp,
23 r_atime timestamp NOT NULL DEFAULT current_timestamp 23 r_atime timestamp NOT NULL DEFAULT current_timestamp
24); 24);
25CREATE INDEX therecord_u_id_key ON clipperz.therecord (u_id); 25CREATE INDEX therecord_u_id_key ON clipperz.therecord (u_id);
26 26
27CREATE TABLE clipperz.therecordversion ( 27CREATE TABLE clipperz.therecordversion (
28 rv_id serial PRIMARY KEY, 28 rv_id serial PRIMARY KEY,
29 r_id integer NOT NULL REFERENCES clipperz.therecord (r_id) ON UPDATE CASCADE ON DELETE CASCADE, 29 r_id integer NOT NULL REFERENCES clipperz.therecord (r_id) ON UPDATE CASCADE ON DELETE CASCADE,
30 rv_ref varchar NOT NULL UNIQUE, 30 rv_ref varchar NOT NULL UNIQUE,
31 rv_header varchar, 31 rv_header varchar,
32 rv_data varchar NOT NULL, 32 rv_data varchar NOT NULL,
33 rv_version varchar NOT NULL, 33 rv_version varchar NOT NULL,
34 rv_previous_key varchar NOT NULL, 34 rv_previous_key varchar NOT NULL,
35 rv_previous_id varchar, 35 rv_previous_id varchar,
36 rv_ctime timestamp NOT NULL DEFAULT current_timestamp, 36 rv_ctime timestamp NOT NULL DEFAULT current_timestamp,
37 rv_mtime timestamp NOT NULL DEFAULT current_timestamp, 37 rv_mtime timestamp NOT NULL DEFAULT current_timestamp,
38 rv_atime timestamp NOT NULL DEFAULT current_timestamp 38 rv_atime timestamp NOT NULL DEFAULT current_timestamp
39); 39);
40 40
41CREATE TABLE clipperz.otpstatus ( 41CREATE TABLE clipperz.otpstatus (
42 otps_id serial PRIMARY KEY, 42 otps_id serial PRIMARY KEY,
43 otps_code varchar NOT NULL, 43 otps_code varchar NOT NULL,
44 otps_name varchar NOT NULL, 44 otps_name varchar NOT NULL,
45 otps_desc varchar NOT NULL 45 otps_desc varchar NOT NULL
46); 46);
47 47
48CREATE TABLE clipperz.theotp ( 48CREATE TABLE clipperz.theotp (
49 otp_id serial PRIMARY KEY, 49 otp_id serial PRIMARY KEY,
50 u_id integer REFERENCES clipperz.theuser (u_id) ON UPDATE CASCADE ON DELETE CASCADE, 50 u_id integer REFERENCES clipperz.theuser (u_id) ON UPDATE CASCADE ON DELETE CASCADE,
51 otps_id integer REFERENCES clipperz.otpstatus (otps_id) ON UPDATE CASCADE ON DELETE CASCADE, 51 otps_id integer REFERENCES clipperz.otpstatus (otps_id) ON UPDATE CASCADE ON DELETE CASCADE,
52 otp_ref varchar NOT NULL UNIQUE, 52 otp_ref varchar NOT NULL UNIQUE,
53 otp_key varchar NOT NULL UNIQUE, 53 otp_key varchar NOT NULL UNIQUE,
54 otp_key_checksum varchar NOT NULL, 54 otp_key_checksum varchar NOT NULL,
55 otp_data varchar NOT NULL, 55 otp_data varchar NOT NULL,
56 otp_version varchar NOT NULL, 56 otp_version varchar NOT NULL,
57 otp_ctime timestamp NOT NULL DEFAULT current_timestamp, 57 otp_ctime timestamp NOT NULL DEFAULT current_timestamp,
58 otp_rtime timestamp NOT NULL DEFAULT current_timestamp, 58 otp_rtime timestamp NOT NULL DEFAULT current_timestamp,
59 otp_utime timestamp NOT NULL DEFAULT current_timestamp 59 otp_utime timestamp NOT NULL DEFAULT current_timestamp
60); 60);
61
62CREATE TABLE clipperz.thesession (
63 s_id varchar PRIMARY KEY,
64 s_data json,
65 s_ctime timestamp DEFAULT current_timestamp,
66 s_mtime timestamp DEFAULT current_timestamp
67);