# Copyright (C) 2004-07  Stephane Galland <galland@arakhne.org>
#
# This program 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 2 of the License, or
# (at your option) any later version.
#
# This program 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 this program; see the file COPYING.  If not, write to
# the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA 02111-1307, USA.

=pod

=head1 NAME

Bib2HTML::Generator::SQLGen - A basic SQL generator

=head1 SYNOPSYS

use Bib2HTML::Generator::SQLGen ;

my $gen = Bib2HTML::Generator::SQLGen->new( content, output, info, titles,
                                            lang, theme, params ) ;

=head1 DESCRIPTION

Bib2HTML::Generator::SQLGen is a Perl module, which permits to
generate SQL scripts for the BibTeX database.

=head1 GETTING STARTED

=head2 Initialization

To start a generator script, say something like this:

    use Bib2HTML::Generator::SQLGen;

    my $gen = Bib2HTML::Generator::SQLGen->new( { }, "./bib.sql",
						 { 'BIB2HTML_VERSION' => "0.1",
						 },
						 { 'SHORT' => "This is the title",
						   'LONG' => "This is the title",
						 },
						 "English",
						 "Simple", ""
					       ) ;

...or something similar. Acceptable parameters to the constructor are:

=over

=item * content (hash)

see AbstractGenerator help.

=item * output (string)

see AbstractGenerator help.

=item * bib2html_data (hash)

see AbstractGenerator help.

=item * titles (hash)

see AbstractGenerator help.

=item * lang (string)

see AbstractGenerator help.

=item * theme (string)

is the name of the theme to use

=item * show_bibtex (boolean)

indicates if this parser must generate a verbatim of the BibTeX code

=item * params (optional array)

is the set of parameters passed to the generator.

=back

=head1 METHOD DESCRIPTIONS

This section contains only the methods in SQLGen.pm itself.

=over

=cut

package Bib2HTML::Generator::SQLGen;

@ISA = ('Bib2HTML::Generator::AbstractGenerator');
@EXPORT_OK = qw();

use strict;
use vars qw(@ISA @EXPORT @EXPORT_OK $VERSION);
use Carp ;
use File::Spec ;
use File::Basename ;

use Bib2HTML::Generator::AbstractGenerator;
use Bib2HTML::General::Misc;
use Bib2HTML::General::Error;
use Bib2HTML::General::HTML;
use Bib2HTML::General::Encode;
use Bib2HTML::Translator::BibTeXName;

#------------------------------------------------------
#
# Global vars
#
#------------------------------------------------------

# Version number of this generator
my $VERSION = "1.0" ;

#------------------------------------------------------
#
# Constructor
#
#------------------------------------------------------

sub new($$$$$$$;$) : method {
  my $proto = shift;
  my $class = ref($proto) || $proto;

  my @langs = ();
  if (isarray($_[4])) {
    foreach my $l (@{$_[4]}) {
     if ($l !~ /_[a-zA-Z0-9]+Gen$/) {
       push @langs, $l."_SQLGen";
     }
     push @langs, $l;
    }
  }
  else {
     @langs = ( $_[4] );
     if ($_[4] !~ /_[a-zA-Z0-9]+Gen$/) {
       unshift @langs, $_[4]."_SQLGen";
     }
  }

  my $self = $class->SUPER::new($_[0], #content
				$_[1], #output
				$_[2], #bib2html info
				$_[3], #titles
				\@langs, #lang
				$_[6], #show bibtex
				$_[7], #params
			       ) ;
  bless( $self, $class );

  return $self;
}

#------------------------------------------------------
#
# Generation parameters
#
#------------------------------------------------------

=pod

=item * save_generator_parameter()

Replies if the specified generator parameter was supported.
This function was called each time a generator parameter was
given to this generator. By default, simply update the
given parameter value (second parameter).
You could do some stuff before
saving (splitting...). Replies false is the parameter was
not recognized. Don't forget to call inherited functions.
Takes 2 args:

=over

=item * param_name (string)

is the name of the parameter.

=item * param_value (byref string)

is the value of the parameter.

=back

