>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 >> [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[]) >> >> >> 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. >>> >>> >>> >> >> -- 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.
