Merge branch 'release'

zbryikt · zbryikt · commit 955dbd84eb20 · 2020-05-25T15:42:37.000+08:00
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -0,0 +1,20 @@
+## 3.0.0
+
+Thanks to @billwashere, we upgraded to a more modern version of `pg` (4.5.1 ->
+7.4.1), which means that the way the database information is provided has
+changed. Previously, you could pass a URL to sharedb-postgres, like:
+
+```js
+const db = require('sharedb-postgres')('postgres://localhost/mydb');
+```
+
+This is no longer supported, and you must instead pass a config object:
+
+```js
+const db = require('sharedb-postgres')({host: 'localhost', database: 'mydb'});
+```
+
+See the [node-postgres](https://node-postgres.com/features/connecting)
+documentation for more details about what can be passed in the config object.
+Additionally, if no object is provided, `pg` will use the same environment
+variables as `libpq` (`PGUSER`, `PGHOST` and so on).
diff --git a/README.md b/README.md
@@ -51,16 +51,15 @@ PGUSER=dbuser  PGPASSWORD=secretpassword PGHOST=database.server.com PGDATABASE=m
 Example using an object
 
 ```js
-var db = require('sharedb-postgres')({
-  user: 'dbuser',
-  host: 'database.server.com',
-  database: 'mydb',
-  password: 'secretpassword',
-  port: 5433,
-});
+var db = require('sharedb-postgres')({host: 'localhost', database: 'mydb'});
 var backend = require('sharedb')({db: db})
 ```
 
 ## Error codes
 
 PostgreSQL errors are passed back directly.
+
+## Changelog
+
+Note that version 3.0.0 introduces breaking changes in how you specify
+connection parameters. See the [changelog](CHANGELOG.md) for more info.
diff --git a/index.js b/index.js
@@ -10,14 +10,16 @@ function PostgresDB(options) {
   this.closed = false;
 
   this.pg_config = options;
-    this.pool = new pg.Pool(this.pg_config)
+  this.pool = new pg.Pool(options);
 };
 module.exports = PostgresDB;
 
 PostgresDB.prototype = Object.create(DB.prototype);
 
 PostgresDB.prototype.close = function(callback) {
   this.closed = true;
+  this.pool.end();
+  
   if (callback) callback();
 };
 
@@ -41,30 +43,63 @@ PostgresDB.prototype.commit = function(collection, id, op, snapshot, options, ca
         callback(err);
         return;
       }
-    /*const*/ var query = {
+    /*
+    * This query uses common table expression to upsert the snapshot table 
+    * (iff the new version is exactly 1 more than the latest table or if
+    * the document id does not exists)
+    *
+    * It will then insert into the ops table if it is exactly 1 more than the 
+    * latest table or it the first operation and iff the previous insert into
+    * the snapshot table is successful.
+    *
+    * This result of this query the version of the newly inserted operation
+    * If either the ops or the snapshot insert fails then 0 rows are returned
+    *
+    * If 0 zeros are return then the callback must return false   
+    *
+    * Casting is required as postgres thinks that collection and doc_id are
+    * not varchar  
+    */  
+    const query = {
       name: 'sdb-commit-op-and-snap',
-      text: `With snaps as (
-        Insert into snapshots (collection,doc_id,doc_type, version,data)
-        Select n.* From ( select $1 c, $2 d, $4 t, $3::integer v, $5::jsonb daa)
-        n 
-        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)
-        On conflict(collection, doc_id) do update set version = $3, data = $5 , doc_type = $4
-        Returning version
-        ) 
-        Insert into ops (collection,doc_id, version,operation)
-        Select n.* From ( select $1 c, $2 t, $3::integer v, $6::jsonb daa)
-        n 
-        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)
-        Returning version`,
+      text: `WITH snapshot_id AS (
+  INSERT INTO snapshots (collection, doc_id, doc_type, version, data)
+  SELECT $1::varchar collection, $2::varchar doc_id, $4 doc_type, $3 v, $5 d
+  WHERE $3 = (
+    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
+  )
+  ON CONFLICT (collection, doc_id) DO UPDATE SET version = $3, data = $5, doc_type = $4
+  RETURNING version
+)
+INSERT INTO ops (collection, doc_id, version, operation)
+SELECT $1::varchar collection, $2::varchar doc_id, $3 v, $6 operation
+WHERE (
+  $3 = (
+    SELECT max(version)+1
+    FROM ops
+    WHERE collection = $1 AND doc_id = $2
+  ) OR NOT EXISTS (
+    SELECT 1
+    FROM ops
+    WHERE collection = $1 AND doc_id = $2
+  )
+) AND EXISTS (SELECT 1 FROM snapshot_id)
+RETURNING version`,
       values: [collection,id,snapshot.v, snapshot.type, snapshot.data,op]
     }
     client.query(query, (err, res) => {
       if (err) {
-        console.log(err.stack)
         callback(err)
       } else if(res.rows.length === 0) {
         done(client);
-        console.log("Unable to commit, not the latest version")
         callback(null,false)
       } 
       else {
diff --git a/package.json b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "sharedb-postgres",
-  "version": "2.0.0",
+  "version": "3.0.0",
   "description": "PostgreSQL adapter for ShareDB",
   "main": "index.js",
   "scripts": {
diff --git a/structure.sql b/structure.sql
@@ -2,7 +2,7 @@ CREATE TABLE IF NOT EXISTS ops (
   collection character varying(255) not null,
   doc_id character varying(255) not null,
   version integer not null,
-  operation json not null, -- {v:0, create:{...}} or {v:n, op:[...]}
+  operation jsonb not null, -- {v:0, create:{...}} or {v:n, op:[...]}
   PRIMARY KEY (collection, doc_id, version)
 );
 
@@ -11,7 +11,7 @@ CREATE TABLE IF NOT EXISTS snapshots (
   doc_id character varying(255) not null,
   doc_type character varying(255) not null,
   version integer not null,
-  data json not null,
+  data jsonb not null,
   PRIMARY KEY (collection, doc_id)
 );
 

Original file line number	Diff line number	Diff line change
`@@ -1,6 +1,6 @@`
`1`	`1`	`{`
`2`	`2`	`"name": "sharedb-postgres",`
`3`		`- "version": "2.0.0",`
	`3`	`+ "version": "3.0.0",`
`4`	`4`	`"description": "PostgreSQL adapter for ShareDB",`
`5`	`5`	`"main": "index.js",`
`6`	`6`	`"scripts": {`