VBA Rubberducking

Duga

00:03

RELOAD!

[MDoerner/AdventOfCode2020] 3 commits. 556 additions. 15 deletions.

[rubberduck-vba/Rubberduck] 2 issue comments.

[Minesweeper] Games Played: 137, Bombs Used: 99, Moves Performed: 19458, New Users: 13

Duga

09:05

[rubberduck-vba/Rubberduck] IvenBach synchronized pull request #5749: Consolidate resource cleanup

IvenBach

09:17

@Duga There are several resources that look like they aren't used anywhere and are Italian only: CodeInspections_*, ReorderPresenter_ParamArrayError, TestExplorer_AppendHeader, TodoMarker*, TodoSettings_*

IllegalAnnotationInspection, EmptyUIRefreshMessage, EmptyUIRefreshMessage_Title also strike me as add since there's no Neutral version of it.

Will figure it out tomorrow. Sleep</iven>

Duga

09:31

[rubberduck-vba/Rubberduck] build for commit fd52de4f on unknown branch: AppVeyor build succeeded

FreeMan

10:43

@this Hey, everyone is remote working these days, it's really not hard...

FreeMan

11:10

Gotta admit. At this point, I'm looking forward to checking out tB as a VBA replacement because I keep hosing myself trying to do VC in Office apps and I keep forgetting to import code when I change branches so all my branches end up mangled.

that'd be worth a few bucks to me!

Son Goku ssj4

15:14

Anyone here that tried to make an web application where user can login and have the ability to save some user settings (which then saves into SQL relational database?)

I'm thinking how would I do user-settings for +- 300 people, +- 25 settings for each. When I read on the internet, having 25 (26 with useriId) columns in your DB is a bad, bad idea. So I found out there is something called "Property bag" so you save each settings to 4-column db table (userId, settingsName, key, value). In this example I would have around 300 * 25 = 7500 rows which is nothing.

But wanted to discuss my options if there is anyone that has tried this in the past.

this

Just to provide an alternative --- instead of having a row for every combination of user ++ setting, have only one row for each user with a nvarchar(MAX) column that stores a JSON object for settings.

The reasoning is that getting a single blob with all the settings means only one roundtrip and you usually need them all anyway so it's easier to persist them in a temporary web storage. The database also doesn't need to know about the settings which could change when the web application design change.

property bag would make more sense if you don't load them all and only need it now & then but that means potentially more round trips for operations that are not directly tied to saving actual data.

