mysql import fara duplicate

la treaba asta nu te mai pot ajuta, că nu mă pricep, dar îți pot da un punct de pornire

cred ca o sa abandonez faza cu postgres deoarece pe un alt server de pe care trebuie sa interoghez baza de date va trebui sa instalez postgres ca si client si nu am voie sa fac asta din cauza licentei asa ca ramanem pe mysql si este totul este in regula. am pus si cronurile aseara si si-a facut update rapid. cel mai greu a fost la baza de date initiala unde am avut 1.800.000 randuri care a durat vreo 4 ore

Din cauza licentei?

da da din cauza licentei (adica softul e pus de cineva si nu am voie sa intru pe server sa fac ce vreau eu) .
in fine am asa o mica paranteza legata de query

mysql> SELECT routinginfo FROM portare WHERE numberto = '075XXXXXXX' ;
+-------------+
| routinginfo |
+-------------+
| 18000    |
+-------------+
1 row in set (0.00 sec)

si vreau cu trim sa tai zero ala din fata la 755 cand fac selectul . mai exact ca si cum asa face SELECT routinginfo FROM portare WHERE numberto = '75XXXXXXX' ; si sa dea acelasi raspuns numai ca in baza de date numerele sunt cu 0 in fata

revin la script cred ca cel mai bine ar fi sa fac normalizarea direct in e164 si atunci daca colegul care mi-a facut scriptuletul m-ar ajuta sa pun un 4 in fata la numarul importat in baza de date direct si atunci s-ar simplifica lururile

la numberfrom si numberto sa fie in baza de date cu 407XXXXX desi in xml le am cu 07XXXXX

înlocuiești asta

foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  $phoneItem->numberfrom,
  $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass));
}

cu asta

foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  '4' . $phoneItem->numberfrom,
  '4' . $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  '4' . $phoneItem->numberfrom,
  '4' . $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass));
}

super simplu daca stii cine stie cunoaste

am testat si functioneaza asa cum am vrut si a usurat cu mult partea de rutare implicit si resursele

revin cu o chestie . oare putem pune o conditie daca in xml gaseste la routinginfo 18000 strict sa stearga din baza de date randul respectiv. sa iti explic si de ce
daca un numar la un moment dat se porteaza i se pune in acea baza de date un routinginfo 187XX adica numberto 07xxxxxx si numberto 07xxxxxxx si routinginfo 187xx iar la un moment dat omul renunta la abonament furnizorul acceptor ii introduce in baza de date routinginfo 18000 si numarul se intoarce in reteaua de origine iar in acel moment eu trebuie sa il sterg din baza de date pentru a il ruta corect.

adaugi asta la sfarsitul scriptului

$sqlRouting = "DELETE FROM portare WHERE routinginfo=18000";
$db->exec($sqlRouting);
$db = null;

merge e ok acuma si complet face tot ceea ce trebuie
scrie-mi pm cum pot sa te rasplatesc pentru efort

oare putem pune pe el un log undeva la /var/log/update_log.log cu cate a facut update si cate a sters

$countProc = 0;
foreach($phoneList->synchronization as $phoneItem)
{
  $stmt->execute(array(
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  '4' . $phoneItem->numberfrom,
  '4' . $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass,
  $phoneItem->datetime,
  $phoneItem->messagetype,
  $phoneItem->idnumber,
  $phoneItem->donorid,
  $phoneItem->recipientid,
  '4' . $phoneItem->numberfrom,
  '4' . $phoneItem->numberto,
  $phoneItem->routinginfo,
  $phoneItem->caseclass));
$countProc++;
}

$sqlRouting = "DELETE FROM portare WHERE routinginfo=18000";
$countDel = $db->exec($sqlRouting);
$db = null;

$dumpFile = '/var/log/update_log.log'; // aici modificu tu in functie de nevoi
$dumphandle = fopen($dumpFile, 'a');
$dumpString = $countProc . ' entries processed and ' . $countDel . ' entries deleted. Job done - ' . date("M d Y H:i:s", time()) . "\n";
fwrite($dumphandle, $dumpString);
fclose($dumphandle);

PHP Notice:  Undefined variable: countDel in /home/portare_halfday.php on line 56
PHP Warning:  date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/portare_halfday.php on line 56

merge de mers dar da notice-ul asta

1404 entries processed and  entries deleted. Job done - Apr 02 2015 10:42:23

adaugi asta la începutul scriptului

date_default_timezone_set('Europe/Bucharest');

pe urmă, după linia

$countDel = $db->exec($sqlRouting);

adaugi

if (is_bool($countDel) === true) $countDel = 0;

am rezolvat. am pus in php.ini date.timezone = Europe/Bucharest

cu linia asta m-am prins ca pune 0 daca nu sterge nimic if (is_bool($countDel) === true) $countDel = 0;

