r8411@llin: dpavlin | 2005-10-10 14:06:48 +0200

[BackupPC.git] / bin / BackupPC_updatedb

diff --git a/bin/BackupPC_updatedb b/bin/BackupPC_updatedb
index 27b2e54..8336cc0 100755 (executable)
--- a/bin/BackupPC_updatedb
+++ b/bin/BackupPC_updatedb
@@ -270,9 +270,10 @@ if (($opt{i} || $opt{j} || ($index_path && ! -e $index_path)) && !$opt{c}) {
 if ($opt{c}) {
        sub do_index {
                my $index = shift || return;
 if ($opt{c}) {
        sub do_index {
                my $index = shift || return;

-               my ($table,$col,$unique) = split(/_/, $index);
+               my ($table,$col,$unique) = split(/:/, $index);

                $unique ||= '';
                $unique ||= '';

-               $index =~ s/,/_/g;
+               $index =~ s/\W+/_/g;
+               print "$index on $table($col)" . ( $unique ? "u" : "" ) . " ";

                $dbh->do(qq{ create $unique index $index on $table($col) });
        }
 
                $dbh->do(qq{ create $unique index $index on $table($col) });
        }
 


@@ -291,8 +292,7 @@ if ($opt{c}) {
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,
                        ID      SERIAL          PRIMARY KEY,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        name    VARCHAR(30)     NOT NULL,

-                       share   VARCHAR(200)    NOT NULL,
-                       localpath VARCHAR(200)      
+                       share   VARCHAR(200)    NOT NULL

                );            
        });
 
                );            
        });
 


