SQLite Forum

Timeline
Login

7 forum posts by user marwis

2020-12-03
13:18 Reply: Request: Conform to XDG Base Directory Specification for sqliterc configuration file (artifact: 98fa76e6d9 user: marwis)

I think in that case it should also follow Windows conventions on Windows :)

i.e. $AppData/SQLite/sqlliterc

01:14 Post: Automatic covering index for virtual tables (artifact: dffda14ca0 user: marwis)

I'm working on ESENT module for SQLite. So far I have implemented basic reading without index support.

I have noticed that joining two virtual tables that don't support indexing (xBestIndex sets estimatedCost=100000 and returns OK) will perform full scan on both, resulting in extremely slow queries for medium sized tables.

However if the same were to happen to unindexed temporary tables SQLite will create automatic covering index and use that to greatly speedup query.

CREATE VIRTUAL TABLE namespace USING esentvtab("F:\FileHistory\mwisn\MWSURFACE4\Configuration\Catalog1.edb", namespace);
CREATE VIRTUAL TABLE string USING esentvtab("F:\FileHistory\mwisn\MWSURFACE4\Configuration\Catalog1.edb", string);

CREATE TEMPORARY TABLE temp.namespace1 AS SELECT * FROM namespace;
CREATE TEMPORARY TABLE temp.string1 AS SELECT * FROM string;

EXPLAIN QUERY PLAN
SELECT namespace.id, string.string
FROM namespace
LEFT JOIN string ON parentId = string.id
LIMIT 10;

EXPLAIN QUERY PLAN
SELECT namespace1.id, string1.string
FROM namespace1
LEFT JOIN string1 ON parentId = string1.id
LIMIT 10;

Resulting query plans are as follows:

QUERY PLAN
|--SCAN TABLE namespace VIRTUAL TABLE INDEX 0:
`--SCAN TABLE string VIRTUAL TABLE INDEX 0:
QUERY PLAN
|--SCAN TABLE namespace1
`--SEARCH TABLE string1 USING AUTOMATIC COVERING INDEX (id=?)

Is there an easy way to add automatic index to virtual table?

I plan on implementing ESENT indexing later but this would be quick win.

2020-11-28
23:57 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: cd7557ae9f user: marwis)

Sounds like git's core.autocrlf=input. Makes much more sense. I'm always getting tripped by this when using new Windows dev machine :/

23:50 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 974b4421ed user: marwis)

Hence "self-inflicted" although it's worth pointing out the GitHub mirror I'm using is official.

I'm actually working on a sqlite extension in separate git repo but decided to setup sqlite dev environment to improve debugging experience and help me understand codebase better.

21:07 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: ba0a19f015 user: marwis)

My obviously self-imposed problem can be fixed by disabling EOL conversions in git (not sure what's the fossil behavior around EOL on Windows but I guess it does work).

git config --get core.autocrlf

# fixup existing data
git rm --cached -r .
git reset
git checkout .
20:49 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 4a35e8a91a user: marwis)

Hmm... could be related to my git settings:

the only one I have is:

core.autocrlf=true

I will try with different settings

20:44 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 1532ff426f user: marwis)

Apologies for lack of detail. I'm unable to edit original message as I've posted anonymously (would be nice if Fossil left a cookie to let me do that or claim the post).

Environment

  • Windows 10 20H2
  • Active code page (chcp): 437
  • magicsplat-tcl-tk 1.10.1.20201009
  • git 2.29.2.2
  • Visual Studio Community 2019 Version 16.8.2

Steps

# Developer Command Prompt for VS 2019
vsdevcmd
git clone https://github.com/sqlite/sqlite.git
cd sqlite
mkdir Debug
cd Debug
nmake /f ..\Makefile.msc TOP=..

At this point shell.c has mix of different EOL styles including a lot of the \r\r\n.

For actual debugging I'm importing project from existing code in VS under name 'sqlite3' and following build commands in sqlite3.vcxproj:

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|Win32'">
    <NMakeFlags>/f ..\Makefile.msc TOP=.. DEBUG=3 USE_AMALGAMATION=0 DYNAMIC_SHELL=0</NMakeFlags>
    <NMakeBuildCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags)</NMakeBuildCommandLine>
    <NMakeCleanCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags) clean</NMakeCleanCommandLine>
    <NMakeReBuildCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags) clean core</NMakeReBuildCommandLine>
    <NMakePreprocessorDefinitions>WIN32;_DEBUG;SQLITE_ENABLE_DESERIALIZE;$(NMakePreprocessorDefinitions)</NMakePreprocessorDefinitions>
    <NMakeIncludeSearchPath>$(OutDir);$(NMakeIncludeSearchPath)</NMakeIncludeSearchPath>
  </PropertyGroup>

and then simple F5 does the trick