Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

On Mon, Feb 10, 2020 at 9:03 PM Richard Hipp  wrote:

> On 2/10/20, Digital Dog  wrote:
> > Maybe they should be treated as a
> > dictionary/hashtable/linked list or similar?
> >
>
>

> Parameter look-ups are on the critical path.  How much performance are
> you willing to give up in order to have parameters with larger
> numbers?
>
>
Thanks for enlighening again. It was just a thought. It seems it would be a
lot of design and code to maintain the performance while preventing the
original problem from happening. Not worth the trouble. But maybe
increasing the parameter count to 10.000 would not hurt?


> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

On Mon, Feb 10, 2020 at 8:27 PM Richard Hipp  wrote:

> On 2/10/20, Digital Dog  wrote:
> >
> > Nobody bothered to actually show the downside of increasing this value to
> > e.g. 10 thousands but everybody immediately proceeded to grumble.
> > What is the justifiable rationale to not change the default?
>
> The maximum number of variables used to be unlimited (or, at least,
> only limited by the ability of a 32-bit integer to count them).  But
> that was shown to facilitate a DOS attack.  Anyone who could inject
> SQL could do "SELECT ?2147483647;".  This would cause SQLite to
> allocate an array of 2147483648 elements, each of which is 72 bytes in
> size, in which to store all of the parameters, potentially using up
> all available memory in the process.  Even ?32767 uses a big chunk of
> heap memory - more than embedded systems people are comfortable giving
> up - especially if the attacker can trick the system into creating
> multiple prepared statements with ?32767 in them.  The allocated array
> lives for the life of the prepared statement.
>

Thanks, that makes a lot of sense. However maybe this needs rearchitecting,
so that only used keys are allocated, not all up to the one specified in
"SELECT ?2147483647".In this case I think SQLite should only allocate space
for this single parameter. Maybe they should be treated as a
dictionary/hashtable/linked list or similar?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Digital Dog]

Huh so the typical ranting for a valid use case has happened.

> Another alternative is to construct the command as a string.
begging for SQL injection. Thanks, no.

> all the suggestions and examples with temporary tables
and that's what you call easy for the programmer?

Nobody bothered to actually show the downside of increasing this value to
e.g. 10 thousands but everybody immediately proceeded to grumble.
What is the justifiable rationale to not change the default?

And while I'm on the line, I would like to ask for another change: to
enable GENERATE_SERIES by default in command line shell builds.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GENERATE_SERIES is not available in the command-line shell

[Digital Dog]

> > Is it going to be fixed in 3.31?
>
> I'll fix the documentation, so that it doesn't say that any more, if
> that is what you mean by "fixed".  :-)
>
> Hoped to have it compiled in by default :-) It'd be nice addition... Come
on, typical Linux or Windows has multiple gigabytes of memory... I'm not
talking about enabling it by default for resource-constrained builds.

Thanks for the response anyway.


> You can use a recursive common table expression in place of
> generated_series().  Like this:
>
> WITH generate_series(value) AS (
>   VALUES($START)
>   UNION ALL
>   SELECT value + $STEP FROM generate_series
> WHERE value $ STEP <= $STOP
> )
> SELECT value FROM generate_series;
>
> This is more typing, I know.  But it is the official SQL standard way
> of getting an ascending list of integers.
>
>
I would prefer more PostgreSQL'ish way of accomplishing the same :-)
It's crazy useful for ad-hoc one-liner queries to generate lots of data.
Have a good day!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] GENERATE_SERIES is not available in the command-line shell

[Digital Dog]

Hi!

In the docs here
https://www.sqlite.org/series.html
there's this statement "The generate_series(START,END,STEP) table-valued
function is a loadable extension included in the SQLite source tree, **and
compiled into the command-line shell.**" Unfortunately in version 3.30.1 on
Windows downloaded from SQLite.org it's not available by default.
Is it going to be fixed in 3.31?
Thanks!

C:\>sqlite3
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT value FROM generate_series(5,50);
Error: no such table: generate_series
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SLOW execution: Simple Query Uses More than 1 min