=cut
sub save_generator_parameter($$) {
  my $self = shift ;
  if ( ( $_[0] eq 'sql-encoding' ) ) {
    # will be saved by PARENT::new
    return 1 ;
  }
  else {
    return $self->SUPER::save_generator_parameter($_[0],$_[1]) ;
  }
}

=pod

=item * display_supported_generator_params()

Display the list of supported generator parameters.

=cut
sub display_supported_generator_params() {
  my $self = shift ;
  $self->SUPER::display_supported_generator_params() ;

  $self->show_supported_param('sql-encoding',
			      'String, the character encoding used in the SQL script '.
			      '("UTF8", "ISO-8859-1"...).' );
}

#------------------------------------------------------
#
# Generation API
#
#------------------------------------------------------

=pod

=item * pre_processing()

Pre_processing.

=cut
sub pre_processing() : method {
  my $self = shift ;

  # Set the HTML encoding
  if (!$self->{'GENERATOR_PARAMS'}{'sql-encoding'}) {
    $self->{'GENERATOR_PARAMS'}{'sql-encoding'} = get_default_encoding();
  }
  set_default_encoding($self->{'GENERATOR_PARAMS'}{'sql-encoding'});

  # Call inherited generation method
  $self->SUPER::pre_processing() ;
}

=pod

=item * do_processing()

Main processing.

=cut
sub do_processing() : method {
  my $self = shift ;

  # Call inherited generation method
  $self->SUPER::do_processing() ;

  # Generates each part of the document
  my $t = '' ;

  # Generates the schema of the database
  $self->create_SQLSCHEMA($t) ;

  # Generates the content for each entries
  $self->create_SQLENTRIES($t) ;

  # Create file
  $self->create_FILE($t) ;
}

=pod

=item * create_SQLSCHEMA()

Generates the SQL schema.
Takes 1 arg:

=over

=item * content (string)

is the content to fill

=back

=cut
sub create_SQLSCHEMA($) : method {
  my $self = shift ;

  $_[0] .= join("\n",
	"DROP TABLE bibtex_entrytype CASCADE;",
	"CREATE TABLE bibtex_entrytype (",
	"  type varchar(50) NOT NULL,",
	"  PRIMARY KEY(type)",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_domain CASCADE;",
	"CREATE TABLE bibtex_domain (",
	"  identifier int NOT NULL,",
	"  name varchar(100) NOT NULL,",
	"  PRIMARY KEY(identifier)",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_entry CASCADE;",
	"CREATE TABLE bibtex_entry (",
	"  entry_key varchar(50) NOT NULL,",
	"  year int NOT NULL default 1900,",
	"  title text NOT NULL,",
	"  type varchar(50) NOT NULL,",
	"  crossref varchar(50),",
	"  PRIMARY KEY(entry_key),",
	"  FOREIGN KEY(type) REFERENCES bibtex_entrytype(type) ON DELETE CASCADE,",
	"  FOREIGN KEY(crossref) REFERENCES bibtex_entry(entry_key) ON DELETE SET NULL",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_field CASCADE;",
	"CREATE TABLE bibtex_field (",
	"  identifier int NOT NULL,",
	"  name varchar(100) NOT NULL,",
	"  value text,",
	"  entry_key varchar(50) NOT NULL,",
	"  PRIMARY KEY(identifier),",
	"  FOREIGN KEY(entry_key) REFERENCES bibtex_entry(entry_key) ON DELETE CASCADE",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_identity CASCADE;",
	"CREATE TABLE bibtex_identity (",
	"  identifier int NOT NULL,",
	"  name varchar(100) NOT NULL,",
	"  firstname varchar(100) NOT NULL,",
	"  von varchar(20),",
	"  junior varchar(20),",
	"  PRIMARY KEY(identifier)",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_authors CASCADE;",
	"CREATE TABLE bibtex_authors (",
	"  entry_key varchar(50) NOT NULL,",
	"  author_id int NOT NULL,",
	"  ordre int NOT NULL DEFAULT 1,",
	"  etal boolean NOT NULL DEFAULT false,",
	"  PRIMARY KEY(entry_key,author_id),",
	"  FOREIGN KEY(entry_key) REFERENCES bibtex_entry(entry_key) ON DELETE CASCADE,",
	"  FOREIGN KEY(author_id) REFERENCES bibtex_identity(identifier) ON DELETE CASCADE",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_editors CASCADE;",
	"CREATE TABLE bibtex_editors (",
	"  entry_key varchar(50) NOT NULL,",
	"  editor_id int NOT NULL,",
	"  ordre int NOT NULL DEFAULT 1,",
	"  etal boolean NOT NULL DEFAULT false,",
	"  PRIMARY KEY(entry_key,editor_id),",
	"  FOREIGN KEY(entry_key) REFERENCES bibtex_entry(entry_key) ON DELETE CASCADE,",
	"  FOREIGN KEY(editor_id) REFERENCES bibtex_identity(identifier) ON DELETE CASCADE",
	");\n");

  $_[0] .= join("\n",
	"DROP TABLE bibtex_entrydomain CASCADE;",
	"CREATE TABLE bibtex_entrydomain (",
	"  entry_key varchar(50) NOT NULL,",
	"  domain_id int NOT NULL,",
	"  PRIMARY KEY(entry_key,domain_id),",
	"  FOREIGN KEY(entry_key) REFERENCES bibtex_entry(entry_key) ON DELETE CASCADE,",
	"  FOREIGN KEY(domain_id) REFERENCES bibtex_domain(identifier) ON DELETE CASCADE",
	");\n");

}

