王筝的博客
ruby学习

 

  def search
    @videos = Video.where("videoid LIKE ? or title LIKE ?", "%#{params[:q]}%", "%#{params[:q]}%")

    render :index
  end 
使用 hash 的写法
  def search
    @videos = Video.where("videoid LIKE :q or title LIKE :q", q: "%#{params[:q]}%")

    render :index
  end

 

https://gist.github.com/stream7/1069589

 

:limit        Numeric Type    Column Size    Max value
1             tinyint           1 byte        127
2             smallint          2 bytes       32767
3             mediumint         3 byte        8388607
nil, 4, 11    int(11)           4 byte        2147483647
5..8          bigint            8 byte        9223372036854775807

 

关于sequence 字段limit: 2 是两个字节,16 位,2 的 15 次方是 32767

class CreateTableSubjections < ActiveRecord::Migration[5.0]
  def change
    create_table :table_subjections do |t| 
      t.belongs_to :subject, index: true
      t.belongs_to :video, index: true
      t.string :title, limit: 40
      t.string :sub_title, limit: 40
      t.string :description, limit: 500
      t.integer :status, limit: 1
      t.integer :sequence, limit: 2

      t.timestamps
    end 
  end 
end

 

对于多态关联的表

class CreateSubjections < ActiveRecord::Migration[5.0]
  def change
    create_table :subjections do |t| 
      t.belongs_to :subject, index: true
      t.string :title, limit: 40
      t.string :sub_title, limit: 40
      t.string :description, limit: 500 
      t.string :subjectable_type, limit: 20
      t.integer :subjectable_id
      t.integer :status, limit: 1
      t.integer :sequence, limit: 2

      t.timestamps
    end 

    add_index :subjections, [:subjectable_type, :subjectable_id]
  end 
end

t.integer :subjectable_id 不需要设置limit,因为这个是用来做外键的,存储的是数据库的自增主键

 

http://api.rubyonrails.org/classes/ActiveRecord/Enum.html

 

新的项目中有一个字段是展示类型,可以用下拉框去做,用string存储具体的类型字段。

尝试了一下把展示类型修改为integer,用Rails enum枚举来做。

使用枚举(整型)来存储类似于下拉框选择的这类的值, 比起直接存储类型的字符串,能减少表中存储字段的大小。

 

建表语句如下

class CreateSubjects < ActiveRecord::Migration[5.0]
  def change
    create_table :subjects do |t| 
      t.string :title, limit: 40
      t.string :sub_title, limit: 40
      t.string :categories, limit: 30
      t.string :description, limit: 500 
      t.integer :status, limit: 1
      t.integer :type, limit: 1
      t.integer :use_default_image, limit: 1
      t.integer :has_ranking_list, limit: 1

      t.timestamps
    end 
  end 
end

model中定义了enum的字段值。

class Subject < ApplicationRecord

  TYPES = [ :'变化标题描述', :'固定标题描述', :'播放器专题', :'无文案专题' ]
  enum type: TYPES

  self.inheritance_column = '_disable'
end

在console里修改当前type的值,rails会自动显示成对应的字符串

[6] pry(main)> Subject.last.update_attribute :type, 2
  Subject Load (0.2ms)  SELECT  `subjects`.* FROM `subjects` ORDER BY `subjects`.`id` DESC LIMIT 1
   (0.2ms)  BEGIN
  SQL (0.4ms)  UPDATE `subjects` SET `type` = 2, `updated_at` = '2016-09-13 06:06:49' WHERE `subjects`.`id` = 3
  SQL (14.3ms)  INSERT INTO `versions` (`item_type`, `item_id`, `event`, `object`, `created_at`, `object_changes`) VALUES ('Subject', 3, 'update', '{\"id\":3,\"title\":\"谢谢\",\"sub_title\":\"1\",\"categories\":\"1\",\"description\":\"1\",\"status\":null,\"type\":1,\"use_default_image\":1,\"has_ranking_list\":1,\"created_at\":\"2016-09-13T04:35:23.000Z\",\"updated_at\":\"2016-09-13T04:35:23.000Z\"}', '2016-09-13 06:06:49', '{\"type\":[1,2],\"updated_at\":[\"2016-09-13T04:35:23.000Z\",\"2016-09-13T06:06:49.000Z\"]}')
   (54.0ms)  COMMIT
