~michaelforrest/use-case-mapper/trunk

# * <tt>:schema_search_path</tt> - An optional schema search path for the connection given as a string of comma-separated schema names. This is backward-compatible with the <tt>:schema_order</tt> option.

185

# * <tt>:encoding</tt> - An optional client encoding that is used in a <tt>SET client_encoding TO <encoding></tt> call on the connection.

186

# * <tt>:min_messages</tt> - An optional client min messages that is used in a <tt>SET client_min_messages TO <min_messages></tt> call on the connection.

187

# * <tt>:allow_concurrency</tt> - If true, use async query methods so Ruby threads don't deadlock; otherwise, use blocking query methods.

188

class PostgreSQLAdapter < AbstractAdapter

189

ADAPTER_NAME = 'PostgreSQL'.freeze

190

191

NATIVE_DATABASE_TYPES = {

192

:primary_key => "serial primary key".freeze,

193

:string => { :name => "character varying", :limit => 255 },

194

:text => { :name => "text" },

195

:integer => { :name => "integer" },

196

:float => { :name => "float" },

197

:decimal => { :name => "decimal" },

198

:datetime => { :name => "timestamp" },

199

:timestamp => { :name => "timestamp" },

200

:time => { :name => "time" },

201

:date => { :name => "date" },

202

:binary => { :name => "bytea" },

203

:boolean => { :name => "boolean" },

204

:xml => { :name => "xml" }

205

}

206

207

# Returns 'PostgreSQL' as adapter name for identification purposes.

208

def adapter_name

209

ADAPTER_NAME

210

end

211

212

# Initializes and connects a PostgreSQL adapter.

213

def initialize(connection, logger, connection_parameters, config)

214

super(connection, logger)

215

@connection_parameters, @config = connection_parameters, config

216

217

connect

218

end

219

220

# Is this connection alive and ready for queries?

221

def active?

222

if @connection.respond_to?(:status)

223

@connection.status == PGconn::CONNECTION_OK

224

else

225

# We're asking the driver, not ActiveRecord, so use @connection.query instead of #query

226

@connection.query 'SELECT 1'

227

true

228

end

229

# postgres-pr raises a NoMethodError when querying if no connection is available.

230

rescue PGError, NoMethodError

231

false

232

end

233

234

# Close then reopen the connection.

235

def reconnect!

236

if @connection.respond_to?(:reset)

237

@connection.reset

238

configure_connection

239

else

240

disconnect!

241

connect

242

end

243

end

244

245

# Close the connection.

246

def disconnect!

247

@connection.close rescue nil

248

end

249

250

def native_database_types #:nodoc:

251

NATIVE_DATABASE_TYPES

252

end

253

254

# Does PostgreSQL support migrations?

255

def supports_migrations?

256

true

257

end

258

259

# Does PostgreSQL support finding primary key on non-ActiveRecord tables?

260

def supports_primary_key? #:nodoc:

261

true

262

end

263

264

# Does PostgreSQL support standard conforming strings?

265

def supports_standard_conforming_strings?

266

# Temporarily set the client message level above error to prevent unintentional

267

# error messages in the logs when working on a PostgreSQL database server that

268

# does not support standard conforming strings.

269

client_min_messages_old = client_min_messages

270

self.client_min_messages = 'panic'

271

272

# postgres-pr does not raise an exception when client_min_messages is set higher

273

# than error and "SHOW standard_conforming_strings" fails, but returns an empty

274

# PGresult instead.

275

has_support = query('SHOW standard_conforming_strings')[0][0] rescue false

276

self.client_min_messages = client_min_messages_old

277

has_support

278

end

279

280

def supports_insert_with_returning?

281

postgresql_version >= 80200

282

end

283

284

def supports_ddl_transactions?

285

true

286

end

287

288

def supports_savepoints?

289

true

290

end

291

292

# Returns the configured supported identifier length supported by PostgreSQL,

293

# or report the default of 63 on PostgreSQL 7.x.

294

def table_alias_length

295

