Database conversion

Following on the heels of my earlier entry about db_structure_dump, I’ve created another small useful Ruby script.

As I said before, my production databse is Oracle 10g and so is my own development database - but the development databases of the others helping out are MySQL. Now that I can dump the CREATE TABLE statements for my Oracle databases I need some way to convert back and forth between MySQL and Oracle syntax – since both aren’t really standard. Or at least I need to be able to dump my development oracle DB to SQL and then convert it to MySQL for the others.

Below is the hack I created that will take an Oracle database’s structure (or  sqlite, odbc, mssql and mysql)and convert it to standard SQL, then to MySQL syntax(since MySQL can’t handle VARCHAR’s longer than 255, for intance).

The code is not generic enough to be useful for everyone, but should give a good start. Watch out if you:

• Don’t use ID as your primary key. This is assumed right now, but a smarter script could check for keys.
• Actually use NUMBER in Oracle as a DECIMAL (and want to retain that in MySQL, rather than have it converted to INTEGER).
• Have join tables (since usually you won’t use ID as the primary key, you’ll have to manually fix the primary key).

class SQLStandardizer

def initialize(type, source)
    @data_type, @data = type, source
    init_substitutions
  end     
    
  # Convert data to compatible SQL
  def convert
    replacements = @subst[@data_type]
    new_data = @data
    replacements.each do |original, replacer|
      new_data.gsub!(original, replacer)
    end
    new_data
  end    
  
   # Initialize the substitution array
  private
  def init_substitutions
    @subst = {}
    @subst['mysql'] = {"int"=>"INTEGER","dec"=>"DECIMAL"}
    @subst['oracle'] = {"BFILE"=>"BLOB",
 				"CLOB"=>"TEXT",
 				"DATE"=>"DATETIME",
 				"LONG"=>"BIGINT",
 				"LONG RAW"=>"BIGINT",
 				"NCHAR"=>"CHAR",
 				"NCLOB"=>"TEXT",
 				"NUMBER"=>"DECIMAL",
 				"NVARCHAR2"=>"VARCHAR",
 				"RAW"=>"BLOB",
 				"VARCHAR2"=>"VARCHAR",
 				"DOUBLE PRECISION"=>"DECIMAL",
 				"\r\nLOGGING"=>"",
 				"\r\nNOCACHE"=>"",
 				"\r\nNOPARALLEL"=>"",
 				" BYTE"=>""}
    @subst['odbc'] = {"LONGCHAR"=>"BLOB"}
    @subst['sqlite'] = {"INTEGER(10)"=>"INTEGER"}
    @subst['mssql'] = {"nvarchar"=>"VARCHAR",
				"nchar"=>"CHAR",
				"int identity"=>"INTEGER",
				"ntext"=>"TEXT",
				"int"=>"INTEGER",
				"image"=>"LONGBLOB",
				"money"=>"FLOAT" }
  end
end

class DBSpecializer
  def initialize(type, source)
    @data_type, @data = type, source
  end
  
  # Right now we can only convert to MySQL
  def convert
    return @data if @data_type != 'mysql'
    new_data = @data.gsub(/VARCHAR\((\d+)\)/) do |match|
      length = $1.to_i
      if length > 255
        'TEXT'
      else
        "VARCHAR(#{length})"
      end
    end
    # We don't really use decimals
    new_data.gsub!('DECIMAL', 'INTEGER')
    # Make primary keys auto-incremented
    new_data.gsub!(' ID INTEGER NOT NULL', ' ID INTEGER NOT NULL AUTO_INCREMENT')
    # Set up primary key and initial auto-increment value
    # FIXME What if our primary key is not ID?
    new_data.gsub!(');', ",\n PRIMARY KEY (`id`)\n) AUTO_INCREMENT=1;")
    new_data
  end
end


if __FILE__ == $0
  from = 'oracle'
  to = 'mysql'
  source_file = 'production_structure.sql'
  output_file = "converted_#{source_file}"
  source = open(source_file) do |f|
    f.readlines.join
  end
  # Convert from oracle to mysql
  standardized = SQLStandardizer.new(from, source).convert
  converted = DBSpecializer.new(to, standardized).convert
  open(output_file, 'w') do |f|
    f << converted
  end
end

Open Source and the Implications of the Long Tail

Chris Anderson recently posted about Pre-filters vs Post-filters, describing how the shift to a long tail economy will result in more post-filters and the evaporation of pre-filters. In layman’s terms this means that increasingly more content will be available, but there will be a large market in filtering down to the desired products.

The Long Tail has typically referred and applied to end-user or consumer products: music, movies, television, or anything that can be shipped. The best case scenarios come from products that can be ‘digitized’ and distributed via the Internet – shelfspace and delivery costs nearly evaporate. But companies such as Amazon.com also are included as part of the Long Tail because they can simply have a few large warehouses behind the scenes supplying the physical products - they don’t have the problem of managing thousands of chain branches.

I think there’s a subtle difference in certain types of software that parallels the phenomenon that Chris writes about, but does not match that world-view. A great deal of software is not aimed for an end customer – frameworks and languages, for example.  The market for such software is corporations, not Joe User, and it is exactly this type of software that has been most successful as open source, and I bleieve it is the only type of software that will succeed as such.

In the past, developing software consisted of creating a framework and then adding an interface: GUI, Web Services, or command-line. A great deal of the framework code has been rewritten from scratch since the dawn of computers. Open source changes this. Suddenly we can slowly drop off more and more of the framework development and get on with developing business relevant functionality. In software aimed at the end-user as the target audience, the core of the application typically lies in a shallow layer of business logic/functionality and a interface. This layer of business logic and interface is the application. It is the vector of differentation for your product. Therefore, any intelligent software company will never outsource this code – whether it’s to India or to open source. A cost of zero doesn’t matter, because this is the core of your product. 

Pre-filters

The evaporation of pre-filters can already be seen at work with the emergence of smaller startups creating Web 2.0 applications focusing on a small set of functionality. Think flickr or 37signals. The time to market has dropped substantially, and the feature set necessary to launch has grown smaller. More and more smart startups will be releasing small web apps, and then developing them live with feedback from their customers. The barrier to releasing a product has nearly evaporated. Learn html, css, and a popular web app framework and you can deliver a web app in 24 hours and with a budget under $100. Oh, and the online world of 888 million is now able to access your product.

Post-filters

Obviously we can directly apply the idea of various post-filters that have come out to find content. Think del.icio.us, furl, or even a Google Search. Folksonomies and word-of-mouth are just the latest – we’ve always had review sites to help us navigate. I think this concept is fairly stright-forward…

Beyond the direct notion of post-filters is the strong parallel in the crop of startups emerging that are purely service and consulting driven. Call it “infrastructure engineering”.Corporations still use a tremendous amount of middleware and frameworks and need help supporting, and upgrading this infrastructure. A very large market will appear for consulting and services companies that can help with these large-scale integrations, increasingly of open source varieties. The companies will act not only as post-filters but also as subject matter experts in combining the correct selections together.

Will we see the same as the smaller webapps also create and publish APIs, and interfaces? Certianly, we’ve already seen greate Google Map Hacks, and other interesting uses of these public APIs. But will the terms of condition ever allow for a true commercial marketplace of these mashups and integrations that users find useful - Or will it just be a source of ideas and code for the original API creators?