iam.tj Git - webmin.git/blob - mysql/view

1 #!/usr/local/bin/perl

2 # view_table.cgi

3 # Display all data in some table

4

5 if (-r 'mysql-lib.pl') {

6 require './mysql-lib.pl';

7 }

8 else {

9 require './postgresql-lib.pl';

10 }

11 require './view-lib.pl';

12

13 if ($config{'charset'}) {

14 $main::force_charset = $config{'charset'};

15 }

16 if ($ENV{'CONTENT_TYPE'} !~ /boundary=/) {

17 &ReadParse();

18 }

19 else {

20 &ReadParseMime();

21 }

22 &can_edit_db($in{'db'}) || &error($text{'dbase_ecannot'});

23 @str = &table_structure($in{'db'}, $in{'table'});

24 foreach $s (@str) {

25 $keyed++ if ($s->{'key'} eq 'PRI');

26 }

27 if (!$keyed && $module_name eq "postgresql") {

28 # Can use oid as key

29 eval { $main::error_must_die = 1;

30 $d = &execute_sql($in{'db'}, "select oid from ".

31 &quote_table($in{'table'}).

32 " where 0 = 1"); };

33 if (!$@) {

34 # Has an OID, so use it

35 $use_oids = 1;

36 $keyed = 1;

37 }

38 }

39

40 # Get search SQL

41 ($search, $searchhids, $searchargs, $advcount) = &get_search_args(\%in);

42

43 # Work out start position

44 $d = &execute_sql_safe($in{'db'},

45 "select count(*) from ".&quote_table($in{'table'})." ".$search);

46 $total = int($d->{'data'}->[0]->[0]);

47 if ($in{'jump'} > 0) {

48 $in{'start'} = int($in{'jump'} / $displayconfig{'perpage'}) *

49 $displayconfig{'perpage'};

50 if ($in{'start'} >= $total) {

51 $in{'start'} = $total - $displayconfig{'perpage'};

52 $in{'start'} = int(($in{'start'} / $displayconfig{'perpage'}) + 1) *

53 $displayconfig{'perpage'};

54 }

55 }

56 else {

57 $in{'start'} = int($in{'start'});

58 }

59 if ($in{'new'} && $total > $displayconfig{'perpage'}) {

60 # go to the last screen for adding a row

61 $in{'start'} = $total - $displayconfig{'perpage'};

62 $in{'start'} = int(($in{'start'} / $displayconfig{'perpage'}) + 1) *

63 $displayconfig{'perpage'};

64 }

65

66 # Get limiting and sorting SQL

67 $limitsql = &get_search_limit(\%in);

68 ($sortsql, $sorthids, $sortargs) = &get_search_sort(\%in);

69

70 # Work out where clause for rows we are operating on

71 $where_select = "select ".($use_oids ? "oid" : "*").

72 " from ".&quote_table($in{'table'})." $search $sortsql $limitsql";

73

74 if ($in{'delete'}) {

75 # Deleting selected rows

76 $d = &execute_sql($in{'db'}, $where_select);

77 @t = map { $_->{'field'} } @str;

78 $count = 0;

79 foreach $r (split(/\0/, $in{'row'})) {

80 local @where;

81 local @r = @{$d->{'data'}->[$r]};

82 if ($use_oids) {

83 # Where clause just uses OID

84 push(@where, "oid = $r[0]");

85 }

86 else {

87 # Where clause uses keys

88 for($i=0; $i<@t; $i++) {

89 if ($str[$i]->{'key'} eq 'PRI') {

90 if ($r[$i] eq 'NULL') {

91 push(@where, &quotestr($t[$i]).

92 " is null");

93 }

94 else {

95 $r[$i] =~ s/'/''/g;

96 push(@where, &quotestr($t[$i]).

97 " = '$r[$i]'");

98 }

99 }

100 }

101 }

102 &execute_sql_logged($in{'db'},

103 "delete from ".&quote_table($in{'table'}).

104 " where ".join(" and ", @where));

105 $count++;

106 }

107 &webmin_log("delete", "data", $count, \%in);

108 &redirect("view_table.cgi?db=$in{'db'}&".

109 "table=".&urlize($in{'table'})."&start=$in{'start'}".

110 $searchargs.$sortargs);

111 }