@table_alias_length ||= (postgresql_version >= 80000 ? query('SHOW max_identifier_length')[0][0].to_i : 63)

296

end

297

298

# QUOTING ==================================================

299

300

# Escapes binary strings for bytea input to the database.

301

def escape_bytea(value)

302

if @connection.respond_to?(:escape_bytea)

303

self.class.instance_eval do

304

define_method(:escape_bytea) do |value|

305

@connection.escape_bytea(value) if value

306

end

307

end

308

elsif PGconn.respond_to?(:escape_bytea)

309

self.class.instance_eval do

310

define_method(:escape_bytea) do |value|

311

PGconn.escape_bytea(value) if value

312

end

313

end

314

else

315

self.class.instance_eval do

316

define_method(:escape_bytea) do |value|

317

if value

318

result = ''

319

value.each_byte { |c| result << sprintf('\\\\%03o', c) }

320

result

321

end

322

end

323

end

324

end

325

escape_bytea(value)

326

end

327

328

# Unescapes bytea output from a database to the binary string it represents.

329

# NOTE: This is NOT an inverse of escape_bytea! This is only to be used

330

# on escaped binary output from database drive.

331

def unescape_bytea(value)

332

# In each case, check if the value actually is escaped PostgreSQL bytea output

333

# or an unescaped Active Record attribute that was just written.

334

if PGconn.respond_to?(:unescape_bytea)

335

self.class.instance_eval do

336

define_method(:unescape_bytea) do |value|

337

if value =~ /\\\d{3}/

338

PGconn.unescape_bytea(value)

339

else

340

value

341

end

342

end

343

end

344

else

345

self.class.instance_eval do

346

define_method(:unescape_bytea) do |value|

347

if value =~ /\\\d{3}/

348

result = ''

349

i, max = 0, value.size

350

while i < max

351

char = value[i]

352

if char == ?\\

353

if value[i+1] == ?\\

354

char = ?\\

355

i += 1

356

else

357

char = value[i+1..i+3].oct

358

i += 3

359

end

360

end

361

result << char

362

i += 1

363

end

364

result

365

else

366

value

367

end

368

end

369

end

370

end

371

unescape_bytea(value)

372

end

373

374

# Quotes PostgreSQL-specific data types for SQL input.

375

def quote(value, column = nil) #:nodoc:

376

if value.kind_of?(String) && column && column.type == :binary

377

"#{quoted_string_prefix}'#{escape_bytea(value)}'"

378

elsif value.kind_of?(String) && column && column.sql_type =~ /^xml$/

379

"xml E'#{quote_string(value)}'"

380

elsif value.kind_of?(Numeric) && column && column.sql_type =~ /^money$/

381

# Not truly string input, so doesn't require (or allow) escape string syntax.

382

"'#{value.to_s}'"

383

elsif value.kind_of?(String) && column && column.sql_type =~ /^bit/

384

case value

385

when /^[01]*$/

386

"B'#{value}'" # Bit-string notation

387

when /^[0-9A-F]*$/i

388

"X'#{value}'" # Hexadecimal notation

389

end

390

else

391

super

392

end

393

end

394

395

# Quotes strings for use in SQL input in the postgres driver for better performance.

396

def quote_string(s) #:nodoc:

397

if @connection.respond_to?(:escape)

398

self.class.instance_eval do

399

define_method(:quote_string) do |s|

400

@connection.escape(s)

401

end

402

end

403

elsif PGconn.respond_to?(:escape)

404

self.class.instance_eval do

405

define_method(:quote_string) do |s|

406

PGconn.escape(s)

407

end

408

end

409

else

410

# There are some incorrectly compiled postgres drivers out there

411

# that don't define PGconn.escape.

412

self.class.instance_eval do

413

remove_method(:quote_string)

414

end

415

end

416

quote_string(s)

417

end

418

419

# Checks the following cases:

420

421

# - table_name

422

# - "table.name"

423

# - schema_name.table_name

424

# - schema_name."table.name"

425

