search

LucidDB / luciddb

DEFUNCT: See README
https://github.com/LucidDB/luciddb
Apache License 2.0
52 stars 24 forks source link

[FRG-29] IMPORT FOREIGN SCHEMA will not execute any foreign table definition if even one fails. #841

Open dynamobi-build opened 12 years ago

dynamobi-build commented 12 years ago

[reporter="schoi", created="Fri, 27 Jan 2006 15:28:01 -0500 (GMT-05:00)"] IMPORT FOREIGN SCHEMA will fail if any of the foreign tables cannot be created; either if the specified table does not exist in the foreign schema or if the table exists in the specified local schema already.

Ideally, what foreign tables can be defined should still be created; otherwise, you basically can IMPORT FOREIGN SCHEMA from a specific foreign server only once onto a local schema.

to repro from //open/luciddb using sqllineEngine:

-- using jdbc foreign data wrapper but same with flatfile data wrapper

create foreign data wrapper test_jdbc library 'plugin/FarragoMedJdbc3p.jar' language java;

create server sql_server foreign data wrapper test_jdbc options(driver_class 'net.sourceforge.jtds.jdbc.Driver', url 'jdbc:jtds:sqlserver://AKELA:1433;user=sa;password=xxxxxx', QUALIFYING_CATALOG_NAME 'BENCHMARK');

create schema local_sch;

IMPORT FOREIGN SCHEMA "dbo" LIMIT TO TABLE_NAME LIKE 'a%' FROM SERVER sql_server into local_sch;
 
-- time passed. assume more tables were created in sql_server, so importing again...

IMPORT FOREIGN SCHEMA "dbo" LIMIT TO TABLE_NAME LIKE 'a%' FROM SERVER sql_server into local_sch;

=> fails, and no tables are added to local_sch:

Error: Target schema "LOCALDB"."LOCAL_SCH" already contains foreign table "abc" with same name (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: Target schema "LOCALDB"."LOCAL_SCH" already contains foreign table "abc" with same name
        at net.sf.farrago.ddl.DdlValidator.newPositionalError(DdlValidator.java:1057)
        at net.sf.farrago.ddl.DdlValidator.validateUniqueNames(DdlValidator.java:948)
        at net.sf.farrago.ddl.DdlRelationalHandler.validateDefinition(DdlRelationalHandler.java:93)
        at net.sf.farrago.ddl.DdlRelationalHandler.validateModification(DdlRelationalHandler.java:102)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.eigenbase.util.ReflectUtil.invokeVisitor(ReflectUtil.java:249)
        at net.sf.farrago.ddl.DdlValidator.invokeHandler(DdlValidator.java:1251)
        at net.sf.farrago.ddl.DdlValidator.validateAction(DdlValidator.java:1235)
        at net.sf.farrago.ddl.DdlValidator.validate(DdlValidator.java:823)
        at net.sf.farrago.db.FarragoDbSession.validateDdl(FarragoDbSession.java:805)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:783)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:719)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:151)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)

dynamobi-build commented 12 years ago

[author="jvs", created="Fri, 27 Jan 2006 18:21:35 -0500 (GMT-05:00)"] The current behavior is correct. The SQL standard mandates that statements such as IMPORT FOREIGN SCHEMA are atomic. So I'm reclassifying this as a New Feature request instead of a Bug.

I think Broadbase had the ability to "refresh" a foreign schema (mirroring drops, alters, and creates from the foreign server). Broadbase modeled a foreign schema as a different kind of schema rather than importing metadata into a normal schema, which made this feature cleaner. But we can probably come up with an extension to IMPORT which makes sense (drops are tricky in the case where two different foreign schemas have been unioned into the same local schema).