Skip to content

PostgreSQL extension to convert ModeShape BSON to/from JSON

License

Notifications You must be signed in to change notification settings

dalbani/pg-modeshape-bson

Repository files navigation

pg-modeshape-bson

PostgreSQL extension to convert ModeShape BSON to/from JSON.

Purpose

To be able to transparently decode/encode binary BSON data from ModeShape, in order to store it as JSON in PostgreSQL.

How to use

  1. Install the extension in PostgreSQL:
$ git clone --recursive https://github.com/dalbani/pg-modeshape-bson.git
$ cd pg-modeshape-bson
$ make
$ sudo make install
  1. Create a proxy table in PostgreSQL (a.k.a "writable view"):
CREATE TABLE modeshape
(
  id text NOT NULL,
  last_changed timestamp without time zone NOT NULL DEFAULT now(),
  content jsonb NOT NULL,
  CONSTRAINT modeshape_pkey PRIMARY KEY (id)
);


CREATE TABLE modeshape_proxy
(
  id text NOT NULL,
  last_changed timestamp without time zone NOT NULL DEFAULT now(),
  content bytea NOT NULL
);


CREATE OR REPLACE RULE "_RETURN" AS
  ON SELECT TO modeshape_proxy
  DO INSTEAD
  SELECT id,
         last_changed,
         json_text_to_modeshape_bson(content::text) AS content
    FROM modeshape;


CREATE OR REPLACE RULE insert_modeshape_proxy_rule AS
  ON INSERT TO modeshape_proxy
  DO INSTEAD
     INSERT INTO modeshape(id, last_changed, content)
     VALUES (NEW.id,
             NEW.last_changed,
             modeshape_bson_to_json_text(NEW.content)::jsonb);


CREATE OR REPLACE RULE update_modeshape_proxy_rule AS
  ON UPDATE TO modeshape_proxy
  DO INSTEAD
     UPDATE modeshape
        SET last_changed = NEW.last_changed,
            content = modeshape_bson_to_json_text(NEW.content)::jsonb
      WHERE id = OLD.id;


CREATE OR REPLACE RULE delete_modeshape_proxy_rule AS
  ON DELETE TO modeshape_proxy
  DO INSTEAD
     DELETE FROM modeshape
      WHERE id = OLD.id;
  1. Configure ModeShape to use the proxy table:
<db-persistence table-name="modeshape_proxy" url="jdbc:postgresql://localhost:5432/modeshape" driver="org.postgresql.Driver" username="modeshape" password="modeshape" create-on-start="false" compress="false" />

Known issues

While $date properties are almost exclusively stored as an ISO 8601 string in BSON documents produced by ModeShape, it appears that the database record with ID "repository:info" contains a $date property using the binary representation (type 9).

It doesn't seem to cause any problem though (as long as this database record is not written??).

About

PostgreSQL extension to convert ModeShape BSON to/from JSON

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published