[Digital Dog]

For reasons which you've described I'm a big fan of removing virtual memory
from CPUs altogether. That would speed up things considerably.

On Sun, Dec 8, 2019 at 6:43 PM James K. Lowden 
wrote:

> On Sat, 7 Dec 2019 05:23:15 +
> Simon Slavin  wrote:
>
> > (Your operating system is allowed to do this.  Checking how much
> > memory is available for every malloc takes too much time.)
>
> Not really.  Consider that many (all?) operating systems before Linux
> that supported dynamic memory returned an error if the requested amount
> couldn't be supplied.  Some of those machines had 0.1% of the
> processing capacity, and yet managed to answer the question reasonably
> quickly.
>
> The origin of oversubscribed memory rather has its origins in the
> changed ratio of the speed of RAM to the speed of I/O, and the price of
> RAM.
>
> As RAM prices dropped, our machines got more RAM and the bigger
> applications that RAM supported.  As memory got faster, relatively, the
> disk (ipso facto) has gotten slower. Virtual memory -- the hallmark of
> the the VAX, 4 decades ago -- has become infeasibly slow both because
> the disk is relatively slower than it was, and because more is being
> demanded of it to support today's big-memory applications.  Swapping in
> Firefox, at 1 GB of memory, who knows why, is a much bigger deal than
> Eight Megabytes and Constantly Swapping.
>
> If too much paging makes the machine too slow (however measured) one
> solution is less paging.  One administrative lever is to constrain how
> much paging is possible by limiting the paging resource: swap space.
> However, limiting swap space may leave the machine underutilized,
> because many applications allocate memory they never use.
>
> Rather than prefer applications that use resources rationally or
> administer machines to prevent thrashing, the best-effort, least-effort
> answer was lazy allocation, and its infamous gap-toothed cousin, the
> OOM.
>
> Nothing technical mandates oversubscribed memory.  The problem, as
> ever, is not with the stars, but with ourselves.
>
> --jkl
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Missed index opportunities with sorting?

[Digital Dog]

On Sat, Dec 7, 2019 at 3:50 AM Simon Slavin  wrote:

> On 7 Dec 2019, at 2:26am, Shawn Wagner  wrote:
>
> > The first one uses the index for all sorting, but the second one only
> uses it for sorting a, not b. I feel like the descending sort could make
> use of the index too, just reading the b sections backwards to get the
> right order. Is there something I'm overlooking that would make this sort
> of optimization impractical or otherwise a bad idea?
>
> Hmm.  Try running ANALYZE and then doing the EXPLAIN QUERY PLAN lines
> again.
>
> But I think that without 'chunkiness' information (how many values columns
> a and b have) it would not be worth doing the complicated programming
> required for reverse-mini-scanning of that index.  The programming is quite
> complicated and unless your index "b" is chunky it won't save you much time
> over the plan shown.
>


So it's better to allocate memory, block the execution until all rows are
read and use cpu time to do unnecessary sorting that could easily be
avoided by just reading index backwards? Is it really so hard to program
it? I do not think so.

However the heuristic to decide when to do backward index scan needs to be
smart enough to select this only as last resort optimization, just before
falling back to explicit sort.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Entity Framework Core support

[Digital Dog]

On Mon, Nov 18, 2019 at 2:23 AM Joe Mistachkin 
wrote:

>
> Mike King wrote:
> >
> > "It needs to be clearly stated somewhere that EF 6.3 is meant only as a
> > tool for migrating from .NET Framework, and that EF Core is the version
> > that should be used." *2
> >
>
> Given the phrasing here, it sounds like this guidance is relatively new
> and/or unofficial at this point?
>
> >
> > . But
> > because it's a new code base, it also lacks some features that EF6 has."
> *3
> >
>
> To clarify, if it lacks features that EF6 has, it sounds a lot less like
> a porting effort and a lot more like a rewriting effort.  That does not
> necessarily mean it won't get done (at some point); however, that will
> certainly impact it's relative priority on the roadmap.
>
>
It's true it lacks some features, and it may never receive some
awkward/obscure features present in old EF. But it's officially the way to
go for the future multi-platform and container-hosted applications. The MS
roadmap is:
- .NET Core 3.1, ASP.NET Core 3.1 and Entity Framework 3.1 will be LTS and
it's the actively developed code
- the future versions of .NET are .NET 5, .NET 6 ... - and they will be
based on.NET Core
- .NET Framework 4.8 just released is the last version of .NET Framework
ever to be published
You may deny that but it's the fact and is not "unofficial", it's known in
.NET community for at least a year. It's also officially documented.

