>>>inferred_schema.graphqls
"An RFC-3339 compliant Full Date Scalar"
scalar Date

"A DateTime scalar that handles both full RFC3339 and shorter timestamp formats"
scalar DateTime

"A JSON scalar"
scalar JSON

"24-hour clock time value string in the format `hh:mm:ss` or `hh:mm:ss.sss`."
scalar LocalTime

"A 64-bit signed integer"
scalar Long

type Query {
  """
  Returns the sensor temperature for a sensor by id from most recent.
      Sensor temperature readings are averaged per second.
  """
  SecReading(sensorid: Long!, limit: Int = 10, offset: Int = 0): [SecReading!]
  "Returns the maximum temperature recorded for a given sensor"
  SensorMaxTemp(sensorid: Long, limit: Int = 10, offset: Int = 0): [SensorMaxTemp!]
  SensorReading(limit: Int = 10, offset: Int = 0): [SensorReading!]
}

"""
Returns the sensor temperature for a sensor by id from most recent.
    Sensor temperature readings are averaged per second.
"""
type SecReading {
  sensorid: Long!
  timeSec: DateTime!
  timeStart: DateTime!
  timeEnd: DateTime!
  temp: Float!
}

"Returns the maximum temperature recorded for a given sensor"
type SensorMaxTemp {
  sensorid: Long!
  window_time: DateTime!
  maxTemp: Float!
}

type SensorReading {
  sensorid: Long!
  time: Long!
  temperature: Float!
  humidity: Float!
  timestamp: DateTime!
}

enum _McpMethodType {
  NONE
  TOOL
  RESOURCE
}

enum _RestMethodType {
  NONE
  GET
  POST
}

directive @api(mcp: _McpMethodType, rest: _RestMethodType, uri: String) on QUERY | MUTATION | FIELD_DEFINITION

>>>pipeline_explain.txt
=== SecReading
ID:          default_catalog.default_database.SecReading
Type:        state
Stage:       flink
Primary key: sensorid, timeStart, timeEnd, timeSec
Timestamp:   timeSec
Row count:   ~7e7
---
Schema:
 - sensorid: BIGINT NOT NULL
 - timeSec: TIMESTAMP_LTZ(3) *ROWTIME* NOT NULL
 - timeStart: TIMESTAMP(3) NOT NULL
 - timeEnd: TIMESTAMP(3) NOT NULL
 - temp: DOUBLE NOT NULL
Inputs:
 - default_catalog.default_database.SensorReading
Annotations:
 - features: STREAM_WINDOW_AGGREGATION (feature)
 - stream-root: SensorReading
 - sort: [1 DESC-nulls-last]

=== SensorMaxTemp
ID:          default_catalog.default_database.SensorMaxTemp
Type:        state
Stage:       flink
Primary key: sensorid
Timestamp:   window_time
Row count:   ~2e7
---
Schema:
 - sensorid: BIGINT NOT NULL
 - window_time: TIMESTAMP_LTZ(3) *ROWTIME* NOT NULL
 - maxTemp: DOUBLE NOT NULL
Inputs:
 - default_catalog.default_database._SensorMaxTempWindow

=== SensorReading
ID:          default_catalog.default_database.SensorReading
Type:        stream
Stage:       flink
Primary key: sensorid, time
Timestamp:   timestamp
Row count:   ~1e8
---
Schema:
 - sensorid: BIGINT NOT NULL
 - time: BIGINT NOT NULL
 - temperature: DOUBLE NOT NULL
 - humidity: DOUBLE NOT NULL
 - timestamp: TIMESTAMP_LTZ(3) *ROWTIME* NOT NULL
Inputs:
 - default_catalog.default_database.SensorReading__base
Annotations:
 - stream-root: SensorReading

=== _SensorMaxTempWindow
ID:          default_catalog.default_database._SensorMaxTempWindow
Type:        state
Stage:       flink
Primary key: -
Timestamp:   window_time
Row count:   ~1e8
---
Schema:
 - sensorid: BIGINT NOT NULL
 - window_time: TIMESTAMP_LTZ(3) *ROWTIME* NOT NULL
 - maxTemp: DOUBLE NOT NULL
Inputs:
 - default_catalog.default_database.SecReading
Annotations:
 - features: STREAM_WINDOW_AGGREGATION (feature)