=pod

=item * sqlquote()

Replies a quoted string of the specified string.
Takes 1 arg:

=over

=item * content (string)

is the content to fill

=back

=cut
sub quotesql($) : method {
  my $self = shift;
  my $s = $_[0];
  $s =~ s/\'/\\\'/g;
  return $s;
}

=pod

=item * create_SQLENTRIES()

Generates the SQL pages for each entry
Takes 1 arg:

=over

=item * content (string)

is the content to fill

=back

=cut
sub create_SQLENTRIES($) : method {
  my $self = shift ;
  my @entries = $self->get_all_entries_ayt() ;
  my $i = $#entries ;
  while ( $i >= 0 ) {
    # Compute entry constants
    my $type = $self->{'CONTENT'}{'entries'}{$entries[$i]}{'type'} ;
    my $fields = $self->{'CONTENT'}{'entries'}{$entries[$i]}{'fields'};
    my $entry = '';

    # Insert the entry's type
    if ((!$self->{'GENERATION'}{'types'})||
        (!strinarray($type,$self->{'GENERATION'}{'types'}))) {
      $entry .= "INSERT INTO bibtex_entrytype (type) VALUES ('".$self->quotesql($type)."');\n";
      push @{$self->{'GENERATION'}{'types'}}, "$type";
    }

    # Insert the entry
    $entry .= "INSERT INTO bibtex_entry (entry_key,year,title,type) VALUES ('".
		$self->quotesql($entries[$i])."','".
		$self->quotesql($fields->{'year'}||0)."','".
		$self->quotesql($fields->{'title'}||'')."','".
		$self->quotesql($type)."');\n";
    delete $fields->{'year'};
    delete $fields->{'title'};

    if (exists $fields->{'author'}) {
      $self->create_SQLAUTHORS($entries[$i],$fields->{'author'},$entry);
      delete $fields->{'author'};
    }

    if (exists $fields->{'editor'}) {
      $self->create_SQLEDITORS($entries[$i],$fields->{'editor'},$entry);
      delete $fields->{'editor'};
    }

    if ((exists $fields->{'domain'})||
        (exists $fields->{'nddomain'})||
        (exists $fields->{'rddomain'})||
        (exists $fields->{'domains'})) {
      $self->create_SQLDOMAINS($entries[$i],
	join(':',
		($fields->{'domain'}||''),
		($fields->{'nddomain'}||''),
		($fields->{'rddomain'}||''),
		($fields->{'domains'}||'')),
	$entry);
      delete $fields->{'domain'};
      delete $fields->{'nddomain'};
      delete $fields->{'rddomain'};
      delete $fields->{'domains'};
    }

    # Fields
    foreach my $field (keys %{$fields}) {
      $self->create_SQLFIELD($entries[$i],
      		"$field", $fields->{"$field"},
		$entry);
    }

    $_[0] .= $entry ;
    $i -- ;
  }
}

