Log Message:
-----------
last work
Modified Files:
--------------
pgagent:
pgagent.sql (r1.1 -> r1.2)
pgagent/src:
job.cpp (r1.1 -> r1.2)
pgAgent.cpp (r1.1 -> r1.2)
pgAgent.dsp (r1.1 -> r1.2)
Added Files:
-----------
pgagent/src:
misc.cpp (r1.1)
Index: pgagent.sql
===================================================================
RCS file: /projects/pgagent/pgagent.sql,v
retrieving revision 1.1
retrieving revision 1.2
diff -Lpgagent.sql -Lpgagent.sql -u -w -r1.1 -r1.2
--- pgagent.sql
+++ pgagent.sql
@@ -1,65 +1,69 @@
/*
-
-drop table pga_jobprotocolstep;
-drop table pga_jobprotocol;
-drop table pga_schedule;
-drop table pga_jobstep;
-drop table pga_job;
-drop table pga_jobclass;
+// pgAgent - PostgreSQL Tools
+// $Id$
+// Copyright (C) 2003 The pgAdmin Development Team
+// This software is released under the Artistic Licence
+//
+// pgagent.sql - pgAgent tables and functions
+//
*/
-CREATE TABLE pga_jobagent (
+COMMENT ON SCHEMA pg_admin IS 'pgAdmin/pgAgent system tables';
+
+CREATE TABLE pg_admin.pga_jobagent (
jagpid int4 NOT NULL PRIMARY KEY,
logintime timestamptz NOT NULL DEFAULT current_timestamp,
station text NOT NULL
) WITHOUT OIDS;
-COMMENT ON TABLE pga_jobagent IS 'active job agents';
+COMMENT ON TABLE pg_admin.pga_jobagent IS 'active job agents';
-CREATE TABLE pga_jobclass (
+CREATE TABLE pg_admin.pga_jobclass (
jclname text NOT NULL
) WITH OIDS;
-CREATE UNIQUE INDEX pga_jobclass_oid ON pga_jobclass(oid);
-COMMENT ON TABLE pga_jobclass IS 'Job classification';
+CREATE UNIQUE INDEX pga_jobclass_oid ON pg_admin.pga_jobclass(oid);
+CREATE UNIQUE INDEX pga_jobclass_name ON pg_admin.pga_jobclass(jclname);
+COMMENT ON TABLE pg_admin.pga_jobclass IS 'Job classification';
-CREATE TABLE pga_job (
-jobjcloid oid NOT NULL REFERENCES pga_jobclass
(oid) ON DELETE RESTRICT ON UPDATE RESTRICT,
+CREATE TABLE pg_admin.pga_job (
+jobjcloid oid NOT NULL REFERENCES
pg_admin.pga_jobclass (oid) ON DELETE RESTRICT ON UPDATE RESTRICT,
jobname text NOT NULL,
jobdesc text NOT NULL DEFAULT '',
jobenabled bool NOT NULL DEFAULT true,
jobcreated timestamptz NOT NULL DEFAULT current_timestamp,
jobchanged timestamptz NOT NULL DEFAULT current_timestamp,
-jobagentid int4 NULL REFERENCES pga_jobagent(jagpid)
ON DELETE SET NULL ON UPDATE RESTRICT,
-jobnextrun timestamptz NULL
+jobagentid int4 NULL REFERENCES
pg_admin.pga_jobagent(jagpid) ON DELETE SET NULL ON UPDATE RESTRICT,
+jobnextrun timestamptz NULL,
+joblastrun timestamptz NULL
);
-CREATE UNIQUE INDEX pga_job_oid ON pga_job(oid);
-COMMENT ON TABLE pga_job IS 'Job main entry';
-COMMENT ON COLUMN pga_job.jobagentid IS 'Agent that currently executes this
job.';
+CREATE UNIQUE INDEX pga_job_oid ON pg_admin.pga_job(oid);
+COMMENT ON TABLE pg_admin.pga_job IS 'Job main entry';
+COMMENT ON COLUMN pg_admin.pga_job.jobagentid IS 'Agent that currently
executes this job.';
-CREATE TABLE pga_jobstep (
-jstjoboid oid NOT NULL REFERENCES pga_job (oid) ON
DELETE CASCADE ON UPDATE RESTRICT,
+CREATE TABLE pg_admin.pga_jobstep (
+jstjoboid oid NOT NULL REFERENCES pg_admin.pga_job
(oid) ON DELETE CASCADE ON UPDATE RESTRICT,
jstname text NOT NULL,
jstdesc text NOT NULL DEFAULT '',
jstenabled bool NOT NULL DEFAULT true,
jstkind char NOT NULL CHECK (jstkind IN ('b',
's')), -- batch, sql
jstcode text NOT NULL,
-jstdboid oid NULL, -- REFERENCES
pga_database(oid) ON DELETE CASCADE ON UPDATE RESTRICT,
+jstdboid oid NULL, -- REFERENCES pg_database(oid)
ON DELETE CASCADE ON UPDATE RESTRICT,
jstonerror char NOT NULL CHECK (jstonerror IN ('f',
's', 'i')) DEFAULT 'f', -- fail, success, ignore
jscnextrun timestamptz NULL
) WITH OIDS;
-CREATE UNIQUE INDEX pga_jobstep_oid ON pga_jobstep(oid);
-CREATE INDEX pga_jobstep_joboid ON pga_jobstep(jstjoboid);
-COMMENT ON TABLE pga_jobstep IS 'Job step to be executed';
-COMMENT ON COLUMN pga_jobstep.jstkind IS 'Kind of jobstep: s=sql, b=batch';
-COMMENT ON COLUMN pga_jobstep.jstonerror IS 'What to do if step returns an
error: f=fail the job, s=mark step as succeeded and continue, i=mark as fail
but ignore it and proceed';
+CREATE UNIQUE INDEX pga_jobstep_oid ON pg_admin.pga_jobstep(oid);
+CREATE INDEX pga_jobstep_joboid ON pg_admin.pga_jobstep(jstjoboid);
+COMMENT ON TABLE pg_admin.pga_jobstep IS 'Job step to be executed';
+COMMENT ON COLUMN pg_admin.pga_jobstep.jstkind IS 'Kind of jobstep: s=sql,
b=batch';
+COMMENT ON COLUMN pg_admin.pga_jobstep.jstonerror IS 'What to do if step
returns an error: f=fail the job, s=mark step as succeeded and continue, i=mark
as fail but ignore it and proceed';
-CREATE TABLE pga_schedule (
-jscjoboid oid NOT NULL REFERENCES pga_job (oid) ON
DELETE CASCADE ON UPDATE RESTRICT,
+CREATE TABLE pg_admin.pga_schedule (
+jscjoboid oid NOT NULL REFERENCES pg_admin.pga_job
(oid) ON DELETE CASCADE ON UPDATE RESTRICT,
jscname text NOT NULL,
jscdesc text NOT NULL DEFAULT '',
jscenabled bool NOT NULL DEFAULT true,
@@ -67,33 +71,32 @@
jscend timestamptz NULL,
jsckind char NOT NULL CHECK (jsckind IN ('n',
's', 'd', 'w', 'm', 'y')) DEFAULT 'n', -- normal, single, daily, weekly,
monthly, yearly
jscsched timestamptz NULL,
-jsclist interval[] NOT NULL,
-jscnextrun timestamptz NOT NULL
+jsclist interval[] NOT NULL
) WITH OIDS;
-CREATE UNIQUE INDEX pga_schedule_oid ON pga_schedule(oid);
-CREATE INDEX pga_jobschedule_joboid ON pga_schedule(jscjoboid);
-COMMENT ON TABLE pga_schedule IS 'Schedule for a job';
-COMMENT ON COLUMN pga_schedule.jsckind IS 'Kind of schedule: normal periodical
(jsclist has one entry), single, daily, weekly, monthly, yearly (jsclist
contains list of intervals in period)';
+CREATE UNIQUE INDEX pga_schedule_oid ON pg_admin.pga_schedule(oid);
+CREATE INDEX pga_jobschedule_joboid ON pg_admin.pga_schedule(jscjoboid);
+COMMENT ON TABLE pg_admin.pga_schedule IS 'Schedule for a job';
+COMMENT ON COLUMN pg_admin.pga_schedule.jsckind IS 'Kind of schedule: normal
periodical (jsclist has one entry), single, daily, weekly, monthly, yearly
(jsclist contains list of intervals in period)';
-CREATE TABLE pga_jobprotocol (
-jprjoboid oid NOT NULL REFERENCES pga_job (oid) ON
DELETE CASCADE ON UPDATE RESTRICT,
+CREATE TABLE pg_admin.pga_jobprotocol (
+jprjoboid oid NOT NULL REFERENCES pg_admin.pga_job
(oid) ON DELETE CASCADE ON UPDATE RESTRICT,
jprstatus char NOT NULL CHECK (jprstatus IN ('r',
's', 'f', 'i', 'd')) DEFAULT 'r', -- running, success, failed, internal
failure, died
jprstart timestamptz NOT NULL DEFAULT current_timestamp,
jprduration interval NULL
) WITH OIDS;
-CREATE UNIQUE INDEX pga_jobprotocol_oid ON pga_jobprotocol(oid);
-CREATE INDEX pga_jobprotocol_joboid ON pga_jobprotocol(jprjoboid);
-COMMENT ON TABLE pga_jobprotocol IS 'Protocol of a job that was run.';
-COMMENT ON COLUMN pga_jobprotocol.jprstatus IS 'Status of job: r=running,
s=successfully finished, f=failed';
+CREATE UNIQUE INDEX pga_jobprotocol_oid ON pg_admin.pga_jobprotocol(oid);
+CREATE INDEX pga_jobprotocol_joboid ON pg_admin.pga_jobprotocol(jprjoboid);
+COMMENT ON TABLE pg_admin.pga_jobprotocol IS 'Protocol of a job that was run.';
+COMMENT ON COLUMN pg_admin.pga_jobprotocol.jprstatus IS 'Status of job:
r=running, s=successfully finished, f=failed';
-CREATE TABLE pga_jobprotocolstep (
-jpejproid oid NOT NULL REFERENCES pga_jobprotocol
(oid) ON DELETE CASCADE ON UPDATE RESTRICT,
+CREATE TABLE pg_admin.pga_jobprotocolstep (
+jpejproid oid NOT NULL REFERENCES
pg_admin.pga_jobprotocol (oid) ON DELETE CASCADE ON UPDATE RESTRICT,
jpedbname name NOT NULL,
jpecode text NOT NULL,
jpestatus char NOT NULL CHECK (jpestatus IN ('r',
's', 'i', 'f')) DEFAULT 'r', -- running, success, ignored, failed
@@ -101,14 +104,14 @@
jpestarted timestamptz NOT NULL DEFAULT current_timestamp,
jpeduration interval NULL
) WITH OIDS;
-CREATE INDEX pga_jobprotocolstep_jproid ON pga_jobprotocolstep(jpejproid);
-COMMENT ON TABLE pga_jobprotocolstep IS 'Protocol of a single step in a job
that was run.';
-COMMENT ON COLUMN pga_jobprotocolstep.jpestatus IS 'Status of job step:
r=running, s=successfully finished, f=failed stopping job, i=ignored failure';
-COMMENT ON COLUMN pga_jobprotocolstep.jperesult IS 'Return code of job step';
+CREATE INDEX pga_jobprotocolstep_jproid ON
pg_admin.pga_jobprotocolstep(jpejproid);
+COMMENT ON TABLE pg_admin.pga_jobprotocolstep IS 'Protocol of a single step in
a job that was run.';
+COMMENT ON COLUMN pg_admin.pga_jobprotocolstep.jpestatus IS 'Status of job
step: r=running, s=successfully finished, f=failed stopping job, i=ignored
failure';
+COMMENT ON COLUMN pg_admin.pga_jobprotocolstep.jperesult IS 'Return code of
job step';
-CREATE OR REPLACE FUNCTION pga_next_schedule(timestamptz, timestamptz,
timestamptz, char, interval[]) returns timestamptz as
+CREATE OR REPLACE FUNCTION pg_admin.pga_next_schedule(timestamptz,
timestamptz, timestamptz, char, interval[]) returns timestamptz as
'
DECLARE
jscstart ALIAS FOR $1;
@@ -161,7 +164,7 @@
nextrun := nextrun + jsclist[1] + period;
END IF;
ELSE
- RAISE EXCEPTION ''pga_next_schedule: unknown schedule kind'';
+ RAISE EXCEPTION ''pg_admin.pga_next_schedule: unknown schedule kind'';
END IF;
RETURN nextrun;
END;
@@ -170,13 +173,13 @@
-CREATE OR REPLACE FUNCTION pga_job_trigger() RETURNS trigger AS '
+CREATE OR REPLACE FUNCTION pg_admin.pga_job_trigger() RETURNS trigger AS '
BEGIN
IF NEW.jobenabled THEN
IF NEW.jobnextrun IS NULL THEN
SELECT INTO NEW.jobnextrun
- MIN(pga_next_schedule(jscstart, jscend, jscsched, jsckind,
jsclist))
- FROM pga_schedule
+ MIN(pg_admin.pga_next_schedule(jscstart, jscend, jscsched,
jsckind, jsclist))
+ FROM pg_admin.pga_schedule
WHERE jscenabled AND jscjoboid=OLD.oid;
END IF;
ELSE
@@ -188,21 +191,21 @@
CREATE TRIGGER pga_job_trigger BEFORE UPDATE
- ON pga_job FOR EACH ROW
- EXECUTE PROCEDURE pga_job_trigger();
+ ON pg_admin.pga_job FOR EACH ROW
+ EXECUTE PROCEDURE pg_admin.pga_job_trigger();
-CREATE OR REPLACE FUNCTION pga_schedule_trigger() RETURNS trigger AS '
+CREATE OR REPLACE FUNCTION pg_admin.pga_schedule_trigger() RETURNS trigger AS '
BEGIN
IF TG_OP = ''DELETE'' THEN
-- update pga_job from remaining schedules
-- the actual calculation of jobnextrun will be performed in the
trigger
- UPDATE pga_job
+ UPDATE pg_admin.pga_job
SET jobnextrun = NULL
WHERE jobenabled AND oid=OLD.jscjoboid;
RETURN OLD;
ELSE
- UPDATE pga_job
+ UPDATE pg_admin.pga_job
SET jobnextrun = NULL
WHERE jobenabled AND oid=NEW.jscjoboid;
RETURN NEW;
@@ -213,15 +216,22 @@
CREATE TRIGGER pga_schedule_trigger AFTER INSERT OR UPDATE OR DELETE
- ON pga_schedule FOR EACH ROW
- EXECUTE PROCEDURE pga_schedule_trigger();
+ ON pg_admin.pga_schedule FOR EACH ROW
+ EXECUTE PROCEDURE pg_admin.pga_schedule_trigger();
/*
-delete from pga_schedule; -- select * from pga_Schedule;
+delete from pg_admin.pga_jobclass;
-insert into pga_schedule(jscjoboid,jscdesc, jscenabled, jscstart, jscsched,
jsclist)
-values (17271, '10min', true, '2002-01-01', '2002-01-01', ARRAY['10
min'::interval] );
-*/
+insert into pg_admin.pga_jobclass (jclname) values ('misc');
+
+ delete from pg_admin.pga_schedule;
+ select * from pg_admin.pga_Schedule;
--- update pga_schedule set jscenabled=jscenabled
\ No newline at end of file
+ insert into pg_admin.pga_schedule(jscjoboid,jscname, jscdesc, jscenabled,
jscstart, jscsched, jsclist)
+ values (xxxxx, '10min', 'arbitrary 10min cycle', true, '2002-01-01',
'2002-01-01', ARRAY['10 min'::interval] );
+
+
+-- update pg_admin.pga_schedule set jscenabled=jscenabled
+
+*/
Index: pgAgent.cpp
===================================================================
RCS file: /projects/pgagent/src/pgAgent.cpp,v
retrieving revision 1.1
retrieving revision 1.2
diff -Lsrc/pgAgent.cpp -Lsrc/pgAgent.cpp -u -w -r1.1 -r1.2
--- src/pgAgent.cpp
+++ src/pgAgent.cpp
@@ -33,7 +33,7 @@
rc = serviceConn->ExecuteVoid(
"INSERT INTO pga_tmp_zombies (jagpid)\n"
"SELECT jagpid\n"
- " FROM pga_jobagent AG\n"
+ " FROM pg_admin.pga_jobagent AG\n"
" LEFT JOIN pg_stat_activity PA ON jagpid=procpid\n"
" WHERE procpid IS NULL"
);
@@ -43,17 +43,17 @@
// There are orphaned agent entries
// mark the jobs as aborted
rc=serviceConn->ExecuteVoid(
- "UPDATE pga_jobprotocol SET jprstatus='d'\n"
+ "UPDATE pg_admin.pga_jobprotocol SET jprstatus='d'\n"
" FROM pga_tmp_zombies Z\n"
- " JOIN pga_job J ON jobagentid=jagpid\n"
- " JOIN pga_jobprotocol PR ON jprjoboid=J.oid\n"
+ " JOIN pg_admin.pga_job J ON jobagentid=jagpid\n"
+ " JOIN pg_admin.pga_jobprotocol PR ON jprjoboid=J.oid\n"
" WHERE PR.jprstatus='r';\n"
- "UPDATE pga_job SET jobagentid=NULL, jobnextrun=NULL\n"
+ "UPDATE pg_admin.pga_job SET jobagentid=NULL, jobnextrun=NULL\n"
" FROM pga_tmp_zombies Z\n"
- " JOIN pga_job J ON jobagentid=jagpid;\n"
+ " JOIN pg_admin.pga_job J ON jobagentid=jagpid;\n"
- "DELETE FROM pga_jobagent\n"
+ "DELETE FROM pg_admin.pga_jobagent\n"
" WHERE jagpid IN (SELECT jagpid FROM pga_tmp_zombies);\n"
);
}
@@ -61,7 +61,7 @@
rc=serviceConn->ExecuteVoid(
- "INSERT INTO pga_jobagent (jagpid) SELECT pg_backend_pid()");
+ "INSERT INTO pg_admin.pga_jobagent (jagpid) SELECT pg_backend_pid()");
if (rc < 0)
return rc;
@@ -71,7 +71,7 @@
DBresult *res=serviceConn->Execute(
"SELECT J.oid AS joboid\n"
- " FROM pga_job J\n"
+ " FROM pg_admin.pga_job J\n"
" WHERE jobenabled AND jobagentid IS NULL\n"
" AND jobnextrun <= now()\n"
" ORDER BY jobnextrun");
--- /dev/null
+++ src/misc.cpp
@@ -0,0 +1,112 @@
+//////////////////////////////////////////////////////////////////////////
+//
+// pgAgent - PostgreSQL Tools
+// $Id: misc.cpp,v 1.1 2005/02/25 12:56:57 andreas Exp $
+// Copyright (C) 2003 The pgAdmin Development Team
+// This software is released under the Artistic Licence
+//
+// misc.cpp - misc functions
+//
+//////////////////////////////////////////////////////////////////////////
+
+#include "pgAgent.h"
+#include "windows.h"
+
+void fatal(const string &text)
+{
+ printf(text.c_str());
+ exit(0);
+}
+
+
+string getArg(int &argc, char** &argv)
+{
+ string s;
+ if (argv[0][2])
+ s = argv[0] +2;
+ else
+ {
+ if (argc > 1)
+ {
+ argc--;
+ argv++;
+ s = argv[0];
+ }
+ else
+ {
+ // very bad!
+ fatal("bad argument.");
+ }
+ }
+
+ return s;
+}
+
+
+void setOptions(int argc, char **argv)
+{
+ while (argc-- > 0)
+ {
+ if (argv[0][0] == '-')
+ {
+ switch (argv[0][1])
+ {
+ case 't':
+ {
+ int val = atoi(getArg(argc, argv).c_str());
+ if (val > 0)
+ shortWait = val;
+ break;
+ }
+ case 'r':
+ {
+ int val = atoi(getArg(argc, argv).c_str());
+ if (val >= 10)
+ longWait = val;
+ break;
+ }
+ case 'c':
+ {
+ int val = atoi(getArg(argc, argv).c_str());
+ if (val >= 5)
+ connPoolCount = val;
+ break;
+ }
+ }
+ }
+ else
+ {
+ if (connectString != "")
+ connectString += " ";
+ connectString += *argv;
+ if (**argv == '"')
+ connectString = connectString.substr(1,
connectString.length()-2);
+ }
+ argv++;
+ }
+}
+
+
+void WaitAWhile(const bool waitLong)
+{
+ int count;
+ if (waitLong)
+ count=longWait;
+ else
+ count=shortWait;
+
+ while (count--)
+ {
+ CheckForInterrupt();
+ Sleep(1000);
+ }
+}
+
+
+
+string NumToStr(const long l)
+{
+ char buf[40];
+ sprintf(buf, "%ld", l);
+ return buf;
+}
\ No newline at end of file
Index: pgAgent.dsp
===================================================================
RCS file: /projects/pgagent/src/pgAgent.dsp,v
retrieving revision 1.1
retrieving revision 1.2
diff -Lsrc/pgAgent.dsp -Lsrc/pgAgent.dsp -u -w -r1.1 -r1.2
--- src/pgAgent.dsp
+++ src/pgAgent.dsp
@@ -118,5 +118,17 @@
SOURCE=.\pgAgent.rc
# End Source File
# End Group
+# Begin Group "Misc Files"
+
+# PROP Default_Filter ""
+# Begin Source File
+
+SOURCE="..\pg_admin-schema"
+# End Source File
+# Begin Source File
+
+SOURCE=..\pgagent.sql
+# End Source File
+# End Group
# End Target
# End Project
Index: job.cpp
===================================================================
RCS file: /projects/pgagent/src/job.cpp,v
retrieving revision 1.1
retrieving revision 1.2
diff -Lsrc/job.cpp -Lsrc/job.cpp -u -w -r1.1 -r1.2
--- src/job.cpp
+++ src/job.cpp
@@ -20,13 +20,13 @@
status="";
int rc=serviceConn->ExecuteVoid(
- "UPDATE pga_job SET jobagentid=pg_backend_pid(), joblastrun=now()\n"
+ "UPDATE pg_admin.pga_job SET jobagentid=pg_backend_pid(),
joblastrun=now()\n"
" WHERE jobagentid IS NULL AND oid=" + joboid);
if (rc == 1)
{
DBresult *res=serviceConn->Execute(
- "INSERT INTO pga_jobprotocol(jprjoboid, jprstatus) "
+ "INSERT INTO pg_admin.pga_jobprotocol(jprjoboid, jprstatus) "
"VALUES (" + joboid + ", 'r')");
if (res)
{
@@ -43,11 +43,11 @@
if (status != "")
{
int rc=serviceConn->ExecuteVoid(
- "UPDATE pga_jobprotocol\n"
+ "UPDATE pg_admin.pga_jobprotocol\n"
" SET jprstatus='" + status + "', jprduration=now() - jprstart\n"
" WHERE oid=" + prtoid + ";\n"
- "UPDATE pga_job\n"
+ "UPDATE pg_admin.pga_job\n"
" SET jobagentid=NULL, jobnextrun=NULL\n"
" WHERE oid=" + joboid
);
@@ -60,7 +60,7 @@
int rc;
DBresult *steps=serviceConn->Execute(
"SELECT JS.oid, jstkind, datname, jstcode, jstonerror\n"
- " FROM pga_jobstep JS, pg_database DB\n"
+ " FROM pg_admin.pga_jobstep JS, pg_database DB\n"
" WHERE jstenabled AND (jstdboid IS NULL OR jstdboid=DB.oid)\n"
" AND jstjoboid=" + joboid + "\n"
" ORDER BY JS.oid, jstdboid");
@@ -77,9 +77,9 @@
string jpsoid, jpecode;
DBresult *res=serviceConn->Execute(
- "INSERT INTO pga_jobprotocolstep(jpejproid, jpedbname, jpecode)\n"
+ "INSERT INTO pg_admin.pga_jobprotocolstep(jpejproid, jpedbname,
jpecode)\n"
"SELECT " + prtoid + ", '" + steps->GetString("datname") + "',
jstcode\n"
- " FROM pga_jobstep WHERE oid=" + steps->GetString("oid"));
+ " FROM pg_admin.pga_jobstep WHERE oid=" +
steps->GetString("oid"));
if (res)
{
@@ -127,7 +127,7 @@
stepstatus = steps->GetString("jstonerror");
rc=serviceConn->ExecuteVoid(
- "UPDATE pga_jobprotocolstep\n"
+ "UPDATE pg_admin.pga_jobprotocolstep\n"
" SET jpeduration = now() - jpestarted,\n"
" jperesult = " + NumToStr(rc) + ", jpestatus = '" +
stepstatus + "'\n"
" WHERE oid=" + jpsoid);
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