>>>flink-sql-no-functions.sql
CREATE TEMPORARY TABLE `SensorReading__schema` (
  `sensorid` BIGINT NOT NULL,
  `time` BIGINT NOT NULL,
  `temperature` DOUBLE NOT NULL,
  `humidity` DOUBLE NOT NULL
)
WITH (
  'connector' = 'datagen'
);
CREATE TABLE `SensorReading` (
  `timestamp` AS COALESCE(`TO_TIMESTAMP_LTZ`(`time`, 3), TIMESTAMP '1970-01-01 00:00:00.000'),
  PRIMARY KEY (`sensorid`, `time`) NOT ENFORCED,
  WATERMARK FOR `timestamp` AS `timestamp` - INTERVAL '0.001' SECOND
)
WITH (
  'format' = 'flexible-csv',
  'path' = '${DATA_PATH}/sensorreading.csv.gz',
  'connector' = 'filesystem',
  'flexible-csv.skip-header' = 'true'
)
LIKE `SensorReading__schema`;
CREATE VIEW `SecReading`
AS
SELECT `sensorid`, `window_time` AS `timeSec`, `window_start` AS `timeStart`, `window_end` AS `timeEnd`, AVG(`temperature`) AS `temp`
FROM TABLE(TUMBLE(TABLE `SensorReading`, DESCRIPTOR(`timestamp`), INTERVAL '1' SECOND))
GROUP BY `sensorid`, `window_start`, `window_end`, `window_time`;
CREATE VIEW `_SensorMaxTempWindow`
AS
SELECT `sensorid`, `window_time`, MAX(`temp`) AS `maxTemp`
FROM TABLE(HOP(TABLE `SecReading`, DESCRIPTOR(`timeSec`), INTERVAL '5' SECOND, INTERVAL '1' MINUTE))
GROUP BY `sensorid`, `window_start`, `window_end`, `window_time`;
CREATE VIEW `SensorMaxTemp`
AS
SELECT `sensorid`, `window_time`, `maxTemp`
FROM (SELECT `sensorid`, `window_time`, `maxTemp`, ROW_NUMBER() OVER (PARTITION BY `sensorid` ORDER BY `window_time` DESC NULLS LAST) AS `__sqrlinternal_rownum`
  FROM `default_catalog`.`default_database`.`_SensorMaxTempWindow`) AS `t`
WHERE `__sqrlinternal_rownum` = 1;
CREATE TABLE `SecReading_1` (
  `sensorid` BIGINT NOT NULL,
  `timeSec` TIMESTAMP(3) WITH LOCAL TIME ZONE NOT NULL,
  `timeStart` TIMESTAMP(3) NOT NULL,
  `timeEnd` TIMESTAMP(3) NOT NULL,
  `temp` DOUBLE NOT NULL,
  PRIMARY KEY (`sensorid`, `timeStart`, `timeEnd`, `timeSec`) NOT ENFORCED
)
WITH (
  'connector' = 'jdbc-sqrl',
  'driver' = 'org.postgresql.Driver',
  'password' = '${POSTGRES_PASSWORD}',
  'sink.on-conflict.action' = 'TIMESTAMP',
  'sink.on-conflict.timestamp-column' = 'timeSec',
  'table-name' = 'SecReading',
  'url' = 'jdbc:postgresql://${POSTGRES_AUTHORITY}',
  'username' = '${POSTGRES_USERNAME}'
);
CREATE TABLE `SensorMaxTemp_2` (
  `sensorid` BIGINT NOT NULL,
  `window_time` TIMESTAMP(3) WITH LOCAL TIME ZONE NOT NULL,
  `maxTemp` DOUBLE NOT NULL,
  PRIMARY KEY (`sensorid`) NOT ENFORCED
)
WITH (
  'connector' = 'jdbc-sqrl',
  'driver' = 'org.postgresql.Driver',
  'password' = '${POSTGRES_PASSWORD}',
  'sink.on-conflict.action' = 'TIMESTAMP',
  'sink.on-conflict.timestamp-column' = 'window_time',
  'table-name' = 'SensorMaxTemp',
  'url' = 'jdbc:postgresql://${POSTGRES_AUTHORITY}',
  'username' = '${POSTGRES_USERNAME}'
);
CREATE TABLE `SensorReading_3` (
  `sensorid` BIGINT NOT NULL,
  `time` BIGINT NOT NULL,
  `temperature` DOUBLE NOT NULL,
  `humidity` DOUBLE NOT NULL,
  `timestamp` TIMESTAMP(3) WITH LOCAL TIME ZONE NOT NULL,
  PRIMARY KEY (`sensorid`, `time`) NOT ENFORCED
)
WITH (
  'connector' = 'jdbc-sqrl',
  'driver' = 'org.postgresql.Driver',
  'password' = '${POSTGRES_PASSWORD}',
  'sink.on-conflict.action' = 'IGNORE',
  'table-name' = 'SensorReading',
  'url' = 'jdbc:postgresql://${POSTGRES_AUTHORITY}',
  'username' = '${POSTGRES_USERNAME}'
);
EXECUTE STATEMENT SET BEGIN
INSERT INTO `default_catalog`.`default_database`.`SecReading_1`
SELECT *
 FROM `default_catalog`.`default_database`.`SecReading`
