A script of watching the status of mysql database.

Warning! It can not be used for create or drop table queries.

Note: This requires Node.js v0.10 to run. If you had not install it , you can download it at http://nodejs.org/download/ .

1.Download the SQLwatcher source or clone the git repository:

$ git clone [email protected]:dollars0427/SQLwatcher.git

2.Switch to the project root directory:

$ cd sqlwathcer

3.Install the dependencies:

$ npm install

1.Copy the configuration file and edit it:

$ cp ./config/option.example.json option.json 
$ vi option.json

$ cp ./config/querylist.example.json querylist.json 
$ vi querylist.json

2.Enter the setting of the database which you want to watch.

    "database": {
        "host":"localhost",
        "port":"3306",
        "dbName":"Enter Your Database Name At Here.",
        "username":"Enter Your Database Username At Here.",
        "password":"Enter Your Database Password At Here."
    },

3.Enter the setting of timer:

    "timer":{
        "repeattime":3000,  
        "keepalivetimes":["10:00","11:00"],
        "timezoneoffset":""
    },

repeattime: After this time, the script will run again automatic.

keepalivetimes: If the database is fine, the script will send keep alive mail at these times.

timezoneoffset: Set the time zone offset, if it is null the script will using the default time zone offset of your system.

4.Change the notification setting. There are two type of notification: http(Call web API) and mail. You can use these at the same time.

	"notification": {
		"type": ["mail", "http"]
	},

5.Enter the setting of mail if necessary.

    "mail":{
        "server":{
            "user":"Enter Your mail address at here.",
            "password":"Enter Your mail password at here.",
            "host":"Enter your smtp server address at here",
            "port":465,
            "ssl":true,
            "tls":false
        },

        "alive":{
            "from":"You <[email protected]>",
            "to":["[email protected]","[email protected]"],
            "subject":"You have done!",
            "text":"Yes, you have done."
        },

        "dead":{
            "from":"You <[email protected]>",
            "to":["[email protected]","[email protected]"],
            "subject":"We are failed.",
            "text":"No, we are failed."

        }

    }

6.Enter the setting of http if necessary.

	"http": {
		"alive": {
			"callurl": "http://example.com/message",
            "method":"get",
			"baseparam": [{
				"example1": "hello"
			}, {
				"example2": "world"
			}],
			"notiparam": {
				"example_message": "It is an example(alive)"
			}
		},
        "dead": {
            "callurl": "http://example.com/message",
            "method":"post",
            "baseparam": [{
                "example1": "hello"
            }, {
                "example2": "world"
            }],
            "notiparam": {
                "example_message": "It is an example(dead)"
            }
        }
	}

callurl: The url of web API which should sqlWatcher send request to.

method: The http request method, get or post.

baseparam: The param of request, which will not contain noftication message.

notiparam: The param of request, which will contain noftication message.

7.Edit the query list for testing the database:

{
  "defaultSelectRec": 1,
  "defaultUpdateRec": 1,
  "job": [
    {   
      "sql": "INSERT INTO TestTable (FirstName, LastName, Age) VALUES (\"Sardo\", \"Ip\", \"21\");",
      "rec": null
    },  
    {   
      "sql": "SELECT * from TestTable limit 1;",
      "rec": null
    },  
    {   
      "sql": "UPDATE TestTable SET FirstName =\"Hiei\" WHERE FirstName = \"Sardo\";",
      "rec": null
    }   
  ]
}

defaultSelectRec: A number of how many record should be the select query found by default.

defaultUpdateRec: A number of how many rows should be the update/insert query affected by default.

rec: The number of how many rows should be affected, or found by own query. If you don't need it, it should be 0.

If you have a text file which contain SQL query, you can using convertsql.js which in config directory to convert it to JSON format too.

$ node convertsql.js [Path of text file] >> [Path of JSON file] ]

Then you will get a query list file in JSON format.

1.Run index.js with this command: $node sqlwatcher.js [Path of setting file] [Path of query list]

In default, these file is at the config directory which at the root folder of project.

If you want to run it at the background, you can use these command: nohup node sqlwatcher.js [Path of setting file] [Path of query list] &

You can run the unit-test of this project by using nodeunit.

1.Switch to the test directory . It is in the root directory of project.

cd ./test

2.Install the dependencies:

npm install

3.Copy the configuration file and edit it:

$ cp ./config/option.conf.sample option.conf
$ vi option.conf

It is same as configuration part.

3.Run nodeunit to test each part:

#This is just an example!
nodeunit testcase.js

If there are any bug, please feel feel to open a issues.