SQLite Forum

Timeline
Login

39 forum posts by user dfgriggs

2021-09-30
13:45 Reply: Feature request: sqlite CLI allow `.mode line` and `.mode quote` together (artifact: 5cb10fd859 user: dfgriggs)

Hi, Bokwoon,

You may well be aware of this, but for immediate relief, you can use hex():

   SELECT hex(site_id), domain, subdomain FROM sites;

To format the UUID, you can of course insert hyphens as in

https://stackoverflow.com/questions/47550860/is-it-possible-to-convert-a-sqlite-blob-column-to-a-hex-string-guid

If you're using standard formats, you might have to consider endianness and variants.

https://neosmart.net/blog/2018/converting-a-binary-blob-guid-to-text-in-sql/

2021-08-30
12:55 Reply: install sqlite on windows10 (artifact: 2791f5d2a8 user: dfgriggs)

Welcome to sqlite, Anonymous,

Sqlite itself (as perhaps you know) is not a user-facing program such as MS Access. It's a library for programmers to link into their programs.

That being said, a command-line interface program, named sqlite3.exe, is provided which is rather powerful.

One of the most-loved aspects of sqlite is its simplicity. There's no installation process per se. To get started with the command-line interface (CLI) go to the download page: https://www.sqlite.org/download.html and look for the Windows pre-compiled binaries. Download the "bundle of command-line tools" zip file. Extract (at least) the sqlite3.exe file into a directory on your machine -- either one within your PATH or directly into the folder where you wish to run it. Sqlite is so compact that its database logic is compiled directly into the sqlite3.exe file, and you're ready to start using it at the command line.

If you google "sqlite gui" you'll find that lots of folks (not part of this project) have written graphical Windows programs to work with sqlite, and you may decide to use one or more of these instead of the CLI. Some of these programs may expect you to download sqlite in the form of a "DLL" file -- just as you would if you want to write a program yourself. You'll find these DLL files on the download page as well. Programmers can also decide to compile sqlite themselves, either into their own programs or into customized DLL files.

Maybe this helps, Donald

2021-08-16
18:28 Reply: Ask for new feature: a pre-decoder mechanism for 'variables' and 'stdev' ? (artifact: 4cf65735f4 user: dfgriggs)

If I'm understanding 'pre-decoder stage' correctly, maybe one of these is relevant:

Sqlite now supports CTEs (WITH clause) which may help you concisely define a function. https://www.sqlite.org/lang_with.html

For basic statistics functions, note the contribution of extension-functions.c at https://sqlite.org/contrib/
or
https://github.com/nalgeon/sqlean/blob/0.4.0/docs/stats.md

Should you be using the CLI (command line interface utility program), it does indeed support named parameters. https://www.sqlite.org/cli.html#sql_parameters

2021-08-13
14:26 Reply: SQLite File size not increased with chunk size (artifact: ca66992544 user: dfgriggs)

You might consider:

  • Creating a dummy table.
  • Use a CTE to insert sufficient data to grow your database to a maximum anticipated size
  • Run vacuum
  • Drop the dummy table.
  • Possibly run a defragmentation utility on your disk as appropriate

Your database should now retain its size and low fragmentation.

Of course, even if you should exceed your new file size, nothing tragic happens.

If you are frequently creating new such databases, you can prepare a big empty database file in advance, and just copy it into place for each new one.

2021-08-12
13:44 Reply: SQLite irrespective query (artifact: a42e6e2bb7 user: dfgriggs)

Would the following simple WHERE clause do what's needed? (BTW, it will include those of unspecified gender who joined recently.)

