~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, InterBase, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid, SAPDB or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n";

}

if ($name =~ /_ODBC$/i || defined($odbc) && $odbc)

{

if (! ($server->{'data_source'} =~ /^([^:]*):([^:]+):([^:]*)/ ))

{

die "Can't find databasename in data_source: '" .

$server->{'data_source'}. "'\n";

}

if ($3) {

$server->{'data_source'} = "$1:ODBC:$3";

} else {

$server->{'data_source'} = "$1:ODBC:$database";

}

return $server;

}

sub all_servers

{

return ["Access", "Adabas", "DB2", "Empress", "FrontBase", "Oracle",

100

"Informix", "InterBase", "Mimer", "mSQL", "MS-SQL", "MySQL", "Pg","SAPDB",

101

"Solid", "Sybase"];

102

}

103

104

#############################################################################

105

# First the configuration for MySQL off course :-)

106

#############################################################################

107

108

package db_MySQL;

109

110

sub new

111

{

112

my ($type,$host,$database,$machine,$socket,$connect_options)= @_;

113

my $self= {};

114

my %limits;

115

bless $self;

116

117

$self->{'cmp_name'} = "mysql";

118

$self->{'data_source'} = "DBI:mysql:database=$database;host=$host";

119

$self->{'data_source'} .= ";mysql_socket=$socket" if($socket);

120

$self->{'data_source'} .= ";$connect_options" if($connect_options);

121

$self->{'limits'} = \%limits;

122

$self->{'blob'} = "blob";

123

$self->{'text'} = "text";

124

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

125

$self->{'vacuum'} = 1; # When using with --fast

126

$self->{'drop_attr'} = "";

127

$self->{'transactions'} = 0; # Transactions disabled by default

128

129

$limits{'NEG'} = 1; # Supports -id

130

$limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int;

131

$limits{'alter_table'} = 1; # Have ALTER TABLE

132

$limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column

133

$limits{'column_alias'} = 1; # Alias for fields in select statement.

134

$limits{'func_extra_%'} = 1; # Has % as alias for mod()

135

$limits{'func_extra_if'} = 1; # Have function if.

136

$limits{'func_extra_in_num'} = 1; # Has function in

137

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

138

$limits{'func_odbc_mod'} = 1; # Have function mod.

139

$limits{'functions'} = 1; # Has simple functions (+/-)

140

$limits{'group_by_position'} = 1; # Can use 'GROUP BY 1'

141

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

142

$limits{'group_func_extra_std'} = 1; # Have group function std().

143

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

144

$limits{'group_functions'} = 1; # Have group functions

145

$limits{'having_with_alias'} = 1; # Can use aliases in HAVING

146

$limits{'having_with_group'} = 1; # Can use group functions in HAVING

147

$limits{'insert_multi_value'} = 1; # Have INSERT ... values (1,2),(3,4)

148

$limits{'insert_select'} = 1;

149

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

150

$limits{'left_outer_join'} = 1; # Supports left outer joins

151

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

152

$limits{'limit'} = 1; # supports the limit attribute

153

$limits{'truncate_table'} = 1;

154

$limits{'load_data_infile'} = 1; # Has load data infile

155

$limits{'lock_tables'} = 1; # Has lock tables

156

$limits{'max_column_name'} = 64; # max table and column name

157

$limits{'max_columns'} = 2000; # Max number of columns in table

158

$limits{'max_conditions'} = 9999; # (Actually not a limit)

159

$limits{'max_index'} = 16; # Max number of keys

160

$limits{'max_index_parts'} = 16; # Max segments/key

161

$limits{'max_tables'} = (($machine || '') =~ "^win") ? 5000 : 65000;

162

$limits{'max_text_size'} = 1000000; # Good enough for tests

163

$limits{'multi_drop'} = 1; # Drop table can take many tables

164

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

165

$limits{'order_by_unused'} = 1;

166

$limits{'query_size'} = 1000000; # Max size with default buffers.

167

$limits{'select_without_from'}= 1; # Can do 'select 1';

168

$limits{'subqueries'} = 0; # Doesn't support sub-queries.

169

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

170

$limits{'unique_index'} = 1; # Unique index works or not

171

$limits{'working_all_fields'} = 1;

172

$limits{'working_blobs'} = 1; # If big varchar/blobs works

173

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

174

175

# Some fixes that depends on the environment

176

if (defined($main::opt_create_options) &&

177

$main::opt_create_options =~ /engine=heap/i)

178

{

179

$limits{'working_blobs'} = 0; # HEAP tables can't handle BLOB's

180

}

181

if (defined($main::opt_create_options) &&

182

$main::opt_create_options =~ /engine=innodb/i)

183

{

184

$self->{'transactions'} = 1; # Transactions enabled

185

}

186

if (defined($main::opt_create_options) &&

187

$main::opt_create_options =~ /engine=ndb/i)

188

{

189

$self->{'transactions'} = 1; # Transactions enabled

190

$limits{'max_columns'} = 90; # Max number of columns in table

191

$limits{'max_tables'} = 32; # No comments

192

}

193

if (defined($main::opt_create_options) &&

194

$main::opt_create_options =~ /engine=bdb/i)

195

{

196

$self->{'transactions'} = 1; # Transactions enabled

197

}

198

if (defined($main::opt_create_options) &&

199

$main::opt_create_options =~ /engine=gemini/i)

200

{

201

$limits{'working_blobs'} = 0; # Blobs not implemented yet

202

$limits{'max_tables'} = 500;

203

$self->{'transactions'} = 1; # Transactions enabled

204

}

205

206

return $self;

207

}

208

209

210

# Get the version number of the database

211

212

213

sub version

214

{

215

my ($self)=@_;

216

my ($dbh,$sth,$version,@row);

217

218

$dbh=$self->connect();

219

$sth = $dbh->prepare("select VERSION()") or die $DBI::errstr;

220

$version="MySQL 3.20.?";

221

if ($sth->execute && (@row = $sth->fetchrow_array))

222

{

223

$row[0] =~ s/-/ /g; # To get better tables with long names

224

$version="MySQL $row[0]";

225

}

226

$sth->finish;

227

228

$sth = $dbh->prepare("show status like 'ssl_version'") or die $DBI::errstr;

229

if ($sth->execute && (@row = $sth->fetchrow_array) && $row[1])

230

{

231

$version .= "/$row[1]";

232

}

233

$sth->finish;

234

$dbh->disconnect;

235

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

236

return $version;

237

}

238

239

240

# Connection with optional disabling of logging

241

242

243

sub connect

244

{

245

my ($self)=@_;

246

my ($dbh);

247

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

248

$main::opt_password,{ PrintError => 0}) ||

249

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

250

251

$dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");

252

return $dbh;

253

}

254

255

256

# Returns a list of statements to create a table

257

# The field types are in ANSI SQL format.

258

259

# If one uses $main::opt_fast then one is allowed to use

260

# non standard types to get better speed.

261

262

263

sub create

264

{

265

my($self,$table_name,$fields,$index,$options) = @_;

266

my($query,@queries);

267

268

$query="create table $table_name (";

269

foreach $field (@$fields)

270

{

271

# $field =~ s/ decimal/ double(10,2)/i;

272

$field =~ s/ big_decimal/ double(10,2)/i;

273

$query.= $field . ',';

274

}

275

foreach $index (@$index)

276

{

277

$query.= $index . ',';

278

}

279

substr($query,-1)=")"; # Remove last ',';

280

$query.=" $options" if (defined($options));

281

$query.=" $main::opt_create_options" if (defined($main::opt_create_options));

282

push(@queries,$query);

283

return @queries;

284

}

285

286

sub insert_file {

287

my ($self,$dbname, $file, $dbh) = @_;

288

my ($command, $sth);

289

290

$file =~ s|\\|/|g; # Change Win32 names to Unix syntax

291

$command = "load data infile '$file' into table $dbname columns optionally enclosed by '\\'' terminated by ','";

292

# print "$command\n";

293

$sth = $dbh->do($command) or die $DBI::errstr;

294

return $sth; # Contains number of rows

295

}

296

297

298

# Do any conversions to the ANSI SQL query so that the database can handle it

299

300

301

sub query {

302

my($self,$sql) = @_;

303

return $sql;

304

}

305

306

sub drop_index {

307

my ($self,$table,$index) = @_;

308

return "DROP INDEX $index ON $table";

309

}

310

311

312

# Abort if the server has crashed

313

# return: 0 if ok

314

# 1 question should be retried

315

316

317

sub abort_if_fatal_error

318

{

319

return 0;

320

}

321

322

323

# This should return 1 if we to do disconnect / connect when doing

324

# big batches

325

326

327

sub small_rollback_segment

328

{

329

return 0;

330

}

331

332

333

# reconnect on errors (needed mainly be crash-me)

334

335

336

sub reconnect_on_errors

337

{

338

return 0;

339

}

340

341

sub fix_for_insert

342

{

343

my ($self,$cmd) = @_;

344

return $cmd;

345

}

346

347

348

# Optimize tables for better performance

349

350

351

sub vacuum

352

{

353

my ($self,$full_vacuum,$dbh_ref,@tables)=@_;

354

my ($loop_time,$end_time,$dbh);

355

if ($#tables >= 0)

356

{

357

$dbh=$$dbh_ref;

358

$loop_time=new Benchmark;

359

$dbh->do("OPTIMIZE TABLE " . join(',',@tables)) || die "Got error: $DBI::errstr when executing 'OPTIMIZE TABLE'\n";

360

$end_time=new Benchmark;

361

print "Time for book-keeping (1): " .

362

Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";

363

}

364

}

365

366

#############################################################################

367

# Definitions for mSQL

368

#############################################################################

369

370

package db_mSQL;

371

372

sub new

373

{

374

my ($type,$host,$database)= @_;

375

my $self= {};

376

my %limits;

377

bless $self;

378

379

$self->{'cmp_name'} = "msql";

380

$self->{'data_source'} = "DBI:mSQL:$database:$host";

381

$self->{'limits'} = \%limits;

382

$self->{'double_quotes'} = 0;

383

$self->{'drop_attr'} = "";

384

$self->{'transactions'} = 0; # No transactions

385

$self->{'blob'} = "text(" . $limits{'max_text_size'} .")";

386

$self->{'text'} = "text(" . $limits{'max_text_size'} .")";

387

388

$limits{'max_conditions'} = 74;

389

$limits{'max_columns'} = 75;

390

$limits{'max_tables'} = 65000; # Should be big enough

391

$limits{'max_text_size'} = 32000;

392

$limits{'query_size'} = 65535;

393

$limits{'max_index'} = 5;

394

$limits{'max_index_parts'} = 10;

395

$limits{'max_column_name'} = 35;

396

397

$limits{'join_optimizer'} = 0; # Can't optimize FROM tables

398

$limits{'load_data_infile'} = 0;

399

$limits{'lock_tables'} = 0;

400

$limits{'functions'} = 0;

401

$limits{'group_functions'} = 0;

402

$limits{'group_distinct_functions'}= 0; # Have count(distinct)

403

$limits{'multi_drop'} = 0;

404

$limits{'select_without_from'}= 0;

405

$limits{'subqueries'} = 0;

406

$limits{'left_outer_join'} = 0;

407

$limits{'table_wildcard'} = 0;

408

$limits{'having_with_alias'} = 0;

409

$limits{'having_with_group'} = 0;

410

$limits{'like_with_column'} = 1;

411

$limits{'order_by_position'} = 1;

412

$limits{'group_by_position'} = 1;

413

$limits{'alter_table'} = 0;

414

$limits{'alter_add_multi_col'}= 0;

415

$limits{'alter_table_dropcol'}= 0;

416

$limits{'group_func_extra_std'} = 0;

417

$limits{'limit'} = 1; # supports the limit attribute

418

$limits{'unique_index'} = 1; # Unique index works or not

419

$limits{'insert_select'} = 0;

420

421

$limits{'func_odbc_mod'} = 0;

422

$limits{'func_extra_%'} = 0;

423

$limits{'func_odbc_floor'} = 0;

424

$limits{'func_extra_if'} = 0;

425

$limits{'column_alias'} = 0;

426

$limits{'NEG'} = 0;

427

$limits{'func_extra_in_num'} = 0;

428

$limits{'working_blobs'} = 1; # If big varchar/blobs works

429

$limits{'order_by_unused'} = 1;

430

$limits{'working_all_fields'} = 1;

431

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

432

return $self;

433

}

434

435

436

# Get the version number of the database

437

438

439

sub version

440

{

441

my ($tmp,$dir);

442

foreach $dir ("/usr/local/Hughes", "/usr/local/mSQL","/my/local/mSQL",

443

"/usr/local")

444

{

445

if (-x "$dir/bin/msqladmin")

446

{

447

$tmp=`$dir/bin/msqladmin version | grep server`;

448

if ($tmp =~ /^\s*(.*\w)\s*$/)

449

{ # Strip pre- and endspace

450

$tmp=$1;

451

$tmp =~ s/\s+/ /g; # Remove unnecessary spaces

452

$tmp .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

453

454

return $tmp;

455

}

456

}

457

}

458

return "mSQL version ???";

459

}

460

461

462

sub connect

463

{

464

my ($self)=@_;

465

my ($dbh);

466

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

467

$main::opt_password,{ PrintError => 0}) ||

468

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

469

return $dbh;

470

}

471

472

473

# Can't handle many field types, so we map everything to int and real.

474

475

476

sub create

477

