GeezerVison is showing github.com — utPLSQL/client_source/sqlplus/ut_run.sql at develop · paytonrules/utPLSQL

Expand file tree

ut_run.sql

Copy path

More file actions

Latest commit

History

459 lines (404 loc) · 17.1 KB

ut_run.sql

Copy path

File metadata and controls

459 lines (404 loc) · 17.1 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

utPLSQL - Version X.X.X.X

Licensed under the Apache License, Version 2.0 (the "License"):

you may not use this file except in compliance with the License.

You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License.

/**

This script is designed to allow invocation of UTPLSQL with multiple reporters.

It allows saving of outcomes into multiple output files.

It also facilitates displaying on screen unit test results while the execution is still ongoing.

Current limit of script parameters is 39

Scrip invocation:

ut_run.sql "client_Path" "project_path" user/password@database [-p=(ut_path|ut_paths)] [-c] [-f=format [-o=output] [-s] ...] [-source_path=path] [-test_path=path]

Parameters:

client_path - The path where this script is installed. Will be used to find auxiliary scripts and to save temp files.

project_path - The path from where this script is being called.

user - username to connect as

password - password of the user

database - database to connect to

-p=ut_path(s)- A path or a comma separated list of paths to unit test to be executed.

The path can be in one of the following formats:

schema[.package[.procedure]]

schema:suite[.suite[.suite][...]][.procedure]

Both formats can be mixed in the comma separated list.

If only schema is provided, then all suites owner by that schema (user) are executed.

If -p is omitted, the current schema is used.

-f=format - A reporter to be used for reporting.

Available options:

-f=ut_documentation_reporter

A textual pretty-print of unit test results (usually use for console output)

-f=ut_teamcity_reporter

A teamcity Unit Test reporter, that can be used to visualize progress of test execution as the job progresses.

-f=ut_xunit_reporter

A XUnit xml format (as defined at: http://stackoverflow.com/a/9691131 and at https://gist.github.com/kuzuha/232902acab1344d6b578)

Usually used by Continuous Integration servers like Jenkins/Hudson or Teamcity to display test results.

-f=ut_coverage_html_reporter

Generates a HTML coverage report providing summary and detailed information on code coverage.

The html reporter is based on open-source simplecov-html reporter for Ruby.

It includes source code of the code that was covered (if possible).

-f=ut_coveralls_reporter

Generates a JSON coverage report providing detailed information on code coverage with line numbers.

This coverage report is designed to be consumed by cloud services like https://coveralls.io/.

-f=ut_coverage_sonar_reporter

Generates a JSON coverage report providing detailed information on code coverage with line numbers.

This report is designed to be consumed by SonarQube to report code coverage.

-f=ut_sonar_test_reporter

Generates a JSON report providing detailed information on test specifications.

This report is designed to be consumed by SonarQube to report test files.

If no -f option is provided, the ut_documentation_reporter will be used.

-o=output - file name to save the output provided by the reporter.

If defined, the output is not displayed on screen by default. This can be changed with the -s parameter.

If not defined, then output will be displayed on screen, even if the parameter -s is not specified.

If more than one -o parameter is specified for one -f parameter, the last one is taken into consideration.

-s - Forces putting output to to screen for a given -f parameter.

-source_path=path - Source files path to be used by coverage reporters.

-test_path=path - Test files path to be used by coverage reporters.

-c - If specified, enables printing of test results in colors as defined by ANSICONSOLE standards

To make coverage reporters work source_path and/or test_path cannot be empty, and ut_run need to be executed from your project's path.

Parameters -f, -o, -s are correlated. That is parameters -o and -s are defining outputs for -f.

Examples of invocation using sqlplus from command line:

sqlplus /nolog @ut_run ~/ut_run_path ~/project/source hr/hr@xe -p=hr_test -f=ut_documentation_reporter -o=run.log -s -f=ut_coverage_html_reporter -o=coverage.html -source_path=source

All Unit tests from schema/package "hr_test" will be be invoked with two reporters:

- ut_documentation_reporter - will output to screen and save it's output to file "run.log"

- ut_coverage_html_reporter - will read file structure from source folder, and save it's output to file "coverage.html"

sqlplus /nolog @ut_run hr/hr@xe

All Unit tests from schema "hr" will be be invoked with ut_documentation_reporter as a format and the results will be printed to screen.

whenever sqlerror exit failure

whenever oserror exit failure

define client_path="&1"

define project_path="&2"

define conn_str="&3"

conn &conn_str

set serveroutput on size unlimited format truncated

set trimspool on

set echo off

set termout off

set feedback off

set pagesize 0

set linesize 30000

set long 30000

set longchunksize 30000

set verify off

set heading off

column param_list new_value param_list noprint;

* Prepare script to make SQLPlus parameters optional and pass parameters call to param_list variable

set define #

spool ##client_path/define_params_variable.sql.tmp

declare

l_sql_columns varchar2(4000);

l_params varchar2(4000);

begin

for i in 1 .. 100 loop

dbms_output.put_line('column '||i||' new_value '||i);

l_sql_columns := l_sql_columns ||'null as "'||i||'",';

l_params := l_params || '''''&&'||i||''''',';

end loop;

dbms_output.put_line('select '||rtrim(l_sql_columns, ',') ||' from dual where rownum = 0;');

dbms_output.put_line('select '''||rtrim(l_params, ',')||''' as param_list from dual;' );

end;

spool off

set define &

* Make SQLPlus parameters optional and pass parameters call to param_list variable

@&&client_path/define_params_variable.sql.tmp

var l_paths varchar2(4000);

var l_color_enabled varchar2(5);

var l_source_path varchar2(4000);

var l_test_path varchar2(4000);

var l_source_files refcursor;

var l_test_files refcursor;

var l_run_params_cur refcursor;

var l_out_params_cur refcursor;

* Parse parameters and return them as variables

set termout on

declare

type t_call_param is record (

ut_reporter_name varchar2(4000) := 'ut_documentation_reporter',

output_file_name varchar2(4000),

output_to_screen varchar2(3) := 'on',

reporter_id varchar2(250)

);

type tt_call_params is table of t_call_param;

l_input_params ut_varchar2_list := ut_varchar2_list(&&param_list);

l_call_params tt_call_params;

l_run_cursor_sql varchar2(32767);

l_out_cursor_sql varchar2(32767);

function is_reporter(a_reporter_name varchar2) return varchar2 is

l_reporter_name varchar2(4000);

l_dummy integer;

l_owner varchar2(4000);

e_invalid_object exception;

e_not_a_reporter exception;

pragma exception_init (e_invalid_object,-44002);

pragma exception_init (e_not_a_reporter,-6550);

begin

l_reporter_name := upper(dbms_assert.simple_sql_name(a_reporter_name));

-- a report is a valid reporter if it can be assigned as element of ut_reporters collection

execute immediate

'declare

r ut_reporters;

begin

r := ut_reporters('||l_reporter_name||'());

end;';

return l_reporter_name;

exception

when e_not_a_reporter or e_invalid_object then

raise_application_error(-20000, 'Invalid reporter name specified: '||a_reporter_name);

end;

function parse_reporting_params(a_params ut_varchar2_list) return tt_call_params is

l_default_call_param t_call_param;

l_call_params tt_call_params := tt_call_params();

l_force_out_to_screen boolean;

l_param_regex varchar2(20) := '^-([fos])(\=(.*))?$';

begin

for param in(

select regexp_substr(column_value,l_param_regex,1,1,'c',1) param_type,

regexp_substr(column_value,l_param_regex,1,1,'c',3) param_value

from table(a_params)

where column_value is not null

and regexp_like(column_value,l_param_regex)

) loop

if param.param_type = 'f' or l_call_params.last is null then

l_call_params.extend;

l_call_params(l_call_params.last) := l_default_call_param;

if param.param_type = 'f' then

l_call_params(l_call_params.last).ut_reporter_name := is_reporter(param.param_value);

end if;

l_force_out_to_screen := false;

end if;

if param.param_type = 'o' then

l_call_params(l_call_params.last).output_file_name := param.param_value;

if not l_force_out_to_screen then

l_call_params(l_call_params.last).output_to_screen := 'off';

end if;

elsif param.param_type = 's' then

l_call_params(l_call_params.last).output_to_screen := 'on';

l_force_out_to_screen := true;

end if;

end loop;

if l_call_params.count = 0 then

l_call_params.extend;

l_call_params(1) := l_default_call_param;

end if;

for i in 1 .. cardinality(l_call_params) loop

l_call_params(i).reporter_id := sys_guid();

end loop;

return l_call_params;

end;

function parse_suite_paths_param(a_params ut_varchar2_list) return varchar2 is

l_paths varchar2(4000);

begin

select ''''||replace(ut_paths,',',''',''')||''''

into l_paths

from (select regexp_substr(column_value,'-p\=(.*)',1,1,'c',1) as ut_paths from table(a_params) )

where ut_paths is not null;

exception

when no_data_found then

l_paths := 'user';

when too_many_rows then

raise_application_error(-20000, 'Parameter "-p=ut_path(s)" defined more than once. Only one "-p=ut_path(s)" parameter can be used.');

end;

return l_paths;

end;

function parse_color_enabled(a_params ut_varchar2_list) return varchar2 is

begin

for i in 1 .. cardinality(a_params) loop

if a_params(i) = '-c' then

return 'true';

end if;

end loop;

return 'false';

end;

function parse_source_files_path_param(a_params ut_varchar2_list, a_param_name varchar2) return varchar2 is

l_path varchar2(4000);

begin

select param_value

into l_path

from (select regexp_substr(column_value,'-'||a_param_name||'\=(.*)',1,1,'c',1) as param_value from table(a_params) )

where param_value is not null;

exception

when no_data_found then

null;

when too_many_rows then

raise_application_error(-20000, 'Parameter "-'||a_param_name||'='||a_param_name||'" defined more than once. Only one "-'||a_param_name||'='||a_param_name||'" parameter can be used.');

end;

return l_path;

end;

begin

l_call_params := parse_reporting_params(l_input_params);

for i in l_call_params.first .. l_call_params.last loop

l_run_cursor_sql :=

l_run_cursor_sql ||

'select '''||l_call_params(i).reporter_id||''' as reporter_id,' ||

' '''||l_call_params(i).ut_reporter_name||''' as reporter_name' ||

' from dual';

l_out_cursor_sql :=

l_out_cursor_sql ||

'select '''||l_call_params(i).reporter_id||''' as reporter_id,' ||

' '''||l_call_params(i).output_to_screen||''' as output_to_screen,' ||

' '''||l_call_params(i).output_file_name||''' as output_file_name' ||

' from dual';

if i < l_call_params.last then

l_run_cursor_sql := l_run_cursor_sql || ' union all ';

l_out_cursor_sql := l_out_cursor_sql || ' union all ';

end if;

end loop;

:l_paths := parse_suite_paths_param(l_input_params);

:l_color_enabled := parse_color_enabled(l_input_params);

:l_source_path := parse_source_files_path_param(l_input_params,'source_path');

:l_test_path := parse_source_files_path_param(l_input_params,'test_path');

if l_run_cursor_sql is not null then

open :l_run_params_cur for l_run_cursor_sql;

end if;

if l_out_cursor_sql is not null then

open :l_out_params_cur for l_out_cursor_sql;

end if;

dbms_output.put_line(:l_source_path);

end;

set termout off

/**

* Convert paths to substitution variable

column source_path new_value source_path noprint;

select :l_source_path as source_path from dual;

column test_path new_value test_path noprint;

select :l_test_path as test_path from dual;

--try running on windows

$ "&&client_path\file_list.bat" "&&project_path" "l_source_files" "&&client_path\source_file_list.sql.tmp" "&&source_path"

$ "&&client_path\file_list.bat" "&&project_path" "l_test_files" "&&client_path\test_file_list.sql.tmp" "&&test_path"

--try running on linux/unix

! "&&client_path/file_list" "&&project_path" "l_source_files" "&&client_path/source_file_list.sql.tmp" "&&source_path"

! "&&client_path/file_list" "&&project_path" "l_test_files" "&&client_path/test_file_list.sql.tmp" "&&test_path"

undef source_path

undef test_path

* Generate the project source and tests files, saving it into the l_source_files and l_test_files bind variables

@&&client_path/source_file_list.sql.tmp

@&&client_path/test_file_list.sql.tmp

* Generate runner script

spool &&client_path/run_in_background.sql.tmp

declare

l_reporter_id varchar2(250);

l_reporter_name varchar2(250);

l_file_path varchar2(32767);

procedure p(a_text varchar2) is begin dbms_output.put_line(a_text); end;

begin

p('set serveroutput on size unlimited format truncated');

p('set trimspool on');

p('set pagesize 0');

p('set linesize 4000');

p('spool ut_run.dbms_output.log');

p('declare');

p(' v_reporter ut_reporter_base;');

p(' v_reporters_list ut_reporters := ut_reporters();');

p(' v_source_files ut_varchar2_list := ut_varchar2_list();');

p(' v_test_files ut_varchar2_list := ut_varchar2_list();');

p('begin');

if :l_run_params_cur%isopen then

loop

fetch :l_run_params_cur into l_reporter_id, l_reporter_name;

exit when :l_run_params_cur%notfound;

p(' v_reporter := '||l_reporter_name||'();');

p(' v_reporter.reporter_id := '''||l_reporter_id||''';');

p(' v_reporters_list.extend; v_reporters_list(v_reporters_list.last) := v_reporter;');

end loop;

close :l_run_params_cur;

end if;

loop

fetch :l_source_files into l_file_path;

exit when :l_source_files%notfound or l_file_path is null;

p(' v_source_files.extend; v_source_files(v_source_files.last) := '''||l_file_path||''';');

end loop;

loop

fetch :l_test_files into l_file_path;

exit when :l_test_files%notfound or l_file_path is null;

p(' v_test_files.extend; v_test_files(v_test_files.last) := '''||l_file_path||''';');

end loop;

p(' ut_runner.run( ');

p(' a_paths => ut_varchar2_list('||:l_paths||'),');

p(' a_reporters => v_reporters_list,');

p(' a_source_files => v_source_files,');

p(' a_test_files => v_test_files,');

p(' a_color_console => '||:l_color_enabled||' );');

p('end;');

p('/');

p('spool off');

p('exit');

end;

spool off

* Generate output retrieval script

spool &&client_path/gather_data_from_outputs.sql.tmp

declare

l_reporter_id varchar2(250);

l_output_file_name varchar2(250);

l_output_to_screen varchar2(250);

l_need_spool boolean;

procedure p(a_text varchar2) is begin dbms_output.put_line(a_text); end;

begin

if :l_out_params_cur%isopen then

loop

fetch :l_out_params_cur into l_reporter_id, l_output_to_screen, l_output_file_name;

exit when :l_out_params_cur%notfound;

l_need_spool := (l_output_file_name is not null);

p( 'set termout '||l_output_to_screen);

if l_need_spool then

p( 'spool '||l_output_file_name);

end if;

p( 'select * from table( ut_output_buffer.get_lines('''||l_reporter_id||''') );');

if l_need_spool then

p('spool off');

end if;

end loop;

end if;

end;

spool off

* Execute runner script in background process

set define #

--try running on windows

$ start /min sqlplus ##conn_str @##client_path/run_in_background.sql.tmp

--try running on linux/unix

! sqlplus ##conn_str @##client_path/run_in_background.sql.tmp &

set define &

set termout on

--make sure we fetch row by row to indicate the progress

set arraysize 1

* Gather outputs from reporters one by one while runner script executes.

@&&client_path/gather_data_from_outputs.sql.tmp

set termout off

* cleanup temporary sql files

--try running on windows

$ del &&client_path\*.sql.tmp

--try running on linux/unix

! rm &&client_path/*.sql.tmp

exit