Hi, I see that there is some built in relations between documents using Jena graph. But I do not like this solution for the following reasons: <ul><li>It does not use the SQL-database's relation support.</li> <li>It does not use the SQL-database's table-support directly </li> <li>It does not give me an easy way to restrict a relation from A to B. In Jena, as I understand it, it is general relations from any to any. </li> </ul> Therefore I have experimented a bit with Nuxeo's built-in directory / vocabulary support and views in PostgreSQL in order to make relations between documents in Nuxeo using native SQL foreign key support.
h2. My recipe for making relations with foreign keys:*1) Make Document definitions**1) Make Document Types* Make Document Types A and B. In A, make a field named rel_to_b of type "xs:string" or "stringList". *2) Make view in SQL* We use PostgreSQL, and create a view as following: bq. CREATE VIEW b_view AS SELECT p.id, p.name AS label, ordering, 0 AS obsolete FROM hierarchy h JOIN tvproduct tp ON tp.id=h.id JOIN product p ON (p.id=tp.id) WHERE h.primarytype='TvProduct' AND h.majorversion=1 AND h.minorversion=0; \\ GRANT SELECT on b_view to nuxeo; Note that in our View we use version "1.0" / source document. This is because our model only relates to the sourceId. This is the unique primary key for the Document (not version) *3) Make Directory* Make a SQL-directory that uses the b_view in a OSGi-config: bq. <?xml version="1.0"?> \\ <component name="my.example.directories"> \\ <implementation class="org.nuxeo.ecm.directory.sql.SQLDirectoryDescriptor" /> \\ <require>org.nuxeo.ecm.directory.sql.SQLDirectoryFactory</require> \\ <extension target="org.nuxeo.ecm.directory.sql.SQLDirectoryFactory" point="directories"> \\ <directory name="tvproducts"> \\ <schema>vocabulary</schema> \\ <dataSource>java:/nxsqldirectory</dataSource> \\ <cacheTimeout>3600</cacheTimeout> \\ <cacheMaxSize>1000</cacheMaxSize> \\ <table>b_view</table> \\ <idField>id</idField> \\ <autoincrementIdField>false</autoincrementIdField> \\ <dataFile>directories/empty.csv</dataFile> \\ <createTablePolicy>never</createTablePolicy> \\ </directory> \\ </extension> \\ </component> Note that it is important to set autoincrementIdField=false and createTablePolicy=never in order to be allowed to use views in SQL. *4) Add widget* Add a widget on the layout for A to let the user select a B-relations. *5) Foreign keys in the database* Also it is desireable to have foreign keys in the database just like for everything else. This is done by adding a foreign key on the field value for single values (one to many relations) or the mapping table for many to many. Example for many to many table: bq. ALTER TABLE a_b ADD FOREIGN KEY (item) REFERENCES b ON DELETE CASCADE; *6) Done* After these steps I have made true relations between documents. h2. My proposal It would be nice to be allowed to have a "relation" extension such that the configuration in 2, 3 and 5 had been done through Nuxeo instead of direct SQL manipulation. Thanks for a great product. -- Posted by "kjetilny" at Nuxeo Discussions <http://nuxeo.org/discussions> View the complete thread: <http://www.nuxeo.org/discussions/thread.jspa?threadID=4003#12275> _______________________________________________ ECM mailing list [email protected] http://lists.nuxeo.com/mailman/listinfo/ecm To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
