SQL, Outlook, Darknet Diaries, Peewee ORM

• SQL Server data compression tests & SQL Server Express data storage limit tests. Results: By using page compression with the data I happened to have, it was possible to increase data storage capacity by 241%, it's not as much as I had anticipated, but it's still a nice increment. Clearly the compression is on very "light and fast" side. That's nice. The 10 GB data limit per database is still being applied, but it's after compression. This is just for situations where full SQL Server pricing puts it totally out of question. First of all, row vs page compression difference was in our case negligible well under 0.1%, which was kind of unexpected. As well as the actual database operation performance is just increased due reduced disk I/O and cache space demands. Complete slideshow with charts were created but that's proprietary data, this is just extremely short summary of test results. - Problem, we need endlessly fast SSD space. - Reason for that problem, no hot, warm, cold data separation. - Anything new, or business as usual? I guess every project has had the same problem. Huge tables, where there is just a few frequently updated rows. -> All this should be stored on high end storage system. Hmm, right? Even if most of data is several years old and maybe is accessed even monthly. Separation of hot and cold data would help enormously.
• Microsoft Outlook Mail, so much fun. Mail sent from Outlook to Outlook and returned with message: "Contact the recipient by some other means (by phone, for example) and ask them to tell their email admin that it appears that their email system isn't accepting connection requests from your email system. Give them the error details shown below. It's likely that the recipient's email admin is the only one who can fix this problem." - Haha, great. Their and Your email system, both are Outlook in this case, hosted by Microsoft.
• Darknet Diaries & Game hacks episodes [ Ep 7, Ep 8 ] were fun. All sounds just so familiar. Anyway the show is awesome!
• Peewee ORM - Had several painful moments while dealing with latest Peewee ORM. But hey, that's what learning is. Peewee is great after all. Just as usual, you just need to do things right. In this case the problem was that I had integer field with primary keys, but without specified relation. In a way, this is very wrong. But at times it's handy. Using select with integer field just didn't work out with Peewee as it would with SQL usually. Because Peewee returns objects, not list of integers. Sometimes ORMs are just so enraging, simple things which would minute or two in SQL end up taking days using ORM. This isn't the first time this happens. Of course there are hundreds of different ways to work around it, but it's not the "right" solution. Of course there can be also right working solution, which still isn't optimal. After long battle, it starts to feel like Peewee ORM bug. Yet I'm not using latest version so, I'm not going to report it.
• Peewoo RM continued - database.Table.select().where(....) works, but when used with delete it fails. Even if it works in other cases. The fail seems to only happen when I've got multiple UNION or | in peewees case in the where statement. Btw. select still works, delete fails with None got no _counter error. Delete should return count, now the count fails, because the delete statement contains sub queries. Strange, way strange. Because this is cleanup task, I guess I'll accept that it's transactionally broken and run it as two independent queries. Blah. Yep, didn't work out as planned in any possible way. Plan B and horrible workaround installed, works perfectly without transactions, yet that wasn't the issue. Confirmed, issue only arises when there are three sources, with two sources it works. It doesn't matter which two sources I use. Third one always fails. Sigh! I'm pretty much sure, it's a bug. So getting count after delete with three sub queries as source fails. Let's just revert to classic model where I'll get list first and then delete based on that list, without using sub queries. Well, list is too long, too many variables. Reverting back to looping and individual deletes. This is perfect example how absolute garbage code gets produced. But it works. Select set A, select sets B,C,D and loop A, if not A.item in B or C or D, delete A. Ok, works. Yuck! This is actually perfect example where SQLite3 performs usually much better than many other databases.

2019-11-03