112 elsif ($in{'save'}) {

113 # Update edited rows

114 $d = &execute_sql($in{'db'}, $where_select);

115 @t = map { $_->{'field'} } @str;

116 $count = 0;

117 for($j=0; $j<$displayconfig{'perpage'}; $j++) {

118 next if (!defined($in{"${j}_$t[0]"}));

119 local (@where, @set);

120 local @r = @{$d->{'data'}->[$j]};

121 local @params;

122 if ($use_oids) {

123 # Where clause just uses OID

124 push(@where, "oid = $r[0]");

125 }

126 for($i=0; $i<@t; $i++) {

127 if (!$use_oids) {

128 # Where clause uses keys

129 if ($str[$i]->{'key'} eq 'PRI') {

130 if ($r[$i] eq 'NULL') {

131 push(@where, &quotestr($t[$i]).

132 " is null");

133 }

134 else {

135 $r[$i] =~ s/'/''/g;

136 push(@where, &quotestr($t[$i]).

137 " = '$r[$i]'");

138 }

139 }

140 }

141 local $ij = $in{"${j}_$t[$i]"};

142 local $ijdef = $in{"${j}_$t[$i]_def"};

143 next if ($ijdef || !defined($ij));

144 if (!$displayconfig{'blob_mode'} || !&is_blob($str[$i])) {

145 $ij =~ s/\r//g;

146 }

147 push(@set, &quotestr($t[$i])." = ?");

148 push(@params, $ij eq '' ? undef : $ij);

149 }

150 &execute_sql_logged($in{'db'},

151 "update ".&quote_table($in{'table'})." set ".

152 join(" , ", @set)." where ".

153 join(" and ", @where), @params);

154 $count++;

155 }

156 &webmin_log("modify", "data", $count, \%in);

157 &redirect("view_table.cgi?db=$in{'db'}&".

158 "table=".&urlize($in{'table'})."&start=$in{'start'}".

159 $searchargs.$sortargs);

160 }

161 elsif ($in{'savenew'}) {

162 # Adding a new row

163 for($j=0; $j<@str; $j++) {

164 if (!$displayconfig{'blob_mode'} || !&is_blob($str[$j])) {

165 $in{$j} =~ s/\r//g;

166 }

167 push(@set, $in{$j} eq '' ? undef : $in{$j});

168 }

169 &execute_sql_logged($in{'db'}, "insert into ".&quote_table($in{'table'}).

170 " values (".join(" , ", map { "?" } @set).")", @set);

171 &redirect("view_table.cgi?db=$in{'db'}&".

172 "table=".&urlize($in{'table'})."&start=$in{'start'}".

173 $searchargs.$sortargs);

174 &webmin_log("create", "data", undef, \%in);

175 }

176 elsif ($in{'cancel'} || $in{'new'}) {

177 undef($in{'row'});

178 }

179

180 $desc = &text('table_header', "<tt>$in{'table'}</tt>", "<tt>$in{'db'}</tt>");

181 &ui_print_header($desc, $text{'view_title'}, "");

182

183 if ($in{'start'} || $total > $displayconfig{'perpage'}) {

184 print "<center>\n";

185 if ($in{'start'}) {

186 printf "<a href='view_table.cgi?db=%s&table=%s&start=%s%s%s'>".

187 "<img src=../images/left.gif border=0 align=middle></a>\n",

188 $in{'db'}, $in{'table'},

189 $in{'start'} - $displayconfig{'perpage'},

190 $searchargs, $sortargs;

191 }

192 print "<font size=+1>",&text('view_pos', $in{'start'}+1,

193 $in{'start'}+$displayconfig{'perpage'} > $total ? $total :

194 $in{'start'}+$displayconfig{'perpage'}, $total),"</font>\n";

195 if ($in{'start'}+$displayconfig{'perpage'} < $total) {

196 printf "<a href='view_table.cgi?db=%s&table=%s&start=%s%s%s'>".

197 "<img src=../images/right.gif border=0 align=middle></a> ",

198 $in{'db'}, $in{'table'},

199 $in{'start'} + $displayconfig{'perpage'},

200 $searchargs, $sortargs;

201 }

202 print "</center>\n";

203 }