{

478

my($self,$table_name,$fields,$index) = @_;

479

my($query,@queries,$name,$nr);

480

481

$query="create table $table_name (";

482

foreach $field (@$fields)

483

{

484

$field =~ s/varchar/char/i; # mSQL doesn't have VARCHAR()

485

# mSQL can't handle more than the real basic int types

486

$field =~ s/tinyint|smallint|mediumint|integer/int/i;

487

# mSQL can't handle different visual lengths

488

$field =~ s/int$\d*$/int/i;

489

# mSQL doesn't have float, change it to real

490

$field =~ s/float($\d*,\d*$){0,1}/real/i;

491

$field =~ s/double($\d*,\d*$){0,1}/real/i;

492

# mSQL doesn't have blob, it has text instead

493

if ($field =~ / blob/i)

494

{

495

$name=$self->{'blob'};

496

$field =~ s/ blob/ $name/;

497

}

498

$query.= $field . ',';

499

}

500

substr($query,-1)=")"; # Remove last ',';

501

push(@queries,$query);

502

$nr=0;

503

504

# Prepend table_name to index name because the the name may clash with

505

# a field name. (Should be diffent name space, but this is mSQL...)

506

507

foreach $index (@$index)

508

{

509

# Primary key is unique index in mSQL

510

$index =~ s/primary key/unique index primary/i;

511

if ($index =~ /^unique\s*$([^\(]*)$$/i)

512

{

513

$nr++;

514

push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");

515

}

516

else

517

{

518

if (!($index =~ /^(.*index)\s+(\w*)\s+($.*$)$/i))

519

{

520

die "Can't parse index information in '$index'\n";

521

}

522

push(@queries,"create $1 ${table_name}_$2 on $table_name $3");

523

}

524

}

525

return @queries;

526

}

527

528

529

sub insert_file {

530

my($self,$dbname, $file) = @_;

531

print "insert an ascii file isn't supported by mSQL\n";

532

return 0;

533

}

534

535

536

sub query {

537

my($self,$sql) = @_;

538

return $sql;

539

}

540

541

sub drop_index

542

{

543

my ($self,$table,$index) = @_;

544

return "DROP INDEX $index FROM $table";

545

}

546

547

sub abort_if_fatal_error

548

{

549

return 0;

550

}

551

552

sub small_rollback_segment

553

{

554

return 0;

555

}

556

557

sub reconnect_on_errors

558

{

559

return 0;

560

}

561

562

sub fix_for_insert

563

{

564

my ($self,$cmd) = @_;

565

return $cmd;

566

}

567

568

#############################################################################

569

# Definitions for PostgreSQL #

570

#############################################################################

571

572

package db_Pg;

573

574

sub new

575

{

576

my ($type,$host,$database)= @_;

577

my $self= {};

578

my %limits;

579

bless $self;

580

581

$self->{'cmp_name'} = "pg";

582

$self->{'data_source'} = "DBI:Pg:dbname=$database";

583

$self->{'limits'} = \%limits;

584

$self->{'blob'} = "text";

585

$self->{'text'} = "text";

586

$self->{'double_quotes'} = 1;

587

$self->{'drop_attr'} = "";

588

$self->{'transactions'} = 1; # Transactions enabled

589

$self->{"vacuum"} = 1;

590

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

591

$limits{'load_data_infile'} = 0;

592

593

$limits{'NEG'} = 1;

594

$limits{'alter_add_multi_col'}= 0; # alter_add_multi_col ?

595

$limits{'alter_table'} = 1;

596

$limits{'alter_table_dropcol'}= 0;

597

$limits{'column_alias'} = 1;

598

$limits{'func_extra_%'} = 1;

599

$limits{'func_extra_if'} = 0;

600

$limits{'func_extra_in_num'} = 1;

601

$limits{'func_odbc_floor'} = 1;

602

$limits{'func_odbc_mod'} = 1; # Has %

603

$limits{'functions'} = 1;

604

$limits{'group_by_position'} = 1;

605

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

606

$limits{'group_func_extra_std'} = 0;

607

$limits{'group_func_sql_min_str'}= 1; # Can execute MIN() and MAX() on strings

608

$limits{'group_functions'} = 1;

609

$limits{'having_with_alias'} = 0;

610

$limits{'having_with_group'} = 1;

611

$limits{'insert_select'} = 1;

612

$limits{'left_outer_join'} = 1;

613

$limits{'like_with_column'} = 1;

614

$limits{'lock_tables'} = 0; # in ATIS gives this a problem

615

$limits{'max_column_name'} = 128;

616

$limits{'max_columns'} = 1000; # 500 crashes pg 6.3

617

$limits{'max_conditions'} = 9999; # This makes Pg real slow

618

$limits{'max_index'} = 64; # Big enough

619

$limits{'max_index_parts'} = 16;

620

$limits{'max_tables'} = 5000; # 10000 crashes pg 7.0.2

621

$limits{'max_text_size'} = 65000; # Good enough for test

622

$limits{'multi_drop'} = 1;

623

$limits{'order_by_position'} = 1;

624

$limits{'order_by_unused'} = 1;

625

$limits{'query_size'} = 16777216;

626

$limits{'select_without_from'}= 1;

627

$limits{'subqueries'} = 1;

628

$limits{'table_wildcard'} = 1;

629

$limits{'truncate_table'} = 1;

630

$limits{'unique_index'} = 1; # Unique index works or not

631

$limits{'working_all_fields'} = 1;

632

$limits{'working_blobs'} = 1; # If big varchar/blobs works

633

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

634

635

return $self;

636

}

637

638

# couldn't find the option to get the version number

639

640

sub version

641

{

642

my ($version,$dir);

643

$version = "PostgreSQL version ???";

644

foreach $dir ($ENV{'PGDATA'},"/usr/local/pgsql/data", "/usr/local/pg/data")

645

{

646

if ($dir && -e "$dir/PG_VERSION")

647

{

648

$version= `cat $dir/PG_VERSION`;

649

if ($? == 0)

650

{

651

chomp($version);

652

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

653

return "PostgreSQL $version";

654

}

655

}

656

}

657

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

658

return $version;

659

}

660

661

662

sub connect

663

{

664

my ($self)=@_;

665

my ($dbh);

666

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

667

$main::opt_password,{ PrintError => 0}) ||

668

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

669

return $dbh;

670

}

671

672

673

sub create

674

{

675

my($self,$table_name,$fields,$index) = @_;

676

my($query,@queries,$name,$in,$indfield,$table,$nr);

677

678

$query="create table $table_name (";

679

foreach $field (@$fields)

680

{

681

if ($main::opt_fast)

682

{

683

# Allow use of char2, char4, char8 or char16

684

$field =~ s/char(2|4|8|16)/char$1/;

685

}

686

# Pg can't handle more than the real basic int types

687

$field =~ s/tinyint|smallint|mediumint|integer/int/;

688

# Pg can't handle different visual lengths

689

$field =~ s/int$\d*$/int/;

690

$field =~ s/float$\d*,\d*$/float/;

691

$field =~ s/ double/ float/;

692

# $field =~ s/ decimal/ float/i;

693

# $field =~ s/ big_decimal/ float/i;

694

# $field =~ s/ date/ int/i;

695

# Pg doesn't have blob, it has text instead

696

$field =~ s/ blob/ text/;

697

$query.= $field . ',';

698

}

699

substr($query,-1)=")"; # Remove last ',';

700

push(@queries,$query);

701

foreach $index (@$index)

702

{

703

$index =~ s/primary key/unique index primary_key/i;

704

if ($index =~ /^unique.*$([^\(]*)$$/i)

705

{

706

# original: $indfield="using btree (" .$1.")";

707

# using btree doesn�t seem to work with Postgres anymore; it creates

708

# the table and adds the index, but it isn�t unique

709

$indfield=" (" .$1.")";

710

$in="unique index";

711

$table="index_$nr"; $nr++;

712

}

713

elsif ($index =~ /^(.*index)\s+(\w*)\s+($.*$)$/i)

714

{

715

# original: $indfield="using btree (" .$1.")";

716

$indfield=" " .$3;

717

$in="index";

718

$table="index_$nr"; $nr++;

719

}

720

else

721

{

722

die "Can't parse index information in '$index'\n";

723

}

724

push(@queries,"create $in ${table_name}_$table on $table_name $indfield");

725

}

726

$queries[0]=$query;

727

return @queries;

728

}

729

730

sub insert_file {

731

my ($self,$dbname, $file, $dbh) = @_;

732

my ($command, $sth);

733

734

# Syntax:

735

# copy [binary] <class_name> [with oids]

736

# {to|from} {<filename>|stdin|stdout} [using delimiters <delim>]

737

print "The ascii files aren't correct for postgres ....!!!\n";

738

$command = "copy $dbname from '$file' using delimiters ','";

739

print "$command\n";

740

$sth = $dbh->do($command) or die $DBI::errstr;

741

return $sth;

742

}

743

744

745

# As postgreSQL wants A % B instead of standard mod(A,B) we have to map

746

# This will not handle all cases, but as the benchmarks doesn't use functions

747

# inside MOD() the following should work

748

749

# PostgreSQL cant handle count(*) or even count(1), but it can handle

750

# count(1+1) sometimes. ==> this is solved in PostgreSQL 6.3

751

752

# PostgreSQL 6.5 is supporting MOD.

753

754

sub query {

755

my($self,$sql) = @_;

756

my(@select,$change);

757

# if you use PostgreSQL 6.x and x is lower as 5 then uncomment the line below.

758

# $sql =~ s/mod$([^,]*),([^$]*)\)/$$1 % $2$/gi;

759

760

# if you use PostgreSQL 6.1.x uncomment the lines below

761

# if ($sql =~ /select\s+count$\*$\s+from/i) {

762

# }

763

# elsif ($sql =~ /count$\*$/i)

764

# {

765

# if ($sql =~ /select\s+(.*)\s+from/i)

766

# {

767

# @select = split(/,/,$1);

768

# if ($select[0] =~ /(.*)\s+as\s+\w+$/i)

769

# {

770

# $change = $1;

771

# }

772

# else

773

# {

774

# $change = $select[0];

775

# }

776

# }

777

# if (($change =~ /count/i) || ($change eq "")) {

778

# $change = "1+1";

779

# }

780

# $sql =~ s/count$\*$/count($change)/gi;

781

# }

782

# till here.

783

return $sql;

784

}

785

786

sub drop_index

787

{

788

my ($self,$table,$index) = @_;

789

return "DROP INDEX $index";

790

}

791

792

sub abort_if_fatal_error

793

{

794

return 1 if ($DBI::errstr =~ /sent to backend, but backend closed/i);

795

return 0;

796

}

797

798

sub small_rollback_segment

799

{

800

return 0;

801

}

802

803

sub reconnect_on_errors

804

{

805

return 0;

806

}

807

808

sub fix_for_insert

809

{

810

my ($self,$cmd) = @_;

811

return $cmd;

812

}

813

814

sub vacuum

815

{

816

my ($self,$full_vacuum,$dbh_ref,@tables)=@_;

817

my ($loop_time,$end_time,$dbh,$table);

818

if (defined($full_vacuum))

819

{

820

$$dbh_ref->disconnect; $$dbh_ref= $self->connect();

821

}

822

$dbh=$$dbh_ref;

823

$loop_time=new Benchmark;

824

if ($#tables >= 0)

825

{

826

foreach $table (@tables)

827

{

828

$dbh->do("vacuum analyze $table") || die "Got error: $DBI::errstr when executing 'vacuum analyze $table'\n";

829

$dbh->do("vacuum $table") || die "Got error: $DBI::errstr when executing 'vacuum'\n";

830

}

831

}

832

else

833

{

834

# $dbh->do("vacuum pg_attributes") || die "Got error: $DBI::errstr when executing 'vacuum'\n";

835

# $dbh->do("vacuum pg_index") || die "Got error: $DBI::errstr when executing 'vacuum'\n";

836

$dbh->do("vacuum analyze") || die "Got error: $DBI::errstr when executing 'vacuum analyze'\n";

837

$dbh->do("vacuum") || die "Got error: $DBI::errstr when executing 'vacuum'\n";

838

}

839

$end_time=new Benchmark;

840

print "Time for book-keeping (1): " .

841

Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";

842

$dbh->disconnect; $$dbh_ref= $self->connect();

843

}

844

845

846

#############################################################################

847

# Definitions for Solid

848

#############################################################################

849

850

package db_Solid;

851

852

sub new

853

{

854

my ($type,$host,$database)= @_;

855

my $self= {};

856

my %limits;

857

bless $self;

858

859

$self->{'cmp_name'} = "solid";

860

$self->{'data_source'} = "DBI:Solid:";

861

$self->{'limits'} = \%limits;

862

$self->{'blob'} = "long varchar";

863

$self->{'text'} = "long varchar";

864

$self->{'double_quotes'} = 1;

865

$self->{'drop_attr'} = "";

866

$self->{'transactions'} = 1; # Transactions enabled

867

868

$limits{'max_conditions'} = 9999; # Probably big enough

869

$limits{'max_columns'} = 2000; # From crash-me

870

$limits{'max_tables'} = 65000; # Should be big enough

871

$limits{'max_text_size'} = 65492; # According to tests

872

$limits{'query_size'} = 65535; # Probably a limit

873

$limits{'max_index'} = 64; # Probably big enough

874

$limits{'max_index_parts'} = 64;

875

$limits{'max_column_name'} = 80;

876

877

$limits{'join_optimizer'} = 1;

878

$limits{'load_data_infile'} = 0;

879

$limits{'lock_tables'} = 0;

880

$limits{'functions'} = 1;

881

$limits{'group_functions'} = 1;

882

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

883

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

884

$limits{'select_without_from'}= 0; # Can do 'select 1' ?;

885

$limits{'multi_drop'} = 0;

886

$limits{'subqueries'} = 1;

887

$limits{'left_outer_join'} = 1;

888

$limits{'table_wildcard'} = 1;

889

$limits{'having_with_alias'} = 0;

890

$limits{'having_with_group'} = 1;

891

$limits{'like_with_column'} = 1;

892

$limits{'order_by_position'} = 0; # 2.30.0018 can this

893

$limits{'group_by_position'} = 0;

894

$limits{'alter_table'} = 1;

895

$limits{'alter_add_multi_col'}= 0;

896

$limits{'alter_table_dropcol'}= 0;

897

898

$limits{'group_func_extra_std'} = 0; # Have group function std().

899

900

$limits{'func_odbc_mod'} = 1;

901

$limits{'func_extra_%'} = 0;

902

$limits{'func_odbc_floor'} = 1;

903

$limits{'column_alias'} = 1;

904

$limits{'NEG'} = 1;

905

$limits{'func_extra_in_num'} = 1;

906

$limits{'unique_index'} = 1; # Unique index works or not

907

$limits{'insert_select'} = 1;

908

$limits{'working_blobs'} = 1; # If big varchar/blobs works

909

$limits{'order_by_unused'} = 1;

910

$limits{'working_all_fields'} = 1;

911

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

912

913

return $self;

914

}

915

916

917

# Get the version number of the database

918

919

920

sub version

921

{

922

my ($version,$dir);

923

$version="Solid version ??";

924

foreach $dir ($ENV{'SOLIDDIR'},"/usr/local/solid", "/my/local/solid")

925

{

926

if ($dir && -e "$dir/bin/solcon")

927

{

928

$version=`$dir/bin/solcon -e"ver" $main::opt_user $main::opt_password | grep Server | sed q`;

929

if ($? == 0)

930

{

931

chomp($version);

932

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

933

return $version;

934

}

935

}

936

}

937

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

938

return $version;

939

}

940

941

sub connect

942

{

943

my ($self)=@_;

944

my ($dbh);

945

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

946

$main::opt_password,{ PrintError => 0}) ||

947

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

948

return $dbh;

949

}

950

951

952

# Returns a list of statements to create a table

953

# The field types are in ANSI SQL format.

954

955

956

sub create

957

{

958

my($self,$table_name,$fields,$index) = @_;

959

my($query,@queries,$nr);

960

961

$query="create table $table_name (";

962

foreach $field (@$fields)

963

{

964

$field =~ s/mediumint/integer/i;

965

$field =~ s/ double/ float/i;

966

# Solid doesn't have blob, it has long varchar

967

$field =~ s/ blob/ long varchar/;

968

# $field =~ s/ decimal/ float/i;

969

# $field =~ s/ big_decimal/ float/i;

970

# $field =~ s/ date/ int/i;

971

$query.= $field . ',';

972

}

973

substr($query,-1)=")"; # Remove last ',';

974

push(@queries,$query);

975

$nr=0;

976

foreach $index (@$index)

977

{

978

if ($index =~ /^primary key/i || $index =~ /^unique/i)

979

{ # Add to create statement

980

substr($queries[0],-1,0)="," . $index;

981

}

982

else

983

{

984

$index =~ /^(.*)\s+($.*$)$/;

985

push(@queries,"create ${1}$nr on $table_name $2");

986

$nr++;

987

}

988

}

989

return @queries;

990

}

991

992

# there is no sql statement in solid which can do the load from

993

# an ascii file in the db ... but there is the speedloader program

994

# an external program which can load the ascii file in the db ...

995

# the server must be down before using speedloader !!!!

996

# (in the standalone version)

997

# it works also with a control file ... that one must be made ....

998

sub insert_file {

999

my ($self, $dbname, $file) = @_;

1000

my ($speedcmd);

1001

$speedcmd = '/usr/local/solid/bin/solload';

1002

print "At this moment not supported - solid server must go down \n";

1003

return 0;

1004

}

1005

1006

# solid can't handle an alias in a having statement so

1007

# select test as foo from tmp group by foo having foor > 2

1008

# becomes

1009

# select test as foo from tmp group by foo having test > 2

1010

1011

sub query {

1012

my($self,$sql) = @_;

1013

my(@select,$tmp,$newhaving,$key,%change);

1014

1015

if ($sql =~ /having\s+/i)

1016

{

1017

if ($sql =~ /select (.*) from/i)

1018

{

1019

(@select) = split(/,\s*/, $1);

1020

foreach $tmp (@select)

1021

{

1022

if ($tmp =~ /(.*)\s+as\s+(\w+)/)

1023

{

1024

$change{$2} = $1;

1025

}

1026

}

1027

}

1028

if ($sql =~ /having\s+(\w+)/i)

1029

{

1030

$newhaving = $1;

1031

foreach $key (sort {$a cmp $b} keys %change)

1032

{

1033

if ($newhaving eq $key)

1034

{

1035

$newhaving =~ s/$key/$change{$key}/g;

1036

}

1037

}

1038

}

1039

$sql =~ s/(having)\s+(\w+)/$1 $newhaving/i;

1040

}

1041

return $sql;

1042

}

1043

1044

1045

sub drop_index

1046

{

1047

my ($self,$table,$index) = @_;

1048

return "DROP INDEX $index";

1049

}

1050

1051

sub abort_if_fatal_error

1052

{

1053

return 0;

1054

}

1055

1056

sub small_rollback_segment

1057

{

1058

return 0;

1059

}

1060

1061

sub fix_for_insert

1062

{

1063

my ($self,$cmd) = @_;

1064

return $cmd;

1065

}

1066

1067

sub reconnect_on_errors

1068

{

1069

return 0;

1070

}

1071

1072

#############################################################################

1073

# Definitions for Empress

1074

1075

# at this moment DBI:Empress can only handle 200 prepare statements ...

1076

# so Empress can't be tested with the benchmark test :(

1077

#############################################################################

1078

1079

package db_Empress;

1080

1081

sub new

1082

{

1083

my ($type,$host,$database)= @_;

1084

my $self= {};

1085

my %limits;

1086

bless $self;

1087

1088

$self->{'cmp_name'} = "empress";

1089

$self->{'data_source'} = "DBI:EmpressNet:SERVER=$host;Database=/usr/local/empress/rdbms/bin/$database";

1090

$self->{'limits'} = \%limits;

1091

$self->{'blob'} = "text";

1092

$self->{'text'} = "text";

1093

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

1094

$self->{'drop_attr'} = "";

1095

$self->{'transactions'} = 1; # Transactions enabled

1096

1097

$limits{'max_conditions'} = 1258;

1098

$limits{'max_columns'} = 226; # server is disconnecting????

1099

# above this value .... but can handle 2419 columns

1100

# maybe something for crash-me ... but how to check ???

1101

$limits{'max_tables'} = 65000; # Should be big enough

1102

$limits{'max_text_size'} = 4095; # max returned ....

1103

$limits{'query_size'} = 65535; # Not a limit, big enough

1104

$limits{'max_index'} = 64; # Big enough

1105

$limits{'max_index_parts'} = 64; # Big enough

1106

$limits{'max_column_name'} = 31;

1107

1108

$limits{'join_optimizer'} = 1;

1109

$limits{'load_data_infile'} = 0;

1110

$limits{'lock_tables'} = 1;

1111

$limits{'functions'} = 1;

1112

$limits{'group_functions'} = 1;

1113

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

1114

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

1115

$limits{'select_without_from'}= 0;

1116

$limits{'multi_drop'} = 0;

1117

$limits{'subqueries'} = 1;

1118

$limits{'table_wildcard'} = 0;

1119

$limits{'having_with_alias'} = 0; # AS isn't supported in a select

1120

$limits{'having_with_group'} = 1;

1121

$limits{'like_with_column'} = 1;

1122

$limits{'order_by_position'} = 1;

1123

$limits{'group_by_position'} = 0;

1124

$limits{'alter_table'} = 1;

1125

$limits{'alter_add_multi_col'}= 0;

1126

$limits{'alter_table_dropcol'}= 0;

1127

1128

$limits{'group_func_extra_std'}= 0; # Have group function std().

1129

1130

$limits{'func_odbc_mod'} = 0;

1131

$limits{'func_extra_%'} = 1;

1132

$limits{'func_odbc_floor'} = 1;

1133

$limits{'func_extra_if'} = 0;

1134

$limits{'column_alias'} = 0;

1135

$limits{'NEG'} = 1;

1136

$limits{'func_extra_in_num'} = 0;

1137

$limits{'unique_index'} = 1; # Unique index works or not

1138

$limits{'insert_select'} = 1;

1139

$limits{'working_blobs'} = 1; # If big varchar/blobs works

1140

$limits{'order_by_unused'} = 1;

1141

$limits{'working_all_fields'} = 1;

1142

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

1143

1144

return $self;

1145

}

1146

1147

1148

# Get the version number of the database

1149

1150

1151

sub version

1152

{

1153

my ($self,$dbh)=@_;

1154

my ($version);

1155

$version="";

1156

if (-x "/usr/local/empress/rdbms/bin/empvers")

1157

{

1158

$version=`/usr/local/empress/rdbms/bin/empvers | grep Version`;

1159

}

1160

if ($version)

1161

{

1162

chomp($version);

1163

}

1164

else

1165

{

1166

$version="Empress version ???";

1167

}

1168

1169

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

1170

return $version;

1171

}

1172

1173

sub connect

1174

{

1175

my ($self)=@_;

1176

my ($dbh);

1177

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

1178

$main::opt_password,{ PrintError => 0}) ||

1179

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

1180

return $dbh;

1181

}

