I see now what this is about. I agree with Roger that you will have to be careful about assuming that you will get any sensible table order from your database meta data. It's better to remain in full control, explicitly. One way of how to do this is by leveraging jOOQ's exposed constraint information. Of course, you could also run queries against JDBC's DatabaseMetaData, or against your database's INFORMATION_SCHEMA directly.
Note, though, that in relational schemas, it is likely that you will find cycles in your referential dependency graph, which means that there is no real "order" to your tables as in the sense of a tree. A previous discussion on this group about calculating a convex hull for objects contained in a schema showed how similar problems can be solved: https://groups.google.com/d/msg/jooq-user/6TBBLYt9eR8/LBf-g5m1Yd4J That discussion led to this blog post here: http://blog.jooq.org/2012/08/20/serious-sql-a-convex-hull-of-correlated-tables/ I'm sure you'll find a similar solution where you will introspect meta data to build a correct order for inserting new data. Cheers Lukas 2013/6/9 Roger Thomas <[email protected]> > From your comments I think you are looking for a solution that provides > the sql referential integrity hierarchy for the database? Is that correct. > > If so using the order of table creation only works if the schema is built > from a single script, where the script builds all the tables and > referential integrity rules in a single pass, and by a table at a time. The > instance that more than a single script is run (such as via revisions) or > the script creates all the tables first and then the rules, you can't infer > that the referential rules are based on the order of table creation for a > general environment. The instance that you try and deal with a general > environment you end up having to build up some sort of tree to describe in > which order tables can be update/inserted into and then order all inserts > within a transaction by the tree order. I've seen comments on the web that > such tools are available, but in pass projects what I have seen is > developers just coding to the database spec (which details the inset order) > and a rule that no data focussed staff breaks the spec, otherwise there > could be a lot of recoding. > > > > On Saturday, June 8, 2013 10:49:56 PM UTC+1, [email protected] wrote: >> >> Ah I see why it's done that way. The reason I ask is because I am >> currently looping through all of the tables, inserting data from a file and >> have problems when a table with a reference to another table is being >> populated before the referenced table. To bypass this, I've created an enum >> that has the correct order for foreign key constraints. Is there a way >> currently in jOOQ to select tables in order of references? I just want to >> make sure before I look into changing up the codegen stuff. >> >> Thanks! >> >> On Saturday, June 8, 2013 3:21:32 AM UTC-4, Lukas Eder wrote: >>> >>> Hi, >>> >>> jOOQ-meta indeed orders tables alphabetically prior to code-generation. >>> This leads to repeatable results, which is useful to those users who put >>> generated code under version control - i.e. without messing up the whole >>> ordering, it is easy to see what has changed. >>> >>> Do databases specifically specify an order to tables within their >>> schemas? I am not aware of such a mechanism. The SQL 1992 standard [1], for >>> instance, specifies the INFORMATION_SCHEMA.TABLES view as such: >>> >>> 21.2.7 TABLES view >>> >>> Function >>> >>> Identify the tables defined in this catalog that are accessible to >>> a given user. >>> >>> Definition >>> >>> CREATE VIEW TABLES >>> AS SELECT >>> TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE >>> FROM DEFINITION_SCHEMA.TABLES >>> WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) >>> IN ( >>> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME >>> FROM DEFINITION_SCHEMA.TABLE_**PRIVILEGES >>> WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER ) >>> UNION >>> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME >>> FROM DEFINITION_SCHEMA.COLUMN_**PRIVILEGES >>> WHERE GRANTEE IN ( 'PUBLIC', CURRENT_USER ) ) >>> AND TABLE_CATALOG >>> = ( SELECT CATALOG_NAME FROM INFORMATION_SCHEMA_ >>> CATALOG_NAME ) >>> >>> No explicit order is defined. The same holds true for JDBC's >>> DatabaseMetaData.getTables() method [2], which doesn't seem to specify any >>> ordering. Note that you can always override jOOQ-meta databases and >>> re-implement the various methods to influence jOOQ-codegen's outcome >>> >>> Cheers >>> Lukas >>> >>> [1]: >>> http://www.andrew.cmu.**edu/user/shadow/sql/sql1992.**txt<http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt> >>> [2]: http://docs.oracle.com/**javase/7/docs/api/java/sql/** >>> DatabaseMetaData.html#**getTables(java.lang.String, java.lang.String, >>> java.lang.String, >>> java.lang.String[])<http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,+java.lang.String,+java.lang.String,+java.lang.String[])> >>> >>> >>> 2013/6/7 <[email protected]> >>> >>>> Hello, >>>> >>>> Is there a way to get all tables of a database in the order they are >>>> defined in the schema? >>>> >>>> SchemaImpl.getTables() sorts them alphabetically and I think it would >>>> be more helpful if by default you could retrieve tables in order of >>>> definition since sorting alphabetically can easily be done by the user. >>>> >>>> Thanks >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "jOOQ User Group" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]**. >>>> For more options, visit >>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>>> . >>>> >>>> >>>> >>> >>> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
