gw2sql A SQL to GeneWeb converter by Yann Corno - last update: 2006-05-01

Contents

1. Introduction
2. Usage
3. Example
4. Theory
5. Adapting sql2gw.pl to your own needs and database
6. Download
7. Changes History
8. Special Thanks
9. Legal Stuff
10. Contacting me

Introduction

sql2gw performs the opposite as its gw2sql counterpart: converting a SQL database into a GW source file that can be imported into GeneWeb using the gwc tool that is included with GeneWeb. See the GeneWeb documentation for details about this file format.

It is a PERL script, so it can be used on any platform. Before using this tool, it is recommanded to read and play with gw2sql to learn some basic concepts.

Usage

    perl sql2gw.pl [-o outfile.gw] [-v|--verbose] [--utf8] [-q lastname]

Parameters in [brakets] are optional.

-o outfile.gw		name of the output GeneWeb file. If omitted, standard output is used.
-v, --verbose		display progress. Only if -o option is used.
--utf8		insert "encoding: utf-8" at the beginning of the GW file. Use it if your SQL database contains UTF-8 data.
-q lastname		output only families where one parent have this last name

Example

the following example makes a complete transfer of a SQL database into a fresh GeneWeb one named "yourGeneWebDB":

    perl sql2gw.pl -o YourGeneWebBase.gw
    gwc YourGeneWebBase.gw -o yourGeneWebDB

Theory

sql2gw converts a genealogical SQL database into a GW file (GeneWeb source file).

It is designed to work with the SQL table structures created by its counterpart, gw2sql. See the gw2sql page to learn about the role of the various tables.

Adapting sql2gw.pl to your own needs and database

There are two things to do, which are performed by editing the sql2gw.pl script:

1. Specify your SQL connection parameters:
   change the values of the $data_source, $data_user, $data_pass variables, so that the script can talk to your database.
    
2. Convert data from your database.
   Most likely, you are using a database structure that is different from the gw2sql one. That's not a problem. Here is how to proceed:
   • Using the other script, gw2sql.pl, build the empty tables in your database:
     

                       perl gw2sql.pl --nodata | mysql yourDatabase

   • Then use SQL commands to copy data from your database to the gw_ tables. For example, let's suppose that you have a 'persons' table, you can copy them into the 'gw_person' table using a simple INSERT/SELECT:
     

                       INSERT INTO gw_person (lastname, firstname) SELECT last_name, first_name FROM persons;

Once all your SQL commands are finished and tested, you should put them in a text file to be able to run them automatically next time.

Download

Download sql2gw.pl (24 KB)

Changes History

See header of script file.

Special Thanks

Special thanks to my friend Erich from Germany, who kindly tested my script, uncovering many bugs. There is no good programmer without a good tester, and Erich is a great one!

Legal Stuff

This program is made available under the GeneWeb GPL Licence. GeneWeb is a software developed by Daniel de Rauglaudre, and is © Copyright INRIA. You may change and distribute this work as long as you comply with the GeneWeb GPL Licence.

IMPORTANT NOTICE: THIS WORK IS PROVIDED FOR EXPERIMENTAL PURPOSES ONLY. THERE ARE ABSOLUTELY NO WARRANTY WHATSOEVER THAT COMES WITH THIS SOURCE CODE AND THE INFORMATION DESCRIBED IN THIS PAGE. YOU MAY LOOSE YOUR DATA AND/OR HAVE COMPUTER HARDWARE OR SOFTWARE DAMAGES, OR SECURITY BREACHES. YOU ARE USING ALL THIS AT YOUR OWN RISKS. YOU HAVE BEEN WARNED !

Contacting me

You feed back or source code contributions are welcome! Contact me at . Have fun.

Yann Corno