[Asterisk-Users] MySQL

[Dan]

Hi,

>----- Original Message ----- 
>From: "Christopher Wall" <chris at clearview-cs.com>
> I have been lookin for someplace to lean how to dump all of the call
> transactions into a sql database. Can anyone provide me any assistance?

If you want to import Master.csv, take a look at my mail posted on 2-Jul on
this list, at the end.

Best regards,
Dan

----- Original Message ----- 
From: "Dan" <dtoma at fx.ro>
To: <asterisk-users at lists.digium.com>
Sent: Wednesday, June 02, 2004 2:30 PM
Subject: Script to import Master.csv in the MySQL database - a short HowTo


> Hi,
>
> I hope this can help others, so this is it.
> Use it at your own risk. I have test it on 3 separate systems without any
> problem.
> Take care to edit the following files taking into consideration your own
> settings.
> If you have all the CDR info in the Master.csv too, then delete all the
data
> from  the 'cdr' table in MySQL before running the script bellow in oder to
> prevent dupplicate records.
> In my example, I have the following config:
> CDR database:        asteriskcdrdb
> CDR table:                cdr
> CVS file:              /var/log/asterisk/cdr-csv/Master.csv
>
>
> 1. Create a file named 'impcdr2sql' with the following content:
>
> #!/bin/bash
> # make a copy of the original Master.csv file to Master.csv.mod
> cp -vf /var/log/asterisk/cdr-csv/Master.csv
> /var/log/asterisk/cdr-csv/Master.csv.mod
> #  format the file to comply with the MySQL data (delete '"' chars when
need
> it)
> #  use a VIM script (nofielddelims.vim) for this purpose
> ex /var/log/asterisk/cdr-csv/Master.csv.mod -c ":source
> nofielddelims.vim" -c ":exit"
> # run the MySQL commands from the cmd.sql file
> mysql < cmd.sql
>
> 2. Enter the command to make the script executable:
>
> chmod 755 impcdr2sql
>
> 3. Create a file named 'nofielddelims.vim' with the following content:
>
> "
> " Delete '"' chars at the beginning of the line
> "
> :%s/^"//
> "
> " Delete '"' chars at the end of the line
> "
> :%s/"$//
> "
> " Delete '"' chars near the ',' char
> "
> :%s/",/,/g
> :%s/,"/,/g
> "
> " Replace '""' by '"'
> "
> :%s/""/"/g
>
>
> 4.  Create a file named 'cmd.sql' with the following content:
>
> use asteriskcdrdb;
> ALTER TABLE `cdr` ADD `tmp1` VARCHAR(30)  DEFAULT "x" NOT NULL;
> ALTER TABLE `cdr` ADD `tmp2` VARCHAR(30)  DEFAULT "y" NOT NULL;
> LOAD DATA INFILE '/var/log/asterisk/cdr-csv/Master.csv.mod'
> replace INTO TABLE cdr
> FIELDS TERMINATED BY ','
> LINES TERMINATED BY '\n'
>
(accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,calld
> ate,tmp1,tmp2,duration,billsec,disposition,amaflags,uniq
> ueid,userfield);
> ALTER TABLE `cdr` DROP `tmp1`;
> ALTER TABLE `cdr` DROP `tmp2`;
>
>
> 5. Keep all the files in the same directory.
> All you need to do is to run the script:
>
> ./impcdr2sql
>
> as root or as an user with full rights on the asteriskcdrdb database and
cdr
> table
> E... voila!
> All your old data from Master.csv is now in the MySQL database in the
> correct format (I hope).
>
>
> Please feel free to make any improovments you want.
> I'm not a Linux expert.
>
> Best regards to you all,
> Dan
>