# Copyright (c) 2006, 2007 - OpenSLX GmbH
#
# This program is free software distributed under the GPL version 2.
# See http://openslx.org/COPYING
#
# If you have any feedback please consult http://openslx.org/feedback and
# send your suggestions, praise, or complaints to feedback@openslx.org
#
# General information about OpenSLX can be found at http://openslx.org/
# -----------------------------------------------------------------------------
# DBSchema.pm
#    - provides database schema of the OpenSLX config-db.
# -----------------------------------------------------------------------------
package OpenSLX::DBSchema;

use strict;
use warnings;

use OpenSLX::Basics;

################################################################################
### DB-schema definition
###     This hash-ref describes the current OpenSLX configuration database 
###        schema.
###        Each table is defined by a list of column descriptions (and optionally
###        a list of default values).
###     A column description is simply the name of the column followed by ':'
###     followed by the data type description. The following data types are
###     currently supported:
###         b        => boolean (providing the values 1 and 0 only)
###         i        => integer (32-bit, signed)
###         s.20    => string, followed by length argument (in this case: 20)
###         pk        => primary key (integer)
###         fk        => foreign key (integer)
################################################################################

my $VERSION = 0.36;

my $DbSchema = {
    'version' => $VERSION,
    'tables' => {
        'client' => {
            # a client is a PC booting via network
            'cols' => [
                'id:pk',            # primary key
                'name:s.128',       # official name of PC (e.g. as given by sticker
                                    # on case)
                'mac:s.20',         # MAC of NIC used for booting
                'comment:s.1024',   # internal comment (optional, for admins)
            ],
            'vals' => [
                {   # add default client
                    'id'         => 0,
                    'name'       => '<<<default>>>',
                    'comment'    => 'internal client that holds default values',
                },
            ],
        },
        'client_attr' => {
            # attributes of clients
            'cols' => [
                'id:pk',            # primary key
                'client_id:fk',     # foreign key to client
                'name:s.128',       # attribute name
                'value:s.255',      # attribute value
            ],
        },
        'client_system_ref' => {
            # clients referring to the systems they should offer for booting
            'cols' => [
                'client_id:fk',     # foreign key
                'system_id:fk',     # foreign key
            ],
        },
        'export' => {
            # an export describes a vendor-OS "wrapped" in some kind of exporting
            # format (NFS or NBD-squash). This represents the rootfs that the
            # clients will see.
            'cols' => [
                'id:pk',            # primary key
                'name:s.64',        # unique name of export, is automatically
                                    # constructed like this:
                                    #   <vendor-os-name>-<export-type>
                'vendor_os_id:fk',  # foreign key
                'comment:s.1024',   # internal comment (optional, for admins)
                'type:s.10',        # 'nbd', 'nfs', ...
                'server_ip:s.16',   # IP of exporting server, if empty the
                                    # boot server will be used
                'port:i',           # some export types need to use a specific
                                    # port for each incarnation, if that's the
                                    # case you can specify it here
                'uri:s.255',        # path to export (squashfs or NFS-path), if
                                    # empty it will be auto-generated by
                                    # config-demuxer
            ],
        },
        'global_info' => {
            # a home for global counters and other info
            'cols' => [
                'id:s.32',          # key
                'value:s.128',      # value
            ],
            'vals' => [
                {   # add nbd-server-port
                    'id' => 'next-nbd-server-port',
                    'value' => '5000',
                },
            ],
        },
        'groups' => {
            # a group encapsulates a set of clients as one entity, managing
            # a group-specific attribute set. All the different attribute
            # sets a client inherits via group membership are folded into
            # one resulting attribute set with respect to each group's priority.
            'cols' => [
                'id:pk',            # primary key
                'name:s.128',       # name of group
                'priority:i',       # priority, used for order in group-list
                                    # (from 0-highest to 99-lowest)
                'comment:s.1024',   # internal comment (optional, for admins)
            ],
        },
        'group_attr' => {
            # attributes of groups
            'cols' => [
                'id:pk',            # primary key
                'group_id:fk',      # foreign key to group
                'name:s.128',       # attribute name
                'value:s.255',      # attribute value
            ],
        },
        'group_client_ref' => {
            # groups referring to their clients
            'cols' => [
                'group_id:fk',      # foreign key
                'client_id:fk',     # foreign key
            ],
        },
        'group_system_ref' => {
            # groups referring to the systems each of their clients should
            # offer for booting
            'cols' => [
                'group_id:fk',      # foreign key
                'system_id:fk',     # foreign key
            ],
        },
        'installed_plugin' => {
            # holds the plugins that have been installed into a specific 
            # vendor-OS
            'cols' => [
                'id:pk',            # primary key
                'vendor_os_id:fk',  # foreign key
                'plugin_name:s.64', # name of installed plugin
                                    # (e.g. suse-9.3-kde, debian-3.1-ppc,
                                    # suse-10.2-cloned-from-kiwi).
                                    # This is used as the folder name for the
                                    # corresponding stage1, too.
            ],
        },
        'installed_plugin_attr' => {
            # (stage1-)attributes of installed plugins
            'cols' => [
                'id:pk',            # primary key
                'installed_plugin_id:fk',    # foreign key to installed plugin
                'name:s.128',       # attribute name
                'value:s.255',      # attribute value
            ],
        },
        'meta' => {
            # information about the database as such
            'cols' => [
                'plugin_info_hash:s.32',    # hash-value identifying a specific
                                            # set of plugins and their 
                                            # attributes
                'schema_version:s.5',       # schema-version currently 
                                            # implemented by DB
            ],
            'vals' => [
                {
                    'plugin_info_hash' => '',
                    'schema_version'   => $VERSION,
                },
            ],
        },
        'system' => {
            # a system describes one bootable instance of an export, it
            # represents a selectable line in the PXE boot menu of all the
            # clients associated with this system
            'cols' => [
                'id:pk',              # primary key
                'export_id:fk',       # foreign key
                'name:s.64',          # unique name of system, is automatically
                                      # constructed like this:
                                      #   <vendor-os-name>-<export-type>-<kernel>
                'label:s.64',         # name visible to user (pxe-label)
                                      # if empty, this will be autocreated from
                                      # the name
                'kernel:s.128',       # path to kernel file, relative to /boot
                'description:s.512',  # visible description (for PXE TEXT)
                'pxe_prefix_ip:s.16', # ip prefix for PXE Menu entry
                'comment:s.1024',     # internal comment (optional, for admins)
            ],
            'vals' => [
                {   # add default system
                    'id' => 0,
                    'name' => '<<<default>>>',
                    'comment' => 'internal system that holds default values',
                },
            ],
        },
        'system_attr' => {
            # attributes of systems
            'cols' => [
                'id:pk',            # primary key
                'system_id:fk',     # foreign key to system
                'name:s.128',       # attribute name
                'value:s.255',      # attribute value
            ],
        },
        'vendor_os' => {
            # a vendor-OS describes a folder containing an operating system as
            # provided by the vendor (a.k.a. unchanged and thus updatable)
            'cols' => [
                'id:pk',            # primary key
                'name:s.48',        # structured name of OS installation
                                    # (e.g. suse-9.3-kde, debian-3.1-ppc,
                                    # suse-10.2-cloned-from-kiwi).
                                    # This is used as the folder name for the
                                    # corresponding stage1, too.
                'comment:s.1024',   # internal comment (optional, for admins)
                'clone_source:s.255',   # if vendor-OS was cloned, this contains
                                        # the rsync-URI pointing to the original
            ],
        },
    },
};

