schema.rb not representative of migrations?

[pmackay]

Has schema.rb been updated to match all migrations? To take one example, this migration change

https://github.com/codeforamerica/ohana-api/blob/master/db/migrate/20140929221750_add_fields_to_organization.rb

doesnt match with the definition of the organization table:

  create_table "organizations", force: true do |t|
    t.text     "name"
    t.text     "urls"
    t.text     "slug"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

[monfresh]

The schema is maintained in structure.sql, not schema.rb, because some migrations can't be recreated by Rails.

To verify this, what you could have done was go to any of our recent migrations, click on the commit next to it, and then look at the changed files. You would have seen that structure.sql was one of them.

Please try to keep GitHub issues to legitimate bugs. I'm going to create a Google group so that people can help each other out and answer questions like these because it's not scalable for me to respond to everything since I'm only working on Ohana in my spare time.

[pmackay]

I really appreciate your responses and thanks for the clarification. Might be good to capture in a wiki page which I will start if can gather enough info. Would you also prefer to use a google group for feature suggestions? What purpose does schema.rb serve in that case?

[monfresh]

I think feature suggestions can stay here. A Wiki sounds great if you want to get that started. I don't think schema.rb serves a purpose anymore. It should be safe to remove, but I never did it probably because I wasn't sure if it would break anything.

[md5]

In @pmackay's defense, schema.rb was last updated on the same day as structure.sql, so it's reasonable to think it was still in use in some way. 👍 to removing it.

[monfresh]

Strange. I'm not sure why it was updated that one time. If you want to test that the installation instructions still work when you remove schema.rb, and then submit a PR, that would be fabuloso!

[pmackay]

@monfresh it would be super helpful if you could post any pointers to reasons why you needed to use structure.sql (and schema.rb wasnt sufficient). I could then try to take this forward.

[monfresh]

To test that the removal of schema.rb is safe, I would follow these steps:

1. Export your ohana_api_development DB so you have a backup: script/export_prod_db
2. Delete the dev DB: dropdb ohana_api_development
3. Clone the ohana-api repo in a new folder on your computer
4. Remove schema.rb
5. Run bin/setup
6. Verify that you can run the app, and do the things mentioned in the installation instructions.

The technical reasons for using structure.sql are not necessary to know to test this, but if you're curious, I would recommend reading the Rails Active Record Migrations guide, specifically the section about Schema dumping.

The Rails Guides are a great source of information, but sometimes you're not exactly sure which guide you want. In that case, there's always Google 😄 A search for "rails structure.sql" will most likely bring up the pertinent Rails Guide.

[pmackay]

@monfresh thanks for this!

Re the Rails guides, I had looked up some general info. In reference to this:

There is however a trade-off: db/schema.rb cannot express database specific items such as triggers, stored procedures or check constraints. While in a migration you can execute custom SQL statements, the schema dumper cannot reconstitute those statements from the database. If you are using features like this, then you should set the schema format to :sql.

I was wondering was what it specifically about the Ohana project that required using the :sql format instead of :ruby?

[monfresh]

When I'm trying to find my way around code that I'm not familiar with, I use a combination of GitHub's search, text editor search, and git log search. In this case, given that paragraph you quoted, here are some of the things I would try:

1. Search for "sql" in the ohana-api repo
   The first result shows you the migration that executes custom SQL. This is what powers the full-text search.
2. The Rails guide also mentions "triggers". If you search for "trigger", you'll also find that migration.
3. Search for structure.sql using git log -S structure.sql. This will show you the commits that contain that search term:

commit 52dd99fbe955490a6959b4404998b3984835ff36
Author: Moncef Belyamani <giter@done>
Date:   Fri May 9 20:45:50 2014 -0400

    Replace Elasticsearch with Postgres full-text search. Fixes #139

You can then visit that commit on GitHub by adding the first few characters of that commit's SHA to the end of https://github.com/codeforamerica/ohana-api/commit/:
52dd99
and then look through the file changes.

[monfresh]

Our API documentation is also a good resource. The Search Locations section explains how full-text search works when you use the keyword parameter, and links to the migration that sets up the tsvector column.

[monfresh]

For more information on Postgres full-text search, here are some resources that helped me implement it (in no particular order):

http://altoros.github.io/2013/implementing-and-improving-postgresql-fulltext-search/
http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/
http://shisaa.jp/postset/postgresql-full-text-search-part-1.html
http://railscasts.com/episodes/343-full-text-search-in-postgresql?view=asciicast
http://linuxgazette.net/164/sephton.html

Do you think the info in the past few comments is worth putting in a Wiki?