SQLiteTilesImportPerlScriptEN: mapconvert

#!/usr/bin/perl
use DBI qw(:sql_types);
use Cwd;
$| = 1;

# We want to trap control-c to ensure a clean shutdown
$SIG{'INT'} = 'shutdown';

my $storenumber = 0;
my $store;
my $lookup;
my $here = getcwd;

# Set maximum size for a database file
my $max_size = 3865470566;


# Connect to both database files and create the schemas (if necessary)
my $needcreate = 0;
unless(-f "lookup.sqlite")
{
        $needcreate = 1;
}
$store = DBI->connect("dbi:SQLite:dbname=store.sqlite.0","","",{AutoCommit => 0}) or die($!);
$lookup = DBI->connect("dbi:SQLite:dbname=lookup.sqlite","","",{AutoCommit => 0}) or die($!);
if($needcreate)
{
        $lookup->do("CREATE TABLE tiles (z integer, x integer, y integer, store_filename string, extension varchar(10), unix_epoch_timestamp integer, primary key (z, x, y, extension))");
        $lookup->do("CREATE TABLE version (v integer)");
        $lookup->do("insert into version values(1)");
        create_store_schema();
}
my $storeinsert = prepare_query();
my $lookupinsert = $lookup->prepare("insert into tiles values(?,?,?,?,?,?)");
my $check_exists = $lookup->prepare("select store_filename from tiles where z=? and x=? and y=?");

# Get a list of zoom levels
my @zooms = <[0-9]*>;

# Work through each zoom level

my $filecount = `find [0-9]* -type f|wc -l`;
chomp($filecount);
my $filenum = 0;
my $transaction = 0;
my ($z,$x,@x,$y,$file,@files,$files,@data,$data,$stamp,$extension,$existingstore);
foreach $z(@zooms)
{
        chdir($z);
        # Get a list of x co-ordinates
        @x = <*>;
        foreach $x(@x)
        {
                chdir($x);
                # Get a list of files
                @files = <*>;
                # Work through the files and add them to the database
                foreach $file(@files)
                {
                        chomp($file);
                        $filenum++;
                        # Get timestamp
                        $stamp = (stat($file))[9];
                        # Get file data
                        $data = `cat $file`;
                        # get extension and filename
                        ($y,$extension) = split(/\./,$file);
                        # Check if the tile already exists
                        $check_exists->execute($z,$x,$y);
                        $existingstore = $check_exists->fetchrow_array();
                        if($existingstore eq "")
                        {
                                # Push into databases
                                $storeinsert->bind_param(4,$data,SQL_BLOB);
                                $storeinsert->bind_param(1,$z,SQL_INTEGER);
                                $storeinsert->bind_param(2,$x,SQL_INTEGER);
                                $storeinsert->bind_param(3,$y,SQL_INTEGER);
                                $storeinsert->bind_param(5,$extension,SQL_VARCHAR);
                                $storeinsert->bind_param(6,$stamp,SQL_INTEGER);
                                $storeinsert->execute();
                                $lookupinsert->execute($z,$x,$y,'store.sqlite.' . $storenumber,$extension,$stamp);
                                # Only commit every 50 transactions to save time
                                $transaction++;
                                if($transaction == 50)
                                {
                                        print(" Processed $filenum / $filecount tiles\r");
                                        flush();
        
                                        # Check the store filesize
                                        my $size = (stat("$here/store.sqlite.$storenumber"))[7];
                                        if($size > $max_size)
                                        {
                                                # Need to open a new store file
                                                $store->disconnect();
                                                $storenumber++;
                                                $store = DBI->connect("dbi:SQLite:dbname=$here/store.sqlite.$storenumber","","",{AutoCommit => 0}) or die($!);
                                                create_store_schema();
                                                $storeinsert = prepare_query();
                                        }
                                }
                        }
                }
                chdir("..");
        }
        chdir("..");
}
# We're done, so shut down tidily
flush();
$store->disconnect();
$lookup->disconnect();


sub prepare_query
{
        my $storeinsert = $store->prepare("insert into tiles values(?,?,?,?,?,?)");
        return($storeinsert);
}
sub create_store_schema
{
        $store->do("CREATE TABLE tiles (z integer, x integer, y integer, tile blob, extension varchar(10), unix_epoch_timestamp integer, primary key (z, x, y, extension))");
        $store->do("CREATE TABLE version (v integer)");
        $store->do("insert into version values(1)");
}
sub shutdown
{
        # called when Ctrl-C is hit
        
        # Make sure another click doesn't abort us
        $SIG{'INT'} = 'IGNORE';
        print("\nShutting down\n");
        flush();
        $store->disconnect();
        $lookup->disconnect();
        exit;
}
sub flush
{
        # Commits the database.
        $store->commit();
        $lookup->commit();
        $transaction = 0;
}