https://docs.microsoft.com/en-us/ef/ef6/:
"Consider using EF Core in new applications if the feature set matches your
requirements."
It's delicate wording for "both standard EF and .NET Framework on which
it's based are dead"

https://docs.microsoft.com/en-us/ef/efcore-and-ef6/index
"EF6 continues to be a supported product, and will continue to see bug
fixes and minor improvements."
Just like the above. "will continue to see ..." is a delicate wording for
"is dead".

"EF Core is a cross-platform product that runs on .NET Core."
"EF Core offers new features that won't be implemented in EF6"
It means: it's the way to go. Cross-platform and new features. Just read
between the lines.

https://devblogs.microsoft.com/dotnet/net-core-is-the-future-of-net/
".NET Framework 4.8 will be the last major version of .NET Framework. "
"New applications should be built on .NET Core. .NET Core is where future
investments in .NET will happen."

https://devblogs.microsoft.com/dotnet/introducing-net-5/
"Today, we’re announcing that the next release after .NET Core 3.0 will be
.NET 5"

So to summarize:
- EF Core SQLite driver is a must,
- to be portable between .NET Framework and .NET Core, the driver needs to
target neither of them - but instead something what is called .NET Standard
(https://docs.microsoft.com/en-us/dotnet/standard/net-standard) - the lower
version the better. If the driver can work with .NET Standard 1.6 then
it'll be compatible with older versions of many .NET implementations. If
it's not feasible, do target .NET Standard 2.0, it's the current sane
minimum.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Digital Dog]

On Wed, Nov 6, 2019 at 7:22 PM Jens Alfke  wrote:

>
>
> > On Nov 6, 2019, at 9:25 AM, Digital Dog  wrote:
> >
> > If there are millions or billions of rows
> > in the data set I consider it big data and the only reasonable format for
> > storing it is a column store format.
>
> There are many types of stores for "big data". My employer, Couchbase, has
> customers who would disagree with you; they use our document database to
> store millions or billions of rows.
>

I was talking about this specific data set with very well defined columns.
However I'm not sure if Parquet will be able to store this amount of
columns, or query them efficiently.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Limit on number of columns in SQLite table

[Digital Dog]

Hi!

I'm late to the party, but want to contribute. I did not read all messages
in the thread, but in those I've read did not contain a question about
number of ROWS. We know how many columns you desire, but how many rows are
there?

No matter how I like SQLite, I would not store this kind of data in it,
neither short term nor long term. If there are millions or billions of rows
in the data set I consider it big data and the only reasonable format for
storing it is a column store format. It gives up to 1:100 compression
(depending on the data of course) and incredible querying speeds (if it
doesn't have to read some columns it just doesn't do that). If it can skip
row groups altogether based on metadata, it will. None of row storage
engines will give you comparable efficiency. And especially because you do
not need transactional semantic, you can consider faster engines, tailored
to this use case (without WAL, locking or MVCC). The ones I can think of
now are: Apache Parquet, Cassandra and MonetDB, not to mention excellent
implementation and support in Microsoft SQL Server. You have to do your own
research on this subject.


On Thu, Oct 17, 2019 at 7:38 PM Mitar  wrote:

