Ravada.pm

package Ravada;

use warnings;
use strict;

our $VERSION = '0.4.0';

use Carp qw(carp croak);
use Data::Dumper;
use DBIx::Connector;
use File::Copy;
use Hash::Util qw(lock_hash);
use JSON::XS;
use Moose;
use POSIX qw(WNOHANG);
use Time::HiRes qw(gettimeofday tv_interval);
use YAML;

use Socket qw( inet_aton inet_ntoa );

no warnings "experimental::signatures";
use feature qw(signatures);

use Ravada::Auth;
use Ravada::Request;
use Ravada::Repository::ISO;
use Ravada::VM::Void;

our %VALID_VM;
our %ERROR_VM;

eval {
    require Ravada::VM::KVM and do {
        Ravada::VM::KVM->import;
    };
    $VALID_VM{KVM} = 1;
};
$ERROR_VM{KVM} = $@;

eval {
    require Ravada::VM::Void and do {
        Ravada::VM::Void->import;
    };
    $VALID_VM{Void} = 1;
};
$ERROR_VM{Void} = $@;

no warnings "experimental::signatures";
use feature qw(signatures);

our %VALID_CONFIG = (
    vm => undef
    ,warn_error => undef
    ,db => {user => undef, password => undef,  hostname => undef}
    ,ldap => { admin_user => { dn => undef, password => undef }
        ,filter => undef
        ,base => undef
        ,auth => undef
        ,admin_group => undef
        ,ravada_posix_group => undef
    }
);

=head1 NAME

Ravada - Remove Virtual Desktop Manager

=head1 SYNOPSIS

  use Ravada;

  my $ravada = Ravada->new()

=cut


our $FILE_CONFIG = "/etc/ravada.conf";
$FILE_CONFIG = undef if ! -e $FILE_CONFIG;

###########################################################################

our $CONNECTOR;
our $CONFIG = {};
our $DEBUG;
our $VERBOSE;
our $CAN_FORK = 1;
our $CAN_LXC = 0;

# Seconds to wait for other long process
our $SECONDS_WAIT_CHILDREN = 5;

our $DIR_SQL = "sql/mysql";
$DIR_SQL = "/usr/share/doc/ravada/sql/mysql" if ! -e $DIR_SQL;

our $USER_DAEMON;
our $USER_DAEMON_NAME = 'daemon';

has 'connector' => (
        is => 'rw'
);

has 'config' => (
    is => 'ro'
    ,isa => 'Str'
);

has 'warn_error' => (
    is => 'rw'
    ,isa => 'Bool'
    ,default => sub { 1 }
);

=head2 BUILD

Internal constructor

=cut


sub BUILD {
    my $self = shift;
    if ($self->config()) {
        _init_config($self->config);
    } else {
        _init_config($FILE_CONFIG) if $FILE_CONFIG && -e $FILE_CONFIG;
    }

    if ( $self->connector ) {
        $CONNECTOR = $self->connector
    } else {
        $CONNECTOR = $self->_connect_dbh();
        $self->connector($CONNECTOR);
    }
    Ravada::Auth::init($CONFIG);

}

sub _install($self) {
    $self->_create_tables();
    $self->_upgrade_tables();
    $self->_update_data();
    $self->_init_user_daemon();
}

sub _init_user_daemon {
    my $self = shift;
    return if $USER_DAEMON;

    $USER_DAEMON = Ravada::Auth::SQL->new(name => $USER_DAEMON_NAME);
    if (!$USER_DAEMON->id) {
        $USER_DAEMON = Ravada::Auth::SQL::add_user(
            name => $USER_DAEMON_NAME,
            is_admin => 1
        );
        $USER_DAEMON = Ravada::Auth::SQL->new(name => $USER_DAEMON_NAME);
    }

}
sub _update_user_grants {
    my $self = shift;
    $self->_init_user_daemon();
    my $sth = $CONNECTOR->dbh->prepare("SELECT id FROM users");
    my $id;
    $sth->execute;
    $sth->bind_columns(\$id);
    while ($sth->fetch) {
        my $user = Ravada::Auth::SQL->search_by_id($id);
        next if $user->name() eq $USER_DAEMON_NAME;

        next if $user->grants();
        $USER_DAEMON->grant_user_permissions($user);
        $USER_DAEMON->grant_admin_permissions($user)    if $user->is_admin;
    }
    $sth->finish;
}

