I want to put string and array data in sqlite3 in ruby on rails

Question

Hi I'm new at programming but I try to make my own service

I'm using cloud9 Ide, ruby on rails and sqlite3

Anyway I'll parse data of a dictionary and I'm about to design database like this(just for example)

        [col-1] [col-2] [col-3]
[row-1]  fruit   apple  [a,p,p,l,e]
[row-2]  fruit  kiwi    [k,i,w,i]
   ...
[row-50] flower  lily   [l,i,l,y]
[row-51] flower  rose   [r,o,s,e]
  ...

3 column and thousands of rows

To give you more details, when an user types "fruit" in text area I want to show word list from "apple" to "kiwi"!!

I learned about storing 'string' that only users submit like this

class CreateFans < ActiveRecord::Migration
  def change
    create_table :fans do |t|

      t.string :username

      t.timestamps null: false
    end
  end
end

But I really don't know how to store my own data
I want to know how to add column and rows and storing local data, not user input!

Actually, I studied yesterday reading .xlsx file and show in ruby on rails through gem 'roo' but I don't know how to use this properly in database. and I want to know there is alternatives...

Thanks for reading my question and I appreciate if you give me advices :)

Answer 1

You can add columns to databases with migrations.

The columns don't have to be only from user input.

for example you could have a migration...

class CreateMyWord < ActiveRecord::Migration
  def change
    create_table :my_words do |t|
      t.string :genre
      t.string :word
      t.string :letters
      t.timestamps null: false
    end
  end
end

When you define your model you specify that the attribute letters is actually an array...

class MyWord < ActiveRecord::Base
  serialize :letters
end

serialize will automatically convert an array to a string representation when storing the record, and will automatically convert it back when retrieving the record.

You can then populate the table yourself in your seeds.db file which you can execute with the command rake db:seed

the seeds db would possibly look like...

my_initial_words = [
                    ['fruit', 'apple', ['a','p','p','l','e'],
                    ['fruit', 'kiwi', ['k','i', 'w', 'i'],
                    ...
                   ]

my_iniital_words.each do |word_data|
  MyWord.create(genre: word_data[0], word: word_data[1], letters: word_data[2])
end

Note that if the letters of the word always match the word, you don't really need a column letters in the database, just have a method in the model that creates the letters array for you when you need it.

class MyWord < ActiveRecord::Base
  def letters
    word.split('')
  end
end