> ""Hi!
>
> On Thu, Oct 17, 2019 at 5:38 PM Jens Alfke  wrote:
> > Why should SQLite make changes, which would introduce performance
> problems if used, just to save your particular application the trouble of
> concatenating some vectors into single columns, when it uses SQLite for an
> edge use-case that’s pretty far removed from its main purpose?
>
> Then maybe section "File archive and/or data container" in
> "Appropriate Uses For SQLite" should explain that this is not the
> purpose of SQLite anymore. Because "SQLite is a good solution for any
> situation that requires bundling diverse content into a self-contained
> and self-describing package for shipment across a network." seem to
> work only when "diverse" is a table with less 2000 columns. Somehow
> describing a table with key/value columns can hardly be called
> self-describing.
>
> I am on purpose ironic, because I am not sure if talking about "main
> purpose" is really a constructive conversation here if there is a list
> of many "non-main" but still suggested use cases for SQLite. Not to
> mention the "Data analysis" use case, where again, if I am used to do
> analysis on datasets with many columns now would have to change the
> algorithms how I do my analysis to adapt to limited number of columns.
> It does not seem that putting vectors into single columns would really
> enable many "Data analysis" options inside SQLite. I am even surprised
> that it says "Many bioinformatics researchers use SQLite in this way."
> With limit on 2000 columns this is a very strange claim. I would love
> to see a reference here and see how they do that. I might learn
> something new.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3's vulnerability in 3.27.1 and 3.26

[Digital Dog]

I was able to reproduce this behaviour using much shorter query (in
sqlite.exe 3.27.1):

SELECT + sum(0) OVER() ORDER BY + sum(0) OVER();
SELECT + avg(0) OVER() ORDER BY + avg(0) OVER();
SELECT 1 + avg(0) OVER() ORDER BY 1 + avg(0) OVER();
SELECT - - - - - avg(0) OVER() ORDER BY - - - - - avg(0) OVER();

What's interesting it doesn't crash when arguments to aggregate are
different in SELECT and ORDER BY, or when there's different number of
pluses (but still at least 5) in both places.
These queries DO work:

SELECT + sum(0) OVER() ORDER BY + sum(1) OVER();
SELECT + sum(0) OVER() ORDER BY ++ sum(0) OVER();

Can't imagine how much time a fuzzer would need to find this peculiar
construct.

On Fri, Feb 22, 2019 at 7:23 PM 范龙飞  wrote:

> ?SELECT(+++
> last_insert_rowid()++sum(0)oVER())ORDER BY (+++
> last_insert_rowid()++sum(0)oVER())ORDER BY 1,1,1,1,1,1?
>
>
> Best regards
> Longfei Fan from 360 Codesafe Team of Legendsec?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Digital Dog]

On Mon, Jan 28, 2019 at 9:26 AM Vladimir Barbu <
vladimir.ba...@schneider-electric-dms.com> wrote:

> This vulnerability has been addressed in SQLite 3.26.0. When could we
> expect new version (official) of System.Data.SQLite which uses 3.26.0?
>

That would also make it much easier to use new features from .NET, such as
window functions and *a lot* of bugfixes since 3.24.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Digital Dog]

On Sun, Dec 16, 2018 at 9:26 PM Thomas Kurz  wrote:

> > Good way to overflow your integers.
> > With floating point, that's not a problem.
>
> With int64, it shouldn't be a problem either.
>
>
Well.. are you absolutely sure about that?

Considering these Zimbabwe Dollars amounts mentioned earlier, let's compare
max unsigned int64 with sample amount:

2^64
18446744073709551616
11

It seems int64 is easy overflown.

PostgreSQL's NUMERIC data type would be a solution here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Allow inclusion of generate_series function

[Digital Dog]

On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf  wrote:

>
> Why one would want a particular capability available in the shell that is
> not available when compiling the amalgamation code directly is beyond my
> ken.


The issue is not limited to generate_series (although that one seems to be
pain point for many users). The issue is that all sqlite downloads offered
on the project's home page could contain more extensions by default,
because they target capable platforms. Only some platforms these days are
restricted. Projects using these platforms require custom builds of sqlite
anyway.