=pod

=item * create_SQLAUTHORS()

Generates the SQL pages for the authors
Takes 3 args:

=over

=item * entry_key (string)

is the BibTeX key of the entry.

=item * authors (string)

is the list of authors.

=item * content (string)

is the content to fill.

=back

=cut
sub create_SQLAUTHORS($$$) : method {
  my $self = shift;
  my $entry_key = shift;
  my $sauthors = shift;

  my $translator = Bib2HTML::Translator::BibTeXName->new();  
  my @names = $translator->splitnames($sauthors);
  my $idxauthor = 0;

  for(my $idxname=0; $idxname<@names; $idxname++) {
    my $name = $names[$idxname];
    if (!$name->{'et al'}) {
      my $author_key = html_lc($translator->formatname($name,'l,i.'));

      my $id;
      if ((!$self->{'GENERATION'}{'authors'})||
          (!$self->{'GENERATION'}{'authors'}{"$author_key"})) {
        if (!$self->{'GENERATION'}{'authors'}) {
          $id = 1;
        }
        else {
          $id = (keys %{$self->{'GENERATION'}{'authors'}}) + 1;
        }
        $_[0] .= "INSERT INTO bibtex_identity (identifier,name,firstname,von,junior) VALUES ('".
	  	  $self->quotesql($id)."','".
		  $self->quotesql(translate_html_entities($name->{'last'}))."','".
		  $self->quotesql(translate_html_entities($name->{'first'}))."','".
		  $self->quotesql(translate_html_entities($name->{'von'}))."','".
		  $self->quotesql(translate_html_entities($name->{'jr'}))."');\n";
        $self->{'GENERATION'}{'authors'}{"$author_key"} = $id;
      }
      else {
        $id = $self->{'GENERATION'}{'authors'}{"$author_key"};
      }

      my $etal = ((($idxname+1)<@names)&&($names[$idxname+1]->{'et al'}));
    
      $_[0] .= "INSERT INTO bibtex_authors (entry_key,author_id,ordre,etal) VALUES ('".
	       $self->quotesql("$entry_key")."','".
	       $self->quotesql("$id")."','".
	       $self->quotesql("$idxauthor")."','".
	       $self->quotesql($etal ? "true" : "false")."');\n";

      $idxauthor++;
    }
  }
}

=pod

=item * create_SQLEDITORS()

Generates the SQL pages for the editors
Takes 3 args:

=over

=item * entry_key (string)

is the BibTeX key of the entry.

=item * editors (string)

is the list of editors.

=item * content (string)

is the content to fill.

=back

=cut
sub create_SQLEDITORS($$$) : method {
  my $self = shift;
  my $entry_key = shift;
  my $seditors = shift;

  my $translator = Bib2HTML::Translator::BibTeXName->new();  
  my @names = $translator->splitnames($seditors);
  my $idxeditor = 0;

  for(my $idxname=0; $idxname<@names; $idxname++) {
    my $name = $names[$idxname];
    if (!$name->{'et al'}) {
      my $editor_key = html_lc($translator->formatname($name,'l,i.'));

      my $id;
      if ((!$self->{'GENERATION'}{'authors'})||
          (!$self->{'GENERATION'}{'authors'}{"$editor_key"})) {
        if (!$self->{'GENERATION'}{'authors'}) {
          $id = 1;
        }
        else {
          $id = (keys %{$self->{'GENERATION'}{'authors'}}) + 1;
        }
        $_[0] .= "INSERT INTO bibtex_identity (identifier,name,firstname,von,junior) VALUES ('".
	  	  $self->quotesql($id)."','".
		  $self->quotesql(translate_html_entities($name->{'last'}))."','".
		  $self->quotesql(translate_html_entities($name->{'first'}))."','".
		  $self->quotesql(translate_html_entities($name->{'von'}))."','".
		  $self->quotesql(translate_html_entities($name->{'jr'}))."');\n";
        $self->{'GENERATION'}{'authors'}{"$editor_key"} = $id;
      }
      else {
        $id = $self->{'GENERATION'}{'authors'}{"$editor_key"};
      }

      my $etal = ((($idxname+1)<@names)&&($names[$idxname+1]->{'et al'}));
    
      $_[0] .= "INSERT INTO bibtex_editors (entry_key,editor_id,ordre,etal) VALUES ('".
	       $self->quotesql("$entry_key")."','".
	       $self->quotesql("$id")."','".
	       $self->quotesql("$idxeditor")."','".
	       $self->quotesql($etal ? "true" : "false")."');\n";

      $idxeditor++;
    }
  }
}

