I created a via Squirrel.
CREATE VIEW duplicateTitle
AS
SELECT med.id, med.title
FROM medium med, medium med2, movie mov1, movie mov2
WHERE med.id <> med2.id
AND lower(med.title) = lower(med2.title)
AND med.id = mov1.id
AND med2.id = mov2.id
UNION
SELECT med.id, med.title
FROM medium med, medium med2, audio aud1, audio aud2
WHERE med.id <> med2.id
AND lower(med.title) = lower(med2.title)
AND med.id = aud1.id
AND med2.id = aud2.id;
Later I dumped the database via
Script.main("-url", getDatabaseURL(database), "-script", backUpFile, "-
user", null , "-password", null, "-options", "DROP");
Restoring the database via
RunScript.main("-url", getDatabaseURL(database), "-script",
backUpFile, "-user", null , "-password", null);
leads to an SQL-error:
2011-04-21 12:50:18,171 [AWT-EventQueue-0] ERROR database.db.Database:
org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "
CREATE FORCE VIEW PUBLIC.DUPLICATETITLE(ID, TITLE) AS
(SELECT MED.ID, MED.TITLE
FROM PUBLIC.MOVIE MOV1
INNER JOIN PUBLIC.MOVIE MOV2 ON 1=1
INNER JOIN PUBLIC.MEDIUM
MED[*] ON
1=1
INNER JOIN PUBLIC.MEDIUM MED2
ON 1=1
WHERE (MED2.ID = MOV2.ID) AND ((MED.ID = MOV1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) UNION (SELECT
MED.ID, MED.TITLE
FROM PUBLIC.AUDIO AUD1
INNER JOIN PUBLIC.AUDIO AUD2 ON 1=1
INNER JOIN PUBLIC.MEDIUM MED
ON 1=1
INNER JOIN PUBLIC.MEDIUM MED2
ON 1=1
WHERE (MED2.ID = AUD2.ID) AND ((MED.ID = AUD1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) "
Syntax error in SQL statement "
CREATE FORCE VIEW PUBLIC.DUPLICATETITLE(ID, TITLE) AS
(SELECT MED.ID, MED.TITLE
FROM PUBLIC.MOVIE MOV1
INNER JOIN PUBLIC.MOVIE MOV2 ON 1=1
INNER JOIN PUBLIC.MEDIUM
MED[*] ON
1=1
INNER JOIN PUBLIC.MEDIUM MED2
ON 1=1
WHERE (MED2.ID = MOV2.ID) AND ((MED.ID = MOV1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) UNION (SELECT
MED.ID, MED.TITLE
FROM PUBLIC.AUDIO AUD1
INNER JOIN PUBLIC.AUDIO AUD2 ON 1=1
INNER JOIN PUBLIC.MEDIUM MED
ON 1=1
INNER JOIN PUBLIC.MEDIUM MED2
ON 1=1
WHERE (MED2.ID = AUD2.ID) AND ((MED.ID = AUD1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) "; SQL
statement:
CREATE FORCE VIEW PUBLIC.DUPLICATETITLE(ID, TITLE) AS
(SELECT MED.ID, MED.TITLE
FROM PUBLIC.MOVIE MOV1 /* PUBLIC.MOVIE.tableScan */
INNER JOIN PUBLIC.MOVIE MOV2 /* PUBLIC.MOVIE.tableScan */ ON 1=1
INNER JOIN PUBLIC.MEDIUM MED /* PUBLIC.PRIMARY_KEY_1: ID = MOV1.ID */
ON 1=1 /* WHERE MED.ID = MOV1.ID */
INNER JOIN PUBLIC.MEDIUM MED2 /* PUBLIC.PRIMARY_KEY_1: ID = MOV2.ID */
ON 1=1
WHERE (MED2.ID = MOV2.ID) AND ((MED.ID = MOV1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) UNION (SELECT
MED.ID, MED.TITLE
FROM PUBLIC.AUDIO AUD1 /* PUBLIC.AUDIO.tableScan */
INNER JOIN PUBLIC.AUDIO AUD2 /* PUBLIC.AUDIO.tableScan */ ON 1=1
INNER JOIN PUBLIC.MEDIUM MED /* PUBLIC.PRIMARY_KEY_1: ID = AUD1.ID */
ON 1=1 /* WHERE MED.ID = AUD1.ID */
INNER JOIN PUBLIC.MEDIUM MED2 /* PUBLIC.PRIMARY_KEY_1: ID = AUD2.ID */
ON 1=1
WHERE (MED2.ID = AUD2.ID) AND ((MED.ID = AUD1.ID) AND ((MED.ID <>
MED2.ID) AND (LOWER(MED.TITLE) = LOWER(MED2.TITLE))))) [42000-140]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.message.DbException.getSyntaxError(DbException.java:179)
at org.h2.command.Parser.getSyntaxError(Parser.java:471)
at org.h2.command.Parser.prepareCommand(Parser.java:229)
at org.h2.engine.Session.prepareLocal(Session.java:434)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:223)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:134)
at java.lang.Thread.run(Unknown Source)
at org.h2.engine.SessionRemote.done(SessionRemote.java:533)
at org.h2.command.CommandRemote.prepare(CommandRemote.java:70)
at org.h2.command.CommandRemote.<init>(CommandRemote.java:48)
at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:411)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1071)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:163)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.tools.RunScript.process(RunScript.java:249)
at org.h2.tools.RunScript.process(RunScript.java:185)
at org.h2.tools.RunScript.process(RunScript.java:310)
at org.h2.tools.RunScript.runTool(RunScript.java:139)
at org.h2.tools.RunScript.main(RunScript.java:67)
Resoring the database without any view in earlier versions of my
program worked very well, I never experienced any problesm. So what is
wrong ?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