=> true
[7] pry(main)> Subject.last
  Subject Load (0.4ms)  SELECT  `subjects`.* FROM `subjects` ORDER BY `subjects`.`id` DESC LIMIT 1
=> #<Subject:0x00558622d04d98
 id: 3,
 title: "谢谢",
 sub_title: "1",
 categories: "1",
 description: "1",
 status: nil,
 type: "播放器专题",
 use_default_image: 1,
 has_ranking_list: 1,
 created_at: Tue, 13 Sep 2016 12:35:23 CST +08:00,
 updated_at: Tue, 13 Sep 2016 14:06:49 CST +08:00>

而直接查询mysql会发现,这个字段里存的是2.

$ bundle exec rails db -p
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from subjects;
+----+--------+-----------+------------+-------------+--------+------+-------------------+------------------+---------------------+---------------------+
| id | title  | sub_title | categories | description | status | type | use_default_image | has_ranking_list | created_at          | updated_at          |
+----+--------+-----------+------------+-------------+--------+------+-------------------+------------------+---------------------+---------------------+
|  3 | 谢谢   | 1         | 1          | 1           |   NULL |    2 |                 1 |                1 | 2016-09-13 04:35:23 | 2016-09-13 06:06:49 |
+----+--------+-----------+------------+-------------+--------+------+-------------------+------------------+---------------------+---------------------+
1 row in set (0.00 sec)

在_form中的代码如下

  <div class="form-group">
    <%= f.label :type, class: 'col-sm-2 control-label' %>

    <div class="col-sm-4">
      <%= f.select :type, Subject::TYPES %>
    </div>
  </div>

在index和show页面直接读取数据就ok了

<%= subject.type %>

 

Mysql2::Error: Cannot delete or update a parent row: a foreign key constraint fails (`todo_list_development`.`todo_items`, CONSTRAINT `fk_rails_d913ce1c56` FOREIGN KEY (`todo_list_id`) REFERENCES `todo_lists` (`id`)): DELETE FROM `todo_lists` WHERE `todo_lists`.`id` = 4

http://api.rubyonrails.org/

class Post < ActiveRecord::Base
  # because we store "Post" in attachable_type now dependent: :destroy will work
  has_many :assets, as: :attachable, dependent: :destroy
end
$ RAILS_ENV=production bundle exec rails c

 

irb(main):008:0> r = ActiveRecord::Base.connection.execute 'show create database ott_remote_cms_production'
=> #<Mysql2::Result:0x002b338fe61d68 @query_options={:as=>:array, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:utc, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>214125, :cast=>true, :default_file=>nil, :default_group=>nil, :adapter=>"mysql2", :encoding=>"utf8", :pool=>5, :username=>"", :password=>"", :host=>"", :port=>3306, :database=>"cms_production", :flags=>2}>
irb(main):009:0> r.each {|row| puts row }
ott_remote_cms_production
CREATE DATABASE `cms_production` /*!40100 DEFAULT CHARACTER SET utf8 */
=> [["cms_production", "CREATE DATABASE `ott_remote_cms_production` /*!40100 DEFAULT CHARACTER SET utf8 */"]]
> r = ActiveRecord::Base.connection.execute 'show tables'
=> #<Mysql2::Result:0x002b338fe01aa8 @query_options={:as=>:array, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:utc, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>214125, :cast=>true, :default_file=>nil, :default_group=>nil, :adapter=>"mysql2", :encoding=>"utf8", :pool=>5, :username=>"", :password=>"", :host=>"", :port=>3306, :database=>"cms_production", :flags=>2}>
irb(main):016:0> r.each {|row| puts row }
=> []
irb(main):017:0> r = ActiveRecord::Base.connection.execute 'show variables like "%char%"';
irb(main):018:0* r.each {|row| puts row }
character_set_client
utf8
character_set_connection
utf8
character_set_database
utf8
character_set_filesystem
binary
character_set_results
utf8
character_set_server
utf8
character_set_system
utf8
character_sets_dir
/usr/share/mysql/charsets/
=> [["character_set_client", "utf8"], ["character_set_connection", "utf8"], ["character_set_database", "utf8"], ["character_set_filesystem", "binary"], ["character_set_results", "utf8"], ["character_set_server", "utf8"], ["character_set_system", "utf8"], ["character_sets_dir", "/usr/share/mysql/charsets/"]]

 