sub _update_isos {
    my $self = shift;
    my $table = 'iso_images';
    my $field = 'name';
    my %data = (
        mate_artful => {
                    name => 'Ubuntu Mate Artful'
            ,description => 'Ubuntu Mate 17.10.1 (Artful) 64 bits'
                   ,arch => 'amd64'
                    ,xml => 'yakkety64-amd64.xml'
             ,xml_volume => 'yakkety64-volume.xml'
                    ,url => 'http://cdimage.ubuntu.com/ubuntu-mate/releases/17.10.*/release/ubuntu-mate-17.10.*-desktop-amd64.iso'
                ,md5_url => '$url/MD5SUMS'
                ,min_disk_size => '10'
        },
        mate_bionic => {
                    name => 'Ubuntu Mate Bionic 64 bits'
            ,description => 'Ubuntu Mate 18.04 (Bionic Beaver) 64 bits'
                   ,arch => 'amd64'
                    ,xml => 'bionic-amd64.xml'
             ,xml_volume => 'bionic64-volume.xml'
                    ,url => 'http://cdimage.ubuntu.com/ubuntu-mate/releases/18.04.*/release/ubuntu-mate-18.04.*-desktop-amd64.iso'
                ,md5_url => '$url/MD5SUMS'
        },
        mate_bionic_i386 => {
                    name => 'Ubuntu Mate Bionic 32 bits'
            ,description => 'Ubuntu Mate 18.04 (Bionic Beaver) 32 bits'
                   ,arch => 'i386'
                    ,xml => 'bionic-i386.xml'
             ,xml_volume => 'bionic32-volume.xml'
                    ,url => 'http://cdimage.ubuntu.com/ubuntu-mate/releases/18.04.*/release/ubuntu-mate-18.04.*-desktop-i386.iso'
                ,md5_url => '$url/MD5SUMS'
        },

        mate_xenial => {
                    name => 'Ubuntu Mate Xenial'
            ,description => 'Ubuntu Mate 16.04.3 (Xenial) 64 bits'
                   ,arch => 'amd64'
                    ,xml => 'yakkety64-amd64.xml'
             ,xml_volume => 'yakkety64-volume.xml'
                    ,url => 'http://cdimage.ubuntu.com/ubuntu-mate/releases/16.04.*/release/ubuntu-mate-16.04.*-desktop-amd64.iso'
                ,md5_url => '$url/MD5SUMS'
                ,min_disk_size => '10'
        },
        ,bionic=> {
                    name => 'Ubuntu Bionic Beaver'
            ,description => 'Ubuntu 18.04 Bionic Beaver 64 bits'
                   ,arch => 'amd64'
                    ,xml => 'bionic-amd64.xml'
             ,xml_volume => 'bionic64-volume.xml'
                    ,url => 'http://releases.ubuntu.com/18.04/'
                ,file_re => '^ubuntu-18.04.*desktop-amd64.iso'
                ,md5_url => '$url/MD5SUMS'
          ,min_disk_size => '9'
        }

        ,zesty => {
                    name => 'Ubuntu Zesty Zapus'
            ,description => 'Ubuntu 17.04 Zesty Zapus 64 bits'
                   ,arch => 'amd64'
                    ,xml => 'yakkety64-amd64.xml'
             ,xml_volume => 'yakkety64-volume.xml'
                    ,url => 'http://releases.ubuntu.com/17.04/'
                ,file_re => 'ubuntu-17.04.*desktop-amd64.iso'
                ,md5_url => 'http://releases.ubuntu.com/17.04/MD5SUMS'
                ,min_disk_size => '10'
        }
        ,serena64 => {
            name => 'Mint 18.1 Mate 64 bits'
    ,description => 'Mint Serena 18.1 with Mate Desktop based on Ubuntu Xenial 64 bits'
           ,arch => 'amd64'
            ,xml => 'xenial64-amd64.xml'
     ,xml_volume => 'xenial64-volume.xml'
            ,url => 'http://mirrors.evowise.com/linuxmint/stable/18.1/'
        ,file_re => 'linuxmint-18.1-mate-64bit.iso'
        ,md5_url => ''
            ,md5 => 'c5cf5c5d568e2dfeaf705cfa82996d93'
            ,min_disk_size => '10'

        }
        ,mint19_64 => {
            name => 'Mint 19 Mate 64 bits'
    ,description => 'Mint Tara 19 with Mate Desktop 64 bits'
           ,arch => 'amd64'
            ,xml => 'xenial64-amd64.xml'
     ,xml_volume => 'xenial64-volume.xml'
            ,url => 'http://ftp.cixug.es/mint/linuxmint.com/stable/19/'
        ,file_re => 'linuxmint-19-mate-64bit-v2.iso'
        ,sha256_url => 'http://ftp.cixug.es/mint/linuxmint.com/stable/19/sha256sum.txt'
            ,min_disk_size => '15'
        }
        ,mint19_32 => {
            name => 'Mint 19 Mate 32 bits'
    ,description => 'Mint Tara 19 with Mate Desktop 32 bits'
           ,arch => 'i386'
            ,xml => 'mint19-i386.xml'
     ,xml_volume => 'mint19_32-volume.xml'
            ,url => 'http://ftp.cixug.es/mint/linuxmint.com/stable/19/'
        ,file_re => 'linuxmint-19-mate-32bit.iso'
     ,sha256_url => 'http://ftp.cixug.es/mint/linuxmint.com/stable/19/sha256sum.txt'
            ,min_disk_size => '15'
        }
        ,alpine381_64 => {
            name => 'Alpine 3.8 64 bits'
    ,description => 'Alpine Linux 3.8 64 bits ( Minimal Linux Distribution )'
           ,arch => 'amd64'
            ,xml => 'alpine-amd64.xml'
     ,xml_volume => 'alpine381_64-volume.xml'
            ,url => 'http://dl-cdn.alpinelinux.org/alpine/v3.8/releases/x86_64/'
        ,file_re => 'alpine-standard-3.8.1-x86_64.iso'
        ,sha256_url => 'http://dl-cdn.alpinelinux.org/alpine/v3.8/releases/x86_64/alpine-standard-3.8.1-x86_64.iso.sha256'
            ,min_disk_size => '1'
        }
        ,alpine381_32 => {
            name => 'Alpine 3.8 32 bits'
    ,description => 'Alpine Linux 3.8 32 bits ( Minimal Linux Distribution )'
           ,arch => 'i386'
            ,xml => 'alpine-i386.xml'
     ,xml_volume => 'alpine381_32-volume.xml'
            ,url => 'http://dl-cdn.alpinelinux.org/alpine/v3.8/releases/x86/'
        ,file_re => 'alpine-standard-3.8.1-x86.iso'
        ,sha256_url => 'http://dl-cdn.alpinelinux.org/alpine/v3.8/releases/x86/alpine-standard-3.8.1-x86.iso.sha256'
            ,min_disk_size => '1'
        }
        ,fedora_27 => {
            name => 'Fedora 27'
            ,description => 'RedHat Fedora 27 Workstation 64 bits'
            ,url => 'http://ftp.halifax.rwth-aachen.de/fedora/linux/releases/27/Workstation/x86_64/iso/Fedora-Workstation-netinst-x86_64-27-.*\.iso'
            ,arch => 'amd64'
            ,xml => 'xenial64-amd64.xml'
            ,xml_volume => 'xenial64-volume.xml'
            ,sha256_url => '$url/Fedora-Workstation-27-.*-x86_64-CHECKSUM'
            ,min_disk_size => '10'
        }
        ,fedora_28 => {
            name => 'Fedora 28'
            ,description => 'RedHat Fedora 28 Workstation 64 bits'
            ,url => 'http://ftp.halifax.rwth-aachen.de/fedora/linux/releases/28/Workstation/x86_64/iso/Fedora-Workstation-netinst-x86_64-28-.*\.iso'
            ,arch => 'amd64'
            ,xml => 'xenial64-amd64.xml'
            ,xml_volume => 'xenial64-volume.xml'
            ,sha256_url => 'http://fedora.mirrors.ovh.net/linux/releases/28/Workstation/x86_64/iso/Fedora-Workstation-28-.*-x86_64-CHECKSUM'
            ,min_disk_size => '10'
        }
        ,kubuntu_64 => {
            name => 'Kubuntu Bionic Beaver 64 bits'
            ,description => 'Kubuntu 18.04 Bionic Beaver 64 bits'
            ,arch => 'amd64'
            ,xml => 'bionic-amd64.xml'
            ,xml_volume => 'bionic64-volume.xml'
            ,md5_url => '$url/MD5SUMS'
            ,url => 'http://cdimage.ubuntu.com/kubuntu/releases/18.04/release/'
            ,file_re => 'kubuntu-18.04-desktop-amd64.iso'
            ,rename_file => 'kubuntu_bionic_64.iso'
        }
        ,kubuntu_32 => {
            name => 'Kubuntu Bionic Beaver 32 bits'
            ,description => 'Kubuntu 18.04 Bionic Beaver 32 bits'
            ,arch => 'i386'
            ,xml => 'bionic-i386.xml'
            ,xml_volume => 'bionic32-volume.xml'
            ,md5_url => '$url/MD5SUMS'
            ,url => 'http://cdimage.ubuntu.com/kubuntu/releases/18.04/release/'
            ,file_re => 'kubuntu-18.04-desktop-i386.iso'
            ,rename_file => 'kubuntu_bionic_32.iso'
        }
        ,suse_15 => {
            name => "openSUSE Leap 15"
            ,description => "openSUSE Leap 15 64 bits"
            ,arch => 'amd64'
            ,xml => 'bionic-amd64.xml'
            ,xml_volume => 'bionic64-volume.xml'
            ,url => 'https://download.opensuse.org/distribution/leap/15.0/iso/'
            ,sha256_url => '$url/openSUSE-Leap-15.\d+-NET-x86_64.iso.sha256'
            ,file_re => 'openSUSE-Leap-15.\d+-NET-x86_64.iso'

        }
        ,xubuntu_beaver_64 => {
            name => 'Xubuntu Bionic Beaver 64 bits'
            ,description => 'Xubuntu 18.04 Bionic Beaver 64 bits'
            ,arch => 'amd64'
            ,xml => 'bionic-amd64.xml'
            ,xml_volume => 'bionic64-volume.xml'
            ,md5_url => '$url/../MD5SUMS'
            ,url => 'http://archive.ubuntu.com/ubuntu/dists/bionic/main/installer-amd64/current/images/netboot/'
            ,file_re => 'mini.iso'
            ,rename_file => 'xubuntu_bionic_64.iso'
        }
        ,xubuntu_beaver_32 => {
            name => 'Xubuntu Bionic Beaver 32 bits'
            ,description => 'Xubuntu 18.04 Bionic Beaver 32 bits'
            ,arch => 'amd64'
            ,xml => 'bionic-i386.xml'
            ,xml_volume => 'bionic32-volume.xml'
            ,md5_url => '$url/../MD5SUMS'
            ,url => 'http://archive.ubuntu.com/ubuntu/dists/bionic/main/installer-i386/current/images/netboot/'
            ,file_re => 'mini.iso'
            ,rename_file => 'xubuntu_bionic_32.iso'
        }
        ,xubuntu_artful => {
            name => 'Xubuntu Artful Aardvark'
            ,description => 'Xubuntu 17.10 Artful Aardvark 64 bits'
            ,arch => 'amd64'
            ,xml => 'yakkety64-amd64.xml'
            ,xml_volume => 'yakkety64-volume.xml'
            ,md5_url => '$url/../MD5SUMS'
            ,url => 'http://archive.ubuntu.com/ubuntu/dists/artful/main/installer-amd64/current/images/netboot/'
            ,file_re => 'mini.iso'
            ,rename_file => 'xubuntu_artful.iso'
            ,min_disk_size => '10'
        }
        ,xubuntu_zesty => {
            name => 'Xubuntu Zesty Zapus'
            ,description => 'Xubuntu 17.04 Zesty Zapus 64 bits'
            ,arch => 'amd64'
            ,xml => 'yakkety64-amd64.xml'
            ,xml_volume => 'yakkety64-volume.xml'
            ,md5_url => '$url/../MD5SUMS'
            ,url => 'http://archive.ubuntu.com/ubuntu/dists/zesty/main/installer-amd64/current/images/netboot'
            ,file_re => 'mini.iso'
            ,rename_file => 'xubuntu_zesty_mini.iso'
            ,min_disk_size => '10'
        }
        ,xubuntu_xenial => {
            name => 'Xubuntu Xenial Xerus'
            ,description => 'Xubuntu 16.04 Xenial Xerus 64 bits (LTS)'
            ,url => 'http://archive.ubuntu.com/ubuntu/dists/xenial/main/installer-amd64/current/images/netboot/mini.iso'
           ,xml => 'yakkety64-amd64.xml'
            ,xml_volume => 'yakkety64-volume.xml'
            ,md5 => 'fe495d34188a9568c8d166efc5898d22'
            ,rename_file => 'xubuntu_xenial_mini.iso'
            ,min_disk_size => '10'
        }
        ,lubuntu_bionic_64 => {
             name => 'Lubuntu Bionic Beaver 64 bits'
             ,description => 'Lubuntu 18.04 Bionic Beaver 64 bits'
             ,url => 'http://cdimage.ubuntu.com/lubuntu/releases/18.04.*/release/lubuntu-18.04.*-desktop-amd64.iso'
             ,md5_url => '$url/MD5SUMS'
             ,xml => 'bionic-amd64.xml'
             ,xml_volume => 'bionic64-volume.xml'
         }
         ,lubuntu_bionic_32 => {
             name => 'Lubuntu Bionic Beaver 32 bits'
             ,description => 'Lubuntu 18.04 Bionic Beaver 32 bits'
             ,arch => 'i386'
             ,url => 'http://cdimage.ubuntu.com/lubuntu/releases/18.04.*/release/lubuntu-18.04.*-desktop-i386.iso'
             ,md5_url => '$url/MD5SUMS'
             ,xml => 'bionic-i386.xml'
             ,xml_volume => 'bionic32-volume.xml'
        }
        ,lubuntu_xenial => {
            name => 'Lubuntu Xenial Xerus'
            ,description => 'Xubuntu 16.04 Xenial Xerus 64 bits (LTS)'
            ,url => 'http://cdimage.ubuntu.com/lubuntu/releases/16.04.*/release/'
            ,file_re => 'lubuntu-16.04.*-desktop-amd64.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'yakkety64-amd64.xml'
            ,xml_volume => 'yakkety64-volume.xml'
            ,min_disk_size => '10'
        }
        ,debian_jessie_32 => {
            name =>'Debian Jessie 32 bits'
            ,description => 'Debian 8 Jessie 32 bits'
            ,url => 'http://cdimage.debian.org/cdimage/archive/^8\..*/i386/iso-cd/'
            ,file_re => 'debian-8.[\d\.]+-i386-xfce-CD-1.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-i386.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
        }
        ,debian_jessie_64 => {
            name =>'Debian Jessie 64 bits'
            ,description => 'Debian 8 Jessie 64 bits'
            ,url => 'http://cdimage.debian.org/cdimage/archive/^8\..*/amd64/iso-cd/'
            ,file_re => 'debian-8.[\d\.]+-amd64-xfce-CD-1.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
        }
        ,debian_stretch => {
            name =>'Debian Stretch 64 bits'
            ,description => 'Debian 9 Stretch 64 bits (XFCE desktop)'
            ,url => 'https://cdimage.debian.org/debian-cd/^9\..*/amd64/iso-cd/'
            ,file_re => 'debian-9.[\d\.]+-amd64-xfce-CD-1.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
        }
        ,windows_7 => {
          name => 'Windows 7'
          ,description => 'Windows 7 64 bits. Requires an user provided ISO image.'
            .'<a target="_blank" href="http://ravada.readthedocs.io/en/latest/docs/new_iso_image.html">[help]</a>'
          ,xml => 'windows_7.xml'
          ,xml_volume => 'wisuvolume.xml'
          ,min_disk_size => '21'
        }
        ,windows_10 => {
          name => 'Windows 10'
          ,description => 'Windows 10 64 bits. Requires an user provided ISO image.'
          .'<a target="_blank" href="http://ravada.readthedocs.io/en/latest/docs/new_iso_image.html">[help]</a>'
          ,xml => 'windows_10.xml'
          ,xml_volume => 'windows10-volume.xml'
          ,min_disk_size => '21'
        }
        ,windows_xp => {
          name => 'Windows XP'
          ,description => 'Windows XP 64 bits. Requires an user provided ISO image.'
          .'<a target="_blank" href="http://ravada.readthedocs.io/en/latest/docs/new_iso_image.html">[help]</a>'
          ,xml => 'windows_xp.xml'
          ,xml_volume => 'wisuvolume.xml'
          ,min_disk_size => '3'
        }
        ,windows_12 => {
          name => 'Windows 2012'
          ,description => 'Windows 2012 64 bits. Requires an user provided ISO image.'
          .'<a target="_blank" href="http://ravada.readthedocs.io/en/latest/docs/new_iso_image.html">[help]</a>'
          ,xml => 'windows_12.xml'
          ,xml_volume => 'wisuvolume.xml'
          ,min_disk_size => '21'
        }
        ,windows_8 => {
          name => 'Windows 8.1'
          ,description => 'Windows 8.1 64 bits. Requires an user provided ISO image.'
          .'<a target="_blank" href="http://ravada.readthedocs.io/en/latest/docs/new_iso_image.html">[help]</a>'
          ,xml => 'windows_8.xml'
          ,xml_volume => 'wisuvolume.xml'
          ,min_disk_size => '21'
        }
       ,empty_32bits => {
          name => 'Empty Machine 32 bits'
          ,description => 'Empty Machine 32 bits Boot PXE'
          ,xml => 'empty-i386.xml'
          ,xml_volume => 'jessie-volume.xml'
          ,min_disk_size => '0'
        }
       ,empty_64bits => {
          name => 'Empty Machine 64 bits'
          ,description => 'Empty Machine 64 bits Boot PXE'
          ,xml => 'empty-amd64.xml'
          ,xml_volume => 'jessie-volume.xml'
          ,min_disk_size => '0'
        }
    );

    $self->_update_table($table, $field, \%data);

}

