Structured Arrays Primer – Part 2

unclepetecorner

We ran out of time last week when covering structured arrays.  This week we will wrap up with Seeking on Structured arrays, some additional syntax that is available to use for structured arrays, and then get wild and crazy and talk about associative structured arrays. With a few more real world examples. After this week you primed with all of the foundational pieces that make up the Andy’s File Manger Classes, so he can pick the ball after that and take it home!  So let’s get started.

Seek and Ye Shall Find

When we last left our hero he was just about to start seeking on a structured array. Seeking on a structured array is similar to Seeking on a simple array, just on steroids.   Because we have structured data, we are able to seek on multiple pieces of that data. But let’s start with the a simple one.

2014-08-01_0259

That returns 13, which is the subscript the element where Sales Month is January 2011. Before we talk about what we do with that value. Let’s modify our structure and a Location to it.

2014-08-01_0305

And with some adjustment of our code to populate the array we will have 36 months of data for two separate locations.

2014-08-01_0314

And now the following statement will return 25, as it is seeking on two columns of our structured array.

2014-08-01_0315

And this statement will return 26 as it finds the data for Location 2.

2014-08-01_0315_001

Accessing the Elements

So now that ArraySeek has return the subscript to us just what do we do with it? Those familiar with WX could probably guess that there are multiple ways that we can use the subscript to access the element.

First we could move the element into a structured variable. Remember from last week we have a structured variable called OneMonthsSales

2014-08-01_0322

We can move the element of the array into that structured variable like this

2014-08-01_0325

And then we can use any of the individual portions of that structure like this

2014-08-01_0326

But there is a sort of short hand version that we can also do. Instead of moving the element into a structured variable first we can directly access the individual pieces like this

2014-08-01_0329

So when should you use one versus the other? Well it depends (of course!). But generally speaking I tend to use the short hand version when I just need to access one or two pieces of data from the element, but if I am using the entire element, for say an update form etc., then I will go ahead and move it into a structured variable.

But wait there’s more!

We can take two of the advanced building blocks we have learned about and combine them. Structured Arrays + Associate Arrays = Array Addiction!

Remember a structured array is just an array with complex data, and an associative array is a indexed array. So by combining the two we can have arrays of complex data that is indexed.

So what does all that mean? Let’s take our sales example that we have been working with. And let’s say we need to maintain that array in our program. So every time a sale is made we need to update the appropriate element of the array.  For our existing array our code might look like this

2014-08-01_0437

We do an ArraySeek to find the Element, update it if it exists, or add a new element if it doesn’t.

But what if we instead created an associate array. We can use the same structure we have been using, but just declare our array as an associative array.

2014-08-01_0440

Our code to populate the array changes slightly

2014-08-01_0441

