comment on

hiyall:

Sorry in advance--it's long, pedantic and preachy. But I'm tired of working on this, so rather than discarding it, I'll just post what I have, and move on...

Yes, chopping down the program to the minimum is always best. First, it helps you clarify the problem in your head better. Often, it clarifies things so much that you figure out the answer and need not post a question! Secondly, it simplifies things for possible respondents--the easier you make it for someone to help you, the more likely someone is likely to help out. Each unnecessary bit of code you have raises the hurdle for someone willing to help.

I typically pass over questions with too much unrelated code, because I don't want to have to do too much analysis to understand the question. Today, I'm home sick and bored, so here I am.. ;^)

Since you already have your data in an array that's easy to work with, you could've posted something more like:

#!/usr/bin/perl -w
use warnings;
use strict;

my @data = (
    # Host         User      DB      Permissions
    [ "localhost", "jma_dbo", "*",   "USAGE, SELECT, INSERT, SHOW VIEW
+" ],

    [ "localhost", "jma_ro", "*",    "USAGE, SELECT" ],
    [ "localhost", "jma_ro", "jma",  "SHOW VIEW" ],
    [ "localhost", "jma_ro", "uat",  "SELECT, INSERT" ],

    [ "localhost", "jma_rw", "jma",  "USAGE, SELECT, INSERT, SHOW VIEW
+" ],

    [ "prod",      "robo",   "*",    "SELECT, SHOW VIEW" ],
    [ "prod",      "robo",   "jma",  "USAGE" ],
);


. . . your attempt to solve the problem . . .
[download]

Since you want to keep it short, you don't need to show *all* the data you have. Just enough to illustrate the problem. Frequently, people leave out data showing any of the special cases you may have. So be sure to review the data and make sure you have at least *one* example of all the special cases you can think of. (I also like to chop out some data (and/or code) to keep the lines from wrapping.)

Then be sure to specify clearly what you're trying to get from the data. Drawing a sample of the output is very helpful, as it can clarify many of your special cases. Be sure to describe your special cases, as well.

Another important point: You're probably very familiar with your data. But don't assume everyone else is as familiar with it as you are. Be sure to describe your problem as if we don't really know what the data is, or what it's for. In this case, you don't really have a database question, you're asking (I think) how to consolidate your data into a report. You don't need to describe what the data *is* so much, but rather what items are what, so when you describe the problem, we can tell what you're saying.

Having said that, I'll state the question that I'm imagining:

I'm trying to create a report of all the permissions in my databases. I've got all the data in an array, but need to figure out how to create a report with it. I want one line of output for each host/user/database combination, and each permission should be in a separate column, all lined up.

In my data, the first three columns are the host, user and database, respectively. The final column is a string (comma separated) representing all the permissions the user has.

The tricky bit is this: Database '*' isn't really a database, it's the default permissions the user has for all databases on the server. I don't want to show a line for the '*' database. Instead, I'd like to make each column show whether the permission is specified for the specific database ('Y'), whether it's a default permission ('**') or whether it's a default permission *and* specified for the database ('Y*').

So how to I merge all this data together into something like this:

host user DB usage select insert show view ... --------- ------- --- ----- ------ ------ --------- ... localhost jma_dbo jma ** ** ** ** localhost jma_ro jma ** ** Y localhost jma_ro uat ** Y* Y localhost jma_rw jma Y Y Y Y prod robo jma Y ** ** . . .
[download]

So I created part of the desired output, and made sure to include each special case I could think of. Then I edited the data to ensure that the data would support the rows shown. Don't leave much other data in there, or it will make comparison/debugging complex. (I left in a *little* other data in there, but not much.) If your data doesn't generate the part of the report you show, then people will often guess which "hidden rules" you forgot to mention would generate the row you wanted. Don't make us guess.

Finally, here's my answer to the question I imagine you're asking:

Normally, when I need to create a report, and there are interactions between various parts of the data, I'll either transform the data structure I have into a form that makes the report easy to write, or I'll create a new data structure chunk by chunk to represent the report, and then print it out. I've chosen the latter for this case.

As I see it, the difficulties are: (1) make sure you can put a flag in each different permission column, (2) marking whether it's a default, specified or both, and (3) ensuring that all databases the user can access are listed.

I'm suggesting a new data structure as a multilevel hash (keys being HOST, USER and DATABASE), where each entry is an array of the permissions that are set. So I'd first create the hash. Then I'd go over the data and apply any special cases. Finally, when the data is in a suitable shape, I'd print the report, like so:

#!/usr/bin/perl -w
use warnings;
use strict;

my @data = ( 
    ... same as above ...
);

# First scan the data to find out what rows are needed, and the permis
+sions
# columns needed

my %report;   # {HOST}{USER}{DB}
my %DBs;      # {HOST}{DB}        # (see tricky bit below)
my %perms;
my $next_column=0;
for my $row (@data) {
    my ($host, $uid, $db, $tmp) = @$row;

    # Convert permissions string to array of permissions
    $tmp = [ split /,\s*/, $tmp ];

    # Ensure we have a column for each permission
    for my $p (@$tmp) {
        next if exists $perms{$p};
        $perms{$p} = $next_column++;
    }

    $report{$host}{$uid}{$db} = $tmp;

    # Track each database we see
    $DBs{$host}{$db}=0;
}
my $max_perm = scalar(keys %perms) - 1;

# Tricky bit: We want to include rows for databases the user has acces
+s to
# (the USAGE permission) even when they have no database-specific entr
+ies.
# So we'll find all host/user combinations with a '*' database having 
+a
# 'USAGE' permission, and make sure that we have a hash entry with a b
+lank
# list of permissions.  To simplify this, we built the DBs hash in the
+ previous
# step to hold a list of all databases we have on each server
for my $host (keys %report) {
    my @dbs_to_add;
    for my $user (keys %{$report{$host}}) {
        next unless exists $report{$host}{$user}{'*'};
        next unless grep { $_ eq 'USAGE' } values $report{$host}{$user
+}{'*'};

        # OK, we have HOST/USER entry where the user has access to all
+ databases
        # on the server, so add a blank permissions list for all serve
+rs not
        # already on the list
        for my $DB (keys %{$DBs{$host}}) {
            next if exists $report{$host}{$user}{$DB};
            $report{$host}{$user}{$DB} = [ ];
        }
    }
}

# Now we can transform the permissions lists for each row into the sym
+bols we
# want to show in the report
for my $host (keys %report) {
    for my $user (keys %{$report{$host}}) {

        # First set symbol to '' or 'Y'
        for my $DB (keys %{$report{$host}{$user}}) {
            next if $DB eq '*';

            my @symbol = ('') x $max_perm; # default values
            $symbol[$perms{$_}] = 'Y' for @{$report{$host}{$user}{$DB}
+};

            $report{$host}{$user}{$DB} = [ @symbol ];
        }

        # Now add '*' for all default permissions
        next unless exists $report{$host}{$user}{'*'};
        my @defaults = @{$report{$host}{$user}{'*'}};
        for my $DB (keys %{$report{$host}{$user}}) {
            next if $DB eq '*';
            $report{$host}{$user}{$DB}[$perms{$_}] .= '**' for @defaul
+ts;
        }
    }
}

# Print the report

# Header
print "server     user ID  DB   ";
printf "% 2u ", $_+1 for 0 .. $max_perm;
print "\n";

print "---------- -------- ---  ";
print "-- " for 0 .. $max_perm;
print "\n";

# Data
for my $host (sort keys %report) {
    for my $user (sort keys %{$report{$host}}) {
        for my $DB (sort keys %{$report{$host}{$user}}) {
            next if $DB eq '*';

            printf "%-10.10s %-8.8s %-3.3s: ", $host, $user, $DB;

            printf "%-2.2s ", $_ for @{$report{$host}{$user}{$DB}};
            print "\n";
        }
    }
}

print "\nColumns:\n";
for my $perm (sort { $perms{$a} <=> $perms{$b} } keys %perms) {
    printf "% 2u: %s\n", $perms{$perm} + 1, $perm;
}
[download]

Finally, I didn't generate *exactly* the report asked for. I demonstrated the concepts I thought I should, and am leaving the adjustments to you, the reader.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

In reply to Re: Hurdle with summarizing results from complex data structure related to MySQL result handling by roboticus
in thread Hurdle with summarizing results from complex data structure related to MySQL result handling by hiyall

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.