During migration from MS SQL Server to DB2 I was facing a problem to generate foreign keys (in DB2 supported format) from MS SQL generated object script without having an access to MSSQL database. It is not a problem to recreate them manually if you have several objects but it could be a problem if you are dealing with hundreds or thousands of them.
So I decided to spend some time on creating a simple program (in Python) to accomplish the task automatically.
The source code is available here (Eclipse PyDev project).
Packages
The solution comprises several packages.
readfiles : read lines from several files (list of files in constructor). It flattens several files to one single reader.
atomizer : transforms input into sequence of atoms. For instance:
CREATE TABLE [dbo].[departments]( [dept_no] [char](4) NOT NULL, [dept_name] [varchar](40) NOT NULL, PRIMARY KEY CLUSTEREDAtomizer will output: CREATE TABLE [dbo.department] ( [dept_no ... etc. It simply breaks input (list of text lines) into sequence of elements ignoring spaces, line breaks etc.
tokenizer : transforms list of atoms into the sequence of recognized keywords ignoring elements out of importance here.
For instance.
Assuming set of constants describing keywords important here:
ALTER=0 SEMICOLON=1 TABLE=2 ADD=3 CONSTRAINT=4 FOREIGN=5 KEY = 6 REFERENCES = 7 BEGCOMMENT=8 ENDCOMMENT=9 CREATE=10 VIEW=11 FUNCTION=12 PROCEDURE=13 GO=14The output will be the sequence: CREATE (constant 10) TABLE [dbo.department] (as single tekst) ( [dept_no ... (etc). Tokenizer makes further analysis more easier.
foreign : selects foreign key definition and prepares data structure: base table, constraint name, list of columns, reference table name and reference column list. Example:
ALTER TABLE [dbo].[dept_emp] WITH CHECK ADD FOREIGN KEY([dept_no]) REFERENCES [dbo].[departments] ([dept_no]) ON DELETE CASCADEor
ALTER TABLE [dbo].[dept_emp] WITH CHECK ADD CONSTRAINT DEPT_NO_DEPARTMENT_FK FOREIGN KEY([dept_no]) REFERENCES [dbo].[departments] ([dept_no]) ON DELETE CASCADEpublish : takes data structure (describing foreign key definition as describe above) and prepares DDL in DB2 format. For instance:
ALTER TABLE dbo.dept_emp ADD CONSTRAINT "FK_dept_emp_departments" FOREIGN KEY (dept_no) REFERENCES dbo.departments (dept_no) @The main program
def test4(): R = readfiles.ReadFiles(INF) A = atomizer.Atomizer(R) T = tokenizer.TOKENIZER(A) F = foreign.ForeignSearcher(T) a = F.nextForeign() f = open("output/foreign_keys.db2","w") while a != None : s = foreignDB2.foreignDB2(a) print s print "" print "" f.write(s) f.write("\n") f.write("\n") a = F.nextForeign() f.close()The first four statements (objects construction) can be fused into a single statement.
Example input and output.
Conclusion
I found this approach useful. I also reused it in resolving several other problems.
- Migrate only a subset foreign keys definition. Read firstly list of tables (without foreign keys) already migrated and emits only foreign keys related to them.
- Prepare list of all objects (tables, view, UDF and SP) in MS SQL object script.
Brak komentarzy:
Prześlij komentarz