Overview
I want populate rows in a table of one database from rows in a table from another database.
Target
The unbound cdr table of the pbxtra database
Source
The radacct table of radius database with these columns:
radactt* Table Fields
radacctid
data_type: 'bigint' is_auto_increment: 1 is_nullable: 0
acctsessionid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
acctuniqueid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
username
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 64
realm
data_type: 'varchar' default_value: (empty string) is_nullable: 1 size: 64
SELECT radacctid, acctsessionid, acctuniqueid, username, realm
FROM radacct201005 LIMIT 5;
+-----------+----------------------------------+------------------+----------+-------+ | radacctid | acctsessionid | acctuniqueid | username | realm | +-----------+----------------------------------+------------------+----------+-------+ | 1 | QXBzNITWHtriWguYURmC6WVCffIzALUz | 517d80a86c04e303 | | | | 2 | N3T9iu6aoX0FWkF9UKDYlEoRBvGLp9L- | 88f63fbc15c7af2c | | | | 3 | IvLAYLwfDSLdIrP1sdl7YYH55lcWDJew | 4f33026bd79946a2 | | | | 4 | X291MTs-fxEBnS2vV2jFJiQFYw9Ao7ld | 4cfd5f4e624c30b9 | | | | 5 | SE5GKiGj9SMTzxPMMm-ETwYeJ9Rdh1FH | 2e6b986dd44c61fb | | | +-----------+----------------------------------+------------------+----------+-------+
nasipaddress
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 15
nasportid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 50
nasporttype
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
select nasipaddress, nasportid, nasporttype
from radacct201005 limit 5;
+--------------+-----------+-------------+ | nasipaddress | nasportid | nasporttype | +--------------+-----------+-------------+ | | 5060 | | | | 5060 | | | | 5060 | | | | 5060 | | | | 5060 | | +--------------+-----------+-------------+
acctstarttime
data_type: 'datetime' default_value: '0000-00-00 00:00:00' is_nullable: 0
acctstoptime
data_type: 'datetime' default_value: '0000-00-00 00:00:00' is_nullable: 0
acctsessiontime
data_type: 'integer' is_nullable: 1
acctauthentic
data_type: 'varchar' is_nullable: 1 size: 32
select acctstarttime, acctstoptime, acctsessiontime, acctauthentic
from radacct201005 limit 5;
+---------------------+---------------------+-----------------+---------------+ | acctstarttime | acctstoptime | acctsessiontime | acctauthentic | +---------------------+---------------------+-----------------+---------------+ | 2010-05-06 22:00:37 | 2010-05-06 22:00:45 | 8 | NULL | | 2010-05-06 22:16:28 | 2010-05-06 22:16:33 | 5 | NULL | | 2010-05-06 22:17:49 | 2010-05-06 22:18:02 | 13 | NULL | | 2010-05-06 22:37:14 | 2010-05-06 22:37:26 | 12 | NULL | | 2010-05-06 22:38:40 | 2010-05-06 22:38:52 | 12 | NULL | +---------------------+---------------------+-----------------+---------------+
connectinfo_start
data_type: 'varchar' is_nullable: 1 size: 32
connectinfo_stop
data_type: 'varchar' is_nullable: 1 size: 32
acctinputoctets
data_type: 'bigint' is_nullable: 1
acctoutputoctets
data_type: 'bigint' is_nullable: 1
select connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets
from radacct201005 limit 5;
+-------------------+------------------+-----------------+------------------+ | connectinfo_start | connectinfo_stop | acctinputoctets | acctoutputoctets | +-------------------+------------------+-----------------+------------------+ | NULL | | 0 | 0 | | NULL | | 0 | 0 | | NULL | | 0 | 0 | | NULL | | 0 | 0 | | NULL | | 0 | 0 | +-------------------+------------------+-----------------+------------------+
calledstationid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 50
callingstationid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 50
acctterminatecause
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 32
servicetype
data_type: 'varchar' is_nullable: 1 size: 32
enumtld
data_type: 'varchar' is_nullable: 1 size: 64
framedipaddress
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 15
select calledstationid, callingstationid, acctterminatecause, servicetype, enumtld, framedipaddress
from radacct201005 limit 5;
+--------------------------------------------+-----------------------+--------------------+-------------+---------+-----------------+ | calledstationid | callingstationid | acctterminatecause | servicetype | enumtld | framedipaddress | +--------------------------------------------+-----------------------+--------------------+-------------+---------+-----------------+ | 0061284842600@sip-proxy1.syd1.fonality.com | 5978@sip.fonality.com | 200 | outgoing | | | | 0061284842600@sip-proxy1.syd1.fonality.com | 5978@sip.fonality.com | 200 | outgoing | | | | 0061284842600@sip-proxy1.syd1.fonality.com | 5978@sip.fonality.com | 200 | outgoing | | | | 0061284842600@sip-proxy1.syd1.fonality.com | 5978@sip.fonality.com | 200 | outgoing | | | | 0061284842600@sip-proxy1.syd1.fonality.com | 5978@sip.fonality.com | 200 | outgoing | | | +--------------------------------------------+-----------------------+--------------------+-------------+---------+-----------------+
select distinct acctterminatecause, servicetype from radacct201005;
+--------------------+-------------+ | acctterminatecause | servicetype | +--------------------+-------------+ | 200 | outgoing | | 483 | transit | | 502 | outgoing | | 503 | outgoing | | 487 | transit | | 403 | transit | | 200 | transit | | 487 | outgoing | | 488 | outgoing | | 480 | outgoing | | 401 | transit | | 484 | outgoing | | 404 | transit | | 404 | outgoing | +--------------------+-------------+
acctstartdelay
data_type: 'integer' is_nullable: 1
acctstopdelay
data_type: 'integer' is_nullable: 1
select acctstartdelay, acctstopdelay
from radacct201005 limit 5;
+----------------+---------------+ | acctstartdelay | acctstopdelay | +----------------+---------------+ | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | +----------------+---------------+
sipmethod
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 50
sipresponsecode
data_type: 'smallint'
default_value: 0
extra: {unsigned => 1}
is_nullable: 0
siptotag
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 128
sipfromtag
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 128
siptranslatedrequesturi
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
select sipmethod, sipresponsecode, siptotag, sipfromtag, siptranslatedrequesturi
from radacct201005 limit 5;
+-----------+-----------------+------------+----------------------------------+------------------------------------------+ | sipmethod | sipresponsecode | siptotag | sipfromtag | siptranslatedrequesturi | +-----------+-----------------+------------+----------------------------------+------------------------------------------+ | Invite | 200 | as7971a703 | .IlD6kxa6oGMRLEtt3GkwoKiayx5nM3j | 0061284842600@s3699x.pbxtra.fonality.com | | Invite | 200 | as252e4c56 | LA803r6J52eKXWCT2VmhF4V-VJ0yuPHD | 0061284842600@s3699x.pbxtra.fonality.com | | Invite | 200 | as1731202e | QGViEVcY8CG-1Pib6HV2PgER5ZQpbSgy | 0061284842600@s3699x.pbxtra.fonality.com | | Invite | 200 | as03222299 | 02JtufYAz1elJ-vU1WXs76bO2hwt7ZOC | 0061284842600@s3699x.pbxtra.fonality.com | | Invite | 200 | as29584c39 | xrINMPsbmBjY12zCFffNtpsP45eQpVG9 | 0061284842600@s3699x.pbxtra.fonality.com | +-----------+-----------------+------------+----------------------------------+------------------------------------------+
sipuseragents
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
sipapplicationtype
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
sipcodecs
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
siprpid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
siprpidheader
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
select sipuseragents, sipapplicationtype, sipcodecs, siprpid, siprpidheader
from radacct201005 limit 5;
Result: EMPTY
sourceip
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
sourceport
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
canonicaluri
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
delaytime
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 5
timestamp
data_type: 'bigint' default_value: 0 is_nullable: 0
destinationid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 15
select sourceip, sourceport, canonicaluri, delaytime, timestamp, destinationid from radacct201005 limit 5;
+----------+------------+------------------------------------------+-----------+-----------+---------------+ | sourceip | sourceport | canonicaluri | delaytime | timestamp | destinationid | +----------+------------+------------------------------------------+-----------+-----------+---------------+ | | | 0061284842600@s3699x.pbxtra.fonality.com | | 0 | | | | | 0061284842600@s3699x.pbxtra.fonality.com | | 0 | | | | | 0061284842600@s3699x.pbxtra.fonality.com | | 0 | | | | | 0061284842600@s3699x.pbxtra.fonality.com | | 0 | 6128 | | | | 0061284842600@s3699x.pbxtra.fonality.com | | 0 | 6128 | +----------+------------+------------------------------------------+-----------+-----------+---------------+ Distinct canonicaluri 's mysql> select distinct canonicaluri from radacct201005; +------------------------------------------+ | canonicaluri | +------------------------------------------+ | | | 001113104807972@125.213.160.145 | | 001113106937425@125.213.160.145 | | 001113106937437@125.213.160.145 | | 001113108614300@125.213.160.145 | | 001161284842601@125.213.160.145 | | 00611113104807972@125.213.160.145 | | 00611113108614300@125.213.160.145 | | 0061243048100@125.213.160.145 | | 0061243048150@125.213.160.145 | | 0061243048153@74.122.117.84 | | 0061280714300@74.122.117.84 | | 0061280714352@74.122.117.84 | | 0061280714369@74.122.117.84 | | 0061284842499@125.213.160.145 | | 0061284842600@125.213.160.145 | | 0061284842600@s3699x.pbxtra.fonality.com | | 0061284842601@125.213.160.145 | | 0061284842601@s3699x.pbxtra.fonality.com | | 0061284842602@125.213.160.145 | | 0061284842691@125.213.160.145 | | 0061284842699@125.213.160.145 | | 0061284842699@s3699x.pbxtra.fonality.com | | 0061296398148@125.213.160.145 | | 0061297978058@125.213.160.145 | | 0061418763945@125.213.160.145 | | 0061419987375@125.213.160.145 | | 1300651656@125.213.160.145 | | 1310300651656@125.213.160.145 | | 132221@125.213.160.145 | | 19284842699@125.213.160.145 | +------------------------------------------+ 31 rows in set (0.19 sec) select distinct sourceip, sourceport, delaytime, timestamp, destinationid from radacct201005; Distinct on sourceip, sourceport, delaytime, timestamp and destination_id +----------+------------+-----------+-----------+---------------+ | sourceip | sourceport | delaytime | timestamp | destinationid | +----------+------------+-----------+-----------+---------------+ | | | | 0 | | | | | | 0 | 6128 | | | | | 0 | 6129 | | | | | 0 | 61 | | | | | 0 | 1 | | | | | 0 | 61419 | | | | | 0 | 61418 | +----------+------------+-----------+-----------+---------------+ 7 rows in set (0.20 sec)
rate
data_type: 'text' is_nullable: 0
price
data_type: 'double precision' is_nullable: 1 size: [20,4]
normalized
data_type: 'enum'
default_value: 0
extra: {list => [0,1]}
is_nullable: 1
billingid
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 255
select distinct rate, price, normalized, billingid from radacct201005;
+------+--------+------------+-----------+ | rate | price | normalized | billingid | +------+--------+------------+-----------+ | | 0.0000 | 1 | 0 | | | NULL | 1 | | | | NULL | 1 | 0 | | | 0.0000 | 1 | | +------+--------+------------+-----------+
mediainfo
data_type: 'varchar' is_nullable: 1 size: 32
rtpstatistics
data_type: 'text' is_nullable: 0
fromheader
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 128
useragent
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 128
contact
data_type: 'varchar' default_value: (empty string) is_nullable: 0 size: 128
select distinct mediainfo, rtpstatistics, fromheader, useragent, contact from radacct201005;
+-----------+---------------+------------+----------------------------------+---------+ | mediainfo | rtpstatistics | fromheader | useragent | contact | +-----------+---------------+------------+----------------------------------+---------+ | NULL | | | blink-0.18.1 | | | NULL | | | ENSR2.5.4 | | | NULL | | | X-Lite release 1014k stamp 56015 | | | | | | blink-0.18.1 | | | | | | ENSR2.5.4 | | | NULL | | | Asterisk PBX 1.6.0.26-samy-r76 | | | | | | Asterisk PBX 1.6.0.26-samy-r76 | | +-----------+---------------+------------+----------------------------------+---------+
Showing changes from previous revision. Removed | Added