Why I want fully-fledged sqlite shell? To be able to easily juggle data
straight from the OS shell, using one-liners, pipelines or bash scripts
without having to compile/enable/whatever my own sqlite shell. To just be
able to download sqlite3 executable and start working.

By offering sqlite shell with most (or all) extensions enabled, the authors
would increase out-of-the-box data processing abilities for all users who
decide to download and just use the shell command. The ability to download
and immediately start working with a really capable shell. Without risk of
bringing malware from other downloads. Without the hassle of rebuilding it
every time I need a new version. Without having to remember one or two
years later upon returning to a stuff that need changes that there was a
custom built sqlite shell there. Consistent across machines, because it was
just downloaded from official download site.

Yes, this is one of possible ways of thinking about consistency. I could
reverse your deduction - why should I want the feature parity between
sqlite embedded in an industrial device and sqlite running in Linux shell
script that gathers data from such devices, processes them differently than
the devices, aggregates them, compresses them and sends somewhere - that's
beyond my ken.


> I realize that consistency is the hobgobblin of little minds, but why
> would one not want the same extensions available when compiling the
> amalgamation into "some other" application but have that extension built
> into the shell (which is merely yet another application)?
>

I will not comment on that one. It's not the level of discussion this
mailing list expects.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Allow inclusion of generate_series function

[Digital Dog]

On Tue, Dec 4, 2018 at 5:57 PM Richard Hipp  wrote:
>
> On 12/4/18, Digital Dog  wrote:
> >
> > I vote for GENERATE_SERIES to be included in official sqlite3 binary and
> > libraries.
>
> We are under pressure to keep SQLite as small and compact as possible.
> We cannot go adding every feature that everyone requests without the
> size of the library growing out of control. Trade-offs have to be
> made.  We try to provide the ability for people who actually want lots
> of features to include them in their own builds. But we do no think it
> is appropriate to add things that grow the size of the library unless
> there is a compelling need.

Okay - keeping the core library small - that makes sense.

But for the Command Line tools for general purpose OS like Windows, Linux,
BSD, macOS which you offer for download? I'm not so sure. It could contain
more features because there are no restrictions. Windows, macOS, typical
Linux server or desktop won't work without gigabytes of RAM. Mobile devices
are joining the club. I think the scenario can be completely reversed -
majority of platforms is capable enough that they can include almost all
features, and only some, embedded, esoteric, have real constraints and
require trimmed feature set. I think they need to be custom-built anyway.

Or maybe it is possible to offer different builds - minimal shell and
fully-packed shell on the download page?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Allow inclusion of generate_series function

[Digital Dog]

On Thu, 25 Oct 2018 09:32:05 -0700 Nathan Green wrote:

> According to the online documentation (https://www.sqlite.org/series.html
),
> generate_series is compiled into the command line shell. As it turns out,
> this is not so. It is not even an option in the build system from what I
> can tell. It would be nice to at least have a build flag to enable it.

> I searched the list archives and noticed an email from late 2015 that
> indicates that generate_series was never actually a part of the shell.
> Apparently no one ever took up the task of making that happen. Would it be
> okay if I put together a patch for this? I would prefer it to be enabled
by
> default, but I can implement it either way.

Here it also doesn't work:

SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select * from generate_series(1,1000);
Error: no such table: generate_series

It's a pity because it's a very nice tally table and fast method of
generating sequential data, random data, intended number of rows etc. which
would be a useful addition in the shell one-liners or other sqlite scripts.
It's also useful for simple performance assessments.

I vote for GENERATE_SERIES to be included in official sqlite3 binary and
libraries.

Thanks in advance.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[Digital Dog]

On Mon, Nov 26, 2018 at 1:19 PM Richard Hipp  wrote:

> On 11/23/18, Digital Dog  wrote:
> > I think these changes would not add a lot of code to sqlite and will make
> > life easier for users.
>
> SQLite remembers the database schema by storing the original CREATE
> TABLE text in the sqlite_master table.  That means the CREATE TABLE is
> reparsed whenever a new database connection opens the database file.
> This can lead to compatibility problems if the meaning of a CREATE
> TABLE statement is somehow affected by PRAGMAs.