=pod

=item * create_SQLDOMAINS()

Generates the SQL pages for the entry's domains
Takes 3 args:

=over

=item * entry_key (string)

is the BibTeX key of the entry.

=item * domains (string)

is the list of domains separated by ':'.

=item * content (string)

is the content to fill.

=back

=cut
sub create_SQLDOMAINS($$$) : method {
  my $self = shift;
  my $entry_key = shift;
  my $sdomains = shift;

  my @domains = split(/\s*:\s*/,$sdomains);

  foreach my $domain (@domains) {
    $domain =~ s/^\s+//;
    $domain =~ s/\s+$//;
    if ($domain) {
      my $domain_key = $domain;
      $domain_key =~ s/\s//g;
      $domain_key = html_lc($domain_key);

      my $id;
      if ((!$self->{'GENERATION'}{'domains'})||
          (!$self->{'GENERATION'}{'domains'}{"$domain_key"})) {
        if (!$self->{'GENERATION'}{'domains'}) {
          $id = 1;
        }
        else {
          $id = %{$self->{'GENERATION'}{'domains'}} + 1;
        }
        $_[0] .= "INSERT INTO bibtex_domain (identifier,name) VALUES ('".
 	  	  $self->quotesql($id)."','".
 		  $self->quotesql(translate_html_entities($domain))."');\n";
        $self->{'GENERATION'}{'domains'}{"$domain_key"} = $id;
      }
      else {
        $id = $self->{'GENERATION'}{'domains'}{"$domain_key"};
      }

      $_[0] .= "INSERT INTO bibtex_entrydomain (entry_key,domain_id) VALUES ('".
        $self->quotesql("$entry_key")."','".
        $self->quotesql("$id")."');\n";
    }
  }
}

=pod

=item * create_SQLFIELD()

Generates the SQL pages for the entry's field
Takes 4 args:

=over

=item * entry_key (string)

is the BibTeX key of the entry.

=item * field_name (string)

is the name of the field to add.

=item * field_value (string)

is the value of the field to add.

=item * content (string)

is the content to fill.

=back

=cut
sub create_SQLFIELD($$$$) : method {
  my $self = shift;
  my $entry_key = shift;
  my $field_name = shift;
  my $field_value = shift;

  if ($self->{'GENERATION'}{'field_id'}) {
    $self->{'GENERATION'}{'field_id'}++;
  }
  else {
    $self->{'GENERATION'}{'field_id'} = 1;
  }

  $_[0] .= "INSERT INTO bibtex_field (identifier,entry_key,name,value) VALUES ('".
        $self->quotesql($self->{'GENERATION'}{'field_id'})."','".
        $self->quotesql("$entry_key")."','".
        $self->quotesql(translate_html_entities("$field_name"))."','".
        $self->quotesql(translate_html_entities("$field_value"))."');\n";
}

=pod

=item * create_FILE

Creates the SQL file.
Takes 1 arg:

=over

=item * content (string)

is the content of the SQL file.

=back

=cut
sub create_FILE($) : method {
  my $self = shift ;
  Bib2HTML::General::Verbose::two( "Writing ".$self->{'TARGET'}."..." ) ;

  my $writer = $self->get_stream_writer();
  
  $writer->openstream($self->{'TARGET'});
  $writer->out($_[0]||'') ;
  $writer->closestream() ;
}

1;
__END__

=back

=head1 COPYRIGHT

(c) Copyright 2004-07 Stéphane Galland E<lt>galland@arakhne.orgE<gt>, under GPL.

=head1 AUTHORS

=over

=item *

Conceived and initially developed by Stéphane Galland E<lt>galland@arakhne.orgE<gt>.

=back

=head1 SEE ALSO

bib2html.pl