$rails db -p

how-to-use-rake-tasks-to-generate-migration-sql

Rakefile文件里有load_tasks的方法

http://api.rubyonrails.org/

Load Rake, railties tasks and invoke the registered hooks. Check Rails::Railtie.rake_tasks for more info.

Source: hide | on GitHub

# File railties/lib/rails/engine.rb, line 451
def load_tasks(app=self)
  require "rake"
  run_tasks_blocks(app)
  self
end

 

rake_tasks(&block) 
If you try to define a set of rake tasks on the instance, 
these will get passed up to the rake tasks defined on the application's class.

Source: hide | on GitHub

# File railties/lib/rails/application.rb, line 270
def rake_tasks(&block)
  self.class.rake_tasks(&block)
end

相关文档

http://guides.rubyonrails.org/command_line.html

https://github.com/ruby/rake

http://eewang.github.io/blog/2013/07/29/how-to-use-rake-tasks-to-generate-migration-sql/

开始写代码了。

在lib/tasks/文件夹新建migrate.rake

# 根据下面的博客做了一点点修改
# http://eewang.github.io/blog/2013/07/29/how-to-use-rake-tasks-to-generate-migration-sql/
namespace :db do               
  [ :migrate, :rollback ].each do |n|
    namespace n do |migration_task| 
      # original_task = migration_task.instance_variable_get("@scope").join ":"

      [:with_sql, :to_sql ].each do |t|
        desc "Run migration, and generated SQL" if t == :with_sql
        desc "Generate migration SQL" if t == :to_sql
        task t => :environment do |sql_task| 
          original_task = sql_task.name.sub(":#{t}", '')

          case original_task   
          when 'db:migrate'    
            filename = 'upgrade.sql'        
          when 'db:rollback'   
            filename = 'rollback.sql'       
          else                 
            raise "unkown migration type #{original_task}"
          end                  
  
          ActiveRecord::Base.connection.class.class_eval do
            # alias the adapter's execute for later use
            alias :old_execute :execute  

  
            SQL_FILENAME = filename         
            RUN_SQL = sql_task.name.ends_with?("with_sql")

            # define our own execute        
            def execute(sql, name = nil)    
              # check for some DDL and DML statements
              if /^(create|alter|drop|insert|delete|update)/i.match sql
                File.open(SQL_FILENAME, 'a') { |f| f.puts "#{sql};\n" } 
                old_execute sql, name if RUN_SQL
              else
                # pass everything else to the aliased execute
                old_execute sql, name
              end
            end

          end

          # create or delete content of migration.sql
          File.open(SQL_FILENAME, 'w') { |f| f.puts "-- Script created @ #{Time.now}" }

          # invoke the normal migration procedure now
          Rake::Task[original_task].invoke

          puts "Ran #{original_task} and wrote sql to #{filename}"
        end
      end
    end
  end
end

这时候来执行$ rake -T db

可以看到新增的功能

rake db:create              # Creates the database from DATABASE_URL or config/database.yml for the current RAILS_ENV (use db:create:all t...
rake db:drop                # Drops the database from DATABASE_URL or config/database.yml for the current RAILS_ENV (use db:drop:all to dr...
rake db:fixtures:load       # Load fixtures into the current environment's database
rake db:migrate             # Migrate the database (options: VERSION=x, VERBOSE=false, SCOPE=blog)
rake db:migrate:status      # Display status of migrations
rake db:migrate:to_sql      # Generate migration SQL
rake db:migrate:with_sql    # Run migration, and generated SQL
rake db:rollback            # Rolls the schema back to the previous version (specify steps w/ STEP=n)
rake db:rollback:to_sql     # Generate migration SQL
rake db:rollback:with_sql   # Run migration, and generated SQL

 

