Skip to content

Commit 1ed2925

Browse files
billwasherebillwashere
committed
JSONB, PG library, and check max update
1 parent f61be56 commit 1ed2925

File tree

4 files changed

+96
-87
lines changed

4 files changed

+96
-87
lines changed

‎README.md

Lines changed: 44 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,15 +8,56 @@ Doesn't support queries (yet?).
88
Moderately experimental. (This drives [Synaptograph](https://www.synaptograph.com)'s backend, and [@nornagon](https://github.com/nornagon) hasn't noticed any issues so far.)
99

1010

11+
## Requirements
12+
13+
Due to the fix to resolve [high concurency issues](https://github.com/share/sharedb-postgres/issues/1) Postgres 9.5+ is now required.
14+
15+
## Migrating older versions
16+
17+
Older versions of this adaptor used the data type json. You will need to alter the data type prior to using if you are upgrading.
18+
19+
```PLpgSQL
20+
ALTER TABLE ops
21+
ALTER COLUMN operation
22+
SET DATA TYPE jsonb
23+
USING operation::jsonb;
24+
25+
ALTER TABLE snapshots
26+
ALTER COLUMN data
27+
SET DATA TYPE jsonb
28+
USING data::jsonb;
29+
```
30+
1131
## Usage
1232

13-
`sharedb-postgres` wraps native [node-postgres](https://github.com/brianc/node-postgres), and it supports the same configuration options.
33+
`sharedb-postgres-jsonb` wraps native [node-postgres](https://github.com/brianc/node-postgres), and it supports the same configuration options.
1434

1535
To instantiate a sharedb-postgres wrapper, invoke the module and pass in your
16-
PostgreSQL configuration as an argument. For example:
36+
PostgreSQL configuration as an argument or use environmental arguments.
37+
38+
For example using environmental arugments:
39+
40+
```js
41+
var db = require('sharedb-postgres')();
42+
var backend = require('sharedb')({db: db})
43+
```
44+
45+
Then executing via the command line
46+
47+
```
48+
PGUSER=dbuser PGPASSWORD=secretpassword PGHOST=database.server.com PGDATABASE=mydb PGPORT=5433 npm start
49+
```
50+
51+
Example using an object
1752

1853
```js
19-
var db = require('sharedb-postgres')('postgres://localhost/mydb');
54+
var db = require('sharedb-postgres')({
55+
user: 'dbuser',
56+
host: 'database.server.com',
57+
database: 'mydb',
58+
password: 'secretpassword',
59+
port: 5433,
60+
});
2061
var backend = require('sharedb')({db: db})
2162
```
2263

‎index.js

Lines changed: 41 additions & 83 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ function PostgresDB(options) {
1010
this.closed = false;
1111

1212
this.pg_config = options;
13+
this.pool = new pg.Pool(this.pg_config)
1314
};
1415
module.exports = PostgresDB;
1516

@@ -20,11 +21,6 @@ PostgresDB.prototype.close = function(callback) {
2021
if (callback) callback();
2122
};
2223

23-
function rollback(client, done) {
24-
client.query('ROLLBACK', function(err) {
25-
return done(err);
26-
})
27-
}
2824

2925
// Persists an op and snapshot if it is for the next version. Calls back with
3026
// callback(err, succeeded)
@@ -39,91 +35,53 @@ PostgresDB.prototype.commit = function(collection, id, op, snapshot, options, ca
3935
* }
4036
* snapshot: PostgresSnapshot
4137
*/
42-
pg.connect(this.pg_config, function(err, client, done) {
43-
if (err) {
44-
done(client);
45-
callback(err);
46-
return;
47-
}
48-
function commit() {
49-
client.query('COMMIT', function(err) {
50-
done(err);
51-
if (err) {
52-
callback(err);
53-
} else {
54-
callback(null, true);
55-
}
56-
})
38+
this.pool.connect((err, client, done) => {
39+
if (err) {
40+
done(client);
41+
callback(err);
42+
return;
43+
}
44+
/*const*/ var query = {
45+
name: 'sdb-commit-op-and-snap',
46+
text: `With snaps as (
47+
Insert into snapshots (collection,doc_id,doc_type, version,data)
48+
Select n.* From ( select $1 c, $2 d, $4 t, $3::integer v, $5::jsonb daa)
49+
n
50+
where v = (select version+1 v from snapshots where collection = $1 and doc_id = $2 for update) or not exists (select 1 from snapshots where collection = $1 and doc_id = $2 for update)
51+
On conflict(collection, doc_id) do update set version = $3, data = $5 , doc_type = $4
52+
Returning version
53+
)
54+
Insert into ops (collection,doc_id, version,operation)
55+
Select n.* From ( select $1 c, $2 t, $3::integer v, $6::jsonb daa)
56+
n
57+
where (v = (select max(version)+1 v from ops where collection = $1 and doc_id = $2) or not exists (select 1 from ops where collection = $1 and doc_id = $2 for update)) and exists (select 1 from snaps)
58+
Returning version`,
59+
values: [collection,id,snapshot.v, snapshot.type, snapshot.data,op]
5760
}
58-
client.query(
59-
'SELECT max(version) AS max_version FROM ops WHERE collection = $1 AND doc_id = $2',
60-
[collection, id],
61-
function(err, res) {
62-
var max_version = res.rows[0].max_version;
63-
if (max_version == null)
64-
max_version = 0;
65-
if (snapshot.v !== max_version + 1) {
66-
return callback(null, false);
67-
}
68-
client.query('BEGIN', function(err) {
69-
client.query(
70-
'INSERT INTO ops (collection, doc_id, version, operation) VALUES ($1, $2, $3, $4)',
71-
[collection, id, snapshot.v, op],
72-
function(err, res) {
73-
if (err) {
74-
// TODO: if err is "constraint violation", callback(null, false) instead
75-
rollback(client, done);
76-
callback(err);
77-
return;
78-
}
79-
if (snapshot.v === 1) {
80-
client.query(
81-
'INSERT INTO snapshots (collection, doc_id, doc_type, version, data) VALUES ($1, $2, $3, $4, $5)',
82-
[collection, id, snapshot.type, snapshot.v, snapshot.data],
83-
function(err, res) {
84-
// TODO:
85-
// if the insert was successful and did insert, callback(null, true)
86-
// if the insert was successful and did not insert, callback(null, false)
87-
// if there was an error, rollback and callback(error)
88-
if (err) {
89-
rollback(client, done);
90-
callback(err);
91-
return;
92-
}
93-
commit();
94-
}
95-
)
96-
} else {
97-
client.query(
98-
'UPDATE snapshots SET doc_type = $3, version = $4, data = $5 WHERE collection = $1 AND doc_id = $2 AND version = ($4 - 1)',
99-
[collection, id, snapshot.type, snapshot.v, snapshot.data],
100-
function(err, res) {
101-
// TODO:
102-
// if any rows were updated, success
103-
// if 0 rows were updated, rollback and not success
104-
// if error, rollback and not success
105-
if (err) {
106-
rollback(client, done);
107-
callback(err);
108-
return;
109-
}
110-
commit();
111-
}
112-
)
113-
}
114-
}
115-
)
116-
})
61+
client.query(query, (err, res) => {
62+
if (err) {
63+
console.log(err.stack)
64+
callback(err)
65+
} else if(res.rows.length === 0) {
66+
done(client);
67+
console.log("Unable to commit, not the latest version")
68+
callback(null,false)
69+
}
70+
else {
71+
done(client);
72+
console.log(res.rows[0])
73+
callback(null,true)
11774
}
118-
)
119-
})
75+
})
76+
77+
})
12078
};
12179

12280
// Get the named document from the database. The callback is called with (err,
12381
// snapshot). A snapshot with a version of zero is returned if the docuemnt
12482
// has never been created in the database.
12583
PostgresDB.prototype.getSnapshot = function(collection, id, fields, options, callback) {
126-
pg.connect(this.pg_config, function(err, client, done) {
84+
this.pool.connect(function(err, client, done) {
12785
if (err) {
12886
done(client);
12987
callback(err);
@@ -173,7 +131,7 @@ PostgresDB.prototype.getSnapshot = function(collection, id, fields, options, cal
173131
//
174132
// Callback should be called as callback(error, [list of ops]);
175133
PostgresDB.prototype.getOps = function(collection, id, from, to, options, callback) {
176-
pg.connect(this.pg_config, function(err, client, done) {
134+
this.pool.connect(function(err, client, done) {
177135
if (err) {
178136
done(client);
179137
callback(err);

‎package.json

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
"keywords": ["sharedb", "sharejs", "share", "postgres"],
1212
"repository": "share/sharedb-postgres",
1313
"dependencies": {
14-
"pg": "^4.5.1",
14+
"pg": "^7.4.1",
1515
"sharedb": "^1.0.0-beta.7"
1616
}
1717
}

‎structure.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,3 +14,13 @@ CREATE TABLE snapshots (
1414
data json not null,
1515
PRIMARY KEY (collection, doc_id)
1616
);
17+
18+
ALTER TABLE ops
19+
ALTER COLUMN operation
20+
SET DATA TYPE jsonb
21+
USING operation::jsonb;
22+
23+
ALTER TABLE snapshots
24+
ALTER COLUMN data
25+
SET DATA TYPE jsonb
26+
USING data::jsonb;

0 commit comments

Comments
 (0)