author | Michael Krelin <hacker@klever.net> | 2013-11-27 17:11:26 (UTC) |
---|---|---|
committer | Michael Krelin <hacker@klever.net> | 2013-11-27 17:11:26 (UTC) |
commit | 0f1cc2ac41835ee8fa5dded1593fa95092b54bbe (patch) (unidiff) | |
tree | 8563cd1578aad126c803be452dedd3b9dd9e0921 /backend | |
parent | b59defff1efe85e43850243910007dd1fe3a4ef2 (diff) | |
download | clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.zip clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.tar.gz clipperz-0f1cc2ac41835ee8fa5dded1593fa95092b54bbe.tar.bz2 |
switched postgresql schema from json type to plaintext
-rw-r--r-- | backend/node/src/clipperz.js | 20 | ||||
-rw-r--r-- | backend/node/src/clipperz.schema.sql | 4 |
2 files changed, 13 insertions, 11 deletions
diff --git a/backend/node/src/clipperz.js b/backend/node/src/clipperz.js index b98c00e..b8b4d3e 100644 --- a/backend/node/src/clipperz.js +++ b/backend/node/src/clipperz.js | |||
@@ -1,62 +1,64 @@ | |||
1 | var FS = require('fs'); | 1 | var FS = require('fs'); |
2 | var CRYPTO = require('crypto'); | 2 | var CRYPTO = require('crypto'); |
3 | var BIGNUM = require('bignum'); | 3 | var BIGNUM = require('bignum'); |
4 | var ASYNC = require('async'); | 4 | var ASYNC = require('async'); |
5 | 5 | ||
6 | var express_store = require('express').session.Store; | 6 | var express_store = require('express').session.Store; |
7 | 7 | ||
8 | function clipperz_hash(v) { | 8 | function clipperz_hash(v) { |
9 | return CRYPTO.createHash('sha256').update( | 9 | return CRYPTO.createHash('sha256').update( |
10 | CRYPTO.createHash('sha256').update(v).digest('binary') | 10 | CRYPTO.createHash('sha256').update(v).digest('binary') |
11 | ).digest('hex'); | 11 | ).digest('hex'); |
12 | }; | 12 | }; |
13 | function clipperz_random() { | 13 | function clipperz_random() { |
14 | 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)); |
15 | return r.substr(0,64); | 15 | return r.substr(0,64); |
16 | }; | 16 | }; |
17 | function clipperz_store(PG) { | 17 | function clipperz_store(PG) { |
18 | var rv = function(o) { express_store.call(this,o); } | 18 | var rv = function(o) { express_store.call(this,o); } |
19 | rv.prototype.get = function(sid,cb) { PG.Q( | 19 | rv.prototype.get = function(sid,cb) { PG.Q( |
20 | "SELECT s_data FROM clipperz.thesession WHERE s_id=$1",[sid], | 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); } | 21 | function(e,r) { cb(e,(e||!r.rowCount)?null:JSON.parse(r.rows[0].s_data)); } |
22 | ) }; | 22 | ) }; |
23 | rv.prototype.set = function(sid,data,cb) { PG.Q( | 23 | rv.prototype.set = function(sid,data,cb) { |
24 | "UPDATE clipperz.thesession SET s_data=$1, s_mtime=current_timestamp" | 24 | var d = JSON.stringify(data); |
25 | +" WHERE s_id=$2",[data,sid], function(e,r) { | 25 | PG.Q( |
26 | "UPDATE clipperz.thesession SET s_data=$1, s_mtime=current_timestamp" | ||
27 | +" WHERE s_id=$2",[d,sid], function(e,r) { | ||
26 | if(e) return cb(e); | 28 | if(e) return cb(e); |
27 | if(r.rowCount) return cb(); | 29 | if(r.rowCount) return cb(); |
28 | PG.Q("INSERT INTO clipperz.thesession (s_id,s_data) VALUES ($1,$2)",[sid,data],cb); | 30 | PG.Q("INSERT INTO clipperz.thesession (s_id,s_data) VALUES ($1,$2)",[sid,d],cb); |
29 | } | 31 | }); |
30 | ) }; | 32 | }; |
31 | rv.prototype.destroy = function(sid,cb) { PG.Q( | 33 | rv.prototype.destroy = function(sid,cb) { PG.Q( |
32 | "DELETE FROM clipperz.thesession WHERE s_id=$1",[sid],cb | 34 | "DELETE FROM clipperz.thesession WHERE s_id=$1",[sid],cb |
33 | ) }; | 35 | ) }; |
34 | rv.prototype.length = function(cb) { PG.Q( | 36 | rv.prototype.length = function(cb) { PG.Q( |
35 | "SELECT count(*) AS c FROM clipperz.thesession", function(e,r) { | 37 | "SELECT count(*) AS c FROM clipperz.thesession", function(e,r) { |
36 | cb(e,e?null:r.rows[0].c); | 38 | cb(e,e?null:r.rows[0].c); |
37 | } | 39 | } |
38 | ) }; | 40 | ) }; |
39 | rv.prototype.length = function(cb) { PQ.Q( | 41 | rv.prototype.length = function(cb) { PQ.Q( |
40 | "DELETE FROM clipperz.thesession", cb | 42 | "DELETE FROM clipperz.thesession", cb |
41 | ) }; | 43 | ) }; |
42 | rv.prototype.__proto__ = express_store.prototype; | 44 | rv.prototype.__proto__ = express_store.prototype; |
43 | return rv; | 45 | return rv; |
44 | } | 46 | } |
45 | 47 | ||
46 | var srp_g = BIGNUM(2); | 48 | var srp_g = BIGNUM(2); |
47 | var srp_n = BIGNUM("115b8b692e0e045692cf280b436735c77a5a9e8a9e7ed56c965f87db5b2a2ece3",16); | 49 | var srp_n = BIGNUM("115b8b692e0e045692cf280b436735c77a5a9e8a9e7ed56c965f87db5b2a2ece3",16); |
48 | var n123 = '112233445566778899aabbccddeeff00112233445566778899aabbccddeeff00'; | 50 | var n123 = '112233445566778899aabbccddeeff00112233445566778899aabbccddeeff00'; |
49 | 51 | ||
50 | 52 | ||
51 | var CLIPPERZ = module.exports = function(CONFIG) { | 53 | var CLIPPERZ = module.exports = function(CONFIG) { |
52 | 54 | ||
53 | var LOGGER = CONFIG.logger||{trace:function(){}}; | 55 | var LOGGER = CONFIG.logger||{trace:function(){}}; |
54 | 56 | ||
55 | var PG = { | 57 | var PG = { |
56 | url: CONFIG.psql, | 58 | url: CONFIG.psql, |
57 | PG: require('pg').native, | 59 | PG: require('pg').native, |
58 | Q: function(q,a,cb) { | 60 | Q: function(q,a,cb) { |
59 | if('function'===typeof a) cb=a,a=[]; | 61 | if('function'===typeof a) cb=a,a=[]; |
60 | LOGGER.trace({query:q,args:a},'SQL: %s',q); | 62 | LOGGER.trace({query:q,args:a},'SQL: %s',q); |
61 | PG.PG.connect(PG.url,function(e,C,D) { | 63 | PG.PG.connect(PG.url,function(e,C,D) { |
62 | if(e) return cb(e); | 64 | if(e) return cb(e); |
@@ -197,65 +199,65 @@ var CLIPPERZ = module.exports = function(CONFIG) { | |||
197 | +" SELECT ss.otps_id FROM clipperz.otpstatus AS ss WHERE ss.otps_code=CASE" | 199 | +" SELECT ss.otps_id FROM clipperz.otpstatus AS ss WHERE ss.otps_code=CASE" |
198 | +" WHEN otp.otp_key_checksum=$2 THEN 'REQUESTED'" | 200 | +" WHEN otp.otp_key_checksum=$2 THEN 'REQUESTED'" |
199 | +" ELSE 'DISABLED' END" | 201 | +" ELSE 'DISABLED' END" |
200 | +" ) END," | 202 | +" ) END," |
201 | +" otp_data = CASE WHEN s.otps_code='ACTIVE' THEN '' ELSE otp.otp_data END," | 203 | +" otp_data = CASE WHEN s.otps_code='ACTIVE' THEN '' ELSE otp.otp_data END," |
202 | +" otp_utime = current_timestamp," | 204 | +" otp_utime = current_timestamp," |
203 | +" otp_rtime = CASE WHEN otp.otp_key_checksum=$2 THEN current_timestamp ELSE otp.otp_rtime END" | 205 | +" otp_rtime = CASE WHEN otp.otp_key_checksum=$2 THEN current_timestamp ELSE otp.otp_rtime END" |
204 | +" FROM clipperz.otpstatus AS s, clipperz.theotp AS o" | 206 | +" FROM clipperz.otpstatus AS s, clipperz.theotp AS o" |
205 | +" WHERE" | 207 | +" WHERE" |
206 | +" o.otp_id=otp.otp_id AND otp.otps_id=s.otps_id AND otp.otp_key=$1" | 208 | +" o.otp_id=otp.otp_id AND otp.otps_id=s.otps_id AND otp.otp_key=$1" |
207 | +" RETURNING otp.u_id, s.otps_code, otp.otp_id, otp.otp_key_checksum, o.otp_data, otp.otp_version", | 209 | +" RETURNING otp.u_id, s.otps_code, otp.otp_id, otp.otp_key_checksum, o.otp_data, otp.otp_version", |
208 | [ ppp.oneTimePasswordKey, ppp.oneTimePasswordKeyChecksum ], | 210 | [ ppp.oneTimePasswordKey, ppp.oneTimePasswordKeyChecksum ], |
209 | function(e,r) { | 211 | function(e,r) { |
210 | if(e) return cb(e); | 212 | if(e) return cb(e); |
211 | if(!r.rowCount) return cb(new Error('OTP not found')); | 213 | if(!r.rowCount) return cb(new Error('OTP not found')); |
212 | r=r.rows[0]; | 214 | r=r.rows[0]; |
213 | if(r.otp_key_checksum!=ppp.oneTimePasswordKeyChecksum) | 215 | if(r.otp_key_checksum!=ppp.oneTimePasswordKeyChecksum) |
214 | return cb(new Error('OTP was disabled because of checksum mismatch')); | 216 | return cb(new Error('OTP was disabled because of checksum mismatch')); |
215 | if(r.otps_code!='ACTIVE') | 217 | if(r.otps_code!='ACTIVE') |
216 | return cb(new Error("OTP wasn't active, sorry")); | 218 | return cb(new Error("OTP wasn't active, sorry")); |
217 | req.session.u=r.u_id; req.session.otp=r.otp_id; | 219 | req.session.u=r.u_id; req.session.otp=r.otp_id; |
218 | res.res({data:r.otp_data,version:r.otp_version}); | 220 | res.res({data:r.otp_data,version:r.otp_version}); |
219 | }); | 221 | }); |
220 | } | 222 | } |
221 | break; | 223 | break; |
222 | 224 | ||
223 | case 'message': | 225 | case 'message': |
224 | if(!req.session.K) return res.res({result:'EXCEPTION',message:"effectively, we're missing a aconnection"}); | 226 | if(!req.session.K) return res.res({result:'EXCEPTION',message:"effectively, we're missing a aconnection"}); |
225 | if(req.session.K!=pp.srpSharedSecret) return res.res({error:'Wrong shared secret!'}); | 227 | if(req.session.K!=pp.srpSharedSecret) return res.res({error:'Wrong shared secret!'}); |
226 | switch(message) { | 228 | switch(message) { |
227 | case 'getUserDetails': return ASYNC.parallel({ | 229 | case 'getUserDetails': return ASYNC.parallel({ |
228 | u: function(cb) { | 230 | u: function(cb) { |
229 | PG.Q("SELECT u_header::varchar,u_statistics,u_version FROM clipperz.theuser WHERE u_id=$1", | 231 | PG.Q("SELECT u_header,u_statistics,u_version FROM clipperz.theuser WHERE u_id=$1", |
230 | [req.session.u],function(e,r) { | 232 | [req.session.u],function(e,r) { |
231 | if(e) return cb(e); | 233 | if(e) return cb(e); |
232 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); | 234 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); |
233 | cb(null,r.rows[0]); | 235 | cb(null,r.rows[0]); |
234 | }); | 236 | }); |
235 | }, | 237 | }, |
236 | stats: function(cb) { | 238 | stats: function(cb) { |
237 | PG.Q("SELECT r_ref,r_mtime FROM clipperz.therecord WHERE u_id=$1", | 239 | PG.Q("SELECT r_ref,r_mtime FROM clipperz.therecord WHERE u_id=$1", |
238 | [req.session.u],function(e,r) { | 240 | [req.session.u],function(e,r) { |
239 | if(e) return cb(e); | 241 | if(e) return cb(e); |
240 | cb(null,r.rows.reduce(function(p,r){p[r.r_ref]={updateDate:r.r_mtime};return p},{})); | 242 | cb(null,r.rows.reduce(function(p,r){p[r.r_ref]={updateDate:r.r_mtime};return p},{})); |
241 | }); | 243 | }); |
242 | } | 244 | } |
243 | },function(e,r) { | 245 | },function(e,r) { |
244 | if(e) return cb(e); | 246 | if(e) return cb(e); |
245 | res.res({header:r.u.u_header,statistics:r.u.u_statistics,version:r.u.u_version,recordsStats:r.stats}); | 247 | res.res({header:r.u.u_header,statistics:r.u.u_statistics,version:r.u.u_version,recordsStats:r.stats}); |
246 | }); | 248 | }); |
247 | 249 | ||
248 | case 'saveChanges': return PG.T(function(e,T) { | 250 | case 'saveChanges': return PG.T(function(e,T) { |
249 | if(e) return cb(e); | 251 | if(e) return cb(e); |
250 | ASYNC.auto({ | 252 | ASYNC.auto({ |
251 | user: function(cb) { | 253 | user: function(cb) { |
252 | T.Q( | 254 | T.Q( |
253 | "UPDATE clipperz.theuser" | 255 | "UPDATE clipperz.theuser" |
254 | +" SET u_header=$1, u_statistics=$2, u_version=$3, u_lock=COALESCE($4,u_lock)" | 256 | +" SET u_header=$1, u_statistics=$2, u_version=$3, u_lock=COALESCE($4,u_lock)" |
255 | +" WHERE u_id=$5" | 257 | +" WHERE u_id=$5" |
256 | +" RETURNING u_lock",[ppp.user.header,ppp.user.statistics,ppp.user.version,ppp.user.lock||null,req.session.u], | 258 | +" RETURNING u_lock",[ppp.user.header,ppp.user.statistics,ppp.user.version,ppp.user.lock||null,req.session.u], |
257 | function(e,r) { | 259 | function(e,r) { |
258 | if(e) return cb(e); | 260 | if(e) return cb(e); |
259 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); | 261 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); |
260 | cb(null,r.rows[0]); | 262 | cb(null,r.rows[0]); |
261 | }); | 263 | }); |
@@ -473,65 +475,65 @@ var CLIPPERZ = module.exports = function(CONFIG) { | |||
473 | } | 475 | } |
474 | },function(e,r) { | 476 | },function(e,r) { |
475 | T.end(e, function(e) { | 477 | T.end(e, function(e) { |
476 | if(e) return cb(e); | 478 | if(e) return cb(e); |
477 | res.res({result:'done',lock:r.user.u_lock}); | 479 | res.res({result:'done',lock:r.user.u_lock}); |
478 | }); | 480 | }); |
479 | }); | 481 | }); |
480 | }); | 482 | }); |
481 | 483 | ||
482 | case 'deleteUser': return PG.Q( | 484 | case 'deleteUser': return PG.Q( |
483 | "DELETE FROM clipperz.theuser WHERE u_id=$1", | 485 | "DELETE FROM clipperz.theuser WHERE u_id=$1", |
484 | [req.session.u],function(e,r) { | 486 | [req.session.u],function(e,r) { |
485 | if(e) return cb(e); | 487 | if(e) return cb(e); |
486 | res.res({result:'ok'}); | 488 | res.res({result:'ok'}); |
487 | }); | 489 | }); |
488 | 490 | ||
489 | case 'echo': return res.res({result:ppp}); | 491 | case 'echo': return res.res({result:ppp}); |
490 | case 'getOneTimePasswordsDetails': return res.res({}); | 492 | case 'getOneTimePasswordsDetails': return res.res({}); |
491 | case 'getLoginHistory': return res.res({result:[]}); | 493 | case 'getLoginHistory': return res.res({result:[]}); |
492 | } | 494 | } |
493 | break; | 495 | break; |
494 | case 'logout': return req.session.destroy(function(e){res.res({})}); | 496 | case 'logout': return req.session.destroy(function(e){res.res({})}); |
495 | } | 497 | } |
496 | cb(); | 498 | cb(); |
497 | }, | 499 | }, |
498 | 500 | ||
499 | dump: function(req,res,cb) { | 501 | dump: function(req,res,cb) { |
500 | if(!req.session.u) return cb(new Error('logging in helps')); | 502 | if(!req.session.u) return cb(new Error('logging in helps')); |
501 | return ASYNC.parallel({ | 503 | return ASYNC.parallel({ |
502 | u: function(cb) { | 504 | u: function(cb) { |
503 | PG.Q( | 505 | PG.Q( |
504 | "SELECT" | 506 | "SELECT" |
505 | +" u_name, u_srp_s, u_srp_v, u_authversion, u_header::varchar, u_statistics, u_version" | 507 | +" u_name, u_srp_s, u_srp_v, u_authversion, u_header, u_statistics, u_version" |
506 | +" FROM clipperz.theuser WHERE u_id=$1",[req.session.u],function(e,r) { | 508 | +" FROM clipperz.theuser WHERE u_id=$1",[req.session.u],function(e,r) { |
507 | if(e) return cb(e); | 509 | if(e) return cb(e); |
508 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); | 510 | if(!r.rowCount) return cb(new Error("user's gone AWOL")); |
509 | r = r.rows[0]; | 511 | r = r.rows[0]; |
510 | return cb(null,{u:r.u_name,d:{s:r.u_srp_s,v:r.u_srp_v, version:r.u_authversion, | 512 | return cb(null,{u:r.u_name,d:{s:r.u_srp_s,v:r.u_srp_v, version:r.u_authversion, |
511 | maxNumberOfRecords: '100', userDetails: r.u_header, | 513 | maxNumberOfRecords: '100', userDetails: r.u_header, |
512 | statistics: r.u_statistics, userDetailsVersion: r.u_version | 514 | statistics: r.u_statistics, userDetailsVersion: r.u_version |
513 | }}); | 515 | }}); |
514 | }); | 516 | }); |
515 | }, | 517 | }, |
516 | records: function(cb) { | 518 | records: function(cb) { |
517 | PG.Q( | 519 | PG.Q( |
518 | "SELECT" | 520 | "SELECT" |
519 | +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime," | 521 | +" r.r_id, r.r_ref, r_data, r_version, r_ctime, r_mtime, r_atime," |
520 | +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime" | 522 | +" rv.rv_id, rv.rv_ref AS rv_ref, rv_header, rv_data, rv_version, rv_ctime, rv_mtime, rv_atime" |
521 | +" FROM" | 523 | +" FROM" |
522 | +" clipperz.therecord AS r" | 524 | +" clipperz.therecord AS r" |
523 | +" LEFT JOIN clipperz.therecordversion AS rv USING (r_id)" | 525 | +" LEFT JOIN clipperz.therecordversion AS rv USING (r_id)" |
524 | +" WHERE r.u_id=$1" | 526 | +" WHERE r.u_id=$1" |
525 | +" ORDER BY r.r_id ASC, rv.rv_id ASC", [req.session.u],function(e,r) { | 527 | +" ORDER BY r.r_id ASC, rv.rv_id ASC", [req.session.u],function(e,r) { |
526 | if(e) return cb(e); | 528 | if(e) return cb(e); |
527 | var rv = {}; | 529 | var rv = {}; |
528 | r.rows.forEach(function(r) { | 530 | r.rows.forEach(function(r) { |
529 | if(!rv[r.r_ref]) rv[r.r_ref] = { | 531 | if(!rv[r.r_ref]) rv[r.r_ref] = { |
530 | data: r.r_data, version: r.r_version, | 532 | data: r.r_data, version: r.r_version, |
531 | creationDate: r.r_ctime.toString(), | 533 | creationDate: r.r_ctime.toString(), |
532 | updateDate: r.r_mtime.toString(), | 534 | updateDate: r.r_mtime.toString(), |
533 | accessDate: r.r_atime.toString(), | 535 | accessDate: r.r_atime.toString(), |
534 | versions: {} | 536 | versions: {} |
535 | }; | 537 | }; |
536 | if(!r.rv_id) return; | 538 | if(!r.rv_id) return; |
537 | rv[r.r_ref].versions[rv[r.r_ref].currentVersion=r.rv_ref] = { | 539 | rv[r.r_ref].versions[rv[r.r_ref].currentVersion=r.rv_ref] = { |
diff --git a/backend/node/src/clipperz.schema.sql b/backend/node/src/clipperz.schema.sql index 1c2305c..591828a 100644 --- a/backend/node/src/clipperz.schema.sql +++ b/backend/node/src/clipperz.schema.sql | |||
@@ -1,67 +1,67 @@ | |||
1 | CREATE SCHEMA clipperz; | 1 | CREATE SCHEMA clipperz; |
2 | 2 | ||
3 | CREATE TABLE clipperz.theuser ( | 3 | CREATE TABLE clipperz.theuser ( |
4 | u_id serial PRIMARY KEY, | 4 | u_id serial PRIMARY KEY, |
5 | u_name varchar NOT NULL UNIQUE, | 5 | u_name varchar NOT NULL UNIQUE, |
6 | u_srp_s varchar NOT NULL, | 6 | u_srp_s varchar NOT NULL, |
7 | u_srp_v varchar NOT NULL, | 7 | u_srp_v varchar NOT NULL, |
8 | u_header json NOT NULL, | 8 | u_header varchar NOT NULL, |
9 | u_statistics varchar NOT NULL, | 9 | u_statistics varchar NOT NULL, |
10 | u_authversion varchar NOT NULL, | 10 | u_authversion varchar NOT NULL, |
11 | u_version varchar NOT NULL, | 11 | u_version varchar NOT NULL, |
12 | u_lock varchar NOT NULL | 12 | u_lock varchar NOT NULL |
13 | ); | 13 | ); |
14 | 14 | ||
15 | CREATE TABLE clipperz.therecord ( | 15 | CREATE 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 | ); |
25 | CREATE INDEX therecord_u_id_key ON clipperz.therecord (u_id); | 25 | CREATE INDEX therecord_u_id_key ON clipperz.therecord (u_id); |
26 | 26 | ||
27 | CREATE TABLE clipperz.therecordversion ( | 27 | CREATE 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 | ||
41 | CREATE TABLE clipperz.otpstatus ( | 41 | CREATE 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 | ||
48 | CREATE TABLE clipperz.theotp ( | 48 | CREATE 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 | 61 | ||
62 | CREATE TABLE clipperz.thesession ( | 62 | CREATE TABLE clipperz.thesession ( |
63 | s_id varchar PRIMARY KEY, | 63 | s_id varchar PRIMARY KEY, |
64 | s_data json, | 64 | s_data varchar, |
65 | s_ctime timestamp DEFAULT current_timestamp, | 65 | s_ctime timestamp DEFAULT current_timestamp, |
66 | s_mtime timestamp DEFAULT current_timestamp | 66 | s_mtime timestamp DEFAULT current_timestamp |
67 | ); | 67 | ); |