(I don't think of settings and user preferences as the same kind of data as say, an invoice or a sales order or whatever)

Consider further that you could still stuff a JSON content in a property bag scheme, would allow you to store several related settings for a page in a single blob but avoid mixing them up with other unrelated settings that only apply to another page. You still minimize the roundtrip that way, too.

Mathieu Guindon

15:31

@IvenBach the Italian translations were made against [next]; there not being a 'neutral' version probably means the resource key was either renamed (IllegalAnnotationInspection -> InvalidAnnotationInspection), or it was moved elsewhere; it's possible that a handful of keys lose a translation in the process, you'll want to try and keep as much as possible of the translation work done... this is tedious indeed, I feel your pain... that's why I want to merge this PR ASAP!

.resx cleanups are hard to coordinate!

@this the web devs at my workplace (long gone now) did that for order line items. Querying that database is bloody impossible... for sanity's sake if you can turn your JSON into a proper schema with actual typed columns, do it - it's worth the effort.

#ItDepends

something something tradeoffs something performance something usability

this

15:57

Yeah I definitely won't use JSON for line order items. That's silly. For settings relating to the front-end, it makes sense to minimize database involvement.

SQL Server does support parsing & building JSON in transact-SQL so you have that going.

Mathieu Guindon

hm #TIL

but yeah it does make sense for anything that doesn't need to be queried later by other systems, ....like order line items :)

this

so if you wanted to send/receive JSON to minimize the work of parsing in the front-end, you could do that way. I am not sure whether that's necessarily a good thing since with a web app, you're supposed to have a business layer.

Let the business layer handle the conversion from database's favorite structure into JavaScript's favorite structure. Or something.

Mathieu Guindon

yeah

the devs were very excited about Laravel (which I understand is some kind of ORM for PHP), ...and not much database design skills. well for a sane database schema anyway.

there's all kinds of weird assumptions baked-in everywhere, foreign key constraints are missing 90% of the time, ...

this

FWIW, for a SSIS package, I elected to write stored procedure that constructs valid XML documents because it's far easier to edit the stored procedure than it is to edit the SSIS package, so I figured best to do all the XML mucking in transact-SQL than in C#. Much easier that way since both XML and SQL are declarative whereas C# is imperative.

@MathieuGuindon that's the law of leaky abstraction at work again. You can abstract it all you want but it won't get you away from dealing the necessity of making a sane database design.

Mathieu Guindon

yep

and then add to that, that it's MySQL so you have a Date column that's non-nullable, containing "0" date values that SQL Server interprets as NULL. Now go and insert/update records from SQL Server to this madness.

IvenBach

16:08

@MathieuGuindon as I’m finding out.

this

@MathieuGuindon Yeah, when I first learnt about this "feature", I admit I did thought it was a clever solution to the problem of "it happened sometime in that month or year, not sure which day". Now, I'm "ugh, too clever"

Mathieu Guindon

@IvenBach it's almost there, this close :)

this

steps away

Mathieu Guindon

@this nah it's worse, ...they just made a column non-nullable and then just proceeded to use 0 as they would a NULL. I'm very, very happy I don't get to deal with the PHP code!

this

so they're using 0000-00-00?

Mathieu Guindon

16:12

yep

this

Well, fun times.

Mathieu Guindon

enum types are lots of fun too

this

FWIW, I recall this being a common hack in MySQL database to get it to be indexed

Mathieu Guindon

lol that's cute, thinking they use indexes

:)

this

rephrase: when they do think about indices which is not very often

Mathieu Guindon

16:13

that said I can't really talk about it right now and I don't want to jinx anything, but I might have found myself an exit strategy

3

this

@MathieuGuindon Nice, I hope it bears out!

Mathieu Guindon

thanks! it's looking very good so far anyway :)

but yeah these null-but-not-nullable dates were a major headache last summer.. just SELECT * FROM table in an openquery explodes and you're scratching your head like why... glad I ended up making it work, but it cemented my thoughts on MySQL :)

Duga

16:33

[rubberduck-vba/Rubberduck] vincehardwick opened issue #5771: COM object separated from underlying RCW

> **Rubberduck version information**
Version 2.5.2.5906
OS: Microsoft Windows NT 10.0.19042.0, x64
Host Product: Microsoft Office x64
Host Version: 16.0.13929.20296
Host Executable: EXCEL.EXE

**Description**
.NET Runtime unhandled exception - COM object separated from underlying RCW cannot be used

**To Reproduce**
Unknown - intermittent issue occurring when closing .xlsm workbooks

**Expected behavior**
Lack of unhandled exception.

**Screenshots**
See attached.
![Screenshot

[rubberduck-vba/Rubberduck] vincehardwick added label bug to issue #5771: COM object separated from underlying RCW

[rubberduck-vba/Rubberduck] vincehardwick created comment on issue #5771: COM object separated from underlying RCW

> Rubberduck unhandled exception - error trace.txt

Duga

16:47

[rubberduck-vba/Rubberduck] retailcoder closed issue #5771: COM object separated from underlying RCW

[rubberduck-vba/Rubberduck] retailcoder created comment on issue #5771: COM object separated from underlying RCW

> Duplicate of #5766

[rubberduck-vba/Rubberduck] retailcoder added label duplicate to issue #5771: COM object separated from underlying RCW

[rubberduck-vba/Rubberduck] retailcoder edited comment on issue #5766: Upgrade with 2 issues

> Thanks! Both are the same known issue explained in the announcement blog post.

IvenBach

17:31

Hrm.... I think some of these resources aren't being used. I'd rather restore unused resources than mistakenly delete ones that are needed/used.

Mathieu Guindon

@IvenBach they likely were though, and recently so; use ResXManager to verify that all resource keys exist for all languages - I suspect you'll find a "hole" for Italian for each one of the "unused" Italian keys

IvenBach

I am using it.

Mathieu Guindon

awesome!

IvenBach

It was pretty obvious for most of them where FooBar_SpecificName had all but Italian whereas BarFoo_SpecificName or within a FooBarUI.resx file SpecificName was found.

Mathieu Guindon

aye, they'd all be the keys your PR is moving around

I salute your courage for taking up this clean-up!

IvenBach

17:43

Some others aren't matching up and I'm having to assume as best I can the rename.

If I only knew Past-Iven "HHCIB"...

Mathieu Guindon

narrator: it wasn't courage

4

IvenBach

So much of what I do is from ignorance.

Mathieu Guindon

it pays off in the end

also, same here

IvenBach

At the very least I'm learning something.

Tedious to ensure I don't drop a resource. I'm sure one or more is bound to occur.

Mathieu Guindon

don't worry, as long as most of it moves along the other keys we're good

keep in mind all resources/translations will be re-gone over by next release

I mean don't destroy translations on purpose, but if a key just doesn't seem fit anywhere, don't fret over it

IvenBach

17:50

It'll be unintentional where it occurs.

Mathieu Guindon

and then if you destroyed a translation and later realize it was a mistake, ...nothing is ever permanently destroyed

this

narrator: and thus that's why we use SCC, kids.

IvenBach

Those that know better do.

Son Goku ssj4

18:16

@this @MathieuGuindon Thanks for the input. I was thinking about storing UserId, Settings (or SettingsCategory), Value (JSON parsable string). But few articles on the internet were quite against it because it's impossible to filter queries. You just get it all at once.
But I suppose that depends on what the app is, size, how much settings, etc etc.

"SQL Server does support parsing & building JSON in transact-SQL so you have that going." <-- @this is really interesting. Will google that up. :)

this

Yeah if you do need to query on it, then I wouldn't do it as a blob. It only makes sense if you do not want the database involved in what are basically implementation details for the front-end.

Son Goku ssj4

It would be interesting to find out how for example Youtube stores this. There are 2.3 billions of users so... It has to be somewhat quick and intelligent.

this

JSON support was introduced in SQL Server 2016, I think.

Mathieu Guindon

probably tanks performance, but could work for some purpose. at my old VB6 gig we had TONS of XML and we had T-SQL crunching it for reports... a fast report was a report that rendered in under 30 minutes

this

Pretty sure Youtube isn't using a relational datastore for it.

@MathieuGuindon ov vey.

still can't get over the fact that there are people who think taking 30 minutes to get a report is perfectly fine

Son Goku ssj4

18:20

So something like MongoDB? I haven't tried NonSQL databases yet... Don't know what to expect or if you can (should) combine them.

Mathieu Guindon

the doors/window manufacturing ERP stored every single nuts & screws in a XML schema for the "model definition" - everything. and then you had the pricing/promo code iterating the nodes to discover whether or not this door or that window was using the glass type currently under promo... was so much fun

@this "meh just run it overnight"

Son Goku ssj4

:D

this

@SonGokussj4 You can combine them - I understand some enterprise do. For small businesses the complexity isn't worth it.

@MathieuGuindon there's the wrong way, then there's the right way, then there's the xml way

looking back the XML-mania was wayyyyyyy overblown

Son Goku ssj4

And on the another note - how are typically these settings applied? Let's say I have 2 pages and in settings the page width and background color. (Or for some practical example in RD: if for example autocompletion should work (fire) after each key press. in VBE)

