File: create-bridge-sql.pl

package info (click to toggle)
libchado-perl 1.31-6
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, sid
  • size: 44,716 kB
  • sloc: sql: 282,721; xml: 192,553; perl: 25,524; sh: 102; python: 73; makefile: 57
file content (111 lines) | stat: -rwxr-xr-x 2,972 bytes parent folder | download | duplicates (4)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
#!/usr/bin/env perl

# usage docs at end of file

use strict;
use warnings;
use Data::Stag;

my %relation_type_h = ();
while (@ARGV && $ARGV[0] =~ /^(\-.+)/) {
    my $switch = shift @ARGV;
    if ($switch eq '-t' || $switch eq '--table') {
        while (@ARGV > 1 && $ARGV[0] !~ /^\-/) {
            $relation_type_h{shift @ARGV} = 'table';
        }
    }
    elsif ($switch eq '-v' || $switch eq '--view') {
        while (@ARGV > 1 && $ARGV[0] !~ /^\-/) {
            $relation_type_h{shift @ARGV} = 'view';
        }
    }
    elsif ($switch eq '-h' || $switch eq '--help') {
        print usage();
        exit 0;
    }
    else {
        die "unknown option: $switch";
    }
}
my $f = shift @ARGV;


my $bridge = Data::Stag->parse($f);
my $schema = $bridge->sget('@/schema');

print "CREATE SCHEMA $schema;\n\n";
foreach my $relation ($bridge->get_relation) {
    my $id = $relation->sget('@/id');
    my @indexes = $relation->get_index;
    my $relation_type = 'view';

    # anything with indexes is by default a table
    if (@indexes) {
        $relation_type = "table";
    }

    # command line override
    if ($relation_type_h{'ALL'}) {
        $relation_type = 
          $relation_type_h{'ALL'};
    }
    if ($relation_type_h{$id}) {
        $relation_type = 
          $relation_type_h{$id};
    }
    my $sql = $relation->sget_sql;
    $sql =~ s/^\s+//;
    $sql =~ s/\;\s*//;
    printf("CREATE %s %s.%s AS\n%s;\n",
           uc($relation_type), 
           $schema,
           $id,
           $sql);
    print "$_\n" foreach @indexes;
    print "\n\n";
}

exit 0;

# --

sub usage {
    return <<EOM
create-bridge-sql.pl [OPTIONS] bridge.sql.xml > bridge.sql      
Options:
  -t --table : list of relations to be materialized
  -v --view  : list of relations to be unmaterialized
ALL can be used to specify a default

Examples:
create-bridge-sql.pl -v term term2term godb-bridge.sql.xml > godb-bridge.sql
create-bridge-sql.pl -v ALL -t term godb-bridge.sql.xml > godb-bridge.sql      
create-bridge-sql.pl chado-bridge.sql.xml > chado-bridge.sql      

Description:

This script generates a 'bridge' layer over the main schema. This
allows chado to 'masquerade' as another database, allowing us to use
software written that database schema.

The bridge layer can be views or materialized views (tables), or a
mixture. If materialized views are created, the layer may need
recreated if the underlying data changes (the bridge may not implement
triggers to do this for you)

The bridge layer is created in a seperate schema - the user has
control over the search path; eg in postgresql:

  chado# SET SEARCH PATH TO godb,public;

Bridges typically live in chado/modules/*/bridges/*-bridge.sql.xml

TODO:
Currently the autogenerated bridge can only be applied once.
It would be nice (and easy) to have code to arbitrarily switch
between tables and views at any points in the database lifecycle

Also - finer grained control over indexes
EOM
      ;
}