~azzar1/unity/add-show-desktop-key

$this->db_connect_id = ($new_link) ? @ocinlogon($this->user, $sqlpassword, $connect, 'UTF8') : (($this->persistency) ? @ociplogon($this->user, $sqlpassword, $connect, 'UTF8') : @ocilogon($this->user, $sqlpassword, $connect, 'UTF8'));

return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');

}

/**

* Version information about used database

function sql_server_info()

{

return @ociserverversion($this->db_connect_id);

}

/**

* SQL Transaction

* @access private

function _sql_transaction($status = 'begin')

{

switch ($status)

{

case 'begin':

return true;

break;

case 'commit':

return @ocicommit($this->db_connect_id);

break;

case 'rollback':

return @ocirollback($this->db_connect_id);

break;

}

return true;

}

/**

* Oracle specific code to handle the fact that it does not compare columns properly

* @access private

function _rewrite_col_compare($args)

{

if (sizeof($args) == 4)

{

if ($args[2] == '=')

{

return '(' . $args[0] . ' OR (' . $args[1] . ' is NULL AND ' . $args[3] . ' is NULL))';

}

100

else if ($args[2] == '<>')

101

{

102

// really just a fancy way of saying foo <> bar or (foo is NULL XOR bar is NULL) but SQL has no XOR :P

103

return '(' . $args[0] . ' OR ((' . $args[1] . ' is NULL AND ' . $args[3] . ' is NOT NULL) OR (' . $args[1] . ' is NOT NULL AND ' . $args[3] . ' is NULL)))';

104

}

105

}

106

else

107

{

108

return $this->_rewrite_where($args[0]);

109

}

110

}

111

112

/**

113

* Oracle specific code to handle it's lack of sanity

114

* @access private

115

116

function _rewrite_where($where_clause)

117

{

118

preg_match_all('/\s*(AND|OR)?\s*([\w_.]++)\s*(?:(=|<[=>]?|>=?)\s*((?>\'(?>[^\']++|\'\')*+\'|[\d-.]+))|((NOT )?IN\s*$(?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+$))/', $where_clause, $result, PREG_SET_ORDER);

119

$out = '';

120

foreach ($result as $val)

121

{

122

if (!isset($val[5]))

123

{

124

if ($val[4] !== "''")

125

{

126

$out .= $val[0];

127

}

128

else

129

{

130

$out .= ' ' . $val[1] . ' ' . $val[2];

131

if ($val[3] == '=')

132

{

133

$out .= ' is NULL';

134

}

135

else if ($val[3] == '<>')

136

{

137

$out .= ' is NOT NULL';

138

}

139

}

140

}

141

else

142

{

143

$in_clause = array();

144

$sub_exp = substr($val[5], strpos($val[5], '(') + 1, -1);

145

$extra = false;

146

preg_match_all('/\'(?>[^\']++|\'\')*+\'|[\d-.]++/', $sub_exp, $sub_vals, PREG_PATTERN_ORDER);

147

$i = 0;

148

foreach ($sub_vals[0] as $sub_val)

149

{

150

// two things:

151

// 1) This determines if an empty string was in the IN clausing, making us turn it into a NULL comparison

152

// 2) This fixes the 1000 list limit that Oracle has (ORA-01795)

153

if ($sub_val !== "''")

154

{

155

$in_clause[(int) $i++/1000][] = $sub_val;

156

}

157

else

158

{

159

$extra = true;

160

}

161

}

162

if (!$extra && $i < 1000)

163

{

164

$out .= $val[0];

165

}

166

else

167

{

168

$out .= ' ' . $val[1] . '(';

169

$in_array = array();

170

171

// constuct each IN() clause

172

foreach ($in_clause as $in_values)

173

{

174

$in_array[] = $val[2] . ' ' . (isset($val[6]) ? $val[6] : '') . 'IN(' . implode(', ', $in_values) . ')';

175

}

176

177

// Join the IN() clauses against a few ORs (IN is just a nicer OR anyway)

178

$out .= implode(' OR ', $in_array);

179

180

// handle the empty string case

181

if ($extra)

182

{

183

$out .= ' OR ' . $val[2] . ' is ' . (isset($val[6]) ? $val[6] : '') . 'NULL';

184

}

185

$out .= ')';

186

187

unset($in_array, $in_clause);

188

}

189

}

190

}

191

192

return $out;

193

}

194

195

/**

196

* Base query method

197

198

* @param string $query Contains the SQL query which shall be executed

199

* @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache

200

* @return mixed When casted to bool the returned value returns true on success and false on failure

201

202

* @access public

203

204

function sql_query($query = '', $cache_ttl = 0)

205

{

206

if ($query != '')

207

{

208

global $cache;

209

210

// EXPLAIN only in extra debug mode

211

if (defined('DEBUG_EXTRA'))

212

{

213

$this->sql_report('start', $query);

214

}

215

216

$this->last_query_text = $query;

217

$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;

218

$this->sql_add_num_queries($this->query_result);

219

220

if ($this->query_result === false)

221

{

222

$in_transaction = false;

223

if (!$this->transaction)

224

{

225

$this->sql_transaction('begin');

226

}

227

else

228

{

229

$in_transaction = true;

230

}

231

232

$array = array();

233

234

// We overcome Oracle's 4000 char limit by binding vars

235

if (strlen($query) > 4000)

236

{

237

if (preg_match('/^(INSERT INTO[^(]++)\$([^()]+)\$ VALUES[^(]++\$(.*?)\$$/s', $query, $regs))

238

{

239

if (strlen($regs[3]) > 4000)

240

{

241

$cols = explode(', ', $regs[2]);

242

preg_match_all('/\'(?:[^\']++|\'\')*+\'|[\d-.]+/', $regs[3], $vals, PREG_PATTERN_ORDER);

243

244

$inserts = $vals[0];

245

unset($vals);

246

247

foreach ($inserts as $key => $value)

248

{

249

if (!empty($value) && $value[0] === "'" && strlen($value) > 4002) // check to see if this thing is greater than the max + 'x2

250

{

251

$inserts[$key] = ':' . strtoupper($cols[$key]);

252

$array[$inserts[$key]] = str_replace("''", "'", substr($value, 1, -1));

253

}

254

}

255

256

$query = $regs[1] . '(' . $regs[2] . ') VALUES (' . implode(', ', $inserts) . ')';

257

}

258

}

259

else if (preg_match_all('/^(UPDATE [\\w_]++\\s+SET )([\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+)(?:,\\s*[\\w_]++\\s*=\\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]+))*+)\\s+(WHERE.*)$/s', $query, $data, PREG_SET_ORDER))

260

{

261

if (strlen($data[0][2]) > 4000)

262

{

263

$update = $data[0][1];

264

$where = $data[0][3];

265

preg_match_all('/([\\w_]++)\\s*=\\s*(\'(?:[^\']++|\'\')*+\'|[\d-.]++)/', $data[0][2], $temp, PREG_SET_ORDER);

266

unset($data);

267

268

$cols = array();

269

foreach ($temp as $value)

270

{

271

if (!empty($value[2]) && $value[2][0] === "'" && strlen($value[2]) > 4002) // check to see if this thing is greater than the max + 'x2

272

{

273

$cols[] = $value[1] . '=:' . strtoupper($value[1]);

274

$array[$value[1]] = str_replace("''", "'", substr($value[2], 1, -1));

275

}

276

else

277

{

278

$cols[] = $value[1] . '=' . $value[2];

279

}

280

}

281

282

$query = $update . implode(', ', $cols) . ' ' . $where;

283

unset($cols);

284

}

285

}

286

}

287

288

switch (substr($query, 0, 6))

289

{

290

case 'DELETE':

291

if (preg_match('/^(DELETE FROM [\w_]++ WHERE)((?:\s*(?:AND|OR)?\s*[\w_]+\s*(?:(?:=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]+)|(?:NOT )?IN\s*$(?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]+,? ?)*+$))*+)$/', $query, $regs))

292

{

293

$query = $regs[1] . $this->_rewrite_where($regs[2]);

294

unset($regs);

295

}

296

break;

297

298

case 'UPDATE':

299

if (preg_match('/^(UPDATE [\\w_]++\\s+SET [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++)(?:, [\\w_]+\s*=\s*(?:\'(?:[^\']++|\'\')*+\'|[\d-.]++|:\w++))*+\\s+WHERE)(.*)$/s', $query, $regs))

300

{

301

$query = $regs[1] . $this->_rewrite_where($regs[2]);

302

unset($regs);

303

}

304

break;

305

306

case 'SELECT':

307

$query = preg_replace_callback('/([\w_.]++)\s*(?:(=|<>)\s*(?>\'(?>[^\']++|\'\')*+\'|[\d-.]++|([\w_.]++))|(?:NOT )?IN\s*$(?>\'(?>[^\']++|\'\')*+\',? ?|[\d-.]++,? ?)*+$)/', array($this, '_rewrite_col_compare'), $query);

308

break;

309

}

310

311

$this->query_result = @ociparse($this->db_connect_id, $query);

312

313

foreach ($array as $key => $value)

314

{

315

@ocibindbyname($this->query_result, $key, $array[$key], -1);

316

}

317

318

$success = @ociexecute($this->query_result, OCI_DEFAULT);

319

320

if (!$success)

321

{

322

$this->sql_error($query);

323

$this->query_result = false;

324

}

325

else

326

{

327

if (!$in_transaction)

328

{

329

$this->sql_transaction('commit');

330

}

331

}

332

333

if (defined('DEBUG_EXTRA'))

334

{

335

$this->sql_report('stop', $query);

336

}

337

338

if ($cache_ttl && method_exists($cache, 'sql_save'))

339

{

340

$this->open_queries[(int) $this->query_result] = $this->query_result;

341

$cache->sql_save($query, $this->query_result, $cache_ttl);

342

}

343

else if (strpos($query, 'SELECT') === 0 && $this->query_result)

344

{

345

$this->open_queries[(int) $this->query_result] = $this->query_result;

346

}

347

}

348

else if (defined('DEBUG_EXTRA'))

349

{

350

$this->sql_report('fromcache', $query);

351

}

352

}

353

else

354

{

355

return false;

356

}

357

358

return ($this->query_result) ? $this->query_result : false;

359

}

360

361

/**

362

* Build LIMIT query

363

364

function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)

365

{

366

$this->query_result = false;

367

368

$query = 'SELECT * FROM (SELECT /*+ FIRST_ROWS */ rownum AS xrownum, a.* FROM (' . $query . ') a WHERE rownum <= ' . ($offset + $total) . ') WHERE xrownum >= ' . $offset;