The only real difference is instead of using the ArrayAdd function, we instead use the associate syntax to create an element indexed using the data and locationid (YYYYMMDD-#).

So now that we have a structured array how would we get the total profit for location 1 for January 2011?

2014-08-01_0446

That is a little cleaner than doing an array seek, but why would we do all of this. Accessing an associative array will be much faster than accessing a standard array, just like accessing a database table via an index is faster. Will this mean a lot if you only have a few elements in your array, probably not, but if you have 1000’s of elements you will probably see a big difference.

But there is a caveat to all of this. Remember when we worked with simple associative arrays, if we tried to access an element for a nonexistent index it would be created with the default value? Unfortunately that is not true for Associative Structured arrays. We will get a runtime error if we try to access an index that doesn’t exist.

2014-08-01_0452

2014-08-01_0451

So how do we deal with that? Well let’s look at our sample code to update sales for the associate array

2014-08-01_0453

Notice we check the ..Empty property. This returns true if the index doesn’t exists.  This little gotcha is the only reason I don’t use associative structured array more than I do.

Real World Solutions or Arrays for Fun and Profit

So lets look at a few real world examples where I used arrays to solve an issues. We won’t dive deep into the code of these examples, as the point is just to give you some food for thought about different ways you might use arrays in your projects.

Fixed Lookups and easier combo boxes

Most of my projects have a few different pieces of data that are what I call “Fixed” lookups, in other words there are a few different selections, to choose from but the choices aren’t anything that needs to be end user configuration. Status codes is a prime example.  How I handle those is I create a Lookup Structure.

2014-08-01_0511

Then I have several different arrays all based on that structure. POStatus, InvoiceStatus, etc. And in the initialization code of my app I populated the values.

2014-08-01_0522

 

One big advantage to this approach is I can populate my combo boxes based on the array setting the displayed value to “Name” and the returned value to “Value”. If you hard code the choices into the combo control, they are much harder to maintain, especially if the list exists in more than one place, and WX (especially WebDev) tends to want to give you the subscript as the return value of the combo box instead of the descriptive text. I often store the descriptive text of status in the database and that would be a PITA if I didn’t use arrays.

Performance Enhancement

Unfortunately my currently loaded test data doesn’t give a complete picture of just home complex the below screen is.

2014-08-01_0534

Everything about this screen is database driven.  There can be up to 4 Sample Types (that is the red boxes), the can be a multitude of Profiles (one of the combo boxes), for each profile there can again be a multitude of test and results. Each test can have a complex set of rules that determines a number of warnings and flags as well as results validation. All of these rules also have conditional branches based on a number of attributes of the subject such as Gender, Age, Smoking/NonSmoking, etc.

When we original wrote this screen we pulled everything from the database, and each time you dropped down the profile combo and changed profiles, we had to load the test for that profile, then go through each test and perform all the rules calculates for that test to display the appropriate flags and validation warnings. A complex study configuration could literally cause this screen to perform over 1000 database fetches. This process could cause a 15 second pause when the profile was changed. Once we had the screen functional, I went back and reviewed all the logic in an attempt to boost performance.

No we load all of the rules based configuration, ranges, test specifications, etc. into arrays when the screen is first loaded. This causes about a 1 or 2 second hit on the first screen load, remember FileToArray is crazy fast! All my logic now uses the arrays instead of fetch and refetching database records. The results? Subsecond response time when changing profiles!!!!

Note a tip here. I first got the screen functional using database calls first. The logic of this screen was very complex and it was much easier to get the general structure of the logic right before bringing arrays etc. into the mix. As they say, you have to eat the elephant one bite at a time!

Different Views of the Same Data

We briefly discussed this one last week. With TeamAlogy.com‘s dashboard it is very common for me to be summarizing over a 100,000 database records. With multiple views of that data based on the different user defined attributes such as (Age, Gender, Transaction Size, etc). And all of this with comparisons for Last Month versus this Month. One location versus another location, the list goes on and on. With the user having the ability to drill down and slice and dice the data as they need.

Again my first build I directly accessed the data, using all of the current drill down and filtering settings of the user’s session to build very complex SQL WHERE clauses, and then issuing multiple SELECT statements with different GROUP BY clauses for the different views. In a traditional transaction system this makes sense as it limits the amount of data “moving over the wire”. But for a Web Service that has a location connection to the data, and a dashboard application that is rehashing the same data multiple ways, it just isn’t very efficient.

So now instead the only portion of the users settings that effect my WHERE clause is the date range and of course their company etc. And I loaded the raw data into an structured array, every single answer to every single question is an entry in my “Raw” array.  If they change data ranges I check to see if I already have that “chunk” of data loaded and only load in whatever additional data is needed.

For there is is just a matter of sorting and processing the Raw Array to build arrays with different views, and then sometimes, using the arrays to again process, summary and generate yet more views. Since ArrayToFile is crazy fast (and the data is local to the webserver) the “Raw” data array loads in no time, and then processing it is all in memory and very fast.

As of today, the array definitions for the TeamAlogy Dashboard class start on line 142 and continue to line 350. Over 200 separate arrays for one function. But be sure to check back with me tomorrow it might be 300 🙂

What does this have to do with Andy’s File Manager Classes?

As I have said, if you grossly over simply it, the File Manager Classes are just fancy Structured Arrays. How can this be? Well ignore the academics that are about to have a heart attack and indulge me for a second. A class is really just a fancy structure. That happens to have procedures and functions (methods) that can be ran. But the members and properties of a class are nothing more than a group of different variables definitions, sound familiar? Isn’t that what a structure is?

Well pcSoft thankfully realized that and gave us the ability to create a array on a class the very same way that we would create a structure. So let’s take a quick peek behind the curtain of the File Manager classes  (Andy might have said under the Skirt!) and see if we can find the array foundation hiding in there somewhere.

First we are going to be looking at Andy’s original File Manager Classes he has recently released a version that uses abstract classes but not to worry the principal is still the same as far as the array portion is concerned.

So for each database table, there are two classes declared. A Manager class, and a Record class.  Let’s look at the definition of the ProductRecClass for one of my projects

2014-08-01_0627

If you ignore Line 3 that is doing some fancy class stuff, and the private, public, and global keywords, you will notice that the definition of a class looks just like the definition of a structure. We additionally have properties, which will be exposed as additional columns  of the structure and there are a few methods, but we won’t get into those because the point here isn’t to teach you the ins and outs of the File Manager Classes, but just how the foundation is built on arrays.

So now lets look at the manager class for the Product table. The Manager class is really just a class to declare the array and then manage the array and the resulting database functions as required.

2014-08-01_0633

I highlighted the two lines we care about here. First Rec is declared as an instance of the ProductRecClass. Ignore the dynamic keyword, that is a fancy bit of class code we won’t get into. This is more or less the same thing as when we declared OneMonthSales based on our structure.

2014-08-01_0638

Then the second line is declare a structured array based on the Record class. Again the equivalent of

2014-08-01_0639

So as you see all he has really done is declared a structure (the record class), declared an instance of that structure (rec), and declared a structured array (arrrec)

Then the methods of the Manager Class just encapsulate the same code we have already been using into different methods. Let’s take a look at a few, beginning with loading data.

2014-08-01_0643_001

This particular class I have added some additional logic to control the loading of thumbnails or not, but again we can ignore all of that the only lines we care about is line 5, which clears the array. Don’t worry about the {::ArrayField} that is just using some indirection to reference arrrec. The line could just as easily read ArrayDeleteAll(arrrec)

Then on Line 36 we see our favorite statement that makes all of this fast. FileToArray. In this instance he is issuing an SQL statement to load the data into a data source variable, but it could just as easily be a file from the analysis or even a query. And once again he is using indirection to reference the array.

So he has populated the array, just like we did in our code.

What about adding an element to the array? Let’s take a look at the SaveRecord method of the Manager class

2014-08-01_0650

Its getting a little harder to pick out our array code but trust me it is all still there. First outside the class (in your code) an instead of the record class was created and populated, just like we did with OneMonthSales

2014-08-01_0652

That instance of the Record class is passed to the SaveRecord method as argRec. For an insert the argActionType will be eAdd. The next two lines are a bit of fancy class code that has to do with dynamic allocation of classes, but in simple terms, he is just making sure he has a clean “copy” of the Record Class.

Next at line 8 a call is made to the RecSave method of the Record class. This is where things get fancy as we certainly would have a procedure in our structure! But if we follow through the Record Class logic we will find that what the call eventually moves the class fields into the database fields and then does an HAdd.

Then Line 9 add the new element to our array. this is the equivalent of our example code of

2014-08-01_0701

If you follow along with the code code you will see that the update and delete logic are similar.

Again this was a gross over simplification of the File Manager classes, but I believe it you work with and understand arrays, then understand how the File Manager classes are just a “fancy” way to create an manage an array for your database table, it will make it much easier to use the File Manager classes and start expanding on them for your purposes.

Be sure to go over to wxLive.us and watch the Uncle Pete’s corner webinar that is the companion to this article. 

Uncle Pete’s Corner is webinar series on all things WX, to watch the recorded version of this webinar, many other WX related webinars or to watch future ones live go to wxLive.us

[suffusion-the-author display=’author’]

Pete Halsted[suffusion-the-author display=’description’]

2 thoughts on “Structured Arrays Primer – Part 2

  1. Hi Pete !

    Seems that “parameterless” queries when executed at project start up using HExecuteQuery and given that your Tables are binded to the same query, you’ll notice that they are faster to load than those binded to arrays. I tried them both on a fairly huge amount of data via a WAN and it was obvious.

    Some questions i would like to ask :

    In order to get a subset of an array, do you browse all the array using a loop and filter inside using an IF clause ? Is there a possible Query To Object coding techs.

    And for queries, is it possible to point out a record inside the returned result (if this is possible than one can use queries results as cache, not to mention that synchronizing with db would be as easier as a tableDisplay() with taReExecuteQuery

    Thanks for devoting some time to read and response and keep posting nice articles dude.

    Like

    1. I would have to see sample code to comment, but not my experience at all. It of course depends on when the query and or Array is loaded and how.

      There is a certain amount of time involved in the rendering of a looper or table, and that depends quite a bit on the elements and the type of looper/table. Which will also effect the results.

      As for using queries like a cache, I don’t think that would really be possible or even desirable, you can do some things with them such as applying an Hfilter, but because of the black box nature not sure if a new database fetch would happen or not.

      As for using the taReExecuteQuery, that is going to do a full database read again. Once you use the File Manager classes (which this article was just a foundation discussion of structured arrays which is the basis of the File Manager classes), then you are only updating the record you effected in the array, not pulling a complete new data set.

      And depending on your application and needs you can have a background process refreshing specific records from the database that have been changed by others.

      That is the point of moving the structured array concept into classes because then you can incorporate all that additional type logic to really get it to perform as needed, without duplicating the code throughout your application.

      As for subsets of the array, there are things you can do with the For Each loop to filter an array. I suspect the net result is no different than you doing a loop with IF statements yourself. What I have done for some advanced needs of an application is created a file manager class that maintain multiple copies of the array with the different subsets I needed, that way the method calls automatically moved them into the proper array, etc.

      The real power is once you turn it into class code you can extended it however you need, and I have tweak for some very complex relational logic, in the class and the rest of your app (and other developers) just need to know Product.AddRecord() or whatever instead of all the extra logic that needs to take place.

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s