SELECT shopper_first_name, shopper_surname,    shopper_email_address,IFNULL(gender, 'Not known' ) AS 'Gender',STRFTIME('%d- 
 %m-%Y', date_joined) AS 'date_joined', STRFTIME('%Y',date('now'))-
 STRFTIME('%Y',date(date_of_birth)) AS 'Age'
FROM shoppers
WHERE gender = 'F'
OR date_joined >= '2020-01-01' 
ORDER BY gender,Age DESC;
12:41 Reply: Test ACID properties SQLITE (artifact: 443f12e93f user: dfgriggs)

Regarding: "....125 copies per person "

One dose leaves you wanting more.

2021-08-06
14:28 Reply: Fail to calculate long expression (artifact: 5f5060cfc6 user: dfgriggs)

The differences seem to be in integer arithmetic, is that right?

If one makes all the numbers floating point, then sqlite (and, I suspect others) join the 8.2598e+12 crowd.

select (((((((788.)(8.46))))+8342. * 1.803 -1.))*4186.4 *(15.))(((22. /((7530. /((2.)*(((((25.))-421.))))))*597.)+7283.8 - 9.60+167.))+(8871.); 8259816920615.11

So is there any evidence that any of the listed software is not working as documented?

2021-07-14
19:08 Reply: can i custom index blob data? (artifact: 796297680e user: dfgriggs)

Hi Dave,

I'm not confident I understand what you're asking.

If you want to index and sort on data derived from your blobs, then unless space is in very short supply, could you not extract and manipulate this data, then store it in additional columns?

A generated column can even be virtual: https://www.sqlite.org/draft/gencol.html

Alternately, triggers can be used to keep the derivative data in sync with its mother blob.

2021-06-29
17:16 Reply: Is there a 64 bit sqlite3.exe available to avoid out of memory error? (artifact: 1ef82d9feb user: dfgriggs)

Compiling sqlite yourself gives you maximum configurability, but Nalgeon kindly hosts 64-bit CLI's at https://github.com/nalgeon/sqlite/releases/

and also packages some extensions together at: https://github.com/nalgeon/sqlean

2021-06-07
12:42 Reply: WAL files deleted (artifact: e23e360710 user: dfgriggs)

Doesn't appear to fail using the Windows version of 3.32.3 CLI (nor with current CLI)

k:\tmp>c:sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> CREATE TABLE t(a);
sqlite> .exit

k:\tmp>dir
 Volume in drive K is Windows
 Volume Serial Number is 3EB4-4EE0      
 Directory of k:\tmp
06/07/2021  08:34 AM    <DIR>          .
06/07/2021  08:34 AM    <DIR>          ..
06/07/2021  08:34 AM             8,192 test.db
               1 File(s)          8,192 bytes
               2 Dir(s)  107,722,104,832 bytes free
2021-05-26
13:10 Post: fyi, minor broken web link (artifact: 59fa69d198 user: dfgriggs)

I noticed that from the download page: https://www.sqlite.org/download.html

if I take the timeline link at the top: https://www.sqlite.org/src/timeline?bt=version-3.36.0&p=trunk:202105251610&n=all

I get a "not found: version-3.36.0" bad request page.

Not a problem for me, and I'm sure it will resolve once it's released; just FYI in case it's helpful

2021-05-20
18:16 Reply: How to import multi-line feeds? (artifact: ee605c5cfe user: dfgriggs)

I'm not aware of a method to set an arbitrary hex value for a field or record separator (though I could swear that strings were acceptable as separators in some ancient version of the CLI -- maybe I just dream in sqlite.)

I don't know if

.mode  ascii

would be of any use to you. 

It's described as:  "Columns/rows delimited by 0x1F and 0x1E"

A hack might be to use the CLI's file functions to read the import file into a single blob field in a temporary 1-row table, perform a REPLACE or two, then write it back out as your new import file.

https://sqlite.org/cli.html#file_i_o_functions

Someone here may have a better answer.

2021-05-13
15:20 Reply: Nothing for several days from the forum (artifact: 8c3169c009 user: dfgriggs)

There have been a number of postings, per: https://sqlite.org/forum/forum

I've gotten a number of emails, so you may want to check your subscription information and also check your email client for overactive spam detection.

2021-04-19
19:30 Reply: Administrator SQLite (artifact: 4c24c5139f user: dfgriggs)

They are commands to the windows command line, before you type SQLITE3, or, as Keith showed, by using the ".system" command to sqlite. From the windows command line, you can type:

      help color
to see your options. You can, of course, include "color" along with "sqlite3" commands in a batch file to start things off. An advantage of customizing the terminal is that if/when you exit sqlite3, you still have a reminder of the omnipotent capabilities of your windows prompt.

16:26 Edit reply: Administrator SQLite (artifact: a91a16fc3c user: dfgriggs)

One way you can denote the difference is to color your terminal windows differently from your default, e.g.

     color  1e

With multiple terminal windows open, that allows you to identify them at a glance.

You can of course combine this with something like:

     title   I am the Administrator, drunk on my tremendous power

if that helps.

16:22 Reply: Administrator SQLite (artifact: 330a421b8c user: dfgriggs)

One way you can denote the difference is to color your terminal windows differently from your default, e.g.

 color  1e

With multiple terminal windows open, that allows you to identify them at a glance.

You can of course combine this with something like:

title   I am the Administrator, drunk on my tremendous power

if that helps.

2021-03-25
13:15 Reply: Error when trying to add a column (Error checking foreign keys after table modification) (artifact: b9f6df3bf5 user: dfgriggs)

Hello, Zappfinger,

The db browser software isn't written or maintained by the sqlite team, so you'd want to find the maintainers of that software and give them a detailed minimal reproducible example.

2021-02-01
16:14 Post: tiny typo (artifact: e86df48e35 user: dfgriggs)

tiny typo at:

https://www.sqlite.org/pragma.html#pragma_analysis_limit

"...and subsequent ANALYZE commands will breaks off processing..."

Not a complaint; just fyi.

2021-01-04
23:11 Delete reply: Can not drop a table... (artifact: 23119912bd user: dfgriggs)
Deleted
22:53 Reply: Can not drop a table... (artifact: 048718aed4 user: dfgriggs)
It seems to work ok for me.  Windows 10, using pre-compiled binary for command line utility, version 4.34.0.

 

SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>     DROP TABLE IF EXISTS a;
sqlite>     CREATE TABLE a(ID INTEGER NOT NULL PRIMARY KEY);
sqlite>
sqlite>     DROP TABLE IF EXISTS b;
sqlite>     CREATE TABLE b(
   ...>         ID INTEGER NOT NULL PRIMARY KEY,
   ...>         c_ID INTEGER REFERENCES c(ID),
   ...>         a_ID INTEGER NOT NULL REFERENCES a(ID) ON DELETE CASCADE
   ...>       );
sqlite>
sqlite>     DROP TABLE a;
sqlite> .tables
b
sqlite>
2020-12-29
22:13 Reply: Suggestion: Use strong hashes on the download page (artifact: f1e3d72efa user: dfgriggs)

Another pre-compiled option for Windows is OpenSSL.

From page: https://wiki.openssl.org/index.php/Binaries

I took the link to:

http://wiki.overbyte.eu/wiki/index.php/ICS_Download#Download_OpenSSL_Binaries_.28required_for_SSL-enabled_components.29

The page asserts: The OpenSSL DLLs and EXE files are digitally code signed 'Open Source Developer, François PIETTE'

Example of use: openssl dgst -sha3-256 sqlite-tools-win32-x86-3340000.zip

2020-11-26
16:03 Reply: SQLite ODBC (artifact: 67d9b2ba59 user: dfgriggs)

Regarding:

"None of these have the feature that I need, namely: CLI enhancement: The .read dot-command now accepts a pipeline in addition to a filename."

I'm writing to say that the sqlite developers take backward compatibility very seriously, and since the feature only accrues to the CLI program, there should be no disadvantages to having "mixed versions" of sqlite on your machine -- in fact, it's typically what one has. (Just hold off on putting any new 3.34 SQL features in your non-CLI code, of course.)

As to obtaining the new CLI -- as others have said, you can compile it yourself immediately, or just wait 8 days and the official release should be available.

Donald

2020-10-31
16:12 Reply: SQLite: -tabs option is missing (artifact: a494876691 user: dfgriggs)

Not speaking to the merits of your suggestion directly, but wanted to be sure you're aware that the commandline interface program does easily let you pick a separator for input or output.

.separator \t

2020-10-28
22:33 Reply: Is `JSON` a valid `create table` type? (artifact: 9fcbfcd171 user: dfgriggs)

I think you'll find your answers in the documentation:

https://sqlite.org/quirks.html

https://sqlite.org/datatype3.html

2020-10-07
18:02 Reply: Performance Issue: How can I increase a performance? (artifact: f29c1a6fb7 user: dfgriggs)

Regarding: " It's just a SQLite command you execute like CREATE INDEX. "

I feel sure Simon was thinking of "EXPLAIN". For the sqlite analyzer visit the download page:

https://www.sqlite.org/download.html

and download the "bundle of command-line tools" for your machine, or compile from source as needed.

The analyzer is a separate program you'll run from your commandline ("terminal") as follows:

sqlite3_analyzer filespecOfYourDatabase >MyAnalyzeOutput.txt

Then post here the contents of MyAnalyzeOutput.txt, or link to it from somewhere like pastebin.com.

Unrelated question: The field "attributes" is labeled JSON. Is this field's contents always small?

2020-10-04
16:07 Reply: Alternative for nesting REPLACE()? (artifact: 1da2219a3d user: dfgriggs)

Bedsides Simon's good suggestion of writing a custom function, you might consider whether the expected rows would fit in cache, and, if so, a series of separate UPDATE's enclosed in a transaction might be sufficiently speedy.

-- or, a CTE can be used with a table of search/replace strings.

-- or would just formatting a big UPDATE not meet your need? I imagine a dozen replacements as something like:

UPDATE myTable SET data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( data, CHAR(13), '~' ) ,CHAR(10), '~' ) ,'division', 'unity' ) ,'fear', 'serenity' ) ,'greed', 'charity' ) ,'travesties', 'justice' ) ,'suspicion', 'empathy' ) ,'depression', 'joy' ) ,'hate', 'love' ) ,'poverty', 'sharing' ) ,'xenophobia', 'friendship') ,'copyright', 'blessing' ) ;