204

205 print "<table width=100% cellspacing=0 cellpadding=0>\n";

206

207 if ($in{'field'}) {

208 # Show details of simple search

209 print "<tr> <td><b>",&text('view_searchhead', "<tt>$in{'for'}</tt>",

210 "<tt>$in{'field'}</tt>"),"</b></td>\n";

211 print "<td align=right><a href='view_table.cgi?db=$in{'db'}&",

212 "table=$in{'table'}$sortargs'>$text{'view_searchreset'}</a></td> </tr>\n";

213 }

214 elsif ($in{'advanced'}) {

215 # Show details of advanced search

216 print "<tr> <td><b>",&text('view_searchhead2', $advcount),"</b></td>\n";

217 print "<td align=right><a href='view_table.cgi?db=$in{'db'}&",

218 "table=$in{'table'}$sortargs'>$text{'view_searchreset'}</a></td> </tr>\n";

219 }

220 if ($in{'sortfield'}) {

221 # Show current sort order

222 print "<tr> <td><b>",&text($in{'sortdir'} ? 'view_sorthead2' : 'view_sorthead1',

223 "<tt>$in{'sortfield'}</tt>"),"</b></td>\n";

224 print "<td align=right><a href='view_table.cgi?db=$in{'db'}&",

225 "table=$in{'table'}$searchargs'>$text{'view_sortreset'}</a></td> </tr>\n";

226 }

227

228 print "</table>\n";

229

230 if ($displayconfig{'blob_mode'}) {

231 print &ui_form_start("view_table.cgi", "form-data");

232 }

233 else {

234 print &ui_form_start("view_table.cgi", "post");

235 }

236 print &ui_hidden("db", $in{'db'}),"\n";

237 print &ui_hidden("table", $in{'table'}),"\n";

238 print &ui_hidden("start", $in{'start'}),"\n";

239 print $searchhids;

240 print $sorthids;

241 $check = !defined($in{'row'}) && !$in{'new'} && $keyed;

