From f52c9fde3f9d13339b24c4acbe756b872e762c09 Mon Sep 17 00:00:00 2001 From: Bernardo Gonzalez Kriegel Date: Mon, 16 Mar 2020 18:44:44 -0300 Subject: [PATCH] Bug 24883: Command line utility to load yaml files This patch adds a command line utility to load yaml files. To test: 1) Apply the patch 2) Try loading a file, e.g. sample notices a) mysql -e "delete from letter" b) misc/load_yaml.pl -f installer/data/mysql/en/mandatory/sample_notices.yml 3) Try loading a file with a syntax error, edit previous file adding ':' at the end of a field without quotes then load again Signed-off-by: Martin Renvoize Signed-off-by: Marcel de Rooy Signed-off-by: Martin Renvoize --- misc/load_yaml.pl | 88 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 88 insertions(+) create mode 100755 misc/load_yaml.pl diff --git a/misc/load_yaml.pl b/misc/load_yaml.pl new file mode 100755 index 0000000000..e670f56660 --- /dev/null +++ b/misc/load_yaml.pl @@ -0,0 +1,88 @@ +#!/usr/bin/perl +# +# Copyright 2020 Koha Development Team +# +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. +# +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . + +use Modern::Perl; + +use YAML::Syck qw( LoadFile ); +use C4::Context; +use Getopt::Long qw(:config no_ignore_case); +use Data::Printer; + +sub print_usage { + ( my $basename = $0 ) =~ s|.*/||; + print < \$file, + 'help|h' => \$help +) or print_usage, exit 1; + +if ($help or not $file) { + print_usage; + exit; +} + +my $dbh = C4::Context->dbh; +my $yaml; +eval { + $yaml = LoadFile( $file ); # Load YAML +}; +if ($@){ + die "Something went wrong loading file $file ($@)"; +} + +for my $table ( @{ $yaml->{'tables'} } ) { + my $table_name = ( keys %$table )[0]; # table name + my @rows = @{ $table->{$table_name}->{rows} }; # + my @columns = ( sort keys %{$rows[0]} ); # column names + my $fields = join ",", map{sprintf("`%s`", $_)} @columns; # idem, joined + my $placeholders = join ",", map { "?" } @columns; # '?,..,?' string + my $query = "INSERT INTO $table_name ( $fields ) VALUES ( $placeholders )"; + my $sth = $dbh->prepare($query); + my @multiline = @{ $table->{$table_name}->{'multiline'} }; # to check multiline values; + foreach my $row ( @rows ) { + my @values = map { + my $col = $_; + ( @multiline and grep { $_ eq $col } @multiline ) + ? join "\r\n", @{$row->{$col}} # join multiline values + : $row->{$col}; + } @columns; + $sth->execute( @values ); + } +} +for my $statement ( @{ $yaml->{'sql_statements'} } ) { # extra SQL statements + $dbh->do($statement); +} -- 2.39.5