#3 Add Schema to DB auto mapping

Near Future
Rob Hranac

> Well, if this is an unsolved problem in Computer Science (please correct
> any mistaken assumptions I may make), I should write a paper (and get
> famous) as I was able to import GML into the old GeoServer without any
> problems. Here's how I did it:

Tee hee. Yes, whenever you hear me use the phrase 'unsolved problem in computer science,' it is probably time to put the waders on because the bullshit is about to get real deep. :) However, I think I didn't make my point clear enough, so let me try again with further hand waving. According to one of my coworkers here who actually does know something about this U.P.i.C.S. because he took class last year at Columbia on XML and database design, the U.P.i.C.S. is: How do you automatically map arbitrary XML schemas (hierarchical, semi-object data models: http://www.w3.org/XML/Datamodel.html\) into standard databases (relational data model) and back out again? As you can tell, I don't really know much about this, but I believe my friend because I can't see how to do the mapping either.

So, in the excellent example you gave, you have a 'flat' schema with no nested elements that easily maps into a single table in the database. Look at this class: http://geoserver.sourceforge.net/documentation/javadocs/org/vfny/freefs/servlets/utilities/PostgreSqlSchemaElement.html.

If you inspect the source, you will see that I have done something almost identical, except in the opposite direction: mapping a 'flat' table into a 'flat' XML schema. However, GML is much more expressive than this and each geometry element could have arbitrary numbers of sub or related elements and lots of other weird constructs that are hard to automatically map directly into relational databases - i.e. need lots of tables and foreign keys and maybe even then you can't quite do it. This is what I mean, but I am probably making too big a deal out of it because it is semi-academic until people start supporting more complex data structures on the client side.

> One caveat -- I don't know if this is compatible with the current table
> definitions (I really doubt it).

I don't quite understand this; remember that table defs must be totally arbitrary. So, there are no table defs in GeoServer other than what the user defines for their own feature type data needs.

> Importing a schema in the example above would not generate new tables,
> rather it would just put the appropriate definitions in the Feature Type
> table. If you are working with two schemas on the server that have
> identically named types, you can distinguish the two by adding a field
> with each entry that allows the database to know which schema definition
> to use.

Got it - this is a good idea. I like your approach and someday soon I will poach your code and fit it into the current incarnation of GeoServer. It makes sense to be able to do the flat mapping in both directions; I just didn't think of this myself. If you want to do the integration, also, feel free. In fact, I just put this in the task tracker and assigned it to you!