sub _update_domain_drivers_types($self) {

    my $data = {
        image => {
            id => 4,
            ,name => 'image'
           ,description => 'Graphics Options'
           ,vm => 'KVM'
        },
        jpeg => {
            id => 5,
            ,name => 'jpeg'
           ,description => 'Graphics Options'
           ,vm => 'KVM'
        },
        zlib => {
            id => 6,
            ,name => 'zlib'
           ,description => 'Graphics Options'
           ,vm => 'KVM'
        },
        playback => {
            id => 7,
            ,name => 'playback'
           ,description => 'Graphics Options'
           ,vm => 'KVM'

        },
        streaming => {
            id => 8,
            ,name => 'streaming'
           ,description => 'Graphics Options'
           ,vm => 'KVM'

        }
        ,disk => {
            id => 9
            ,name => 'disk'
            ,vm => 'KVM'
        }

    };
    $self->_update_table('domain_drivers_types','id',$data);

    my $sth = $CONNECTOR->dbh->prepare(
        "UPDATE domain_drivers_types SET vm='KVM' WHERE vm='qemu'"
    );
    $sth->execute;
    $sth->finish;
}

sub _update_domain_drivers_options($self) {

    my $data = {
        qxl => {
            id => 1,
            ,id_driver_type => 1,
            ,name => 'QXL'
           ,value => 'type="qxl" ram="65536" vram="65536" vgamem="16384" heads="1" primary="yes"'
        },
        vmvga => {
            id => 2,
            ,id_driver_type => 1,
            ,name => 'VMVGA'
           ,value => 'type="vmvga" vram="16384" heads="1" primary="yes"'
        },
        cirrus => {
            id => 3,
            ,id_driver_type => 1,
            ,name => 'Cirrus'
           ,value => 'type="cirrus" vram="16384" heads="1" primary="yes"'
        },
        vga => {
            id => 4,
            ,id_driver_type => 1,
            ,name => 'VGA'
           ,value => 'type="vga" vram="16384" heads="1" primary="yes"'
        },
        ich6 => {
            id => 6,
            ,id_driver_type => 2,
            ,name => 'ich6'
           ,value => 'model="ich6"'
        },
        ac97 => {
            id => 7,
            ,id_driver_type => 2,
            ,name => 'ac97'
           ,value => 'model="ac97"'
        },
        virtio => {
            id => 8,
            ,id_driver_type => 3,
            ,name => 'virtio'
           ,value => 'type="virtio"'
        },
        e1000 => {
            id => 9,
            ,id_driver_type => 3,
            ,name => 'e1000'
           ,value => 'type="e1000"'
        },
        rtl8139 => {
            id => 10,
            ,id_driver_type => 3,
            ,name => 'rtl8139'
           ,value => 'type="rtl8139"'
        },
        auto_glz => {
            id => 11,
            ,id_driver_type => 4,
            ,name => 'auto_glz'
           ,value => 'compression="auto_glz"'
        },
        auto_lz => {
            id => 12,
            ,id_driver_type => 4,
            ,name => 'auto_lz'
           ,value => 'compression="auto_lz"'
        },
        quic => {
            id => 13,
            ,id_driver_type => 4,
            ,name => 'quic'
           ,value => 'compression="quic"'
        },
        glz => {
            id => 14,
            ,id_driver_type => 4,
            ,name => 'glz'
           ,value => 'compression="glz"'
        },
        lz => {
            id => 15,
            ,id_driver_type => 4,
            ,name => 'lz'
           ,value => 'compression="lz"'
        },
        off => {
            id => 16,
            ,id_driver_type => 4,
            ,name => 'off'
           ,value => 'compression="off"'
        },
        auto => {
            id => 17,
            ,id_driver_type => 5,
            ,name => 'auto'
           ,value => 'compression="auto"'
        },
        never => {
            id => 18,
            ,id_driver_type => 5,
            ,name => 'never'
           ,value => 'compression="never"'
        },
        always => {
            id => 19,
            ,id_driver_type => 5,
            ,name => 'always'
           ,value => 'compression="always"'
        },
        auto1 => {
            id => 20,
            ,id_driver_type => 6,
            ,name => 'auto'
           ,value => 'compression="auto"'
        },
        never1 => {
            id => 21,
            ,id_driver_type => 6,
            ,name => 'never'
           ,value => 'compression="never"'
        },
        always1 => {
            id => 22,
            ,id_driver_type => 6,
            ,name => 'always'
           ,value => 'compression="always"'
        },
        on => {
            id => 23,
            ,id_driver_type => 7,
            ,name => 'on'
           ,value => 'compression="on"'
        },
        off1 => {
            id => 24,
            ,id_driver_type => 7,
            ,name => 'off'
           ,value => 'compression="off"'
        },
        filter => {
            id => 25,
            ,id_driver_type => 8,
            ,name => 'filter'
           ,value => 'mode="filter"'
        },
        all => {
            id => 26,
            ,id_driver_type => 8,
            ,name => 'all'
           ,value => 'mode="all"'
        },
        off2 => {
            id => 27,
            ,id_driver_type => 8,
            ,name => 'off'
           ,value => 'mode="off"'
        }
    };
    $self->_update_table('domain_drivers_options','id',$data);
}

