func Oracle_to_PG(conf *SABModules.Config_STR, pg_minsert int) int {
var (
ckl_servers int
num_servers int
ckl = int(0)
insert_exec = int(0)
row_comment string
row_tm string
row_number string
row_fname string
queryx string
pg_Query_Create = string(`
CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
(
server character varying(255),
uid bytea, phone character varying(255),
comment character varying(255),
tm character varying(5), visible character varying(5), type integer, fname character varying(255));
`)
pg_Query_Create_Status = string(`
CREATE TABLE IF NOT EXISTS XYZWorkTableZYX
(server character varying(255), status character varying(255),
primary key (server));
`)
return_result = int(0)
)
log.Printf("Oracle export to PG...")
num_servers = len(conf.Oracle_SRV)
db, err := sql.Open("postgres", conf.PG_DSN)
if err != nil {
log.Printf("PG::Open() error: %v\n", err)
return 10
}
defer db.Close()
queryx = strings.Replace(pg_Query_Create_Status, "XYZWorkTableZYX", SABDefine.PG_Table_Oracle_Status, -1)
_, err = db.Query(queryx)
if err != nil {
log.Printf("PG::Query() Create table error: %v\n", err)
return 11
}
queryx = strings.Replace(pg_Query_Create, "XYZWorkTableZYX", SABDefine.PG_Table_Oracle, -1)
_, err = db.Query(queryx)
if err != nil {
log.Printf("PG::Query() Create table error: %v\n", err)
return 12
}
for ckl_servers = 0; ckl_servers < num_servers; ckl_servers++ {
log.Printf("\t\tServer %2d of %2d / Pass 1 of 1 / Server name: %s\n", ckl_servers+1, num_servers, conf.Oracle_SRV[ckl_servers][3])
cx, err := oracle.NewConnection(conf.Oracle_SRV[ckl_servers][0], conf.Oracle_SRV[ckl_servers][1], conf.Oracle_SRV[ckl_servers][2], false)
if err != nil {
log.Printf("Oracle::Connection() error: %v\n", err)
continue
}
defer cx.Close()
cu := cx.NewCursor()
defer cu.Close()
err = cu.Execute(SABDefine.Oracle_QUE, nil, nil)
if err != nil {
log.Printf("Oracle::Execute() error: %v\n", err)
continue
}
rows, err := cu.FetchMany(pg_minsert)
queryx = fmt.Sprintf("delete from %s where server='%s';", SABDefine.PG_Table_Oracle, conf.Oracle_SRV[ckl_servers][3])
// log.Printf("%s\n", queryx)
_, err = db.Query(queryx)
if err != nil {
log.Printf("PG::Query() Clean table error: %v\n", err)
return 13
}
timenow := time.Now().Format("2006.01.02 15:04:05")
queryx = fmt.Sprintf("INSERT INTO %s (server, status) select '%s', '%s' where not exists (select server from %s where server='%s'); update %s set status='%s' where server='%s'; ", SABDefine.PG_Table_Oracle_Status, conf.Oracle_SRV[ckl_servers][3], timenow, SABDefine.PG_Table_Oracle_Status, conf.Oracle_SRV[ckl_servers][3], SABDefine.PG_Table_Oracle_Status, timenow, conf.Oracle_SRV[ckl_servers][3])
// log.Printf("%s\n", queryx)
_, err = db.Query(queryx)
if err != nil {
log.Printf("%s\n", queryx)
log.Printf("PG::Query() Create table error: %v\n", err)
return 14
}
ckl = 0
for err == nil && len(rows) > 0 {
for _, row := range rows {
if ckl < 1 {
queryx = ""
}
if fmt.Sprintf("%s", row[4]) != "%!s(<nil>)" {
row_comment = fmt.Sprintf("%s", row[4])
} else {
row_comment = ""
}
if fmt.Sprintf("%s", row[5]) != "%!s(<nil>)" {
row_tm = fmt.Sprintf("%s", row[5])
} else {
row_tm = "X"
}
if fmt.Sprintf("%s", row[8]) != "%!s(<nil>)" {
row_fname = SABModules.TextMutation(fmt.Sprintf("%s", row[8]))
} else {
row_fname = ""
}
if fmt.Sprintf("%s", row[1]) != "%!s(<nil>)" && fmt.Sprintf("%s", row[2]) != "%!s(<nil>)" {
return_result = 94
switch fmt.Sprintf("%d", row[7]) {
case "1":
row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
row_number = strings.Replace(fmt.Sprintf("+7%s%s", row[1], row[2]), " ", "", -1)
row_number = SABModules.PhoneMutation(row_number)
case "2":
row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
row_number = strings.Replace(fmt.Sprintf("8%s%s", row[1], row[2]), " ", "", -1)
row_number = SABModules.PhoneMutation(row_number)
case "3":
if fmt.Sprintf("%s", row[3]) != "%!s(<nil>)" {
row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
row[1] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[1]))
row[2] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[2]))
row[3] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[3]))
row_number = strings.Replace(fmt.Sprintf("8(%s)%sдоб.%s", row[1], row[2], row[3]), " ", "", -1)
row_number = strings.Replace(row_number, "доб.", " доб.", -1)
} else {
row[1] = strings.Replace(fmt.Sprintf("%s", row[1]), "+7", "", -1)
row[1] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[1]))
row[2] = SABModules.PhoneMutation(fmt.Sprintf("%s", row[2]))
row_number = strings.Replace(fmt.Sprintf("8(%s)%s", row[1], row[2]), " ", "", -1)
}
default:
insert_exec = 1
}
} else {
insert_exec = 1
}
if insert_exec == 0 {
queryx = fmt.Sprintf("%sinsert into %s (server, uid, phone, comment, tm, visible, type, fname) select '%s','%v','%s','%s','%s','%s','%d','%s' where not exists (select uid from %s where uid='%v' and phone='%s'); ", queryx, SABDefine.PG_Table_Oracle, conf.Oracle_SRV[ckl_servers][3], row[0], row_number, row_comment, row_tm, row[6], row[7], row_fname, SABDefine.PG_Table_Oracle, row[0], row_number)
// fmt.Printf("%s\n", queryx)
if ckl >= pg_minsert-1 {
// log.Printf("%s\n\n", queryx)
_, err = db.Query(queryx)
if err != nil {
log.Printf("%s\n", queryx)
log.Printf("PG::Query() insert error: %v /// %s\n", err, queryx)
}
queryx = ""
ckl = 0
} else {
ckl++
}
}
insert_exec = 0
}
rows, err = cu.FetchMany(pg_minsert)
}
// log.Printf("%s\n\n", queryx)
_, err = db.Query(queryx)
if err != nil {
log.Printf("%s\n", queryx)
log.Printf("PG::Query() Insert into table error: %v\n", err)
}
}
log.Printf("\tComplete")
return return_result
}
func main() {
const (
pName = string("SABook AsteriskCIDUpdater")
pVer = string("3 2015.09.10.21.10")
)
var (
def_config_file = string("./AsteriskCIDUpdater.json") // Default configuration file
def_log_file = string("/var/log/ABook/AsteriskCIDUpdater.log") // Default log file
def_daemon_mode = string("NO") // Default start in foreground
sqlite_key string
sqlite_value string
pg_name string
pg_phone string
pg_array [100000][3]string
sq_array [100000][3]string
pg_array_len = int(0)
sq_array_len = int(0)
ckl1 = int(0)
ckl2 = int(0)
ckl_status = int(0)
ast_cmd string
rconf SABModules.Config_STR
sql_mode = int(0)
query string
)
fmt.Printf("\n\t%s V%s\n\n", pName, pVer)
rconf.LOG_File = def_log_file
def_config_file, def_daemon_mode = SABModules.ParseCommandLine(def_config_file, def_daemon_mode)
// log.Printf("%s %s %s", def_config_file, def_daemon_mode, os.Args[0])
SABModules.ReadConfigFile(def_config_file, &rconf)
sqlite_select := fmt.Sprintf("SELECT key, value FROM astdb where key like '%%%s%%';", rconf.AST_CID_Group)
pg_select := fmt.Sprintf("select x.cid_name, y.phone from ldapx_persons x, ldapx_phones y, (select a.phone, count(a.phone) as phone_count from ldapx_phones as a, ldapx_persons as b where a.pers_id=b.uid and b.contract=0 and a.pass=2 and b.lang=1 group by a.phone order by a.phone) as subq where x.uid=y.pers_id and y.pass=2 and x.lang=1 and subq.phone=y.phone and subq.phone_count<2 and y.phone like '%s%%' and x.contract=0 group by x.cid_name, y.phone order by y.phone;", rconf.AST_Num_Start)
SABModules.Pid_Check(&rconf)
SABModules.Pid_ON(&rconf)
SABModules.Log_ON(&rconf)
log.Printf(".")
log.Printf("..")
log.Printf("...")
log.Printf("-> %s V%s", pName, pVer)
log.Printf("--> Go!")
db, err := sql.Open("sqlite3", rconf.AST_SQLite_DB)
if err != nil {
log.Printf("SQLite3::Open() error: %v\n", err)
return
}
defer db.Close()
dbpg, err := sql.Open("postgres", rconf.PG_DSN)
if err != nil {
log.Printf("PG::Open() error: %v\n", err)
return
}
defer dbpg.Close()
dbast, err := net.Dial("tcp", fmt.Sprintf("%s:%d", rconf.AST_ARI_Host, rconf.AST_ARI_Port))
if err != nil {
log.Printf(".")
log.Printf("Asterisk ARI::Dial() error: %v", err)
log.Printf("\tWorking in SQL SQLite mode!")
log.Printf(".")
sql_mode = 1
}
if sql_mode == 0 {
defer dbast.Close()
}
ast_gami := gami.NewAsterisk(&dbast, nil)
ast_get := make(chan gami.Message, 10000)
if sql_mode == 0 {
err = ast_gami.Login(rconf.AST_ARI_User, rconf.AST_ARI_Pass)
if err != nil {
log.Printf("Asterisk ARI::Login() error: %v\n", err)
return
}
}
rows, err := db.Query(sqlite_select)
if err != nil {
log.Printf("SQLite3::Query() error: %v\n", err)
return
}
for rows.Next() {
err = rows.Scan(&sqlite_key, &sqlite_value)
if err != nil {
log.Printf("rows.Scan error: %v\n", err)
return
}
sq_array[sq_array_len][0] = sqlite_key
sq_array[sq_array_len][1] = sqlite_value
sq_array[sq_array_len][2] = SABModules.PhoneMutation(sqlite_key)
sq_array_len++
}
rows, err = dbpg.Query(pg_select)
if err != nil {
log.Printf("PG::Query() error: %v\n", err)
return
}
pg_array_len = 0
for rows.Next() {
err = rows.Scan(&pg_name, &pg_phone)
if err != nil {
log.Printf("rows.Scan error: %v\n", err)
return
}
pg_array[pg_array_len][0] = fmt.Sprintf("/%s/%s", rconf.AST_CID_Group, pg_phone)
pg_array[pg_array_len][1] = pg_name
pg_array[pg_array_len][2] = SABModules.PhoneMutation(pg_phone)
pg_array_len++
}
for ckl1 = 0; ckl1 < sq_array_len; ckl1++ {
ckl_status = 0
for ckl2 = 0; ckl2 < pg_array_len; ckl2++ {
if sq_array[ckl1][0] == pg_array[ckl2][0] && sq_array[ckl1][1] == pg_array[ckl2][1] {
ckl_status = 1
break
}
}
if ckl_status == 0 {
if sql_mode == 0 {
ast_cmd = fmt.Sprintf("database del %s %s", rconf.AST_CID_Group, sq_array[ckl1][2])
log.Printf("\t- %s\n", ast_cmd)
ast_cb := func(m gami.Message) {
ast_get <- m
}
err = ast_gami.Command(ast_cmd, &ast_cb)
if err != nil {
log.Printf("Asterisk ARI::Command() error: %v\n", err)
return
}
for x1, x2 := range <-ast_get {
if x1 == "ActionID" || x1 == "CmdData" || x1 == "Usage" {
log.Printf("\t\t\t%s\n", x2)
}
}
} else {
query = fmt.Sprintf("delete from astdb where key='%s';", sq_array[ckl1][0])
log.Printf("\t- %s\n", query)
_, err := db.Exec(query)
if err != nil {
log.Printf("SQLite3::Query() DEL error: %v\n", err)
return
}
}
}
}
for ckl1 = 0; ckl1 < pg_array_len; ckl1++ {
ckl_status = 0
for ckl2 = 0; ckl2 < sq_array_len; ckl2++ {
if pg_array[ckl1][0] == sq_array[ckl2][0] && pg_array[ckl1][1] == sq_array[ckl2][1] {
ckl_status = 1
break
}
}
if ckl_status == 0 {
if sql_mode == 0 {
ast_cmd = fmt.Sprintf("database del %s %s", rconf.AST_CID_Group, pg_array[ckl1][2])
log.Printf("\t- %s\n", ast_cmd)
ast_cb := func(m gami.Message) {
ast_get <- m
}
err = ast_gami.Command(ast_cmd, &ast_cb)
if err != nil {
log.Printf("Asterisk ARI::Command() error: %v\n", err)
return
}
for x1, x2 := range <-ast_get {
if x1 == "ActionID" || x1 == "CmdData" || x1 == "Usage" {
log.Printf("\t\t\t%s\n", x2)
}
}
ast_cmd = fmt.Sprintf("database put %s %s \"%s\"", rconf.AST_CID_Group, pg_array[ckl1][2], pg_array[ckl1][1])
log.Printf("\t+ %s\n", ast_cmd)
ast_cb = func(m gami.Message) {
ast_get <- m
}
err = ast_gami.Command(ast_cmd, &ast_cb)
if err != nil {
log.Printf("Asterisk ARI::Command() error: %v\n", err)
return
}
for x1, x2 := range <-ast_get {
if x1 == "ActionID" || x1 == "CmdData" || x1 == "Usage" {
log.Printf("\t\t\t%s\n", x2)
}
}
} else {
query = fmt.Sprintf("delete from astdb where key='%s';", pg_array[ckl1][0])
log.Printf("\t- %s\n", query)
_, err := db.Exec(query)
if err != nil {
log.Printf("SQLite3::Query() DEL error: %v\n", err)
return
}
query = fmt.Sprintf("insert into astdb (key,value) values ('%s','%s');", pg_array[ckl1][0], pg_array[ckl1][1])
log.Printf("\t+ %s\n", query)
_, err = db.Exec(query)
if err != nil {
log.Printf("SQLite3::Query() INS error: %v\n", err)
return
}
}
}
}
log.Printf("...")
log.Printf("..")
log.Printf(".")
SABModules.Log_OFF()
SABModules.Pid_OFF(&rconf)
}