PurpleAirSQLiteQueryStatements module

Copyright 2022 carlkidcrypto, All rights reserved. A file containing SQLITE statements defined as constants. Generate the SQLITE query strings. For simplicity our table names will match what the PurpleAir documentation says. We will do the same for table column names.

PurpleAirSQLiteQueryStatements.CREATE_ENVIRONMENTAL_FIELDS_TABLE = '\n    CREATE TABLE IF NOT EXISTS environmental_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        humidity INTEGER NULL,\n        humidity_a INTEGER NULL,\n        humidity_b INTEGER NULL,\n        temperature INTEGER NULL,\n        temperature_a INTEGER NULL,\n        temperature_b INTEGER NULL,\n        pressure REAL NULL,\n        pressure_a REAL NULL,\n        pressure_b REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

SQLITE statement for environmental_fields table

PurpleAirSQLiteQueryStatements.CREATE_MISCELLANEOUS_FIELDS = '\n    CREATE TABLE IF NOT EXISTS miscellaneous_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        voc REAL NULL,\n        voc_a REAL NULL,\n        voc_b REAL NULL,\n        ozone1 REAL NULL,\n        analog_input REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

SQLITE statement for miscellaneous_fields table

PurpleAirSQLiteQueryStatements.CREATE_PARTICLE_COUNT_FIELDS = '\n    CREATE TABLE IF NOT EXISTS particle_count_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        um_count_0_3 REAL NULL,\n        um_count_a_0_3 REAL NULL,\n        um_count_b_0_3 REAL NULL,\n        um_count_0_5 REAL NULL,\n        um_count_a_0_5 REAL NULL,\n        um_count_b_0_5 REAL NULL,\n        um_count_1_0 REAL NULL,\n        um_count_a_1_0 REAL NULL,\n        um_count_b_1_0 REAL NULL,\n        um_count_2_5 REAL NULL,\n        um_count_a_2_5 REAL NULL,\n        um_count_b_2_5 REAL NULL,\n        um_count_5_0 REAL NULL,\n        um_count_a_5_0 REAL NULL,\n        um_count_b_5_0 REAL NULL,\n        um_count_10_0 REAL NULL,\n        um_count_a_10_0 REAL NULL,\n        um_count_b_10_0 REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: we can not start column names with numbers. So 0_3_um_count becomes um_count_0_3 SQLITE statement for particle_count_fields table

PurpleAirSQLiteQueryStatements.CREATE_PM10_0_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm10_0_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        pm10_0 REAL NULL,\n        pm10_0_a REAL NULL,\n        pm10_0_b REAL NULL,\n        pm10_0_atm REAL NULL,\n        pm10_0_atm_a REAL NULL,\n        pm10_0_atm_b REAL NULL,\n        pm10_0_cf_1 REAL NULL,\n        pm10_0_cf_1_a REAL NULL,\n        pm10_0_cf_1_b REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm10_0 instead of pm10.0 SQLITE statement for pm10_0_fields table