sub _update_domain_drivers_options_disk($self) {

    my @options = ('virtio', 'usb','ide', 'sata', 'scsi');

    my $id = 28;
    my %data = map {
        $_ => {
            id => $id++
            ,id_driver_type => 9,
            ,name => $_
            ,value => $_
        }
    } @options;

    $self->_update_table('domain_drivers_options','id',\%data);
}

sub _update_table($self, $table, $field, $data, $verbose=0) {

    my $sth_search = $CONNECTOR->dbh->prepare("SELECT id FROM $table WHERE $field = ?");
    for my $name (sort keys %$data) {
        my $row = $data->{$name};
        $sth_search->execute($row->{$field});
        my ($id) = $sth_search->fetchrow;
        if ( $id ) {
            warn("INFO: $table : $row->{$field} already added.\n") if $verbose;
            next;
        }
        warn("INFO: updating $table : $row->{$field}\n")    if $0 !~ /\.t$/;

        my $sql =
            "INSERT INTO $table "
            ."("
            .join(" , ", sort keys %{$data->{$name}})
            .")"
            ." VALUES ( "
            .join(" , ", map { "?" } keys %{$data->{$name}})
            ." )"
        ;
        my $sth = $CONNECTOR->dbh->prepare($sql);
        $sth->execute(map { $data->{$name}->{$_} } sort keys %{$data->{$name}});
        $sth->finish;
    }
}

