create indexes on inherited tables

diff --git a/lib/APKPM/Store.pm b/lib/APKPM/Store.pm
index e04fda9..9e5ac67 100644 (file)
--- a/lib/APKPM/Store.pm
+++ b/lib/APKPM/Store.pm
@@ -27,6 +27,42 @@ sub dbh {
 our $redis;
 sub redis { $redis ||= Redis->new };
 
+sub _create_index {
+       my ( $self, $table, $timestamp ) = @_;
+
+       my $sth = $self->dbh->prepare( qq{
+
+select
+    t.relname as table_name,
+    i.relname as index_name,
+    a.attname as column_name
+from
+    pg_class t,
+    pg_class i,
+    pg_index ix,
+    pg_attribute a
+where
+    t.oid = ix.indrelid
+    and i.oid = ix.indexrelid
+    and a.attrelid = t.oid
+    and a.attnum = ANY(ix.indkey)
+    and t.relkind = 'r'
+    and t.relname like ?
+order by
+    t.relname,
+    i.relname;
+
+       } );
+       $sth->execute($table);
+       my $sql = '';
+       while( my $row = $sth->fetchrow_hashref ) {
+               #warn "# _create_index $table ",dump($row);
+               $sql .= qq|create index $row->{index_name}_$timestamp on ${table}_$timestamp($row->{column_name});\n|;
+       }
+
+       return $sql;
+}
+
 sub pg_insert {
        my ( $self, $table, $h ) = @_;
 
@@ -44,6 +80,7 @@ sub pg_insert {
                $create_table = qq{
                        create table $part () inherits ($table) ;
                };
+               $create_table .= $self->_create_index( $table, $timestamp );
                $table = $part;
                warn "# using partition $table";
        }

diff --git a/t/Store.t b/t/Store.t
index 0579f49..02848ee 100755 (executable)
--- a/t/Store.t
+++ b/t/Store.t
@@ -2,7 +2,7 @@
 use strict;
 use warnings;
 
-use Test::More tests => 16;
+use Test::More tests => 17;
 use Data::Dump qw(dump);
 
 use lib 'lib';
@@ -11,6 +11,9 @@ use_ok 'APKPM::Store';
 
 ok my $o = APKPM::Store->new, 'new';
 
+ok my $sql = $o->_create_index('dslam', '2012_04_20'), '_create_index';
+diag $sql;
+
 ok my $r = $o->insert('job',{
 _table => 'ping',
 ip => '127.0.0.1',