From c4bc663de430a4e7fe23d55c88f23740a3c4f09e Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@gmail.com>
Date: Sun, 5 Feb 2017 00:49:39 +0000
Subject: [PATCH] bin/ovh-api

---
 bin/ovh-api                 | 122 +++++++++++++++++++++++++++++++++---
 www/dbq/docker-compose.yaml |   1 +
 2 files changed, 114 insertions(+), 9 deletions(-)

diff --git a/bin/ovh-api b/bin/ovh-api
index 92b46f73..376e50ba 100755
--- a/bin/ovh-api
+++ b/bin/ovh-api
@@ -68,6 +68,9 @@ my $Api = OvhApi->new(type => OvhApi::OVH_API_EU, applicationKey => $KEY[0], app
 # Auth
 auth() if @KEY < 3;
 
+# Sql
+sql() if $Opt{sql};
+
 # Query
 query();
 
@@ -100,6 +103,10 @@ sub query {
 	help() unless @ARGV;
 
 	my $URL = shift @ARGV or help();
+  my %aliases = (
+    '/server' => '/dedicated/server',
+  );
+  exists $aliases{$URL} and $URL = $aliases{$URL};
 	my $BODY = @ARGV ? shift(@ARGV) : {};
 
 	if ( -e $BODY ) {
@@ -140,22 +147,117 @@ sub query {
 }
 
 sub sql {
-my $pref = 'ovh_';
-my @tables = (
+my $preff = 'ovh_';
+my @tbs = (
+  {
+    name => 'domain',
+    path => '/domain',
+    fields => [
+      { 'name' =>'domain', 'sql' => 'domain VARCHAR(500) PRIMARY KEY' },
+      { 'name' =>'lastUpdate', 'sql' => 'updated DATETIME' },
+      #{ 'name' =>'nameServerType': 'hosted',
+      #{ 'name' =>'glueRecordIpv6Supported': true,
+      { 'name' =>'offer', 'sql' => 'offer VARCHAR(30)' },
+      { 'name' =>'transferLockStatus', 'sql' => 'transfer_status VARCHAR(20)' },
+      { 'name' =>'dnssecSupported', 'sql' => 'dnssec BOOLEAN' },
+      #{ 'name' =>'glueRecordMultiIpSupported', 'sql' => 'glue_record_multi_ip BOOLEAN' }.
+      { 'name' =>'whoisOwner', 'sql' => 'whois_owner VARCHAR(50)' },
+    ],
+  },
   {
     name => 'ip',
-    url => '/ip',
-    sql => 'CREATE TABLE IF NOT EXISTS '.$pref.'ip ('
-    .');',
+    path => '/ip',
+    fields => [
+      { 'name' => 'ip', 'sql' => ,'id VARCHAR(30) PRIMARY KEY', },
+      { 'name' => 'routedTo', 'sql' => ,'routedto VARCHAR(100)', },
+      { 'name' => 'type', 'sql' => ,'type VARCHAR(100)', },
+      { 'name' => 'country', 'sql' => ,'country VARCHAR(100)', },
+    ],
   },
   {
     name => 'server',
-    url => '/server',
-    sql => 'CREATE TABLE IF NOT EXISTS '.$pref.'server ('
-      .'serverid INT,'
-    .');',
+    path => '/dedicated/server',
+    fields => [
+      { 'name' => 'serverId', 'sql' => ,'id BIG INT PRIMARY KEY', },
+      { 'name' => 'name', 'sql' => ,'name VARCHAR(200)', },
+      { 'name' => 'ip', 'sql' => ,'ip VARCHAR(30)', },
+      { 'name' => 'reverse', 'sql' => ,'reverse VARCHAR(500)', },
+      { 'name' => 'monitoring', 'sql' => ,'monitoring BOOLEAN', },
+      { 'name' => 'state', 'sql' => ,'state BOOLEAN', },
+      { 'name' => 'linkSpeed', 'sql' => ,'link_speed VARCHAR(30)', },
+      { 'name' => 'datacenter', 'sql' => ,'datacenter VARCHAR(30)', },
+      { 'name' => 'commercialRange', 'sql' => ,'commercial_range VARCHAR(30)', },
+    ],
+    indexes => {
+      'server_name' => 'name',
+      'server_ip' => 'ip',
+    },
   },
 );
+
+  for my $tb (@tbs) {
+    #warn $tb->{name};
+    my $response = $Api->rawCall(path=>$tb->{path},method=>'GET',body=>{});
+    die "$NAME: ERR: ".$response->error()."\n" if $response->isFailure();
+    my $name =$preff.$tb->{name};
+
+    print "--\n";
+    print "-- > $name\n";
+    #print "--\n";
+
+    print "CREATE TABLE IF NOT EXISTS $name (\n"
+      .join(",\n",map {"  $_->{sql}"} @{$tb->{fields}})
+    ."\n);\n";
+
+    for my $iname (keys %{$tb->{indexes}}) {
+      print 'CREATE INDEX IF NOT EXISTS '.$preff.$iname.' ON '.$preff.'server('.$tb->{indexes}{$iname}.");\n";
+    }
+
+    # Search rows
+    for my $id (@{ $response->content }) {
+
+      my $response = $Api->rawCall(path=>$tb->{path}.'/'.$id,method=>'GET',body=>{});
+      die "$NAME: ERR: ".$response->error()."\n" if $response->isFailure();
+
+      my %row = %{ $response->content };
+      next unless %row;
+
+      my @vals;
+
+      # Search fields
+      for my $fd (@{$tb->{fields}}) {
+        my $val = $row{ $fd->{name}  };
+
+        if (ref $val eq 'HASH') {
+          $val = join(' ',values %$val);
+        } elsif (ref $val eq 'ARRAY') {
+          $val = join(' ',@$val);
+        }
+
+        if (!defined $val) {
+          $val = 'NULL';
+
+        } elsif($fd->{sql} =~ /BOOLEAN/) {
+          $val = $val =~ /^1|y|t/ ? 1 : 0;
+
+        } elsif($fd->{sql} =~ /INT|REAL|NUM|DOUBLE|FLOAT|DECIMAL/) {
+
+        } else {
+          $val =~ s/'/\\'/g;
+          $val = "'$val'";
+
+        }
+
+        push @vals, $val;
+      }
+
+      print "REPLACE INTO $name VALUES (".join(',',@vals).");\n";
+
+    }
+
+    print "-- < $name\n";
+  }
+
 }
 
 sub check_row {
@@ -265,8 +367,10 @@ $NAME - Script to query ovh's api
 
 =head1 OPTIONS
 
+ option[sql]        Produce sql to update a database
  option[rows|r]     Print each rows of a table (eg /ip, /server, ...)
  option[method|m=s] Method (default: GET)
+
  option[verbose|v+] Verbose mode: increase the verbosity level.
  option[debug+]     Debug mode: increase the verbosity level.
  option[version|V]  Print version (default: $VERSION)
diff --git a/www/dbq/docker-compose.yaml b/www/dbq/docker-compose.yaml
index ceb5731e..cfb4a51a 100644
--- a/www/dbq/docker-compose.yaml
+++ b/www/dbq/docker-compose.yaml
@@ -11,6 +11,7 @@ services:
       NB_ROOT: ${NB_ROOT}
     hostname: dbq
     volumes:
+      - /opt:/opt
       - ./../..:$NB_ROOT
       - /var/run
       - /etc/profile.d
-- 
2.47.3