sub _remove_old_isos {
    my $self = shift;
    for my $sql (
        "DELETE FROM iso_images "
            ."  WHERE url like '%debian-9.0%iso'"
        ,"DELETE FROM iso_images"
            ."  WHERE name like 'Debian%' "
            ."      AND NOT url  like '%*%' "
        ,"DELETE FROM iso_images "
            ."  WHERE name like 'Lubuntu Artful%'"
            ."      AND url NOT LIKE '%*%' "
        ,"DELETE FROM iso_images "
            ."  WHERE name like 'Lubuntu Zesty%'"

    ) {
        my $sth = $CONNECTOR->dbh->prepare($sql);
        $sth->execute();
        $sth->finish;
    }
}

sub _update_data {
    my $self = shift;

    $self->_remove_old_isos();
    $self->_update_isos();

    $self->_rename_grants();
    $self->_alias_grants();
    $self->_add_grants();
    $self->_enable_grants();
    $self->_update_user_grants();

    $self->_update_domain_drivers_types();
    $self->_update_domain_drivers_options();
    $self->_update_domain_drivers_options_disk();
    $self->_update_old_qemus();

    $self->_add_indexes();
}

sub _add_indexes($self) {
    return if $CONNECTOR->dbh->{Driver}{Name} !~ /mysql/i;
    $self->_add_indexes_vms();
    $self->_add_indexes_domains();
    $self->_add_indexes_requests();
}