Donald

2020-09-29
13:12 Reply: Dump and online backupjexclude tables (artifact: 0260eca709 user: dfgriggs)

Since disk space is a concern with the OP, I wondered if a regimen of:

  1. Weekly full backup with output of .dump
  2. Daily incremental backup of .dump

would result (especially if compressed) in smaller disk requirements even if the fts table was included.

2020-09-18
18:48 Reply: How to write this trigger? (artifact: 8a4568c312 user: dfgriggs)

Hello, Anonymous,

  1. I think you want an AFTER trigger. See https://sqlite.org/lang_createtrigger.html#cautions_on_the_use_of_before_triggers
  2. I wonder if you wanted an UPDATE rather than a SELECT
  3. You don't want the trigger to alter the entire table with every insert, so you likely want a WHERE clause.
  4. AFAIK, you must write two triggers if they should fire on both INSERT and UPDATE.

In the example below, I used visible characters instead of CR/LF to avoid confusion. I inserted two rows before we even created the trigger -- just to prove we aren't updating the entire table with each firing.

SQLite version 3.32.3 2020-06-18 14:00:33 sqlite> CREATE TEMP TABLE t (notes TEXT); sqlite> /* Insert two rows before we create the trigger */ sqlite> INSERT INTO t VALUES ('CAFE'), ('BOBCAT');
sqlite> CREATE TRIGGER tgr AFTER insert on t /*changes 'CA' to 'WHA' */ ...> begin ...> UPDATE t SET notes = replace(new.notes, 'C'||'A', 'WHA') ...> WHERE ROWID = new.ROWID; ...> end; sqlite> sqlite> INSERT INTO t VALUES ('CAT'); sqlite> SELECT ROWID, * FROM t; 1|CAFE 2|BOBCAT 3|WHAT Maybe this helps, Donald