<?php
$xmlFeed='http://195.ccccc/sync/syncfullday.xml';
$phoneList = simplexml_load_file($xmlFeed);
date_default_timezone_set('Europe/Bucharest');
$dbhost='localhost';
$dbuser='xxx';
$dbpass='xxxx';
$dbname='xxxxx';
try
{
$db = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8",$dbuser,$dbpass,array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
catch (PDOException $err)
{
echo "There was an error. <br>\n" . $err;
die;
}
$sql_str ="INSERT INTO portare (datetime, messagetype, idnumber, donorid, recipientid, numberfrom, numberto, routinginfo, caseclass) ";
$sql_str .="VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ";
$sql_str .="ON DUPLICATE KEY UPDATE datetime=?, messagetype=?, idnumber=?, donorid=?, recipientid=?, numberfrom=?, numberto=?, routinginfo=?, caseclass=?";
$stmt = $db->prepare($sql_str);
$countProc = 0;
foreach($phoneList->synchronization as $phoneItem)
{
$stmt->execute(array(
$phoneItem->datetime,
$phoneItem->messagetype,
$phoneItem->idnumber,
$phoneItem->donorid,
$phoneItem->recipientid,
'4' . $phoneItem->numberfrom,
'4' . $phoneItem->numberto,
$phoneItem->routinginfo,
$phoneItem->caseclass,
$phoneItem->datetime,
$phoneItem->messagetype,
$phoneItem->idnumber,
$phoneItem->donorid,
$phoneItem->recipientid,
'4' . $phoneItem->numberfrom,
'4' . $phoneItem->numberto,
$phoneItem->routinginfo,
$phoneItem->caseclass));
$countProc++;
}
$sqlRouting = "DELETE FROM portare WHERE routinginfo=18000";
$countDel = $db->exec($sqlRouting);
if (is_bool($countDel) === true) $countDel = 0;
$db = null;
$dumpFile = '/var/log/update_log.log'; // aici modificu tu in functie de nevoi
$dumphandle = fopen($dumpFile, 'a');
$dumpString = $countProc . ' entries processed and ' . $countDel . ' entries deleted. Job done - ' . date("M d Y H:i:s", time()) . "\n";
fwrite($dumphandle, $dumpString);
fclose($dumphandle);

asta e varianta finala

Edited by arsenieciprian, 02 April 2015 - 13:29.

salut si revin din nou cu o problema abia descoperita.
la numberfrom si numberto este o greseala care initial eu am crezut ca sunt 2 campuri identice ceea ce e gresit sunt identice doar in cazul in care exista doar un singur numar de telefon iar daca sunt mai multe deoadata este alceva de exemplu campul acela poate fi numberfrom 407XXXX1 si la numberto 407XXXXX6 ceea ce insemana ca eu trebuie sa introduc in db toate numerele de la 1 la 6 si trebuie modificata si structura bazei de date totodata cu aceasta.
o sa dau exemple
te rog ajuta+ma din nou

<sync:synchronizationinfo xmlns:sync="http://XXXXXXXXX">
<period>
<startdate>2015-08-05T09:00:00</startdate>
<enddate>2015-08-05T18:00:00</enddate>
</period>
<synchronization>
<datetime>2015-08-05T09:06:27</datetime>
<messagetype>npbroadcast</messagetype>
<idnumber>P201XXXX</idnumber>
<donorid>COSM</donorid>
<recipientid>RCSF</recipientid>
<numberfrom>076XXXXXX4</numberfrom>
<numberto>076XXXXXXXX4</numberto>
<routinginfo>18770</routinginfo>
<caseclass>mobile</caseclass>
</synchronization>
<synchronization>
<datetime>2015-08-05T09:12:50</datetime>
<messagetype>npbroadcast</messagetype>
<idnumber>P20XXXXXXX9</idnumber>
<donorid>COSM</donorid>
<recipientid>RCSF</recipientid>
<numberfrom>076XXXXXX1</numberfrom>
<numberto>076XXXXXX7</numberto>
<routinginfo>18770</routinginfo>
<caseclass>mobile</caseclass>
</synchronization>




+---------------------+-------------+----------+---------+-------------+-------------+---------+-----------+
| datetime     | messagetype | idnumber | donorid | recipientid | routinginfo | number  | caseclass |
+---------------------+-------------+----------+---------+-------------+-------------+---------+-----------+
| 2015-08-06 14:18:38 | NULL     | NULL | RCSF    | RTLC     | 18XXX    | 03XXXX | GEO    |
+---------------------+-------------+----------+---------+-------------+-------------+---------+-----------+

asta e varianta de baza de date ultima



+---------------------+---------+-------------+-------------+-------------+------------+
| DateTime     | DonorId | RecipientId | Number   | RoutingInfo | NumberType |
+---------------------+---------+-------------+-------------+-------------+------------+
| 2015-08-07 11:35:34 | ORNG    | VODA     | 40753409409 | 18720    | MOBILE |
+---------------------+---------+-------------+-------------+-------------+------------+

Edited by arsenieciprian, 07 August 2015 - 10:45.