If I visit the first page, I should download the settings. That's logical. But when I visit the second page, isn't it a waste of time to download it again?

But if I save it in browser cache for example and change the background from black to white, how does the new page know which settings to apply? Or should there be something li…

this

I'd say that for the same small business, SQL server's native XML/JSON handling may be enough.

Son Goku ssj4

18:26

For the RD (because RD has TONS of settings) the same - I'm not sure how that works.

this

@SonGokussj4 your web application is responsible for persisting the changes in setting by writing back to the database.

I would think that a decent JavaScript cough uhm, excuse me..... library would have something to let you persist the change into both the local browser cache and write back to the database.

Son Goku ssj4

Ah okay. So download settings once. Store them into cache. When getting the value, always check if settings are loaded. If not, load them.
In Settings windows, if I change something, it should write both to DB and modify the Cache.
That's good.

If I have some form in VBA and settings are on a sheet, the process should be the same? Load into an object in memory, then always check if the object exists, if not, load it up, if yes, use the value, then if settings changed, write it to the object and to the sheet?

Mathieu Guindon

cache invalidation FTW!

(it's one of the "hard problems" of programming - like naming... and off-by-one errors!)

this

Yes, you got the idea. The same principle is what we use for RD setting btw

and because of that, it's also one of more complicated piece of the components in RD.

7 mins ago, by Mathieu Guindon

cache invalidation FTW!

Duga

19:01

[rubberduck-vba/Rubberduck] IvenBach synchronized pull request #5749: Consolidate resource cleanup

Vogel612

@this mumble mumble cookies

cookies have inbuilt cache invalidation

(then again they would be sent with every request, so probably not that good an idea)

IvenBach

@Duga A bit of back and forth with removing, then adding. mumble mumble progress not straight path mumble.

Duga

19:29

[rubberduck-vba/Rubberduck] build for commit 66d55c6d on unknown branch: AppVeyor build succeeded

this

@Vogel612 I've done precious little web development but I seem to recall that javascript libraries (I want to say vue.js in this case but not sure) provide an abstraction so that you can just say "hey, cache this" and it figures out the best way to do it.

Vogel612

depends on the library, really..

this

in my case I did not want to even think about the implementation. Just get the job done, and get out of there in a hurry.

mansellan

oh cool, dzzie has made VBDec available free (but not open) for personal use - link

looks like he's also moved where he blogs now - link

this

may want to share w/ Wayne, too.

mansellan

19:44

yep good shout

Mathieu Guindon

> Author at Avast Thread Labs

grr

lol

mansellan

haha

this

@MathieuGuindon at least it's not Symantec....

Mathieu Guindon

perhaps we can get him to ..IDK... if (filename.StartsWith("Rubberduck")) { continue; }

#NotABackdoorMoveAlongNothingToSeeHere

this

@MathieuGuindon huh, funny that, I was watching the news and it says that there has been lot of malicious virus with names starting with Rubberduck and they're destroying trillions worth of stuff.

Mathieu Guindon

20:13

@mansellan ha, I thought the name was familiar... mistook him for this guy lol

David Zemens, Michigan

51.2k 11 70 118

mansellan

20:43

lol

Hosch250

22:18

@theVBE-it'srightforme The talk went good. They never locked it down so you have to sign in, so you should be able to watch it if you are interested.

It was recorded, along with the rest of them.

the VBE - it's right for me

23:01

@Hosch250 link?

glad to hear it went well.

@this may I conclude db design skills are still in high demand?

this

on its own? not very marketable. What is far more marketable is the business consultancy. There are too many code monkeys who do not have the aptitude to understand the business first and as a consequence, they throw more technology at a problem. Poorly designed DB is just one of many symptoms.

I'd also assert that you can't properly design a DB if you don't understand the business so that's a prerequisite -- all the normalization stuff is essential but useless if you have no idea what the business problem is.

Transcript for