Playing around mit Java und Hibernate

version: "3.1"

services:
  db:
    image: postgres
    command: ["postgres", "-c", "logging_collector=on", "-c", "log_directory=/var/log/postgresql", "-c", "log_filename=postgresql.log", "-c", "log_statement=all"]
    restart: always
    environment:
      POSTGRES_PASSWORD: postgres
    volumes:
      - postgres-volume:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

    ports:
      - 5432:5432
      
volumes:
  postgres-volume:

CREATE SCHEMA IF NOT EXISTS playground;

DROP TABLE IF EXISTS playground.edges;
DROP TABLE IF EXISTS playground.nodes;

CREATE TABLE playground.nodes
(
    id SERIAL PRIMARY KEY,
    node_name VARCHAR(10) NOT NULL
);

CREATE TABLE playground.edges
(
    id SERIAL PRIMARY KEY,
    from_node_id INTEGER NOT NULL REFERENCES playground.nodes(id),
    to_node_id INTEGER NOT NULL REFERENCES playground.nodes(id),
    type VARCHAR(20) NOT NULL
);

@Entity
@Table(name = "nodes", schema = "playground", catalog = "postgres")
public class NodesEntity {
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Basic
    @Column(name = "node_name", nullable = false, length = 10)
    private String nodeName;

    @OneToMany(mappedBy = "fromNode", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public Set<EdgesEntity> edges = new LinkedHashSet<>();

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public String getNodeName() { return nodeName; }
    public void setNodeName(String nodeName) { this.nodeName = nodeName; }
}

@Entity
@Table(name = "edges", schema = "playground", catalog = "postgres")
public class EdgesEntity {
    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @ManyToOne
    @JoinColumn(name = "from_node_id", nullable = false)
    private NodesEntity fromNode;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "to_node_id", nullable = false)
    private NodesEntity toNode;

    @Basic
    @Column(name = "type", nullable = false, length = 20)
    private String type;

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public NodesEntity getFromNode() { return fromNode; }
    public void setFromNode(NodesEntity fromNode) { this.fromNode = fromNode; }

    public NodesEntity getToNode() { return toNode; }
    public void setToNode(NodesEntity toNode) { this.toNode = toNode; }

    public String getType() { return type; }
    public void setType(String type) { this.type = type; }
}

public static void main(final String[] args) throws Exception {
    final Session session = getSession();
    try {
        NodesEntity nodeA = new NodesEntity();
        nodeA.setNodeName("A");

        NodesEntity nodeB = new NodesEntity();
        nodeB.setNodeName("B");

        EdgesEntity entityAToB = new EdgesEntity();
        entityAToB.setFromNode(nodeA);
        entityAToB.setToNode(nodeB);
        entityAToB.setType("parent");

        nodeA.edges.add(entityAToB);

        EdgesEntity entityBToA = new EdgesEntity();
        entityBToA.setFromNode(nodeB);
        entityBToA.setToNode(nodeA);
        entityBToA.setType("child");

        nodeB.edges.add(entityBToA);

        session.save(nodeA);
    } finally {
        session.close();
    }
}

CREATE INDEX edges_from_node_id_to_node_id_idx ON playground.edges(from_node_id,to_node_id);

INSERT INTO playground.nodes (node_name) VALUES(chr(generate_series(65,75))); -- A..K
INSERT INTO playground.edges (from_node_id, to_node_id, type)
  VALUES
    (1,2,'parent'),(2,3,'parent'),(3,1,'parent'),(3,10,'parent'),(10,3,'child'),
    (7,8,'parent'),(8,7,'child');
  
  
CREATE OR REPLACE FUNCTION get_graph(node_id INTEGER)
  RETURNS TABLE (node_id INTEGER)
  LANGUAGE SQL
AS $$
  WITH RECURSIVE traverse(id, path, cycle) AS (
    SELECT
      playground.edges.from_node_id,
      ARRAY[playground.edges.from_node_id],
      false
    FROM playground.edges
    WHERE playground.edges.from_node_id = node_id
    UNION ALL
    SELECT
      playground.edges.to_node_id, 
      traverse.path || playground.edges.to_node_id,
      playground.edges.to_node_id = ANY(traverse.path)
    FROM traverse
    INNER JOIN playground.edges
    ON playground.edges.from_node_id = traverse.id 
    WHERE NOT cycle 
  )
  SELECT id FROM traverse GROUP BY id ORDER BY MIN(CARDINALITY(path))
$$;

List<Integer> nodeIds = session.createSQLQuery("SELECT get_graph(3)").list();

MultiIdentifierLoadAccess<NodesEntity> multiLoadAccess = session.byMultipleIds(NodesEntity.class);
List<NodesEntity> nodes = multiLoadAccess.multiLoad(nodeIds);