369

370

return $this->sql_query($query, $cache_ttl);

371

}

372

373

/**

374

* Return number of affected rows

375

376

function sql_affectedrows()

377

{

378

return ($this->query_result) ? @ocirowcount($this->query_result) : false;

379

}

380

381

/**

382

* Fetch current row

383

384

function sql_fetchrow($query_id = false)

385

{

386

global $cache;

387

388

if ($query_id === false)

389

{

390

$query_id = $this->query_result;

391

}

392

393

if (isset($cache->sql_rowset[$query_id]))

394

{

395

return $cache->sql_fetchrow($query_id);

396

}

397

398

if ($query_id !== false)

399

{

400

$row = array();

401

$result = @ocifetchinto($query_id, $row, OCI_ASSOC + OCI_RETURN_NULLS);

402

403

if (!$result || !$row)

404

{

405

return false;

406

}

407

408

$result_row = array();

409

foreach ($row as $key => $value)

410

{

411

// Oracle treats empty strings as null

412

if (is_null($value))

413

{

414

$value = '';

415

}

416

417

// OCI->CLOB?

418

if (is_object($value))

419

{

420

$value = $value->load();

421

}

422

423

$result_row[strtolower($key)] = $value;

424

}

425

426

return $result_row;

427

}

428

429

return false;

430

}

431

432

/**

433

* Seek to given row number

434

* rownum is zero-based

435

436

function sql_rowseek($rownum, &$query_id)

437

{

438

global $cache;

439

440

if ($query_id === false)

441

{

442

$query_id = $this->query_result;

443

}

444

445

if (isset($cache->sql_rowset[$query_id]))

446

{

447

return $cache->sql_rowseek($rownum, $query_id);

448

}

449

450

if ($query_id === false)

451

{

452

return false;

453

}

454

455

// Reset internal pointer

456

@ociexecute($query_id, OCI_DEFAULT);

457

458

// We do not fetch the row for rownum == 0 because then the next resultset would be the second row

459

for ($i = 0; $i < $rownum; $i++)

460

{

461

if (!$this->sql_fetchrow($query_id))

462

{

463

return false;

464

}

465

}

466

467

return true;

468

}

469

470

/**

471

* Get last inserted id after insert statement

472

473

function sql_nextid()

474

{

475

$query_id = $this->query_result;

476

477

if ($query_id !== false && $this->last_query_text != '')

478

{

479

if (preg_match('#^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)#is', $this->last_query_text, $tablename))

480

{

481

$query = 'SELECT ' . $tablename[1] . '_seq.currval FROM DUAL';

482

$stmt = @ociparse($this->db_connect_id, $query);

483

@ociexecute($stmt, OCI_DEFAULT);

484

485

$temp_result = @ocifetchinto($stmt, $temp_array, OCI_ASSOC + OCI_RETURN_NULLS);

486

@ocifreestatement($stmt);

487

488

if ($temp_result)

489

{

490

return $temp_array['CURRVAL'];

491

}

492

else

493

{

494

return false;

495

}

496

}

497

}

498

499

return false;

500

}

501

502

/**

503

* Free sql result

504

505

function sql_freeresult($query_id = false)

506

{

507

global $cache;

508

509

if ($query_id === false)

510

{

511

$query_id = $this->query_result;

512

}

513

514

if (isset($cache->sql_rowset[$query_id]))

515

{

516

return $cache->sql_freeresult($query_id);

517

}

518

519

if (isset($this->open_queries[(int) $query_id]))

520

{

521

unset($this->open_queries[(int) $query_id]);

522

return @ocifreestatement($query_id);

523

}

524

525

return false;

526

}

527

528

/**

529

* Escape string used in sql query

530

531

function sql_escape($msg)

532

{

533

return str_replace("'", "''", $msg);

534

}

535

536

/**

537

* Build LIKE expression

538

* @access private

539

540

function _sql_like_expression($expression)

541

{

542

return $expression . " ESCAPE '\\'";

543

}

544

545

function _sql_custom_build($stage, $data)

546

{

547

return $data;

548

}

549

550

/**

551

* return sql error array

552

* @access private

553

554

function _sql_error()

555

{

556

$error = @ocierror();

557

$error = (!$error) ? @ocierror($this->query_result) : $error;

558

$error = (!$error) ? @ocierror($this->db_connect_id) : $error;

559

560

if ($error)

561

{

562

$this->last_error_result = $error;

563

}

564

else

565

{

566

$error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();

567

}

568

569

return $error;

570

}

571

572

/**

573

* Close sql connection

574

* @access private

575

576

function _sql_close()

577

{

578

return @ocilogoff($this->db_connect_id);

579

}

580

581

/**

582

* Build db-specific report

583

* @access private

584

585

function _sql_report($mode, $query = '')

586

{

587

switch ($mode)

588

{

589

case 'start':

590

591

$html_table = false;

592

593

// Grab a plan table, any will do

594

$sql = "SELECT table_name

595

FROM USER_TABLES

596

WHERE table_name LIKE '%PLAN_TABLE%'";

597

$stmt = ociparse($this->db_connect_id, $sql);

598

ociexecute($stmt);

599

$result = array();

600

601

if (ocifetchinto($stmt, $result, OCI_ASSOC + OCI_RETURN_NULLS))

602

{

603

$table = $result['TABLE_NAME'];

604

605

// This is the statement_id that will allow us to track the plan

606

$statement_id = substr(md5($query), 0, 30);

607

608

// Remove any stale plans

609

$stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'");

610

ociexecute($stmt2);

611

ocifreestatement($stmt2);

612

613

// Explain the plan

614

$sql = "EXPLAIN PLAN

615

SET STATEMENT_ID = '$statement_id'

616

FOR $query";

617

$stmt2 = ociparse($this->db_connect_id, $sql);

618

ociexecute($stmt2);

619

ocifreestatement($stmt2);

620

621

// Get the data from the plan

622

$sql = "SELECT operation, options, object_name, object_type, cardinality, cost

623

FROM plan_table

624

START WITH id = 0 AND statement_id = '$statement_id'

625

CONNECT BY PRIOR id = parent_id

626

AND statement_id = '$statement_id'";

627

$stmt2 = ociparse($this->db_connect_id, $sql);

628

ociexecute($stmt2);

629

630

$row = array();

631

while (ocifetchinto($stmt2, $row, OCI_ASSOC + OCI_RETURN_NULLS))

632

{

633

$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);

634

}

635

636

ocifreestatement($stmt2);

637

638

// Remove the plan we just made, we delete them on request anyway

639

$stmt2 = ociparse($this->db_connect_id, "DELETE FROM $table WHERE statement_id='$statement_id'");

640

ociexecute($stmt2);

641

ocifreestatement($stmt2);

642

}

643

644

ocifreestatement($stmt);

645

646

if ($html_table)

647

{

648

$this->html_hold .= '</table>';

649

}

650

651

break;

652

653

case 'fromcache':

654

$endtime = explode(' ', microtime());

655

$endtime = $endtime[0] + $endtime[1];

656

657

$result = @ociparse($this->db_connect_id, $query);

658

$success = @ociexecute($result, OCI_DEFAULT);

659

$row = array();

660

661

while (@ocifetchinto($result, $row, OCI_ASSOC + OCI_RETURN_NULLS))

662

{

663

// Take the time spent on parsing rows into account

664

}

665

@ocifreestatement($result);

666

667

$splittime = explode(' ', microtime());

668

$splittime = $splittime[0] + $splittime[1];

669

670

$this->sql_report('record_fromcache', $query, $endtime, $splittime);

671

672

break;

673

}

674

}

675

}

676

677

b'\\ No newline at end of file'

Older »