SQLJet creates malformed databases

GoogleCodeExporter commented 9 years ago

Based on the tutorial I created a new database with one table and an index 
(see attached file):

CREATE TABLE IF NOT EXISTS TESTXX (a int, b int, c int, d int, blob blob, 
PRIMARY KEY (a,b,c,d))
CREATE INDEX IF NOT EXISTS IND on TESTXX (a,b,c,d)

If I run 
sqlite3 test.db "pragma integrity_check"

on the created file sqlite is telling me that the created database is 
malformed:
SQL error: malformed database schema (IND) - no such table: main.TESTXX

Original issue reported on code.google.com by rob...@mailueberfall.de on 25 Sep 2009 at 1:15

Attachments:

MalformedDB.java

GoogleCodeExporter commented 9 years ago

"IF NOT EXISTS" isn't usual case in createTable() call. We could provide it. 
Question
is only in that, what should be performed in case when table with specified 
name(e.g.
TESTXX) already exists in database and somebody call createTable("CREATE TABLE 
IF NOT
EXISTS %tablename ..." ); where %tablename (e.g. TESTXX) is table name which 
already
exists? Should be thrown exception or just returned null? I'll make it somehow 
in my
or your choice.

Original comment by sergey.s...@gmail.com on 25 Sep 2009 at 8:30

GoogleCodeExporter commented 9 years ago

Original comment by sergey.s...@gmail.com on 25 Sep 2009 at 8:30

Changed state: Accepted

GoogleCodeExporter commented 9 years ago

I've looked - there is other our troubles with "pragma integrity_check" passing.
Thank you for reporting! We'll investigate and fix it.

Original comment by sergey.s...@gmail.com on 25 Sep 2009 at 8:55

GoogleCodeExporter commented 9 years ago

I've tried without IF NOT EXISTS as

CREATE TABLE TESTXX (a int, b int, c int, d int, blob blob, 
PRIMARY KEY (a,b,c,d))
CREATE INDEX IF NOT EXISTS IND on (a,b,c,d)

and got:

$ sqlite3 test.db "pragma integrity_check"
SQL error: malformed database schema - near "a": syntax error

if i open in our DBBrowser.java i've got

org.tmatesoft.sqljet.core.internal.lang.SqlJetParserException: [line 1:66] no 
viable
alternative at input 'KEY'
    at
org.tmatesoft.sqljet.core.internal.lang.SqlParser.displayRecognitionError(SqlPar
ser.java:265)
    at org.antlr.runtime.BaseRecognizer.reportError(BaseRecognizer.java:186)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.type_name(SqlParser.java:4362)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.column_def(SqlParser.java:9651)
    at
org.tmatesoft.sqljet.core.internal.lang.SqlParser.create_table_stmt(SqlParser.ja
va:9410)
    at
org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.parseTable(SqlJetSchema.j
ava:237)
    at
org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.readShema(SqlJetSchema.ja
va:196)
    at org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.init(SqlJetSchema.java:110)
    at org.tmatesoft.sqljet.core.internal.schema.SqlJetSchema.<init>(SqlJetSchema.java:100)
    at org.tmatesoft.sqljet.core.table.SqlJetDb$1.runWithLock(SqlJetDb.java:112)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.runWithLock(SqlJetDb.java:212)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.<init>(SqlJetDb.java:106)
    at org.tmatesoft.sqljet.core.table.SqlJetDb.open(SqlJetDb.java:131)
    at
org.tmatesoft.sqljet.browser.core.schema.SchemaComponent.open(SchemaComponent.ja
va:50)
    at
org.tmatesoft.sqljet.browser.core.BrowserComponentManager.stateChanged(BrowserCo
mponentManager.java:220)
    at
org.tmatesoft.sqljet.browser.core.BrowserComponentManager.open(BrowserComponentM
anager.java:195)
    at
org.tmatesoft.sqljet.browser.core.actions.OpenAction.actionPerformed(OpenAction.
java:68)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
    at javax.swing.AbstractButton.doClick(AbstractButton.java:302)
    at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1051)
    at
javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.jav
a:1092)
    at java.awt.Component.processMouseEvent(Component.java:5517)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
    at java.awt.Component.processEvent(Component.java:5282)
    at java.awt.Container.processEvent(Container.java:1966)
    at java.awt.Component.dispatchEventImpl(Component.java:3984)
    at java.awt.Container.dispatchEventImpl(Container.java:2024)
    at java.awt.Component.dispatchEvent(Component.java:3819)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
    at java.awt.Container.dispatchEventImpl(Container.java:2010)
    at java.awt.Window.dispatchEventImpl(Window.java:1791)
    at java.awt.Component.dispatchEvent(Component.java:3819)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
    at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
Caused by: NoViableAltException(155@[()+ loopback of 193:17: (names+= ID )+])
    at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
    at org.antlr.runtime.DFA.predict(DFA.java:116)
    at org.tmatesoft.sqljet.core.internal.lang.SqlParser.type_name(SqlParser.java:4220)
    ... 40 more

Original comment by sergey.s...@gmail.com on 25 Sep 2009 at 11:31

GoogleCodeExporter commented 9 years ago

Very good point!

I've fixed in trunk several small bugs in our SQL schema handler and now your
attached code (MalformedDB.java) should works well. This fix should be surely
included into current branches/1.0.x.

Thank you.

Original comment by sergey.s...@gmail.com on 26 Sep 2009 at 1:39

Changed state: Fixed
Added labels: Milestone-Release1.0

GoogleCodeExporter commented 9 years ago

Original comment by sergey.s...@gmail.com on 26 Sep 2009 at 2:12

Added labels: trunk
Removed labels: Milestone-Release1.0

google-code-export / sqljet

SQLJet creates malformed databases #110