;
INSERT INTO `default_catalog`.`default_database`.`SensorMaxTemp_2`
 SELECT *
  FROM `default_catalog`.`default_database`.`SensorMaxTemp`
 ;
 INSERT INTO `default_catalog`.`default_database`.`SensorReading_3`
  SELECT *
   FROM `default_catalog`.`default_database`.`SensorReading`
  ;
  END
>>>kafka.json
{
  "topics" : [ ],
  "testRunnerTopics" : [ ]
}
>>>postgres-schema.sql
CREATE TABLE IF NOT EXISTS "SecReading" ("sensorid" BIGINT NOT NULL, "timeSec" TIMESTAMP WITH TIME ZONE NOT NULL, "timeStart" TIMESTAMP WITHOUT TIME ZONE NOT NULL, "timeEnd" TIMESTAMP WITHOUT TIME ZONE NOT NULL, "temp" DOUBLE PRECISION NOT NULL, PRIMARY KEY ("sensorid","timeStart","timeEnd","timeSec"));
CREATE TABLE IF NOT EXISTS "SensorMaxTemp" ("sensorid" BIGINT NOT NULL, "window_time" TIMESTAMP WITH TIME ZONE NOT NULL, "maxTemp" DOUBLE PRECISION NOT NULL, PRIMARY KEY ("sensorid"));
CREATE TABLE IF NOT EXISTS "SensorReading" ("sensorid" BIGINT NOT NULL, "time" BIGINT NOT NULL, "temperature" DOUBLE PRECISION NOT NULL, "humidity" DOUBLE PRECISION NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("sensorid","time"))
>>>postgres-views.sql

