Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit01146d7

Browse files
author
Vladimir Ershov
committed
Merge commit '861c85ad10cc26db3e98dbd465ae3929dbdd21c6' into PGPROEE9_6_scheduler
2 parents92d1f9f +861c85a commit01146d7

32 files changed

+2066
-12
lines changed

‎contrib/pgpro_scheduler/pgpro_scheduler--2.0.sql

Lines changed: 27 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -338,7 +338,7 @@ LANGUAGE plpgsql set search_path FROM CURRENT;
338338
CREATEFUNCTION_possible_args() RETURNS jsonbAS
339339
$BODY$
340340
BEGIN
341-
RETURNjson_build_object(
341+
RETURNjsonb_build_object(
342342
'node','node name (default: master)',
343343
'name','job name',
344344
'comments','some comments on job',
@@ -437,9 +437,9 @@ BEGIN
437437
IF N>0 THEN
438438
EXECUTE'SELECT array_agg(lll) FROM (SELECT distinct(date_trunc(''min'', unnest::timestamp with time zone)) as lll FROM unnest($1) ORDER BY date_trunc(''min'', unnest::timestamp with time zone)) as Z'
439439
INTO dates USING dates;
440-
cron := COALESCE(cron,'{}'::jsonb)||json_build_object('dates', array_to_json(dates))::jsonb;
440+
cron := COALESCE(cron,'{}'::jsonb)||jsonb_build_object('dates', array_to_json(dates));
441441
END IF;
442-
442+
443443
clean_cron :='{}'::jsonb;
444444
FOR nameINSELECT*FROM unnest('{dates, crontab, onstart, days, hours, wdays, months, minutes}'::text[])
445445
LOOP
@@ -635,7 +635,7 @@ LANGUAGE plpgsql
635635
CREATEFUNCTIONcreate_job(crontext, commandtext, nodetext DEFAULTNULL) RETURNSintegerAS
636636
$BODY$
637637
BEGIN
638-
RETURN create_job(json_build_object('cron', cron,'command', command,'node', node)::jsonb);
638+
RETURN create_job(jsonb_build_object('cron', cron,'command', command,'node', node));
639639
END
640640
$BODY$
641641
LANGUAGE plpgsql
@@ -644,7 +644,7 @@ LANGUAGE plpgsql
644644
CREATEFUNCTIONcreate_job(dttimestamp with time zone, commandtext, nodetext DEFAULTNULL) RETURNSintegerAS
645645
$BODY$
646646
BEGIN
647-
RETURN create_job(json_build_object('date', dt::text,'command', command,'node', node)::jsonb);
647+
RETURN create_job(jsonb_build_object('date', dt::text,'command', command,'node', node));
648648
END
649649
$BODY$
650650
LANGUAGE plpgsql
@@ -653,7 +653,7 @@ LANGUAGE plpgsql
653653
CREATEFUNCTIONcreate_job(dtstimestamp with time zone[], commandtext, nodetext DEFAULTNULL) RETURNSintegerAS
654654
$BODY$
655655
BEGIN
656-
RETURN create_job(json_build_object('dates', array_to_json(dts),'command', command,'node', node)::jsonb);
656+
RETURN create_job(jsonb_build_object('dates', array_to_json(dts),'command', command,'node', node));
657657
END
658658
$BODY$
659659
LANGUAGE plpgsql
@@ -662,7 +662,7 @@ LANGUAGE plpgsql
662662
CREATEFUNCTIONcreate_job(crontext, commandstext[], nodetext DEFAULTNULL) RETURNSintegerAS
663663
$BODY$
664664
BEGIN
665-
RETURN create_job(json_build_object('cron', cron,'commands', array_to_json(commands),'node', node)::jsonb);
665+
RETURN create_job(jsonb_build_object('cron', cron,'commands', array_to_json(commands),'node', node));
666666
END
667667
$BODY$
668668
LANGUAGE plpgsql
@@ -671,7 +671,7 @@ LANGUAGE plpgsql
671671
CREATEFUNCTIONcreate_job(dttimestamp with time zone, commandstext[], nodetext DEFAULTNULL) RETURNSintegerAS
672672
$BODY$
673673
BEGIN
674-
RETURN create_job(json_build_object('date', dt::text,'commands', array_to_json(commands),'node', node)::jsonb);
674+
RETURN create_job(jsonb_build_object('date', dt::text,'commands', array_to_json(commands),'node', node));
675675
END
676676
$BODY$
677677
LANGUAGE plpgsql
@@ -680,7 +680,7 @@ LANGUAGE plpgsql
680680
CREATEFUNCTIONcreate_job(dtstimestamp with time zone[], commandstext[], nodetext DEFAULTNULL) RETURNSintegerAS
681681
$BODY$
682682
BEGIN
683-
RETURN create_job(json_build_object('dates', array_to_json(dts),'commands', array_to_json(commands),'node', node)::jsonb);
683+
RETURN create_job(jsonb_build_object('dates', array_to_json(dts),'commands', array_to_json(commands),'node', node));
684684
END
685685
$BODY$
686686
LANGUAGE plpgsql
@@ -796,14 +796,27 @@ $BODY$
796796
LANGUAGE plpgsql
797797
SECURITY DEFINERset search_pathFROM CURRENT;
798798

799+
CREATEFUNCTIONset_job_attribute(jobIdinteger, nametext, value jsonb) RETURNSbooleanAS
800+
$BODY$
801+
BEGIN
802+
IF name<>'rule' THEN
803+
RAISE EXCEPTION'key % cannot have a jsonb value. Only "rule" allowed', name;
804+
END IF;
805+
806+
RETURN set_job_attributes(jobId, jsonb_build_object(name, value));
807+
END
808+
$BODY$
809+
LANGUAGE plpgsql
810+
SECURITY DEFINERset search_pathFROM CURRENT;
811+
799812
CREATEFUNCTIONset_job_attribute(jobIdinteger, nametext, value anyarray) RETURNSbooleanAS
800813
$BODY$
801814
BEGIN
802815
IF name<>'dates'AND name<>'commands' THEN
803816
RAISE EXCEPTION'key % cannot have an array value. Only dates, commands allowed', name;
804817
END IF;
805818

806-
RETURN set_job_attributes(jobId,json_build_object(name, array_to_json(value))::jsonb);
819+
RETURN set_job_attributes(jobId,jsonb_build_object(name, array_to_json(value)));
807820
END
808821
$BODY$
809822
LANGUAGE plpgsql
@@ -815,9 +828,11 @@ DECLARE
815828
attrs jsonb;
816829
BEGIN
817830
IF name='dates'OR name='commands' THEN
818-
attrs := json_build_object(name, array_to_json(value::text[]));
831+
attrs := jsonb_build_object(name, array_to_json(value::text[]));
832+
ELSIF name='rule' THEN
833+
attrs := jsonb_build_object('rule', value::jsonb);
819834
ELSE
820-
attrs :=json_build_object(name, value);
835+
attrs :=jsonb_build_object(name, value);
821836
END IF;
822837
RETURN set_job_attributes(jobId, attrs);
823838
END
Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
#!/usr/bin/perl
2+
use strict;
3+
no warnings;
4+
use Test::Harness;
5+
use DBI;
6+
use Getopt::Long;
7+
8+
my$dbname;
9+
my$username;
10+
my$password;
11+
my$host;
12+
GetOptions ("--host=s"=> \$host,
13+
"--dbname=s"=> \$dbname,
14+
"--username=s"=> \$username,
15+
"--password=s"=> \$password);
16+
17+
print"Prepare test enviroment\n";
18+
my$dbh = DBI->connect("dbi:Pg:dbname=$dbname; host=$host","$username","$password",
19+
{PrintError=> 0});
20+
if($dbh->err != 0){
21+
print$DBI::errstr ."\n";
22+
exit(-1);
23+
}
24+
25+
my$query ="DROP TABLE IF EXISTS test_results;";
26+
$dbh->do($query);
27+
if($dbh->err != 0){
28+
print$DBI::errstr ."\n";
29+
exit(-1);
30+
}
31+
32+
$query ="CREATE TABLE test_results( time_mark timestamp, commentary text );";
33+
$dbh->do($query);
34+
if($dbh->err != 0){
35+
print$DBI::errstr ."\n";
36+
exit(-1);
37+
}
38+
39+
$query ="DROP ROLE IF EXISTS tester;";
40+
$dbh->do($query);
41+
if($dbh->err != 0){
42+
print$DBI::errstr ."\n";
43+
exit(-1);
44+
}
45+
46+
$query ="CREATE ROLE tester;";
47+
$dbh->do($query);
48+
if($DBI::err != 0){
49+
print$DBI::errstr ."\n";
50+
exit(-1);
51+
}
52+
53+
$query ="GRANT INSERT ON test_results TO tester;";
54+
$dbh->do($query);
55+
if($dbh->err != 0){
56+
print$DBI::errstr ."\n";
57+
exit(-1);
58+
}
59+
60+
$dbh->disconnect();
61+
62+
print"Run tests\n";
63+
my@db_param = ["--host=$host","--dbname=$dbname","--username=$username","--password=$password"];
64+
my%args = (
65+
verbosity=> 1,
66+
test_args=>@db_param
67+
);
68+
my$harness = TAP::Harness->new( \%args );
69+
my@tests =glob('t/*.t' );
70+
$harness->runtests(@tests );
71+
72+
73+
74+
75+
76+
Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,81 @@
1+
#!/usr/bin/perl
2+
use strict;
3+
no warnings;
4+
use Test::More;
5+
use DBI;
6+
use Getopt::Long;
7+
8+
my$dbname;
9+
my$username;
10+
my$password;
11+
my$host;
12+
GetOptions ("--host=s"=> \$host,
13+
"--dbname=s"=> \$dbname,
14+
"--username=s"=> \$username,
15+
"--password=s"=> \$password);
16+
my$dbh = DBI->connect("dbi:Pg:dbname=$dbname; host=$host","$username","$password",
17+
{PrintError=> 0});
18+
ok($dbh->err == 0)or (print$DBI::errstrand BAIL_OUT);
19+
20+
my$query ="DELETE FROM test_results;";
21+
$dbh->do($query);
22+
ok($dbh->err == 0)or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
23+
24+
$query ="SELECT schedule.create_job(NULL, '');";
25+
my$sth =$dbh->prepare($query);
26+
$sth->execute();
27+
ok($dbh->err == 0)or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
28+
my$job_id =$sth->fetchrow_array()and$sth->finish();
29+
$sth->finish();
30+
31+
$query ="SELECT schedule.deactivate_job(?)";
32+
$sth =$dbh->prepare($query);
33+
$sth->bind_param(1,$job_id);
34+
ok($sth->execute())or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
35+
$sth->finish();
36+
37+
$query ="SELECT schedule.set_job_attributes(?,\'{\"name\":\"Test\",
38+
\"cron\":\"* * * * *\",
39+
\"commands\": [\"INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJob'')\",
40+
\"INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJob'')\"],
41+
\"run_as\":\"tester\",
42+
\"use_same_transaction\":\"true\"
43+
}\')";
44+
$sth =$dbh->prepare($query);
45+
$sth->bind_param(1,$job_id);
46+
ok($sth->execute())or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
47+
$sth->finish();
48+
49+
$query ="SELECT schedule.activate_job(?)";
50+
$sth =$dbh->prepare($query);
51+
$sth->bind_param(1,$job_id);
52+
ok($sth->execute())or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
53+
$sth->finish();
54+
55+
sleep 120;
56+
$query ="SELECT count(*) FROM test_results";
57+
$sth =$dbh->prepare($query);
58+
ok($sth->execute())or (print$DBI::errstrand$dbh->disconnect()and BAIL_OUT);
59+
60+
my$result =$sth->fetchrow_array()and$sth->finish();
61+
ok ($result > 1)orprint"Count == 0\n";
62+
63+
$query ="SELECT schedule.deactivate_job(?)";
64+
$sth =$dbh->prepare($query);
65+
$sth->bind_param(1,$job_id);
66+
ok($sth->execute())orprint$DBI::errstr ;
67+
$sth->finish();
68+
69+
$query ="DELETE FROM test_results;";
70+
$dbh->do($query);
71+
ok($dbh->err == 0)orprint$DBI::errstr;
72+
73+
$query ="SELECT schedule.drop_job(?)";
74+
$sth =$dbh->prepare($query);
75+
$sth->bind_param(1,$job_id);
76+
ok($sth->execute())orprint$DBI::errstr;
77+
$sth->finish();
78+
79+
$dbh->disconnect();
80+
81+
done_testing();
Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
#!/usr/bin/perl
2+
use strict;
3+
no warnings;
4+
use Test::More;
5+
use DBI;
6+
use Getopt::Long;
7+
8+
my$dbname;
9+
my$username;
10+
my$password;
11+
my$host;
12+
GetOptions ("--host=s"=> \$host,
13+
"--dbname=s"=> \$dbname,
14+
"--username=s"=> \$username,
15+
"--password=s"=> \$password);
16+
my$dbh = DBI->connect("dbi:Pg:dbname=$dbname; host=$host","$username","$password",
17+
{PrintError=> 0});
18+
ok($dbh->err == 0)or (print$DBI::errstrand BAIL_OUT);
19+
20+
my$query ="DELETE FROM test_results;";
21+
$dbh->do($query);
22+
ok($dbh->err == 0)orprint$DBI::errstr ."\n";
23+
24+
$query ="SELECT schedule.create_job(\'abcdefghi\',
25+
ARRAY[\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\',
26+
\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\']);";
27+
my$sth =$dbh->prepare($query);
28+
$sth->execute();
29+
ok($dbh->err != 0)orprint$DBI::errstr ."\n";
30+
$sth->finish();
31+
32+
$query ="SELECT schedule.create_job(\'*bcdefgh*\',
33+
ARRAY[\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\',
34+
\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\']);";
35+
$sth =$dbh->prepare($query);
36+
$sth->execute();
37+
ok($dbh->err != 0)orprint$DBI::errstr ."\n";
38+
$sth->finish();
39+
40+
$query ="SELECT schedule.create_job(\'* * * # *\',
41+
ARRAY[\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\',
42+
\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\']);";
43+
$sth =$dbh->prepare($query);
44+
$sth->execute();
45+
ok($dbh->err != 0)orprint$DBI::errstr ."\n";
46+
$sth->finish();
47+
48+
$query ="SELECT schedule.create_job(\'\',
49+
ARRAY[\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\',
50+
\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\']);";
51+
$sth =$dbh->prepare($query);
52+
$sth->execute();
53+
ok($dbh->err != 0)orprint$DBI::errstr ."\n";
54+
$sth->finish();
55+
56+
$query ="SELECT schedule.create_job(\'\',
57+
ARRAY[\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\',
58+
\'INSERT INTO test_results (time_mark, commentary) VALUES(now(), ''createJobWithCron'')\']);";
59+
$sth =$dbh->prepare($query);
60+
$sth->execute();
61+
ok($dbh->err != 0)orprint$DBI::errstr ."\n";
62+
$sth->finish();
63+
64+
$dbh->disconnect();
65+
66+
done_testing();
67+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp