@socialgouv/matomo-postgres - npm Package Compare versions

Comparing version 1.3.1 to 1.4.0

package.json

 {
   "name": "@socialgouv/matomo-postgres",
   "description": "Extract visitor events from Matomo API and push to Postgres",
-  "version": "1.3.1",
+  "version": "1.4.0",
   "types": "types/index.d.ts",
@@ -6,0 +6,0 @@ "license": "Apache-2.0",

README.md

@@ -7,4 +7,8 @@ # @socialgouv/matomo-postgres
 
+Use [pg_partman](https://github.com/pgpartman/pg_partman) to partition data by month.
+
 ## Usage
 
+Create the [initial table](./initial.sql) database table then run the following job with correct environment variables.
+
 ```sh
@@ -34,5 +38,5 @@ npx @socialgouv/matomo-postgres
 export MATOMO_KEY=
-export DESTINATION_TABLE=
-export STARTDATE=
-export OFFSET=
+export DESTINATION_TABLE= # optional
+export STARTDATE= # optional
+export OFFSET= # optional
 export PGDATABASE=postgres://postgres:postgres@127.0.0.1:5455/postgres
@@ -39,0 +43,0 @@ yarn start

src/__tests__/index.test.js

@@ -15,3 +15,3 @@ const mock_pgQuery = jest.fn();
 
-const NB_REQUEST_TO_INIT_DB = 21; // Number of query to init DB (createTable.js)
+const NB_REQUEST_TO_INIT_DB = 1; // Number of query to init DB (createTable.js)
 const TEST_DATE = new Date();
@@ -90,7 +90,2 @@
   expect(mock_pgQuery.mock.calls[NB_REQUEST_TO_INIT_DB][0]).toEqual(
-    // call 0 is create table
-    // call 1 is add column usercustomdimension
-    // call 2 is add column action_url
-    // ...
-    //
     "select action_timestamp from matomo order by action_timestamp desc limit 1"
@@ -97,0 +92,0 @@ );

src/createTable.js

@@ -11,4 +11,8 @@ const { Client } = require("pg");
   const table = client.escapeIdentifier(DESTINATION_TABLE);
-  const text = `CREATE TABLE IF NOT EXISTS ${table}
-  (
+  const text = `
+
+CREATE SCHEMA IF NOT EXISTS partman;
+CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
+CREATE TABLE IF NOT EXISTS ${table}
+(
     idsite                      text,
@@ -29,3 +33,3 @@ idvisit                     text,
     serverdateprettyfirstaction date,
-    action_id                   text UNIQUE,
+    action_id                   text,
     action_type                 text,
@@ -37,3 +41,3 @@ action_eventcategory        text,
     action_timespent            text,
-    action_timestamp            timestamp with time zone,
+    action_timestamp            timestamp with time zone DEFAULT now(),
     usercustomproperties        json,
@@ -54,34 +58,9 @@ usercustomdimensions        json,
     action_title                text
-)`;
+) PARTITION BY RANGE (action_timestamp);
+`;
 
   await client.query(text, []);
 
-  const migrations = [
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "usercustomdimensions" json;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "action_url" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "sitesearchkeyword" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "action_title" text;`,
-    `ALTER TABLE IF EXISTS ${table} ALTER COLUMN action_eventvalue TYPE decimal USING action_eventvalue::decimal;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension1" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension2" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension3" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension4" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension5" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension6" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension7" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension8" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension9" text;`,
-    `ALTER TABLE IF EXISTS ${table} ADD COLUMN IF NOT EXISTS "dimension10" text;`,
-    `CREATE INDEX IF NOT EXISTS idx_action_timestamp ON ${table} (action_timestamp);`,
-    `CREATE INDEX IF NOT EXISTS idx_idvisit ON ${table}(idvisit);`,
-    `CREATE INDEX IF NOT EXISTS idx_action_eventcategory ON ${table}(action_eventcategory);`,
-    `CREATE INDEX IF NOT EXISTS idx_action_type ON ${table}(action_type);`,
-    `CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_action_eventaction ON ${table}(action_eventaction);`,
-  ];
+  const migrations = [];
 
-  // --------------------------------------------- //
-  // If you add new query: Don't forget to update  //
-  // const `NB_REQUEST_TO_INIT_DB` (index.test.js) //
-  // --------------------------------------------- //
-
   for (const query of migrations) {
@@ -88,0 +67,0 @@ await client.query(query, []);

No alert changes

Improved metrics

Total package byte prevSize

162899

increased by1.94%

Number of package files

26

increased by13.04%

Number of lines in readme file

45

increased by9.76%

Worsened metrics

Lines of code

979

decreased by-2.59%

No dependency changes