path: root/lib/RHRD/rddb.pm

# rddb.pm
#
#  rhrdlibs
#
#  Copyright (C) 2015 Christian Pointner <equinox@helsinki.at>
#
#  This file is part of rhrdlibs.
#
#  rhrdlibs is free software: you can redistribute it and/or modify
#  it under the terms of the GNU Affero General Public License as published by
#  the Free Software Foundation, either version 3 of the License, or
#  any later version.
#
#  rhrdlibs is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU Affero General Public License for more details.
#
#  You should have received a copy of the GNU Affero General Public License
#  along with rhrdlibs. If not, see <http://www.gnu.org/licenses/>.
#

package RHRD::rddb;

use strict;
use Config::IniFiles;
use DBI;

###########################  constants  ###########################

use constant {
  DB_VERSION => 245,
  RD_CONFIG_FILE => '/etc/rd.conf',
  DROPBOX_PSEUDO_STATION_NAME => 'import-dropbox',
  RD_MIN_CART => 1,
  RD_MAX_CART => 999999,
  RD_MIN_CUT => 1,
  RD_MAX_CUT => 999,

  RHRD_SYSTEM_MACROS_GROUP => 'SYSTEM',
  RHRD_SHOW_MACROS_GROUP => 'SHOWS',
  RHRD_ALLSHOWS_GROUP => 'ALL_SHOWS',
  RHRD_ALLMUSICPOOLS_GROUP => 'ALL_POOLS',
};

###########################  connection handling  ###########################

sub opendb
{
  my $RD_CONF = RD_CONFIG_FILE;
  my $cfg = Config::IniFiles->new(-file => $RD_CONF)
    or return (undef , 'ERROR', "Config File Error: " . join("\n", @Config::IniFiles::errors));

  my $dbhost = $cfg->val('mySQL', 'Hostname');
  my $dbname = $cfg->val('mySQL', 'Database');
  my $dbuser = $cfg->val('mySQL', 'Loginname');
  my $dbpasswd = $cfg->val('mySQL', 'Password');

  my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost","$dbuser","$dbpasswd")
    or return (undef, 'ERROR', "Database Error: " . $DBI::errstr);


  $dbh->do(qq{SET CHARACTER SET utf8;})
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);


  my $sth = $dbh->prepare(qq{select DB from VERSION;})
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($dbver) = $sth->fetchrow_array();
  $sth->finish();

  if($dbver != DB_VERSION) {
    return (undef, 'ERROR', "Wrong Database Version " . $dbver . " (should be " . DB_VERSION . ")");
  }

  return ($dbh, 'OK', 'success');
}

sub closedb
{
  my $dbh = shift;
  $dbh->disconnect();
}

###########################  utils  ###########################

sub get_cart_range
{
  my ($dbh, $groupname) = @_;

  my $sql = qq{select DEFAULT_LOW_CART, DEFAULT_HIGH_CART, DEFAULT_TITLE from GROUPS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($low_cart, $high_cart, $chunk_size) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $low_cart) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($low_cart, $high_cart, $chunk_size);
}

###########################  TOKEN handling  ###########################

sub get_token
{
  my ($dbh, $username) = @_;

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub set_token
{
  my ($dbh, $username, $token) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }

  my $sql = qq{update USERS set PASSWORD = ? where LOGIN_NAME = ?;};
  my $rows = $dbh->do($sql, undef, $token, $username)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  unless($rows == 1) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($token, 'OK', 'success');
}

sub check_token
{
  my ($dbh, $username, $token) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }

  my $sql = qq{select PASSWORD from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($token_result) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $token_result) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }

  if($token_result eq $token) {
    return (1, 'OK', 'success');
  }
  return (0, 'ERROR', "wrong password");
}

###########################  USER handling  ###########################