@@ -307,41 +307,42 @@ if ($opt{c}) {
        
        $dbh->do(qq{
                create table backups (
        
        $dbh->do(qq{
                create table backups (

+                       id      serial,

                        hostID  INTEGER         NOT NULL references hosts(id),
                        num     INTEGER         NOT NULL,
                        date    integer         NOT NULL, 
                        type    CHAR(4)         not null,
                        shareID integer         not null references shares(id),
                        size    bigint          not null,
                        hostID  INTEGER         NOT NULL references hosts(id),
                        num     INTEGER         NOT NULL,
                        date    integer         NOT NULL, 
                        type    CHAR(4)         not null,
                        shareID integer         not null references shares(id),
                        size    bigint          not null,

-                       PRIMARY KEY(hostID, num, shareID) 
+                       inc_size bigint         not null default -1,
+                       inc_deleted boolean     default false,
+                       PRIMARY KEY(id)

                );            
        });
 
                );            
        });
 

-       #do_index('backups_hostid,num_unique');
-
-

        $dbh->do(qq{     
                create table files (
        $dbh->do(qq{     
                create table files (

-                       ID      SERIAL          PRIMARY KEY,  
-                       shareID INTEGER         NOT NULL references shares(id),
-                       backupNum  INTEGER      NOT NULL,
-                       name       VARCHAR(255) NOT NULL,
-                       path       VARCHAR(255) NOT NULL,
-                       date       integer      NOT NULL,
-                       type       INTEGER      NOT NULL,
-                       size       INTEGER      NOT NULL
+                       ID              SERIAL,
+                       shareID         INTEGER NOT NULL references shares(id),
+                       backupNum       INTEGER NOT NULL,
+                       name            VARCHAR(255) NOT NULL,
+                       path            VARCHAR(255) NOT NULL,
+                       date            integer NOT NULL,
+                       type            INTEGER NOT NULL,
+                       size            bigint  NOT NULL,
+                       primary key(id)

                );
        });
 
 
        $dbh->do( qq{
                );
        });
 
 
        $dbh->do( qq{

-               create table archive
-               (
-                       id                      int not null,
+               create table archive (
+                       id              serial,

                        dvd_nr          int not null,
                        dvd_nr          int not null,

+                       total_size      bigint default -1,

                        note            text,
                        username        varchar(20) not null,
                        note            text,
                        username        varchar(20) not null,

-                       date            timestamp,
+                       date            timestamp default now(),

                        primary key(id)
                );      
        }
                        primary key(id)
                );      
        }


@@ -350,100 +351,38 @@ if ($opt{c}) {
        $dbh->do( qq{
                create table archive_backup
                (
        $dbh->do( qq{
                create table archive_backup
                (

-                       archive_id      int not null,
-                       backup_id       int not null,
-                       status          text,
+                       archive_id      int not null references archive(id),
+                       backup_id       int not null references backups(id),

                        primary key(archive_id, backup_id)
                );
        });
 
                        primary key(archive_id, backup_id)
                );
        });
 

-       $dbh->do( qq{
-               create table workflows(
-                       id                      int not null,
-                       step_id         int not null,
-                       start           timestamp,
-                       stop            timestamp,
-                       username        varchar(20),
-                       archive_id      int not null,
-                       running         boolean default true,
-                       primary key(id)
-               );
-       });
-
-       $dbh->do( qq{
-               create table workflow_step
-               (
-                       step_id         int not null,
-                       code            text,
-                       next_step       int,
-                       stop            boolean default false,
-                       primary key(step_id)
-               );
-       });
-
-       $dbh->do( qq{
-                       alter table workflow_step
-                               add constraint fk_workflow_next_step
-                               foreign key(next_step)
-                               references workflow_step(step_id);
-       });
-
-       $dbh->do( qq{
-               alter table workflows
-                       add constraint fk_workflows_step_id
-                       foreign key(step_id)
-                       references workflow_step(step_id);
-       });
-       
-       $dbh->do( qq{
-               alter table workflows
-                       add constraint fk_workflows_archive_id
-                       foreign key(archive_id)
-                       references archive(id); 
-       });
-
-       $dbh->do( qq{
-               create table workflow_log
-               (
-                       workflow_id             int not null,
-                       step_id                 int not null,
-                       date                    timestamp not null,
-                       status                  text,
-                       primary key(workflow_id, step_id)
-               );
-       });
-
-       $dbh->do( qq{
-               alter table workflow_log
-                       add constraint fk_workflow_log_workflow_id
-                       foreign key (workflow_id)
-                       references workflows(id);
-               });
-       
-       $dbh->do( qq{
-               alter table workflow_log
-                       add constraint fk_workflow_log_step_id
-                       foreign key (step_id)
-                       references      workflow_step(step_id);
-               });
-
-       print "creating indexes:";
+       print "creating indexes: ";

 
        foreach my $index (qw(
 
        foreach my $index (qw(

-               hosts_name
-               backups_hostID
-               backups_num
-               shares_hostID
-               shares_name
-               files_shareID
-               files_path
-               files_name
-               files_date
-               files_size
+               hosts:name
+               backups:hostID
+               backups:num
+               backups:shareID
+               shares:hostID
+               shares:name
+               files:shareID
+               files:path
+               files:name
+               files:date
+               files:size
+               archive:dvd_nr

        )) {
        )) {

-               print " $index";

                do_index($index);
        }
                do_index($index);
        }

+
+       print " creating sequence: ";
+       foreach my $seq (qw/dvd_nr/) {
+               print "$seq ";
+               $dbh->do( qq{ CREATE SEQUENCE $seq } );
+       }
+
+

        print "...\n";
 
        $dbh->commit;
        print "...\n";
 
        $dbh->commit;


@@ -604,14 +543,14 @@ sub getShareID() {
 
        $sth->{insert_share} ||= $dbh->prepare(qq{
                INSERT INTO shares 
 
        $sth->{insert_share} ||= $dbh->prepare(qq{
                INSERT INTO shares 

-                       (hostID,name,share,localpath) 
-               VALUES (?,?,?,?)
+                       (hostID,name,share) 
+               VALUES (?,?,?)

        });
 
        my $drop_down = $hostname . '/' . $share;
        $drop_down =~ s#//+#/#g;
 
        });
 
        my $drop_down = $hostname . '/' . $share;
        $drop_down =~ s#//+#/#g;
 

-       $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
+       $sth->{insert_share}->execute($hostID,$share, $drop_down);

        return $dbh->last_insert_id(undef,undef,'shares',undef);
 }
 
        return $dbh->last_insert_id(undef,undef,'shares',undef);
 }