Download - Rails in the enterprise
![Page 1: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/1.jpg)
Rails in the Enterprise: Off the Beaten TrackAlex Rothenberghttp://alexrothenberg.com@alexrothenberg
Pat Shaughnessyhttp://patshaughnessy.net@patshaughnessy2
Thursday, July 22, 2010
![Page 2: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/2.jpg)
Thursday, July 22, 2010
![Page 3: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/3.jpg)
Thursday, July 22, 2010
![Page 4: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/4.jpg)
Thursday, July 22, 2010
![Page 5: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/5.jpg)
Thursday, July 22, 2010
![Page 6: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/6.jpg)
Using Rails When ...
sharing a development database
there was no documented way to create a new database
our existing database was not built with Rails in mind
the database schema is hard to work with
when you find application code in the databaseThursday, July 22, 2010
![Page 7: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/7.jpg)
Problem 1:
Sharing a development database
Thursday, July 22, 2010
![Page 8: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/8.jpg)
From: ...To: All DevelopersCc: ...Date: 07/06/2010 09:47 AMSubject: Dev/QA Databases unavailable.
Hi All,
This is to inform you that all the databases on Dev/QA servers are down due to issues in UNIX file systems. Please find the following list of servers are impacted - oradbdev-ux01 - oradbdev-ux02 - oradbdev-ux03 - oradbqa-ux01 - oradbqa-ux02
DM team are working on this issue with the Unix team. We will keep you post the status of the server.
If you have any concern please let me know.
Thanks & Regards,
....
Thursday, July 22, 2010
![Page 9: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/9.jpg)
Great tutorials forinstalling Oracle on a Mac:
Mac OS X Leopard:http://blog.rayapps.com/2009/04/12/how-to-install-oracle-database-10g-on-mac-os-x-intel/
Mac OS X Snow Leopard:http://blog.rayapps.com/2009/09/14/how-to-install-oracle-database-10g-on-mac-os-x-snow-leopard/
Thursday, July 22, 2010
![Page 10: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/10.jpg)
Oracle install kits
Oracle 11g:http://www.oracle.com/technology/software/products/database/index.html
Oracle XE (for Linux & Windows only)http://www.oracle.com/technology/software/products/database/xe/index.html
Thursday, July 22, 2010
![Page 11: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/11.jpg)
~ pat$ sqlplus dev/dev@orclSQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 14 22:12:06 2010
SQL> exit
~ pat$ sqlplus test/test@orclSQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 14 22:12:06 2010
SQL>
Thursday, July 22, 2010
![Page 12: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/12.jpg)
Problem 2:
There was no documented way to create the database
Thursday, July 22, 2010
![Page 13: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/13.jpg)
class CreatePeople < ActiveRecord::Migration def self.up create_table :people do |t| t.string :name
t.timestamps end end
def self.down drop_table :people endend
Thursday, July 22, 2010
![Page 14: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/14.jpg)
my_app pat$ rake db:migrate(in /Users/pat/my_app)
my_app pat$
Thursday, July 22, 2010
![Page 15: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/15.jpg)
ActiveRecord::Schema.define(:version => ...) do
create_table "people", :force => true do |t| t.string "name" t.datetime "created_at" t.datetime "updated_at" end
# Lots more Enterprise crap deleted here...
end
Thursday, July 22, 2010
![Page 16: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/16.jpg)
class MigrationZero < ActiveRecord::Migration def self.up end
def self.down endend
Thursday, July 22, 2010
![Page 17: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/17.jpg)
class MigrationZero < ActiveRecord::Migration def self.up
create_table "people", :force => true do |t| t.string "name" t.datetime "created_at" t.datetime "updated_at" end
# Lots more Enterprise crap pasted here...
end
Thursday, July 22, 2010
![Page 18: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/18.jpg)
my_app pat$ rake db:populate(in /Users/pat/my_app)Deleting existing people...Loading new people...43 people added.
etc...
Thursday, July 22, 2010
![Page 19: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/19.jpg)
Problem 3:
Our existing database was not built with Rails in mind
Thursday, July 22, 2010
![Page 20: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/20.jpg)
class Person < ActiveRecord::Baseend
Thursday, July 22, 2010
![Page 21: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/21.jpg)
class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personidend
Thursday, July 22, 2010
![Page 22: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/22.jpg)
class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personid
has_many :addresses, :foreign_key => :personidend
Thursday, July 22, 2010
![Page 23: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/23.jpg)
Thursday, July 22, 2010
![Page 24: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/24.jpg)
Thursday, July 22, 2010
![Page 25: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/25.jpg)
Legacy Data Gem
Install:gem install legacy_data
Source:http://github.com/alexrothenberg/legacy_data
Thursday, July 22, 2010
![Page 26: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/26.jpg)
my_app pat$ script/generate models_from_tables
analyzing psn_perdiem => PsnPerdiemanalyzing psn_person => PsnPersonanalyzing psn_personal_info => PsnPersonalInfo
etc...
Thursday, July 22, 2010
![Page 27: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/27.jpg)
class Person < ActiveRecord::Base set_table_name :psn_person set_primary_key :personid
has_many :addresses, :foreign_key => :personid
validates_presence_of :first_name validates_uniqueness_of :employee_idend
Thursday, July 22, 2010
![Page 28: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/28.jpg)
• User friendly error messages
• Accessible to most of development team
• Easier to write tests
Thursday, July 22, 2010
![Page 29: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/29.jpg)
Problem 4:
The database schema was hard to work with
Thursday, July 22, 2010
![Page 30: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/30.jpg)
SELECT personid, first_name, last_name, ...FROM psn_person
Thursday, July 22, 2010
![Page 31: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/31.jpg)
SELECT personid, first_name, last_nameFROM psn_personINNER JOIN psn_person_extra ON psn_person...INNER JOIN psn_person_career ON psn_person...INNER JOIN psn_person_email ON psn_person...INNER JOIN pik_position ON psn_person...INNER JOIN pik_yes_no ON psn_person...
...and many more...
Thursday, July 22, 2010
![Page 32: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/32.jpg)
class Person < ActiveRecord::Base has_one :person_extra has_one :person_career has_one :person_email belongs_to :pik_position belongs_to :pik_yes_no
# And many other associations tooend
Thursday, July 22, 2010
![Page 33: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/33.jpg)
Thursday, July 22, 2010
![Page 34: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/34.jpg)
SELECT id, first_name, last_name, ...FROM people
SELECT personid, first_name, last_nameFROM psn_personINNER JOIN psn_person_extra ON psn_person...INNER JOIN psn_person_career ON psn_person...INNER JOIN psn_person_email ON psn_person...INNER JOIN pik_position ON psn_person...INNER JOIN pik_yes_no ON psn_person...
Thursday, July 22, 2010
![Page 35: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/35.jpg)
class CreatePeopleView < ActiveRecord::Migration def self.up execute <<-END_SQL CREATE VIEW people AS SELECT personid AS id, ... FROM psn_person INNER JOIN ... ... END_SQL end
def self.down execute "DROP VIEW people" endend
Thursday, July 22, 2010
![Page 36: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/36.jpg)
class Person < ActiveRecord::Base default_scope :readonly=>trueend
Thursday, July 22, 2010
![Page 37: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/37.jpg)
require 'spec_helper'
describe Person do
it "should ..." do Factory :person, :name => 'hilda' Factory :person, :name => 'fredo'
# Do something with people # and assert on result ... end
end
Thursday, July 22, 2010
![Page 38: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/38.jpg)
Thursday, July 22, 2010
![Page 39: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/39.jpg)
Problem 5:
We found legacy code in our database
Thursday, July 22, 2010
![Page 40: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/40.jpg)
Thursday, July 22, 2010
![Page 41: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/41.jpg)
PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId )IStype tyAddressTableIS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData; --PROCEDURE FillTable ( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable )IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID;BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP;END;
Thursday, July 22, 2010
![Page 42: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/42.jpg)
PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId )IStype tyAddressTableIS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData; --PROCEDURE FillTable ( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable )IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID;BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP;END;
FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER;BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'||pDuplicateTable(pRow).AddressNote ; -- dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'||pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF;
Thursday, July 22, 2010
![Page 43: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/43.jpg)
PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId )IStype tyAddressTableIS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData; --PROCEDURE FillTable ( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable )IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID;BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP;END;
FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER;BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'||pDuplicateTable(pRow).AddressNote ; -- dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'||pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF;
IF pDuplicateTable(pRow).systemNote IS NOT NULL THEN pCorrectTable(m).systemNote := pCorrectTable(m).SystemNote||CHR(10)||'Note from Duplicate:'||pDuplicateTable(pRow).systemNote; shouldModify := pkgGlobal.gTrue; END IF; IF shouldModify = pkgGlobal.gTrue THEN -- Clobber added 10/16/03 pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.modify( pCorrectTable(m), pSecure, pCommit ); -- dbms_output.put_line('Moving columns from pDuplicate PersonId:'||pDuplicateTable(pRow).PersonID||' to Master PersonId:'||pCorrectTable(m).PersonId||' where data is present in pDuplicate and null in master '); END IF; -- Print pDuplicate Record which is to be deleted -- dbms_output.put_line('pDuplicate Record which is to be deleted...'); -- dbms_output.put_line('ADDRESSID :'||to_char(pDuplicateTable(pRow).addressId)); -- dbms_output.put_line('PERSONID :'||to_char(pDuplicateTable(pRow).personId)); -- dbms_output.put_line('ADDRESSTYPEID :'||to_char(pDuplicateTable(pRow).AddressTypeId)); -- dbms_output.put_line('CITY :'||pDuplicateTable(pRow).city); -- dbms_output.put_line('STATEPROVINCEID:'||to_char(pDuplicateTable(pRow).stateProvinceId)); -- dbms_output.put_line('POSTALCODE :'||pDuplicateTable(pRow).PostalCode); -- dbms_output.put_line('COUNTRYID :'||to_char(pDuplicateTable(pRow).countryId)); -- dbms_output.put_line('SYSTEMNOTE :'||substr(pDuplicateTable(pRow).systemNote,1,230)); -- dbms_output.put_line('ADDRESSNOTE :'||substr(pDuplicateTable(pRow).addressNote,1,230)); -- Check if Primary Email exists in Master, if no and the pDuplicate is isPrimary, then move the flag to master BEGIN SELECT COUNT(*) INTO primary_count_for_delete FROM tbAddress WHERE personId = pCorrectPersonId AND IsPrimary = 1; IF primary_count_for_delete = 0 AND pDuplicateTable(pRow).IsPrimary = 1 THEN pCorrectTable(m).IsPrimary := 1; pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.Modify( pCorrectTable(m), pSecure, pCommit ); END IF; EXCEPTION WHEN no_data_found THEN NULL; END; EXIT; ELSE Ret := pkgGlobal.gFalse; END IF; m := pCorrectTable.Next( m ); END LOOP; RETURN Ret;EXCEPTIONWHEN OTHERS THEN pkgException.RaiseOther (SQLCODE, sqlerrm); RETURN Ret; NULL;END;
Thursday, July 22, 2010
![Page 44: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/44.jpg)
PROCEDURE MergeAddress ( pCorrectPersonID IN pkgGlobal.tyID, pDuplicatePersonId IN pkgGlobal.tyId )IStype tyAddressTableIS TABLE OF pkgAddressBase.tyData INDEX BY binary_integer; IDTable tyIDTable; EmptyTable tyAddressTable; CorrectTable tyAddressTable; DuplicateTable tyAddressTable; i INTEGER := 0; primary_count INTEGER := 0; primary_count_for_delete INTEGER := 0; CID pkgGlobal.tyID; SQLStr pkgGlobal.tyData; --PROCEDURE FillTable ( pDuplicatePersonId IN pkgGlobal.tyId, pTable OUT tyAddressTable )IS aToken pkgGlobal.tyId; CURSOR c1 IS SELECT ADDRESSID, PERSONID, ADDRESSTYPEID, ISPRIMARY, STREET1, STREET2, STREET3, STREET4, CITY, STATEPROVINCEID, POSTALCODE, COUNTRYID, SYSTEMNOTE, ADDRESSNOTE FROM tbAddress WHERE PersonID = pDuplicatePersonID;BEGIN FOR r IN c1 LOOP pTable(r.AddressId).AddressId := r.AddressID; pTable(r.AddressId).PersonID := r.PersonID; pTable(r.AddressId).AddressTypeId := r.AddressTypeId; pTable(r.AddressId).IsPrimary := r.IsPrimary; pTable(r.AddressId).Street1 := r.Street1; pTable(r.AddressId).Street2 := r.Street2; pTable(r.AddressId).Street3 := r.Street3; pTable(r.AddressId).Street4 := r.Street4; pTable(r.AddressId).city := r.city; pTable(r.AddressId).StateProvinceId := r.StateProvinceId; pTable(r.AddressId).postalCode := r.postalCode; pTable(r.Addressid).countryId := r.countryId; pTable(r.Addressid).AddressNote := r.addressNote; pTable(r.Addressid).systemNote := r.systemNote; END LOOP;END;
FUNCTION CompareValues ( pCorrectTable IN OUT tyAddressTable, pDuplicateTable IN OUT tyAddressTable, pRow IN INTEGER) RETURN INTEGER IS i INTEGER := 0; m INTEGER := 0; Ret INTEGER := 0; master_city tbAddress.city%type; master_street1 tbAddress.street1%type; pDuplicate_city tbAddress.city%type; pDuplicate_street1 tbAddress.street1%type; master_countryId pkgGlobal.TyId; shouldModify INTEGER;BEGIN m := pCorrectTable.First; FOR i IN 1..pCorrectTable.Count LOOP SELECT DECODE(pCorrectTable(m).city,'See Address Note','CITY1',pCorrectTable(m).city) INTO master_city FROM dual; SELECT DECODE(pDuplicateTable(pRow).city,'See Address Note','CITY2',pDuplicateTable(pRow).city) INTO pDuplicate_city FROM dual; SELECT DECODE(pCorrectTable(m).street1,'See Address Note','STREET1',pCorrectTable(m).Street1) INTO master_street1 FROM dual; SELECT DECODE(pDuplicateTable(pRow).street1,'See Address Note','STREET1111',pDuplicateTable(pRow).street1) INTO pDuplicate_street1 FROM dual; SELECT DECODE(pCorrectTable(m).countryId,-100,100,pCorrectTable(m).countryId) INTO master_countryId FROM dual; IF master_countryId = pDuplicateTable( pRow ).countryId AND removeSpecial(master_city) = removeSpecial(pDuplicate_city) AND removeSpecial(master_Street1) = removeSpecial(pDuplicate_Street1) AND removeSpecial(NVL(pCorrectTable(m).street2,'STREET2')) = removeSpecial(NVL(pDuplicateTable(pRow).street2,'STREET2')) AND removeSpecial(NVL(pCorrectTable(m).street3,'STREET3')) = removeSpecial(NVL(pDuplicateTable(pRow).street3,'STREET3')) AND removeSpecial(NVL(pCorrectTable(m).street4,'STREET4')) = removeSpecial(NVL(pDuplicateTable(pRow).street4,'STREET4')) AND NVL(pCorrectTable(m).StateProvinceId,999999999999) = NVL(pDuplicateTable( pRow ).StateProvinceId,999999999999) AND NVL(pCorrectTable(m).postalCode,'999999999999') = NVL(pDuplicateTable( pRow ).postalcode,'999999999999') THEN Ret := pkgGlobal.gTrue; IF pDuplicateTable(pRow).AddressNote IS NOT NULL THEN pCorrectTable(m).AddressNote := pCorrectTable(m).AddressNote||CHR(10)||'Note from pDuplicate:'||pDuplicateTable(pRow).AddressNote ; -- dbms_output.put_line('Moving Column addressNote:'||substr(pCorrectTable(m).addressNote,1,200)||':'||' from Duplicate personId:'||pDuplicateTable(prow).personId||' to master PersonId:'||pCorrectTable(m).PersonId||' record for addressTypeId:'||pCorrectTable(m).addressTypeId); shouldModify := pkgGlobal.gTrue; END IF;
IF pDuplicateTable(pRow).systemNote IS NOT NULL THEN pCorrectTable(m).systemNote := pCorrectTable(m).SystemNote||CHR(10)||'Note from Duplicate:'||pDuplicateTable(pRow).systemNote; shouldModify := pkgGlobal.gTrue; END IF; IF shouldModify = pkgGlobal.gTrue THEN -- Clobber added 10/16/03 pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.modify( pCorrectTable(m), pSecure, pCommit ); -- dbms_output.put_line('Moving columns from pDuplicate PersonId:'||pDuplicateTable(pRow).PersonID||' to Master PersonId:'||pCorrectTable(m).PersonId||' where data is present in pDuplicate and null in master '); END IF; -- Print pDuplicate Record which is to be deleted -- dbms_output.put_line('pDuplicate Record which is to be deleted...'); -- dbms_output.put_line('ADDRESSID :'||to_char(pDuplicateTable(pRow).addressId)); -- dbms_output.put_line('PERSONID :'||to_char(pDuplicateTable(pRow).personId)); -- dbms_output.put_line('ADDRESSTYPEID :'||to_char(pDuplicateTable(pRow).AddressTypeId)); -- dbms_output.put_line('CITY :'||pDuplicateTable(pRow).city); -- dbms_output.put_line('STATEPROVINCEID:'||to_char(pDuplicateTable(pRow).stateProvinceId)); -- dbms_output.put_line('POSTALCODE :'||pDuplicateTable(pRow).PostalCode); -- dbms_output.put_line('COUNTRYID :'||to_char(pDuplicateTable(pRow).countryId)); -- dbms_output.put_line('SYSTEMNOTE :'||substr(pDuplicateTable(pRow).systemNote,1,230)); -- dbms_output.put_line('ADDRESSNOTE :'||substr(pDuplicateTable(pRow).addressNote,1,230)); -- Check if Primary Email exists in Master, if no and the pDuplicate is isPrimary, then move the flag to master BEGIN SELECT COUNT(*) INTO primary_count_for_delete FROM tbAddress WHERE personId = pCorrectPersonId AND IsPrimary = 1; IF primary_count_for_delete = 0 AND pDuplicateTable(pRow).IsPrimary = 1 THEN pCorrectTable(m).IsPrimary := 1; pCorrectTable(m).clobber := pkgGlobal.gFalse; pkgAddressBase.Modify( pCorrectTable(m), pSecure, pCommit ); END IF; EXCEPTION WHEN no_data_found THEN NULL; END; EXIT; ELSE Ret := pkgGlobal.gFalse; END IF; m := pCorrectTable.Next( m ); END LOOP; RETURN Ret;EXCEPTIONWHEN OTHERS THEN pkgException.RaiseOther (SQLCODE, sqlerrm); RETURN Ret; NULL;END;
BEGIN ret := upper(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE( string1,'!',''),'@',''),'#',''),'$',''), '%',''),'^',''),'&',''),'*',''), '-', ''),'+',''),'',''),'(',''), ')',''),'?',''),',',''),'.',''), '/',''),'=','')); RETURN ret;END;
Thursday, July 22, 2010
![Page 45: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/45.jpg)
Thursday, July 22, 2010
![Page 46: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/46.jpg)
Thursday, July 22, 2010
![Page 47: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/47.jpg)
require 'spec_helper'
describe Person do
it "should merge two duplicates" do Factory :person, :email => '[email protected]' Factory :person, :email => '[email protected]'
Person.merge_duplicates
Person.count.should == 1 end
end
Thursday, July 22, 2010
![Page 48: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/48.jpg)
my_app pat$ rake spec(in /Users/pat/my_app)..................................................F.....................................................................................
1)RuntimeError in 'Should merge two duplicates'OCIError: ORA-04092:cannot ROLLBACK in a trigger...ORA-01403: no data foundORA-06512: at "HRTEST.PKGAPPBASE", line 1775ORA-04088: error during execution of triggerUPDATE psn_address SET personid = 10543035 WHERE applicationid = 10594482
Thursday, July 22, 2010
![Page 49: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/49.jpg)
PROCEDURE updateops
# ...lots of PL/SQL...
vQryStr := 'SELECT ... WHERE personid = ' || ppersonid;
dbms_output.put_line(vQryStr);
# ...lots more PL/SQL...
END;
Thursday, July 22, 2010
![Page 50: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/50.jpg)
Oracle Enhanced Adapter
Install: gem install activerecord-oracle_enhanced-adapter
Source:http://github.com/rsim/oracle-enhanced
Thursday, July 22, 2010
![Page 51: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/51.jpg)
6357 lines
0 tests
33% failure
322 lines
lots of tests
0.1% failure
Merge Job
Thursday, July 22, 2010
![Page 52: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/52.jpg)
Using Rails When ...
sharing a development database
there was no documented way to create a new database
our existing database was not built with Rails in mind
the database schema is hard to work with
when you find application code in the databaseThursday, July 22, 2010
![Page 53: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/53.jpg)
Use Rails best practices even when confronted with legacy problems
Thursday, July 22, 2010
![Page 54: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/54.jpg)
Thank you!
Alex Rothenberghttp://alexrothenberg.com@alexrothenberg
Pat Shaughnessyhttp://patshaughnessy.net@patshaughnessy2
Thursday, July 22, 2010
![Page 55: Rails in the enterprise](https://reader034.vdocuments.us/reader034/viewer/2022042606/54bd198a4a7959f95e8b4606/html5/thumbnails/55.jpg)
http://media.photobucket.com/image/lipstick%20on%20a%20pig/046664/LipstickPig-C.jpg?o=11
http://www.flickr.com/photos/piccadillywilson/1366479417/
Photo Credits
Thursday, July 22, 2010