################################################################################
###
### standard methods
###
################################################################################
sub new
{
    my $class = shift;

    my $self = {
    };

    return bless $self, $class;
}

sub checkAndUpgradeDBSchemaIfNecessary
{
    my $self     = shift;
    my $configDB = shift;

    my $metaDB = $configDB->{'meta-db'};

    vlog(2, "trying to determine schema version...");
    my $currVersion = $metaDB->schemaFetchDBVersion();
    if (!defined $currVersion) {
        # that's bad, someone has messed with our DB: there is a
        # database, but the 'meta'-table is empty. 
        # There might still be data in the other tables, but we have no way to 
        # find out which schema version they're in. So it's safer to give up.
        croak _tr('Could not determine schema version of database');
    }

    if ($currVersion == 0) {
        vlog(1, _tr('Creating DB (schema version: %s)', $DbSchema->{version}));
        foreach my $tableName (keys %{$DbSchema->{tables}}) {
            # create table (optionally inserting default values, too)
            $metaDB->schemaAddTable(
                $tableName,
                $DbSchema->{tables}->{$tableName}->{cols},
                $DbSchema->{tables}->{$tableName}->{vals}
            );
        }
        $metaDB->schemaSetDBVersion($DbSchema->{version});
        $configDB->cleanupAnyInconsistencies() 
            or die _tr('unable to cleanup DB!');
        vlog(1, _tr('DB has been created successfully'));
    } elsif ($currVersion < $DbSchema->{version}) {
        vlog(
            1,
            _tr(
                'Our schema-version is %s, DB is %s, upgrading DB...',
                $DbSchema->{version}, $currVersion
            )
        );
        $self->_schemaUpgradeDBFrom($metaDB, $currVersion);
        $configDB->cleanupAnyInconsistencies() 
            or die _tr('unable to cleanup DB!');
        vlog(1, _tr('upgrade done'));
    } else {
        vlog(1, _tr('DB matches current schema version (%s)', $currVersion));
    }

    return 1;
}

