/wrike-sql

PoC of SQL implementation on the top of Wrike API

Primary LanguageJava

Wrike SQL

Proof of concept of full-scale SQL over Wrike REST API. This prototype is based on the Trino project and actually holds a custom implementation of Trino's connector. This project is a domain-specific "query engine" with just ~1k LoC which contains a bridge between REST endpoints/models and SQL API.

Why

  1. Compatible "SQL API" which can be exposed along with REST, GraphQL, gRPC, etc
  2. Interchange format can be easily integrated with any database tool via the Trino JDBC driver (DataGrip, DbVisualizer, etc)
  3. Native integration with data analytics tools due to standard SQL types and schema introspection (Tableau, Metabase, etc)
  4. Data mesh with cross-domain data joins. It is possible to transfer, combine and aggregate data from REST API response with any data sources like S3, BigQuery, or Snowflake using a big variety of connectors

How to run

  1. Install JDK 17+
  2. Create an app and permanent token here
  3. ./mvnw -Dtoken=${TOKEN} -Dport=${PORT} -Dtest=TestEmbedded#run test
  4. Connect to jdbc:trino://127.0.0.1:${PORT}

Examples

Schema inspection Schema inspection
Data manipulation using SQL Schema inspection

  • Basic insert

    INSERT INTO wrike.rest.tasks(title) VALUES('Buy a car');

  • Select with filter and sorting

    SELECT * FROM wrike.rest.tasks WHERE createddate > NOW() - INTERVAL '5' MINUTE ORDER BY createddate DESC;

  • Insert with FK

    INSERT INTO wrike.rest.comments(taskid, text) VALUES('', 'Not today');

  • Update from previous and new states

    UPDATE wrike.rest.tasks SET title = title || '?', status = 'Cancelled' WHERE id = '';

  • Delete and subquery

    DELETE FROM wrike.rest.comments WHERE authorid IN (SELECT c.id FROM wrike.rest.contacts c WHERE c.firstname like 'Александр%');

  • Joining and grouping

    SELECT c.firstname, COUNT(*) assigned FROM wrike.rest.tasks t JOIN wrike.rest.contacts c ON contains(t.responsibleids, c.id) GROUP BY c.firstname;
ETL: REST -> PostgreSQL Schema inspection 
   SELECT task.title,
          task.createddate,
          con_author.firstname author,
          listagg(con_responsible.firstname, ', ') WITHIN GROUP (ORDER BY task.title) AS responsibles,
          last_value(com.text) OVER (PARTITION BY task.title ORDER BY com.createddate DESC) last_comment
   FROM wrike.rest.tasks task
   JOIN wrike.rest.contacts con_author ON contains(task.authorids, con_author.id)  
   LEFT JOIN wrike.rest.contacts con_responsible ON contains(task.responsibleids, con_responsible.id)
   LEFT JOIN wrike.rest.comments com ON com.taskid = task.id
   GROUP BY task.title, task.createddate, con_author.firstname, com.createddate, com.text;

Scope of prototype

  • Schema introspection (SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS)
  • Query tasks, folders, contacts, comments with batches
  • API Pagination support
  • JOIN support
  • INSERT support (INSERT INTO tasks(title) VALUES('hello'))
  • PUSH-down static filter by primary key
  • DELETE support (DELETE FROM tasks WHERE id = 'QWERTY')
  • UPDATE support (UPDATE tasks SET title = 'new title' WHERE id = 'QWERTY')
  • UPDATE from previous state (UPDATE tasks SET title = title || '!' WHERE id = 'QWERTY')
  • Run Trino with Wrike plugin in embedded mode