install-eg.sh: do not enable nginx config during install, this causes problems
[sitka/sitka-tools.git] / maintenance / report-sql.pl
CommitLineData
08186954
JD
1#!/usr/bin/perl
2# vim:ts=4:noet:
3
4# report-sql.pl - generate SQL for an existing report
5#
6# This script was created by taking the clark-kent.pl utility from
7# Evergreen (http://evergreen-ils.org) and removing all the parts not
8# directly required for generating report SQL.
9#
10# Copyright (C) 2008 Equinox Software, Inc.
11# Copyright (C) 2016 BC Libraries Cooperative
12# Author: Jeff Davis <jeff.davis@bc.libraries.coop>
13#
14# This program is free software; you can redistribute it and/or
15# modify it under the terms of the GNU General Public License
16# as published by the Free Software Foundation; either version 2
17# of the License, or (at your option) any later version.
18#
19# This program is distributed in the hope that it will be useful,
20# but WITHOUT ANY WARRANTY; without even the implied warranty of
21# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
22# GNU General Public License for more details.
23
24use strict;
25use DBI;
26use Getopt::Long;
27use Data::Dumper;
28use OpenSRF::EX qw/:try/;
29use OpenSRF::Utils qw/:daemon/;
30use OpenSRF::Utils::JSON;
31use OpenSRF::Utils::Logger qw/$logger/;
32use OpenSRF::System;
33use OpenSRF::AppSession;
34use OpenSRF::Utils::SettingsClient;
35use OpenILS::Reporter::SQLBuilder;
36
37use open ':utf8';
38
39
40my $config = '/srv/openils/conf/opensrf_core.xml';
41my $opt_report;
42my $help;
43
44GetOptions(
45 "bootstrap|boostrap=s" => \$config,
46 "report=i" => \$opt_report,
47 "help" => \$help
48);
49
50if ($help || !$report) {
51 print <<"HELP";
52Output the SQL that will be generated for the specified report.
53NB: This script must be run on a server with Evergreen running!
54
55USAGE:
56 $0 --report <report-id> [ --bootstrap /openils/conf/opensrf_core.xml ] > query.sql
57
58OPTIONS:
59 --report
60 The id of a row in reporter.report.
61 --bootstrap
62 Path to your opensrf_core.xml config file.
63
64HELP
65 exit;
66}
67
68OpenSRF::System->bootstrap_client( config_file => $config );
69
70my (%data_db, %state_db);
71
72my $sc = OpenSRF::Utils::SettingsClient->new;
73
74$data_db{db_driver} = $sc->config_value( reporter => setup => database => 'driver' );
75$data_db{db_host} = $sc->config_value( reporter => setup => database => 'host' );
76$data_db{db_port} = $sc->config_value( reporter => setup => database => 'port' );
77$data_db{db_name} = $sc->config_value( reporter => setup => database => 'db' );
78if (!$data_db{db_name}) {
79 $data_db{db_name} = $sc->config_value( reporter => setup => database => 'name' );
80 print STDERR "WARN: <database><name> is a deprecated setting for database name. For future compatibility, you should use <database><db> instead." if $data_db{db_name};
81}
82$data_db{db_user} = $sc->config_value( reporter => setup => database => 'user' );
83$data_db{db_pw} = $sc->config_value( reporter => setup => database => 'pw' );
84
85
86
87# Fetch the optional state database connection info
88$state_db{db_driver} = $sc->config_value( reporter => setup => state_store => 'driver' ) || $data_db{db_driver};
89$state_db{db_host} = $sc->config_value( reporter => setup => state_store => 'host' ) || $data_db{db_host};
90$state_db{db_port} = $sc->config_value( reporter => setup => state_store => 'port' ) || $data_db{db_port};
91$state_db{db_name} = $sc->config_value( reporter => setup => state_store => 'db' );
92if (!$state_db{db_name}) {
93 $state_db{db_name} = $sc->config_value( reporter => setup => state_store => 'name' ) || $data_db{db_name};
94}
95$state_db{db_user} = $sc->config_value( reporter => setup => state_store => 'user' ) || $data_db{db_user};
96$state_db{db_pw} = $sc->config_value( reporter => setup => state_store => 'pw' ) || $data_db{db_pw};
97
98
99die "Unable to retrieve database connection information from the settings server"
100 unless ($state_db{db_driver} && $state_db{db_host} && $state_db{db_port} && $state_db{db_name} && $state_db{db_user} &&
101 $data_db{db_driver} && $data_db{db_host} && $data_db{db_port} && $data_db{db_name} && $data_db{db_user});
102
103my $state_dsn = "dbi:" . $state_db{db_driver} . ":dbname=" . $state_db{db_name} .';host=' . $state_db{db_host} . ';port=' . $state_db{db_port};
104my $data_dsn = "dbi:" . $data_db{db_driver} . ":dbname=" . $data_db{db_name} .';host=' . $data_db{db_host} . ';port=' . $data_db{db_port};
105
106my $resultset_limit;
107
108my $dbh = DBI->connect(
109 $state_dsn,
110 $state_db{db_user},
111 $state_db{db_pw},
112 { AutoCommit => 1,
113 pg_expand_array => 0,
114 pg_enable_utf8 => 1,
115 RaiseError => 1
116 }
117);
118
119if ($opt_report) {
120 my $s3 = $dbh->selectrow_hashref(<<" SQL", {}, $opt_report);
121 SELECT * FROM reporter.report WHERE id = ?;
122 SQL
123
124 my $s2 = $dbh->selectrow_hashref(<<" SQL", {}, $s3->{template});
125 SELECT * FROM reporter.template WHERE id = ?;
126 SQL
127
128 $s3->{template} = $s2;
129
130 my $b = OpenILS::Reporter::SQLBuilder->new;
131 my $report_data = OpenSRF::Utils::JSON->JSON2perl( $s3->{data} );
132 $b->register_params( $report_data );
133
134 $s3->{resultset} = $b->parse_report( OpenSRF::Utils::JSON->JSON2perl( $s3->{template}->{data} ) );
135
136 $s3->{resultset}->set_pivot_data($report_data->{__pivot_data}) if $report_data->{__pivot_data};
137 $s3->{resultset}->set_pivot_label($report_data->{__pivot_label}) if $report_data->{__pivot_label};
138 $s3->{resultset}->set_pivot_default($report_data->{__pivot_default}) if $report_data->{__pivot_default};
139 $s3->{resultset}->resultset_limit($resultset_limit) if $resultset_limit;
140
141 print $s3->{resultset}->toSQL;
142}
143
144$dbh->disconnect;
145