1182

1183

sub insert_file {

1184

my($self,$dbname, $file) = @_;

1185

my($command,$sth);

1186

$command = "insert into $dbname from '$file'";

1187

print "$command\n" if ($opt_debug);

1188

$sth = $dbh->do($command) or die $DBI::errstr;

1189

1190

return $sth;

1191

}

1192

1193

1194

# Returns a list of statements to create a table

1195

# The field types are in ANSI SQL format.

1196

1197

1198

sub create

1199

{

1200

my($self,$table_name,$fields,$index) = @_;

1201

my($query,@queries,$nr);

1202

1203

$query="create table $table_name (";

1204

foreach $field (@$fields)

1205

{

1206

$field =~ s/mediumint/int/i;

1207

$field =~ s/tinyint/int/i;

1208

$field =~ s/smallint/int/i;

1209

$field =~ s/longint/int/i;

1210

$field =~ s/integer/int/i;

1211

$field =~ s/ double/ longfloat/i;

1212

# Solid doesn't have blob, it has long varchar

1213

# $field =~ s/ blob/ text(65535,65535,65535,65535)/;

1214

$field =~ s/ blob/ text/;

1215

$field =~ s/ varchar$(\d+)$/ char($1,3)/;

1216

$field =~ s/ char$(\d+)$/ char($1,3)/;

1217

# $field =~ s/ decimal/ float/i;

1218

# $field =~ s/ big_decimal/ longfloat/i;

1219

# $field =~ s/ date/ int/i;

1220

$field =~ s/ float(.*)/ float/i;

1221

if ($field =~ / int$(\d+)$/) {

1222

if ($1 > 4) {

1223

$field =~ s/ int$\d+$/ longinteger/i;

1224

} else {

1225

$field =~ s/ int$\d+$/ longinteger/i;

1226

}

1227

} else {

1228

$field =~ s/ int/ longinteger/i;

1229

}

1230

$query.= $field . ',';

1231

}

1232

substr($query,-1)=")"; # Remove last ',';

1233

push(@queries,$query);

1234

$nr=1;

1235

foreach $index (@$index)

1236

{

1237

# Primary key is unique index in Empress

1238

$index =~ s/primary key/unique index/i;

1239

if ($index =~ /^unique.*$([^\(]*)$$/i)

1240

{

1241

$nr++;

1242

push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");

1243

}

1244

else

1245

{

1246

if (!($index =~ /^(.*index)\s+(\w*)\s+($.*$)$/i))

1247

{

1248

die "Can't parse index information in '$index'\n";

1249

}

1250

push(@queries,"create $1 ${table_name}_$2 on $table_name $3");

1251

}

1252

}

1253

return @queries;

1254

}

1255

1256

# empress can't handle an alias and but can handle the number of the

1257

# columname - so

1258

# select test as foo from tmp order by foo

1259

# becomes

1260

# select test from tmp order by 1

1261

1262

sub query {

1263

my($self,$sql) = @_;

1264

my(@select,$i,$tmp,$newselect,$neworder,@order,$key,%change);

1265

my($tmp1,$otmp,$tmp2);

1266

1267

if ($sql =~ /\s+as\s+/i)

1268

{

1269

if ($sql =~ /select\s+(.*)\s+from/i) {

1270

$newselect = $1;

1271

(@select) = split(/,\s*/, $1);

1272

$i = 1;

1273

foreach $tmp (@select) {

1274

if ($tmp =~ /\s+as\s+(\w+)/) {

1275

$change{$1} = $i;

1276

}

1277

$i++;

1278

}

1279

}

1280

$newselect =~ s/\s+as\s+(\w+)//gi;

1281

$tmp2 = 0;

1282

if ($sql =~ /order\s+by\s+(.*)$/i) {

1283

(@order) = split(/,\s*/, $1);

1284

foreach $otmp (@order) {

1285

foreach $key (sort {$a cmp $b} keys %change) {

1286

if ($otmp eq $key) {

1287

$neworder .= "$tmp1"."$change{$key}";

1288

$tmp1 = ", ";

1289

$tmp2 = 1;

1290

} elsif ($otmp =~ /(\w+)\s+(.+)$/) {

1291

if ($key eq $1) {

1292

$neworder .= "$tmp1"."$change{$key} $2";

1293

$tmp2 = 1;

1294

}

1295

}

1296

}

1297

if ($tmp2 == 0) {

1298

$neworder .= "$tmp1"."$otmp";

1299

}

1300

$tmp2 = 0;

1301

$tmp1 = ", ";

1302

}

1303

}

1304

$sql =~ s/(select)\s+(.*)\s+(from)/$1 $newselect $3/i;

1305

$sql =~ s/(order\s+by)\s+(.*)$/$1 $neworder/i;

1306

}

1307

return $sql;

1308

}

1309

1310

sub fix_for_insert

1311

{

1312

my ($self,$cmd) = @_;

1313

$cmd =~ s/\'\'/\' \'/g;

1314

return $cmd;

1315

}

1316

1317

1318

sub drop_index

1319

{

1320

my ($self,$table,$index) = @_;

1321

return "DROP INDEX $index";

1322

}

1323

1324

# This is a because of the 200 statement problem with DBI-Empress

1325

1326

sub abort_if_fatal_error

1327

{

1328

if ($DBI::errstr =~ /Overflow of table of prepared statements/i)

1329

{

1330

print "Overflow of prepared statements ... killing the process\n";

1331

exit 1;

1332

}

1333

return 0;

1334

}

1335

1336

sub small_rollback_segment

1337

{

1338

return 0;

1339

}

1340

1341

sub reconnect_on_errors

1342

{

1343

return 0;

1344

}

1345

1346

#############################################################################

1347

# Definitions for Oracle

1348

#############################################################################

1349

1350

package db_Oracle;

1351

1352

sub new

1353

{

1354

my ($type,$host,$database)= @_;

1355

my $self= {};

1356

my %limits;

1357

bless $self;

1358

1359

$self->{'cmp_name'} = "Oracle";

1360

$self->{'data_source'} = "DBI:Oracle:$database";

1361

$self->{'limits'} = \%limits;

1362

$self->{'blob'} = "long";

1363

$self->{'text'} = "long";

1364

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

1365

$self->{'drop_attr'} = "";

1366

$self->{'transactions'} = 1; # Transactions enabled

1367

$self->{"vacuum"} = 1;

1368

1369

$limits{'max_conditions'} = 9999; # (Actually not a limit)

1370

$limits{'max_columns'} = 254; # Max number of columns in table

1371

$limits{'max_tables'} = 65000; # Should be big enough

1372

$limits{'max_text_size'} = 2000; # Limit for blob test-connect

1373

$limits{'query_size'} = 65525; # Max size with default buffers.

1374

$limits{'max_index'} = 16; # Max number of keys

1375

$limits{'max_index_parts'} = 16; # Max segments/key

1376

$limits{'max_column_name'} = 32; # max table and column name

1377

1378

$limits{'truncate_table'} = 1;

1379

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

1380

$limits{'load_data_infile'} = 0; # Has load data infile

1381

$limits{'lock_tables'} = 0; # Has lock tables

1382

$limits{'functions'} = 1; # Has simple functions (+/-)

1383

$limits{'group_functions'} = 1; # Have group functions

1384

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

1385

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

1386

$limits{'select_without_from'}= 0;

1387

$limits{'multi_drop'} = 0;

1388

$limits{'subqueries'} = 1;

1389

$limits{'left_outer_join'} = 0; # This may be fixed in the query module

1390

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

1391

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

1392

$limits{'having_with_group'} = 1; # Can't use group functions in HAVING

1393

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

1394

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

1395

$limits{'group_by_position'} = 0;

1396

$limits{'alter_table'} = 1;

1397

$limits{'alter_add_multi_col'}= 0;

1398

$limits{'alter_table_dropcol'}= 0;

1399

1400

$limits{'group_func_extra_std'} = 0; # Have group function std().

1401

1402

$limits{'func_odbc_mod'} = 0; # Oracle has problem with mod()

1403

$limits{'func_extra_%'} = 0; # Has % as alias for mod()

1404

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

1405

$limits{'func_extra_if'} = 0; # Have function if.

1406

$limits{'column_alias'} = 1; # Alias for fields in select statement.

1407

$limits{'NEG'} = 1; # Supports -id

1408

$limits{'func_extra_in_num'} = 1; # Has function in

1409

$limits{'unique_index'} = 1; # Unique index works or not

1410

$limits{'insert_select'} = 1;

1411

$limits{'working_blobs'} = 1; # If big varchar/blobs works

1412

$limits{'order_by_unused'} = 1;

1413

$limits{'working_all_fields'} = 1;

1414

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

1415

1416

1417

return $self;

1418

}

1419

1420

1421

# Get the version number of the database

1422

1423

1424

sub version

1425

{

1426

my ($self)=@_;

1427

my ($dbh,$sth,$version,@row);

1428

1429

$dbh=$self->connect();

1430

$sth = $dbh->prepare("select VERSION from product_component_version WHERE PRODUCT like 'Oracle%'") or die $DBI::errstr;

1431

$version="Oracle 7.x";

1432

if ($sth->execute && (@row = $sth->fetchrow_array))

1433

{

1434

$version="Oracle $row[0]";

1435

}

1436

$sth->finish;

1437

$dbh->disconnect;

1438

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

1439

return $version;

1440

}

1441

1442

sub connect

1443

{

1444

my ($self)=@_;

1445

my ($dbh);

1446

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

1447

$main::opt_password,{ PrintError => 0}) ||

1448

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

1449

return $dbh;

1450

}

1451

1452

1453

# Returns a list of statements to create a table

1454

# The field types are in ANSI SQL format.

1455

1456

# If one uses $main::opt_fast then one is allowed to use

1457

# non standard types to get better speed.

1458

1459

1460

sub create

1461

{

1462

my($self,$table_name,$fields,$index) = @_;

1463

my($query,@queries,$ind,@keys);

1464

1465

$query="create table $table_name (";

1466

foreach $field (@$fields)

1467

{

1468

$field =~ s/ character$(\d+)$/ char$$1$/i;

1469

$field =~ s/ character varying$(\d+)$/ varchar$$1$/i;

1470

$field =~ s/ char varying$(\d+)$/ varchar$$1$/i;

1471

$field =~ s/ integer/ number$38$/i;

1472

$field =~ s/ int/ number$38$/i;

1473

$field =~ s/ tinyint/ number$38$/i;

1474

$field =~ s/ smallint/ number$38$/i;

1475

$field =~ s/ mediumint/ number$38$/i;

1476

$field =~ s/ tinynumber$(\d+)$$(\d+)$/ number$$1,$2$/i;

1477

$field =~ s/ smallnumber$(\d+)$$(\d+)$/ number$$1,$2$/i;

1478

$field =~ s/ mediumnumber$(\d+)$$(\d+)$/ number$$1,$2$/i;

1479

$field =~ s/ number$(\d+)$$(\d+)$/ number$$1,$2$/i;

1480

$field =~ s/ numeric$(\d+)$$(\d+)$/ number$$1,$2$/i;

1481

$field =~ s/ decimal$(\d+)$$(\d+)$/ number$$1,$2$/i;

1482

$field =~ s/ dec$(\d+)$$(\d+)$/ number$$1,$2$/i;

1483

$field =~ s/ float/ number/;

1484

$field =~ s/ real/ number/;

1485

$field =~ s/ double precision/ number/;

1486

$field =~ s/ double/ number/;

1487

$field =~ s/ blob/ long/;

1488

$query.= $field . ',';

1489

}

1490

1491

foreach $ind (@$index)

1492

{

1493

my @index;

1494

if ( $ind =~ /\bKEY\b/i ){

1495

push(@keys,"ALTER TABLE $table_name ADD $ind");

1496

}else{

1497

my @fields = split(' ',$index);

1498

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

1499

push(@index,$query);

1500

}

1501

}

1502

substr($query,-1)=")"; # Remove last ',';

1503

push(@queries,$query,@keys,@index);

1504

#print "query:$query\n";

1505

1506

return @queries;

1507

}

1508

1509

sub insert_file {

1510

my($self,$dbname, $file) = @_;

1511

print "insert an ascii file isn't supported by Oracle (?)\n";

1512

return 0;

1513

}

1514

1515

1516

# Do any conversions to the ANSI SQL query so that the database can handle it

1517

1518

1519

sub query {

1520

my($self,$sql) = @_;

1521

return $sql;

1522

}

1523

1524

sub fix_for_insert

1525

{

1526

my ($self,$cmd) = @_;

1527

$cmd =~ s/\'\'/\' \'/g;

1528

return $cmd;

1529

}

1530

1531

1532

sub drop_index

1533

{

1534

my ($self,$table,$index) = @_;

1535

return "DROP INDEX $index";

1536

}

1537

1538

1539

# Abort if the server has crashed

1540

# return: 0 if ok

1541

# 1 question should be retried

1542

1543

1544

sub abort_if_fatal_error

1545

{

1546

return 0;

1547

}

1548

1549

sub small_rollback_segment

1550

{

1551

return 1;

1552

}

1553

1554

sub reconnect_on_errors

1555

{

1556

return 0;

1557

}

1558

1559

1560

# optimize the tables ....

1561

1562

sub vacuum

1563

{

1564

my ($self,$full_vacuum,$dbh_ref)=@_;

1565

my ($loop_time,$end_time,$sth,$dbh);

1566

1567

if (defined($full_vacuum))

1568

{

1569

$$dbh_ref->disconnect; $$dbh_ref= $self->connect();

1570

}

1571

$dbh=$$dbh_ref;

1572

$loop_time=new Benchmark;

1573

# first analyze all tables

1574

$sth = $dbh->prepare("select table_name from user_tables") || die "Got error: $DBI::errstr";

1575

$sth->execute || die "Got error: $DBI::errstr when select user_tables";

1576

while (my @r = $sth->fetchrow_array)

1577

{

1578

$dbh->do("analyze table $r[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze table'\n";

1579

}

1580

# now analyze all indexes ...

1581

$sth = $dbh->prepare("select index_name from user_indexes") || die "Got error: $DBI::errstr";

1582

$sth->execute || die "Got error: $DBI::errstr when select user_indexes";

1583

while (my @r1 = $sth->fetchrow_array)

1584

{

1585

$dbh->do("analyze index $r1[0] compute statistics") || die "Got error: $DBI::errstr when executing 'analyze index $r1[0]'\n";

1586

}

1587

$end_time=new Benchmark;

1588

print "Time for book-keeping (1): " .

1589

Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";

1590

$dbh->disconnect; $$dbh_ref= $self->connect();

1591

}

1592

1593

1594

#############################################################################

1595

# Definitions for Informix

1596

#############################################################################

1597

1598

package db_Informix;

1599

1600

sub new

1601

{

1602

my ($type,$host,$database)= @_;

1603

my $self= {};

1604

my %limits;

1605

bless $self;

1606

1607

$self->{'cmp_name'} = "Informix";

1608

$self->{'data_source'} = "DBI:Informix:$database";

1609

$self->{'limits'} = \%limits;

1610

$self->{'blob'} = "byte in table";

1611

$self->{'text'} = "byte in table";

1612

$self->{'double_quotes'} = 0; # Can handle: 'Walker''s'

1613

$self->{'drop_attr'} = "";

1614

$self->{'transactions'} = 1; # Transactions enabled

1615

$self->{'host'} = $host;

1616

1617

$limits{'NEG'} = 1; # Supports -id

1618

$limits{'alter_table'} = 1;

1619

$limits{'alter_add_multi_col'}= 0;

1620

$limits{'alter_table_dropcol'}= 1;

1621

$limits{'column_alias'} = 1; # Alias for fields in select statement.

1622

$limits{'func_extra_%'} = 0; # Has % as alias for mod()

1623

$limits{'func_extra_if'} = 0; # Have function if.

1624

$limits{'func_extra_in_num'}= 0; # Has function in

1625

$limits{'func_odbc_floor'} = 0; # Has func_odbc_floor function

1626

$limits{'func_odbc_mod'} = 1; # Have function mod.

1627

$limits{'functions'} = 1; # Has simple functions (+/-)

1628

$limits{'group_by_position'} = 1; # Can use 'GROUP BY 1'

1629

$limits{'group_by_alias'} = 0; # Can use 'select a as ab from x GROUP BY ab'

1630

$limits{'group_func_extra_std'} = 0; # Have group function std().

1631

$limits{'group_functions'} = 1; # Have group functions

1632

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

1633

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

1634

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

1635

$limits{'having_with_group'}= 1; # Can't use group functions in HAVING

1636

$limits{'join_optimizer'} = 1; # Can optimize FROM tables (always 1 only for msql)

1637

$limits{'left_outer_join'} = 0; # Supports left outer joins (ANSI)

1638

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

1639

$limits{'load_data_infile'} = 0; # Has load data infile

1640

$limits{'lock_tables'} = 1; # Has lock tables

1641

$limits{'max_conditions'} = 1214; # (Actually not a limit)

1642

$limits{'max_column_name'} = 18; # max table and column name

1643

$limits{'max_columns'} = 994; # Max number of columns in table

1644

$limits{'max_tables'} = 65000; # Should be big enough

1645

$limits{'max_index'} = 64; # Max number of keys

1646

$limits{'max_index_parts'} = 15; # Max segments/key

1647

$limits{'max_text_size'} = 65535; # Max size with default buffers. ??

1648

$limits{'multi_drop'} = 0; # Drop table can take many tables

1649

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

1650

$limits{'query_size'} = 32766; # Max size with default buffers.

1651

$limits{'select_without_from'}= 0; # Can do 'select 1';

1652

$limits{'subqueries'} = 1; # Doesn't support sub-queries.

1653

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

1654

$limits{'unique_index'} = 1; # Unique index works or not

1655

$limits{'insert_select'} = 1;

1656

$limits{'working_blobs'} = 1; # If big varchar/blobs works

1657

$limits{'order_by_unused'} = 1;

1658

$limits{'working_all_fields'} = 1;

1659

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

1660

1661

return $self;

1662

}

1663

1664

1665

# Get the version number of the database

1666

1667

1668

sub version

1669

{

1670

my ($self)=@_;

1671

my ($dbh,$sth,$version,@row);

1672

1673

$ENV{'INFORMIXSERVER'} = $self->{'host'};

1674

$dbh=$self->connect();

1675

$sth = $dbh->prepare("SELECT owner FROM systables WHERE tabname = ' VERSION'")

1676

or die $DBI::errstr;

1677

$version='Informix unknown';

1678

if ($sth->execute && (@row = $sth->fetchrow_array))

1679

{

1680

$version="Informix $row[0]";

1681

}

1682

$sth->finish;

1683

$dbh->disconnect;

1684

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

1685

return $version;

1686

}

1687

1688

sub connect

1689

{

1690

my ($self)=@_;

1691

my ($dbh);

1692

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

1693

$main::opt_password,{ PrintError => 0}) ||

1694

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

1695

return $dbh;

1696

}

1697

1698

1699

1700

# Create table

1701

1702

1703

sub create

1704

{

1705

my($self,$table_name,$fields,$index) = @_;

1706

my($query,@queries,$name,$nr);

1707

1708

$query="create table $table_name (";

1709

foreach $field (@$fields)

1710

{

1711

# $field =~ s/\btransport_description\b/transport_desc/;

1712

# to overcome limit 18 chars

1713

$field =~ s/tinyint/smallint/i;

1714

$field =~ s/tinyint$\d+$/smallint/i;

1715

$field =~ s/mediumint/integer/i;

1716

$field =~ s/mediumint$\d+$/integer/i;

1717

$field =~ s/smallint$\d+$/smallint/i;

1718

$field =~ s/integer$\d+$/integer/i;

1719

$field =~ s/int$\d+$/integer/i;

1720

# $field =~ s/\b(?:small)?int(?:eger)?$(\d+)$/decimal($1)/i;

1721

# $field =~ s/float($\d*,\d*$){0,1}/real/i;

1722

$field =~ s/(float|double)($.*?$)?/float/i;

1723

1724

if ($field =~ / blob/i)

1725

{

1726

$name=$self->{'blob'};

1727

$field =~ s/ blob/ $name/;

1728

}

1729

$query.= $field . ',';

1730

}

1731

substr($query,-1)=")"; # Remove last ',';

1732

push(@queries,$query);

1733

$nr=0;

1734

1735

foreach $index (@$index)

1736

{

1737

# Primary key is unique index in Informix

1738

$index =~ s/primary key/unique index primary/i;

1739

if ($index =~ /^unique\s*$([^\(]*)$$/i)

1740

{

1741

$nr++;

1742

push(@queries,"create unique index ${table_name}_$nr on $table_name ($1)");

1743

}

1744

else

1745

{

1746

if (!($index =~ /^(.*index)\s+(\w*)\s+($.*$)$/i))

1747

{

1748

die "Can't parse index information in '$index'\n";

1749

}

1750

### push(@queries,"create $1 ${table_name}_$2 on $table_name $3");

1751

$nr++;

1752

push(@queries,"create $1 ${table_name}_$nr on $table_name $3");

1753

}

1754

}

1755

return @queries;

1756

}

1757

1758

# Some test needed this

1759

1760

1761

sub query {

1762

my($self,$sql) = @_;

1763

return $sql;

1764

}

1765

1766

1767

sub fix_for_insert

1768

{

1769

my ($self,$cmd) = @_;

1770

$cmd =~ s/\\\'//g;

1771

return $cmd;

1772

}

1773

1774

1775

1776

sub drop_index

1777

{

1778

my ($self,$table,$index) = @_;

1779

return "DROP INDEX $index";

1780

}

1781

1782

1783

# Abort if the server has crashed

1784

# return: 0 if ok

1785

# 1 question should be retried

1786

1787

1788

sub abort_if_fatal_error

1789

{

1790

return 0;

1791

}

1792

1793

sub small_rollback_segment

1794

{

1795

return 0;

1796

}

1797

1798

sub reconnect_on_errors

1799

{

1800

return 0;

1801

}

1802

1803

1804

#############################################################################

1805

# Configuration for Access

1806

#############################################################################

1807

1808

package db_access;

1809

1810

sub new

1811

{

1812

my ($type,$host,$database)= @_;

1813

my $self= {};

1814

my %limits;

1815

bless $self;

1816

1817

$self->{'cmp_name'} = "access";

1818

$self->{'data_source'} = "DBI:ODBC:$database";

1819

if (defined($host) && $host ne "")

1820

{

1821

$self->{'data_source'} .= ":$host";

1822

}

1823

$self->{'limits'} = \%limits;

1824

$self->{'blob'} = "blob";

1825

$self->{'text'} = "blob"; # text ?

1826

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

1827

$self->{'drop_attr'} = "";

1828

$self->{'transactions'} = 1; # Transactions enabled

1829

1830

$limits{'max_conditions'} = 97; # We get 'Query is too complex'

1831

$limits{'max_columns'} = 255; # Max number of columns in table

1832

$limits{'max_tables'} = 65000; # Should be big enough

1833

$limits{'max_text_size'} = 255; # Max size with default buffers.

1834

$limits{'query_size'} = 65535; # Not a limit, big enough

1835

$limits{'max_index'} = 32; # Max number of keys

1836

$limits{'max_index_parts'} = 10; # Max segments/key

1837

$limits{'max_column_name'} = 64; # max table and column name

1838

1839

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

1840

$limits{'load_data_infile'} = 0; # Has load data infile

1841

$limits{'lock_tables'} = 0; # Has lock tables

1842

$limits{'functions'} = 1; # Has simple functions (+/-)

1843

$limits{'group_functions'} = 1; # Have group functions

1844

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

1845

$limits{'group_distinct_functions'}= 0; # Have count(distinct)

1846

$limits{'select_without_from'}= 1; # Can do 'select 1';

1847

$limits{'multi_drop'} = 0; # Drop table can take many tables

1848

$limits{'subqueries'} = 1; # Supports sub-queries.

1849

$limits{'left_outer_join'} = 1; # Supports left outer joins

1850

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

1851

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

1852

$limits{'having_with_group'} = 1; # Can use group functions in HAVING

1853

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

1854

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

1855

$limits{'group_by_position'} = 0; # Can use 'GROUP BY 1'

1856

$limits{'alter_table'} = 1;

1857

$limits{'alter_add_multi_col'}= 2; #Have ALTER TABLE t add a int, b int;

1858

$limits{'alter_table_dropcol'}= 1;

1859

1860

$limits{'group_func_extra_std'} = 0; # Have group function std().

1861

1862

$limits{'func_odbc_mod'} = 0; # Have function mod.

1863

$limits{'func_extra_%'} = 0; # Has % as alias for mod()

1864

$limits{'func_odbc_floor'} = 0; # Has func_odbc_floor function

1865

$limits{'func_extra_if'} = 0; # Have function if.

1866

$limits{'column_alias'} = 1; # Alias for fields in select statement.

1867

$limits{'NEG'} = 1; # Supports -id

1868

$limits{'func_extra_in_num'} = 1; # Has function in

1869

$limits{'unique_index'} = 1; # Unique index works or not

1870

$limits{'insert_select'} = 1;

1871

$limits{'working_blobs'} = 1; # If big varchar/blobs works

1872

$limits{'order_by_unused'} = 1;

1873

$limits{'working_all_fields'} = 1;

1874

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

1875

return $self;

1876

}

1877

1878

1879

# Get the version number of the database

1880

1881

1882

sub version

1883

{

1884

my ($self)=@_;

1885

my $version="Access 2000";

1886

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

1887

return $version; #DBI/ODBC can't return the server version

1888

}

1889

1890

sub connect

1891

{

1892

my ($self)=@_;

1893

my ($dbh);

1894

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

1895

$main::opt_password,{ PrintError => 0}) ||

1896

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

1897

return $dbh;

1898

}

