Beispiel #1
0
func setupDatabaseListener() {
	if !checkDatabaseHandleValid(db) {
		return
	}

	//create/replace function for notifyPhoneNumber()
	if _, err := db.Exec(`CREATE or REPLACE FUNCTION notifyPhoneNumber() RETURNS trigger AS $$
 			BEGIN  
  			  IF TG_OP='DELETE' THEN
    				EXECUTE FORMAT('NOTIFY notifyphonenumber, ''%s''', OLD.PhoneNumber); 
  				ELSE
    				EXECUTE FORMAT('NOTIFY notifyphonenumber, ''%s''', NEW.PhoneNumber); 
  				END IF;
  			RETURN NULL;
 			END;  
		$$ LANGUAGE plpgsql;`); err != nil {
		log.Println(err)
	} else {
		log.Println("Successfully create/replace function notifyPhoneNumber()")
	}

	//check for trigger existence
	var triggerExist bool
	if err := db.QueryRow(`SELECT EXISTS(
		SELECT 1
			FROM pg_trigger
			WHERE tgname='inprogresschange')`).Scan(&triggerExist); err != nil {
		log.Println(err)
	}
	if !triggerExist {
		if _, err := db.Exec(`CREATE TRIGGER inprogresschange AFTER INSERT OR UPDATE OR DELETE
 			ON inprogress
 			FOR EACH ROW 
 			EXECUTE PROCEDURE notifyPhoneNumber();`); err != nil {
			log.Println(err)
		} else {
			log.Println("Successfully create trigger inprogresschange")
		}
	} else {
		log.Println("Trigger inprogresschange exists.")
	}

	//Create handler for logging listener errors
	reportProblem := func(ev pq.ListenerEventType, err error) {
		if err != nil {
			fmt.Println(err.Error())
		}
	}

	//Listen for table updates
	var listenerObj *pq.Listener
	listenerObj = pq.NewListener(os.Getenv("DATABASE_URL"), 10*time.Second, time.Minute, reportProblem)

	err := listenerObj.Listen("notifyphonenumber")
	if err != nil {
		log.Println(err)
	}

	//Find our session PID so we can ignore notifications from ourselves
	var pid int
	fmt.Println("Getting session PID...")
	if rows, err := db.Query(`SELECT * 
		FROM pg_stat_activity 
		WHERE pid = pg_backend_pid();`); err != nil {
		log.Println(err)
	} else {
		var i interface{} //empty interface to read unneeded columns into
		for rows.Next() {
			if err := rows.Scan(&i, &i, &pid, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i); err != nil {
				log.Println(err)
			} else {
				fmt.Println("Session PID:", pid)
			}
		}
	}

	//Monitor for noitification in background
	go func() {
		for {
			var notificationObj *pq.Notification
			notificationObj = <-listenerObj.Notify
			fmt.Printf("Backend PID %v\nChannel %v\nPayload %v\n", notificationObj.BePid, notificationObj.Channel, notificationObj.Extra)
			//Get updated row from database if the notifying PID is not this instance's PID
			if pid != notificationObj.BePid {
				if updatedRows := selectRowsFromTableByPhoneNumber("inprogress", notificationObj.Extra); updatedRows == nil {
					log.Println(err)
				} else {
					//We handle the possibility of deleted rows in laod pickups rows into memory since we can only enumerate over rows object once
					loadPickupRowsIntoMemory(&pickups, updatedRows, notificationObj)
				}
			}
		}
	}()
}