$ mysqldump -uroot -p ott_remote_cms_development > back.sql

$ rake db:drop

$ rake db:create

$ rake db:migrate:with_sql

$ mysql -uroot -p ott_remote_cms_development < back.sql

$ cat upgrade.sql

把sql发给dba就可以了,确实太麻烦了。

 

原因是这样的,运维不开放正式环境数据库的alter权限,所以每次都要给他们把sql语句发过去,

由他们来操作。

https://github.com/capistrano/rails

Require everything (bundler, rails/assets and rails/migrations):

# Capfile
require 'capistrano/rails'
Or require just what you need manually:

# Capfile
require 'capistrano/bundler' # Rails needs Bundler, right?
require 'capistrano/rails/assets'
require 'capistrano/rails/migrations'

 

参考的是https://github.com/capistrano/rvm/issues/49

Using the task pattern described before, capistrano/rvm gets required after capistrano/bundler. This makes capistrano use the command bundle exec ruby --version. This command would fail on the server when it is run inside a directory without a Gemfile.

There are two ways to make this work:

1) Avoid using bundle exec for all ruby commands:

Capfile

task :require_rvm do
  require 'capistrano/rvm'
end
task 'staging' => [:require_rvm]

require 'capistrano/bundler'
config/deploy/staging.rb

set :bundle_bins, %w(gem rake rails)
2) Require bundler the same way we require rvm:

Capfile

task :require_rvm do
  require 'capistrano/rvm'end

task :require_bundler do
  require 'capistrano/bundler'
end

task 'staging' => [:require_rvm, :require_bundler]
task 'production' => [:require_bundler]

 

 

以下是最终修改后的Capfile

task :require_rails do
  require 'capistrano/rails'
end
# require 'capistrano/rvm'
require 'capistrano/rbenv'
# require 'capistrano/chruby'
task :require_rails_without_migrations do
  require 'capistrano/bundler'
  require 'capistrano/rails/assets'
end
# require 'capistrano/rails/migrations'
# require 'capistrano/passenger'

task :staging => [:require_rails]
task :production => [:require_rails_without_migrations]

 

http://stackoverflow.com/questions/4307411/how-to-express-a-not-in-query-with-activerecord-rails

I'm using this:

Topic.where('id NOT IN (?)',actions)
Where actions is an array with: [1,2,3,4,5]

Edit:

For Rails 4 notation:

Article.where.not(title: ['Rails 3', 'Rails 5'])

需求是 v1.2以前版本(v1.2不用过滤)的接口中过滤掉电影、电视剧、动漫、综艺以外的频道

两种方法都是好用的

    if params[:version] < "1.2"
      arr = ["电影","电视剧","综艺","动漫"]
      #default_navi_channels = CmsChannel.where(title: arr).where('state = 1 and channel_type = ?' ,'cibn_navi_channel').order('position asc')
      default_navi_channels = CmsChannel.where('title IN (?)', arr).where('state = 1 and channel_type = ?' ,'cibn_navi_channel').order('position
    end

 

 

查询了大神的经验贴

http://stackoverflow.com/questions/19078044/disable-activerecord-for-rails-4

http://stackoverflow.com/questions/821251/how-to-configure-ruby-on-rails-with-no-database

其中点赞最多的也是我用的方法

If you are creating a new application, you can use -O to skip ActiveRecord:

rails new my_app -O
For existing applications:

1. Remove database adapter gems from your Gemfile (mysql2, sqlite3, etc.)

2. Change your config/application.rb

Remove require 'rails/all line and require frameworks you want to use, for example:

require "action_controller/railtie"
require "action_mailer/railtie"
require "sprockets/railtie"
require "rails/test_unit/railtie"
3. Delete your config/database.yml file, db/schema.rb and migrations (if any)

4. Delete migration check in test/test_helper.rb

5. Delete any ActiveRecord configuration from your config/environments files (this is what is causing your error)


This is all you need to do for an empty Rails app. If you run into problems caused by your existing code, stack trace should give you sufficient information on what you need to change. You might for example have some ActiveRecord configuration in your initializers.

6.删除controller里对Model的调用,把model里对ActiveRecord的依赖也删除