1899

1900

1901

# Returns a list of statements to create a table

1902

# The field types are in ANSI SQL format.

1903

1904

1905

sub create

1906

{

1907

my($self,$table_name,$fields,$index) = @_;

1908

my($query,@queries,$nr);

1909

1910

$query="create table $table_name (";

1911

foreach $field (@$fields)

1912

{

1913

$field =~ s/mediumint/integer/i;

1914

$field =~ s/tinyint/smallint/i;

1915

$field =~ s/float$\d+,\d+$/float/i;

1916

$field =~ s/integer$\d+$/integer/i;

1917

$field =~ s/smallint$\d+$/smallint/i;

1918

$field =~ s/int$\d+$/integer/i;

1919

$field =~ s/blob/text/i;

1920

$query.= $field . ',';

1921

}

1922

substr($query,-1)=")"; # Remove last ',';

1923

push(@queries,$query);

1924

$nr=0;

1925

foreach $index (@$index)

1926

{

1927

$ext="WITH DISALLOW NULL";

1928

if (($index =~ s/primary key/unique index primary_key/i))

1929

{

1930

$ext="WITH PRIMARY;"

1931

}

1932

if ($index =~ /^unique.*$([^\(]*)$$/i)

1933

{

1934

$nr++;

1935

$index="unique index ${table_name}_$nr ($1)";

1936

}

1937

$index =~ /^(.*)\s+($.*$)$/;

1938

push(@queries,"create ${1} on $table_name $2");

1939

}

1940

return @queries;

1941

}

1942

1943

1944

# Do any conversions to the ANSI SQL query so that the database can handle it

1945

1946

1947

sub query {

1948

my($self,$sql) = @_;

1949

return $sql;

1950

}

1951

1952

sub drop_index

1953

{

1954

my ($self,$table,$index) = @_;

1955

return "DROP INDEX $index ON $table";

1956

}

1957

1958

1959

# Abort if the server has crashed

1960

# return: 0 if ok

1961

# 1 question should be retried

1962

1963

1964

sub abort_if_fatal_error

1965

{

1966

return 1 if (($DBI::errstr =~ /The database engine couldn\'t lock table/i) ||

1967

($DBI::errstr =~ /niet vergrendelen. De tabel is momenteel in gebruik /i) ||

1968

($DBI::errstr =~ /Den anv.* redan av en annan/i) ||

1969

($DBI::errstr =~ /non-exclusive access/));

1970

return 0;

1971

}

1972

1973

sub small_rollback_segment

1974

{

1975

return 0;

1976

}

1977

1978

sub reconnect_on_errors

1979

{

1980

return 1;

1981

}

1982

1983

sub fix_for_insert

1984

{

1985

my ($self,$cmd) = @_;

1986

return $cmd;

1987

}

1988

1989

#############################################################################

1990

# Configuration for Microsoft SQL server

1991

#############################################################################

1992

1993

package db_ms_sql;

1994

1995

sub new

1996

{

1997

my ($type,$host,$database)= @_;

1998

my $self= {};

1999

my %limits;

2000

bless $self;

2001

2002

$self->{'cmp_name'} = "ms-sql";

2003

$self->{'data_source'} = "DBI:ODBC:$database";

2004

if (defined($host) && $host ne "")

2005

{

2006

$self->{'data_source'} .= ":$host";

2007

}

2008

$self->{'limits'} = \%limits;

2009

$self->{'blob'} = "text";

2010

$self->{'text'} = "text";

2011

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

2012

$self->{'drop_attr'} = "";

2013

$self->{'transactions'} = 1; # Transactions enabled

2014

2015

$limits{'max_conditions'} = 1030; # We get 'Query is too complex'

2016

$limits{'max_columns'} = 250; # Max number of columns in table

2017

$limits{'max_tables'} = 65000; # Should be big enough

2018

$limits{'max_text_size'} = 9830; # Max size with default buffers.

2019

$limits{'query_size'} = 9830; # Max size with default buffers.

2020

$limits{'max_index'} = 64; # Max number of keys

2021

$limits{'max_index_parts'} = 15; # Max segments/key

2022

$limits{'max_column_name'} = 30; # max table and column name

2023

2024

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

2025

$limits{'load_data_infile'} = 0; # Has load data infile

2026

$limits{'lock_tables'} = 0; # Has lock tables

2027

$limits{'functions'} = 1; # Has simple functions (+/-)

2028

$limits{'group_functions'} = 1; # Have group functions

2029

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

2030

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

2031

$limits{'select_without_from'}= 1; # Can do 'select 1';

2032

$limits{'multi_drop'} = 1; # Drop table can take many tables

2033

$limits{'subqueries'} = 1; # Supports sub-queries.

2034

$limits{'left_outer_join'} = 1; # Supports left outer joins

2035

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

2036

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

2037

$limits{'having_with_group'} = 1; # Can't use group functions in HAVING

2038

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

2039

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

2040

$limits{'group_by_position'} = 0; # Can use 'GROUP BY 1'

2041

$limits{'alter_table'} = 1;

2042

$limits{'alter_add_multi_col'}= 0;

2043

$limits{'alter_table_dropcol'}= 0;

2044

2045

$limits{'group_func_extra_std'} = 0; # Have group function std().

2046

2047

$limits{'func_odbc_mod'} = 0; # Have function mod.

2048

$limits{'func_extra_%'} = 1; # Has % as alias for mod()

2049

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

2050

$limits{'func_extra_if'} = 0; # Have function if.

2051

$limits{'column_alias'} = 1; # Alias for fields in select statement.

2052

$limits{'NEG'} = 1; # Supports -id

2053

$limits{'func_extra_in_num'} = 0; # Has function in

2054

$limits{'unique_index'} = 1; # Unique index works or not

2055

$limits{'insert_select'} = 1;

2056

$limits{'working_blobs'} = 1; # If big varchar/blobs works

2057

$limits{'order_by_unused'} = 1;

2058

$limits{'working_all_fields'} = 1;

2059

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

2060

return $self;

2061

}

2062

2063

2064

# Get the version number of the database

2065

2066

2067

sub version

2068

{

2069

my ($self)=@_;

2070

my($sth,@row, $version);

2071

$version='MS SQL server ?';

2072

$dbh=$self->connect();

2073

$sth = $dbh->prepare("SELECT \@\@VERSION") or die $DBI::errstr;

2074

$sth->execute or die $DBI::errstr;

2075

@row = $sth->fetchrow_array;

2076

if ($row[0]) {

2077

@server = split(/\n/,$row[0]);

2078

chomp(@server);

2079

$version= "$server[0]";

2080

}

2081

$sth->finish;

2082

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

2083

return $version;

2084

}

2085

2086

sub connect

2087

{

2088

my ($self)=@_;

2089

my ($dbh);

2090

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

2091

$main::opt_password,{ PrintError => 0}) ||

2092

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

2093

return $dbh;

2094

}

2095

2096

2097

# Returns a list of statements to create a table

2098

# The field types are in ANSI SQL format.

2099

2100

2101

sub create

2102

{

2103

my($self,$table_name,$fields,$index) = @_;

2104

my($query,@queries,$nr);

2105

2106

$query="create table $table_name (";

2107

foreach $field (@$fields)

2108

{

2109

$field =~ s/mediumint/integer/i;

2110

$field =~ s/float$\d+,\d+$/float/i;

2111

$field =~ s/double$\d+,\d+$/float/i;

2112

$field =~ s/double/float/i;

2113

$field =~ s/integer$\d+$/integer/i;

2114

$field =~ s/int$\d+$/integer/i;

2115

$field =~ s/smallint$\d+$/smallint/i;

2116

$field =~ s/smallinteger/smallint/i;

2117

$field =~ s/tinyint$\d+$/tinyint/i;

2118

$field =~ s/tinyinteger/tinyint/i;

2119

$field =~ s/blob/text/i;

2120

$query.= $field . ',';

2121

}

2122

substr($query,-1)=")"; # Remove last ',';

2123

push(@queries,$query);

2124

$nr=0;

2125

foreach $index (@$index)

2126

{

2127

$ext="WITH DISALLOW NULL";

2128

if (($index =~ s/primary key/unique index primary_key/i))

2129

{

2130

$ext="WITH PRIMARY;"

2131

}

2132

if ($index =~ /^unique.*$([^\(]*)$$/i)

2133

{

2134

$nr++;

2135

$index="unique index ${table_name}_$nr ($1)";

2136

}

2137

$index =~ /^(.*)\s+($.*$)$/;

2138

push(@queries,"create ${1} on $table_name $2");

2139

}

2140

return @queries;

2141

}

2142

2143

2144

# Do any conversions to the ANSI SQL query so that the database can handle it

2145

2146

2147

sub query {

2148

my($self,$sql) = @_;

2149

return $sql;

2150

}

2151

2152

sub drop_index

2153

{

2154

my ($self,$table,$index) = @_;

2155

return "DROP INDEX $table.$index";

2156

}

2157

2158

2159

# Abort if the server has crashed

2160

# return: 0 if ok

2161

# 1 question should be retried

2162

2163

2164

sub abort_if_fatal_error

2165

{

2166

return 0;

2167

}

2168

2169

sub small_rollback_segment

2170

{

2171

return 0;

2172

}

2173

2174

sub reconnect_on_errors

2175

{

2176

return 0;

2177

}

2178

2179

sub fix_for_insert

2180

{

2181

my ($self,$cmd) = @_;

2182

return $cmd;

2183

}

2184

2185

#############################################################################

2186

# Configuration for Sybase

2187

#############################################################################

2188

package db_sybase;

2189

2190

sub new

2191

{

2192

my ($type,$host,$database)= @_;

2193

my $self= {};

2194

my %limits;

2195

bless $self;

2196

2197

$self->{'cmp_name'} = "sybase";

2198

$self->{'data_source'} = "DBI:Sybase:database=$database";

2199

if (defined($host) && $host ne "")

2200

{

2201

$self->{'data_source'} .= ";hostname=$host";

2202

}

2203

$self->{'limits'} = \%limits;

2204

$self->{'blob'} = "text";

2205

$self->{'text'} = "text";

2206

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

2207

$self->{'drop_attr'} = "";

2208

$self->{'transactions'} = 1; # Transactions enabled

2209

$self->{"vacuum"} = 1;

2210

2211

$limits{'max_conditions'} = 1030; # We get 'Query is too complex'

2212

$limits{'max_columns'} = 250; # Max number of columns in table

2213

$limits{'max_tables'} = 65000; # Should be big enough

2214

$limits{'max_text_size'} = 9830; # Max size with default buffers.

2215

$limits{'query_size'} = 9830; # Max size with default buffers.

2216

$limits{'max_index'} = 64; # Max number of keys

2217

$limits{'max_index_parts'} = 15; # Max segments/key

2218

$limits{'max_column_name'} = 30; # max table and column name

2219

2220

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

2221

$limits{'load_data_infile'} = 0; # Has load data infile

2222

$limits{'lock_tables'} = 0; # Has lock tables

2223

$limits{'functions'} = 1; # Has simple functions (+/-)

2224

$limits{'group_functions'} = 1; # Have group functions

2225

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

2226

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

2227

$limits{'select_without_from'}= 1; # Can do 'select 1';

2228

$limits{'multi_drop'} = 1; # Drop table can take many tables

2229

$limits{'subqueries'} = 1; # Supports sub-queries.

2230

$limits{'left_outer_join'} = 1; # Supports left outer joins

2231

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

2232

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

2233

$limits{'having_with_group'} = 1; # Can't use group functions in HAVING

2234

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

2235

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

2236

$limits{'group_by_position'} = 0; # Can use 'GROUP BY 1'

2237

$limits{'alter_table'} = 1;

2238

$limits{'alter_add_multi_col'}= 0;

2239

$limits{'alter_table_dropcol'}= 0;

2240

2241

$limits{'group_func_extra_std'} = 0; # Have group function std().

2242

2243

$limits{'func_odbc_mod'} = 0; # Have function mod.

2244

$limits{'func_extra_%'} = 1; # Has % as alias for mod()

2245

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

2246

$limits{'func_extra_if'} = 0; # Have function if.

2247

$limits{'column_alias'} = 1; # Alias for fields in select statement.

2248

$limits{'NEG'} = 1; # Supports -id

2249

$limits{'func_extra_in_num'} = 0; # Has function in

2250

$limits{'unique_index'} = 1; # Unique index works or not

2251

$limits{'insert_select'} = 1;

2252

$limits{'working_blobs'} = 1; # If big varchar/blobs works

2253

$limits{'order_by_unused'} = 1;

2254

$limits{'working_all_fields'} = 1;

2255

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

2256

return $self;

2257

}

2258

2259

2260

# Get the version number of the database

2261

2262

2263

sub version

2264

{

2265

my ($self)=@_;

2266

my ($dbh,$sth,$version,@row);

2267

2268

$dbh=$self->connect();

2269

$sth = $dbh->prepare('SELECT @@version') or die $DBI::errstr;

2270

$version="Sybase (unknown)";

2271

if ($sth->execute && (@row = $sth->fetchrow_array))

2272

{

2273

$version=$row[0];

2274

}

2275

$sth->finish;

2276

$dbh->disconnect;

2277

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

2278

return $version;

2279

}

2280

2281

sub connect

2282

{

2283

my ($self)=@_;

2284

my ($dbh);

2285

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

2286

$main::opt_password,{ PrintError => 0 , AutoCommit => 1}) ||

2287

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

2288

return $dbh;

2289

}

2290

2291

2292

# Returns a list of statements to create a table

2293

# The field types are in ANSI SQL format.

2294

2295

2296

sub create

2297

{

2298

my($self,$table_name,$fields,$index) = @_;

2299

my($query,@queries,$nr);

2300

2301

$query="create table $table_name (";

2302

foreach $field (@$fields)

2303

{

2304

$field =~ s/mediumint/integer/i;

2305

$field =~ s/float$\d+,\d+$/float/i;

2306

$field =~ s/int$\d+$/int/i;

2307

$field =~ s/double/float/i;

2308

$field =~ s/integer$\d+$/integer/i;

2309

$field =~ s/smallint$\d+$/smallint/i;

2310

$field =~ s/tinyint$\d+$/tinyint/i;

2311

$field =~ s/blob/text/i;

2312

$query.= $field . ',';

2313

}

2314

substr($query,-1)=")"; # Remove last ',';

2315

push(@queries,$query);

2316

$nr=0;

2317

foreach $index (@$index)

2318

{

2319

# $ext="WITH DISALLOW NULL";

2320

if (($index =~ s/primary key/unique index primary_key/i))

2321

{

2322

# $ext="WITH PRIMARY;"

2323

}

2324

if ($index =~ /^unique.*$([^\(]*)$$/i)

2325

{

2326

$nr++;

2327

$index="unique index ${table_name}_$nr ($1)";

2328

}

2329

$index =~ /^(.*)\s+($.*$)$/;

2330

push(@queries,"create ${1} on $table_name $2");

2331

}

2332

return @queries;

2333

}

2334

2335

2336

# Do any conversions to the ANSI SQL query so that the database can handle it

2337

2338

2339

sub query {

2340

my($self,$sql) = @_;

2341

return $sql;

2342

}

2343

2344

sub drop_index

2345

{

2346

my ($self,$table,$index) = @_;

2347

return "DROP INDEX $table.$index";

2348

}

2349

2350

2351

# Abort if the server has crashed

2352

# return: 0 if ok

2353

# 1 question should be retried

2354

2355

2356

sub abort_if_fatal_error

2357

{

2358

return 0;

2359

}

2360

2361

sub small_rollback_segment

2362

{

2363

return 0;

2364

}

2365

2366

sub reconnect_on_errors

2367

{

2368

return 0;

2369

}

2370

2371

sub fix_for_insert

2372

{

2373

my ($self,$cmd) = @_;

2374

return $cmd;

2375

}

2376

2377

2378

# optimize the tables ....

2379

# WARNING (from walrus)! This sub will work only from DBD:sybase

2380

# driver. Because if we use ODBC we don't know actual database name

2381

# (but DSN name only)

2382

sub vacuum

2383

{

2384

my ($self,$full_vacuum,$dbh_ref)=@_;

2385

my ($loop_time,$end_time,$dbh);

2386

2387

if (defined($full_vacuum))

2388

{

2389

$$dbh_ref->disconnect; $$dbh_ref= $self->connect();

2390

}

2391

$dbh=$$dbh_ref;

2392

$loop_time=new Benchmark;

2393

my (@tables,$sth,$current_table,$current_base);

2394

$dbh->do("dump tran $database with truncate_only");

2395

$sth=$dbh->prepare("sp_tables" ) or die "prepere";

2396

$sth->execute() or die "execute";

2397

while (@row = $sth->fetchrow_array()) {

2398

$current_table = $row[2];

2399

$current_base = $row[0];

2400

next if ($current_table =~ /^sys/);

2401

push(@tables,$current_table) if ($database == $current_base);

2402

}

2403

2404

$sth->finish();

2405

2406

foreach $table (@tables) {

2407

# print "$table: \n";

2408

$dbh->do("update statistics $table") or print "Oops!";

2409

}

2410

2411

# $dbh->do("analyze table ?? compute statistics") || die "Got error: $DBI::errstr when executing 'vacuum'\n";

2412

$end_time=new Benchmark;

2413

print "Time for book-keeping (1): " .

2414

Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n";

2415

$dbh->disconnect; $$dbh_ref= $self->connect();

2416

}

2417

2418

2419

2420

2421

#############################################################################

2422

# Definitions for Adabas

2423

#############################################################################

2424

2425

package db_Adabas;

2426

2427

sub new

2428

{

2429

my ($type,$host,$database)= @_;

2430

my $self= {};

2431

my %limits;

2432

bless $self;

2433

2434

$self->{'cmp_name'} = "Adabas";

2435

$self->{'data_source'} = "DBI:Adabas:$database";

2436

$self->{'limits'} = \%limits;

2437

$self->{'blob'} = "long";

2438

$self->{'text'} = "long";

2439

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

2440

$self->{'drop_attr'} = "";

2441

$self->{'transactions'} = 1; # Transactions enabled

2442

2443

$limits{'max_conditions'} = 50; # (Actually not a limit)

2444

$limits{'max_columns'} = 254; # Max number of columns in table

2445

$limits{'max_tables'} = 65000; # Should be big enough

2446

$limits{'max_text_size'} = 2000; # Limit for blob test-connect

2447

$limits{'query_size'} = 65525; # Max size with default buffers.

2448

$limits{'max_index'} = 16; # Max number of keys

2449

$limits{'max_index_parts'} = 16; # Max segments/key

2450

$limits{'max_column_name'} = 32; # max table and column name

2451

2452

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

2453

$limits{'load_data_infile'} = 0; # Has load data infile

2454

$limits{'lock_tables'} = 0; # Has lock tables

2455

$limits{'functions'} = 1; # Has simple functions (+/-)

2456

$limits{'group_functions'} = 1; # Have group functions

2457

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

2458

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

2459

$limits{'select_without_from'}= 0;

2460

$limits{'multi_drop'} = 0;

2461

$limits{'subqueries'} = 1;

2462

$limits{'left_outer_join'} = 0; # This may be fixed in the query module

2463

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

2464

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

2465

$limits{'having_with_group'} = 1; # Can't use group functions in HAVING

2466

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

2467

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

2468

$limits{'group_by_position'} = 1;

2469

$limits{'alter_table'} = 1;

2470

$limits{'alter_add_multi_col'}= 2; #Have ALTER TABLE t add a int, b int;

2471

$limits{'alter_table_dropcol'}= 1;

2472

2473

$limits{'group_func_extra_std'} = 0; # Have group function std().

2474

2475

$limits{'func_odbc_mod'} = 0; # Oracle has problem with mod()

2476

$limits{'func_extra_%'} = 0; # Has % as alias for mod()

2477

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

2478

$limits{'func_extra_if'} = 0; # Have function if.

2479

$limits{'column_alias'} = 1; # Alias for fields in select statement.

2480

$limits{'NEG'} = 1; # Supports -id

2481

$limits{'func_extra_in_num'} = 1; # Has function in

2482

$limits{'unique_index'} = 1; # Unique index works or not

2483

$limits{'insert_select'} = 1;

2484

$limits{'working_blobs'} = 1; # If big varchar/blobs works

2485

$limits{'order_by_unused'} = 1;

2486

$limits{'working_all_fields'} = 1;

2487

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

2488

2489

2490

return $self;

2491

}

2492

2493

2494

# Get the version number of the database

2495

2496

2497

sub version

2498

{

2499

my ($self)=@_;

2500

my ($dbh,$sth,$version,@row);

2501

2502

$dbh=$self->connect();

2503

$sth = $dbh->prepare("SELECT KERNEL FROM VERSIONS") or die $DBI::errstr;

2504

$version="Adabas (unknown)";

2505

if ($sth->execute && (@row = $sth->fetchrow_array)

2506

&& $row[0] =~ /([\d\.]+)/)

2507

{

2508

$version="Adabas $1";

2509

}

2510

$sth->finish;

2511

$dbh->disconnect;

2512

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

2513

return $version;

2514

}

2515

2516

sub connect

2517

{

2518

my ($self)=@_;

2519

my ($dbh);

2520

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

2521

$main::opt_password,{ PrintError => 0}) ||

2522

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

2523

return $dbh;

2524

}

2525

2526

2527

# Returns a list of statements to create a table

2528

# The field types are in ANSI SQL format.

2529

2530

# If one uses $main::opt_fast then one is allowed to use

2531

# non standard types to get better speed.

2532

2533

2534

sub create

2535

{

2536

my($self,$table_name,$fields,$index) = @_;

2537

my($query,@queries,$ind,@keys);

2538

2539

$query="create table $table_name (";

2540

foreach $field (@$fields)

2541

{

2542

$field =~ s/CHARACTER\s+VARYING/VARCHAR/i;

2543

$field =~ s/TINYINT/SMALLINT/i;

2544

$field =~ s/MEDIUMINT/INT/i;

2545

$field =~ s/SMALLINT\s*$\d+$/SMALLINT/i;

2546

$field =~ s/INT\s*$\d+$/INT/i;

2547

$field =~ s/BLOB/LONG/i;

2548

$field =~ s/INTEGER\s*$\d+$/INTEGER/i;

2549

$field =~ s/FLOAT\s*$(\d+),\d+$/FLOAT$$1$/i;

2550

$field =~ s/DOUBLE/FLOAT$38$/i;

2551

$field =~ s/DOUBLE\s+PRECISION/FLOAT$38$/i;

2552

$query.= $field . ',';

2553

}

2554

2555

foreach $ind (@$index)

2556

{

2557

my @index;

2558

if ( $ind =~ /\bKEY\b/i ){

2559

push(@keys,"ALTER TABLE $table_name ADD $ind");

2560

}else{

2561

my @fields = split(' ',$index);

2562

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

2563

push(@index,$query);

2564

}

2565

}

2566

substr($query,-1)=")"; # Remove last ',';

2567

push(@queries,$query,@keys,@index);

2568

#print "query:$query\n";

2569

2570

return @queries;

2571

}

2572

2573

sub insert_file {

2574

my($self,$dbname, $file) = @_;

2575

print "insert an ascii file isn't supported by Oracle (?)\n";

2576

return 0;

2577

}

2578

2579

2580

# Do any conversions to the ANSI SQL query so that the database can handle it

2581

2582

2583

sub query {

2584

my($self,$sql) = @_;

2585

return $sql;

2586

}

2587

2588

sub drop_index

2589

{

2590

my ($self,$table,$index) = @_;

2591

return "DROP INDEX $index";

2592

}

2593

2594

2595

# Abort if the server has crashed

2596

# return: 0 if ok

2597

# 1 question should be retried

2598

2599

2600

sub abort_if_fatal_error

2601

{

2602

return 0;

2603

}

2604

2605

sub small_rollback_segment

2606

{

2607

return 0;

2608

}

2609

2610

sub reconnect_on_errors

2611

{

2612

return 0;

2613

}

2614

2615

sub fix_for_insert

2616

{

2617

my ($self,$cmd) = @_;

2618

return $cmd;

2619

}

2620

2621

#############################################################################

2622

# Configuration for IBM DB2

2623

#############################################################################

2624

2625

package db_db2;

2626

2627

sub new

2628

{

2629

my ($type,$host,$database)= @_;

2630

my $self= {};

2631

my %limits;

2632

bless $self;

2633

2634

$self->{'cmp_name'} = "DB2";

2635

$self->{'data_source'} = "DBI:ODBC:$database";

2636

if (defined($host) && $host ne "")

2637

{

2638

$self->{'data_source'} .= ":$host";

2639

}

2640

$self->{'limits'} = \%limits;

2641

$self->{'blob'} = "varchar(255)";

2642

$self->{'text'} = "varchar(255)";

2643

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

2644

$self->{'drop_attr'} = "";

2645

$self->{'transactions'} = 1; # Transactions enabled

2646

2647

$limits{'max_conditions'} = 418; # We get 'Query is too complex'

2648

$limits{'max_columns'} = 500; # Max number of columns in table

2649

$limits{'max_tables'} = 65000; # Should be big enough

2650

$limits{'max_text_size'} = 254; # Max size with default buffers.

2651

$limits{'query_size'} = 254; # Max size with default buffers.

2652

$limits{'max_index'} = 48; # Max number of keys

2653

$limits{'max_index_parts'} = 15; # Max segments/key

2654

$limits{'max_column_name'} = 18; # max table and column name

2655

2656

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

2657

$limits{'load_data_infile'} = 0; # Has load data infile

2658

$limits{'lock_tables'} = 0; # Has lock tables

2659

$limits{'functions'} = 1; # Has simple functions (+/-)

2660

$limits{'group_functions'} = 1; # Have group functions

2661

$limits{'group_func_sql_min_str'}= 1;

2662

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

2663

$limits{'select_without_from'}= 0; # Can do 'select 1';

2664

$limits{'multi_drop'} = 0; # Drop table can take many tables

2665

$limits{'subqueries'} = 1; # Supports sub-queries.

2666

$limits{'left_outer_join'} = 1; # Supports left outer joins

2667

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

2668

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

2669

$limits{'having_with_group'} = 1; # Can't use group functions in HAVING

2670

$limits{'like_with_column'} = 0; # Can use column1 LIKE column2

2671

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

2672

$limits{'group_by_position'} = 0; # Can use 'GROUP BY 1'

2673

$limits{'alter_table'} = 1;

2674

$limits{'alter_add_multi_col'}= 0;

2675

$limits{'alter_table_dropcol'}= 0;

2676

2677

$limits{'group_func_extra_std'} = 0; # Have group function std().

2678

2679

$limits{'func_odbc_mod'} = 1; # Have function mod.

2680

$limits{'func_extra_%'} = 0; # Has % as alias for mod()

2681

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

2682

$limits{'func_extra_if'} = 0; # Have function if.

2683

$limits{'column_alias'} = 1; # Alias for fields in select statement.

2684

$limits{'NEG'} = 1; # Supports -id

2685

$limits{'func_extra_in_num'} = 0; # Has function in

2686

$limits{'unique_index'} = 1; # Unique index works or not

2687

$limits{'insert_select'} = 1;

2688

$limits{'working_blobs'} = 1; # If big varchar/blobs works

2689

$limits{'order_by_unused'} = 1;

2690

$limits{'working_all_fields'} = 1;

2691

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

2692

return $self;

2693

}

2694

2695

2696

# Get the version number of the database

2697

2698

2699

sub version

2700

{

2701

my ($self)=@_;

2702

return "IBM DB2 5"; #DBI/ODBC can't return the server version

2703

}

2704

2705

sub connect

2706

{

2707

my ($self)=@_;

2708

my ($dbh);

2709

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user, $main::opt_password) ||

2710

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

2711

return $dbh;

2712

}

