GCstar forums

draco91 · 2011-01-29 19:06:02

Hello,

I wanted to build an SQL database with my movies (to use it along php within a LAMP server). For those who are interested in building custom SQL exports, for instance with upgrade statements rather than insert, this my be useful.

As I mentionned above, the built-in export function was not enough for me and

Code:

gcstar -x -e SQL --exportprefs "table=>films.items,withDrop=>0,withCreate=>0" ~/Atelier/Vid�os/films-db/base-films.gcs | mysql -uwww-admin

did not give the exact result I needed.

Instead, I use the file convertor-to-sql.xslt with the following content:

Code:

<!-- XSLT GCStar par Draco91 -->

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" version="1.0" encoding="UTF-8" indent="no" omit-xml-declaration="yes"/>

<xsl:template match="/collection">
CONNECT films;

<xsl:variable name="old">'</xsl:variable>
<xsl:variable name="old2">'
</xsl:variable>

<xsl:for-each select="item">

UPDATE fichiers
SET 
    webPage = '<xsl:value-of select="translate(@webPage, $old, '`')"/>', 
    title = '<xsl:value-of select="translate(@title, $old, '`')"/>', 
    actors = '<xsl:for-each select="actors/line">
        <xsl:value-of select="translate(concat(col[1], ' (', col[2], ') ; '), $old, '`')"/>
        </xsl:for-each>',
    year = right('<xsl:value-of select="translate(@date, $old, '`')"/>', 4), 
    time = '<xsl:value-of select="translate(@time, $old, '`')"/>', 
    genre = '<xsl:value-of select="translate(@genre, $old, '`')"/>', 
    image = '<xsl:value-of select="substring-after(@image, '_pictures/')" />' ,
    country = '<xsl:value-of select="translate(@country, $old, '`')"/>', 
    ratingpress = '<xsl:value-of select="translate(@ratingpress, $old, '`')"/>', 
    director = '<xsl:value-of select="translate(@director, $old, '`')"/>', 
    synopsis = '<xsl:value-of select="translate(synopsis, $old, '`')"/>', 
    problem = '',
    <xsl:if test="@original=''">
    original = '<xsl:value-of select="translate(@title, $old, '`')"/>' </xsl:if>
    <xsl:if test="@original!=''">
    original = '<xsl:value-of select="translate(@original, $old, '`')"/>' </xsl:if>
WHERE id = <xsl:value-of select="translate(@id, $old, '`')"/> ;

</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

It's fairly simple so there is not much to comment about. Note that the single quotes have to be escaped in SQL (that is, convert ' to ''). As no xslt function allows to do that, I convert first to a ` using the translate function, and then apply a "sed" on the output to convert it to twice a single quote, that is:

Code:

xsltproc path/to/convertor-to-sql.xslt /path/to/gcstar.gcs | sed "s/\`/\'\'/g" | sed "s/&amp;/&/g"

The second sed was needed since xsltproc generates "&" instead of "&" on some occasions.

I pipe the output to mysql as follows:

Code:

xsltproc path/to/convertor-to-sql.xslt /path/to/gcstar.gcs | sed "s/\`/\'\'/g" | sed "s/&amp;/&/g" | mysql -uwww-admin -p"the password" -v

To perform the reverse conversion, I use the following php file:

Code:

<?
    echo "<?xml version='1.0' encoding='UTF-8'?>
";

    $user = $_SERVER['PHP_AUTH_USER'];

    // this allows to provide a query through the GET variables, as shown in the example below
    if (isset($_GET['query']))
        $cr_query = $_GET["query"];
    else
        $cr_query = "SELECT *
FROM fichiers
WHERE filmid = -1"; 

    $connexion = mysql_connect("localhost", "www-admin", "my password");

    mysql_select_db("films"); 
    $demande = $cr_query;
    $demande2 = "SELECT COUNT(*) as CNT 
FROM fichiers ;";

    $result = mysql_query($demande);
    $result2 = mysql_query($demande2);
    $count = mysql_numrows($result);
?>
<collection type="GCfilms" items="<? echo $count; ?>" version="1.6.1">
 <information>
  <maxId><? echo $maxid = mysql_result($result2, 0, 'CNT'); ?></maxId>
  <? echo "<!-- " . $demande . " -->"; ?>
 </information>

<?
    for ($i=0; $i<$count; $i++) {
?>
 <item
  id="<? 
    $id = mysql_result($result, $i, 'id');; 
    if (1*$id == -1)
        echo $maxid++;
    else
        echo $id;
        
?>"
  title="<? echo mysql_result($result, $i, 'title');; ?>"
  date="<? echo mysql_result($result, $i, 'year');; ?>"
  time=""
  director="<? echo mysql_result($result, $i, 'director');; ?>"
  country="<? echo mysql_result($result, $i, 'country');; ?>"
  genre=""
  image=""
  backpic=""
  original=""
  webPage="<? echo mysql_result($result, $i, 'webPage');; ?>"
  seen=""
  added=""
  region=""
  format="DVD"
  number="1"
  identifier="0"
  place=""
  rating="0"
  ratingpress="0"
  audio=""
  subt=""
  age=""
  video=""
  serie=""
  rank=""
  trailer=""
  borrower="none"
  lendDate=""
  borrowings=""
  favourite="0"
  tags=""
 >
  <synopsis><? echo mysql_result($result, $i, 'synopsis') ; ?></synopsis>
  <comment>Titre : <? echo substr(mysql_result($result, $i, 'filename'),22,999); ?></comment>
 </item>
<?
    }
    mysql_close();
?>
</collection>

To use it, I use the following bash command:

Code:

wget --user=gcstar-updater --password=blablabla --no-check-certificate "https://localhost/films/export-to-gcstar.php?query=SELECT+*+%0D%0AFROM+fichiers+%0D%0AWHERE+filmid+%3D+-1++%0D%0A++;" -O /tmp/gcstar_temp.gcs 
sed "s/&/&amp;/g" /tmp/gcstar_temp.gcs > /tmp/gcstar.gcs

An alternative that I first tried was to use sql commands to export to xml. Unfortunately, this is not working, probably because of an encoding issue. If someone can understand why, it's probably an easier way to do the conversion:

Code:

CONNECT films

SELECT CONCAT('<?xml version="1.0" encoding="UTF-8"?>
<collection type="GCfilms" items="', count(*), '" version="1.6.1">
 <information>
  <maxId>', max(filmid)+1, '</maxId>
 </information>') from fichiers;

SELECT CONCAT('<item
  id="', id, '"
  title="', title, '"
  date=""
  time=""
  director=""
  country=""
  genre=""
  image=""
  backpic=""
  actors=""
  original=""
  webPage="', webPage, '"
  seen="0"
  added=""
  region=""
  format=""
  number=""
  identifier=""
  place=""
  rating=""
  ratingpress=""
  audio=""
  subt=""
  age=""
  video=""
  serie=""
  rank=""
  trailer=""
  borrower=""
  lendDate=""
  borrowings=""
  favourite=""
  tags=""
  >
  <synopsis></synopsis>
  <comment>Titre : ', title, '</comment>
 </item>


') from fichiers;

SELECT '
</collection>';

If this is in the file export-to-gcs.sql, the command would be something like:

Code:

mysql -uwww-user -p"blabla" < export-to-gcs.sql > output.gcs

Hope this helps anyone with similar targets. Let me know if things are unclear.

Ludovic

GCstar forums - [SQL] xslt to convert gcstar file to sql (and the other way)

#1 2011-01-29 19:06:02

[SQL] xslt to convert gcstar file to sql (and the other way)

Code:

Code:

Code:

Code:

Code:

Code:

Code:

Code: