Ravada.pm

package Ravada;

use warnings;
use strict;

our $VERSION = '1.4.0';

use Carp qw(carp croak cluck);
use Data::Dumper;
use DBIx::Connector;
use File::Copy;
use Hash::Util qw(lock_hash unlock_hash);
use IPC::Run3 qw(run3);
use Mojo::JSON qw( encode_json decode_json );
use Moose;
use POSIX qw(WNOHANG);
use Proc::PID::File;
use Time::HiRes qw(gettimeofday tv_interval);
use YAML;
use MIME::Base64;
use Socket qw( inet_aton inet_ntoa );
use Image::Magick::Q16;

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

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

our %VALID_VM;
our %ERROR_VM;
our $TIMEOUT_STALE_PROCESS;
our $TIMEOUT_REFRESH_REQUESTS = 0;

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, host => undef, db => undef}
    ,ldap => { admin_user => { dn => undef, password => undef }
        ,filter => undef
        ,base => undef
        ,auth => undef
        ,admin_group => undef
        ,ravada_posix_group => undef
        ,groups_base => undef
        ,field => undef
        ,server => undef
        ,port => undef
        ,size_limit => undef
    }
    ,log => undef
);

=head1 NAME

Ravada - Remote 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 $FORCE_DEBUG = 0;
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';

our $FIRST_TIME_RUN = 1;
$FIRST_TIME_RUN = 0 if $0 =~ /\.t$/;

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 _set_first_time_run($self) {
    my $sth = $CONNECTOR->dbh->table_info('%',undef,'domains','TABLE');
    my $info = $sth->fetchrow_hashref();
    $sth->finish;
    if  ( keys %$info ) {
        $FIRST_TIME_RUN = 0;
    } else {
        print "Installing " if $0 !~ /\.t$/;
    }
}

sub _install($self) {
    my $pid = Proc::PID::File->new(name => "ravada_install");
    $pid->file({dir => "/run/user/$>"}) if $>;
    if ( $pid->alive ) {
        print "Waiting for install process to finish" if $ENV{TERM};
        while ( $pid->alive ) {
            sleep 1;
            print "." if $ENV{TERM};
        }
        print "\n" if $ENV{TERM};
        return;
    }
    $pid->touch;
    $self->_set_first_time_run();

    $self->_create_tables();
    $self->_sql_create_tables();
    $self->_upgrade_tables();
    $self->_upgrade_timestamps();
    $self->_update_data();
    $self->_init_user_daemon();
    $self->_sql_insert_defaults();

    $self->_do_create_constraints();

    $pid->release();

    print "\n" if $FIRST_TIME_RUN;

}

sub _do_create_constraints($self) {
    return if !$self->{_constraints};

    if ($CAN_FORK) {

        my $pid = fork();
        die "Error: I cannot fork" if !defined $pid;
        if ($pid) {
            $self->_add_pid($pid);
            return;
        }
    }

    my $pid_file = Proc::PID::File->new(name => "ravada_constraint");
    $pid_file->file({dir => "/run/user/$>"}) if $>;
    if ( $pid_file->alive ) {
        exit if $CAN_FORK;
        return;
    }
    $pid_file->touch;
    my $dbh = $CONNECTOR->dbh;

    my $known_constraints;

    for my $constraint (@{$self->{_constraints}}) {
        my ($table,$name) = $constraint =~ /ALTER TABLE (.*?) .*?CONSTRAINT (\w+)\s/i;
        if ( !defined $table ) {
            cluck "Warning: I can't find the table in this constraint: $constraint";
            next;
        }
        if (!exists $known_constraints->{$table}) {
            my $current = $self->_get_constraints($table);
            $known_constraints->{$table} = $current;
        }
        next if exists $known_constraints->{$table}->{$name};

        warn "INFO: creating constraint $name \n"
        if $name && !$FIRST_TIME_RUN && $0 !~ /\.t$/;
        print "+" if $FIRST_TIME_RUN && !$CAN_FORK;

        $self->_clean_db_leftovers();

        my $sth = $dbh->do($constraint);
    }
    $pid_file->release;
    exit if $CAN_FORK;
}

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) {
        for (1 .. 120 ) {
            my @list = $self->_list_pids();
            last if !@list;
            sleep 1 if @list;
            $self->_wait_pids();
        }
        $USER_DAEMON = Ravada::Auth::SQL->new(name => $USER_DAEMON_NAME);
        return 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 WHERE is_temporary=0");
    my $id;
    $sth->execute;
    $sth->bind_columns(\$id);
    while ($sth->fetch) {
        my $user = Ravada::Auth::SQL->search_by_id($id) or confess "Unknown user id = $id";
        next if $user->name() eq $USER_DAEMON_NAME;

        my %grants = $user->grants();

        for my $key (keys %grants) {
            delete $grants{$key} if !defined $grants{$key};
        }
        next if keys %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 = (
	    androidx86 => {
                    name => 'Android 8.1 x86'
            ,description => 'Android-x86 64 bits. Requires an user provided ISO image.'
                   ,arch => 'x86_64'
                    ,xml => 'android-amd64.xml'
             ,xml_volume => 'android-volume.xml'
	     ,min_disk_size => '4'
        },
        arch_1909 => {
                    name => 'Arch Linux 19.09'
            ,description => 'Arch Linux 19.09.01 64 bits'
                   ,arch => 'x86_64'
                    ,xml => 'bionic-amd64.xml'
             ,xml_volume => 'bionic64-volume.xml'
                    ,url => 'https://archive.archlinux.org/iso/2019.09.01/'
                    ,file_re => 'archlinux-2019.09.01-x86_64.iso'
                    ,md5_url => ''
                    ,md5 => '1d6bdf5cbc6ca98c31f02d23e418dd96'
        },
	mate_focal_fossa => {
                    name => 'Ubuntu Mate Focal Fossa 64 bits'
            ,description => 'Ubuntu Mate 20.04 (Focal Fossa) 64 bits'
                   ,arch => 'x86_64'
                    ,xml => 'focal_fossa-amd64.xml'
             ,xml_volume => 'focal_fossa64-volume.xml'
                    ,url => 'http://cdimage.ubuntu.com/ubuntu-mate/releases/20.04.*/release/ubuntu-mate-20.04.*-desktop-amd64.iso'
                ,sha256_url => '$url/SHA256SUMS'
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
                ,min_ram => 1
        },
        mate_bionic => {
                    name => 'Ubuntu Mate Bionic 64 bits'
            ,description => 'Ubuntu Mate 18.04 (Bionic Beaver) 64 bits'
                   ,arch => 'x86_64'
                    ,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'
                ,sha256_url => '$url/SHA256SUMS'
                ,min_ram => 1
        },
        mate_bionic_i386 => {
                    name => 'Ubuntu Mate Bionic 32 bits'
            ,description => 'Ubuntu Mate 18.04 (Bionic Beaver) 32 bits'
                   ,arch => 'i686'
                    ,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'
                ,sha256_url => '$url/SHA256SUMS'
                ,min_ram => 1
        },
        ubuntu_xenial => {
                    name => 'Ubuntu Xenial Xerus 64 bits'
            ,description => 'Ubuntu 16.04 LTS Xenial Xerus 64 bits'
                   ,arch => 'x86_64'
                    ,xml => 'xenial64-amd64.xml'
             ,xml_volume => 'xenial64-volume.xml'
                    ,url => 'http://releases.ubuntu.com/16.04/ubuntu-16.04.*-desktop-amd64.iso'
                ,sha256_url => '$url/SHA256SUMS'
                ,min_disk_size => '10'
        },

        mate_xenial => {
                    name => 'Ubuntu Mate Xenial'
            ,description => 'Ubuntu Mate 16.04.3 (Xenial) 64 bits'
                   ,arch => 'x86_64'
                    ,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'
                ,sha256_url => '$url/SHA256SUMS'
                ,min_disk_size => '10'
        },
	,focal_fossa=> {
                    name => 'Ubuntu Focal Fossa'
            ,description => 'Ubuntu 20.04 Focal Fossa 64 bits'
                   ,arch => 'x86_64'
                    ,xml => 'focal_fossa-amd64.xml'
             ,xml_volume => 'focal_fossa64-volume.xml'
                    ,url => 'http://releases.ubuntu.com/20.04/'
                ,file_re => '^ubuntu-20.04.*-desktop-amd64.iso'
                ,sha256_url => '$url/SHA256SUMS'
          ,min_disk_size => '9'
          ,min_ram => 1
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
                   ,arch => 'x86_64'
        }

        ,bionic=> {
                    name => 'Ubuntu Bionic Beaver'
            ,description => 'Ubuntu 18.04 Bionic Beaver 64 bits'
                   ,arch => 'x86_64'
                    ,xml => 'bionic-amd64.xml'
             ,xml_volume => 'bionic64-volume.xml'
                    ,url => 'http://releases.ubuntu.com/18.04/'
                ,file_re => '^ubuntu-18.04.*desktop-amd64.iso'
                ,sha256_url => '$url/SHA256SUMS'
          ,min_disk_size => '9'
                ,min_ram => 1
            ,arch => 'x86_64'
        }

        ,serena64 => {
            name => 'Mint 18.1 Mate 64 bits'
    ,description => 'Mint Serena 18.1 with Mate Desktop based on Ubuntu Xenial 64 bits'
           ,arch => 'x86_64'
            ,xml => 'xenial64-amd64.xml'
     ,xml_volume => 'xenial64-volume.xml'
            ,url => 'https://mirrors.edge.kernel.org/linuxmint/stable/18.3'
        ,file_re => 'linuxmint-18.3-mate-64bit.iso'
        ,md5_url => ''
            ,md5 => 'c5cf5c5d568e2dfeaf705cfa82996d93'
            ,min_disk_size => '10'

        }
        ,mint20_64 => {
            name => 'Mint 20 Mate 64 bits'
    ,description => 'Mint Ulyana 20 with Mate Desktop 64 bits'
           ,arch => 'x86_64'
            ,xml => 'xenial64-amd64.xml'
     ,xml_volume => 'xenial64-volume.xml'
            ,url => 'https://mirrors.edge.kernel.org/linuxmint/stable/20.2'
        ,file_re => 'linuxmint-20.2-mate-64bit.iso'
        ,sha256_url => '$url/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 => 'x86_64'
            ,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 => '2'
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
        }
        ,alpine381_32 => {
            name => 'Alpine 3.8 32 bits'
    ,description => 'Alpine Linux 3.8 32 bits ( Minimal Linux Distribution )'
           ,arch => 'i686'
            ,xml => 'alpine-i386.xml'
     ,xml_volume => 'alpine381_32-volume.xml'
            ,url => 'http://dl-cdn.alpinelinux.org/alpine/v3.8/releases/x86/'
            ,options => { machine => 'pc-i440fx' }
        ,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_28 => {
            name => 'Fedora 28'
            ,description => 'RedHat Fedora 28 Workstation 64 bits'
            ,url => 'https://archives.fedoraproject.org/pub/archive/fedora/linux/releases/28/Workstation/x86_64/iso/Fedora-Workstation-netinst-x86_64-28-.*\.iso'
            ,arch => 'x86_64'
            ,xml => 'xenial64-amd64.xml'
            ,xml_volume => 'xenial64-volume.xml'
            ,sha256_url => '$url/Fedora-Workstation-28-.*-x86_64-CHECKSUM'
            ,min_disk_size => '10'
        }
	      ,kubuntu_64_focal_fossa => {
            name => 'Kubuntu Focal Fossa 64 bits'
            ,description => 'Kubuntu 20.04 Focal Fossa 64 bits'
            ,arch => 'x86_64'
            ,xml => 'focal_fossa-amd64.xml'
            ,xml_volume => 'focal_fossa64-volume.xml'
            ,sha256_url => '$url/SHA256SUMS'
            ,url => 'http://cdimage.ubuntu.com/kubuntu/releases/20.04.*/release/'
            ,file_re => 'kubuntu-20.04.*-desktop-amd64.iso'
            ,rename_file => 'kubuntu_focal_fossa_64.iso'
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
                ,min_ram => 1
        }
        ,kubuntu_64 => {
            name => 'Kubuntu Bionic Beaver 64 bits'
            ,description => 'Kubuntu 18.04 Bionic Beaver 64 bits'
            ,arch => 'x86_64'
            ,xml => 'bionic-amd64.xml'
            ,xml_volume => 'bionic64-volume.xml'
            ,sha256_url => '$url/SHA256SUMS'
            ,url => 'http://cdimage.ubuntu.com/kubuntu/releases/18.04/release/'
            ,file_re => 'kubuntu-18.04.\d+-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 => 'i686'
            ,xml => 'bionic-i386.xml'
            ,xml_volume => 'bionic32-volume.xml'
            ,sha256_url => '$url/SHA256SUMS'
            ,url => 'http://cdimage.ubuntu.com/kubuntu/releases/18.04/release/'
            ,file_re => 'kubuntu-18.04.\d+-desktop-i386.iso'
            ,rename_file => 'kubuntu_bionic_32.iso'
        }
        ,suse_15 => {
            name => "openSUSE Leap 15"
            ,description => "openSUSE Leap 15 64 bits"
            ,arch => 'x86_64'
            ,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 => 'x86_64'
            ,xml => 'bionic-amd64.xml'
            ,xml_volume => 'bionic64-volume.xml'
            ,sha256_url => '$url/../SHA256SUMS'
            ,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 => 'i686'
            ,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_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'
             ,sha256_url => '$url/SHA256SUMS'
             ,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 => 'i686'
             ,url => 'http://cdimage.ubuntu.com/lubuntu/releases/18.04.*/release/lubuntu-18.04.*-desktop-i386.iso'
             ,sha256_url => '$url/SHA256SUMS'
             ,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'
            ,sha256_url => '$url/SHA256SUMS'
            ,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\.1\d+\.\d$/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'
            ,arch => 'i686'
        }
        ,debian_jessie_64 => {
            name =>'Debian Jessie 64 bits'
            ,description => 'Debian 8 Jessie 64 bits'
            ,url => 'http://cdimage.debian.org/cdimage/archive/^8\.1\d+.*\d$/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'
            ,arch => 'x86_64'
        }
       ,debian_stretch_32 => {
            name =>'Debian Stretch 32 bits'
            ,description => 'Debian 9 Stretch 32 bits (XFCE desktop)'
            ,url => 'https://cdimage.debian.org/cdimage/archive/^9\.1\d.*\d$/i386/iso-cd/'
            ,file_re => 'debian-9.[\d\.]+-i386-xfce-CD-1.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-i386.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
            ,arch => 'i686'
        }
        ,debian_stretch_64 => {
            name =>'Debian Stretch 64 bits'
            ,description => 'Debian 9 Stretch 64 bits (XFCE desktop)'
            ,url => 'https://cdimage.debian.org/cdimage/archive/^9\.1\d.*/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'
            ,arch => 'x86_64'
        }
        ,debian_buster_64=> {
            name =>'Debian Buster 64 bits'
            ,description => 'Debian 10 Buster 64 bits (XFCE desktop)'
            ,url => 'https://cdimage.debian.org/cdimage/archive/^10\.1\d+.*\d$/amd64/iso-cd/'
            ,file_re => 'debian-10.[\d\.]+-amd64-xfce-CD-1.iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
            ,arch => 'x86_64'
        }
        ,debian_buster_32=> {
            name =>'Debian Buster 32 bits'
            ,description => 'Debian 10 Buster 32 bits (XFCE desktop)'
            ,url => 'https://cdimage.debian.org/cdimage/archive/^10\.1\d+.*\d$/i386/iso-cd/'
            ,file_re => 'debian-10.[\d\.]+-i386-(netinst|xfce-CD-1).iso'
            ,md5_url => '$url/MD5SUMS'
            ,xml => 'jessie-i386.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
            ,arch => 'i686'
        }
        ,debian_bullseye_64=> {
            name =>'Debian Bullseye 64 bits'
            ,arch => 'x86_64'
            ,description => 'Debian 11 Bullseye 64 bits (netinst)'
            ,url => 'https://cdimage.debian.org/debian-cd/^11\..*\d$/amd64/iso-cd/'
            ,file_re => 'debian-11.[\d\.]+-amd64-netinst.iso'
            ,sha256_url => '$url/SHA256SUMS'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
        }
        ,debian_bullseye_32=> {
            name =>'Debian Bullseye 32 bits'
            ,arch => 'i686'
            ,description => 'Debian 10 Bullseye 32 bits (netinst)'
            ,url => 'https://cdimage.debian.org/debian-cd/^11\..*\d$/i386/iso-cd/'
            ,file_re => 'debian-11.[\d\.]+-i386-netinst.iso'
            ,sha256_url => '$url/SHA256SUMS'
            ,xml => 'jessie-i386.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
            ,options => { machine => 'pc-i440fx' }
        }
        ,devuan_beowulf_amd64=> {
            name =>'Devuan Beowulf 64 bits'
            ,description => 'Devuan Beowulf Desktop Live (amd64)'
            ,arch => 'x86_64'
            ,url => 'http://tw1.mirror.blendbyte.net/devuan-cd/devuan_beowulf/desktop-live/'
            ,file_re => 'devuan_beowulf_.*_amd64_desktop-live.iso'
            ,sha256_url => '$url/SHASUMS.txt'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
        }
        ,devuan_beowulf_i386=> {
            name =>'Devuan Beowulf 32 bits'
            ,description => 'Devuan Beowulf Desktop Live (i386)'
            ,arch => 'i686'
            ,url => 'http://tw1.mirror.blendbyte.net/devuan-cd/devuan_beowulf/desktop-live/'
            ,file_re => 'devuan_beowulf_.*_i386_desktop-live.iso'
            ,sha256_url => '$url/SHASUMS.txt'
            ,xml => 'jessie-i386.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,min_disk_size => '10'
        }
        ,parrot_xfce_amd64 => {
            name => 'Parrot Home Edition XFCE'
            ,description => 'Parrot Home Edition XFCE 64 Bits'
            ,arch => 'x86_64'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,url => 'https://edge1.parrot.run/parrot/iso/4.11.3/'
            ,file_re => 'Parrot-xfce-4.11.3_amd64.iso'
            ,sha256_url => '$url/signed-hashes.txt'
            ,min_disk_size => '10'
        }
        ,parrot_mate_amd64 => {
		  name => 'Parrot Security Edition MATE'
            ,description => 'Parrot Security Edition MATE 64 Bits'
            ,arch => 'x86_64'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,url => 'https://edge1.parrot.run/parrot/iso/4.11.3/'
            ,file_re => 'Parrot-security-4.11.3_amd64.iso'
            ,sha256_url => '$url/signed-hashes.txt'
            ,min_disk_size => '10'
        }
        ,kali_64 => {
            name => 'Kali Linux 2021'
            ,description => 'Kali Linux 2021 64 Bits'
            ,arch => 'x86_64'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,url => 'https://cdimage.kali.org/kali-2021.\d+/'
            ,file_re => 'kali-linux-2021.\d+-installer-amd64.iso'
            ,sha256_url => '$url/SHA256SUMS'
            ,min_disk_size => '10'
        }
        ,kali_64_netinst => {
            name => 'Kali Linux 2021 (NetInstaller)'
            ,description => 'Kali Linux 2021 64 Bits (light NetInstall)'
            ,arch => 'x86_64'
            ,xml => 'jessie-amd64.xml'
            ,xml_volume => 'jessie-volume.xml'
            ,url => 'https://cdimage.kali.org/kali-2021.\d+/'
            ,file_re => 'kali-linux-2021.\d+-installer-netinst-amd64.iso'
            ,sha256_url => '$url/SHA256SUMS'
            ,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'
          ,arch => 'x86_64'
        }
        ,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'
          ,min_swap_size => '2'
          ,arch => 'x86_64'
          ,extra_iso => 'https://fedorapeople.org/groups/virt/virtio-win/direct-downloads/archive-virtio/virtio-win-0.1.215-\d+/virtio-win-0.1.\d+.iso'
        }
        ,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'
          ,arch => 'x86_64'
        }
        ,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'
          ,arch => 'x86_64'
        }
        ,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'
          ,arch => 'x86_64'
        }
        ,windows_11 => {
          name => 'Windows 11'
          ,description => 'Windows 11 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_11.xml'
          ,xml_volume => 'windows11-volume.xml'
          ,min_disk_size => '64'
          ,min_swap_size => '2'
          ,min_ram => 4
          ,arch => 'x86_64'
          ,extra_iso => 'https://fedorapeople.org/groups/virt/virtio-win/direct-downloads/archive-virtio/virtio-win-0.1.2\d+-\d+/virtio-win-0.1.2\d+.iso'
            ,options => { machine => 'pc-q35', bios => 'UEFI' }
        }
       ,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'
          ,has_cd => 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'
          ,arch => 'x86_64'
          ,has_cd => 0
        }
    );
    $self->_scheduled_fedora_releases(\%data) if $0 !~ /\.t$/;
    $self->_update_table($table, $field, \%data);
    $self->_update_table_isos_url(\%data);

}

sub _update_table_isos_url($self, $data) {
    my $sth = $CONNECTOR->dbh->prepare("SELECT * FROM iso_images WHERE name=?");
    for my $release (sort keys %$data) {
        my $entry = $data->{$release};
        if (exists $entry->{options} && $entry->{options} 
            && ref($entry->{options})
        ) {
            unlock_hash(%$entry);
            $entry->{options} = encode_json($entry->{options});
            lock_hash(%$entry);
        }
        $sth->execute($entry->{name});
        my $row = $sth->fetchrow_hashref();
        for my $field (keys %$entry) {
            next if defined $row->{$field} && $row->{$field} eq $entry->{$field};
            my $sth_update = $CONNECTOR->dbh->prepare(
                "UPDATE iso_images SET $field=?"
                ." WHERE id=?"
            );
            $sth_update->execute($entry->{$field}, $row->{id});
            warn("INFO: updating $release $field '".($row->{$field} or '')."' -> '$entry->{$field}'\n")
            if !$FIRST_TIME_RUN && $0 !~ /\.t$/;
        }
    }
}

sub _scheduled_fedora_releases($self,$data) {

    return if !exists $VALID_VM{KVM} ||!$VALID_VM{KVM} || $>;
    my $vm = $self->search_vm('KVM') or return; # TODO move ISO downloads off KVM

    my @now = localtime(time);
    my $year = $now[5]+1900;
    my $month = $now[4]+1;

    my $url_archive
    = 'https://archives.fedoraproject.org/pub/archive/fedora/linux/releases/';

    my $url_current
    = 'http://ftp.halifax.rwth-aachen.de/fedora/linux/releases/';

    my $release = 27;

    for my $y ( 2018 .. $year ) {
        for my $m ( 5, 11 ) {
            return if $y == $year && $m>$month;
            $release++;
            my $name = "fedora_".$release;
            next if exists $data->{$name};

            my $url = $url_archive;
            $url = $url_current if $y>=$year-1;

            my $url_file = $url.$release
                    .'/Workstation/x86_64/iso/Fedora-Workstation-.*-x86_64-'.$release
                    .'-.*\.iso';
            my @found;
            eval { @found = $vm->_search_url_file($url_file) };
            if ( $@ && $@ !~ /Not Found/i ) {
                warn $@;
                return;
            }
            if(!@found) {
                next if $url =~ m{//archives};

                $url_file = $url_archive.$release
                    .'/Workstation/x86_64/iso/Fedora-Workstation-.*-x86_64-'.$release
                    .'-.*\.iso';
                @found = $vm->_search_url_file($url_file);
                next if !scalar(@found);
            }

            $data->{$name} = {
            name => 'Fedora '.$release
            ,description => "RedHat Fedora $release Workstation 64 bits"
            ,arch => 'x86_64'
            ,url => $url_file
            ,xml => 'xenial64-amd64.xml'
            ,xml_volume => 'xenial64-volume.xml'
            ,sha256_url => '$url/Fedora-Workstation-'.$release.'-.*-x86_64-CHECKSUM'
            ,min_disk_size => 10 + $release-27
            };
        }
    }
}

sub _add_domain_drivers_display($self) {
    my $port_rdp = 3389;
    my %data = (
        'KVM' => [
            'spice'
            ,'vnc'
            ,{name => 'x2go', data => 22 }
            ,{name => 'Windows RDP', value => 'rdp', data => $port_rdp}
        ]
        ,'Void' => [
            'void'
            ,'spice'
            ,{name => 'x2go', data => 22 }
            ,{name => 'Windows RDP', value => 'rdp' , data => $port_rdp }
        ]
    );

    my $id_type = Ravada::Utils::max_id($CONNECTOR->dbh, 'domain_drivers_types')+1;
    my $id_option = Ravada::Utils::max_id($CONNECTOR->dbh, 'domain_drivers_options');
    for my $vm ( keys %data) {
        my $type = {
            id => $id_type
            ,name => 'display'
            ,description => 'Display'
            ,vm => $vm
        };

        $self->_update_table('domain_drivers_types','name,vm',$type)
            and do {
            for my $option ( @{$data{$vm}} ) {
                if (!ref($option)) {
                    $option = { name => $option
                        ,value => $option
                    };
                }
                $option->{value} = $option->{name} if !exists $option->{value};
                $option->{id_driver_type} = $id_type;
                $option->{id} = ++$id_option;
                $self->_update_table('domain_drivers_options','id_driver_type,name',$option)
            }
            $id_type++;
        };
    }
}

sub _add_domain_drivers_cpu($self) {
    my %data = (
        'KVM' => [
            'custom'
            ,'host-model'
            ,'host-passthrough'
        ]
    );

    my $id_type = Ravada::Utils::max_id($CONNECTOR->dbh, 'domain_drivers_types')+1;
    my $id_option = Ravada::Utils::max_id($CONNECTOR->dbh, 'domain_drivers_options');
    for my $vm ( keys %data) {
        my $type = {
            id => $id_type
            ,name => 'cpu'
            ,description => 'CPU'
            ,vm => $vm
        };

        $self->_update_table('domain_drivers_types','name,vm',$type)
            and do {
            for my $option ( @{$data{$vm}} ) {
                if (!ref($option)) {
                    $option = { name => $option
                        ,value => $option
                    };
                }
                $option->{value} = $option->{name} if !exists $option->{value};
                $option->{id_driver_type} = $id_type;
                $option->{id} = ++$id_option;
                $self->_update_table('domain_drivers_options','id_driver_type,name',$option)
            }
            $id_type++;
        };
    }
}

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'

        }