PurpleAirSQLiteQueryStatements.CREATE_PM1_0_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm1_0_fields(\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        pm1_0 REAL NULL,\n        pm1_0_a REAL NULL,\n        pm1_0_b REAL NULL,\n        pm1_0_atm REAL NULL,\n        pm1_0_atm_a REAL NULL,\n        pm1_0_atm_b REAL NULL,\n        pm1_0_cf_1 REAL NULL,\n        pm1_0_cf_1_a REAL NULL,\n        pm1_0_cf_1_b REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm1_0 instead of pm1.0 SQLITE statement for pm1_0_fields table

PurpleAirSQLiteQueryStatements.CREATE_PM2_5_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm2_5_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        pm2_5_alt REAL NULL,\n        pm2_5_alt_a REAL NULL,\n        pm2_5_alt_b REAL NULL,\n        pm2_5 REAL NULL,\n        pm2_5_a REAL NULL,\n        pm2_5_b REAL NULL,\n        pm2_5_atm REAL NULL,\n        pm2_5_atm_a REAL NULL,\n        pm2_5_atm_b REAL NULL,\n        pm2_5_cf_1 REAL NULL,\n        pm2_5_cf_1_a REAL NULL,\n        pm2_5_cf_1_b REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm2_5 instead of pm2.5 SQLITE statement for pm2_5_fields table

PurpleAirSQLiteQueryStatements.CREATE_PM2_5_PSEUDO_AVERAGE_FIELDS = '\n    CREATE TABLE IF NOT EXISTS pm2_5_pseudo_average_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        pm2_5_10minute REAL NULL,\n        pm2_5_10minute_a REAL NULL,\n        pm2_5_10minute_b REAL NULL,\n        pm2_5_30minute REAL NULL,\n        pm2_5_30minute_a REAL NULL,\n        pm2_5_30minute_b REAL NULL,\n        pm2_5_60minute REAL NULL,\n        pm2_5_60minute_a REAL NULL,\n        pm2_5_60minute_b REAL NULL,\n        pm2_5_6hour REAL NULL,\n        pm2_5_6hour_a REAL NULL,\n        pm2_5_6hour_b REAL NULL,\n        pm2_5_24hour REAL NULL,\n        pm2_5_24hour_a REAL NULL,\n        pm2_5_24hour_b REAL NULL,\n        pm2_5_1week REAL NULL,\n        pm2_5_1week_a REAL NULL,\n        pm2_5_1week_b REAL NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note: Since we can’t have decimals in variable names, we do pm2_5 instead of pm2.5 SQLITE statement for pm2_5_pseudo_average_fields table

PurpleAirSQLiteQueryStatements.CREATE_STATION_INFORMATION_AND_STATUS_FIELDS_TABLE = '\n    CREATE TABLE IF NOT EXISTS station_information_and_status_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        name TEXT NULL,\n        icon INTEGER NULL,\n        model TEXT NULL,\n        hardware TEXT NULL,\n        location_type INTEGER NULL,\n        private INTEGER NULL,\n        latitude REAL NULL,\n        longitude REAL NULL,\n        altitude REAL NULL,\n        position_rating INTEGER NULL,\n        led_brightness INTEGER NULL,\n        firmware_version TEXT NULL,\n        firmware_upgrade TEXT NULL,\n        rssi INTEGER NULL,\n        uptime INTEGER NULL,\n        pa_latency INTEGER NULL,\n        memory INTEGER NULL,\n        last_seen TEXT NULL,\n        last_modified TEXT NULL,\n        date_created TEXT NULL,\n        channel_state INTEGER NULL,\n        channel_flags INTEGER NULL,\n        channel_flags_manual INTEGER NULL,\n        channel_flags_auto INTEGER NULL,\n        confidence INTEGER NULL,\n        confidence_manual INTEGER NULL,\n        confidence_auto INTEGER NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

SQLITE statement for station_information_and_status_fields table

PurpleAirSQLiteQueryStatements.CREATE_THINGSPEAK_FIELDS = '\n    CREATE TABLE IF NOT EXISTS thingspeak_fields (\n        data_time_stamp TEXT NOT NULL,\n        sensor_index INTEGER NOT NULL,\n        primary_id_a INTEGER NULL,\n        primary_key_a TEXT NULL,\n        secondary_id_a INTEGER NULL,\n        secondary_key_a TEXT NULL,\n        primary_id_b INTEGER NULL,\n        primary_key_b TEXT NULL,\n        secondary_id_b INTEGER NULL,\n        secondary_key_b TEXT NULL,\n        PRIMARY KEY(data_time_stamp, sensor_index))'

Note TO SELF MAY END UP GETTING RID OF THIS TABLE. I SEE NO USE FOR IT. SQLITE statement for thingspeak_fields table

PurpleAirSQLiteQueryStatements.SQLITE_DROP_ALL_TABLES = '\n    DROP TABLE station_information_and_status_fields;\n    DROP TABLE environmental_fields;\n    DROP TABLE miscellaneous_fields;\n    DROP TABLE pm1_0_fields;\n    DROP TABLE pm2_5_fields;\n    DROP TABLE pm2_5_pseudo_average_fields;\n    DROP TABLE pm10_0_fields;\n    DROP TABLE particle_count_fields;\n    DROP TABLE thingspeak_fields;\n    '

SQLITE statement to drop all tables in the database

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_ENVIRONMENTAL_FIELDS = '\n    INSERT INTO environmental_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            humidity,\n            humidity_a,\n            humidity_b,\n            temperature,\n            temperature_a,\n            temperature_b,\n            pressure,\n            pressure_a,\n            pressure_b\n        ) \n        VALUES\n        (\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?\n        )'

SQLITE insert statement for environmental_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_MISCELLANEOUS_FIELDS = '\n    INSERT INTO miscellaneous_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            voc,\n            voc_a,\n            voc_b,\n            ozone1,\n            analog_input\n        ) \n        VALUES\n        (\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?\n        )'

SQLITE insert statement for miscellaneous_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_PARTICLE_COUNT_FIELDS = '\n    INSERT INTO particle_count_fields \n    (\n        data_time_stamp,\n        sensor_index,\n        um_count_0_3,\n        um_count_a_0_3,\n        um_count_b_0_3,\n        um_count_0_5,\n        um_count_a_0_5,\n        um_count_b_0_5,\n        um_count_1_0,\n        um_count_a_1_0,\n        um_count_b_1_0,\n        um_count_2_5,\n        um_count_a_2_5,\n        um_count_b_2_5,\n        um_count_5_0,\n        um_count_a_5_0,\n        um_count_b_5_0,\n        um_count_10_0,\n        um_count_a_10_0,\n        um_count_b_10_0\n    )\n    VALUES\n    (\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?\n    )'

SQLITE insert statement for particle_count_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_PM10_0_FIELDS = '\n    INSERT INTO pm10_0_fields\n    (\n        data_time_stamp,\n        sensor_index,\n        pm10_0,\n        pm10_0_a,\n        pm10_0_b,\n        pm10_0_atm,\n        pm10_0_atm_a,\n        pm10_0_atm_b,\n        pm10_0_cf_1,\n        pm10_0_cf_1_a,\n        pm10_0_cf_1_b\n    ) \n    VALUES\n    (\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?\n    )'

SQLITE insert statement for pm10_0_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_PM1_0_FIELDS = '\n    INSERT INTO pm1_0_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            pm1_0,\n            pm1_0_a,\n            pm1_0_b,\n            pm1_0_atm,\n            pm1_0_atm_a,\n            pm1_0_atm_b,\n            pm1_0_cf_1,\n            pm1_0_cf_1_a,\n            pm1_0_cf_1_b\n        )\n        VALUES\n        (\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?\n        )'

SQLITE insert statement for pm1_0_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_PM2_5_FIELDS = '\n    INSERT INTO pm2_5_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            pm2_5_alt,\n            pm2_5_alt_a,\n            pm2_5_alt_b,\n            pm2_5,\n            pm2_5_a,\n            pm2_5_b,\n            pm2_5_atm,\n            pm2_5_atm_a,\n            pm2_5_atm_b,\n            pm2_5_cf_1,\n            pm2_5_cf_1_a,\n            pm2_5_cf_1_b\n        ) \n        VALUES\n        (\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?\n        )'

SQLITE insert statement for pm2_5_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_PM2_5_PSEUDO_AVERAGE_FIELDS = '\n    INSERT INTO pm2_5_pseudo_average_fields \n    (\n        data_time_stamp,\n        sensor_index,\n        pm2_5_10minute,\n        pm2_5_10minute_a,\n        pm2_5_10minute_b,\n        pm2_5_30minute,\n        pm2_5_30minute_a,\n        pm2_5_30minute_b,\n        pm2_5_60minute,\n        pm2_5_60minute_a,\n        pm2_5_60minute_b,\n        pm2_5_6hour,\n        pm2_5_6hour_a,\n        pm2_5_6hour_b,\n        pm2_5_24hour,\n        pm2_5_24hour_a,\n        pm2_5_24hour_b,\n        pm2_5_1week,\n        pm2_5_1week_a,\n        pm2_5_1week_b\n    )\n    VALUES\n    (\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?\n    )'

SQLITE insert statement for pm2_5_pseudo_average_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_STATION_INFORMATION_AND_STATUS_FIELDS = '\n    INSERT INTO station_information_and_status_fields\n        (\n            data_time_stamp,\n            sensor_index,\n            name,\n            icon,\n            model,\n            hardware,\n            location_type,\n            private,\n            latitude,\n            longitude,\n            altitude,\n            position_rating,\n            led_brightness,\n            firmware_version,\n            firmware_upgrade,\n            rssi,\n            uptime,\n            pa_latency,\n            memory,\n            last_seen,\n            last_modified,\n            date_created,\n            channel_state,\n            channel_flags,\n            channel_flags_manual,\n            channel_flags_auto,\n            confidence,\n            confidence_manual,\n            confidence_auto\n        )\n        VALUES \n        (\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?,\n            ?\n        )'

SQLITE insert statement for station_information_and_status_fields

PurpleAirSQLiteQueryStatements.SQLITE_INSERT_STATEMENT_THINGSPEAK_FIELDS = '\n    INSERT INTO thingspeak_fields\n    (\n        data_time_stamp,\n        sensor_index,\n        primary_id_a,\n        primary_key_a,\n        secondary_id_a,\n        secondary_key_a,\n        primary_id_b,\n        primary_key_b,\n        secondary_id_b,\n        secondary_key_b\n    )\n    VALUES\n    (\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?,\n        ?\n    )'

SQLITE insert statement for thingspeak_fields