idempotence_and_commutativity_devoxxfr_demo.sql

docker run -t -e POSTGRES_PASSWORD=WHATEVER -e POSTGRES_DB=devoxx_demo -p 5555:5432 --name pg postgres
psql -U postgres -p 5555 -h localhost devoxx_demo

CREATE TABLE IF NOT EXISTS users_stats (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);
CREATE TABLE IF NOT EXISTS dataset_a (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);
CREATE TABLE IF NOT EXISTS dataset_b (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);

TRUNCATE users_stats;
TRUNCATE dataset_a;
TRUNCATE dataset_b;

INSERT INTO dataset_a VALUES 
(1, '[email protected]', 5, 0, now() - INTERVAL '1 days 6 hours'),
(2, '[email protected]', 0, 2, now() - INTERVAL '1 days 3 hours');

INSERT INTO dataset_b VALUES
(3, '[email protected]', 15, 1, now() - INTERVAL '1 days'),
(1, '[email protected]', 6, 3, now() - INTERVAL '1 days');

SELECT * FROM users_stats --table consoluser_idée

SELECT * FROM dataset_a --table temporaire A

BEGIN;
-- Mise à jour de la table consoluser_idée avec le dataset A 
UPDATE users_stats 
  SET 
    nb_dog_ads = dataset_a.nb_dog_ads, 
    nb_castle_ads = dataset_a.nb_castle_ads, 
    version = dataset_a.version
FROM dataset_a 
WHERE 
  users_stats.user_id = dataset_a.user_id AND 
  dataset_a.version > users_stats.version;

-- Insertion des nouveaux utilisateurs acquis via le dataset A 
INSERT INTO users_stats 
  SELECT 
        user_id, 
        email, 
        nb_dog_ads, 
        nb_castle_ads, 
        version
  FROM dataset_a
  WHERE dataset_a.user_id NOT IN (SELECT user_id FROM users_stats);

COMMIT;

SELECT * FROM users_stats

BEGIN;
-- Mise à jour de la table consoluser_idée avec le dataset A 
UPDATE users_stats 
  SET 
    nb_dog_ads = dataset_a.nb_dog_ads, 
    nb_castle_ads = dataset_a.nb_castle_ads, 
    version = dataset_a.version
FROM dataset_a 
WHERE users_stats.user_id = dataset_a.user_id AND 
  dataset_a.version > users_stats.version;

-- Insertion des nouveaux utilisateurs acquis via le dataset A 
INSERT INTO users_stats 
  SELECT 
        user_id, 
        email, 
        nb_dog_ads, 
        nb_castle_ads, 
        version
  FROM dataset_a
  WHERE dataset_a.user_id NOT IN (SELECT user_id FROM users_stats);

COMMIT;

SELECT * FROM dataset_b --table temporaire B


BEGIN;
-- Mise à jour de la table consoluser_idée avec le dataset B
 
UPDATE users_stats 
  SET 
    nb_dog_ads = dataset_b.nb_dog_ads,
    nb_castle_ads = dataset_b.nb_castle_ads, 
    version = dataset_b.version
FROM dataset_b 
WHERE users_stats.user_id = dataset_b.user_id AND 
  dataset_b.version > users_stats.version; 

-- Insertion des nouveaux utilisateurs acquis via le dataset B
INSERT INTO users_stats 
  SELECT 
        user_id, 
        email, 
        nb_dog_ads, 
        nb_castle_ads, 
        version
  FROM dataset_b
  WHERE dataset_b.user_id NOT IN (SELECT user_id FROM users_stats);

COMMIT;

SELECT * FROM users_stats


TRUNCATE users_stats;
BEGIN;
-- Mise à jour de la table consoluser_idée avec le dataset B
 
UPDATE users_stats 
  SET 
    nb_dog_ads = dataset_b.nb_dog_ads,
    nb_castle_ads = dataset_b.nb_castle_ads, 
    version = dataset_b.version
FROM dataset_b 
WHERE users_stats.user_id = dataset_b.user_id AND 
  dataset_b.version > users_stats.version; 

-- Insertion des nouveaux utilisateurs acquis via le dataset B
INSERT INTO users_stats 
  SELECT 
        user_id, 
        email, 
        nb_dog_ads, 
        nb_castle_ads, 
        version
  FROM dataset_b
  WHERE dataset_b.user_id NOT IN (SELECT user_id FROM users_stats);

COMMIT;

BEGIN;
-- Mise à jour de la table consoluser_idée avec le dataset A 
UPDATE users_stats 
  SET 
    nb_dog_ads = dataset_a.nb_dog_ads, 
    nb_castle_ads = dataset_a.nb_castle_ads, 
    version = dataset_a.version
FROM dataset_a 
WHERE users_stats.user_id = dataset_a.user_id AND 
  dataset_a.version > users_stats.version;

-- Insertion des nouveaux utilisateurs acquis via le dataset A 
INSERT INTO users_stats 
  SELECT 
        user_id, 
        email, 
        nb_dog_ads, 
        nb_castle_ads, 
        version
  FROM dataset_a
  WHERE dataset_a.user_id NOT IN (SELECT user_id FROM users_stats);

COMMIT;

SELECT * FROM users_stats