Question

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.

Answer 1

0

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 Kulakov
772●2●5

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

Subscription:

Related questions