2020-09-15
14:07 Reply: Querying all compile options used (artifact: 780ceb472f user: dfgriggs)

Maybe just me, but I'm not understanding the question very well.   I know there's, e.g. a compile option of

    SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<pages>

that would have no effect unless at some point someone has invoked:

PRAGMA journal_mode = WAL; 

Is that what you mean? Maybe you could elaborate on the problem that caused you to pose the question?

  

2020-09-07
21:56 Post: possible documentation typos (artifact: 8276b574c0 user: dfgriggs)
  1. https://www.sqlite.org/compile.html For maximum database safety following a power lose

  2. https://www.sqlite.org/compile.html This option has no affect on the SQLite core (suggest effect)

  3. https://sqlite.org/floatingpoint.htm Title is: Floating Pointer Numbers

  4. Suggestion, not a typo, but if you're editing https://sqlite.org/floatingpoint.htm then around "If you need an exact answer, you should not use binary64..." might want to link down to the decimal.c extension on the same page.

Submitted 100% in the hopes of being helpful, and 0% in the way of complaint.

2020-08-17
17:54 Reply: strange strftime returns at julian day zero (artifact: 3cfbf774ca user: dfgriggs)

The beginning of the Julian day epoch is 4714 BC (Nov 24, noon Greenwich, Gregorian Proleptic calendar.)