sub _add_indexes_vms($self) {
    my %index;
    my $sth = $CONNECTOR->dbh->prepare("show index from vms");
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        $index{$row->{Key_name}}->{$row->{Column_name}}++;
    }

    my $index_name = 'hostname_vm_type';
    return if $index{$index_name};
    warn "INFO: Adding index to vms: $index_name";
    $sth = $CONNECTOR->dbh->prepare("ALTER TABLE vms add unique $index_name"
        ." (hostname, vm_type)");
    $sth->execute;
}

sub _add_indexes_domains($self) {
    my %index;
    my $sth = $CONNECTOR->dbh->prepare("show index from domains");
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        $index{$row->{Key_name}}->{$row->{Column_name}}++;
    }
    return if $index{id_base_index};
    warn "INFO: Adding domains . id_base index";
    $sth = $CONNECTOR->dbh->prepare("ALTER TABLE domains add index id_base_index "
        ."(id_base)");
    $sth->execute;
}

sub _add_indexes_requests($self) {
    my %index;
    my $sth = $CONNECTOR->dbh->prepare("show index from requests");
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        $index{$row->{Key_name}}->{$row->{Column_name}}++;
    }
    return if $index{domain_status};
    warn "INFO: Adding requests . id_domain,status index";
    $sth = $CONNECTOR->dbh->prepare("ALTER TABLE requests add index domain_status "
        ."(id_domain, status)");
    $sth->execute;
}

