Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy way to do this (instead of doing it manually)? My client does not want to use Rails migrations and uses the Workbench as the authorative database schema so I'm stuck with doing it in Workbench.

share|improve this question

migrated from stackoverflow.com May 25 '13 at 8:58

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 2 down vote accepted

You can use this Python Script in the Scripting Shell of MySQL Workbench. It is an improved version of the script found here: http://mysqlworkbench.org/2012/06/mysql-workbench-script-for-adding-columns-to-all-tables-in-a-model/

# define your columns and types here
columns_types = {"created_at" : "DATETIME", "updated_at" : "DATETIME"}

# tables you want to skip
skip_tables = ["main_defs", "main_menu_items", "delayed_jobs", "delayed_job_workers", "delayed_job_logs"]



# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]

# iterate through all tables
for table in schema.tables:

    # skip the current table if it is in skip_tables
    if table.name in skip_tables:
        continue

    # iterate through all columns to be added
    for column_name, type in columns_types.items():

        # skip this column_name if there is already a column with this name
        column_names = [x.name for x in table.columns]
        if column_name in column_names:
            continue

        # create a new column object and set its name
        column = grt.classes.db_mysql_Column()
        column.name = column_name
        # add it to the table
        table.addColumn(column)
        # set the datatype of the column
        column.setParseType(type, None)
share|improve this answer

In ruby on rails, You don't have to create created_at and updated_at columns separately. when you run the migrations t.timestamps written in your migration file creates the created_at and updated_at columns in your table. e.g.

class CreateStudents < ActiveRecord::Migration
  def change
    create_table :students do |t|
      t.string :name
      t.timestamps
    end
  end
end

This will create a table students with columns id, name, created_at and updated_at.

share|improve this answer
    
Yes, I know all that, but it's beside the point: My client wants MySQL Workbench as the authorative source for the database schema. Therefore I need to make the adjustments in Workbench and generate the schema from that. The client does not use rails migrations. –  nerdinand May 24 '13 at 9:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.