Re: [sqlite] New word to replace "serverless"
standalone seems reasonable. To confuse things further, I have seen Sqlite embedded in an embedded web server, serverless doesn't fit that case. On Tue, Jan 28, 2020 at 9:45 AM Jose Isaias Cabrera wrote: > > > Richard Hipp, on Monday, January 27, 2020 05:18 PM, wrote... > > > > For many years I have described SQLite as being "serverless", as a way > > to distinguish it from the more traditional client/server design of > > RDBMSes. "Serverless" seemed like the natural term to use, as it > > seems to mean "without a server". > > > > But more recently, "serverless" has become a popular buzz-word that > > means "managed by my hosting provider rather than by me." Many > > readers have internalized this new marketing-driven meaning for > > "serverless" and are hence confused when they see my claim that > > "SQLite is serverless". > > It's kinda funny. Back in 2006 I needed to create an app with SQL but on a > local machine. MySQL was too big for the simple app, so, I wanted something > without a server. So, I actually searched on "serverless SQL engine", and > BOOOM!, sqlite.org came up. Now you want to take that away from me. :-) > ___ > 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] Causal profiling
> I’ve spent too much time lately trying to figure out or debug hellacious C > spaghetti code And I’ve spent too much time lately trying to figure out or debug hellacious C++ spaghetti code Someone who writes bad C, will write even worse C++ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash Bug Report
3.30.1 x86_64 Linux Same problem here. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
Number one on my wishlist. UNSIGNED Wanted for ordering more than anything else ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
In applications dates/times are input, dates/times are output. Commonly the storage format of dates/times is of no concern. More effort is often spent on time zone display and input, which is an application issue rather than a data store issue. (e.g. fossil) All one *needs* is database functions to input what you output (and vice versa), For me, the major benefit of a database "date/time" types is clarity for humans when reading the schema. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
Note https://www.sqlite.org/datatype3.html says: "Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions." This is partially true. The date and time functions will optionally parse 'a timezone indicator of the form "[+-]HH:MM" or just "Z"'. The same functions will not create this optional form. i.e. strftime is missing '%z' as a conversion specifier. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Affinity Performance - Non Issue?
Hi Stephen, I have a SQLite database that was ported from mysql. Most of the column definitions remain unchanged. There is lots of CHAR and VARCHAR definitions and contrary to Simon's response they mean what they say. 90+ % of the data is fixed length CHAR. (codes and fixed labels). When the database was originally converted to sqlite (around 3.7 era) the TEXT versus CHAR was benchmarked. We could not discern any difference on queries. Affinity is a great tool for keeping your schema portable. The grumblers have too much spare time on their hands. On Wed, May 15, 2019 at 2:51 AM Stephen Chrzanowski wrote: > > Hey all; > > I've seen it grumbled about before about giving a field a property of CHAR, > and have seen the correction that it should be TEXT. I understand that > SQLite doesn't really "care" what the contents of the field is, but, just > out of curiosity, is there a kind of performance hit on using CHAR versus > TEXT or INT versus INTEGER? > > Other than translating from one database engine to another, and with the > fact that SQLite doesn't care what the affinity is (Other than for internal > workings), I can't seem to think that there'd be a big hit since it needs > to determine what that value is supposed to be when reporting back to the > application. > ___ > 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
[sqlite] strftime et al
Whilst playing with fossil, I have encountered what I believe to be a
deficiency in strftime() (and friends).
As the Sqlite documentation states, dates supplied to the date
functions may have a timezone suffix i.e. "[+-]HH:MM" or just "Z".
The Sqlite input date parsing is faultless to my testing.
The catch is that there is no way to output what one inputs.
e.g. 2013-10-07 04:23:19 -04:00
There is no date_tzoffset('date') function or modifier to perform this task.
The 'utc', 'localtime' modifiers or no modifier enable one to
calculate an offset for a datetime (this offset is internally
calculated and can be calculated with some lengthy SQL).
The proposal:
1. Supply an additional '%z' substitution for strftime().
Without the 'localtime' modifier this will produce the string "Z".
With the 'localtime' modifier this will produce the string "[+-]HH:MM"
2. For the date(), time() and datetime() functions allow an additional
modifier (say 'tz').
This would change datetime() return value as follows:
select datetime('2019-04-21 08:03:07'); -- '2019-04-21 08:03:07'
select datetime('2019-04-21 08:03:07','tz'); -- '2019-04-21 08:03:07Z'
select datetime('2019-04-21 08:03:07','localtime'); -- '2019-04-21 18:03:07'
select datetime('2019-04-21 08:03:07','localtime','tz'); --
'2019-04-21 18:03:07+10:00'
similarly for date() and time().
This change would allow:
select datetime(datetime('now','localtime','tz'));
To produce a correct UTC datetime.
I have a patch.
Prompted by fossil date/time handling.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving CSV import
Agree with all that. > A way to skip a header row when the table exists would be useful. > How about > .header on/off ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .import fails CHECK constraint on valid data
>
> For good or bad, check constraints appear to be evaluated before this
>
conversion.
I call that very bad.
On Wed, Mar 20, 2019 at 7:58 AM Shawn Wagner
wrote:
> A manual INSERT demonstrates the same behavior, actually. Using your Tc
> table:
>
> sqlite> insert into Tc values ('12');
> Error: CHECK constraint failed: Tc
>
> The thing about .import is that, instead of guessing what type each value
> it reads is, they're all just bound to an insert statement as strings. When
> the row is actually stored in the table, those strings are converted to
> numeric types if the relevant columns have the appropriate affinity and it
> can be done losslessly. Details:
> https://www.sqlite.org/datatype3.html#type_affinity
>
> For good or bad, check constraints appear to be evaluated before this
> conversion.
>
> On Tue, Mar 19, 2019 at 1:43 PM James K. Lowden
> wrote:
>
> > On Sun, 10 Mar 2019 17:04:46 -0400
> > "James K. Lowden" wrote:
> >
> > > Why does the .import command cause the CHECK constraint to fail, when
> > > an ordinary INSERT does not?
> >
> > On Sun, 10 Mar 2019 14:12:33 -0700
> > Shawn Wagner wrote:
> >
> > > The check constraint is probably being evaluated (with t as a string)
> > > before any type conversion to match the column affinity is done.
> >
> > Does anyone have a better answer? Isn't .import supposed to work like
> > INSERT? If it doesn't, CHECK constraints for type safety are useless
> > for tables that are loaded from files.
> >
> > --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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import data into a temporary table
The big downside of the csv extension, is that no matter what you do your table ends up with every column with a type of text. A trap if you are you are using union/except/intersect clauses. On Fri, Mar 8, 2019 at 11:00 AM Keith Medcalf wrote: > > On Thursday, 7 March, 2019 14:45, Eric Tsau asked: > > >Is it possible to add the option of importing data into a temporary > >table? > > >Currently you have to create a temporary table first before importing > >to it, or having to drop the table afterwards. > > >.import dump.csv temp.table > >or > >.import dump.csv attach.table > > Apparently not ;) It would appear that the .import shell command does not > know how to create tables in schema's other than "main" > > However, you can create a virtual table in the temp database using the csv > extension (since .import only works from the sqlite3 shell) ... > > create virtual table temp.tablename using csv(filename=filename.csv, > header=yes); > > The virtual table will be deleted when the connection is closed ... since > it is only a temporary thing. You could then create a duplicate > materialized table if you wished (but since you have a table attached to a > csv file why would you want to do that?) > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > > > ___ > 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] Handling ROLLBACK
Does ROLLBACK release the transaction lock on the database ? I checked scripts on this. Yes. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling ROLLBACK
if I understand the question: On Sun, Mar 3, 2019 at 10:12 AM Simon Slavin wrote: > Does ROLLBACK release the transaction lock on the database ? > I think so or my code would not be working. > > Does ROLLBACK cancel the BEGIN ? Or do I need to issue END ? > END is a synonym for commit . So begin transaction; stuff commit OR rollback. rollback cancels the begin > > Suppose ROLLBACK does not cancel the BEGIN, can a programmer reliably issue more SQL commands, including another ROLLBACK ? Will SQLite continue to react correctly to other ROLLBACKs, and to SQL commands which result in "(516) SQLITE_ABORT_ROLLBACK". > > > If you think I've missed a relevant point, please don't hesitate to bring > it up. > ___ > 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] Building Amalgamation
Maybe not. See https://www.sqlite.org/compile.html#_options_to_omit_features to quote "Because of this, these options may only be used when the library is built from canonical source, not from the amalgamation <https://www.sqlite.org/amalgamation.html>" So the lists in the two places of the documentation are different. On Wed, Feb 13, 2019 at 7:01 PM Simon Slavin wrote: > On 13 Feb 2019, at 6:09am, D Burgess wrote: > > > 3.6.20 is a loong time ago. Which led me to think that maybe that list > is no longer be accurate. > > Is there an easy way to extract the available options for the latest > release? > > This is not something I normally worry about, but is the list here any > good ? > > <https://www.sqlite.org/compile.html> > > Simon. > ___ > 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] Building Amalgamation
Thanks Simon. I had previously read that. From that link it says: " The set of compile-time options that must be passed into the code generators can vary from one release of SQLite to the next, but at the time of this writing (circa SQLite 3.6.20, 2009-11-04) the set of options that must be known by the code generators includes:" 3.6.20 is a loong time ago. Which led me to think that maybe that list is no longer be accurate. Is there an easy way to extract the available options for the latest release? On Wed, Feb 13, 2019 at 5:02 PM Simon Slavin wrote: > On 13 Feb 2019, at 5:56am, D Burgess wrote: > > > Is there a definitive list of "defines" for building the amalgamation. > > You might mean this: > > <https://www.sqlite.org/howtocompile.html#building_the_amalgamation> > > If it's something else you want, post again and we'll try to help. > > Simon. > ___ > 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
[sqlite] Building Amalgamation
Is there a definitive list of "defines" for building the amalgamation. i.e. for the following two steps $ ./configure $ ./make sqlite3.c I build the Amalgamation because I need ENABLE_UPDATE_DELETE_LIMIT. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Min/Max and skip-scan optimizations
On Wed, Feb 6, 2019 at 11:26 AM Keith Medcalf wrote: "you have not normalized the data before storing it" This is true of most of the hundreds, if not thousands, of schema that I have seen. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.
There is also a problem with the documentation on csv.c Example from the source comments: CREATE VIRTUAL TABLE temp.csv2 USING csv( filename = "../http.log", schema = "CREATE TABLE x(date,ipaddr,url,referrer,userAgent)" ); It should be noted that the schema= parameter allows one to specify names, NOT types. Everything is set to a datatype of TEXT. Any datatype declarations are ignored. so with "CREATE VIRTUAL TABLE temp.csv2 USING csv( filename = "../http.log", schema = "CREATE TABLE x(anbr INTEGER ,ipaddr,url,referrer,userAgent)" ); typeof (anbr) is "text" ___ 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
> Banks still use, as they have for a very long time, Binary Coded > Decimal, or some equivalent that does not suffer from a loss of > accuracy, so all this foofaraw to do with floating point representation > of various amounts of currency does not apply to the real world. > > Cheers, > GaryB-) > As do insurance companies and many in the manufacturing world (inventory). There is a lot to like about BCD. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding CoC
> The CoC is fine. Don't change it. +1 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] weekday time modifier
select strftime('%Y-%m-%d %H:%M', '2018-09-23 8:59', 'localtime','weekday
0');
Also gets a bit confusing. The "weekday" operates on the UTC time.
On Sun, Sep 23, 2018 at 11:42 PM, Kevin Martin wrote:
> Hi,
>
> Not sure if this is me misreading it, but the description of the weekday
> modifier in the documentation seems a bit ambiguous.
>
> It says:
>
> > The "weekday" modifier advances the date forward to the next date where
> the weekday number is N. Sunday is 0, Monday is 1, and so forth.
>
> It is not clear what happens when the date before the modifier is already
> the correct weekday. I interpreted this as it would advance by a full week,
> but it does't, it leaves the date untouched:
>
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
> 2018-09-23
> sqlite>
>
> I seem the same behaviour in 3.24.
>
> Thanks,
> Kev
> ___
> 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] [EXTERNAL] shell csv import
Thank you E.Pasma, most elegant. Solves my problem. Thank you Rowan, I was trying to achieve it with /bin/sh (dash) On Tue, Sep 18, 2018 at 7:12 PM, E.Pasma wrote: > > > Rowan Worth wrote: > > > > You can also filter out specific messages at the shell level: > > > > sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the > > rest with NULL' >&2) > > > > But note that the >() syntax is not a POSIX sh feature, and will not work > > in a script using a shebang of #!/bin/sh. You need to change it to > > #!/bin/bash or whatever shell you have on hand. For more info see the > > "Process Substition" section of the bash man page. > > > > If you have the ability to modify the generated SQL, presumably you could > > avoid the error by generating a NULL yourself for the missing column? > > -Rowan > I replied just before reading this. This solution may be preferred. > > > ___ > 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] [EXTERNAL] shell csv import
Thanks hick. But that's not the problem. The import will always write to stderr, it's not an error Lots of other stuff in the script and I want to be able to catch any errors in the other parts of the script. On Tue, Sep 18, 2018 at 4:20 PM, Hick Gunter wrote: > When running a script from the shell, you can redirect stderr tot he null > device using 2>/dev/null or to the same destination as stdout using 2>&1. > The latter is also very useful in crontab entries, as neglecting to handle > stderr will result in an email tot he user that contains anything written > there > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von D Burgess > Gesendet: Dienstag, 18. September 2018 08:15 > An: sqlite-users@mailinglists.sqlite.org > Betreff: [EXTERNAL] [sqlite] shell csv import > > I have a script that loads csv into an existing table. > > I get this message on stderr for each row imported: > > "... expected 7 columns but found 6 - filling the rest with NULL" > > > We have the means to send stdout to /dev/null using the .once or .output > > Is there a way to send suppress stderr messages for a dot command? > > If not, can we have one? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > 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
[sqlite] shell csv import
I have a script that loads csv into an existing table. I get this message on stderr for each row imported: "... expected 7 columns but found 6 - filling the rest with NULL" We have the means to send stdout to /dev/null using the .once or .output Is there a way to send suppress stderr messages for a dot command? If not, can we have one? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unsigned
The original question was that I was curious about the history. Noting where we are now at, I will give as examples of two real world applications: 1. 32 bit embedded sqlite. Realtime storing data from various hardware interfaces. The data includes unsigned 32 bit integers which are stored as float (don't ask me why, I guess the primary reason is the same reason that 32bit Lua uses floats as integers). There is heavy (unsigned) arithmetic computation at database read/write time. The application is stable. The maintainers/developers rue the additional code because of sqlite and no native unsigned type. They have a historic codebase for 3 other database systems which they have previously used. 2. Mixed 64/32 bit system that has integers that use the full 64 bits. Numbers are sourced by realtime hardware. Absence of 64 bit unsigned means addition of few functions to handle inserts and display representation(s), numbers stored as text/blobs. Again this all works, just extra code and indexes are less than optimum (compared to previous used mysql). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unsigned
To answer Jens - electronic IDs. And yes I use bignums on the client side. Note that I have workarounds and the system I have is stable and works, I just rue not having 64 bits and UNSIGNED indexes. There was additional work to get it all going in SQLite. I probably would not have originally chosen SQLite if I had known what I know now. The reason for the original question was that a friend in a different industry asked me the same question. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unsigned
I currently store them as blobs. A lot of them, 16 bytes (versus numeric 8 per item). And not optimal for indexes. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unsigned
> You can just store binary blobs and interpret then in the client, no? Or do you need to do arithmetic on them? Not arithmetic, but &, |, <<, >> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unsigned
My problem is getting handling unsigned integers that have the high
bit set (i.e. negative)
(assume 64bit)
if I insert 0x8000 (i.e. 9223372036854775808), I would
like to be able to select and get the same unsigned decimal number
back.
select 0x8000,cast(9223372036854775808 as
integer),printf('%lu %ld 0x%0X 0x%0X',
0x8000,0x8000,0x8000,9223372036854775808);
-9223372036854775808|9223372036854775807|9223372036854775808
-9223372036854775808 0x8000 0x7FFF
The above select shows the issues.
On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth wrote:
> sqlite is pretty loose about types. The column definitions don't constrain
> what is stored in the rows at all:
>
> sqlite> CREATE TABLE a(c INTEGER);
> sqlite> INSERT INTO a VALUES ("fourty-two");
> sqlite> SELECT * FROM a;
> fourty-two
>
> So "UNSIGNED" seems kind of pointless as it's implies a further constraint
> which is not going to be honoured. Note that sqlite does support actual
> constraints via the CHECK clause:
>
> sqlite> CREATE TABLE b(c INTEGER, CHECK (c >= 0));
> sqlite> INSERT INTO b VALUES (-15);
> Error: constraint failed
> sqlite> INSERT INTO b VALUES (15);
>
> Although this is still allowed:
>
> sqlite> INSERT INTO b VALUES ("twenty");
> sqlite> SELECT * FROM b;
> 15
> twenty
>
> You can disallow it if you get even more specific:
>
> sqlite> CREATE TABLE b2(c INTEGER, CHECK (TYPEOF(c) == 'integer' AND c >=
> 0));
> sqlite> INSERT INTO b2 VALUES ("twenty");
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (0);
> sqlite> INSERT INTO b2 VALUES (-1);
> Error: constraint failed
> sqlite> INSERT INTO b2 VALUES (1);
> sqlite> SELECT * FROM b2;
> 0
> 1
>
> Note that the type in the column definition does have an effect - it
> defines the column's "affinity" and may change the way data is stored. For
> example:
>
> sqlite> INSERT INTO b2 VALUES ("2");
> Error: constraint failed
>
> The TYPEOF check rejects this, but without that constraint:
>
> sqlite> INSERT INTO b VALUES ("2");
> sqlite> SELECT c, TYPEOF(c) FROM b;
> 15|integer
> twenty|text
> 2|integer
>
> ie. the text data we tried to insert was converted to an integer for
> storage.
>
> Further reading: https://www.sqlite.org/datatype3.html
>
> -Rowan
>
>
> On 21 August 2018 at 14:46, D Burgess wrote:
>
>> Is there a historical reason why sqlite does not have a UNSIGNED type
>> to go with INTEGER?
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unsigned
Is there a historical reason why sqlite does not have a UNSIGNED type to go with INTEGER? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Quick way to figure SQLite database block size per table & indexes
download sqlite3_analyzer from http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange wrote: > Hi, > > Is there a quick way (as in speed) to obtain the number of database blocks > (or kilobytes) allocated for each table and each index ? > > I have been using various manual approaches so far (from using length() to > vacuum and drops), but none of them are really fast > or can practical to automate. > > Eric > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange wrote: > Hi, > > Is there a quick way (as in speed) to obtain the number of database blocks > (or kilobytes) allocated for each table and each index ? > > I have been using various manual approaches so far (from using length() to > vacuum and drops), but none of them are really fast > or can practical to automate. > > Eric > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- D Burgess ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficiency of partial indexes
On the systems I use, for log files, you cant beat a text file for integrity and speed. I would suffer the slower queries and use something like the CSV extension on your text log files. On Mon, Jul 30, 2018 at 10:44 AM, Simon Slavin wrote: > I have a particular logging task which is time-critical, both in reading and > writing. It runs on Linux, but not a fast desktop computer, more like the > slow kind built into your WiFi router, with limited solid state storage. I > can use any version of the SQLite C API I want, and currently use a minimal > build which omits almost all optional parts. Remember: on this setup > processing is slow, storage space limited. > > Log entries are written from six a minute (peak time) to one an hour (night). > In some use-cases queries will be frequent (a few a day). In others, users > are not interested and weeks may pass without a query. It has the age-old > problem when you need random access to the data: > > A) Create no indexes. Fast writing, but slow when looking things up. > B) Create indexes. Slower writing, but faster when looking things up. > > Naturally, I want it all. I'd been toying with the idea that initial writing > should be to a text file, and data flushed to SQLite just before a query is > executed. But a recent SQLite innovation changes things. Instead of using a > text file I can use partial indexes. > > So I add a column to my table called "searchable". It starts off set to > FALSE. I replace my indexes with partial indexes which count only > "searchable" rows. When a search is done, before actually doing the search I > do > >UPDATE MyTable SET searchable = TRUE > > This, theoretically, updates the indexes. Does anyone have experience with > this ? The programming is simpler if I use this trick, since I don't have to > handle and flush a text file. But I haven't used partial indexes before. > Any advice or suggestions before I do testing ? Is there a better way I've > missed entirely ? > > Simon. > ___ > 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