2713

2714

2715

# Returns a list of statements to create a table

2716

# The field types are in ANSI SQL format.

2717

2718

2719

sub create

2720

{

2721

my($self,$table_name,$fields,$index) = @_;

2722

my($query,@queries,$nr);

2723

2724

$query="create table $table_name (";

2725

foreach $field (@$fields)

2726

{

2727

$field =~ s/mediumint/integer/i;

2728

$field =~ s/float$\d+,\d+$/float/i;

2729

$field =~ s/integer$\d+$/integer/i;

2730

$field =~ s/int$\d+$/integer/i;

2731

$field =~ s/tinyint$\d+$/smallint/i;

2732

$field =~ s/tinyint/smallint/i;

2733

$field =~ s/smallint$\d+$/smallint/i;

2734

$field =~ s/smallinteger/smallint/i;

2735

$field =~ s/blob/varchar(256)/i;

2736

$query.= $field . ',';

2737

}

2738

substr($query,-1)=")"; # Remove last ',';

2739

push(@queries,$query);

2740

$nr=0;

2741

foreach $index (@$index)

2742

{

2743

$ext="WITH DISALLOW NULL";

2744

if (($index =~ s/primary key/unique index primary_key/i))

2745

{

2746

$ext="WITH PRIMARY;"

2747

}

2748

if ($index =~ /^unique.*$([^\(]*)$$/i)

2749

{

2750

$nr++;

2751

$index="unique index ${table_name}_$nr ($1)";

2752

}

2753

$index =~ /^(.*)\s+($.*$)$/;

2754

push(@queries,"create ${1} on $table_name $2");

2755

}

2756

return @queries;

2757

}

2758

2759

2760

# Do any conversions to the ANSI SQL query so that the database can handle it

2761

2762

2763

sub query {

2764

my($self,$sql) = @_;

2765

return $sql;

2766

}

2767

2768

sub drop_index

2769

{

2770

my ($self,$table,$index) = @_;

2771

return "DROP INDEX $table.$index";

2772

}

2773

2774

2775

# Abort if the server has crashed

2776

# return: 0 if ok

2777

# 1 question should be retried

2778

2779

2780

sub abort_if_fatal_error

2781

{

2782

return 0;

2783

}

2784

2785

sub small_rollback_segment

2786

{

2787

return 1;

2788

}

2789

2790

sub reconnect_on_errors

2791

{

2792

return 0;

2793

}

2794

2795

sub fix_for_insert

2796

{

2797

my ($self,$cmd) = @_;

2798

return $cmd;

2799

}

2800

2801

#############################################################################

2802

# Configuration for MIMER

2803

#############################################################################

2804

2805

package db_Mimer;

2806

2807

sub new

2808

{

2809

my ($type,$host,$database)= @_;

2810

my $self= {};

2811

my %limits;

2812

bless $self;

2813

2814

$self->{'cmp_name'} = "mimer";

2815

$self->{'data_source'} = "DBI:mimer:$database:$host";

2816

$self->{'limits'} = \%limits;

2817

$self->{'blob'} = "binary varying(15000)";

2818

$self->{'text'} = "character varying(15000)";

2819

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

2820

$self->{'drop_attr'} = "";

2821

$self->{'transactions'} = 1; # Transactions enabled

2822

$self->{'char_null'} = "cast(NULL as char(1))";

2823

$self->{'numeric_null'} = "cast(NULL as int)";

2824

2825

$limits{'max_conditions'} = 9999; # (Actually not a limit)

2826

$limits{'max_columns'} = 252; # Max number of columns in table

2827

$limits{'max_tables'} = 65000; # Should be big enough

2828

$limits{'max_text_size'} = 15000; # Max size with default buffers.

2829

$limits{'query_size'} = 1000000; # Max size with default buffers.

2830

$limits{'max_index'} = 32; # Max number of keys

2831

$limits{'max_index_parts'} = 16; # Max segments/key

2832

$limits{'max_column_name'} = 128; # max table and column name

2833

2834

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

2835

$limits{'load_data_infile'} = 1; # Has load data infile

2836

$limits{'lock_tables'} = 0; # Has lock tables

2837

$limits{'functions'} = 1; # Has simple functions (+/-)

2838

$limits{'group_functions'} = 1; # Have group functions

2839

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

2840

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

2841

$limits{'select_without_from'}= 0; # Cannot do 'select 1';

2842

$limits{'multi_drop'} = 0; # Drop table cannot take many tables

2843

$limits{'subqueries'} = 1; # Supports sub-queries.

2844

$limits{'left_outer_join'} = 1; # Supports left outer joins

2845

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

2846

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

2847

$limits{'having_with_group'} = 1; # Can use group functions in HAVING

2848

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

2849

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

2850

$limits{'group_by_position'} = 0; # Cannot use 'GROUP BY 1'

2851

$limits{'alter_table'} = 1; # Have ALTER TABLE

2852

$limits{'alter_add_multi_col'}= 0; # Have ALTER TABLE t add a int,add b int;

2853

$limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column

2854

$limits{'insert_multi_value'} = 0; # Does not have INSERT ... values (1,2),(3,4)

2855

$limits{'multi_distinct'} = 0; # Does not allow select count(distinct a),count(distinct b)..

2856

2857

$limits{'group_func_extra_std'} = 0; # Does not have group function std().

2858

2859

$limits{'func_odbc_mod'} = 1; # Have function mod.

2860

$limits{'func_extra_%'} = 0; # Does not have % as alias for mod()

2861

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function

2862

$limits{'func_extra_if'} = 0; # Does not have function if.

2863

$limits{'column_alias'} = 1; # Alias for fields in select statement.

2864

$limits{'NEG'} = 1; # Supports -id

2865

$limits{'func_extra_in_num'} = 1; # Has function in

2866

$limits{'limit'} = 0; # Does not support the limit attribute

2867

$limits{'unique_index'} = 1; # Unique index works or not

2868

$limits{'insert_select'} = 1;

2869

$limits{'working_blobs'} = 1; # If big varchar/blobs works

2870

$limits{'order_by_unused'} = 0;

2871

$limits{'working_all_fields'} = 1;

2872

2873

return $self;

2874

}

2875

2876

2877

# Get the version number of the database

2878

2879

2880

sub version

2881

{

2882

my ($self)=@_;

2883

my ($dbh,$sth,$version,@row);

2884

2885

$dbh=$self->connect();

2886

2887

# Pick up SQLGetInfo option SQL_DBMS_VER (18)

2888

2889

$version = $dbh->func(18, GetInfo);

2890

$dbh->disconnect;

2891

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

2892

return $version;

2893

}

2894

2895

2896

# Connection with optional disabling of logging

2897

2898

2899

sub connect

2900

{

2901

my ($self)=@_;

2902

my ($dbh);

2903

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

2904

$main::opt_password,{ PrintError => 0}) ||

2905

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

2906

2907

$dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");

2908

return $dbh;

2909

}