for (NodesEntity node:nodes) {
    System.out.println(node.getNodeName());

    for(EdgesEntity edge : node.edges) {
        System.out.println(String.format("   %s (%d) -> %s (%d) %s",
                edge.getFromNode().getNodeName(),
                edge.getFromNode().getId(),
                edge.getToNode().getNodeName(),
                edge.getToNode().getId(),
                edge.getType()));
    }
}

/*
Hibernate: 
    select
        nodesentit0_.id as id1_1_0_,
        nodesentit0_.node_name as node_nam2_1_0_,
        edges1_.from_node_id as from_nod3_0_1_,
        edges1_.id as id1_0_1_,
        edges1_.id as id1_0_2_,
        edges1_.from_node_id as from_nod3_0_2_,
        edges1_.to_node_id as to_node_4_0_2_,
        edges1_.type as type2_0_2_,
        nodesentit2_.id as id1_1_3_,
        nodesentit2_.node_name as node_nam2_1_3_ 
    from
        playground.nodes nodesentit0_ 
    left outer join
        playground.edges edges1_ 
            on nodesentit0_.id=edges1_.from_node_id 
    left outer join
        playground.nodes nodesentit2_ 
            on edges1_.to_node_id=nodesentit2_.id 
    where
        nodesentit0_.id in (
            ?, ?, ?, ?
        )
C
   C (3) -> J (10) parent
   C (3) -> A (1) parent
J
   J (10) -> C (3) child
A
   A (1) -> B (2) parent
B
   B (2) -> C (3) parent
*/

CREATE OR REPLACE FUNCTION some_function(some_ids UUID[])
  RETURNS TABLE (id INTEGER)
  LANGUAGE SQL
AS $$

-- ERROR: function some_function(record[]) does not exist

CREATE OR REPLACE FUNCTION some_function(VARIADIC some_ids UUID[])
  RETURNS TABLE (id INTEGER)
  LANGUAGE SQL
AS $$

// Doesnt work:
@Query(nativeQuery = true, value = "SELECT some_function(ARRAY[:uuids])")
LinkedHashSet<Integer> getSomeFunction(Collection<UUID> uuids);

// Works with VARIADIC
@Query(nativeQuery = true, value = "SELECT some_function(:uuids)")
LinkedHashSet<Integer> getSomeFunction(Collection<UUID> uuids);

BEGIN;
  LOCK TABLE new_table IN ACCESS EXCLUSIVE MODE;

  CREATE TABLE new_table_clean AS 
    (SELECT DISTINCT ON(column1, column2) * FROM new_table);
  
  TRUNCATE TABLE new_table;

  INSERT INTO new_table SELECT * FROM new_table_clean;
END;

-- Source: https://www.cybertec-postgresql.com/
SELECT
  relname, seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch,
  seq_tup_read / seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND schemaname='myschema'
ORDER BY seq_tup_read DESC;

-- Richtige Einfügereihenfolge: https://www.cybertec-postgresql.com/en/postgresql-foreign-keys-and-insertion-order-in-sql/
WITH RECURSIVE fkeys AS (
   /* source and target tables for all foreign keys */
   SELECT conrelid AS source,
          confrelid AS target
   FROM pg_constraint
   WHERE contype = 'f'
),
tables AS (
      (   /* all tables ... */
          SELECT oid AS table_name,
                 1 AS level,
                 ARRAY[oid] AS trail,
                 FALSE AS circular
          FROM pg_class
          WHERE relkind = 'r'
            AND NOT relnamespace::regnamespace::text LIKE ANY
                    (ARRAY['pg_catalog', 'information_schema', 'pg_temp_%'])
       EXCEPT
          /* ... except the ones that have a foreign key */
          SELECT source,
                 1,
                 ARRAY[ source ],
                 FALSE
          FROM fkeys
      )
   UNION ALL
      /* all tables with a foreign key pointing a table in the working set */
      SELECT fkeys.source,
             tables.level + 1,
             tables.trail || fkeys.source,
             tables.trail @> ARRAY[fkeys.source]
      FROM fkeys
         JOIN tables ON tables.table_name = fkeys.target
      /*
       * Stop when a table appears in the trail the third time.
       * This way, we get the table once with "circular = TRUE".
       */
      WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2
),
ordered_tables AS (
   /* get the highest level per table */
   SELECT DISTINCT ON (table_name)
          table_name,
          level,
          circular
   FROM tables
   ORDER BY table_name, level DESC
)
SELECT table_name::regclass,
       level
FROM ordered_tables
WHERE NOT circular
ORDER BY level, table_name;