VBA Rubberducking

Vogel612

7:00 PM

depends on how well the data-driven tests fail

if you can only fail a data-driven test "all or nothing" that's not helping

if you get separate fails for every data parameterization, it's probably easier to use data-driven here

Mathieu Guindon

OP's For loop then

Vogel612

the problem with OP's for loop is that most frameworks abort after the first assertion failure

so you get incomplete information

Mathieu Guindon

good point. I also feel like Public Sub Remainder doesn't tell me what the thing is testing, and what it's testing depends on the arbitrary data: the intended test cases aren't half as clear-cut as dedicated test-per-testcase

IvenBach

@Vogel612 I figured out --soft and --hard but I get confused with the terms. Are "Index" and "Snapshot" synonyms? I can't understand the manual, yet. I was reading at atlassian.com/git/tutorials/… and they use "Snapshot"

Mathieu Guindon

that said I'm probably missing a test or two that assert the actual correctness of the calculation

Vogel612

7:07 PM

@IvenBach dunno, lemme check.

"staged snapshot" ~= "index" ~= "staging area"

IvenBach

A word by any other name would still mean the same... So long as they are indeed referring to the same thing.

Vogel612

the problem with the idea of a snapshot here is that the git "snapshots" (aka. index) may be "partial", which is not usually a property of snapshots

IvenBach

What's a partial snapshot then?

Vogel612

do you remember way back when we started with git?

the different ways of how source control can work?

IvenBach

I don't remember much of when I started.

Vogel612

7:16 PM

so there's two ways to store versions

either you go "start here, then do this, then this, then this, then this, and now you're done"

Duga

[rubberduck-vba/Rubberduck] retailcoder edited comment on issue #4385: Assert.SequenceEquals Not Working as Expected?

