DO 5.0.3, SQLite 1.0.87

I have added a view manually to a SQLite database that contains a recursive query (part of a much bigger query btw):

WITH RECURSIVE fn(x) AS (VALUES(2) UNION ALL SELECT x+1 FROM fn WHERE x<13) SELECT strftime('%Y%m', date('2013-11-01'), '+' || x || ' months') FiscalMonth FROM fn

When using DO to open the database, I get the following exception

System.Data.SQLite.SQLiteException was unhandled   HResult=-2147467259   Message=database disk image is malformed malformed database schema (MasertAccountPeriodsView) - near "fn": syntax error   Source=mscorlib   ErrorCode=11   StackTrace:
    Server stack trace: 
       at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
       at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
       at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at Xtensive.Sql.Drivers.Sqlite.v3.Extractor.ExtractTables()
       at Xtensive.Sql.Drivers.Sqlite.v3.Extractor.ExtractCatalogContents()
       at Xtensive.Sql.Drivers.Sqlite.v3.Extractor.ExtractSchema(String catalogName, String schemaName)
       at Xtensive.Sql.SqlDriver.Extract(SqlConnection connection, IEnumerable`1 tasks)
       at Xtensive.Orm.Providers.StorageDriver.Extract(SqlConnection connection, IEnumerable`1 tasks)
       at Xtensive.Orm.Providers.SqlExecutor.Extract(IEnumerable`1 tasks)
       at Xtensive.Orm.Upgrade.SqlWorker.ExtractSchema(UpgradeServiceAccessor services, ISqlExecutor executor)
       at Xtensive.Orm.Upgrade.SqlWorker.Run(UpgradeServiceAccessor services, SqlWorkerTask task)
       at Xtensive.Orm.Upgrade.SqlWorker.<>c__DisplayClass1.<Create>b__0()
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
       at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
       at System.Func`1.EndInvoke(IAsyncResult result)
       at Xtensive.Core.AsyncFutureResult`1.Get()
       at Xtensive.Orm.Upgrade.UpgradingDomainBuilder.CompleteSqlWorker()
       at Xtensive.Orm.Upgrade.UpgradingDomainBuilder.BuildMultistageDomain()
       at Xtensive.Orm.Upgrade.UpgradingDomainBuilder.Run()
       at Xtensive.Orm.Upgrade.UpgradingDomainBuilder.Build(DomainConfiguration configuration)
       at Xtensive.Orm.Domain.Build(DomainConfiguration configuration)
       at HP.Constants.get_Domain() in c:\xxxxxxxx.cs:line 1172
       at HP.Storage.OpenSession() in c:\xxxxxxxxxx.cs:line 126
       at TestHarness.Program.Main(String[] args) in c:\xxxxxxxxxx.cs:line 141
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart() InnerException:

Interestingly, if I open a SQLiteConnection manually - no issues when the connection opens, and if I then subsequently open a session with DO (while the manual SQLiteConnection is still open), no exception is raised.

asked Dec 14 '14 at 04:45

Craig%20Dunstan's gravatar image

Craig Dunstan
5222


One Answer:

Hello Craig Dunstan.

In some versions of SqLite views with incorrect definitions in select clause may damage all schema. Execute command .schema in command prompt client of SqLite for database. It must show all CREATE statements. Post response to comment. If response is too big then post just CREATE VIEW statements

answered Dec 15 '14 at 05:06

Alexey%20Kulakov's gravatar image

Alexey Kulakov
48215

Thanks for the prompt response. Below shows the issue.

create view AccountPeriodsView as select distinct MasterAccountId AMID, ma.Name AccountName, ma.State, FiscalPeriods.FiscalMonth, l.BusinessUnit, l.Id as LineId from MasterAccountAssignments maa join MasterAccounts ma on ma.Id = maa.MasterAccountId join ( WITH RECURSIVE fn(x) AS (VALUES(2) UNION ALL SELECT x+1 FROM fn WHERE x<13) SELECT strftime('%Y%m', date('2013-11-01'), '+' || x || ' months') FiscalMonth FROM fn
) FiscalPeriods join Lines l

Thanks in advance and apologies for brevity - limited characters!

(Dec 15 '14 at 05:54) Craig Dunstan Craig%20Dunstan's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×4

Asked: Dec 14 '14 at 04:45

Seen: 2,385 times

Last updated: Dec 15 '14 at 05:54

powered by OSQA