crate / micropython-cratedb

A CrateDB Driver for MicroPython.
https://cratedb.com
Apache License 2.0
5 stars 0 forks source link

Consider higher level interface using lists of dicts for each row of data? #6

Open simonprickett opened 1 month ago

amotl commented 1 month ago

Hi Simon,

because your issue description lacks additional information, I can only try to extrapolate.

dataset is my favourite higher level database interface for Python. Are you referring to something like this, or do you have different things in mind?

With kind regards, Andreas.

simonprickett commented 1 month ago

I was thinking something higher level, optional (so you can still just use execute) and light as we don't want to be too clever on microcontrollers that have limited memory etc.

I'll take a look at dataset, I was thinking something like this.

Given a table:

CREATE TABLE some_table (id TEXT, temp DOUBLE PRECISION, humidity DOUBLE PRECISION);

Then something like this:

sensor_reading = {
    "id": "2abcde",
    "temp": 23.2,
    "humidity": 58.7
}

Use something like this to save that dict in the database, the driver assumes the key names in the dict are the column names in the table:

crate.insert("some_table". sensor_reading)
amotl commented 1 month ago

That sounds good!

Concerning data model design, If that resonates with you to provide an implementation that works beyond a specific DDL, so that the high-level interface you are aiming for can be implemented in a generic way, I'd recommend to have a look how Kotori is doing it, emulating the InfluxDB data model, but also how the CTK MongoDB I/O-, and CTK DynamoDB I/O-subsystems are doing it.

Other than that those are generic variants, they also work around other obstacles quickly discovered with CrateDB. For example, the venerable impossibility to store column names with leading underscores, i.e. _foo is invalid, becomes a non-issue without much ado.

Kotori/InfluxDB

time TIMESTAMP
tags OBJECT(DYNAMIC)
fields OBJECT(DYNAMIC)

MongoDB

oid TEXT
data OBJECT(DYNAMIC)

DynamoDB

pk OBJECT(STRICT)
data OBJECT(DYNAMIC)
aux OBJECT(IGNORED)
amotl commented 1 month ago

Concerning interface design, looking at it from a developer's perspective, it would be nice if we could pull in your driver without much ado into a downstream package like the Terkin Multi-Protocol Datalogger, roughly like its TelemetryTransportHTTP.