242 if ($total || $in{'new'}) {

243 # Get the rows of data, and show the table header

244 $d = &execute_sql_safe($in{'db'},

245 "select * from ".&quote_table($in{'table'})." $search $sortsql $limitsql");

246 @data = @{$d->{'data'}};

247 @tds = $check ? ( "width=5" ) : ( );

248 ($has_blob) = grep { &is_blob($_) } @str;

249

250 @rowlinks = $check ? ( &select_all_link("row"),

251 &select_invert_link("row") ) : ( );

252 print &ui_links_row(\@rowlinks);

253 print &ui_columns_start([

254 $check ? ( "" ) : ( ),

255 map { &column_sort_link($_->{'field'}) } @str

256 ], 100, 0, \@tds);

257

258 # Add an empty row for inserting

259 $realrows = scalar(@data);

260 if ($in{'new'}) {

261 push(@data, [ map { $_->{'default'} eq 'NULL' ? '' :

262 $_->{'default'} eq 'CURRENT_TIMESTAMP' ? '':

263 $_->{'default'} } @str ]);

264 $row{$realrows} = 1;

265 }

266

267 # Show the rows, some of which may be editable

268 map { $row{$_}++ } split(/\0/, $in{'row'});

269 $w = int(100 / scalar(@str));

270 $w = 10 if ($w < 10);

271 for($i=0; $i<@data; $i++) {

272 local @d = map { $_ eq "NULL" ? undef : $_ } @{$data[$i]};

273 if ($row{$i} && ($displayconfig{'add_mode'} || $has_blob)) {

274 # Show multi-line row editor

275 $et = "<table border>\n";

276 $et .= "<tr $tb> <td><b>$text{'view_field'}</b></td> ".

277 "<td><b>$text{'view_data'}</b></td> </tr>\n";

278 for($j=0; $j<@str; $j++) {

279 local $nm = $i == $realrows ? $j :

280 "${i}_$str[$j]->{'field'}";

281 $et .= "<tr $cb> <td><b>$str[$j]->{'field'}</b></td> <td>\n";

282 if ($displayconfig{'blob_mode'} &&

283 &is_blob($str[$j]) && $d[$j]) {

284 # Show as keep/upload inputs

285 $et .= &ui_radio($nm."_def", 1,

286 [ [ 1, $text{'view_keep'} ],

287 [ 0, $text{'view_set'} ] ])." ".

288 &ui_upload($nm);

289 }

290 elsif ($displayconfig{'blob_mode'} &&

291 &is_blob($str[$j])) {

292 # Show upload input

293 $et .= &ui_upload($nm);

294 }

295 elsif ($str[$j]->{'type'} =~ /^enum$(.*)$$/) {

296 # Show as enum list

297 $et .= &ui_select($nm, $d[$j],

298 [ [ "", " " ],

299 map { [ $_ ] } &split_enum($1) ],

300 1, 0, 1);

301 }

302 elsif ($str[$j]->{'type'} =~ /$(\d+)$/) {

303 # Show as known-size text

304 if ($1 > 255) {

305 # Too big, use text area

306 $et .= &ui_textarea(

307 $nm, $d[$j], 5, 70);

308 }

309 else {

310 # Text box

311 local $nw = $1 > 70 ? 70 : $1;

312 $et .= &ui_textbox(

313 $nm, $d[$j], $nw);

314 }

315 }

316 elsif (&is_blob($str[$j])) {

317 # Show as multiline text

318 $et .= &ui_textarea($nm, $d[$j], 5, 70);

319 }

320 else {

321 # Show as fixed-size text

322 $et .= &ui_textbox($nm, $d[$j], 30);

323 }

324 $et .= "</td></tr>\n";

325 }

326 $et .= "</table>";

327 print &ui_columns_row([ $check ? ( "" ) : ( ), $et ],

328 [ @tds, "colspan=".scalar(@d) ] );

329 }

330 elsif ($row{$i}) {

331 # Show one-line row-editor

332 local @cols;

333 for($j=0; $j<@d; $j++) {

334 local $l = $d[$j] =~ tr/\n/\n/;

335 local $nm = $i == $realrows ? $j :

336 "${i}_$d->{'titles'}->[$j]";

337 if ($displayconfig{'blob_mode'} &&

338 &is_blob($str[$j])) {

339 # Cannot edit this blob

340 push(@cols, undef);

341 }

342 elsif ($str[$j]->{'type'} =~ /^enum$(.*)$$/) {

343 # Show as enum list

344 push(@cols, &ui_select($nm, $d[$j],

345 [ [ "", " " ],

346 map { [ $_ ] } &split_enum($1) ],

347 1, 0, 1));

348 }

349 elsif ($str[$j]->{'type'} =~ /$(\d+)$/) {

350 # Show as known-size text

351 local $nw = $1 > 70 ? 70 : $1;

352 push(@cols,

353 &ui_textbox($nm, $d[$j], $nw));

354 }

355 elsif ($l) {

356 # Show as multiline text

357 $l++;

358 push(@cols,

359 &ui_textarea($nm, $d[$j], $l, $w));

360 }

361 else {

362 # Show as known size text

363 push(@cols,

364 &ui_textbox($nm, $d[$j], $w));

365 }

366 }

367 print &ui_columns_row([ $check ? ( "" ) : ( ), @cols ],

368 \@tds);

369 }

370 else {

371 # Show row contents

372 local @cols;

373 local $j = 0;

374 foreach $c (@d) {

375 if ($displayconfig{'blob_mode'} &&

376 &is_blob($str[$j]) && $c ne '') {

377 # Show download link for blob

378 push(@cols, "<a href='download.cgi?db=$in{'db'}&table=$in{'table'}&start=$in{'start'}".$searchargs.$sortargs."&row=$i&col=$j'>$text{'view_download'}</a>");

379 }

380 else {

381 # Just show text (up to limit)

382 if ($config{'max_text'} &&

383 length($c) > $config{'max_text'}) {

384 $c = substr($c, 0,

385 $config{'max_text'})." ...";

386 }

387 push(@cols, &html_escape($c));

388 }

389 $j++;

390 }

391 if ($check) {

392 print &ui_checked_columns_row(\@cols, \@tds,

393 "row", $i);

394 }

395 else {

396 print &ui_columns_row(\@cols, \@tds);

397 }

398 }

399 }

400 print &ui_columns_end();

401 print &ui_links_row(\@rowlinks);

402 }

