Soluzione Ruby generica per SQLite3 “LIKE” o PostgreSQL “ILIKE”?

Sto usando SQLite3 per lo sviluppo e PostgreSQL per l’implementazione. Tuttavia, sto affrontando il seguente problema:

La mia semplice ricerca con SQLite3 :

 def self.search(search) if search find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"]) else find(:all) end end 

Tuttavia, non funziona con PostgreSQL , e ho bisogno di sostituire il LIKE per ILIKE per risolvere il problema:

 def self.search(search) if search find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"]) else find(:all) end end 

Esiste un “modo Ruby” per effettuare queste ricerche su qualsiasi database?

EDIT – basato sulle tue risposte Non credo che troverò una soluzione generica per questo.

Ho seguito il tutorial di Ruby on Rails: Learn Rails by Example – di Michael Hartl , in cui l’ultimo Gemfile mostra entrambi i database … beh, deludente …

La radice del problema si trova qui:

Sto usando SQLite3 per lo sviluppo e PostgreSQL per l’implementazione.

Questa è una ctriggers idea ™. Continuerai a incorrere in incompatibilità – o peggio: non realizzarne alcuni finché non si fa il danno.
Utilizza lo stesso RDBMS (PostgreSQL) per lo sviluppo e la produzione e risparmia inutili problemi.


Mentre sei bloccato con il tuo sfortunato setup, c’è una semplice soluzione :

 lower(style) LIKE lower(?) 

Funziona su entrambe le piattaforms allo stesso modo.

  • È ansible rilasciare la mano destra lower() , se si fornisce un modello di ricerca minuscolo.

  • In SQLite standard lower(X) si piegano solo lettere ASCII. Per altro, cito il capitolo Funzioni di base nel manuale SQLite :

    La funzione inferiore (X) restituisce una copia della stringa X con tutti i caratteri ASCII convertiti in minuscolo. La funzione predefinita lower () predefinita funziona solo per i caratteri ASCII. Per eseguire conversioni del caso su caratteri non ASCII, caricare l’estensione ICU .

    Enfasi mia.

  • PostgreSQL lower(X) funziona immediatamente con UTF-8.


Come positivo effetto collaterale, puoi velocizzare quella query in PostgreSQL con un indice sull’espressione lower(style) , che sarà più veloce dell’utilizzo di ILIKE e un indice di base sullo style .

Inoltre, dal momento che PostgreSQL 9.1 è ansible utilizzare un indice GIN o GIST con l’ estensione pg_trgm per velocizzare qualsiasi query LIKE e ILIKE – i trigram non fanno distinzione tra maiuscole e minuscole. Istruzioni dettagliate e collegamenti in questa risposta correlata:

  • Stringhe UTF-8 simili per il campo di completamento automatico

Penso che Arel sia il modo migliore per risolvere questo problema. Viene utilizzato da Rails per la registrazione triggers ed è indipendente dal database. Il tuo codice funzionerà allo stesso modo in sqlite3 o postgres che sembra adattarsi alla tua situazione. Usando il metodo match si passa automaticamente a un ilike in un ambiente postgres. esempio:

 users=User.arel_table User.where(users[:style].matches("%#{search}%").or(users[:construction].matches("%#{search}%"))) 

Puoi ottenere maggiori informazioni dal github: https://github.com/rails/arel/

No, non esiste un “modo ruby” per cercare un database – Ruby on Rails (in particolare ActiveRecord ) contiene metodi di supporto per eseguire operazioni CRUD su RDB supportati da ActiveRecord ma non c’è molto meglio di un modo di cercare usando LIKE, quindi gli esempi hai fornito.

La sezione del documento Rails relativa a questa discussione sarebbe ActiveRecord :: FinderMethods .

Come nota a margine, invece di fare find(:all) puoi semplicemente fare tutto .

I documenti Rails utilizzano la stessa syntax utilizzata per le istruzioni LIKE, ad esempio:

 Person.exists?(['name LIKE ?', "%#{query}%"]) 

Utilizzare il metodo sopra è perfettamente sicuro.

Il motivo per cui affermazioni come quella riportata di seguito non sono sicure è perché la stringa in where viene passata direttamente nella query del database senza alcuna sanitizzazione, che lascia il database aperto allo sfruttamento (ad esempio un semplice apostrofo in params[:first_name] potrebbe rovinare l’intera query e lascia il tuo database vulnerabile, in particolare SQL injection). Nell’esempio sopra, ActiveRecord può disinfettare i parametri che stai passando nella query.

 Client.where("first_name LIKE '%#{params[:first_name]}%'") 

Sfortunatamente non penso che troverai una buona soluzione a questo. L’unica altra syntax disponibile per te, invece di: condizioni, è usare una clausola .where:

 where(["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"]) 

Sfortunatamente, come probabilmente hai capito, questa syntax alternativa si troverà nello stesso identico problema. Questo è solo uno dei fastidi che incontrerai nell’avere un ambiente di sviluppo sostanzialmente diverso dal tuo ambiente di produzione – ci sono anche altre differenze abbastanza sostanziali tra SQLite e PostgresSQL. Ti consiglio di installare Postgres sul tuo computer di sviluppo e di usarlo. Renderà lo sviluppo molto più semplice e renderà il tuo codice molto più pulito.

Mentre non è una buona pratica utilizzare diversi database in produzione e sviluppo, è comunque un buon caso utilizzare la gem squeel : https://github.com/ernie/squeel/

Con esso, è ansible scrivere le query con un DSL che è facile e discutibilmente molto più pulito e leggibile rispetto a raw sql e la gem gestirà la sua traduzione in SQL specifica per l’RDBMS utilizzato.

Ryan Bates ha un buon video a riguardo: http://railscasts.com/episodes/354-squeel

Stavo affrontando lo stesso problema una volta, ecco la mia soluzione:

Ho scritto un po ‘di lib che rende ansible usare la funzione per ogni database:

 class AdapterSpecific class << self def like_case_insensitive case ActiveRecord::Base.connection.adapter_name when 'PostgreSQL' 'ILIKE' else 'LIKE' end end def random #something end end 

Per usarlo nel tuo modello:

 def self.search(search) if search find(:all, :conditions => ["style #{AdapterSpecific.like_case_insensitive} :query OR construction #{AdapterSpecific.like_case_insensitive} :query", {:query => "%#{search}%"}]) else find(:all) end end 

Da quando sono stato scritto, ho migrato il database di distribuzione a Postgres poiché questa è una configurazione molto migliore per diversi motivi (vedere altre risposte).

Potresti anche pensare di usare la ricerca full-text per Postgres che renderà la tua ricerca testuale molto più efficiente, vedi texticle per un'implementazione di base o pg_search se hai bisogno di più personalizzazioni.

La ricerca usando LIKE può essere dolorosa sul database. Certamente non userà un indice che può essere abbastanza proibitivo.

Una risposta più lunga: consiglierei di utilizzare Postgres in fase di sviluppo (ammaraggio sqlite3) e di avere un indice full-text su tutti i tuoi campi di ricerca style, construction tramite il tipo di tsvector Postgres.

Le ricerche full-text in Postgres quando si utilizza un indice sono molto veloci.