> That test is essentially a "poor man's data-driven test" working around lack of data-driven support for Rubberduck unit tests (#1229).

An alternative work-around could be to split it into multiple tests that explicitly cover a single case, for example:

```vb
'@TestMethod
Public Sub GivenSameDividendAndDivisor_RemainderIsZero()
Const value As Double = 10
Const expected As Double = 0
If value = 0 Then
Assert.Inconclusive "Test is misconfigured"
Exit Sub

Vogel612

and that's "diff-based"

Mathieu Guindon

@Duga had to edit again, linked to the wrong issue

Vogel612

so you have a start, add some "changes" until you get to the "current version"

the alternative is to store each version completely separate from every other version

IvenBach

You're talking about the differences in version control.

Vogel612

7:21 PM

and the easiest way to do that is to use "snapshots"

@IvenBach yes.

everything in git is a snapshot

Mathieu Guindon

You should probably specify Option Explicit at the top of the module. That would highlight things like sSQLSting (boy that's an easy typo, btw) being undeclared (and sSQLQry being unused). Proper indentation would enhance readability, too. — Mathieu Guindon 30 secs ago

s[string] SQL Sting

Vogel612

usually you'd not think that a snapshot doesn't encompass "everything"

IvenBach

'Initial commit
Public Sub Foo()
    Dim bar As String
    bar = "duck"
    Debug.Print bar
End Sub

'Alter bar's value commit
Public Sub Foo()
    Dim bar As String
    bar = "DUCK"
    Debug.Print bar
End Sub

^ Git has a full entire snapshot of each

Vogel612

correct

svn instead stores:

IvenBach

Whereas a delta-diff VCS would be just the change between "duck"-->"DUCK"

Mathieu Guindon

7:24 PM

@IvenBach that's how you can browse any file in the repository at any given tree

Vogel612

'Initial commit
Public Sub Foo()
    Dim bar As String
    bar = "duck"
    Debug.Print bar
End Sub
---
- 'InitialCommit / + 'Alter bar's value commit
- bar = "duck" / + "bar = "DUCK"

IvenBach

@MathieuGuindon :lightbulb: Now I'm using them electrons.

And another reason why it's so fast.

Vogel612

exactly

IvenBach

:+1:

I take a long while but eventually get there.

Vogel612

now the cool thing about git snapshots is that they are smarter than you think

IvenBach

7:26 PM

Howso?

Vogel612

consider multiple files

what happens if a file does not change between commits.

How do you avoid storing the same file for every commit, even if it doesn't change?

Mathieu Guindon

7:40 PM

#magic

Comintern

Is there any reason RD couldn't do data driven tests?

We could pull the parameters out of annotations.

Mathieu Guindon

@Comintern that's the idea of #1229

FreeMan

0

Q: Lathe faceplate - ID and use

I've bought a second-hand lathe to try woodturning. Question 1. What make is the lathe, in case I need spares? there are no markings on it at all. About 1m long, cast iron hollow rectangular pylon at head end, twin round rails to which tailstock and tool support clamp. Twin square rails as a bas...

lathe

"I have a motor vehicle. It has 4 tires and a steering wheel. What kind is it?"

Mathieu Guindon

a farm tractor, duh

Comintern

> M6 thread fits them. I'm wondering, why threaded?

Ummm, so the M6 screws don't just fall out?

Vogel612

7:49 PM

@MathieuGuindon there is no such thing as magic

Mathieu Guindon

I hope I don't lose too many followers for this, but too bad for them if I do. This shit is serious.

I try very hard to stay away from politics and controversy (there's plenty of it on Twitter already IMO), but this is important and I simply can't not retweet this thread. https://twitter.com/jezhumble/status/1043194008896524288

Tweeted by rubberduckvba on September 21, 2018 at 7:33 PM

@Vogel612 except (computer) science so advanced it's indistinguishable from it =)

Vogel612

meh.

I grant you that deep neural networks are magic

everything else... de-magicable

I can give you a hint :)

say you have an object. And you need to know about the object in different instances of another class.

how would you go about "saving" (aka. storing on the heap) the object only once?

Mathieu Guindon

not sure I understand the premise

"the object in different instances of another class"?

so there's an instance of Class1 and Class2, both holding a reference to Thing1

click

pointers

Vogel612

ding

correct

FreeMan

@Comintern 'cause if it ain't threaded, it ain't a screw, it's a pin.

Comintern

7:58 PM

Yay! I think I finally found my memory leak.

KySoto

bleh, i cant think of a SQL way to get a value from the previous record

im reduced to using the ultra slow method of using a VBA function to get it

Mathieu Guindon

@Comintern you remembered it?

@KySoto "previous record" ...based on what?

Comintern

LOL. I was remembering right up to the point where I crashed with an out of memory error.

Mathieu Guindon

the clustered index? some custom sort?

Comintern

public static void WriteStatus(int current, int total)
{
    var write = "Processed " + current + " of " + total + " records.";
    Console.Write(new string((char)8, write.Length) + write);
    if (current == total)
    {
        Console.WriteLine(string.Empty);
    }
}

^ That.

KySoto

8:03 PM

@MathieuGuindon We have engineering masters that have a set of sequence steps that operators clock labor into

this

@KySoto use window functions. That's what it's for.

Mathieu Guindon

^

KySoto

they FREQUENTLY type in the wrong quantity at some step

this

depending on what you need, LAG() or LEAD(). You might actually want to use other aggregation functions.

IvenBach

@Vogel612 It only stores the file if it flags it as being modified, and you add it to the Index.

Mathieu Guindon

8:03 PM

@this I'd do a windowed row_number() in a CTE

KySoto

SO im building a query that will give us a report of all of the active work orders where those quantities are bad

Mathieu Guindon

googles up LAG and LEAD

Vogel612

@IvenBach hm. but if you "jump to a commit" then, how do you know which files there are in the repo, if it's not been added to the "snapshot"?

Comintern

I was just thinking ROW_NUMBER myself.

KySoto

and the simple way im doing this is getting the good and bad quantities from the current step, then comparing it to the previous steps good quantity

Mathieu Guindon

8:04 PM

OMG WHERE WAS LAG ALL MY LIFE

this

You're welcome.

Mathieu Guindon

not that I'd need to use it very often

KySoto

if the current steps quantity good + bad is higher than hte previous steps good

then we flag it

IvenBach

@Vogel612 Not quite sure I understand your question ~~Big Dan~~ Vogel. Wouldn't it traverse the parents to see what files should be there?

this

I don't use it very often either but when it's needed, it's a lifesaver

KySoto

8:05 PM

if its the first step, we get the amount we created our work order for

Vogel612

@IvenBach but then you don't have independent commits

this

@KySoto in this case, I think you want a SUM(...) OVER (... with a range bounding.

KySoto

i guess it does make sense to do it in sql server and make a view out of it

but since i didnt know how to get the value i needed

i had to do it via access

since i know i can get it from a vba function

Mathieu Guindon

oh no

this

no, don't

use sql server

Mathieu Guindon

8:08 PM

^

KySoto

also, i have no idea what sum(...) over(... with range bonding is

Comintern

^^

this

data belongs in database.

IvenBach

@Vogel612 That's why I'm missing something and don't understand your question.

this

buy my book, maybe? :p

Mathieu Guindon

8:09 PM

@KySoto shortly put: THE BEST THING SINCE SLICED BREAD

KySoto

or chocolate?

:P

AND chocolate

O_O

but not Whiskey

i cant do whiskey

last time i drank it

i had one shot, (of only that) and woke up with the swims

Mathieu Guindon

8:11 PM

shot?!

KySoto

yes?

Mathieu Guindon

lol, nope :)

KySoto

wow. piece of junk copy paste

i was going to get a data sample, and it was like. naw cant paste this into excle

Vogel612

@IvenBach Think of a commit as an instance of a class

KySoto

maaan

Vogel612

8:18 PM

what members does the class have?

KySoto

lag and lead are sql server 2012

gdit

i have sql server 2008

IvenBach

@Vogel612 Whatever members you define it to have, and whatever it Inherits or Implements. Is this a trick question?

Comintern

@KySoto You should have ROW_NUMBER

Vogel612

go ahead and define these members

what API does a commit expose to git internally?

IvenBach

I have no clue.

Vogel612

8:20 PM

what API does git expose to you as the user on a commit?

IvenBach

I don't know.

KySoto

@Comintern man this just got even more complicated -_-

also that could give me an incorrect value

IvenBach

@Vogel612 Would it be the flags you can use in git-scm.com/docs/git-commit?

Vogel612

no. git-commit is a commit's constructor

and the flags are the constructors arguments

Comintern

@KySoto Huh? What would be an incorrect row numbeR?

KySoto

8:24 PM

the row number would be correct but i dont know how i could properly get the appropriate value

Mathieu Guindon

that's what you use the windowing for

KySoto

i have literally zero idea what windowing is

Mathieu Guindon

row_number() over (partition by {fields} order by {fields})

KySoto

partitioning

Comintern

Think of the row numbers down the side of an Excel sheet. It literally does that in a field.

The partition is like a group

IvenBach

8:26 PM

@Vogel612 I'm struggling with what you're asking.

Mathieu Guindon

say you have dates and you want to number them. if you partition them by month-of-year then you get the row number to reset to 1 every first of the month

Vogel612

git can do stuff with commits. Like:
- show a log of commits
- check out a given commit
- merge two commits
- ...

Comintern

Or if you have transactions for vendors. You partition by vendor and order by timestamp.

Mathieu Guindon

^ yeah that's a better example, perhaps

Vogel612

what would a class "Commit" expose to facilitate / enable these operations

KySoto

8:28 PM

so i have this set of fields:

IvenBach

I don't know since I don't understand everything git does. Is git-scm.com/book/en/v2/Git-Internals-Git-Objects what you're asking?

KySoto

[WORKORDER_TYPE]
,[WORKORDER_BASE_ID]
,[WORKORDER_LOT_ID]
,[WORKORDER_SPLIT_ID]
,[WORKORDER_SUB_ID]
,[SEQUENCE_NO]

the first field id need to filter with, the next 5 need to be joined

then i have to figure out what the previous sequence_no is

but the numbers are pretty arbitrary

some work orders start at sequence 70 and skip by 10s, or not depending on who made it

Vogel612

@IvenBach you're skipping ahead

Mathieu Guindon

,[RN]=row_number() over (partition by [WORKORDER_TYPE] order by [SEQUENCE_NO])

KySoto

work order type is not unique

IvenBach

8:30 PM

I'm just trying to figure out what you're initially asking.

Vogel612

but yes, that's what I'm asking about

KySoto

in fact i need to set it to "W" because this table stores quotes "Q" and masters "M"

and im looking only at "W"

Mathieu Guindon

doesn't matter

just try it!

IvenBach

Don't forget that even though I can better use git now I still have no clue what it's doing. Just like I can use Windows but don't know anything about WM api calls or anything underneath the GUI.

Mathieu Guindon

then play with it, change the partitioning (you can specify multiple fields to partition by), see what results you get

it's really the only real way of understanding just how awesome windowing functions are

Comintern

8:33 PM

You probably want something like this:

SELECT WORKORDER_TYPE,SEQUENCE_NO,ROW_NUMBER() OVER(PARTITION BY WORKORDER_TYPE ORDER BY SEQUENCE_NO) AS NORMAL_SEQUENCE FROM Whatever

Mathieu Guindon

and once you grasp windowed sums, start playing with rows unbounded preceding - and now you can single-handedly issue year-to-date summaries instantly spit-out by SQL Server, without involving any Excel pivot

@Comintern ..looks very much like what I did, except with an explicit AS clause for the alias :)

AND SCREAMCASE. SCREAMCASE EVERYWHERE!

KySoto

so this is my sql statement

SELECT
      [WORKORDER_TYPE]
      ,[WORKORDER_BASE_ID]
      ,[WORKORDER_LOT_ID]
      ,[WORKORDER_SPLIT_ID]
      ,[WORKORDER_SUB_ID]
      ,[SEQUENCE_NO]

,[RN]=row_number() over (partition by [WORKORDER_TYPE] order by [SEQUENCE_NO])
  FROM [VMFG1].[dbo].[OPERATION]
  where WORKORDER_TYPE = 'W' and WORKORDER_BASE_ID like 'W%'
  order by WORKORDER_BASE_ID desc,WORKORDER_LOT_ID,WORKORDER_SPLIT_ID,WORKORDER_SUB_ID,SEQUENCE_NO

Comintern

I scream case SQL keywords.

KySoto

WORKORDER_TYPE	WORKORDER_BASE_ID	WORKORDER_LOT_ID	WORKORDER_SPLIT_ID	WORKORDER_SUB_ID	SEQUENCE_NO	RN
W	W69918	1	0	0	70	277369
W	W69918	1	0	0	80	317899
W	W69918	1	0	0	90	371501
W	W69918	1	0	0	100	385067
W	W69918	1	0	0	110	433450
W	W69918	1	0	0	120	459636
W	W69917	1	0	0	70	277368
W	W69917	1	0	0	80	317900
W	W69917	1	0	0	90	371500
W	W69917	1	0	0	100	385068
W	W69917	1	0	0	120	459637

yeah i just threw it together

those row numbers are all over hte place

oh. derp. over base_id

Mathieu Guindon

that's because of the order by clause

make the final sorting match the partitioning & ordering, then everything should be in sequence

KySoto

8:36 PM

WORKORDER_TYPE	WORKORDER_BASE_ID	WORKORDER_LOT_ID	WORKORDER_SPLIT_ID	WORKORDER_SUB_ID	SEQUENCE_NO	RN
W	W69918	1	0	0	70	1
W	W69918	1	0	0	80	2
W	W69918	1	0	0	90	3
W	W69918	1	0	0	100	4
W	W69918	1	0	0	110	5
W	W69918	1	0	0	120	6
W	W69917	1	0	0	70	1
W	W69917	1	0	0	80	2
W	W69917	1	0	0	90	3
W	W69917	1	0	0	100	4
W	W69917	1	0	0	120	5

yep

thats better

That looks better.

@IvenBach then I don't understand what more you need to know about reset ...

but eh.

KySoto

because if its a 1, then return a 0

for hte sequence

Mathieu Guindon

8:37 PM

just stick a -1 in there then

Comintern

- 1?

KySoto

well so now that i have row numbers

Comintern

You actually had them before, you just normalized them.

KySoto

now that i have useful row numbers

i might be able to make what i need

Mathieu Guindon

now turn that select into a CTE, since you can't use LAG - you'll need to join it onto itself to get the RN for the previous record

I can't imagine doing that in Access

KySoto

8:39 PM

yeah CTE doesnt exist in access

Mathieu Guindon

well it'd be a subquery

KySoto

i had to create a function to get the previous record

some people would use dlookup

but that is slow as balls

i just created a parameterized query that was as efficient as i could make it

Mathieu Guindon

re-run your query with the "show execution plan" - make sure you have all the indexes you need

KySoto

but you are still opening and closing a recordset for every record

Mathieu Guindon

(there should already be an index on the columns you're partitioning and ordering by though)

KySoto

8:41 PM

heh yeah, i probably dont, i mean this is a sql server 2000 database replicated over to sql server 2008r2

Comintern

They had indexes long before 2000 ;-)

Mathieu Guindon

then they'll definitely pop up in the execution plan if they're missing

KySoto

yeah but the replica part

Comintern

Shouldn't matter.

KySoto

i dont know if the index's were properly brought over

Mathieu Guindon

8:42 PM

with proper indexes the whole operation should be pretty much instant

KySoto

i know the table designs dont show the pkey

oh, damn

Key != index

true

yeah but no PK => no clustered index => table scans every time

Comintern

8:43 PM

true

KySoto

yep, its a nonclustered index

2% cost though

Mathieu Guindon

ok, so there's an index, and execution plan isn't screaming about a missing index

and you're hitting the one that's there

that's good

Comintern

That looks like it should have a composite PK on WORKORDER_TYPE, SEQUENCE_NO

Mathieu Guindon

I only ever use a composite PK for many-to-many assoc. table

KySoto

yeah, they do have a composit key on the set of data i am working with

Mathieu Guindon

8:45 PM

@Comintern in my db that would be a unique constraint named NK_TableName

NK => "natural key"

KySoto

yeah the index scan is only 2% on the execution plan

the two sorts i have are 49% each

Comintern

In my DB it would have some crappy random name.

KySoto

the row number sort, and the overall sort

Comintern

I suck at naming constraints.

Mathieu Guindon

alter table mrp.[Fabrics] add constraint PK_Fabrics primary key clustered (Id);
alter table mrp.[Fabrics] add constraint NK_Fabrics unique ([Collection],[Code])

alter table mrp.[ModelCategories] add constraint FK_ModelCategories_Model foreign key (ModelId) references mrp.[Models] (Id);

KySoto

8:47 PM

heh you have schemas

we have... dbo

Mathieu Guindon

works just as well

KySoto

no other schemas at work here

Mathieu Guindon

the DB has several purposes, that's why I broke it down into schemas

Comintern

schemas are more useful for partitioning data or managing access than anything else.

Mathieu Guindon

@Comintern it's really about picking a scheme, and sticking to it :)

Tweeting Duck

8:49 PM

I try very hard to stay away from politics and controversy (there's plenty of it on Twitter already IMO), but this is important and I simply can't not retweet this thread. https://twitter.com/jezhumble/status/1043194008896524288

Tweeted by rubberduckvba on September 21, 2018 at 7:33 PM

Comintern

So just out of curiosity, what is the benefit of non-natural PK?

Mathieu Guindon

single-column FKs/joins over ints

Comintern

Ah.

I'm so used to multi-field joins in the crap databases I deal with I tend to overlook that.

this

Personally I have my doubts about natural keys. If the natural keys are alphanumeric, then you are going to end up taking more space than if it was a surrogate integer key.

and more space = more work in the join

but TBH, I never actually put this to a serious test. Probably should.

Mathieu Guindon

the unique constraint is a built-in data integrity assurance that you get for free, and an index you get on columns that are VERY likely to be filtered on

I never use NKs for joins

not in my schemas anyway

I'm just very systematic with db design... served me well to this day :)

this

8:55 PM

RE: unique, that's not a reason why you can't use SK, since you still can create. The better argument would bet ath you end up with an extra unique index you wouldn't otherwise have had it been used as a NK.

Comintern

Oh yeah, I am too.

this

I think you're in minority if you can manage to not use NK in the joins. All samples of NKs I 've seen always end up being used in a join.

Mathieu Guindon

huh

when interfacing with 3rd-party systems, yes. when querying my own stuff, nope, never.

Comintern

I have an address table for a side project that uses the hash of all the fields as the PK (generated by an insert trigger).

Mathieu Guindon

maybe I should write a book ;o)

Comintern

8:57 PM

I'd probably do something similar with other more complex natural keys.

this

One of my co-author advocates for "use NK when there's a good one, otherwise surrogate it" which seems more sensible. It's the joins that worries me the most.

@Comintern as long you don't exceed the hash length, but if you do, then don't you end up with collisions?

Mathieu Guindon

I use a PK/SK in every single table I create, and identify a NK that I enforce with a unique constraint too. ..every time.

> ZOMG but you have an extraneous index you don't KNOW you're going to need!

yes, and proud.

Comintern

@this I've yet to have a collision. Granted, I only have about 10 million addresses.

this

Wait - I see what you mean now - when I talk about SK/NK, I usually implicitly mean them as a PK.

If they aren't PK, they're just indexes.

Mathieu Guindon

As above: PK (clustered) is always an int identity(1,1) column named Id

this

9:04 PM

In my book you're using SKs, then.

Mathieu Guindon

yup

this

It's not as useful to say you're using NKs for index compared to using NKs for PKs.

Mathieu Guindon

and the application consuming the db never ever cares about them

this

since in theory, all indexes should be based on some kind of CKs.

Agreed. That's not application's concern.

Mathieu Guindon

What's a CK?

this

9:06 PM

candidate key

e.g. an academic term that only mathematicians like Chris Date care about.

all keys, whether it's PK, NK, or SK has to be derived from a CK. Otherwise, it wouldn't be a key in first place.

KySoto

yeah so it looks like this thing i made really only needs to be ran once

because the macro i implemented will catch any future errors

Duga

[rubberduck-vba/Rubberduck] simplesolutionssas created comment on issue #4385: Assert.SequenceEquals Not Working as Expected?

> I considered splitting the test in several smaller tests, but since this is a rather simple function in a rather extensive Arithmetic class, I decided to try and keep all test cases as compact as possible.

Anyway, this worked:
> `Dim ExpectedRemainders As Variant`
> `ExpectedRemainders = Array(CDbl(0), CDbl(1), CDbl(1), CDbl(0), CDbl(0.2), CDbl(0.2), CDbl(0), CDbl(-1), CDbl(-1), CDbl(0), CDbl(-0.2), CDbl(-0.2))`

Thanks Mathieu.

[rubberduck-vba/Rubberduck] simplesolutionssas edited comment on issue #4385: Assert.SequenceEquals Not Working as Expected?

> I considered splitting the test in several smaller tests, but since this is a rather simple function in a rather extensive Arithmetic class, I decided to try and keep all test cases as compact as possible.

Anyway, this worked:
> `Dim ExpectedRemainders As Variant`
> `ExpectedRemainders = Array(CDbl(0), CDbl(1), CDbl(1), CDbl(0), CDbl(0.2), CDbl(0.2), CDbl(0), CDbl(-1), CDbl(-1), CDbl(0), CDbl(-0.2), CDbl(-0.2))`

Thanks Mathieu.

By the way, nice idea the one you have going on for

Mathieu Guindon

9:23 PM

@Duga yet I don't know how your code reacts to a 0 divisor =)

Vogel612

hmm ...

Mathieu Guindon

Excel folks are funny

@rubberduckvba That is a solid strategy! Sounds like you're a pro. 😉

Tweeted by msexcel on September 21, 2018 at 9:18 PM in reply to rubberduckvba

Vogel612

the extensibility assembly is not available for .NET Core projects by default it seems

Mathieu Guindon

TTQW

this

@Vogel612 even if it were, I kind of doubt it's the only thing that 's not on .NET core

besides what does Core buy us?

Vogel612

9:31 PM

the new csproj format seems to kinda heavily push to core

this

well core & standard are the new thing. Still, I don't think we can benefit that much from either.

Vogel612

yea

Peter M Taylor

10:14 PM

@all any suggestions to improve notepad in Window 10. An executive in MS is tweeting if people got ideas to improve calculator and notepad...now the time. onmsft.com/news/…

IvenBach

11:30 PM

If hoping and wishing made code compile and work...

Transcript for