403 else {

404 print "<b>$text{'view_none'}</b> <p>\n";

405 }

406

407 # Show buttons to edit / delete rows

408 if (!$keyed) {

409 print "<b>$text{'view_nokey'}</b><p>\n";

410 print &ui_form_end();

411 }

412 elsif (!$check) {

413 if ($in{'new'}) {

414 print &ui_form_end([ [ "savenew", $text{'save'} ],

415 [ "cancel", $text{'cancel'} ] ]);

416 }

417 else {

418 print &ui_form_end([ [ "save", $text{'save'} ],

419 [ "cancel", $text{'cancel'} ] ]);

420 }

421 }

422 elsif ($total) {

423 print &ui_form_end([ [ "edit", $text{'view_edit'} ],

424 [ "new", $text{'view_new'} ],

425 [ "delete", $text{'view_delete'} ] ]);

426 }

427 else {

428 print &ui_form_end([ [ "new", $text{'view_new'} ] ]);

429 }

430

431 if (!$in{'field'} && $total > $displayconfig{'perpage'}) {

432 # Show search and jump buttons

433 print &ui_hr();

434 print "<table width=100%><tr>\n";

435 print "<form action=view_table.cgi>\n";

436 print "<input type=hidden name=search value=1>\n";

437 print &ui_hidden("db", $in{'db'});

438 print &ui_hidden("table", $in{'table'});

439 $sel = &ui_select("field", undef,

440 [ map { [ $_->{'field'}, $_->{'field'} ] } @str ]);

441 $match = &ui_select("match", 0,

442 [ map { [ $_, $text{'view_match'.$_} ] } (0.. 5) ]);

443 print "<td>",&text('view_search2', "<input name=for size=20>", $sel,

444 $match);

445 print "  ",

446 "<input type=submit value='$text{'view_searchok'}'></td>\n";

447 print "</form>\n";

448

449 print "<form action=view_table.cgi>\n";

450 print &ui_hidden("db", $in{'db'});

451 print &ui_hidden("table", $in{'table'});

452 print "<td align=right><input type=submit value='$text{'view_jump'}'> ";

453 print "<input name=jump size=6></td></form>\n";

454

455 print "</tr><tr>\n";

456

457 print "<form action=search_form.cgi>\n";

458 print &ui_hidden("db", $in{'db'});

459 print &ui_hidden("table", $in{'table'});

460 print "<td><input type=submit value='$text{'view_adv'}'></td>\n";

461 print "</form>\n";

462

463 print "</tr> </table>\n";

464 }

465

466 if ($access{'edonly'}) {

467 &ui_print_footer("edit_dbase.cgi?db=$in{'db'}",$text{'dbase_return'},

468 "", $text{'index_return'});

469 }

470 else {

471 &ui_print_footer("edit_table.cgi?db=$in{'db'}&table=".

472 &urlize($in{'table'}),

473 $text{'table_return'},

474 "edit_dbase.cgi?db=$in{'db'}", $text{'dbase_return'},

475 "", $text{'index_return'});

476 }

477

478 # column_sort_link(name)

479 # Returns HTML for a link to switch sorting mode

480 sub column_sort_link

481 {

482 local ($field) = @_;

483 local $dir = $in{'sortfield'} eq $field ? !$in{'sortdir'} : 0;

484 local $img = $in{'sortfield'} eq $field && $dir ? "sortascgrey.gif" :

485 $in{'sortfield'} eq $field && !$dir ? "sortdescgrey.gif" :

486 $dir ? "sortasc.gif" : "sortdesc.gif";

487 return "<a href='view_table.cgi?db=$in{'db'}&table=".

488 &urlize($in{'table'})."&start=$in{'start'}&sortfield=$field&sortdir=$dir$searchargs'>".

489 "<b>$field</b><img valign=middle src=../images/$img border=0>";

490 }

491