update to 3.0 Stack

// UPDATE V3-STACK TTN STAND FEB 2021

// SQLite3
const sqlite3 = require('sqlite3')
let sql_create = "CREATE TABLE IF NOT EXISTS TABLE (\n"
        +"id    INTEGER,\n"
        +"sensor_device_id  TEXT,\n"
        +"sensor_timestamp  TEXT,\n"
        +"sensor_payload_bytes  BLOB,\n"
        +"sensor_battery    REAL,\n"
        +"sensor_temperature    REAL,\n"
        +"sensor_humidity   REAL,\n"
        +"PRIMARY KEY(id AUTOINCREMENT))";

// MQTT
var mqtt=require('mqtt');

var options={
    username:"DIE_APP@ttn", //TTN Application ID
    password:"DER KEY DER KURZ NACH API-SCHLÜSSEL GENERIERUNG ANGEZEIGT WIRD UND DANACH VERSCHWINDET", // TTN ACCESS KEY
    };

var client  = mqtt.connect("mqtt://eu1.cloud.thethings.network:1883",options); // Server location EU 

//connect
client.on("connect",function(){ 
    var topic="v3/DIE_APP@ttn/devices/DAS_DEVICE/up";

    client.subscribe(topic); //single topic
    //console.log("connected +subscribed");
    });
//handle errors
client.on("error",function(error){
    //console.log("Can't connect" + error);
    process.exit(1)});

//handle incoming messages
client.on("message",function(topic, message, packet){
    //console.log("message is "+ message);
    //console.log("topic is "+ topic);

    let obj_message=JSON.parse(message);
      //https://www.thethingsindustries.com/docs/reference/data-formats/
      let rec_dev_id=obj_message.end_device_ids.device_id;
      let rec_timestamp=obj_message.received_at;
      let rec_payload_bytes=obj_message.uplink_message.decoded_payload.bytes;
      let rec_battery=obj_message.uplink_message.decoded_payload.batt;
      let rec_temperature=obj_message.uplink_message.decoded_payload.temp_ambient;
      let rec_humidity=obj_message.uplink_message.decoded_payload.rh; 

      //open Database
    let db = new sqlite3.Database('./data/sensor.db', (err) => {  //3. sqlite3.OPEN_CREATE: open the database, if the database does not exist, create a new database.
      if (err) {
        //console.error(err.message);
      }
      //console.log('Connected to database.');
    });
    //create table if not exist
      db.all(sql_create, [], (err, rows) => {
        if (err) {
          throw err;
          //console.log("Tabelle nicht angelegt");
        }
      });
    // insert one row
      db.run(`INSERT INTO TABLE(sensor_device_id,sensor_timestamp,sensor_payload_bytes,sensor_battery,sensor_temperature,sensor_humidity  ) VALUES(?,?,?,?,?,?)`, [rec_dev_id,rec_timestamp,rec_payload_bytes,rec_battery,rec_temperature,rec_humidity], function(err) {
        if (err) {
          return; //console.log(err.message);
        }
        // get the last insert id
        //console.log(`A row has been inserted with rowid ${this.lastID}`);
      });

    //close db
    db.close((err) => {
      if (err) {
        //console.error(err.message);
      }
      //console.log('Close the database connection.');
    });

  });
PietH
