...my understanding of database indexes. As a small bonus, it includes a few helpful SQL oneliners like these two: Detecting unused indexes Detecting duplicate indexes Warning Do not run random...
...radius of a location is a bit trickier: def close_destinations(latitude, longitude) distance_sql = Graticule::Distance::Spherical.to_sql(:latitude => latitude, :longitude => longitude, :units => :kilometers) Destination.all(:conditions => [ "#{distance_sql...
...find every Destination that is close to the given latitude and longitude: Using to_sql will give you an SQL snippet to compute the distance from the given geo point...
...will throw an error to one of the offending threads. This error states the SQL statement that this thread was currently waiting for, and that tried to acquire one of...
Solutions: make your queries comply with it, if possible change the sql mode in the config/database.yml (and that of all your colleagues, staging and production servers!):
...adapter: mysql2 ... variables: sql_mode: TRADITIONAL (Be aware, that this solution disables the only_full_group_by setting globally and MySQL will select those values arbitrarily again)
Site.joins(:user).where(:users => { :name => 'Bruce' }) This scope will expand to a SQL query like this: SELECT * FROM sites INNER JOIN users WHERE sites.user_id = sites.id AND users.name...
...where(:users => { :name => 'Bruce' }).to_id_query #to_id_query will immediately run an SQL query where it collects all the IDs that match your scope: SELECT sites.id FROM sites...
...something else -- your IDE may even be aware of it, for example RubyMine understands <<-SQL, <<-HTML, <<-XML, <<-JSON, etc and highlights correctly. Please note: Your string will end with a...
...scope of Contact records: # We start by building a scope of all contacts. # No SQL query is made. all_contacts = Contact.all # => ActiveRecord::Relation # Now we filter the scope to only...
...contain contacts with "gmail" in either :name or :email column. # Again, no SQL query is made. gmail_contacts = ContactFilter.new.filter(all_contacts, 'gmail') # => ActiveRecord::Relation # Inspect the filtered scope. gmail_contacts.to_sql...
...order does not work with complex sorting constraints and may even silently create malformed SQL for rails < 5. Take a look at this query which orders by the maximum of...
Page.order('GREATEST(pages.published_from_de, pages.published_from_en) DESC').to_sql # => SELECT "pages".* FROM "pages" ORDER BY GREATEST(pages.published_from_de, pages.published_from_en) DESC Rails 4
...conditions as a hash (as seen above). To be on the safe side, use SQL fragments which are never overridden: >> Article.available.scoped(:conditions => [ 'articles.state = ?', 'deleted' ]) # SELECT * FROM `articles` WHERE ((articles.state = 'deleted...
...pgAdmin as a GUI. While you can do most things just like on an SQL console, you can use it to display EXPLAIN results in a more human-readable way...
...pgAdmin, connect to your server Pick a database from the left pane Click the "SQL" icon in the toolbar, or press Ctrl+E to open the query tool.
...dump in the custom format you can can view the text format dump (plain SQL statements). PostgreSQL 12 and newer Write it as a textdump to a file:
...from and to InnoDB tables while completely bypassing the parsing and planning overhead of SQL. This could potentially be very awesome because we would then have a distributed key/value store...
...that stores data in a way it can also be queried with complex SQL queries...
...error when using Graticule? Numerical argument out of domain - acos Similarly to the to_sql problem for some edge cases, Graticule::Distance::Spherical.distance (and possibly those of Graticule's other...
...the same coordinates (applies only for some). How to fix So, similar to the SQL fix, we just force the value we compute the arc cosine of into...
...db:migrate and stash you changes in the migration files. 2. Temporally change the sql mode mysql> SELECT @@global.sql_mode; +--------------------------------------------+ | @@global.sql_mode | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+
The SQL code generated by Graticule's spherical distance computation is insufficient and can lead to NULL distances in edge cases. The reason for this is that MySQL is performing...
...it. Put the following into an initializer like config/initializers/graticule_spherical_to_sql_without_nulls.rb which patches Graticule's to_sql method: Graticule::Distance::Spherical.class_eval do def self.to_sql(options) options = { :units => :miles, :latitude_column...
It's generally not trivial to change a datetime's seconds, minutes, etc in SQL. Here is how it works when speaking PostgreSQL. Consider you have a timestamp column whose...
...be the one who messed up permissions on a shared database cluster. The following SQL code will find all table names and execute a DROP TABLE statement for each.
...this case means that all those commands are processed step by step, causing 4 SQL statements to change the table. In turn, your database needs to modify the table structure...
PostgreSQL supports the SQL OVERLAPS operator. You can use it to test if two date ranges overlap: => SELECT ('2001-02-16'::date, '2001-12-21'::date) OVERLAPS...
It's every developer's nightmare: SQL queries that get large and unwieldy. This can happen fairly quickly with the addition of multiple joins, a subquery and some complicated filtering...
When you load a record with find options that have SQL fragments in :select or :joins, ActiveRecord will make that record read-only. This is a protective measure by Rails...
...root, children, siblings, descendants) and all of them can be fetched in a single sql query. Additional features are STI support, named_scopes, depth caching, depth constraints, easy migration from...
tmp/* storage/* db/*.sqlite3 db/schema.rb db/structure.sql public/system .project .idea/ public/javascripts/all* public/stylesheets/all* public/stylesheets/*.css config/database.yml *~ *#* .#* .DS_Store webrat-*.html capybara-*.html rerun.txt coverage.data coverage/* dump_for_download.dump .~lock.* .*.swp C:\\nppdf32Log\\debuglog.txt
...against many common attacks: cross site scripting (XSS), cross site request forgery (CSRF) and SQL injection. Core members are knowledgeable and genuinely concerned with security. However, there are places where...