SQLite Forum

Timeline
Login

20 most recent forum posts

2020-11-29
02:42 Reply: Proposal: Add the SUBSTRING alias to the SUBSTR function (artifact: 79aa74c0c8 user: jdennis)

I checked it by running the queries. Both the standard SUBSTRING (s FROM start [FOR length]) and the variant SUBSTRING(s,start[,length]) work, and return identical rows. I would call it a documentation bug, or if you like, an undocumented feature :-) This is in 10.2, I don't know what 10S reports as a version string.

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.

22:43 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: adec6fae47 user: LarryBrasfield)

Please excuse this interjection if it is too blindingly obvious.

There is no Git repository for which the SQLite developers take responsibility. The official project repository page has a link to Fossil, the DSCM tool which is used for the project (and was developed for it.) I think that if you can trace your difficulty to something in the Git settings for that tracking project, its maintainers might be interested in fixing what they have done or in documenting the appropriate Git user settings for their repo.

You might also consider using Fossil for the SQLite project. You will spend less time trail-blazing that way, and you may well find Fossil a pleasure to use.

21:56 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 550aa4e701 user: wyoung)

not sure what's the fossil behavior around EOL on Windows

Fossil doesn't have an "auto CRLF" setting: the data is the data.

Fossil will attempt to recognize files with CRLF line endings and offer to convert them to LF-only for you, but you can mark files as known to have CRLF line endings so Fossil won't bug you about them.

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

19:59 Post: Feature request: PostgreSQL aggregates (artifact: 4557e2061f user: anonymous)

Some of the features described in section 4.2.7 of PostgreSQL documentation are implemented in SQLite, such as the FILTER clause; my request is to also implement the ORDER BY clause for aggregate functions too (this would be useful with the built-in group_concat function, as well as for many user-defined aggregate functions).

17:43 Reply: Performance improvement in BEGIN EXCLUSIVE compared to BEGIN IMMEDIATE? (artifact: 4d87c2c51e user: anonymous)
@Dan,

Many thanks for your speedy and concise response. Much appreciated :)
15:27 Reply: Performance improvement in BEGIN EXCLUSIVE compared to BEGIN IMMEDIATE? (artifact: b7e1d5daae user: dan)

It will make no difference at all.

Transactions have to do all the same stuff regardless of whether they are started with BEGIN, BEGIN IMMEDIATE or BEGIN EXCLUSIVE. All that changes is exactly when they invoke the system calls to take the various required file locks.

14:59 Edit reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: ffa9e13aeb user: LarryBrasfield)

Looking at the last few published versions, release and prerelease, I see that lines are bounded in shell.c as is normal for *Nix systems, with a single ASCII linefeed character. (or \n in your parlance)

Looking at the shell.c produced on my Windows machine using the Fossil repository for SQLite, I see that its lines are bounded by a CR LF sequence (\r\n) as is normal on the platform.

This makes me wonder what strange combination of repository movement and build platform has led to your strange line bounds. Perhaps you should say, just in case this is actually a fixable problem rather than something quirky and unlikely to repeat under normal conditions.

[Added via later edit: ]

Because, obviously, you are causing shell.c to be built, I think it behooves you, as a developer considerate of those who might deal with this issue, to: State what platform you are using to build shell.c and the amalgamation; State what platform the source repository was copied from if not created on the build platform; State what version and build of TCL you are using; and what, if any, editing you have done to the various sources that are combined to form shell.c

I suggest also that you see whether your problem is restricted to just portions of your shell.c, arising from use of your own editor, or have somehow affected all line endings in shell.c .

I think that, until these issues are resolved, it is quite premature to put the word "BUG" in the thread title. If I had done that, I would remove it via the forum's edit feature.

13:43 Reply: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 47c7224ec6 user: LarryBrasfield)

Looking at the last few published versions, release and prerelease, I see that lines are bounded in shell.c as is normal for *Nix systems, with a single ASCII linefeed character. (or \n in your parlance)

Looking at the shell.c produced on my Windows machine using the Fossil repository for SQLite, I see that its lines are bounded by a CR LF sequence (\r\n) as is normal on the platform.

This makes me wonder what strange combination of repository movement and build platform has led to your strange line bounds. Perhaps you should say, just in case this is actually a fixable problem rather than something quirky and unlikely to repeat under normal conditions.

07:15 Post: Performance improvement in BEGIN EXCLUSIVE compared to BEGIN IMMEDIATE? (artifact: 37eabb3f20 user: anonymous)
According to the documentation for BEGIN TRANSACTION:


EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway.


My situation is that I am running on Windows NOT in WAL mode (journal mode=DELETE) - so the behaviour of BEGIN IMMEDIATE and BEGIN EXCLUSIVE should be different (according to my understanding of the document snippet above).

I currently execute a relatively large (typically approx 1 million rows across 5-10 tables) import process which is guaranteed (based on the design/architecture of my application) to run without any other processes or threads connecting at all to the database (so only a single connection running in a single thread has access to the database during the import) - this is already controlled and guaranteed (so this part is not the discussion point :) )

I  process the import volume in batches, each wrapped in their own transaction and currently use BEGIN IMMEDIATE to start the transactions.

Will there be any performance benefit (however minor) in changing to use BEGIN EXCLUSIVE on Windows .... or will it maybe be slower as maybe extra locking mechanisms are used to guarantee that only that connection has access to the database file?
06:11 Post: BUG: Wrong line endings of shell.c prevent debugging on Windows (artifact: 0f557bc8c5 user: anonymous)

It looks like shell.c ends up with \r\r\n for line endings which breaks debugging (lines don't match).

Unfortunately I don't know enough about Tcl to fix mkshellc.tcl.

sed -e 's/\r\r/\r/' does the trick as a temporary workaround.

04:34 Post: Feature Request: DML RETURNING clause (artifact: b61296925b user: soapboxcicero)

There's no standard that I'm aware of but it has support in postgres and mariadb (partial). Oracle and sql server have similar features in their procedural extensions.

Postgres compatibility would be my specific request: https://www.postgresql.org/docs/13/dml-returning.html

It doesn't really add any new capabilities but it does cut down on verbosity, especially when using a repl. I would think there are easy optimizations available, however, as the query and manipulation could be done in a single pass.

Apologies if this has been requested before. I couldn't find anything but it's not the most search-friendly term.

Oracle: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/returninginto_clause.htm

Sql server: https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

Mariadb:

00:58 Reply: NULL values for collation sequence (artifact: 079bbd0753 user: kmedcalf)

Collation sequences only apply to text. NULL is not valid text. A "null string" (that is, a string that does not contain anything) is actually a string and not NULL.

So it really matters what you mean by NULL.

A "null string" is a string (text) that does not contain anything. It is a string/text value, and that value is "empty" of content. Conversely NULL is a specific value that is of the NULL type. Collating sequences only apply to TEXT values.

2020-11-27
23:43 Reply: NULL values for collation sequence (artifact: 26b37054c7 user: mbenningfield1)

Outstanding. Thank you.

23:43 Reply: NULL values for collation sequence (artifact: 91efb5ee93 user: drh)

Can I positively assert that no NULL values will ever be passed to the collation sequence?

Yes. See line 1003 of main.c

23:42 Reply: NULL values for collation sequence (artifact: d68fb6dfda user: mbenningfield1)

In the old Nabble archive post, DRH states that NULLS always sort first, which is not specified in the SQL92 standard, but is left up to implementations.

I'm not asking how to collate NULL values, I'm asking if there is an explicit guarantee in the API that SQLite will always take care of sorting the NULLS first, and that a collation sequence will not have make sure to do so. That certainly appears to be the case.

More ↓