Hochladen eines MySQL-Telefonbuchs

From Fritz!Box
Jump to: navigation, search

AVM Wiki >> Tipps+Tricks >> Hochladen eines MySQL-Telefonbuchs @ whmf   -   IRC-Chat   -   hu nl it English
Community Modelle Zubehör Environment Konfiguration Shell-Befehle Software Freetz Lexikon Tipps+Tricks Bilder

Hardware-Tipps System-Tipps Netzwerk-Tipps Telefonie-Tipps Software-Tipps Sonstige Tipps Todo

Hochladen eines MySQL-Telefonbuchs

Nachdem mir das Verfahren mit Reboot nicht gepasst hat, habe ich in Ruby ein Skript geschrieben, welches die Daten (ebenfalls die XML-Datei) über die Weboberfläche hochlädt. Anbei das komplette Skript. Zum einen das Zustammenstellen der Daten (bei mir aus einer MySQL-Datenbank, da ich Funambol als zentralen Adressserver verwende), zum anderen die Logik zum Login und zum Hochladen der Daten.

Durch diese Art des Uploads entfällt dann auch der Reboot der FRITZ!Box

#!/usr/bin/ruby -w
# coding: utf-8

require "mysql"
require "builder"
require 'net/http'
require 'cgi'
require 'digest/md5'
require 'iconv'

def password 
  "SECRET_PASSWORD"
end

def get_phonebook_xml
  dbh = Mysql.real_connect("192.168.178.75", "DB_USER", password, "funambol")
  types = Hash["1", "home", "3", "mobile", "10", "work"] 
  buffer = ""
  xml = Builder::XmlMarkup.new(:target => buffer,  :indent => 2 )
  xml.instruct! :xml, :encoding => "UTF-8"
  xml.phonebook {
    res = dbh.query("SELECT id, first_name, last_name FROM fnbl_pim_contact WHERE userid='bd' AND (status='U' OR status ='N')")
    
    res.each_hash do |row|
      res2 = dbh.query("SELECT type, value FROM fnbl_pim_contact_item WHERE contact=#{row["id"]} AND (type=1 OR type=3 OR type=10)")
      if (res2.num_rows > 0)
	xml.contact {
          xml.category "0"
          xml.person {
            xml.realName row["first_name"] + " " + row["last_name"]
            xml.imageURL
            xml.telephony {
	      res2.each_hash do |row_item|
                
		# 1 - Home, 3 - Mobile, 10 - Work
		phone = row_item["value"].gsub(/[-() ]/, '').gsub(/\+49/, '0').gsub(/\+/, '00')
                if not phone.empty?
		  xml.number phone, "vanity"=>"", "prio"=>"0", "type"=>types[row_item["type"]]
                end
	      end
            } 
            xml.services
            xml.setup
            xml.mod_time Time.now.to_i
          }
        }
      end
      res2.free
    end

    res.free
  }
  
  buffer
rescue Mysql::Error => e
  puts "Error code: #{e.errno}"
  puts "Error message: #{e.error}"
  puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
ensure
  dbh.close if dbh
end

def text_to_multipart(key,value)
  return "Content-Disposition: form-data; name=\"#{CGI::escape(key)}\"\r\n" + 
         "\r\n" + 
         "#{value}\r\n"
end

def file_to_multipart(key,filename,mime_type,content)
  return "Content-Disposition: form-data; name=\"#{CGI::escape(key)}\"; filename=\"#{filename}\"\r\n" +
         "Content-Transfer-Encoding: binary\r\n" +
         "Content-Type: #{mime_type}\r\n" + 
         "\r\n" + 
         "#{content}\r\n"
end

def post_to_fritzbox
  h = Net::HTTP.new('fritz.box', 80)
  h.start do |http|
    response = http.get("/cgi-bin/webcm?getpage=../html/login_sid.xml")
    challenge = /<Challenge>(.*)<\/Challenge>/.match(response.body)[1]
    digest = Digest::MD5.new.hexdigest(Iconv.conv("UTF-16LE", "UTF-8", "#{challenge}-#{password}"))
    postdata ="login:command/response=#{challenge}-#{digest}&getpage=../html/de/menus/menu2.html"
    
    response = http.post("/cgi-bin/webcm", postdata, { 'Content-Type' => 'application/x-www-form-urlencoded' })
    sid = /name="sid" value="([A-Fa-f0-9]{16})"/.match(response.body)[1]
    
    params = [ 
      text_to_multipart('sid',sid),
      text_to_multipart('PhonebookId','0'),
      file_to_multipart('PhonebookImportFile','test.xml','text/xml', get_phonebook_xml)     
       ]
      
    boundary = '349832898984244898448024464570528145'
    query = params.collect {|p| '--' + boundary + "\r\n" + p}.join('') + "--" + boundary + "--\r\n"

    response = http.post2("/cgi-bin/firmwarecfg", query, "Content-type" => "multipart/form-data; boundary=" + boundary)
  end
end

post_to_fritzbox

28. März 2011 - Weisserd

Siehe auch

Telefonbuch: (39)