Find a free Database

[b3n3d1k7]

We need to find a free database to fulfill our needs. I.e. store

• users
• statistics (temperatures, water-level, pressure, number of surfers, ...)
• ...

E.g. Oracle Cloud offers a free DB (20GB Storage, 1 Core) This sounds like a good option - if the performance is enough.

AWS offers 25GB of dynamo DB for free with read and write units limits: I've never used dynamo DB - so I'm not sure if it will fit our needs.

Azure offers 5GB of cosmos db for free with limited request units: I've also never used cosmos db.

I guess the oracle db would be a good choice since there is no limit on reads and writes. It will never cost us anything or stop working. It will just be slow if we have too many reads and writes on it due to the limited cpu.

[b3n3d1k7]

@mroffmix @aspirinv what do you guys think?

[b3n3d1k7]

Oracle Cloud also offers 2 free compute instances with 1GB ram and 1/8ocpu each. This should be sufficient for the start i guess. I will try to set up a db and 2 instances in my private account and deploy a basic web-server for testing.

[aspirinv]

@b3n3d1k7 thanks for that research. AWS seems to me the nicest opportunity of the mentioned, but it has the line that I'm not sure about "Your free usage under the AWS Free Tier is calculated each month across all regions and automatically applied to your bill – free usage does not accumulate"

As one more option I could add MongoDB Atlas that's much less: 500MB and shared infrastructure, but that sould be enough

[b3n3d1k7]

I managed to get the oracle db to work without any need for a backend. We can directly fetch data from the db and insert data using http requests. However, there is currently no authentication or anything - so basically everyone can read and write. But this is also true for the current solution, right? I still need to write some lambda-function to insert the metrics into the db though...

Here is a screenshot of the db rest interface and a sample request for the surfers from postman:

[b3n3d1k7]

I've created and deployed a lambda function which updates the db every 15 minutes with new metrics. However, because of the layout i chose (all metrics in one row) and the availability of the metrics (air temp is only available for every hour - not every 15 min like the other values), i currently only insert hourly values.

The metrics can be fetched using a simple http get request: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/metrics?days=30 You can adjust days to get the number of days you want. There should be around 90 days of metrics currently in the db - I'm not sure how many days we can keep, but currently we only use around 40% of the available space.

For surfers the logic is the same - i.e. for each timestamp there will be an entry with the number of surfers. Again you can fetch the metric with a simple get request and specifiy the days of data you want: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers?days=30 If required I can add another method to get the latest value.

Setting the number of surfers is a simple post request to e.g. https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers?surfers=13 for setting the surfers to 13.

I will adjust the android app to use the db now to see if everything works there too. Let me know if you have any objections, suggestions or questions.

[b3n3d1k7]

Got the android app working with the new db:

I uploaded the current version of the app to https://drive.google.com/file/d/1aPH4yRjKlbiE-o0TwPzosyNa5TsHysgq/view?usp=sharing Feel free to test and report any problems.

[b3n3d1k7]

New version of the android app now also has charts: (Link is the same as above)

[aspirinv]

I managed to get the oracle db to work without any need for a backend. We can directly fetch data from the db and insert data using http requests. However, there is currently no authentication or anything - so basically everyone can read and write. But this is also true for the current solution, right? I still need to write some lambda-function to insert the metrics into the db though...

Here is a screenshot of the db rest interface and a sample request for the surfers from postman:

What would happened if someone writes in db some dummy data more than 25GB, would it get stopped or converted to payed version?

[b3n3d1k7]

I guess it will just return an "out of space" error since the db is 20GB in size.

[b3n3d1k7]

Note: there are even backups to recover the db if required at some point:

[b3n3d1k7]

Important: this is obsolete, done use it - see comment https://github.com/mroffmix/EW/issues/4#issuecomment-752387732

Since we discussed that we want to add E1 and Floßlände surfers too I added tables and added handlers. example requests get: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_e1?days=30 https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_e2?days=30 https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_fl?days=30

post: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_e1?surfers=13 https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_e2?surfers=13 https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_fl?surfers=13

Note: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers currently maps to https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_e2 but will be removed eventually - so please dont use it anymore

[b3n3d1k7]

Since creating multiple tables was not ideal, i extended the existing table by a column "wave" with type int. 1 -> E1, 2 -> E2, 3 -> FL

requests: get: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_generic?days=7 -> returns surfers of last 7 days of all waves. Includes wave column to tell which item belongs to which wave.

post: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers_generic?wave=2&surfers=8 -> sets surfers to 8 for wave 2 (i.e. E2)

Note: https://zltjxru82zwah1l-eisbach.adb.eu-frankfurt-1.oraclecloudapps.com/ords/eisbach/ew/surfers currently sets wave to 2 but will be removed eventually - so please dont use it anymore