International SQL-Ledger Network Association

This is an old revision of the document!

---

You just need to copy this script anywhere on the server where you have the database that you want to use as a template. Then edit the script and change the database name 'ledger28' to your database name and run the script from command line like this:

  perl generate_chart_file.pl

It will create the required chart file in the current folder which you can then move into your sql-ledger/sql folder.

This script creates INSERT statements for the 3 tables chart, curr, defaults, but you can add or remove tables from this list as needed by editing the script.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $db_name = 'ledger28';
my $db_host = 'localhost';
my $db_user = 'sql-ledger';
my $db_pass = '';

my $dbh = DBI->connect(
  "dbi:Pg:dbname=$db_name;host=$db_host",
  $db_user, $db_pass,
  {
      RaiseError => 1,
      PrintError => 0,
  }
);

my $output_file = 'RMADE_DEU_Basiskontenplan-chart.sql';
open( my $output_fh, '>', $output_file ) or die "Could not open $output_file: $!";

my @tables = ( 'chart', 'curr', 'defaults' );

foreach my $table (@tables) {
  my $stmt = $dbh->prepare("SELECT * FROM $table");
  $stmt->execute();

  my @columns;
  for my $col_info ( @{ $stmt->{NAME_lc} } ) {
      push @columns, $col_info unless $col_info eq 'id';
  }

  while ( my $row = $stmt->fetchrow_hashref() ) {
      my @values      = map { defined $row->{$_} ? "'$row->{$_}'" : 'NULL' } @columns;
      my $columns_str = join( ', ', @columns );
      my $values_str  = join( ', ', @values );
      my $insert_sql  = "INSERT INTO $table ($columns_str) VALUES ($values_str);";
      print $output_fh "$insert_sql\n";
  }
}

close $output_fh;

$dbh->disconnect;

print "SQL statements generated and saved to $output_file\n";