Script database does not output foreign key references

[reiniero]

scriptdb.pas: Foreign keys are not picked up while FlameRobin does process them, e.g. this for employee.fdb: ALTER TABLE EMPLOYEE ADD CONSTRAINT INTEG_28 FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);

[motaz]

what do you mean by "picked up"

[reiniero]

hi motaz

I meant processed, correctly processed. sorry it is probably not correct english.

regards, reinier

On 4/12/14, Motaz Abdel Azeem notifications@github.com wrote:

what do you mean by "picked up"

---

Reply to this email directly or view it on GitHub: https://github.com/motaz/turbobird/issues/11#issuecomment-40285997

[reiniero]

Problem probably in TdmSysTables.GetTableConstraints which appears to look for all foreign key constraints testing with the query used (but without the where clause limiting table names) gives 0 results on a Firebird 2.5 employee sample database select Trim(Refc.RDB$Constraint_Name) as ConstName, Trim(Refc.RDB$CONST_NAME_UQ) as KeyName, Trim(Ind.RDB$Relation_Name) as CurrentTableName, Trim(Seg.RDB$Field_name) as CurrentFieldName, Trim(Con.RDB$Relation_Name) as OtherTableName, Trim(Ind.RDB$Foreign_key) as OtherFieldName, RDB$Update_Rule as UpdateRule, RDB$Delete_Rule as DeleteRule from RDB$RELATION_CONSTRAINTS Con, rdb$REF_Constraints Refc, RDB$INDEX_SEGMENTS Seg, RDB$INDICES Ind where Con.RDB$Constraint_Name = Refc.RDB$Const_Name_UQ and Refc.RDB$Constraint_Name = Ind.RDB$Index_Name and Refc.RDB$Constraint_Name = Seg.RDB$Index_Name

Perhaps one of the queries in http://www.alberton.info/firebird_sql_meta_info.html may be used for inspiration?

[reiniero]

This query seems to give the desired output: SQLQuery.SQL.Text:='select '+ 'rc.rdb$constraint_name as ConstName, '+ 'rfc.rdb$const_name_uq as KeyName, '+ 'rc2.rdb$relation_name as CurrentTableName, '+ 'flds_pk.rdb$field_name as CurrentFieldName, '+ 'rc.rdb$relation_name as OtherTableName, '+ 'flds_fk.rdb$field_name as OtherFieldName, '+ 'rfc.rdb$update_rule as UpdateRule, '+ 'rfc.rdb$delete_rule as DeleteRule '+ 'from rdb$relation_constraints AS rc '+ 'inner join rdb$ref_constraints as rfc on (rc.rdb$constraint_name = rfc.rdb$constraint_name) '+ 'inner join rdb$index_segments as flds_fk on (flds_fk.rdb$index_name = rc.rdb$index_name) ' + 'inner join rdb$relation_constraints as rc2 on (rc2.rdb$constraint_name = rfc.rdb$const_name_uq) ' + 'inner join rdb$index_segments as flds_pk on ' + '((flds_pk.rdb$index_name = rc2.rdb$index_name) and (flds_fk.rdb$field_position = flds_pk.rdb$field_position)) ' + 'where rc.rdb$constraint_type = ''FOREIGN KEY'' '+ 'and rc.rdb$relation_name = ''' + UpperCase(ATableName) + ''' '+ 'order by rc.rdb$constraint_name, flds_fk.rdb$field_position ';

I've just committed it. Motaz, could you please test and verify if that is indeed the output you expect in the references tab - the "On Key" column represents the foreign key field in the current table, right?

[motaz]

I have tested it, References displays current table constraints instead of references, it is some how inverted

[motaz]

TurboBird.lpi and .res generates conflicts, should we remove them from source control?

[reiniero]

Perhaps best to give an example for e.g. the EMPLOYEE table in the sample db which has e.g. foreign key INTEG_28 on local field DEPT_NO which refers to DEPARTMENT table, field DEPT_NO. Currently turbobird displays for me Constraint name=integ_28, foreign table=department, foreign field=dept_no, on key=dept_no... same info as FlameRobin having trouble seeing what you want to switch where?

Regarding the res files: what conflicts are you getting? I'd think you can overwrite your local version with the one from the repo. The res file should be regenerated from the .rc file (at least on windows/systems with windres installed). The lpi is essential though

[motaz]

I don't use Employee table for testing because it contains some types that FPC cann't open. Opening Job table and PROJ_DEPT_BUDGET generates Unknown field type error:

Unknown field type : QUART_HEAD_CNT

I'm testing it in my Accounting system

[reiniero]

Those are array fields which are indeed not supported by sqldb (or by many other tools like FlameRobin). Committed an update that properly detects these data types in table management. Nothing much to do in select... though

[reiniero]

The foreign key scripting issue should be fixed in commit 15b5c28e6652bc00b517e245cbeb610ca7fa68ef