Feeds a database with the content of a bibtex file parsed with bibtexbrowser

feeding-mysql-database-with-bibtexbrowser.php

<?php 
/* Feeds a database with the content of a bibtex file parsed with bibtexbrowser
 * See: http://www.monperrus.net/martin/feeding-mysql-database-with-bibtexbrowser
 * Author: Martin Monperrus
 * Last Modification Date: July 2013
 * Creation Date: Feb 2012
 */

// if exists, should contain define('DB_PASSWORD', 'dssizyrekzbqsf');, etc.
@include('conf.local.php');

/** MySQL database username */
@define('DB_USER', 'root');

/** MySQL database password */
@define('DB_PASSWORD', 'dssizyrekzbqsf');

/** MySQL hostname */
@define('DB_HOST', 'localhost');

/** The name of the database */
@define('DB_NAME', 'bibliography');

/** The name of the table */
@define('BIBTEX_TABLE', 'bibliography');


/** returns a BibDatabase object created from the content of $bibtex_file */
function init_bibtexbrowser($bibtex_file) {
  $_GET['bib'] = $bibtex_file;
  $_GET['library'] = 1;
  include('bibtexbrowser.php');
  setDB();
  $database = $_GET[Q_DB]; 
  return $database;
}

/** returns the list of fields used in the BibDatabase object $bibdb */
function get_field_list($bibdb) {
  $entries = $bibdb->bibdb;
  $result = array();
  foreach($entries as $entry) {
    foreach($entry->getFields() as $k => $v) {
      @$result[$k]++;
    }
  }
  return array_keys($result);
}

/** converts a Bibtex field name into a valid MySQL column name */
function convert_column_name($field) {
  return str_replace('-','_',$field);
}

/** sets the schema of the mysql DB based on $field_list and BIBTEX_TABLE */
function init_db($field_list) {
  mysql_connect(DB_HOST, DB_USER , DB_PASSWORD)
    or die('Could not connect: ' . mysql_error());

  mysql_select_db(DB_NAME) or die('Could not select database');
  
  // introspection
  $query = 'show tables;';
  $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);
  $found = false;
  while ($line = mysql_fetch_row($result)) { 
    if ($line[0] === BIBTEX_TABLE) {
      $found = true;
    }
  }
  
  // we create the table if it does not exist
  if (!$found) {
    $query = 'CREATE TABLE '.BIBTEX_TABLE.' (bibtexkey VARCHAR(255), PRIMARY KEY (bibtexkey)) ENGINE = MyISAM DEFAULT CHARSET=UTF8;';
    $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);
  }

  // introspection 2
  $query = 'show columns from '.BIBTEX_TABLE.';';
  $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);
  $columns = array();
  while ($line = mysql_fetch_row($result)) { 
    $columns[] = $line[0];
  }
  
  // altering table to add missing columns
  foreach($field_list as $rfield) {
    // some fields require special naming
    $field = convert_column_name($rfield);
    if (!in_array($field,$columns) && strtolower($field)!='key') {
       // altering the table
       $query = 'alter table '.BIBTEX_TABLE.' add `'.$field.'` TEXT NULL;';
       $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);    
    }
  }
}

/** adds escape and quotes around an HTML string; the string is also converted to UTF-8 */
function create_mysql_string_from_bibtexbrowser_value($f) {
  return "'".mysql_real_escape_string(html_entity_decode($f,ENT_NOQUOTES,'UTF-8'))."'";
}

/** feeds a MySQL database using the content of the BibDatabase object $bibdb.
 *
 * The MySQL schema is usually created using function init_db
 */
function feed_database($bibtex_db) {

  mysql_connect(DB_HOST, DB_USER , DB_PASSWORD)
    or die('Could not connect: ' . mysql_error());

  mysql_select_db(DB_NAME) or die('Could not select database');

  //print_r($bibtex_db->bibdb);
  
  foreach($bibtex_db->bibdb as $key=>$entry) {
    // do we have an entry ?
    $query = 'select * from '.BIBTEX_TABLE.' where bibtexkey=\''.$entry->getKey().'\';';
    //echo $query;
    $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);    
    //print_r($result);
    if (mysql_fetch_assoc($result) !==FALSE) {
      // updating the entry
      $fields = $entry->fields;
      $updates = array ();
      foreach ($fields as $k=>$v) { 
        if ($k!='key') {
          $column = convert_column_name($k);
          $updates[] = $column.'='.create_mysql_string_from_bibtexbrowser_value($v);
        }
      }
      echo 'updating '.$entry->getKey().'<br/>';
      $query = 'update '.BIBTEX_TABLE.' set '.implode(',',$updates).' where bibtexkey=\''.$entry->getKey().'\';';
      //echo $query;
      $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);        
    } else {
      // no such key
      $fields = $entry->fields;
      $keys = array ();
      foreach (array_keys($fields) as $f) { 
        if ($f!='key') {$keys[] = convert_column_name($f);}
        else {$keys[] = 'bibtexkey';}
      }

      $values = array ();
      foreach (array_values($fields) as $f) { 
        $values[] = create_mysql_string_from_bibtexbrowser_value($f);
      }
      
      echo 'adding '.$entry->getKey().'<br/>';
      $query = 'insert into  '.BIBTEX_TABLE.'('.implode(',',$keys).')       values ('.implode(',',$values).');';
      //echo $query;
      $result = mysql_query($query) or die('Query failed: ' . mysql_error().' '.$query);    
    }
  
  } // end foreach
} // end function 



$bibtex_db = init_bibtexbrowser('/var/www/bibtexbrowser/test/input/all.bib');
$field_list = get_field_list($bibtex_db);
init_db($field_list);
feed_database($bibtex_db);

?>

So I updated the code to use mysqli_* functions to replace the depreciated MySQL_* functions. However, I found the code cannot really insert data to the MySQL database. First, it can step into the feed_database($bibtex_db); function, but it seems always assuming the entry exists and run the _update_ entry query. Second, after running it, all columns remain empty (checked from phpMySQL admin interface). No error occurred to me. Can you help take a look? The code is here. Thanks.

Now I am using a new strategy to judge if a entry exists in the current table, and it seems works! You can check my current version for improvements and do more tests.