sub _rename_grants($self) {

    my %rename = (
        create_domain => 'create_machine'
    );
    my $sth_old = $CONNECTOR->dbh->prepare("SELECT id FROM grant_types"
            ." WHERE name=?"
    );
    for my $old ( keys %rename ) {
        $sth_old->execute($rename{$old});
        next if $sth_old->fetchrow;

        my $sth = $CONNECTOR->dbh->prepare(
                 "UPDATE grant_types"
                ." SET name=? "
                ." WHERE name = ?"
        );
        $sth->execute($rename{$old}, $old);
        warn "INFO: renaming grant $old to $rename{$old}\n";
    }
}

sub _alias_grants($self) {

    my %alias= (
        remove_clone => 'remove_clones'
        ,shutdown_clone => 'shutdown_clones'
    );

    my $sth_old = $CONNECTOR->dbh->prepare("SELECT id FROM grant_types_alias"
            ." WHERE name=? AND alias=?"
    );
    while (my ($old, $new) =  each(%alias)) {
        $sth_old->execute($old, $new);
        return if $sth_old->fetch;
        my $sth = $CONNECTOR->dbh->prepare(
                 "INSERT INTO grant_types_alias (name,alias)"
                 ." VALUES(?,?) "
        );
        $sth->execute($old, $new);
    }
}