From the datetime documentation, the "%Y" conversion is not defined for BCE years.

https://www.sqlite.org/lang_datefunc.html

%Y year: 0000-9999

2020-07-14
13:31 Reply: how to check update statement really modify a record (artifact: 481faf0b61 user: dfgriggs)

Hello,

Yes. https://www.sqlite.org/lang_corefunc.html#changes

2020-06-29
14:53 Reply: Can of worms - Easiest way to build a UI for Sqlite? (artifact: 5f21603bfd user: dfgriggs)

One possibility that allows forms and reports may be LibreOffice "Base" using the ODBC drivers of Christian Werner.

The instructions at the page below look promising:

https://thejeshgn.com/2018/08/23/using-sqlite-with-libreoffice-base-on-linux/

2020-06-11
19:03 Reply: .mode html fails on html content (artifact: 3392f0a349 user: dfgriggs)

This may be obvious and unhelpful, but for my need I just use

.mode list

And create my own HTML markup with:

UPDATE myTable set ln = '<a href="' || ln || '">' || ln || '</a><br />' WHERE ln like 'http%';

(Where "ln" is a text field with an entire line of output.)

2020-05-14
13:38 Reply: output in column mode is truncated (artifact: 68b799c55b user: dfgriggs)

And in case a spreadsheet isn't suitable for your needs, you're probably aware of the .width command in the command line shell.

See section 5 of: https://sqlite.org/cli.html

2020-04-10
11:15 Reply: Return boolean if item EXISTS in database (artifact: 127f27b773 user: dfgriggs)

You may already know that SQLite does not have a separate boolean type, and that typing, in general, differs from strongly-typed databases.

https://www.sqlite.org/datatype3.html#boolean_datatype

2020-04-08
22:52 Reply: Bug in ALTER TABLE ADD COLUMN (artifact: ffa5244727 user: dfgriggs)

But how would it matter in actual practice? The ALTER TABLE ADD COLUMN command is intended for situations in which the need for the new column was not foreseen during the initial design.

Of what real use would adding code to allow this corner case be of benefit -- and justify the added verification test and small expenditure of RAM?

2020-03-26
19:31 Reply: Searching www.sqlite.org options (artifact: 67f1bc2b45 user: dfgriggs)

Hi, Jose,

I can't speak to the search question, but if you go to the command line page, https://sqlite.org/cli.html

and do a FIND for .schema, you may find the information you need.

2020-03-18
16:35 Reply: Renaming a database (artifact: 3e62dce4e8 user: dfgriggs)

Re: "..I have a requirement to rename a database, and various disparate clients which might be using it."

Hi, Toby,

Are you at liberty to elaborate on this requirement? This list includes some really helpful folks who might be able to offer some alternate solutions.