sub add_user
{
  my ($dbh, $username, $token, $fullname) = @_;
  if(!defined $token || $token eq '') {
    return (undef, 'ERROR', "empty token is not allowed")
  }
  if(!defined $fullname) {
    $fullname = '';
  }

  my $sql = qq{insert into USERS (LOGIN_NAME, FULL_NAME, PHONE_NUMBER, DESCRIPTION, PASSWORD, ENABLE_WEB, ADMIN_USERS_PRIV, ADMIN_CONFIG_PRIV, CREATE_CARTS_PRIV, DELETE_CARTS_PRIV, MODIFY_CARTS_PRIV, EDIT_AUDIO_PRIV, ASSIGN_CART_PRIV, CREATE_LOG_PRIV, DELETE_LOG_PRIV, DELETE_REC_PRIV, PLAYOUT_LOG_PRIV, ARRANGE_LOG_PRIV, MODIFY_TEMPLATE_PRIV, ADDTO_LOG_PRIV, REMOVEFROM_LOG_PRIV, CONFIG_PANELS_PRIV, VOICETRACK_LOG_PRIV, EDIT_CATCHES_PRIV, ADD_PODCAST_PRIV, EDIT_PODCAST_PRIV, DELETE_PODCAST_PRIV) values ( ?, ?, "", "", ? , "N", "N", "N", "Y", "Y", "Y", "Y", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N");};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  my $cnt = $sth->execute($username, $fullname, $token)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub remove_user
{
  my ($dbh, $username) = @_;

  my @actions = ({
      # Delete RSS Feed Perms
      sql => qq{delete from FEED_PERMS where USER_NAME = ?;},
      name => 'podcast feed assignments',
      cnt => 0
    }, {
      # Delete Member User Perms
      sql => qq{delete from USER_PERMS where USER_NAME = ?;},
      name => 'group assignments',
      cnt => 0
    }, {
      # Delete from User List
      sql => qq{delete from USERS where LOGIN_NAME = ?;},
      name => 'user entries',
      cnt => 0
    }, {
      # Delete from Cached Web Connections
      sql => qq{delete from WEB_CONNECTIONS where LOGIN_NAME = ?;},
      name => 'cached web connections',
      cnt => 0
    });

  for my $href (@actions) {
    my $sth = $dbh->prepare($href->{sql})
      or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);
    delete($href->{sql});

    $href->{cnt} = $sth->execute($username)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $sth->finish();
  }

  return @actions;
}

sub check_user
{
  my ($dbh, $username) = @_;

  my $sql = qq{select count(*) from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();

  if ($cnt != 0) {
    $sql = qq{select count(*) from STATIONS where DEFAULT_NAME = ?;};
    my $sth = $dbh->prepare($sql)
      or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

    $sth->execute($username)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    ($cnt) = $sth->fetchrow_array();
    $sth->finish();

    if($cnt) { return (2, 'OK', "user '" . $username . "' is known by rivendell and is the default user of at least one station"); }
    else { return (1, 'OK', "user '" . $username . "' is known by rivendell and isn't the default user of any station"); }
  }

  return (0, 'OK', "user '" . $username . "' not known by rivendell");
}

sub get_fullname
{
  my ($dbh, $username) = @_;

  my $sql = qq{select FULL_NAME from USERS where LOGIN_NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($fullname) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $fullname) {
    return (undef, 'ERROR', "user '" . $username . "' not known by rivendell")
  }
  return ($fullname, 'OK', 'success');
}

sub get_users
{
  my ($dbh) = @_;

  my $sql = qq{select LOGIN_NAME from USERS order by LOGIN_NAME;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @users;
  while(my ($user) = $sth->fetchrow_array()) {
    push @users, $user;
  }
  $sth->finish();

  return @users;
}

###########################  GROUP handling  ###########################

sub add_group
{
  my ($dbh, $groupname, $description) = @_;
  if(!defined $description) {
    $description = '';
  }

  my $sql = qq{insert into GROUPS (NAME, DESCRIPTION) values (?, ?);};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  my $cnt = $sth->execute($groupname, $description)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();
  return ($cnt, 'OK', "success");
}

sub remove_group
{
  my ($dbh, $groupname) = @_;

  my @actions = ({
      # Delete Member Carts
      sql => qq{delete from CART where GROUP_NAME = ?;},
      name => 'member carts',
      cnt => 0
    }, {
      # Delete Dropboxes
      sql => qq{delete from DROPBOXES where GROUP_NAME = ?;},
      name => 'dropboxes',
      cnt => 0
    }, {
      # Delete Audio Perms
      sql => qq{delete from AUDIO_PERMS where GROUP_NAME = ?;},
      name => 'service permissions',
      cnt => 0
    }, {
      # Delete Member User Perms
      sql => qq{delete from USER_PERMS where GROUP_NAME = ?;},
      name => 'user assignments',
      cnt => 0
    }, {
      # Delete Replicator Map Records
      sql => qq{delete from REPLICATOR_MAP where GROUP_NAME = ?;},
      name => 'replicator map records',
      cnt => 0
    }, {
      # Delete from Group List
      sql => qq{delete from GROUPS where NAME = ?;},
      name => 'group entries',
      cnt => 0
    });

  for my $href (@actions) {
    my $sth = $dbh->prepare($href->{sql})
      or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);
    delete($href->{sql});

    $href->{cnt} = $sth->execute($groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    $sth->finish();
  }

  return @actions;
}

sub check_group
{
  my ($dbh, $groupname) = @_;

  my $sql = qq{select count(*) from GROUPS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($cnt) = $sth->fetchrow_array();
  $sth->finish();

  if ($cnt != 0) {
    $sql = qq{select count(*) from CART where GROUP_NAME = ?;};
    my $sth = $dbh->prepare($sql)
      or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

    $sth->execute($groupname)
      or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

    ($cnt) = $sth->fetchrow_array();
    $sth->finish();

    if($cnt) { return (2, 'OK', "group '" . $groupname . "' exists and has carts assigned to it"); }
    else { return (1, 'OK', "group '" . $groupname . "' exists but no cart is assigned to it"); }
  }

  return (0, 'OK', "group '" . $groupname . "' does not exist");
}

sub get_group_carts
{
  my ($dbh, $groupname) = @_;

  my $sql = qq{select DEFAULT_LOW_CART, DEFAULT_HIGH_CART, DEFAULT_CART_TYPE, ENFORCE_CART_RANGE from GROUPS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($low_cart, $high_cart, $cart_type, $enforce_cart_range) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $low_cart) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($low_cart, $high_cart, $cart_type, $enforce_cart_range);
}

sub set_group_carts
{
  my ($dbh, $groupname, $low_cart, $high_cart, $cart_type, $enforce_cart_range) = @_;
  if(!defined $low_cart) {
    $low_cart = 0;
  }
  if(!defined $high_cart) {
    $high_cart = 0;
  }
  if(!defined $cart_type) {
    $cart_type = 1;
  }
  if(!defined $enforce_cart_range) {
    $enforce_cart_range = 'N';
  }

  my $sql = qq{update GROUPS set DEFAULT_LOW_CART = ?, DEFAULT_HIGH_CART = ?, DEFAULT_CART_TYPE  = ? , ENFORCE_CART_RANGE = ? where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  my $cnt = $sth->execute($low_cart, $high_cart, $cart_type, $enforce_cart_range, $groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

  return ($cnt, 'OK', "Success");
}

sub get_group_reports
{
  my ($dbh, $groupname) = @_;

  my $sql = qq{select ENABLE_NOW_NEXT, REPORT_TFC, REPORT_MUS from GROUPS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($now_next, $traffic, $music) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $now_next) { return (undef, 'ERROR', "group '" . $groupname . "' does not exist"); }

  return ($now_next, $traffic, $music);
}

sub set_group_reports
{
  my ($dbh, $groupname, $now_next, $traffic, $music) = @_;
  if(!defined $now_next) {
    $now_next = 'N';
  }
  if(!defined $traffic) {
    $traffic = 'Y';
  }
  if(!defined $music) {
    $music = 'Y';
  }

  my $sql = qq{update GROUPS set ENABLE_NOW_NEXT = ?, REPORT_TFC = ?, REPORT_MUS = ? where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  my $cnt = $sth->execute($now_next, $traffic, $music, $groupname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  $sth->finish();

  return ($cnt, 'OK', "Success");
}

###########################  SHOW handling  ###########################

sub get_showtitle_and_log
{
  my ($dbh, $showid) = @_;

  my $sql = qq{select TITLE,MACROS from CART where NUMBER = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($showid)
    or return (undef, undef, 'ERROR', "Database Error: " . $sth->errstr);

  my ($title, $macros) = $sth->fetchrow_array;
  $sth->finish();

  unless(defined $title) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid .  " not found!")
  }
  unless(defined $macros) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has no macro!");
  }

  unless($macros =~ /^LL 1 ([^ ]+) 0\!$/) {
    return (undef, undef, 'ERROR', "Show with ID=" . $showid . " has invalid macro: '" . $macros . "'");
  }
  my $log = $1;

  return ($title, $log, 'OK', 'success');
}

sub get_dropboxes
{
  my ($dbh, $username) = @_;

  my $sql = qq{select USER_PERMS.GROUP_NAME,DROPBOXES.TO_CART,DROPBOXES.NORMALIZATION_LEVEL,DROPBOXES.AUTOTRIM_LEVEL,DROPBOXES.SET_USER_DEFINED,GROUPS.DEFAULT_LOW_CART,GROUPS.DEFAULT_HIGH_CART,GROUPS.DESCRIPTION from USER_PERMS, DROPBOXES, GROUPS where USER_PERMS.USER_NAME=? and DROPBOXES.GROUP_NAME=USER_PERMS.GROUP_NAME and DROPBOXES.GROUP_NAME=GROUPS.NAME and DROPBOXES.STATION_NAME=?;};

  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($username, DROPBOX_PSEUDO_STATION_NAME)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @allowed_dbs;
  while(my ($group, $to_cart, $normlevel, $trimlevel, $params, $lowcart, $highcart, $groupdesc) = $sth->fetchrow_array()) {
    my @p = split(';', $params);

    my $entry = {};
    $entry->{'GROUP'} = $group;
    $entry->{'GROUPDESC'} = $groupdesc;
    $entry->{'GROUPLOWCART'} = int $lowcart;
    $entry->{'GROUPHIGHCART'} = int $highcart;
    $entry->{'NORMLEVEL'} = int $normlevel;
    $entry->{'TRIMLEVEL'} = int $trimlevel;
    $entry->{'PARAM'} = $params;
    if($p[0] eq "S") {
      $entry->{'TYPE'} = 'show';
      $entry->{'SHOWID'} = $to_cart;

      my ($title, $log, $status, $errorstring) = get_showtitle_and_log($dbh, $to_cart);
      unless (defined $title && defined $log) {
        return (undef, $status, $errorstring);
      }
      $entry->{'SHOWTITLE'} = $title;
      $entry->{'SHOWLOG'} = $log;

      $entry->{'SHOWRHYTHM'} = $p[1];
      $entry->{'SHOWDOW'} = int $p[2];
      $entry->{'SHOWDOW'} = 0 unless $entry->{'SHOWDOW'} < 7;
      substr($p[3], 2, 0) = ':';
      $entry->{'SHOWSTARTTIME'} = $p[3];
      $entry->{'SHOWLEN'} = int $p[4];
    } elsif($p[0] eq "J") {
      $entry->{'TYPE'} = 'jingle';
      $entry->{'JINGLETITLE'} = $groupdesc;
    } elsif($p[0] eq "M") {
      $entry->{'TYPE'} = 'musicpool';
      $entry->{'MUSICPOOLTITLE'} = $groupdesc;
    }

    push @allowed_dbs, $entry;
  }
  $sth->finish();

  return @allowed_dbs;
}

sub get_show_carts
{
  my ($dbh, $logname, $group_low_cart, $group_high_cart) = @_;

  my $sql = qq{select LOG_EXISTS from LOGS where NAME = ?;};
  my $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute($logname)
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my $log_exists = $sth->fetchrow_array;
  $sth->finish();

  if(!defined $log_exists || $log_exists ne 'Y') {
    return (undef, 'ERROR', "Log with name '$logname' does not exist")
  }

  $logname=~s/ /_/g;
  $logname = $dbh->quote_identifier($logname . '_LOG');
  $sql = qq{select COUNT,CART_NUMBER from $logname order by COUNT;};

  $sth = $dbh->prepare($sql)
    or return (undef, 'ERROR', "Database Error: " . $dbh->errstr);

  $sth->execute()
    or return (undef, 'ERROR', "Database Error: " . $sth->errstr);

  my @carts;
  while(my ($count, $cart) = $sth->fetchrow_array()) {
    if($cart >= $group_low_cart && $cart <= $group_high_cart) {
      push @carts, $cart;
    }
  }
  $sth->finish();

  return @carts;
}

return 1;

sub get_shows_cart_range
{
  my ($dbh) = @_;
  return get_cart_range($dbh, RHRD_ALLSHOWS_GROUP)
}

###########################  MUSICPOOL handling  ###########################

sub get_musicpool_cart_range
{
  my ($dbh) = @_;
  return get_cart_range($dbh, RHRD_ALLMUSICPOOLS_GROUP)
}

#################################  END  ####################################