sub _add_grants($self) {
    $self->_add_grant('rename', 0,"Can rename any virtual machine owned by the user.");
    $self->_add_grant('rename_all', 0,"Can rename any virtual machine.");
    $self->_add_grant('rename_clones', 0,"Can rename clones from virtual machines owned by the user.");
    $self->_add_grant('shutdown', 1,"Can shutdown own virtual machines.");
    $self->_add_grant('screenshot', 1,"Can get a screenshot of own virtual machines.");
    $self->_add_grant('start_many',0,"Can have more than one machine started.")
}

sub _add_grant($self, $grant, $allowed, $description) {
    my $sth = $CONNECTOR->dbh->prepare(
        "SELECT id, description FROM grant_types WHERE name=?"
    );
    $sth->execute($grant);
    my ($id, $current_description) = $sth->fetchrow();
    $sth->finish;

    if ($id && $current_description ne $description) {
        my $sth = $CONNECTOR->dbh->prepare(
            "UPDATE grant_types SET description = ? WHERE id = ?;"
        );
        $sth->execute($description, $id);
        $sth->finish;
    }
    return if $id;

    $sth = $CONNECTOR->dbh->prepare("INSERT INTO grant_types (name, description)"
        ." VALUES (?,?)");
    $sth->execute($grant, $description);
    $sth->finish;

    return if !$allowed;

    $sth = $CONNECTOR->dbh->prepare("SELECT id FROM grant_types WHERE name=?");
    $sth->execute($grant);
    my ($id_grant) = $sth->fetchrow;
    $sth->finish;

    my $sth_insert = $CONNECTOR->dbh->prepare(
        "INSERT INTO grants_user (id_user, id_grant, allowed) VALUES(?,?,?) ");

    $sth = $CONNECTOR->dbh->prepare("SELECT id FROM users ");
    $sth->execute;

    while (my ($id_user) = $sth->fetchrow ) {
        eval { $sth_insert->execute($id_user, $id_grant, $allowed) };
        die $@ if $@ && $@ !~/Duplicate entry /;
    }
}

sub _null_grants($self) {
    my $sth = $CONNECTOR->dbh->prepare("SELECT count(*) FROM grant_types "
            ." WHERE enabled = NULL "
        );
    $sth->execute;
    my ($count) = $sth->fetchrow;

    exit if !$count && $self->{_null}++;
    return $count;
}

sub _enable_grants($self) {

    return if $self->_null_grants();

    my $sth = $CONNECTOR->dbh->prepare(
        "UPDATE grant_types set enabled=0"
    );
    $sth->execute;
    my @grants = (
        'change_settings',  'change_settings_all',  'change_settings_clones'
        ,'clone',           'clone_all',            'create_base', 'create_machine'
        ,'grant'
        ,'manage_users'
        ,'rename', 'rename_all', 'rename_clones'
        ,'remove',          'remove_all',   'remove_clone',     'remove_clone_all'
        ,'screenshot'
        ,'shutdown',        'shutdown_all',    'shutdown_clone'
        ,'screenshot'
        ,'start_many'