flightdm has asked for the wisdom of the Perl Monks concerning the following question:

Hi, all - I'm building a little applet to be used for a quick rate lookup; the data is not likely to change for a good few years, at which point the whole thing will be moot. Usually, I'd just pop it all into a couple of tables, do something like 
select concat('Cost range is ', min(cost), '-', max(cost)) from mail_c
+ost mc join mail_areas ma on mc.zone = ma.zone where ma.area between 
+1 and 10 and mc.weight = 12;

[download]
and be done - but out of the 10 or so proposed users, 7 will be carrying it on their laptops which will for various reasons often operate disconnected from the Web. It's also worth noting that most of these folks are not very amenable to additional software installations. Like, at all. E.g., installing MySQL, or even SQLite, would be treated with the horror befitting Amityville and such. So, the easiest thing I see would be for me to build this as a stand-alone app with no external dependencies (e.g., no MySQL installations.) In any case, the data is not huge - less than 30k for both the data sets even in text format - and looks like this: 
zip      area
001    2
002    2
003    2
005    3
012    6
[...]

[download]
for the first one, and 
weight     area    cost
1              2          3.17
2              2          3.87
3              2          4.51
4              2          5.22
[...]
199            2         96.01
200            2         96.29
1              3          4.02
2              3          4.15
[...]

[download]
The key question is - how the heck do I structure this stuff? The term "look up", to me, immediately implies a hash or two... but for some reason (perhaps this freakin' cold that's got my brain running at half-speed) I'm not seeing it. Especially since there are no unique keys in the second table - it's a series of ranges from 1 to 200 with their associated areas and costs. So while I'm OK with the first one looking something like this (although a little birdie is whispering "there's a more efficient way to do this, stupid!" in my ear, I'm not seeing that at the moment either): 

$areas = (
     '001' => 2,
     '002' => 2,
     '003' => 2,
     [...]
);

[download]
I have no idea what to do for the second one. Any help - mostly in the "how to structure this whole thing" sense - would be much appreciated.

Replies are listed 'Best First'.
Re: Effective data structures for join-type lookups
by Corion (Patriarch) on Oct 03, 2016 at 21:11 UTC

    If you can install Perl, you can also install DBD::SQLite, which contains SQLite already.

    You can also install DBD::CSV, which will do SQL over CSV files.

    If you are all averse against installing modules, just use a hash of arrayrefs, where each hash contains as keys the join criteria for one table.

    Basically, have your tables as arrays, and store in the hashe values the row indices for each row, and as keys use the join criteria.

    I still recommend going with DBD::SQLite.

[reply]

      Thanks, Corion. Perl and Python are already on - otherwise I'd be having nightmares about JS, ugh - but yeah, installing anything more would be like dentistry. On black mambas. Without anesthetic or restraints. Straight Perl is best, methinks.

      And I think you've got me shifted onto the right track: I'll have to reformat the second table to make it work as you describe... which was the key to solving the whole thing. Like so:

      # From this -
weight     area    cost
1              2          3.17
2              2          3.87
3              2          4.51
4              2          5.22

# To THIS:
$cost{2} = {
                      1     => 3.17,
                      2     => 3.87,
                      3     => 4.51,
                      4     => 5.22,
                      [...]
};

      [download]
      at which point, it solves itself. Thanks tons!
[reply]
[d/l]

        This does sound like a perfect case for an embedded app in JS. Would work on disconnected laptops, phones, or tablets. HTML5 has SQLite support native. JS isn’t so bad and it’s great to have chops in it.

        There are a few Perl tools to help build such a thing. Static DB could be dumped from source SQL or other flat data via JSON for example.

[reply]

        Since at least 7 of your users will have to "install" your little app on their laptops, you could use the fatpack tool (in App::FatPacker) to bundle your app and its dependencies into a single .pl file.

        Then there's the pp tool (in PAR::Packer). It can bundle your app and dependencies into an exe file. An advantage of using pp is that the exe will include its own copy of Perl. The down side to doing this is the start time is slow (because everything gets unpacked to a temporary folder each time it is run).

        pp has an option to create a Perl script. I have not tried it, so I don't know if this might be a better option.

        There is also a "par" tool in PerlPowerTools, but I'm not sure how helpful that will be. Nor was I able to install PerlPowerTools (there was a failure in one of the dependencies that I don't have time to debug), so I can't try it.

[reply]
        Sounds like you have workable solution but I'd like to point out that depending upon the Perl distribution that was used, SQLite may already be there! All of the code to use SQLite installs with DBD:SQLite.

        I use Active Perl and the DBI and SQLite ship with the distribution, so there is nothing to "install". Perhaps something to consider/investigate for future projects? Most distributions ship with a lot of modules "pre-installed". In my case there are 281 of them! SQLite is pretty ubiquitous. I've heard that every smart phone has an SQLite DB. Certainly many products such as Mozilla Firefox use it. Anyway, you might be pleasantly surprised to find SQLite already there.

[reply]

Back to Seekers of Perl Wisdom