# - "schema.name".table_name

426

# - "schema.name"."table.name"

427

def quote_table_name(name)

428

schema, name_part = extract_pg_identifier_from_name(name.to_s)

429

430

unless name_part

431

quote_column_name(schema)

432

else

433

table_name, name_part = extract_pg_identifier_from_name(name_part)

434

"#{quote_column_name(schema)}.#{quote_column_name(table_name)}"

435

end

436

end

437

438

# Quotes column names for use in SQL queries.

439

def quote_column_name(name) #:nodoc:

440

PGconn.quote_ident(name.to_s)

441

end

442

443

# Quote date/time values for use in SQL input. Includes microseconds

444

# if the value is a Time responding to usec.

445

def quoted_date(value) #:nodoc:

446

if value.acts_like?(:time) && value.respond_to?(:usec)

447

"#{super}.#{sprintf("%06d", value.usec)}"

448

else

449

super

450

end

451

end

452

453

# REFERENTIAL INTEGRITY ====================================

454

455

def supports_disable_referential_integrity?() #:nodoc:

456

version = query("SHOW server_version")[0][0].split('.')

457

(version[0].to_i >= 8 && version[1].to_i >= 1) ? true : false

458

rescue

459

return false

460

end

461

462

def disable_referential_integrity(&block) #:nodoc:

463

if supports_disable_referential_integrity?() then

464

execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";"))

465

end

466

yield

467

ensure

468

if supports_disable_referential_integrity?() then

469

execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";"))

470

end

471

end

472

473

# DATABASE STATEMENTS ======================================

474

475

# Executes a SELECT query and returns an array of rows. Each row is an

476

# array of field values.

477

def select_rows(sql, name = nil)

478

select_raw(sql, name).last

479

end

480

481

# Executes an INSERT query and returns the new record's ID

482

def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil)

483

# Extract the table from the insert sql. Yuck.

484

table = sql.split(" ", 4)[2].gsub('"', '')

485

486

# Try an insert with 'returning id' if available (PG >= 8.2)

487

if supports_insert_with_returning?

488

pk, sequence_name = *pk_and_sequence_for(table) unless pk

489

if pk

490

id = select_value("#{sql} RETURNING #{quote_column_name(pk)}")

491

clear_query_cache

492

return id

493

end

494

end

495

496

# Otherwise, insert then grab last_insert_id.

497

if insert_id = super

498

insert_id

499

else

500

# If neither pk nor sequence name is given, look them up.

501

unless pk || sequence_name

502

pk, sequence_name = *pk_and_sequence_for(table)

503

end

504

505

# If a pk is given, fallback to default sequence name.

506

# Don't fetch last insert id for a table without a pk.

507

if pk && sequence_name ||= default_sequence_name(table, pk)

508

last_insert_id(table, sequence_name)

509

end

510

end

511

end

512

513

# create a 2D array representing the result set

514

def result_as_array(res) #:nodoc:

515

# check if we have any binary column and if they need escaping

516

unescape_col = []

517

for j in 0...res.nfields do

518

# unescape string passed BYTEA field (OID == 17)

519

unescape_col << ( res.ftype(j)==17 )

520

end

521

522

ary = []

523

for i in 0...res.ntuples do

524

ary << []

525

for j in 0...res.nfields do

526

data = res.getvalue(i,j)

527

data = unescape_bytea(data) if unescape_col[j] and data.is_a?(String)

528

ary[i] << data

529

end

530

end

531

return ary

532

end

533

534

535

# Queries the database and returns the results in an Array-like object

536

def query(sql, name = nil) #:nodoc:

537

log(sql, name) do

538

if @async

539

res = @connection.async_exec(sql)

540

else

541

res = @connection.exec(sql)

542

end

543

return result_as_array(res)

544

end

545

end

546

547

# Executes an SQL statement, returning a PGresult object on success

548

# or raising a PGError exception otherwise.

549

def execute(sql, name = nil)

550

log(sql, name) do

551

if @async

552

@connection.async_exec(sql)