>>>vertx.json
{
  "models" : {
    "v1" : {
      "queries" : [
        {
          "type" : "args",
          "parentType" : "Query",
          "fieldName" : "SecReading",
          "exec" : {
            "arguments" : [
              {
                "type" : "variable",
                "path" : "limit"
              },
              {
                "type" : "variable",
                "path" : "offset"
              },
              {
                "type" : "variable",
                "path" : "sensorid"
              }
            ],
            "query" : {
              "type" : "SqlQuery",
              "sql" : "SELECT *\nFROM (SELECT \"sensorid\", \"timeSec\", \"timeStart\", \"timeEnd\", \"temp\"\n  FROM \"SecReading\"\n  ORDER BY \"timeSec\" DESC NULLS LAST) AS \"t\"\nWHERE \"sensorid\" = $1",
              "parameters" : [
                {
                  "type" : "arg",
                  "path" : "sensorid",
                  "sqlType" : "BIGINT"
                }
              ],
              "pagination" : "LIMIT_AND_OFFSET",
              "cacheDurationMs" : 0,
              "database" : "POSTGRES"
            }
          }
        },
        {
          "type" : "args",
          "parentType" : "Query",
          "fieldName" : "SensorMaxTemp",
          "exec" : {
            "arguments" : [
              {
                "type" : "variable",
                "path" : "limit"
              },
              {
                "type" : "variable",
                "path" : "offset"
              },
              {
                "type" : "variable",
                "path" : "sensorid"
              }
            ],
            "query" : {
              "type" : "SqlQuery",
              "sql" : "SELECT *\nFROM \"SensorMaxTemp\"\nWHERE \"sensorid\" = $1 OR $1 IS NULL",
              "parameters" : [
                {
                  "type" : "arg",
                  "path" : "sensorid",
                  "sqlType" : "BIGINT"
                }
              ],
              "pagination" : "LIMIT_AND_OFFSET",
              "cacheDurationMs" : 0,
              "database" : "POSTGRES"
            }
          }
        },
        {
          "type" : "args",
          "parentType" : "Query",
          "fieldName" : "SensorReading",
          "exec" : {
            "arguments" : [
              {
                "type" : "variable",
                "path" : "limit"
              },
              {
                "type" : "variable",
                "path" : "offset"
              }
            ],
            "query" : {
              "type" : "SqlQuery",
              "sql" : "SELECT *\nFROM \"SensorReading\"",
              "parameters" : [ ],
              "pagination" : "LIMIT_AND_OFFSET",
              "cacheDurationMs" : 0,
              "database" : "POSTGRES"
            }
          }
        }
      ],
      "mutations" : [ ],
      "subscriptions" : [ ],
      "operations" : [
        {
          "function" : {
            "name" : "GetSecReading",
            "description" : "Returns the sensor temperature for a sensor by id from most recent.\n    Sensor temperature readings are averaged per second.",
            "parameters" : {
              "type" : "object",
              "properties" : {
                "offset" : {
                  "type" : "integer"
                },
                "limit" : {
                  "type" : "integer"
                },
                "sensorid" : {
                  "type" : "integer"
                }
              },
              "required" : [
                "sensorid"
              ]
            }
          },
          "format" : "JSON",
          "apiQuery" : {
            "query" : "query SecReading($sensorid: Long!, $limit: Int = 10, $offset: Int = 0) {\nSecReading(sensorid: $sensorid, limit: $limit, offset: $offset) {\nsensorid\ntimeSec\ntimeStart\ntimeEnd\ntemp\n}\n\n}",
            "queryName" : "SecReading",
            "operationType" : "QUERY"
          },
          "mcpMethod" : "TOOL",
          "restMethod" : "GET",
          "uriTemplate" : "queries/SecReading{?offset,limit,sensorid}"
        },
        {
          "function" : {
            "name" : "GetSensorMaxTemp",
            "description" : "Returns the maximum temperature recorded for a given sensor",
            "parameters" : {
              "type" : "object",
              "properties" : {
                "offset" : {
                  "type" : "integer"
                },
                "limit" : {
                  "type" : "integer"
                },
                "sensorid" : {
                  "type" : "integer"
                }
              },
              "required" : [ ]
            }
          },
          "format" : "JSON",
          "apiQuery" : {
            "query" : "query SensorMaxTemp($sensorid: Long, $limit: Int = 10, $offset: Int = 0) {\nSensorMaxTemp(sensorid: $sensorid, limit: $limit, offset: $offset) {\nsensorid\nwindow_time\nmaxTemp\n}\n\n}",
            "queryName" : "SensorMaxTemp",
            "operationType" : "QUERY"
          },
          "mcpMethod" : "TOOL",
          "restMethod" : "GET",
          "uriTemplate" : "queries/SensorMaxTemp{?offset,limit,sensorid}"
        },
        {
          "function" : {
            "name" : "GetSensorReading",
            "parameters" : {
              "type" : "object",
              "properties" : {
                "offset" : {
                  "type" : "integer"
                },
                "limit" : {
                  "type" : "integer"
                }
              },
              "required" : [ ]
            }
          },
          "format" : "JSON",
          "apiQuery" : {
            "query" : "query SensorReading($limit: Int = 10, $offset: Int = 0) {\nSensorReading(limit: $limit, offset: $offset) {\nsensorid\ntime\ntemperature\nhumidity\ntimestamp\n}\n\n}",
            "queryName" : "SensorReading",
            "operationType" : "QUERY"
          },
          "mcpMethod" : "TOOL",
          "restMethod" : "GET",
          "uriTemplate" : "queries/SensorReading{?offset,limit}"
        }
      ],
      "schema" : {
        "type" : "string",
        "schema" : "\"An RFC-3339 compliant Full Date Scalar\"\nscalar Date\n\n\"A DateTime scalar that handles both full RFC3339 and shorter timestamp formats\"\nscalar DateTime\n\n\"A JSON scalar\"\nscalar JSON\n\n\"24-hour clock time value string in the format `hh:mm:ss` or `hh:mm:ss.sss`.\"\nscalar LocalTime\n\n\"A 64-bit signed integer\"\nscalar Long\n\ntype Query {\n  \"\"\"\n  Returns the sensor temperature for a sensor by id from most recent.\n      Sensor temperature readings are averaged per second.\n  \"\"\"\n  SecReading(sensorid: Long!, limit: Int = 10, offset: Int = 0): [SecReading!]\n  \"Returns the maximum temperature recorded for a given sensor\"\n  SensorMaxTemp(sensorid: Long, limit: Int = 10, offset: Int = 0): [SensorMaxTemp!]\n  SensorReading(limit: Int = 10, offset: Int = 0): [SensorReading!]\n}\n\n\"\"\"\nReturns the sensor temperature for a sensor by id from most recent.\n    Sensor temperature readings are averaged per second.\n\"\"\"\ntype SecReading {\n  sensorid: Long!\n  timeSec: DateTime!\n  timeStart: DateTime!\n  timeEnd: DateTime!\n  temp: Float!\n}\n\n\"Returns the maximum temperature recorded for a given sensor\"\ntype SensorMaxTemp {\n  sensorid: Long!\n  window_time: DateTime!\n  maxTemp: Float!\n}\n\ntype SensorReading {\n  sensorid: Long!\n  time: Long!\n  temperature: Float!\n  humidity: Float!\n  timestamp: DateTime!\n}\n\nenum _McpMethodType {\n  NONE\n  TOOL\n  RESOURCE\n}\n\nenum _RestMethodType {\n  NONE\n  GET\n  POST\n}\n\ndirective @api(mcp: _McpMethodType, rest: _RestMethodType, uri: String) on QUERY | MUTATION | FIELD_DEFINITION\n"
      }
    }
  }
}
