| 1 | =head1 NAME | ||
| 2 | |||
| 3 | StoredHash::ARS - Remedy/ARS adapter for StoredHash | ||
| 4 | |||
| 5 | =head1 DESCRIPTION | ||
| 6 | |||
| 7 | StoredHash::ARS allows the normal persistence methods of StoredHash to be used with | ||
| 8 | Remedy ARS database backend (with few exceptions where certain method would not make sense). | ||
| 9 | |||
| 10 | ARS is much more constrained about DB side conventions than traditional relational databases. | ||
| 11 | ID numbering is always automatic and primary key is always field called (actually numbered as | ||
| 12 | ARS uses numbers for attribute names) '1'. | ||
| 13 | |||
| 14 | To learn to use StoredHash::ARS, read "perldoc StoredHash". The documentation here | ||
| 15 | focuses only on ARS / Remedy specific quirks. | ||
| 16 | |||
| 17 | NOTE: This module is still experimental. | ||
| 18 | |||
| 19 | =head2 SYNOPSIS | ||
| 20 | |||
| 21 | use StoredHash::ARS; | ||
| 22 | use ARS; | ||
| 23 | |||
| 24 | # ARS Connection | ||
| 25 | my $ctrl = ars_Login($ENV{'ARS_SERVER'}, $ENV{'ARS_USER'}, $ENV{'ARS_PASS'}); | ||
| 26 | # StoredHash persister configuration ('pkey' intentionally missing for ARS) | ||
| 27 | my $shpc = {'table' => 'HPD::Helpdesk', 'dbh' => $ctrl, 'debug' => 0}; | ||
| 28 | my $shp = StoredHash::ARS->new(%$shpc); | ||
| 29 | # Load | ||
| 30 | my hdent = $shp->load(['HD...']); | ||
| 31 | # Insert, Update, Delete ... as usual | ||
| 32 | |||
| 33 | |||
| 34 | =head1 METHODS | ||
| 35 | |||
| 36 | Method documentation focuses only on ASR specific differences. | ||
| 37 | Be sure to read StoredHash documentation for an overview. | ||
| 38 | |||
| 39 | General notes: | ||
| 40 | |||
| 41 | =over 4 | ||
| 42 | |||
| 43 | =item * The hash to store must be keyed by Remedy field numbers (not descriptibve names ...) | ||
| 44 | |||
| 45 | =item * On update, delete ... ops that require entry id to be passed, use the | ||
| 46 | value assigned to Remedy field '1' (reserved for id of an entry by Remedy conventions). | ||
| 47 | |||
| 48 | =item * StoredHash basic / DBI backend will tolerate calling methods via persister | ||
| 49 | that does not have the connection ('dbh') set. StoredHash::ARS will not. | ||
| 50 | |||
| 51 | =item * Because of no composite keys in ARS, the StoredHash::ARS persistnce | ||
| 52 | methods will tolarate a loose / forging way of passing id:s as plain scalars, | ||
| 53 | with "wrapping" array being optional. However using array(ref) is still the | ||
| 54 | recommended, future-proof way of passing the id. | ||
| 55 | |||
| 56 | =item * Remedy schemas usually host a huge amount of attributes. For this reason you should focus | ||
| 57 | on using parameter 'attrs' in your persistence operations for optimal performance. | ||
| 58 | |||
| 59 | =back | ||
| 60 | |||
| 61 | =cut | ||
| 62 | |||
| 63 | package StoredHash::ARS; | ||
| 64 | |||
| 65 | use ARS; | ||
| 66 | |||
| 67 | use strict; | ||
| 68 | use warnings; | ||
| 69 | |||
| 70 | use Data::Dumper; | ||
| 71 | # Inherit all | ||
| 72 | our @ISA = ('StoredHash'); | ||
| 73 | our $VERSION = '0.30'; | ||
| 74 | our $ars_errstr; # Auto exported from ARS | ||
| 75 | |||
| 76 | =head2 StoredHash::ARS->new(%opts) | ||
| 77 | |||
| 78 | StoredHash::ARS constructor. Notes on keyword parameters (in %opts). | ||
| 79 | |||
| 80 | =over 4 | ||
| 81 | |||
| 82 | =item * 'dbh' - The connection passed should be ARS Connection (use ARS API idiomatic handle $ctrl as value). | ||
| 83 | |||
| 84 | =item * 'table' - use ARS Schema name (e.g. 'table' => 'HPD:HelpDesk') | ||
| 85 | |||
| 86 | =back | ||
| 87 | |||
| 88 | Because Remedy always uses the same numeric name for primary key and auto-allocates id, the parameters | ||
| 89 | 'pkeys' and 'autoid' are not relevant for Remedy. | ||
| 90 | |||
| 91 | =cut | ||
| 92 | sub new { | ||
| 93 | my ($class, %opt) = @_; | ||
| 94 | # TODO: Lazy load ARS for easier testability. | ||
| 95 | # eval("use ARS;"); | ||
| 96 | my %overr = ('pkey' => ['1'], 'autoid' => 1,); | ||
| 97 | my $shp = StoredHash->new(%opt, %overr); | ||
| 98 | return bless($shp, $class); | ||
| 99 | } | ||
| 100 | |||
| 101 | #sub errstr { | ||
| 102 | # my ($p, $v) = @_; | ||
| 103 | # $ars_errstr | ||
| 104 | #} | ||
| 105 | #sub qexecute { | ||
| 106 | # my ($p, $q, $vals, $rett) = @_; | ||
| 107 | |||
| 108 | =head2 $shp->insert($e); | ||
| 109 | |||
| 110 | Insert an entry into ARS database. | ||
| 111 | Pass ARS Entry instance ($e) with NUMERIC Field IDs (i.e. not descriptive field names). | ||
| 112 | |||
| 113 | $e = {'240000005' => 'mrjohnsmith', '260000002' => 'mrjohnsmith@corp.com'}; | ||
| 114 | my $eid = $sp->insert($e); | ||
| 115 | |||
| 116 | Return Remedy entry ID for the new entry (usable in further persistence ops). | ||
| 117 | |||
| 118 | =cut | ||
| 119 | sub insert { | ||
| 120 | my ($p, $e) = @_; | ||
| 121 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 122 | if (ref($e) ne 'HASH') {die("Not a HASH for ARS Insert");} | ||
| 123 | my $eid = ars_CreateEntry($ctrl, $sc, %$e); | ||
| 124 | if (!$eid) {die("Failed to create entry ($ars_errstr)\n");} | ||
| 125 | return($eid); # OLD: [$eid] | ||
| 126 | } | ||
| 127 | =head2 $shp->update($e, [$eid], %opts) | ||
| 128 | |||
| 129 | Update entry in ARS database schema. Keyword params in %opts: | ||
| 130 | |||
| 131 | =over 4 | ||
| 132 | |||
| 133 | =item * 'attrs' - Allow only a subset of attributes from entry ($e) to be updated. | ||
| 134 | |||
| 135 | =back | ||
| 136 | |||
| 137 | =cut | ||
| 138 | |||
| 139 | #=item * Allow ID to be extracted (param count problem) ? | ||
| 140 | sub update { | ||
| 141 | my ($p, $e, $eid, %c) = @_; | ||
| 142 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 143 | # Allow ARRAY or plain scalar, coerce to scalar here | ||
| 144 | my $isidarr = (ref($eid) eq 'ARRAY') ? 1 : 0; | ||
| 145 | if ($isidarr && (scalar(@$eid) == 1)) {$eid = $eid->[0];} | ||
| 146 | elsif ($isidarr) {die("Passing no values or multiple values for Remedy ID");} | ||
| 147 | if (!$eid) {die("No ID (value of field '1') Specified for ARS update");} | ||
| 148 | # Allow Blessed ? Use reftype() | ||
| 149 | if (ref($e) ne 'HASH') {die("Not a HASH for ARS Update");} | ||
| 150 | # Serialize entry values to ARRAY (additionally Allow mapping ?) | ||
| 151 | my @kvs; | ||
| 152 | if (my $ats = $c{'ats'}) {@kvs = map({($_, $e->{$_});} @$ats);} | ||
| 153 | else {@kvs = map({($_, $e->{$_});} keys(%$e));} # %$e | ||
| 154 | # getTime=0, Return undef on fail, 1 on success | ||
| 155 | my $ok = ars_SetEntry($ctrl, $sc, $eid, 0, @kvs); | ||
| 156 | return($ok); | ||
| 157 | } | ||
| 158 | =head2 $shp->delete([$eid]) | ||
| 159 | |||
| 160 | Delete an Entry by id ($eid) permanently from ARS database. | ||
| 161 | |||
| 162 | Return true value for successesful deletion. | ||
| 163 | |||
| 164 | =cut | ||
| 165 | sub delete { | ||
| 166 | my ($p, $eid) = @_; | ||
| 167 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 168 | my $isidarr = (ref($eid) eq 'ARRAY') ? 1 : 0; | ||
| 169 | if ($isidarr && (scalar(@$eid) == 1)) {$eid = $eid->[0];} | ||
| 170 | elsif ($isidarr) {die("Passing no values or multiple values for Remedy ID");} | ||
| 171 | # || ref($eid) | ||
| 172 | if (!$eid ) {die("Entry ID must be passed as array of single item or non-empty scalar");} | ||
| 173 | my $ok = ars_DeleteEntry($ctrl, $sc, $eid); | ||
| 174 | return($ok); | ||
| 175 | } | ||
| 176 | =head2 $shp->exists([$eid]) | ||
| 177 | |||
| 178 | Test presence of entry by ID ($eid) in ARS database. | ||
| 179 | Return true for present in DB, false for not present. | ||
| 180 | |||
| 181 | =cut | ||
| 182 | sub exists { | ||
| 183 | my ($p, $eid) = @_; | ||
| 184 | # OLD: | ||
| 185 | #my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 186 | #if ((ref($eid) eq 'ARRAY') && (scalar(@$eid) == 1)) {$eid = $eid->[0];} | ||
| 187 | #my %vals = ars_GetEntry($ctrl, $sc, $eid); #, @ats | ||
| 188 | #if (%vals) {return(1);} | ||
| 189 | #return(0); | ||
| 190 | # NEW: Load entry with only ID - this should be extremely light search. | ||
| 191 | my $e = $p->load($eid, 'attrs' => [1]); | ||
| 192 | if ($p->{'debug'}) {print(STDERR Dumper($e));} | ||
| 193 | if (!$e) {return(0);} | ||
| 194 | if (ref($e) ne 'HASH') {return(0);} | ||
| 195 | if (!%$e) {return(0);} | ||
| 196 | return(1); | ||
| 197 | } | ||
| 198 | =head2 $shp->load([$eid], %opts) | ||
| 199 | |||
| 200 | Load an entry from Remedy database schema by its id ($eid). Keyword params in %opts: | ||
| 201 | |||
| 202 | =over 4 | ||
| 203 | |||
| 204 | =item * 'attrs' - Load only attributes listed (arrayref, pass numeric field IDs) | ||
| 205 | |||
| 206 | =back | ||
| 207 | |||
| 208 | =cut | ||
| 209 | sub load { | ||
| 210 | my ($p, $eid, %o) = @_; | ||
| 211 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 212 | if (!ref($ctrl)) {die("No Connection\n");} | ||
| 213 | if ((ref($eid) eq 'ARRAY') && (scalar(@$eid) == 1)) {$eid = $eid->[0];} | ||
| 214 | if (ref($eid)) {die("EID is Still ref after dereferencing");} | ||
| 215 | if (!$eid) {die("No entry id");} | ||
| 216 | my @ats = ref($o{'attrs'}) eq 'ARRAY' ? @{$o{'attrs'}} : (); # TODO: %o | ||
| 217 | if ($p->{'debug'}) {print(STDERR "Querying by attrs: ".Dumper(\@ats));} | ||
| 218 | my %vals = ars_GetEntry($ctrl, $sc, $eid, @ats);# $diaryfield_fid | ||
| 219 | return(\%vals); | ||
| 220 | } | ||
| 221 | |||
| 222 | =head2 $shp->loadset($filter, $sortby, %opts) | ||
| 223 | |||
| 224 | Keyword parameters (in %opts): | ||
| 225 | |||
| 226 | =over 4 | ||
| 227 | |||
| 228 | =item * 'attrs' - if set to true scalar value, the entries are fetched | ||
| 229 | brute force with all entry attributes (in Remedy this usually is a LOT of data). | ||
| 230 | |||
| 231 | =item * | ||
| 232 | |||
| 233 | =back | ||
| 234 | |||
| 235 | =cut | ||
| 236 | sub loadset { | ||
| 237 | my ($p, $h, $sort, %c) = @_; # filter, sortby | ||
| 238 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 239 | if (!ref($ctrl)) {die("No Connection\n");} | ||
| 240 | my $qs = wherefilter($h); | ||
| 241 | if ($p->{'debug'}) {print(STDERR "Filter: $qs\n");} | ||
| 242 | my $qual = ars_LoadQualifier($ctrl, $sc, $qs); | ||
| 243 | if ($ars_errstr || !$qual) {die("No Qualifier (for schema '$sc'): $ars_errstr");} | ||
| 244 | my $df = $c{'attrs'}; # || []; # $c{'attr'} || ...; | ||
| 245 | my (@es, @es2); # Entry sets - @es2 is the final to return | ||
| 246 | # TODO: Break into 3 cases for 'attrs': | ||
| 247 | # 1) undefined, param not present - return default fields (this is the "safe", low bandwidth solution) | ||
| 248 | # 2) explicit, valid list of more than 1 attributes, return exactly those | ||
| 249 | # 3) empty array - return all fields (recommennded to be used only at development / testing / debugging) | ||
| 250 | # Consider 1/3 behaviour | ||
| 251 | # No $df - signal search returning small (server side configured) default fields | ||
| 252 | if (!ref($df)) {$df = [];goto EXPLICIT;} # What does empty do ? | ||
| 253 | # Has $df, but empty array | ||
| 254 | # http://cpansearch.perl.org/src/JMURPHY/ARSperl-1.90/html/manual/ars_GetListEntry.html | ||
| 255 | elsif (!@$df) { | ||
| 256 | # Implied / schema default fields (i.e not all) | ||
| 257 | if ($p->{'debug'}) {print(STDERR "fields passed as empty set, return set with ALL fields\n");} | ||
| 258 | my %entries = ars_GetListEntry($ctrl, $sc, $qual, 0, 0); | ||
| 259 | # if ($p->{'debug'} > 3) {print(STDERR Dumper(\%entries));} | ||
| 260 | @es2 = map({my %e = ars_GetEntry($ctrl, $sc, $_);(\%e);} keys(%entries)); | ||
| 261 | # TODO: Try ars_GetListEntryWithFields($ctrl, $sc, $qual, 0, 0); | ||
| 262 | } | ||
| 263 | # Explicit fields (return those) or empty array (return ALL fields !) | ||
| 264 | # http://cpansearch.perl.org/src/JMURPHY/ARSperl-1.90/html/manual/ars_GetListEntryWithFields.html | ||
| 265 | else { | ||
| 266 | EXPLICIT: | ||
| 267 | #my $cnt = | ||
| 268 | my $desc = @$df ? "Explicit fields: @$df" : 'set with default fields'; | ||
| 269 | if ($p->{'debug'}) {print(STDERR "Fields passed internally (@$df), return $desc\n");} | ||
| 270 | @es = ars_GetListEntryWithFields($ctrl, $sc, $qual, 0, 0, $df); | ||
| 271 | # Map to Include keys (Already in entry) | ||
| 272 | my $i = 0; | ||
| 273 | my $re; | ||
| 274 | #@es = map({($i % 2) ? () : ($_);} @es); | ||
| 275 | #@es = values(@es); # Coerce early | ||
| 276 | for ($i = 1;$re = $es[$i];$i += 2) {push(@es2, $re);} | ||
| 277 | } | ||
| 278 | return(\@es2); | ||
| 279 | } | ||
| 280 | |||
| 281 | =head2 $shp->cols() | ||
| 282 | |||
| 283 | Query columns for ARS Schema (given by persister internal field 'table' | ||
| 284 | passed at construction time). Return numeric fieldnames sorted alphabetically | ||
| 285 | (as ARS does not have deterministic order for fields). | ||
| 286 | |||
| 287 | =cut | ||
| 288 | sub cols { | ||
| 289 | my ($p) = @_; | ||
| 290 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 291 | my %fld = ars_GetFieldTable($ctrl, $sc); | ||
| 292 | # TODO ADVANCED: | ||
| 293 | # - Col ID Numbers to names mapping (hash) | ||
| 294 | # - Full ARS column_info / Meta info | ||
| 295 | #if (0) {} | ||
| 296 | return([sort keys(%fld)]); | ||
| 297 | } | ||
| 298 | #sub loadtree { | ||
| 299 | # my ($p, %c) = @_; | ||
| 300 | # | ||
| 301 | #} | ||
| 302 | |||
| 303 | #sub loadchildren { | ||
| 304 | # my ($p, $e, %c) = @_; | ||
| 305 | # # Load type by created filter | ||
| 306 | # my ($ct, $park, $memn) = @$cinfo{'table','parkey','memname',}; | ||
| 307 | #} | ||
| 308 | |||
| 309 | #sub makecinfo { | ||
| 310 | # my ($p, $cv) = @_; | ||
| 311 | #sub validate { | ||
| 312 | # my ($p) = @_; | ||
| 313 | #} | ||
| 314 | |||
| 315 | #sub pkeys { | ||
| 316 | # my ($p) = @_; | ||
| 317 | # | ||
| 318 | #} | ||
| 319 | #sub pkeyvals { | ||
| 320 | # my ($p, $e) = @_; | ||
| 321 | |||
| 322 | # Should not be used with ARS | ||
| 323 | sub fetchautoid { | ||
| 324 | my ($p) = @_; | ||
| 325 | die("fetchautoid - Not Relevant with ARS\n"); | ||
| 326 | } | ||
| 327 | # Should always produce 1 for Remedy | ||
| 328 | #sub pkeycnt { | ||
| 329 | # my ($p) = @_; | ||
| 330 | # | ||
| 331 | #sub invalidids { | ||
| 332 | # my ($p, @idv) = @_; | ||
| 333 | #} | ||
| 334 | # Irrelevant with Remedy API | ||
| 335 | #sub whereid { | ||
| 336 | # my ($p) = @_; | ||
| 337 | #} | ||
| 338 | |||
| 339 | #sub sqlvalesc { | ||
| 340 | # my ($v) = @_; | ||
| 341 | # $v =~ s/'/\'/g; | ||
| 342 | # return($v); | ||
| 343 | #} | ||
| 344 | |||
| 345 | # WHERE IN Clause | ||
| 346 | sub invalues { | ||
| 347 | my ($vals) = @_; | ||
| 348 | my @uvals = map({ | ||
| 349 | #if (/^\d+$/) {"";} | ||
| 350 | "\"$_\""; | ||
| 351 | } @$vals); | ||
| 352 | join(',', @uvals); | ||
| 353 | } | ||
| 354 | sub rangefilter { | ||
| 355 | my ($attr, $v) = @_; | ||
| 356 | } | ||
| 357 | # Internal method to create ARS/Remedy where filter. | ||
| 358 | sub wherefilter { | ||
| 359 | my ($e, %c) = @_; | ||
| 360 | my $w = ''; | ||
| 361 | my $fop = ' AND '; | ||
| 362 | #my $rnga = $c{'rnga'}; # Range attributes | ||
| 363 | if (ref($e) ne 'HASH') {die("No hash for filter generation");} | ||
| 364 | my @keys = sort keys(%$e); | ||
| 365 | my @qc; | ||
| 366 | # Assume hard values, treat everything as string (?) | ||
| 367 | # TODO: forcestr ? | ||
| 368 | no strict ('refs'); | ||
| 369 | @qc = map({ | ||
| 370 | my $v = $e->{$_}; | ||
| 371 | #my $rv = ref($v); | ||
| 372 | #if ($rnga->{$_} && ($rv eq 'ARRAY') && (@$v == 2)) {rangefilter($_, $v);} | ||
| 373 | if (ref($v) eq 'ARRAY') {" '$_' IN (".invalues($v).") ";} | ||
| 374 | # SQL Wildcard | ||
| 375 | elsif ($v =~ /%/) {"'$_' LIKE \"$v\"";} | ||
| 376 | # Detect numeric (likely numeric, not perfect) | ||
| 377 | elsif ($v =~ /^\d+$/) {"'$_' = $v";} | ||
| 378 | # Assume string | ||
| 379 | else {"'$_' = \"".StoredHash::sqlvalesc(undef, $v)."\"";} | ||
| 380 | |||
| 381 | } @keys); | ||
| 382 | return(join($fop, @qc)); | ||
| 383 | } | ||
| 384 | sub allentvals { | ||
| 385 | my ($h) = @_; | ||
| 386 | die("allentvals: Not implemented"); | ||
| 387 | } | ||
| 388 | #=head2 $shp->count($filter) | ||
| 389 | # | ||
| 390 | # Exists ONLY for interface completeness, NOT Implemented (yet). | ||
| 391 | # Return -1 (till completed) | ||
| 392 | # | ||
| 393 | #=cut | ||
| 394 | sub count { | ||
| 395 | my ($p, $h) = @_; | ||
| 396 | # How do we counts ents easily / most efficiently ? | ||
| 397 | # my $arr = $shp->loadset(undef,undef,'attrs' => [1]);return(scalar(@$arr)); | ||
| 398 | my ($ctrl, $sc) = @$p{'dbh', 'table'}; | ||
| 399 | my $qs = ($h && %$h) ? wherefilter($h) : "1 = 1"; | ||
| 400 | my $qual = ars_LoadQualifier($ctrl, $sc, $qs); | ||
| 401 | #my %entries = ars_GetListEntry($ctrl, $sc, $qual, 0, 0); | ||
| 402 | # Get entries with ONLY ID field to minimize "DB traffic". | ||
| 403 | my %entries = ars_GetListEntryWithFields($ctrl, $sc, $qual, 0, 0, [1]); | ||
| 404 | if ($p->{'debug'}) {print(Dumper(\%entries));} | ||
| 405 | my $cnt = keys(%entries); | ||
| 406 | return(-1); | ||
| 407 | } | ||
| 408 | 1; |