553

else

554

@connection.exec(sql)

555

end

556

end

557

end

558

559

# Executes an UPDATE query and returns the number of affected tuples.

560

def update_sql(sql, name = nil)

561

super.cmd_tuples

562

end

563

564

# Begins a transaction.

565

def begin_db_transaction

566

execute "BEGIN"

567

end

568

569

# Commits a transaction.

570

def commit_db_transaction

571

execute "COMMIT"

572

end

573

574

# Aborts a transaction.

575

def rollback_db_transaction

576

execute "ROLLBACK"

577

end

578

579

if defined?(PGconn::PQTRANS_IDLE)

580

# The ruby-pg driver supports inspecting the transaction status,

581

# while the ruby-postgres driver does not.

582

def outside_transaction?

583

@connection.transaction_status == PGconn::PQTRANS_IDLE

584

end

585

end

586

587

def create_savepoint

588

execute("SAVEPOINT #{current_savepoint_name}")

589

end

590

591

def rollback_to_savepoint

592

execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")

593

end

594

595

def release_savepoint

596

execute("RELEASE SAVEPOINT #{current_savepoint_name}")

597

end

598

599

# SCHEMA STATEMENTS ========================================

600

601

def recreate_database(name) #:nodoc:

602

drop_database(name)

603

create_database(name)

604

end

605

606

# Create a new PostgreSQL database. Options include <tt>:owner</tt>, <tt>:template</tt>,

607

# <tt>:encoding</tt>, <tt>:tablespace</tt>, and <tt>:connection_limit</tt> (note that MySQL uses

608

# <tt>:charset</tt> while PostgreSQL uses <tt>:encoding</tt>).

609

610

# Example:

611

# create_database config[:database], config

612

# create_database 'foo_development', :encoding => 'unicode'

613

def create_database(name, options = {})

614

options = options.reverse_merge(:encoding => "utf8")

615

616

option_string = options.symbolize_keys.sum do |key, value|

617

case key

618

when :owner

619

" OWNER = \"#{value}\""

620

when :template

621

" TEMPLATE = \"#{value}\""

622

when :encoding

623

" ENCODING = '#{value}'"

624

when :tablespace

625

" TABLESPACE = \"#{value}\""

626

when :connection_limit

627

" CONNECTION LIMIT = #{value}"

628

else

629

630

end

631

end

632

633

execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}"

634

end

635

636

# Drops a PostgreSQL database

637

638

# Example:

639

# drop_database 'matt_development'

640

def drop_database(name) #:nodoc:

641

if postgresql_version >= 80200

642

execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"

643

else

644

begin

645

execute "DROP DATABASE #{quote_table_name(name)}"

646

rescue ActiveRecord::StatementInvalid

647

@logger.warn "#{name} database doesn't exist." if @logger

648

end

649

end

650

end

651

652

653

# Returns the list of all tables in the schema search path or a specified schema.

654

def tables(name = nil)

655

schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')

656

query(<<-SQL, name).map { |row| row[0] }

657

SELECT tablename

658

FROM pg_tables

659