2910

2911

2912

# Returns a list of statements to create a table

2913

# The field types are in ANSI SQL format.

2914

2915

# If one uses $main::opt_fast then one is allowed to use

2916

# non standard types to get better speed.

2917

2918

2919

sub create

2920

{

2921

my($self,$table_name,$fields,$index,$options) = @_;

2922

my($query,@queries,@indexes);

2923

2924

$query="create table $table_name (";

2925

foreach $field (@$fields)

2926

{

2927

# $field =~ s/ decimal/ double(10,2)/i;

2928

# $field =~ s/ big_decimal/ double(10,2)/i;

2929

$field =~ s/ double/ double precision/i;

2930

$field =~ s/ tinyint$.*$/ smallint/i;

2931

$field =~ s/ smallint$.*$/ smallint/i;

2932

$field =~ s/ mediumint/ integer/i;

2933

$field =~ s/ float$.*$/ float/i;

2934

# $field =~ s/ date/ int/i; # Because of tcp ?

2935

$query.= $field . ',';

2936

}

2937

foreach $index (@$index)

2938

{

2939

if ( $index =~ /\bINDEX\b/i )

2940

{

2941

my @fields = split(' ',$index);

2942

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

2943

push(@indexes,$query);

2944

2945

} else {

2946

$query.= $index . ',';

2947

}

2948

}

2949

substr($query,-1)=")"; # Remove last ',';

2950

$query.=" $options" if (defined($options));

2951

push(@queries,$query,@indexes);

2952

return @queries;

2953

}

2954

2955

sub insert_file {

2956

my($self,$dbname, $file) = @_;

2957

print "insert of an ascii file isn't supported by Mimer\n";

2958

return 0;

2959

}

2960

2961

2962

# Do any conversions to the ANSI SQL query so that the database can handle it

2963

2964

2965

sub query {

2966

my($self,$sql) = @_;

2967

return $sql;

2968

}

2969

2970

sub drop_index {

2971

my ($self,$table,$index) = @_;

2972

return "DROP INDEX $index";

2973

}

2974

2975

2976

# Abort if the server has crashed

2977

# return: 0 if ok

2978

# 1 question should be retried

2979

2980

2981

sub abort_if_fatal_error

2982

{

2983

return 1 if ($DBI::errstr =~ /Table locked by another cursor/);

2984

return 0;

2985

}

2986

2987

sub small_rollback_segment

2988

{

2989

return 0;

2990

}

2991

2992

sub reconnect_on_errors

2993

{

2994

return 0;

2995

}

2996

2997

sub fix_for_insert

2998

{

2999

my ($self,$cmd) = @_;

3000

return $cmd;

3001

}

3002

3003

#############################################################################

3004

# Configuration for InterBase

3005

#############################################################################

3006

3007

package db_interbase;

3008

3009

sub new

3010

{

3011

my ($type,$host,$database)= @_;

3012

my $self= {};

3013

my %limits;

3014

bless $self;

3015

3016

$self->{'cmp_name'} = "interbase";

3017

$self->{'data_source'} = "DBI:InterBase:database=$database;ib_dialect=3";

3018

$self->{'limits'} = \%limits;

3019

$self->{'blob'} = "blob";

3020

$self->{'text'} = "";

3021

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

3022

$self->{'drop_attr'} = "";

3023

$self->{'transactions'} = 1; # Transactions enabled

3024

$self->{'char_null'} = "";

3025

$self->{'numeric_null'} = "";

3026

3027

$limits{'max_conditions'} = 9999; # (Actually not a limit)

3028

$limits{'max_columns'} = 252; # Max number of columns in table

3029

$limits{'max_tables'} = 65000; # Should be big enough

3030

$limits{'max_text_size'} = 15000; # Max size with default buffers.

3031

$limits{'query_size'} = 1000000; # Max size with default buffers.

3032

$limits{'max_index'} = 65000; # Max number of keys

3033

$limits{'max_index_parts'} = 8; # Max segments/key

3034

$limits{'max_column_name'} = 128; # max table and column name

3035

3036

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

3037

$limits{'load_data_infile'} = 0; # Has load data infile

3038

$limits{'lock_tables'} = 0; # Has lock tables

3039

$limits{'functions'} = 1; # Has simple functions (+/-)

3040

$limits{'group_functions'} = 1; # Have group functions

3041

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings

3042

$limits{'group_distinct_functions'}= 1; # Have count(distinct)

3043

$limits{'select_without_from'}= 0; # Cannot do 'select 1';

3044

$limits{'multi_drop'} = 0; # Drop table cannot take many tables

3045

$limits{'subqueries'} = 1; # Supports sub-queries.

3046

$limits{'left_outer_join'} = 1; # Supports left outer joins

3047

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

3048

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

3049

$limits{'having_with_group'} = 1; # Can use group functions in HAVING

3050

$limits{'like_with_column'} = 0; # Can use column1 LIKE column2

3051

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

3052

$limits{'group_by_position'} = 0; # Cannot use 'GROUP BY 1'

3053

$limits{'alter_table'} = 1; # Have ALTER TABLE

3054

$limits{'alter_add_multi_col'}= 1; # Have ALTER TABLE t add a int,add b int;

3055

$limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column

3056

$limits{'insert_multi_value'} = 0; # Does not have INSERT ... values (1,2),(3,4)

3057

3058

$limits{'group_func_extra_std'} = 0; # Does not have group function std().

3059

3060

$limits{'func_odbc_mod'} = 0; # Have function mod.

3061

$limits{'func_extra_%'} = 0; # Does not have % as alias for mod()

3062

$limits{'func_odbc_floor'} = 0; # Has func_odbc_floor function

3063

$limits{'func_extra_if'} = 0; # Does not have function if.

3064

$limits{'column_alias'} = 1; # Alias for fields in select statement.

3065

$limits{'NEG'} = 0; # Supports -id

3066

$limits{'func_extra_in_num'} = 0; # Has function in

3067

$limits{'limit'} = 0; # Does not support the limit attribute

3068

$limits{'working_blobs'} = 1; # If big varchar/blobs works

3069

$limits{'order_by_unused'} = 1;

3070

$limits{'working_all_fields'} = 1;

3071

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

3072

3073

return $self;

3074

}

3075

3076

3077

# Get the version number of the database

3078

3079

3080

sub version

3081

{

3082

my ($self)=@_;

3083

my ($dbh,$version);

3084

3085

$version='Interbase ?';

3086

3087

$dbh=$self->connect();

3088

eval { $version = $dbh->func('version','ib_database_info')->{'version'}; };

3089

$dbh->disconnect;

3090

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

3091

return $version;

3092

}

3093

3094

3095

# Connection with optional disabling of logging

3096

3097

3098

sub connect

3099

{

3100

my ($self)=@_;

3101

my ($dbh);

3102

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

3103

$main::opt_password,{ PrintError => 0, AutoCommit => 1}) ||

3104

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

3105

3106

return $dbh;

3107

}

3108

3109

3110

# Returns a list of statements to create a table

3111

# The field types are in ANSI SQL format.

3112

3113

# If one uses $main::opt_fast then one is allowed to use

3114

# non standard types to get better speed.

3115

3116

3117

sub create

3118

{

3119

my($self,$table_name,$fields,$index,$options) = @_;

3120

my($query,@queries,@keys,@indexes);

3121

3122

$query="create table $table_name (";

3123

foreach $field (@$fields)

3124

{

3125

# $field =~ s/ big_decimal/ decimal/i;

3126

$field =~ s/ double/ double precision/i;

3127

$field =~ s/ tinyint/ smallint/i;

3128

$field =~ s/ mediumint/ integer/i;

3129

$field =~ s/\bint\b/integer/i;

3130

$field =~ s/ float$\d,\d$/ float/i;

3131

$field =~ s/ smallint$\d$/ smallint/i;

3132

$field =~ s/ integer$\d$/ integer/i;

3133

$query.= $field . ',';

3134

}

3135

foreach $ind (@$index)

3136

{

3137

if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){

3138

push(@keys,"ALTER TABLE $table_name ADD $ind");

3139

}else{

3140

my @fields = split(' ',$ind);

3141

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

3142

push(@indexes,$query);

3143

}

3144

}

3145

substr($query,-1)=")"; # Remove last ',';

3146

$query.=" $options" if (defined($options));

3147

push(@queries,$query,@keys,@indexes);

3148

return @queries;

3149

}

3150

3151

sub insert_file {

3152

my($self,$dbname, $file) = @_;

3153

print "insert of an ascii file isn't supported by InterBase\n";

3154

return 0;

3155

}

3156

3157

3158

# Do any conversions to the ANSI SQL query so that the database can handle it

3159

3160

3161

sub query {

3162

my($self,$sql) = @_;

3163

return $sql;

3164

}

3165

3166

sub drop_index {

3167

my ($self,$table,$index) = @_;

3168

return "DROP INDEX $index";

3169

}

3170

3171

3172

# Abort if the server has crashed

3173

# return: 0 if ok

3174

# 1 question should be retried

3175

3176

3177

sub abort_if_fatal_error

3178

{

3179

return 1 if ($DBI::errstr =~ /Table locked by another cursor/);

3180

return 0;

3181

}

3182

3183

sub small_rollback_segment

3184

{

3185

return 1;

3186

}

3187

3188

sub reconnect_on_errors

3189

{

3190

return 1;

3191

}

3192

3193

sub fix_for_insert

3194

{

3195

my ($self,$cmd) = @_;

3196

return $cmd;

3197

}

3198

3199

#############################################################################

3200

# Configuration for FrontBase

3201

#############################################################################

3202

3203

package db_FrontBase;

3204

3205

sub new

3206