sub getColumnsOfTable
{
    my $self      = shift;
    my $tableName = shift;

    return    
        map { (/^(\w+)\W/) ? $1 : $_; } 
        @{$DbSchema->{tables}->{$tableName}->{cols}};
}

################################################################################
###
### methods for upgrading the DB schema
###
################################################################################
my %DbSchemaHistory;

sub _schemaUpgradeDBFrom
{
    my $self        = shift;
    my $metaDB      = shift;
    my $currVersion = shift;

    foreach my $version (sort { $a <=> $b } keys %DbSchemaHistory) {
        next if $currVersion >= $version;

        vlog(0, "upgrading schema version to $version");
        if ($DbSchemaHistory{$version}->($metaDB)) {
            $metaDB->schemaSetDBVersion($version);
        }
    }

    return 1;
}

%DbSchemaHistory = (
    0.2 => sub {
        my $metaDB = shift;
    
        # move attributes into separate tables ...
        #
        # ... system attributes ...
        $metaDB->schemaAddTable(
            'system_attr', 
            [
                'id:pk',
                'system_id:fk',
                'name:s.128',
                'value:s.255',
            ]
        );
        foreach my $system ($metaDB->fetchSystemByFilter()) {
            my %attrs;
            foreach my $key (keys %$system) {
                next if substr($key, 0, 5) ne 'attr_';
                my $attrValue = $system->{$key} || '';
                next if $system->{id} > 0 && !length($attrValue);
                my $newAttrName = substr($key, 5);
                $attrs{$newAttrName} = $attrValue;
            }
            $metaDB->setSystemAttrs($system->{id}, \%attrs);
        }
        $metaDB->schemaDropColumns(
            'system',
            [
                'attr_automnt_dir',
                'attr_automnt_src',
                'attr_country',
                'attr_dm_allow_shutdown',
                'attr_hw_graphic',
                'attr_hw_local_disk',
                'attr_hw_monitor',
                'attr_hw_mouse',
                'attr_late_dm',
                'attr_netbios_workgroup',
                'attr_nis_domain',
                'attr_nis_servers',
                'attr_ramfs_fsmods',
                'attr_ramfs_miscmods',
                'attr_ramfs_nicmods',
                'attr_ramfs_screen',
                'attr_sane_scanner',
                'attr_scratch',
                'attr_slxgrp',
                'attr_start_alsasound',
                'attr_start_atd',
                'attr_start_cron',
                'attr_start_dreshal',
                'attr_start_ntp',
                'attr_start_nfsv4',
                'attr_start_printer',
                'attr_start_samba',
                'attr_start_snmp',
                'attr_start_sshd',
                'attr_start_syslog',
                'attr_start_x',
                'attr_start_xdmcp',
                'attr_tex_enable',
                'attr_timezone',
                'attr_tvout',
                'attr_vmware',
            ],
            [
                'id:pk',
                'export_id:fk',
                'name:s.64',
                'label:s.64',
                'kernel:s.128',
                'kernel_params:s.512',
                'hidden:b',
                'comment:s.1024',
            ]
        );
        #
        # ... client attributes ...
        $metaDB->schemaAddTable(
            'client_attr',
            [
                'id:pk',
                'client_id:fk',
                'name:s.128',
                'value:s.255',
            ]
        );
        foreach my $client ($metaDB->fetchClientByFilter()) {
            my %attrs;
            foreach my $key (keys %$client) {
                next if substr($key, 0, 5) ne 'attr_';
                my $attrValue = $client->{$key} || '';
                next if !length($attrValue);
                my $newAttrName = substr($key, 5);
                $attrs{$newAttrName} = $attrValue;
            }
            $metaDB->setClientAttrs($client->{id}, \%attrs);
        }
        $metaDB->schemaDropColumns(
            'client',
            [
                'attr_automnt_dir',
                'attr_automnt_src',
                'attr_country',
                'attr_dm_allow_shutdown',
                'attr_hw_graphic',
                'attr_hw_local_disk',
                'attr_hw_monitor',
                'attr_hw_mouse',
                'attr_late_dm',
                'attr_netbios_workgroup',
                'attr_nis_domain',
                'attr_nis_servers',
                'attr_sane_scanner',
                'attr_scratch',
                'attr_slxgrp',
                'attr_start_alsasound',
                'attr_start_atd',
                'attr_start_cron',
                'attr_start_dreshal',
                'attr_start_ntp',
                'attr_start_nfsv4',
                'attr_start_printer',
                'attr_start_samba',
                'attr_start_snmp',
                'attr_start_sshd',
                'attr_start_syslog',
                'attr_start_x',
                'attr_start_xdmcp',
                'attr_tex_enable',
                'attr_timezone',
                'attr_tvout',
                'attr_vmware',
            ],
            [
                'id:pk',
                'name:s.128',
                'mac:s.20',
                'boot_type:s.20',
                'unbootable:b',
                'kernel_params:s.128',
                'comment:s.1024',
            ]
        );
        #
        # ... group attributes ...
        $metaDB->schemaAddTable(
            'group_attr',
            [
                'id:pk',
                'group_id:fk',
                'name:s.128',
                'value:s.255',
            ]
        );
        foreach my $group ($metaDB->fetchGroupByFilter()) {
            my %attrs;
            foreach my $key (keys %$group) {
                next if substr($key, 0, 5) ne 'attr_';
                my $attrValue = $group->{$key} || '';
                next if !length($attrValue);
                my $newAttrName = substr($key, 5);
                $attrs{$newAttrName} = $attrValue;
            }
            $metaDB->setGroupAttrs($group->{id}, \%attrs);
        }
        $metaDB->schemaDropColumns(
            'groups',
            [
                'attr_automnt_dir',
                'attr_automnt_src',
                'attr_country',
                'attr_dm_allow_shutdown',
                'attr_hw_graphic',
                'attr_hw_local_disk',
                'attr_hw_monitor',
                'attr_hw_mouse',
                'attr_late_dm',
                'attr_netbios_workgroup',
                'attr_nis_domain',
                'attr_nis_servers',
                'attr_sane_scanner',
                'attr_scratch',
                'attr_slxgrp',
                'attr_start_alsasound',
                'attr_start_atd',
                'attr_start_cron',
                'attr_start_dreshal',
                'attr_start_ntp',
                'attr_start_nfsv4',
                'attr_start_printer',
                'attr_start_samba',
                'attr_start_snmp',
                'attr_start_sshd',
                'attr_start_syslog',
                'attr_start_x',
                'attr_start_xdmcp',
                'attr_tex_enable',
                'attr_timezone',
                'attr_tvout',
                'attr_vmware',
            ],
            [
                'id:pk',
                'name:s.128',
                'priority:i',
                'comment:s.1024',
            ]
        );
    
        return 1;
    },
    0.21 => sub {
        my $metaDB = shift;
    
        # add new table installed_plugin
        $metaDB->schemaAddTable(
            'installed_plugin', 
            [
                'id:pk',
                'vendor_os_id:fk',
                'plugin_name:s.64',
            ]
        );
    
        return 1;
    },
    0.22 => sub {
        my $metaDB = shift;
    
        # dummy schema change, just to trigger the attribute synchronization
        # into the default system
    
        return 1;
    },
    0.23 => sub {
        my $metaDB = shift;

        # add new column system.description
        $metaDB->schemaAddColumns(
            'system',
            [
                'description:s.512',
            ],
            undef,
            [
                'id:pk',
                'export_id:fk',
                'name:s.64',
                'label:s.64',
                'kernel:s.128',
                'kernel_params:s.512',
                'hidden:b',
                'description:s.512',
                'comment:s.1024',
            ]
        );
    
        return 1;
    },
    0.24 => sub {
        my $metaDB = shift;
    
        # split theme::name into theme::splash, theme::displaymanager and
        # theme::desktop
        foreach my $system ($metaDB->fetchSystemByFilter()) {
            my $attrs = $system->{attrs} || {};
            next if !exists $attrs->{'theme::name'};
            $attrs->{'theme::splash'} 
                = $attrs->{'theme::displaymanager'}
                = $attrs->{'theme::desktop'} 
                = $attrs->{'theme::name'};
            delete $attrs->{'theme::name'};
            $metaDB->setSystemAttrs($system->{id}, $attrs);
        }
    
        # force all plugin names to lowercase
        foreach my $vendorOS ($metaDB->fetchVendorOSByFilter()) {
            my @installedPlugins 
                = $metaDB->fetchInstalledPlugins($vendorOS->{id});
            foreach my $plugin (@installedPlugins) {
                my $pluginName = $plugin->{plugin_name};
                $metaDB->removeInstalledPlugin($vendorOS->{id}, $pluginName);
                $metaDB->addInstalledPlugin($vendorOS->{id}, lc($pluginName));
            }
        }

        return 1;
    },
    0.25 => sub {
        my $metaDB = shift;
    
        # drop attribute ramfs_screen
        $metaDB->removeAttributeByName('ramfs_screen');
    
        return 1;
    },
    0.26 => sub {
        my $metaDB = shift;
    
        # rename all exports and systems that contain a single colon to
        # the current naming scheme with a double colon
        foreach my $system ($metaDB->fetchSystemByFilter()) {
            if ($system->{name} =~ m{^([^:]+):([^:]+)$}) {
                if ($system->{label} eq $system->{name}) {
                    $system->{label} = "${1}::${2}";
                }
                $system->{name} = "${1}::${2}";
                $metaDB->changeSystem([ $system->{id} ], [ $system ]);
            }
        }
        foreach my $export ($metaDB->fetchExportByFilter()) {
            if ($export->{name} =~ m{^([^:]+):([^:]+)$}) {
                $export->{name} = "${1}::${2}";
                $metaDB->changeExport([ $export->{id} ], [ $export ]);
            }
        }
    
        return 1;
    },
    0.27 => sub {
        my $metaDB = shift;
    
        # add default vendor-OS, which holds info about the plugins that shall
        # be automatically installed into all vendor-OS that are being created.
        $metaDB->addVendorOS([{
            id      => '0',
            name    => '<<<default>>>',
            comment => 'holds default plugins for all vendor-OS',
        }]);
    
        return 1;
    },
    0.28 => sub {
        my $metaDB = shift;
    
        # correct effects of implementation error last time around that caused 
        # the default vendor-OS to not have any plugins at all - so we add
        # the default plugins here:
# OLTA: deactivated for good since this does not work anymore with newer
#       implementations (as addInstalledPlugin requires the table 
#       'installed_plugin_attr', which is going to be created in db-schema 
#       version 0.29 (see below)
#        $metaDB->addInstalledPlugin(0, 'theme');
    
        return 1;
    },
    0.29 => sub {
        my $metaDB = shift;
    
        # add new table installed_plugin_attrs
        $metaDB->schemaAddTable(
            'installed_plugin_attr', 
            [
                'id:pk',
                'installed_plugin_id:fk',
                'name:s.128',
                'value:s.255',
            ],
        );
    
        return 1;
    },
    0.30 => sub {
        my $metaDB = shift;
    
        # dummy schema change, just to trigger the attribute synchronization
        # into the default system (required since plugins have been added
        # and removed)
    
        return 1;
    },
    0.31 => sub {
        my $metaDB = shift;
    
        # dummy schema change, just to trigger the attribute synchronization
        # again, as the respective code has been extended
    
        return 1;
    },
    0.32 => sub {
        my $metaDB = shift;
    
        # dummy schema change, just to trigger the attribute synchronization,
        # as the 'theme' plugin has been removed
    
        return 1;
    },
    0.33 => sub {
        my $metaDB = shift;

        # add new column meta.plugin_info_hash
        $metaDB->schemaAddColumns(
            'meta',
            [
                'plugin_info_hash:s.32',
            ],
            undef,
            [
                'plugin_info_hash:s.32',
                'schema_version:s.5',
            ]
        );
    
        return 1;
    },
    0.34 => sub {
        my $metaDB = shift;

        # turn client fields 'boot_type', 'kernel_params' and 'unbootable' 
        # into attributes:
        foreach my $client ($metaDB->fetchClientByFilter()) {
            my $attrs = $metaDB->fetchClientAttrs($client->{id});
            $attrs->{boot_type}            = $client->{boot_type} || 'pxe';
            $attrs->{kernel_params_client} = $client->{kernel_params};
            $attrs->{unbootable}           = $client->{unbootable};
            $metaDB->setClientAttrs($client->{id}, $attrs);
        }
        $metaDB->schemaDropColumns(
            'client',
            [
                'boot_type',
                'kernel_params',
                'unbootable',
            ],
            [
                'id:pk',
                'name:s.128',
                'mac:s.20',
                'comment:s.1024',
            ]
        );
    
        # turn system fields 'hidden' and 'kernel_params' into attributes:
        foreach my $system ($metaDB->fetchSystemByFilter()) {
            my $attrs = $metaDB->fetchSystemAttrs($system->{id});
            $attrs->{hidden}        = $system->{hidden};
            $attrs->{kernel_params} = $system->{kernel_params};
            $metaDB->setSystemAttrs($system->{id}, $attrs);
        }
        $metaDB->schemaDropColumns(
            'system',
            [
                'hidden',
                'kernel_params',
            ],
            [
                'id:pk',
                'export_id:fk',
                'name:s.64',
                'label:s.64',
                'kernel:s.128',
                'description:s.512',
                'comment:s.1024',
            ]
        );
    
        return 1;
    },
    0.35 => sub {
        my $metaDB = shift;

        # add new column system.pxe_prefix_ip
        $metaDB->schemaAddColumns(
            'system',
            [
                'pxe_prefix_ip:s.16',
            ],
            undef
        );
    
        return 1;
    },
    0.36 => sub {
        my $metaDB = shift;

        # value 'preboot-cd' in client-attr 'boot_type' has been changed
        # to 'preboot', and a separate attribute 'preboot_media' has been
        # introduced:
        foreach my $client ($metaDB->fetchClientByFilter()) {
            my $attrs = $metaDB->fetchClientAttrs($client->{id});
            if ($attrs->{boot_type} eq 'preboot-cd') {
                $attrs->{boot_type} = 'preboot';
                $attrs->{preboot_media} = 'cd';
                $metaDB->setClientAttrs($client->{id}, $attrs);
            }
        }
    
        return 1;
    },
);

1;