WHERE schemaname IN (#{schemas})

660

SQL

661

end

662

663

# Returns the list of all indexes for a table.

664

def indexes(table_name, name = nil)

665

schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')

666

result = query(<<-SQL, name)

667

SELECT distinct i.relname, d.indisunique, d.indkey, t.oid

668

FROM pg_class t, pg_class i, pg_index d

669

WHERE i.relkind = 'i'

670

AND d.indexrelid = i.oid

671

AND d.indisprimary = 'f'

672

AND t.oid = d.indrelid

673

AND t.relname = '#{table_name}'

674

AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN (#{schemas}) )

675

ORDER BY i.relname

676

SQL

677

678

679

indexes = []

680

681

indexes = result.map do |row|

682

index_name = row[0]

683

unique = row[1] == 't'

684

indkey = row[2].split(" ")

685

oid = row[3]

686

687

columns = query(<<-SQL, "Columns for index #{row[0]} on #{table_name}").inject({}) {|attlist, r| attlist[r[1]] = r[0]; attlist}

688

SELECT a.attname, a.attnum

689

FROM pg_attribute a

690

WHERE a.attrelid = #{oid}

691

AND a.attnum IN (#{indkey.join(",")})

692

SQL

693

694

column_names = indkey.map {|attnum| columns[attnum] }

695

IndexDefinition.new(table_name, index_name, unique, column_names)

696

697

end

698

699

indexes

700

end

701

702

# Returns the list of all column definitions for a table.

703

def columns(table_name, name = nil)

704

# Limit, precision, and scale are all handled by the superclass.

705

column_definitions(table_name).collect do |name, type, default, notnull|

706

PostgreSQLColumn.new(name, default, type, notnull == 'f')

707

end

708

end

709

710

# Returns the current database name.

711

def current_database

712

query('select current_database()')[0][0]

713

end

714

715

# Returns the current database encoding format.

716

def encoding

717

query(<<-end_sql)[0][0]

718

SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database

719

WHERE pg_database.datname LIKE '#{current_database}'

720

end_sql

721

end

722

723

# Sets the schema search path to a string of comma-separated schema names.

724

# Names beginning with $ have to be quoted (e.g. $user => '$user').

725

# See: http://www.postgresql.org/docs/current/static/ddl-schemas.html

726

727

# This should be not be called manually but set in database.yml.

728

def schema_search_path=(schema_csv)

729

if schema_csv

730

execute "SET search_path TO #{schema_csv}"

731

@schema_search_path = schema_csv

732

end

733

end

734

735

# Returns the active schema search path.

736

def schema_search_path

737

@schema_search_path ||= query('SHOW search_path')[0][0]

738

end

739

740

# Returns the current client message level.

741

def client_min_messages

742

query('SHOW client_min_messages')[0][0]

743

end

744

745

# Set the client message level.

746

def client_min_messages=(level)

747

execute("SET client_min_messages TO '#{level}'")

748

end

749

750

# Returns the sequence name for a table's primary key or some other specified key.

751

def default_sequence_name(table_name, pk = nil) #:nodoc:

752

default_pk, default_seq = pk_and_sequence_for(table_name)

753

default_seq || "#{table_name}_#{pk || default_pk || 'id'}_seq"

754

end

755

756

# Resets the sequence of a table's primary key to the maximum value.

757

def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc:

758

unless pk and sequence

759

default_pk, default_sequence = pk_and_sequence_for(table)

760

pk ||= default_pk

761

sequence ||= default_sequence

762

end

763

if pk

764

if sequence

765

quoted_sequence = quote_column_name(sequence)

766

767

select_value <<-end_sql, 'Reset sequence'

768

SELECT setval('#{quoted_sequence}', (SELECT COALESCE(MAX(#{quote_column_name pk})+(SELECT increment_by FROM #{quoted_sequence}), (SELECT min_value FROM #{quoted_sequence})) FROM #{quote_table_name(table)}), false)

769

end_sql

770

else

771

@logger.warn "#{table} has primary key #{pk} with no default sequence" if @logger

772

end

773

end

774

end

775

776

# Returns a table's primary key and belonging sequence.

777

def pk_and_sequence_for(table) #:nodoc:

778

# First try looking for a sequence with a dependency on the

779

# given table's primary key.

780

result = query(<<-end_sql, 'PK and serial sequence')[0]

781

SELECT attr.attname, seq.relname

782

FROM pg_class seq,

783

pg_attribute attr,

784

pg_depend dep,

785

pg_namespace name,

786

pg_constraint cons

787

WHERE seq.oid = dep.objid

788

AND seq.relkind = 'S'

789

AND attr.attrelid = dep.refobjid

790

AND attr.attnum = dep.refobjsubid

791

AND attr.attrelid = cons.conrelid

792

AND attr.attnum = cons.conkey[1]

793

AND cons.contype = 'p'

794

AND dep.refobjid = '#{quote_table_name(table)}'::regclass

795

end_sql

796

797

if result.nil? or result.empty?

798

# If that fails, try parsing the primary key's default value.

799

# Support the 7.x and 8.0 nextval('foo'::text) as well as

800

# the 8.1+ nextval('foo'::regclass).

801

result = query(<<-end_sql, 'PK and custom sequence')[0]

802

SELECT attr.attname,

803

CASE

804

WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN

805

substr(split_part(def.adsrc, '''', 2),

806

strpos(split_part(def.adsrc, '''', 2), '.')+1)

807

ELSE split_part(def.adsrc, '''', 2)

808

END

809

FROM pg_class t

810

JOIN pg_attribute attr ON (t.oid = attrelid)

811

JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum)

812

JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1])

813

WHERE t.oid = '#{quote_table_name(table)}'::regclass

814

AND cons.contype = 'p'

815

AND def.adsrc ~* 'nextval'

816

end_sql

817

end

818

819

# [primary_key, sequence]

820

[result.first, result.last]

821

rescue

822

nil

823

end

824

825

# Returns just a table's primary key

826

def primary_key(table)

827

pk_and_sequence = pk_and_sequence_for(table)

828

pk_and_sequence && pk_and_sequence.first

829

end

830

831

# Renames a table.

832

def rename_table(name, new_name)

833

execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}"

834

end

835

836

# Adds a new column to the named table.

837

# See TableDefinition#column for details of the options you can use.

838

def add_column(table_name, column_name, type, options = {})

839

default = options[:default]

840

notnull = options[:null] == false

841

842

# Add the column.

843

execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}")

844

845

change_column_default(table_name, column_name, default) if options_include_default?(options)

846

change_column_null(table_name, column_name, false, default) if notnull

847

end

848

849

# Changes the column of a table.

850

def change_column(table_name, column_name, type, options = {})

851

quoted_table_name = quote_table_name(table_name)

852

853

begin

854

execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"

855

rescue ActiveRecord::StatementInvalid => e

856

raise e if postgresql_version > 80000

857

# This is PostgreSQL 7.x, so we have to use a more arcane way of doing it.

858

begin

859

begin_db_transaction

860

tmp_column_name = "#{column_name}_ar_tmp"

861

add_column(table_name, tmp_column_name, type, options)

862

execute "UPDATE #{quoted_table_name} SET #{quote_column_name(tmp_column_name)} = CAST(#{quote_column_name(column_name)} AS #{type_to_sql(type, options[:limit], options[:precision], options[:scale])})"

863

remove_column(table_name, column_name)

864

rename_column(table_name, tmp_column_name, column_name)

865

commit_db_transaction

866

rescue

867

rollback_db_transaction

868

end

869

end

870

871

change_column_default(table_name, column_name, options[:default]) if options_include_default?(options)

872

change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null)

873

end

874

875

# Changes the default value of a table column.

876

def change_column_default(table_name, column_name, default)

877

execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}"

878

end

879

880

def change_column_null(table_name, column_name, null, default = nil)

881

unless null || default.nil?

882

execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")

883

end

884

execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL")

885

end

886

887

# Renames a column in a table.

888

def rename_column(table_name, column_name, new_column_name)

889

execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"

890

end

891

892

# Drops an index from a table.

893

def remove_index(table_name, options = {})

894

execute "DROP INDEX #{quote_table_name(index_name(table_name, options))}"

895

end

896

897

# Maps logical Rails types to PostgreSQL-specific data types.

898

def type_to_sql(type, limit = nil, precision = nil, scale = nil)

899

return super unless type.to_s == 'integer'

900

901

case limit

902

when 1..2; 'smallint'

903

when 3..4, nil; 'integer'

904

when 5..8; 'bigint'

905

else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")

906

end

907

end

908

909

# Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

910

911

# PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and

912

# requires that the ORDER BY include the distinct column.

913

914

# distinct("posts.id", "posts.created_at desc")

915

def distinct(columns, order_by) #:nodoc:

916

return "DISTINCT #{columns}" if order_by.blank?

917

918

# Construct a clean list of column names from the ORDER BY clause, removing

919

# any ASC/DESC modifiers

920

order_columns = order_by.split(',').collect { |s| s.split.first }

921

order_columns.delete_if &:blank?

922

order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }

923

924

# Return a DISTINCT ON() clause that's distinct on the columns we want but includes

925

# all the required columns for the ORDER BY to work properly.

926

sql = "DISTINCT ON (#{columns}) #{columns}, "

927

sql << order_columns * ', '

928

end

929

930

# Returns an ORDER BY clause for the passed order option.

931

932

# PostgreSQL does not allow arbitrary ordering when using DISTINCT ON, so we work around this

933

# by wrapping the +sql+ string as a sub-select and ordering in that query.

934

def add_order_by_for_association_limiting!(sql, options) #:nodoc:

935

return sql if options[:order].blank?

936

937

order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)

938

order.map! { |s| 'DESC' if s =~ /\bdesc$/i }

939

order = order.zip((0...order.size).to_a).map { |s,i| "id_list.alias_#{i} #{s}" }.join(', ')

940

941

sql.replace "SELECT * FROM (#{sql}) AS id_list ORDER BY #{order}"

942

end

943

944

protected

945

# Returns the version of the connected PostgreSQL version.

946

def postgresql_version

947

@postgresql_version ||=

948

if @connection.respond_to?(:server_version)

949

@connection.server_version

950

else

951

# Mimic PGconn.server_version behavior

952

begin

953

query('SELECT version()')[0][0] =~ /PostgreSQL (\d+)\.(\d+)\.(\d+)/

954

($1.to_i * 10000) + ($2.to_i * 100) + $3.to_i

955

rescue

956

957

end

958

end

959

end

960

961

private

962

# The internal PostgreSQL identifier of the money data type.

963

MONEY_COLUMN_TYPE_OID = 790 #:nodoc:

964

965

# Connects to a PostgreSQL server and sets up the adapter depending on the

966

# connected server's characteristics.

967

def connect

968

@connection = PGconn.connect(*@connection_parameters)

969

PGconn.translate_results = false if PGconn.respond_to?(:translate_results=)

970

971

# Ignore async_exec and async_query when using postgres-pr.

972

@async = @config[:allow_concurrency] && @connection.respond_to?(:async_exec)

973

974

# Use escape string syntax if available. We cannot do this lazily when encountering

975

# the first string, because that could then break any transactions in progress.

976

# See: http://www.postgresql.org/docs/current/static/runtime-config-compatible.html

977

# If PostgreSQL doesn't know the standard_conforming_strings parameter then it doesn't

978

# support escape string syntax. Don't override the inherited quoted_string_prefix.

979

if supports_standard_conforming_strings?

980

self.class.instance_eval do

981

define_method(:quoted_string_prefix) { 'E' }

982

end

983

end

984

985

# Money type has a fixed precision of 10 in PostgreSQL 8.2 and below, and as of

986

# PostgreSQL 8.3 it has a fixed precision of 19. PostgreSQLColumn.extract_precision

987

# should know about this but can't detect it there, so deal with it here.

988

money_precision = (postgresql_version >= 80300) ? 19 : 10

989

PostgreSQLColumn.module_eval(<<-end_eval)

990

def extract_precision(sql_type) # def extract_precision(sql_type)

991

if sql_type =~ /^money$/ # if sql_type =~ /^money$/

992

#{money_precision} # 19

993

else # else

994

super # super

995

end # end

996

end # end

997

end_eval

998

999

configure_connection

1000

end

1001

1002

# Configures the encoding, verbosity, and schema search path of the connection.

1003

# This is called by #connect and should not be called manually.

1004

def configure_connection

1005

if @config[:encoding]

1006

if @connection.respond_to?(:set_client_encoding)

1007

@connection.set_client_encoding(@config[:encoding])

1008

else

1009

execute("SET client_encoding TO '#{@config[:encoding]}'")

1010

end

1011

end

1012

self.client_min_messages = @config[:min_messages] if @config[:min_messages]

1013

self.schema_search_path = @config[:schema_search_path] || @config[:schema_order]

1014

end

1015

1016

# Returns the current ID of a table's sequence.

1017

def last_insert_id(table, sequence_name) #:nodoc:

1018

Integer(select_value("SELECT currval('#{sequence_name}')"))

1019

end

1020

1021

# Executes a SELECT query and returns the results, performing any data type

1022

# conversions that are required to be performed here instead of in PostgreSQLColumn.

1023

def select(sql, name = nil)

1024

fields, rows = select_raw(sql, name)

1025

result = []

1026

for row in rows

1027

row_hash = {}

1028

fields.each_with_index do |f, i|

1029

row_hash[f] = row[i]

1030

end

1031

result << row_hash

1032

end

1033

result

1034

end

1035

1036

def select_raw(sql, name = nil)

1037

res = execute(sql, name)

1038

results = result_as_array(res)

1039

fields = []

1040

rows = []

1041

if res.ntuples > 0

1042

fields = res.fields

1043

results.each do |row|

1044

hashed_row = {}

1045

row.each_index do |cell_index|

1046

# If this is a money type column and there are any currency symbols,

1047

# then strip them off. Indeed it would be prettier to do this in

1048

# PostgreSQLColumn.string_to_decimal but would break form input

1049

# fields that call value_before_type_cast.

1050

if res.ftype(cell_index) == MONEY_COLUMN_TYPE_OID

1051

# Because money output is formatted according to the locale, there are two

1052

# cases to consider (note the decimal separators):

1053

# (1) $12,345,678.12

1054

# (2) $12.345.678,12

1055

case column = row[cell_index]

1056

when /^-?\D+[\d,]+\.\d{2}$/ # (1)

1057

row[cell_index] = column.gsub(/[^-\d\.]/, '')

1058

when /^-?\D+[\d\.]+,\d{2}$/ # (2)

1059

row[cell_index] = column.gsub(/[^-\d,]/, '').sub(/,/, '.')

1060

end

1061

end

1062

1063

hashed_row[fields[cell_index]] = column

1064

end

1065

rows << row

1066

end

1067

end

1068

res.clear

1069

return fields, rows

1070

end

1071

1072

# Returns the list of a table's column names, data types, and default values.

1073

1074

# The underlying query is roughly:

1075

# SELECT column.name, column.type, default.value

1076

# FROM column LEFT JOIN default

1077

# ON column.table_id = default.table_id

1078

# AND column.num = default.column_num

1079

# WHERE column.table_id = get_table_id('table_name')

1080

# AND column.num > 0

1081

# AND NOT column.is_dropped

1082

# ORDER BY column.num

1083

1084

# If the table name is not prefixed with a schema, the database will

1085

# take the first match from the schema search path.

1086

1087

# Query implementation notes:

1088

# - format_type includes the column size constraint, e.g. varchar(50)

1089

# - ::regclass is a function that gives the id for a table name

1090

def column_definitions(table_name) #:nodoc:

1091

query <<-end_sql

1092

SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull

1093

FROM pg_attribute a LEFT JOIN pg_attrdef d

1094

ON a.attrelid = d.adrelid AND a.attnum = d.adnum

1095

WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass

1096

AND a.attnum > 0 AND NOT a.attisdropped

1097

ORDER BY a.attnum

1098

end_sql

1099

end

1100

1101

def extract_pg_identifier_from_name(name)

1102

match_data = name[0,1] == '"' ? name.match(/\"([^\"]+)\"/) : name.match(/([^\.]+)/)

1103

1104

if match_data

1105

rest = name[match_data[0].length..-1]

1106

rest = rest[1..-1] if rest[0,1] == "."

1107

[match_data[1], (rest.length > 0 ? rest : nil)]

1108

end

1109

end

1110

end

1111

end

1112

end

1113

Older »