13 use SL::InstanceConfiguration;
25 my ($dbh, $query, %params) = @_;
28 return if $query->execute(@{ $params{bind} || [] });
29 BAIL_OUT($dbh->errstr);
32 return if $dbh->do($query, undef, @{ $params{bind} || [] });
34 BAIL_OUT($params{message} . ": " . $dbh->errstr) if $params{message};
35 BAIL_OUT("Query failed: " . $dbh->errstr . " ; query: $query");
38 sub verify_configuration {
39 SL::LxOfficeConf->read;
41 my %config = %{ $::lx_office_conf{'testing/database'} || {} };
42 my @unset = sort grep { !$config{$_} } qw(host port db user template);
44 BAIL_OUT("Missing entries in configuration in section [testing/database]: " . join(' ', @unset)) if @unset;
50 $SIG{__DIE__} = sub { Carp::confess( @_ ) } if $::lx_office_conf{debug}->{backtrace_on_die};
51 $::lxdebug = LXDebug->new(target => LXDebug::STDERR_TARGET);
52 $::lxdebug->disable_sub_tracing;
53 $::locale = Locale->new($::lx_office_conf{system}->{language});
55 $::auth = SL::Auth->new(unit_tests_database => 1);
56 $::locale = Locale->new('de');
57 $::instance_conf = SL::InstanceConfiguration->new;
58 $db_cfg = $::lx_office_conf{'testing/database'};
61 sub drop_and_create_database {
63 'dbi:Pg:dbname=' . $db_cfg->{template} . ';host=' . $db_cfg->{host} . ';port=' . $db_cfg->{port},
66 SL::DBConnect->get_options,
70 my $dbh_template = SL::DBConnect->connect(@dbi_options) || BAIL_OUT("No database connection to the template database: " . $DBI::errstr);
71 my $auth_dbh = $::auth->dbconnect(1);
74 dbg("Database exists; dropping");
75 $auth_dbh->disconnect;
77 dbh_do($dbh_template, "DROP DATABASE \"" . $db_cfg->{db} . "\"", message => "Database could not be dropped");
82 dbg("Creating database");
84 dbh_do($dbh_template, "CREATE DATABASE \"" . $db_cfg->{db} . "\" TEMPLATE \"" . $db_cfg->{template} . "\" ENCODING 'UNICODE'", message => "Database could not be created");
85 $dbh_template->disconnect;
90 ok(1, "Database has been setup sucessfully.");
95 my ($dbupdater, $control_or_file) = @_;
97 my $file = ref($control_or_file) ? ("sql/Pg-upgrade2" . ($dbupdater->{auth} ? "-auth" : "") . "/$control_or_file->{file}") : $control_or_file;
98 my $control = ref($control_or_file) ? $control_or_file : undef;
100 dbg("Applying $file");
102 my $error = $dbupdater->process_file($dbh, $file, $control);
104 BAIL_OUT("Error applying $file: $error") if $error;
107 sub create_initial_schema {
108 dbg("Creating initial schema");
111 'dbi:Pg:dbname=' . $db_cfg->{db} . ';host=' . $db_cfg->{host} . ';port=' . $db_cfg->{port},
114 SL::DBConnect->get_options(PrintError => 0, PrintWarn => 0),
117 $dbh = SL::DBConnect->connect(@dbi_options) || BAIL_OUT("Database connection failed: " . $DBI::errstr);
118 $::auth->{dbh} = $dbh;
119 my $dbupdater = SL::DBUpgrade2->new(form => $::form, return_on_error => 1, silent => 1);
120 my $defaults = SL::DefaultManager->new($::lx_office_conf{system}->{default_manager});
121 my $coa = $defaults->chart_of_accounts( 'Germany-DATEV-SKR03EU' );
122 my $am = $defaults->accounting_method( 'cash' );
123 my $pd = $defaults->profit_determination( 'balance' );
124 my $is = $defaults->inventory_system( 'periodic' );
125 my $curr = $defaults->currency( 'EUR' );
127 apply_dbupgrade($dbupdater, "sql/lx-office.sql");
128 apply_dbupgrade($dbupdater, "sql/${coa}-chart.sql");
130 dbh_do($dbh, qq|UPDATE defaults SET coa = '${coa}', accounting_method = '${am}', profit_determination = '${pd}', inventory_system = '${is}', curr = '${curr}'|);
131 dbh_do($dbh, qq|CREATE TABLE schema_info (tag TEXT, login TEXT, itime TIMESTAMP DEFAULT now(), PRIMARY KEY (tag))|);
134 sub create_initial_auth_schema {
135 dbg("Creating initial auth schema");
137 my $dbupdater = SL::DBUpgrade2->new(form => $::form, return_on_error => 1, auth => 1);
138 apply_dbupgrade($dbupdater, 'sql/auth_db.sql');
143 my $dbupdater = SL::DBUpgrade2->new(form => $::form, return_on_error => 1, auth => $params{auth});
144 my @unapplied_scripts = $dbupdater->unapplied_upgrade_scripts($dbh);
146 apply_dbupgrade($dbupdater, $_) for @unapplied_scripts;
148 # some dpupgrades are hardcoded for Germany and will be recovered by the same nasty code
149 if ((not defined $params{auth}) && ($::lx_office_conf{system}->{default_manager} eq "swiss")) {
150 # buchungsgruppen_sortkey.sql depends release_2_4_1
151 dbh_do($dbh, qq|UPDATE buchungsgruppen SET sortkey=1 WHERE description='Standard 8%'|);
152 dbh_do($dbh, qq|UPDATE buchungsgruppen SET sortkey=2 WHERE description='Standard 2.5%'|);
153 # steuerfilterung.pl depends release_3_0_0
154 dbh_do($dbh, qq|ALTER TABLE tax ADD chart_categories TEXT|);
155 dbh_do($dbh, qq|UPDATE tax SET chart_categories = 'I' WHERE (taxnumber='2200') OR (taxnumber='2201')|);
156 dbh_do($dbh, qq|UPDATE tax SET chart_categories = 'E' WHERE (taxnumber='1170') OR (taxnumber='1171')|);
157 dbh_do($dbh, qq|UPDATE tax SET chart_categories = 'ALQCIE' WHERE chart_categories IS NULL|);
158 dbh_do($dbh, qq|ALTER TABLE tax ALTER COLUMN chart_categories SET NOT NULL|);
159 # taxzone_id_in_oe_delivery_orders.sql depends release_3_1_0
160 dbh_do($dbh, qq|UPDATE oe SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Schweiz') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL)|);
161 dbh_do($dbh, qq|UPDATE delivery_orders SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Schweiz') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL)|);
162 dbh_do($dbh, qq|UPDATE ar SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Schweiz') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL)|);
163 dbh_do($dbh, qq|UPDATE ap SET taxzone_id = (SELECT id FROM tax_zones WHERE description = 'Schweiz') WHERE (taxzone_id = 0) OR (taxzone_id IS NULL)|);
164 # tax_skonto_automatic.sql depends release_3_2_0
165 dbh_do($dbh, qq|UPDATE tax SET skonto_purchase_chart_id = (SELECT id FROM chart WHERE accno = '4900')|);
166 dbh_do($dbh, qq|UPDATE tax SET skonto_sales_chart_id = (SELECT id FROM chart WHERE accno = '3800')|);
168 dbh_do($dbh, qq|UPDATE defaults SET rndgain_accno_id = (SELECT id FROM CHART WHERE accno='6953')|);
169 dbh_do($dbh, qq|UPDATE defaults SET rndloss_accno_id = (SELECT id FROM CHART WHERE accno='6943')|);
173 sub create_client_user_and_employee {
174 dbg("Creating client, user, group and employee");
176 dbh_do($dbh, qq|DELETE FROM auth.clients|);
177 dbh_do($dbh, qq|INSERT INTO auth.clients (id, name, dbhost, dbport, dbname, dbuser, dbpasswd, is_default) VALUES (1, 'Unit-Tests', ?, ?, ?, ?, ?, TRUE)|,
178 bind => [ @{ $db_cfg }{ qw(host port db user password) } ]);
179 dbh_do($dbh, qq|INSERT INTO auth."user" (id, login) VALUES (1, 'unittests')|);
180 dbh_do($dbh, qq|INSERT INTO auth."group" (id, name) VALUES (1, 'Vollzugriff')|);
181 dbh_do($dbh, qq|INSERT INTO auth.clients_users (client_id, user_id) VALUES (1, 1)|);
182 dbh_do($dbh, qq|INSERT INTO auth.clients_groups (client_id, group_id) VALUES (1, 1)|);
183 dbh_do($dbh, qq|INSERT INTO auth.user_group (user_id, group_id) VALUES (1, 1)|);
186 default_printer_id => '',
187 template_format => '',
189 email => 'unit@tester',
191 dateformat => 'dd.mm.yy',
192 show_form_details => '',
193 name => 'Unit Tester',
195 hide_cvar_search_options => '',
196 numberformat => '1.000,00',
202 stylesheet => 'lx-office-erp.css',
203 mandatory_departments => 0,
207 my $sth = $dbh->prepare(qq|INSERT INTO auth.user_config (user_id, cfg_key, cfg_value) VALUES (1, ?, ?)|) || BAIL_OUT($dbh->errstr);
208 dbh_do($dbh, $sth, bind => [ $_, $config{$_} ]) for sort keys %config;
211 $sth = $dbh->prepare(qq|INSERT INTO auth.group_rights (group_id, "right", granted) VALUES (1, ?, TRUE)|) || BAIL_OUT($dbh->errstr);
212 dbh_do($dbh, $sth, bind => [ $_ ]) for sort $::auth->all_rights;
215 dbh_do($dbh, qq|INSERT INTO employee (id, login, name) VALUES (1, 'unittests', 'Unit Tester')|);
217 $::auth->set_client(1) || BAIL_OUT("\$::auth->set_client(1) failed");
218 %::myconfig = $::auth->read_user(login => 'unittests');
221 verify_configuration();
223 drop_and_create_database();
224 create_initial_schema();
225 create_initial_auth_schema();
226 apply_upgrades(auth => 1);
227 create_client_user_and_employee();