Thanks for explanation, now I can see it would also interfere with other
features, such as FTS, which creates some tables with rowids, and some
without, and would require adjustments, perhaps even adding new syntax
"WITH ROWID" or a lot of this PRAGMA juggling. At the beginning after
reading the docs I just thought it's worth suggesting fixing the old
behaviour, I'm backing off over this idea.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in Alter Table

[Digital Dog]

On Mon, Nov 26, 2018 at 2:52 AM Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset;
> Error: error in view CumulativeStatisticsByPlaceName after rename: no such
> column: StartGMTOffset
>
> I was able to reproduce this behaviour:

C:\temp>sqlite3 bug.sqlite
SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
sqlite> create table t1 (c1 int);
sqlite> create view v1 as select c1 from t1;
sqlite> create view v11 as select null from t1 left join v1 on v1.c1=t1.c1;
sqlite> alter table t1 rename column c1 to c2;
Error: error in view v11 after rename: no such column: v1.c1
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[Digital Dog]

> From: E.Pasma 

> Digital Dog wrote:
> >
> > PRAGMA default_without_rowid = on
> > To make all tables created while the directive is in use the WITHOUT
ROWID
> > tables.
> ..

> >
> > PRAGMA enforce_not_null_on_primary_key = on
> > For WITHOUT ROWID tables it would be a no-op, but for rowid tables it
would
> > restore correct behavior.

> Hello, just a remark about this second pragma.
> Enforce_not_null_on+primary_key seems obsolete to me, also for "rowid"
tables, that do not
> have a primary key defined.
> Because once the first suggested pragma (default_without_rowid) is set,
this rowid will be
> treated as a true integer primary key, including the NOT NULL property.
And this also applies
> if a table is defined with INTEGER PRIMARY KEY as an alias for roiwid.
> Does that leave only the first suggestion?

I may have phrased the subject in a wrong way which misguided you.

My idea is to be able to apply these pragmas separately, I mean a user can
opt-in to using just the second PRAGMA because for a valid reason rowid
tables are better choice for their database. This pragma would make
SQLite's behaviour consistent between rowid and WITHOUT_ROWID tables with
respect to handling primary key values (i.e. failing on NULL).
This setting obviously wouldn't do anything useful to tables without a
primary key. But if in the future SQLite authors implement ALTER TABLE ...
ADD CONSTRAINT ... PRIMARY KEY, then the setting will be taken into account
as well.

default_without_oid is a switch which now would allow changing old default
behaviour - i.e. I decide that I want all subsequently created tables in my
database to be WITHOUT OID without specifying that in the CREATE TABLE
script. It also gives the SQLite authors the ability to change default
value of this setting for new databases in the future, and then the
directive will be the way to restore old/deprecated (by then) way of
creating tables.

Thanks!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

[Digital Dog]

Hi! Welcome to the list.

I'm reading documentation on the topic:

"In an elegant system, all tables would behave as WITHOUT ROWID tables even
without the WITHOUT ROWID keyword".
then
"However, NOT NULL was not enforced on PRIMARY KEY columns by early
versions of SQLite due to a bug. By the time that this bug was discovered,
so many SQLite databases were already in circulation that the decision was
made not to fix this bug for fear of breaking compatibility."
at https://www.sqlite.org/withoutrowid.html

I suggest a possible solution to make it more elegant and more compliant.
There could be PRAGMA directives to opt-in for compliant behavior:

PRAGMA default_without_rowid = on
To make all tables created while the directive is in use the WITHOUT ROWID
tables.

PRAGMA enforce_not_null_on_primary_key = on
For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would
restore correct behavior.

I think these changes would not add a lot of code to sqlite and will make
life easier for users.

In spirit it would be similar to already existing
PRAGMA foreign_keys = on

(I also wanted to suggest the same to be able to select behaviour of NULL
values in unique index, but am too shy to do it).

Just a thought.

Thanks!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users