golang+ Database timed Tasks

This is a creation in Article, where the information may have evolved or changed.

golang+ Database timed Tasks

   项目背景大致如下，楼主在用nodejs写项目时遇到一些需要定时去处理的事情，例如僵尸用户定时清除，一些产品定时下架，邮件定时发送等等! 期初使用nodejs setTimeOut递归嵌套实现，后来发现内存不断飙升，故而放弃，最终改用了性能不错的golang实现

Database design

Field name	meaning
Id	Number
Name	Task Name
Create_at	Creation time
Type	1. Execute once 2. Loop execution
Separate_time	Execution interval
Status	Execution status 0.1 not started. Execute in-1. Execution failed-2. Manual Pause
Remark	Notes Information
Fn	The database stored procedure or function to execute
Start_time	Start Execution time
Next_exec_time	Next Execution time
Last_exec_time	Last Execution time
Fn_type	email, SQL, etc.

Approximate implementation process

1. Need to have a dead loop, sleep 10s start and then sleep 10 ...

    for {time.Sleep(10 * time.Second)go execTask(*db) //使用子进程执行，防止卡死主进程}

1. Start execution, find the task you need to perform

rows, err := db.Query("SELECT id,name,status,type,fn,fn_type, separate_time FROM public.tasks where (status = 0 and start_time < now()) or (status = 1 and next_exec_time < now());")

1. Perform tasks

res, err := db.Exec(fn)

1. The next execution time is updated when the task is executed successfully

func setTaskNextExecTime(db sql.DB, taskId string, separateTime int64) error {next_exec_time := time.Now().Unix() + separateTimenextTime := time.Unix(next_exec_time, 999)res, err := db.Exec("UPDATE tasks set status = 1, last_exec_time=now(), next_exec_time=$2 WHERE id = $1::uuid", taskId, nextTime)res = nillog.Println(res)return err;}

Advantages and Disadvantages

    优点：        1. 所有任务执行状态都可以查询到，例如任务异常或者上次执行时间，下次执行时间        2. 增加一个定时任务，只需要在数据库插入一条记录就OK    缺点:        1. 如果要绑定非数据库可操作任务，需要自己扩展    

Project Source

Mtask Project Main.gopackage mainimport ("Database/sql" _ "GITHUB.COM/LIB/PQ" "Log" "Time" "OS" "Io/ioutil" "encoding/ JSON ")//configuration struct type Conf struct {Db map[string] string}//read config file func readconf (path string) {var c Conf err E Rrorfi, err: = OS. Open (PATH) if err! = Nil {return c, err} else {defer fi. Close ()//Read config file fd, err: = Ioutil. ReadAll (FI) if err! = Nil {return c, err} else {var c conferr = json. Unmarshal (FD, &c) if err! = Nil {return c, err} else {return C, Err}}}return C, err}func Main () {c, err: = Readconf ("./ Conf.json ") if err! = Nil {log. Print (ERR) Panic (err)}db, err: = SQL. Open ("Postgres", c.db["Postgres"]) if err! = Nil {log. Print (Err)} else {defer db. Close () for {time. Sleep (Ten * time. Second) Go Exectask (*db)}}}func exectask (DB SQL. DB) {defer func () {if err: = Recover (); Err! = nil {log. Print (Err) log. Printf ("An error occurred while executing the task:%s", Err)}} (); log. PRINTLN ("Start task .....") rows, err: = db. Query ("Select Id,name,status,type,fn,fn_type, Separate_time from public.tasks where (status =0 and Start_time < now ()) or (status = 1 and Next_exec_time < Now ()); ") If err! = Nil {log. Print (Err)} else {defer rows. Close () for rows. Next () {var id stringvar name stringvar status Intvar tasktype intvar separatetime int64var fn stringvar fntype stringerr = Rows. Scan (&id, &name, &status, &tasktype, &AMP;FN, &fntype, &separatetime) if err! = Nil {//log error, Update the task information at the same time as the Exception log. Print (err) Err = Settaskexecfail (db, id) if err! = Nil {log. Print (ERR)}} else {if (Fntype = = "SQL") {res, err: = db. Exec (FN) if err! = Nil {log. Print (err) Err = Settaskexecfail (db, id) if err! = Nil {log. Print (Err)}log. Printf ("Task:%s Error executing", name)} else {res = Nillog. PRINTLN (res) If tasktype = = 1 {err = settaskexecsuccess (db, id) if err! = Nil {log. Print (Err)}log. Printf ("Task:%s execution complete", name)} else {err = Settasknextexectime (db, ID, separatetime) if err! = Nil {log. Print (Err)}}log. Printf ("Task:%s execution succeeded", name)}} else if (Fntype = = "Bash") {log. Printf ("This is a Bash task")} else if (Fntype = = "Python") {log. Printf ("This is aPython task ")} else if (Fntype = =" Email ") {//Send Email task err = execemailtask (db) if err! = Nil {Handlefail (db, id) log. PRINTLN (Err)} else {handlesuccess (db, id)}log. Printf ("Send Mail Task") settaskexecsuccess (db, id) settasknextexectime (db, ID, separatetime)} else if (Fntype = = "SMS") {// Send SMS Task log. Printf ("Send SMS Task")}}}err = rows. ERR () if err! = Nil {log. Print (Err)}}log. PRINTLN ("End Execution task ....")}func settaskexecfail (DB SQL. DB, taskId String) error {res, err: = db. Exec ("UPDATE tasks Set status =-2 WHERE id = $1::uuid", taskId) Err = Nillog. Println (RES) return Err}func settaskexecsuccess (DB SQL. DB, taskId String) error {res, err: = db. Exec ("UPDATE tasks Set status = 2 WHERE id = $1::uuid", taskId) Err = Nillog. Println (RES) return Err}func settasknextexectime (DB SQL. DB, taskId string, separatetime Int64) error {next_exec_time: = time. Now (). Unix () + Separatetimenexttime: = time. Unix (Next_exec_time, 999) res, err: = db. Exec ("UPDATE tasks Set status = 1, Last_exec_time=now (), next_exec_time=$2 WHERE id = $1::uuid", TaskId, nexttime) res = Nillog. Println (RES) return err;}