Commit | Line | Data |
80ae061a |
1 | package SQL::Translator::Parser::DBI::PostgreSQL; |
2 | |
3 | # ------------------------------------------------------------------- |
7ed7402c |
4 | # $Id: PostgreSQL.pm,v 1.10 2007-06-04 04:01:14 mwz444 Exp $ |
80ae061a |
5 | # ------------------------------------------------------------------- |
90075866 |
6 | # Copyright (C) 2002-4 SQLFairy Authors |
80ae061a |
7 | # |
8 | # This program is free software; you can redistribute it and/or |
9 | # modify it under the terms of the GNU General Public License as |
10 | # published by the Free Software Foundation; version 2. |
11 | # |
12 | # This program is distributed in the hope that it will be useful, but |
13 | # WITHOUT ANY WARRANTY; without even the implied warranty of |
14 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
15 | # General Public License for more details. |
16 | # |
17 | # You should have received a copy of the GNU General Public License |
18 | # along with this program; if not, write to the Free Software |
19 | # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
20 | # 02111-1307 USA |
21 | # ------------------------------------------------------------------- |
22 | |
23 | =head1 NAME |
24 | |
25 | SQL::Translator::Parser::DBI::PostgreSQL - parser for DBD::Pg |
26 | |
27 | =head1 SYNOPSIS |
28 | |
29 | See SQL::Translator::Parser::DBI. |
30 | |
31 | =head1 DESCRIPTION |
32 | |
d2522b19 |
33 | Uses DBI to query PostgreSQL system tables to determine schema structure. |
80ae061a |
34 | |
35 | =cut |
36 | |
37 | use strict; |
38 | use DBI; |
80ae061a |
39 | use Data::Dumper; |
40 | use SQL::Translator::Schema::Constants; |
41 | |
42 | use vars qw[ $DEBUG $VERSION @EXPORT_OK ]; |
7ed7402c |
43 | $VERSION = sprintf "%d.%02d", q$Revision: 1.10 $ =~ /(\d+)\.(\d+)/; |
80ae061a |
44 | $DEBUG = 0 unless defined $DEBUG; |
45 | |
46 | # ------------------------------------------------------------------- |
47 | sub parse { |
48 | my ( $tr, $dbh ) = @_; |
49 | |
638e82c2 |
50 | my $schema = $tr->schema; |
80ae061a |
51 | |
d2522b19 |
52 | my $column_select = $dbh->prepare( |
53 | "SELECT a.attname, t.typname, a.attnum,a.atttypmod as length, |
54 | a.attnotnull, a.atthasdef, d.adsrc |
55 | FROM pg_type t,pg_attribute a |
56 | LEFT JOIN pg_attrdef d ON (d.adrelid = a.attrelid AND a.attnum = d.adnum) |
57 | WHERE a.attrelid=? AND attnum>0 |
58 | AND a.atttypid=t.oid |
59 | ORDER BY a.attnum" |
60 | ); |
61 | |
62 | my $index_select = $dbh->prepare( |
63 | "SELECT oid, c.relname, i.indkey, i.indnatts, i.indisunique, |
64 | i.indisprimary, pg_get_indexdef(oid) AS create_string |
65 | FROM pg_class c,pg_index i |
66 | WHERE c.relnamespace=2200 AND c.relkind='i' |
67 | AND c.oid=i.indexrelid AND i.indrelid=?" |
68 | ); |
69 | |
70 | my $table_select = $dbh->prepare( |
71 | "SELECT oid,relname FROM pg_class WHERE relnamespace IN |
72 | (SELECT oid FROM pg_namespace WHERE nspname='public') |
73 | AND relkind='r';" |
74 | ); |
75 | $table_select->execute(); |
76 | |
77 | while ( my $tablehash = $table_select->fetchrow_hashref ) { |
78 | |
79 | my $table_name = $$tablehash{'relname'}; |
80 | my $table_oid = $$tablehash{'oid'}; |
81 | |
82 | my $table = $schema->add_table( |
83 | name => $table_name, |
84 | #what is type? type => $table_info->{TABLE_TYPE}, |
638e82c2 |
85 | ) || die $schema->error; |
80ae061a |
86 | |
d2522b19 |
87 | $column_select->execute($table_oid); |
88 | |
89 | while (my $columnhash = $column_select->fetchrow_hashref ) { |
638e82c2 |
90 | |
31f10179 |
91 | #data_type seems to not be populated; perhaps there needs to |
92 | #be a mapping of query output to reserved constants in sqlt? |
93 | |
94 | my $col = $table->add_field( |
d2522b19 |
95 | name => $$columnhash{'attname'}, |
96 | default_value => $$columnhash{'adsrc'}, |
7ed7402c |
97 | data_type => $$columnhash{'typname'}, |
d2522b19 |
98 | order => $$columnhash{'attnum'}, |
d2522b19 |
99 | ) || die $table->error; |
31f10179 |
100 | |
101 | $col->{size} = [$$columnhash{'length'}] if $$columnhash{'length'}>0; |
7ed7402c |
102 | $col->{is_nullable} = $$columnhash{'attnotnull'} ? 0 : 1; |
d2522b19 |
103 | } |
638e82c2 |
104 | |
d2522b19 |
105 | $index_select->execute($table_oid); |
106 | |
107 | my @column_names = $table->field_names(); |
108 | while (my $indexhash = $index_select->fetchrow_hashref ) { |
109 | #don't deal with function indexes at the moment |
110 | next if ($$indexhash{'indkey'} eq '' |
111 | or !defined($$indexhash{'indkey'}) ); |
112 | |
113 | my $type; |
31f10179 |
114 | if ($$indexhash{'indisprimary'}) { |
115 | $type = UNIQUE; #PRIMARY_KEY; |
116 | |
117 | #tell sqlt that this is the primary key: |
118 | my $col_name=$column_names[($$indexhash{'indkey'} - 1)]; |
119 | $table->get_field($col_name)->{is_primary_key}=1; |
120 | |
121 | } elsif ($$indexhash{'indisunique'}) { |
d2522b19 |
122 | $type = UNIQUE; |
123 | } else { |
124 | $type = NORMAL; |
125 | } |
638e82c2 |
126 | |
d2522b19 |
127 | my @column_ids = split /\s+/, $$indexhash{'indkey'}; |
128 | my @columns; |
129 | foreach my $col (@column_ids) { |
130 | push @columns, $column_names[($col - 1)]; |
638e82c2 |
131 | } |
d2522b19 |
132 | |
133 | $table->add_index( |
134 | name => $$indexhash{'relname'}, |
135 | type => $type, |
31f10179 |
136 | fields => \@columns, |
d2522b19 |
137 | ) || die $table->error; |
638e82c2 |
138 | } |
80ae061a |
139 | } |
140 | |
141 | return 1; |
142 | } |
143 | |
144 | 1; |
145 | |
146 | # ------------------------------------------------------------------- |
13b0b70f |
147 | # Time is a waste of money. |
148 | # Oscar Wilde |
80ae061a |
149 | # ------------------------------------------------------------------- |
150 | |
151 | =pod |
152 | |
153 | =head1 AUTHOR |
154 | |
d2522b19 |
155 | Scott Cain E<lt>cain@cshl.eduE<gt>, previous author: |
13b0b70f |
156 | Paul Harrington E<lt>harringp@deshaw.comE<gt>. |
80ae061a |
157 | |
158 | =head1 SEE ALSO |
159 | |
13b0b70f |
160 | SQL::Translator, DBD::Pg. |
80ae061a |
161 | |
162 | =cut |