Note to self: Technical debt

I’m a nerd.

Yeah, I admit it. Most of my readers probably know packages like Home Assistant and Domoticz… right?

Well.

Because I can… I wrote my own whole damn domotica-based system from scratch.

Yes. You read that correctly.

I wrote my own whole damn domotica system.

From. Scratch.

YEP 😛

And again — why?

Because it started as a test project in my own Oracle database. Hey, I’m an Oracle developer. I run my own Oracle database as a testbed for my domotica installation — for testing purposes.

Just like my own WebLogic 14c OSB test cluster.

Yes, I have my own 14c OSB testbed.

I know… NERD ALERT.

Just a few tables, just to see if I could quickly store some sensor data from a couple of cheap temperature sensors I ordered from AliExpress.

From there, things… well… escalated?

From only a handful of sensors to… 133 active sensors …and 11 inactive ones.

NERD ALERT… AGAIN?!

Yeah.

This is my normal. 😛

At first I told myself I’d migrate everything to InfluxDB and Grafana Or maybe PostgreSQL. You know… something more obvious than using an Oracle database.

And… well…

That never happened.

Let’s just say… it’s still in my backlog. heheheheheheheh

Records

SELECT COUNT(*) FROM dts_sensordata dsa

Result: 109,803,146 records.

Yes you read that right. 109.803.146. That’s over 109 million records.

In a single Oracle table. Running on my self-built domotica system.

And the best part? These aren’t even raw “write-on-every-change” datapoints.

Nope.

Each row has a FROM and a TILL timestamp. If the value doesn’t change?

I just update the TILL date.

No new row.

So what you’re looking at…is the compressed, deduplicated, streamlined version.

Imagine how big it would’ve been without that logic.

So yeah.

I’m sitting on Mount Everest.

Technical debt

Last week a part of my system stopped sending in data.

Great!

Today, I “found” the time to actually debug the issue. There I went — down the rabbit hole.

Round 1. Debugging Shelly Cloud.

My Shelly code partially stopped working. After 10 minutes I found the root cause. I was overloading their system!

The solution?!

sleep(1.5)

Yeah. That solved it 😛

Round 2. Technical debt part #1

I had three Python functions. Doing basically the same thing. Different endpoints, slightly different variable names… but the logic? Identical.

devices= [
# device_id naam RV-key TEMP-key
("key1", "Woonkamer", "RV_WOONKAMER", "WOONKAMER"),
("key2", "Kamer Kevin", "RV_KAMERKEVIN", "TEMP_KAMERKEVIN"),
("key3", "Masterbedroom", "RV_MASTERBEDROOM", "TEMP_MASTERBEDROOM"),
]

IF dts_sun_api.zon_onder( l_datum ) = FALSE
THEN
   SELECT MAX( doa.id ) INTO l_vorige_rec_id
     FROM dts_omvormerdata doa
    WHERE doa.dos_id = l_omvormer_string_rec.id
      AND doa.trunceddate >= TRUNC( l_datum ) - 7;

And the beauty! My own comment!

-- This checks whether the last entry is from the previous day.
-- This also means there must already be at least one record for this code to work with a new inverter.

And what happens when my SELECT MAX() with the trunceddate performance check returns no record?

Yep.

l_vorige_rec_id ends up as NULL.

And the rest of the logic quietly imploded.

I fixed is structurally ( finally ). And added a note to self:

-- Jesus Brain, this is bad. On first execution, that previous MAX() can return NULL,
-- which means your code doesn't work — and will never work.
-- And the solution? Just insert a simple entry using that date and that actual value
-- that chart will never start at the new day and look weird.. who cares! 
-- This only triggers if there hasn't been a record in the last 7 days...
-- Oh, and that whole "optimize with -7 days" thing? Yeah… that's broken too. Pff.

So yes.

I fixed it. Structurally. This time.

And the beautiful thing is…

This will happen again but in a different part of my code.

Because I have loads of these shortcuts. Little time bombs, lovingly copy-pasted at 23:47 in the evening while I should’ve been in bed for at least two hours already.

Bugs are forever.

BBO!

#Buggy Brain Out!