{

3207

my ($type,$host,$database)= @_;

3208

my $self= {};

3209

my %limits;

3210

bless $self;

3211

3212

$self->{'cmp_name'} = "FrontBase";

3213

$self->{'data_source'} = "DBI:FB:dbname=$database;host=$host";

3214

$self->{'limits'} = \%limits;

3215

$self->{'blob'} = "varchar(8000000)";

3216

$self->{'text'} = "varchar(8000000)";

3217

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

3218

$self->{'drop_attr'} = ' restrict';

3219

$self->{'transactions'} = 1; # Transactions enabled

3220

$self->{'error_on_execute_means_zero_rows'}=1;

3221

3222

$limits{'max_conditions'} = 5427; # (Actually not a limit)

3223

# The following should be 8192, but is smaller because Frontbase crashes..

3224

$limits{'max_columns'} = 150; # Max number of columns in table

3225

$limits{'max_tables'} = 5000; # 10000 crashed FrontBase

3226

$limits{'max_text_size'} = 65000; # Max size with default buffers.

3227

$limits{'query_size'} = 8000000; # Max size with default buffers.

3228

$limits{'max_index'} = 38; # Max number of keys

3229

$limits{'max_index_parts'} = 20; # Max segments/key

3230

$limits{'max_column_name'} = 128; # max table and column name

3231

3232

$limits{'join_optimizer'} = 1; # Can optimize FROM tables

3233

$limits{'load_data_infile'} = 1; # Has load data infile

3234

$limits{'lock_tables'} = 0; # Has lock tables

3235

$limits{'functions'} = 1; # Has simple functions (+/-)

3236

$limits{'group_functions'} = 1; # Have group functions

3237

$limits{'group_distinct_functions'}= 0; # Have count(distinct)

3238

$limits{'select_without_from'}= 0;

3239

$limits{'multi_drop'} = 0; # Drop table cannot take many tables

3240

$limits{'subqueries'} = 1; # Supports sub-queries.

3241

$limits{'left_outer_join'} = 1; # Supports left outer joins

3242

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

3243

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING

3244

$limits{'having_with_group'} = 0; # Can use group functions in HAVING

3245

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2

3246

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'

3247

$limits{'group_by_position'} = 0; # Use of 'GROUP BY 1'

3248

$limits{'alter_table'} = 1; # Have ALTER TABLE

3249

$limits{'alter_add_multi_col'}= 0; # Have ALTER TABLE t add a int,add b int;

3250

$limits{'alter_table_dropcol'}= 0; # Have ALTER TABLE DROP column

3251

$limits{'insert_multi_value'} = 1;

3252

3253

$limits{'group_func_extra_std'} = 0; # Does not have group function std().

3254

3255

$limits{'func_odbc_mod'} = 0; # Have function mod.

3256

$limits{'func_extra_%'} = 0; # Does not have % as alias for mod()

3257

$limits{'func_odbc_floor'} = 0; # Has func_odbc_floor function

3258

$limits{'func_extra_if'} = 0; # Does not have function if.

3259

$limits{'column_alias'} = 1; # Alias for fields in select statement.

3260

$limits{'NEG'} = 1; # Supports -id

3261

$limits{'func_extra_in_num'} = 0; # Has function in

3262

$limits{'limit'} = 0; # Does not support the limit attribute

3263

$limits{'insert_select'} = 0;

3264

$limits{'order_by_unused'} = 0;

3265

3266

# We don't get an error for duplicate row in 'test-insert'

3267

$limits{'unique_index'} = 0; # Unique index works or not

3268

# We can't use a blob as a normal string (we got a wierd error)

3269

$limits{'working_blobs'} = 0;

3270

# 'select min(region),max(region) from bench1' kills the server after a while

3271

$limits{'group_func_sql_min_str'} = 0;

3272

# If you do select f1,f2,f3...f200 from table, Frontbase dies.

3273

$limits{'working_all_fields'} = 0;

3274

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

3275

3276

return $self;

3277

}

3278

3279

3280

# Get the version number of the database

3281

3282

3283

sub version

3284

{

3285

my ($self)=@_;

3286

my ($dbh,$sth,$version,@row);

3287

3288

# $dbh=$self->connect();

3289

3290

# Pick up SQLGetInfo option SQL_DBMS_VER (18)

3291

3292

#$version = $dbh->func(18, GetInfo);

3293

$version="FrontBase 3.3";

3294

# $dbh->disconnect;

3295

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

3296

return $version;

3297

}

3298

3299

3300

# Connection with optional disabling of logging

3301

3302

3303

sub connect

3304

{

3305

my ($self)=@_;

3306

my ($dbh);

3307

$dbh=DBI->connect($self->{'data_source'},

3308

$main::opt_user,

3309

$main::opt_password,

3310

{ PrintError => 0 ,

3311

'fb_host'=>$main::opt_host

3312

}) ||

3313

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

3314

$db->{AutoCommit}=1;

3315

# $dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");

3316

return $dbh;

3317

}

3318

3319

3320

# Returns a list of statements to create a table

3321

# The field types are in ANSI SQL format.

3322

3323

# If one uses $main::opt_fast then one is allowed to use

3324

# non standard types to get better speed.

3325

3326

3327

sub create

3328

{

3329

my($self,$table_name,$fields,$index,$options) = @_;

3330

my($query,@queries,@indexes,@keys);

3331

3332

$query="create table $table_name (";

3333

foreach $field (@$fields)

3334

{

3335

$field =~ s/ blob/ varchar(32000)/i;

3336

$field =~ s/ big_decimal/ float/i;

3337

$field =~ s/ double/ float/i;

3338

$field =~ s/ tinyint/ smallint/i;

3339

$field =~ s/ mediumint/ int/i;

3340

$field =~ s/ integer/ int/i;

3341

$field =~ s/ float$\d,\d$/ float/i;

3342

$field =~ s/ smallint$\d$/ smallint/i;

3343

$field =~ s/ int$\d$/ int/i;

3344

$query.= $field . ',';

3345

}

3346

foreach $ind (@$index)

3347

{

3348

# my @index;

3349

if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){

3350

push(@keys,"ALTER TABLE $table_name ADD $ind");

3351

}else{

3352

my @fields = split(' ',$ind);

3353

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

3354

push(@indexes,$query);

3355

}

3356

}

3357

substr($query,-1)=")"; # Remove last ',';

3358

$query.=" $options" if (defined($options));

3359

push(@queries,$query,@keys,@indexes);

3360

return @queries;

3361

}

3362

3363

sub insert_file {

3364

my($self,$dbname, $file) = @_;

3365

print "insert of an ascii file isn't supported by InterBase\n";

3366

return 0;

3367

}

3368

3369

3370

# Do any conversions to the ANSI SQL query so that the database can handle it

3371

3372

3373

sub query {

3374

my($self,$sql) = @_;

3375

return $sql;

3376

}

3377

3378

sub drop_index {

3379

my ($self,$table,$index) = @_;

3380

return "DROP INDEX $index";

3381

}

3382

3383

3384

# Abort if the server has crashed

3385

# return: 0 if ok

3386

# 1 question should be retried

3387

3388

3389

sub abort_if_fatal_error

3390

{

3391

return 0 if ($DBI::errstr =~ /No raw data handle/);

3392

return 1;

3393

}

3394

3395

sub small_rollback_segment

3396

{

3397

return 0;

3398

}

3399

3400

sub reconnect_on_errors

3401

{

3402

return 1;

3403

}

3404

3405

sub fix_for_insert

3406

{

3407

my ($self,$cmd) = @_;

3408

return $cmd;

3409

}

3410

3411

#############################################################################

3412

# Configuration for SAPDB

3413

#############################################################################

3414

3415

package db_sapdb;

3416

3417

sub new

3418

{

3419

my ($type,$host,$database)= @_;

3420

my $self= {};

3421

my %limits;

3422

bless $self;

3423

3424

$self->{'cmp_name'} = "sapdb";

3425

$self->{'data_source'} = "DBI:SAP_DB:$database";

3426

$self->{'limits'} = \%limits;

3427

$self->{'blob'} = "LONG"; # *

3428

$self->{'text'} = "LONG"; # *

3429

$self->{'double_quotes'} = 1; # Can handle: 'Walker''s'

3430

$self->{'drop_attr'} = "";

3431

$self->{'transactions'} = 1; # Transactions enabled *

3432

$self->{'char_null'} = "";

3433

$self->{'numeric_null'} = "";

3434

3435

$limits{'max_conditions'} = 9999; # (Actually not a limit) *

3436

$limits{'max_columns'} = 1023; # Max number of columns in table *

3437

$limits{'max_tables'} = 65000; # Should be big enough * unlimited actually

3438

$limits{'max_text_size'} = 15000; # Max size with default buffers.

3439

$limits{'query_size'} = 64*1024; # Max size with default buffers. *64 kb by default. May be set by system variable

3440

$limits{'max_index'} = 510; # Max number of keys *

3441

$limits{'max_index_parts'} = 16; # Max segments/key *

3442

$limits{'max_column_name'} = 32; # max table and column name *

3443

3444

$limits{'join_optimizer'} = 1; # Can optimize FROM tables *

3445

$limits{'load_data_infile'} = 0; # Has load data infile *

3446

$limits{'lock_tables'} = 1; # Has lock tables

3447

$limits{'functions'} = 1; # Has simple functions (+/-) *

3448

$limits{'group_functions'} = 1; # Have group functions *

3449

$limits{'group_func_sql_min_str'} = 1; # Can execute MIN() and MAX() on strings *

3450

$limits{'group_distinct_functions'}= 1; # Have count(distinct) *

3451

$limits{'select_without_from'}= 0; # Cannot do 'select 1'; *

3452

$limits{'multi_drop'} = 0; # Drop table cannot take many tables *

3453

$limits{'subqueries'} = 1; # Supports sub-queries. *

3454

$limits{'left_outer_join'} = 1; # Supports left outer joins *

3455

$limits{'table_wildcard'} = 1; # Has SELECT table_name.*

3456

$limits{'having_with_alias'} = 0; # Can use aliases in HAVING *

3457

$limits{'having_with_group'} = 1; # Can use group functions in HAVING *

3458

$limits{'like_with_column'} = 1; # Can use column1 LIKE column2 *

3459

$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1' *

3460

$limits{'group_by_position'} = 0; # Cannot use 'GROUP BY 1' *

3461

$limits{'alter_table'} = 1; # Have ALTER TABLE *

3462

$limits{'alter_add_multi_col'}= 1; # Have ALTER TABLE t add a int,add b int; *

3463

$limits{'alter_table_dropcol'}= 1; # Have ALTER TABLE DROP column *

3464

$limits{'insert_multi_value'} = 0; # INSERT ... values (1,2),(3,4) *

3465

3466

$limits{'group_func_extra_std'} = 0; # Does not have group function std().

3467

3468

$limits{'func_odbc_mod'} = 0; # Have function mod. *

3469

$limits{'func_extra_%'} = 0; # Does not have % as alias for mod() *

3470

$limits{'func_odbc_floor'} = 1; # Has func_odbc_floor function *

3471

$limits{'func_extra_if'} = 0; # Does not have function if. *

3472

$limits{'column_alias'} = 1; # Alias for fields in select statement. *

3473

$limits{'NEG'} = 1; # Supports -id *

3474

$limits{'func_extra_in_num'} = 0; # Has function in *

3475

$limits{'limit'} = 0; # Does not support the limit attribute *

3476

$limits{'working_blobs'} = 1; # If big varchar/blobs works *

3477

$limits{'order_by_unused'} = 1; #

3478

$limits{'working_all_fields'} = 1; #

3479

$limits{'multi_distinct'} = 1; # allows select count(distinct a),count(distinct b)..

3480

3481

3482

return $self;

3483

}

3484

3485

3486

# Get the version number of the database

3487

3488

3489

sub version

3490

{

3491

my ($self)=@_;

3492

my ($dbh,$sth,$version,@row);

3493

3494

$dbh=$self->connect();

3495

$sth = $dbh->prepare("SELECT KERNEL FROM VERSIONS") or die $DBI::errstr;

3496

$version="SAP DB (unknown)";

3497

if ($sth->execute && (@row = $sth->fetchrow_array)

3498

&& $row[0] =~ /([\d\.]+)/)

3499

{

3500

$version=$row[0];

3501

$version =~ s/KERNEL/SAP DB/i;

3502

}

3503

$sth->finish;

3504

$dbh->disconnect;

3505

$version .= "/ODBC" if ($self->{'data_source'} =~ /:ODBC:/);

3506

return $version;

3507

}

3508

3509

3510

# Connection with optional disabling of logging

3511

3512

3513

sub connect

3514

{

3515

my ($self)=@_;

3516

my ($dbh);

3517

$dbh=DBI->connect($self->{'data_source'}, $main::opt_user,

3518

$main::opt_password,{ PrintError => 0, AutoCommit => 1}) ||

3519

die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";

3520

3521

return $dbh;

3522

}

3523

3524

3525

# Returns a list of statements to create a table

3526

# The field types are in ANSI SQL format.

3527

3528

3529

sub create

3530

{

3531

my($self,$table_name,$fields,$index,$options) = @_;

3532

my($query,@queries,$nr);

3533

my @index;

3534

my @keys;

3535

3536

$query="create table $table_name (";

3537

foreach $field (@$fields)

3538

{

3539

$field =~ s/\bmediumint\b/int/i;

3540

$field =~ s/\btinyint\b/int/i;

3541

$field =~ s/ int$\d$/ int/i;

3542

$field =~ s/BLOB/LONG/i;

3543

$field =~ s/INTEGER\s*$\d+$/INTEGER/i;

3544

$field =~ s/SMALLINT\s*$\d+$/SMALLINT/i;

3545

$field =~ s/FLOAT\s*$(\d+),\d+$/FLOAT$$1$/i;

3546

$field =~ s/DOUBLE/FLOAT$38$/i;

3547

$field =~ s/DOUBLE\s+PRECISION/FLOAT$38$/i;

3548

$query.= $field . ',';

3549

}

3550

$nr=0;

3551

foreach $ind (@$index)

3552

{

3553

if ( $ind =~ /\bKEY\b/i ){

3554

push(@keys,"ALTER TABLE $table_name ADD $ind");

3555

} elsif ($ind =~ /^unique.*$([^\(]*)$$/i) {

3556

$nr++;

3557

my $query="create unique index ${table_name}_$nr on $table_name ($1)";

3558

push(@index,$query);

3559

}else{

3560

my @fields = split(' ',$ind);

3561

my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]";

3562

push(@index,$query);

3563

}

3564

}

3565

substr($query,-1)=")"; # Remove last ',';

3566

$query.=" $options" if (defined($options));

3567

push(@queries,$query);

3568

push(@queries,@keys);

3569

push(@queries,@index);

3570

return @queries;

3571

}

3572

3573

sub insert_file {

3574

my($self,$dbname, $file) = @_;

3575

print "insert of an ascii file isn't supported by SAPDB\n";

3576

return 0;

3577

}

3578

3579

3580

# Do any conversions to the ANSI SQL query so that the database can handle it

3581

3582

3583

sub query {

3584

my($self,$sql) = @_;

3585

return $sql;

3586

}

3587

3588

sub drop_index {

3589

my ($self,$table,$index) = @_;

3590

return "DROP INDEX $index";

3591

}

3592

3593

3594

# Abort if the server has crashed

3595

# return: 0 if ok

3596

# 1 question should be retried

3597

3598

3599

sub abort_if_fatal_error

3600

{

3601

return 0;

3602

}

3603

3604

sub small_rollback_segment

3605

{

3606

return 0;

3607

}

3608

3609

sub reconnect_on_errors

3610

{

3611

return 0;

3612

}

3613

3614

sub fix_for_insert

3615

{

3616

my ($self,$cmd) = @_;

3617

return $cmd;

3618

}

3619

3620

Older »