SQLite Forum

Timeline
Login

50 forum posts by user cuz occurring on or before 2021-08-08 20:32:12.

More ↑
2021-08-08
20:32 Reply: Best practice: Save partial dates (artifact: 0a3ed73ccd user: cuz)

While there might be some sense to this suggestion in terms of data handling-ability (and perhaps prettiness?), it is cumbersome, requires intermediate encoding/decoding and will be orders of magnitude slower. JSON (and other object orientated storage types) are typically more sensible precisely where form and format varies, and in this case the format is very much set in stone by contrast.

2021-08-04
20:33 Reply: Calculating Trip Duration in Minutes? (artifact: 5d370848ce user: cuz)

This is incorrect... EXACTLY...

Yes, I am well aware of that. It was an ill-formed joke, I have a habit of referring to absolutes by using ironic language such as "average" or "standard" or "government issue".

I was going to continue tongue-in-cheek about how there are many types of days and UT1 vs. UTC and all the astronomy days, but that would be disingenuous as that is not what I intended, my meaning was strictly to time as understood by the Julianday calculation, which is absolute in dayspan.

I realised after your reply that not only was my intent not obvious, it might well be misleading to someone else reading this, and for that reason I apologize and will watch my mouth on this in most of the multiple possible futures.

Do not fear though, I am rather decisively on your side of that fence. ;)

19:49 Reply: Calculating Trip Duration in Minutes? (artifact: a94da99257 user: cuz)

"Julianday" is a representation/measurement in days or fractions thereof.

So there are 24 hours in a day, 1440 minutes in a day and 86400 seconds in the average day.

If you have a factor in "days" (or fractions thereof) then to translate it to hours requires you multiplying it by 24, for minutes it is * 1440 and for seconds it is * 86400.

2021-07-30
21:17 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. (artifact: 8872bab984 user: cuz)

There's already been some correct replies, I'll maybe add that adding 1 in an Int64 number could potentially scale by orders of magnitude in its FP counterpart, so no, it is never possible to be replaced just as-is - to say nothing of the difficulty of making it AUTOINCREMENT.

However, if it is FP primary keys you want, there is already a mechanism for that:

CREATE TABLE fptable(
  ID REAL NOT NULL PRIMARY KEY,

  ... 

) WITHOUT ROWID;

That's a 100% correct replacement of a int row-id with a float. No shenanigans, no interpreting, no casting, no problems.

21:04 Reply: Round function returning -0.0 (artifact: 4ecb18062a user: cuz)

Did you just call him a minuscist?

20:57 Reply: Abbreviation (artifact: 9f3c5703a6 user: cuz)

I can kinda roll with your first suggestion, but for your second, though it makes sense when considering simply the text you posted, thinking of "what even is a window statement", your all rows, or the origin ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, is essentially the antithesis of what a window statement tries to achieve, that all-inclusive of a window is principally just a full JOIN.

So while your text is shorter, there is nearly never a reason to use it if your intent is to use window queries.

I'm very willing to be corrected though, even eager to see a reasonable example or use-case. If you can show me a query with a window statement like that which produces useful results that cannot be produced by a normal query, perhaps with a sub-query or JOIN, then I would certainly concede you have a point.

2021-07-27
15:16 Edit reply: Need 1.0.110.0 x64 Download (artifact: 602e07ed6b user: cuz)

These are all available from system.data.sqlite.org, at least, for the versions that do exist. You might need to use the closest matching.

See: system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

See the options in the lower section of the page, near the other .NET static packages.

15:14 Reply: Need 1.0.110.0 x64 Download (artifact: a1055aa002 user: cuz)

These are all available from system.data.sqlite.org, at least, for the versions that do exist. You might need to use the closest matching.

See: system.data.sqlite.org/index.html/doc/e0418c2d52/www/downloads.wiki

See the options in the lower section of the page, near the other .NET static packages.

2021-07-26
09:13 Reply: sqlite3_carray_bind and constness (artifact: 14a0b4daf9 user: cuz)

On a more serious note, I get your sentiment with that reply, and as a Scientist I do like a healthy dollop of self-doubt dished up by people I trust.

I am however not grasping your idea fully, or to better explain - whilst claiming pure truth for everything in dispute is fundamentalism, saying "this is true" or "this is false" when it has been shown to be empirically so, is absolutely necessary, and ascribing variability to that which has none, is dishonest.

That is where you end up with people starting to talk about such things as "it is my truth" and "Nobody knows 100% for sure that gravity is real" or "I have made a version of bubble-sort that is quicker than Merge-sort".

In short, when it is so, there is nothing wrong with saying it is so, and when it isn't so, saying it is so on a public forum will (and should) produce exactly the kind of medicine that is needed to remedy such delusion.

One is always better off tackling/attacking the subject of what is said to be just so, rather than attacking the habit of saying things are just so, for the latter is doctrine and not argument.

08:53 Reply: sqlite3_carray_bind and constness (artifact: b32a87b4c5 user: cuz)

...a good sign you get conformation bias.

My new favourite word for sheeple: People with "conformation bias". :)

2021-07-24
19:52 Reply: Changing from MySQL to SQlite (artifact: 90fcc74eb9 user: cuz)

Lots of SQLite experts here, and quite possibly a few PHP aficionados, but you are bound to get a much broader and possibly better response by asking this on a PHP forum.

2021-07-23
11:37 Reply: how can i identify the data is just inserted into the database or updated the existed item when i use upsert-clause (artifact: 230045404d user: cuz)

Probably because the upserts turn into something like individual updates internally where needed.

There's also a total-changes api, the real name escapes me now and I'm not where I can easily check it, it might be sqlite3_total_changes().

I think it is connection/session based, so the idea is to check it before and after the query to establish the changes.

I did not test this or know it for sure, it's just what came to mind reading your question. If you do try it, kindly let us know whether it worked or not.

2021-07-22
16:04 Reply: Csv or Vsv parsing blob column (artifact: 69aa3ca667 user: cuz)

Simon is correct about the CSV capability you've seen being in the CLI, not the library itself.

You may be able to write the field to file and then use the library's CSV import capabilities on it, but somewhere some coding is going to get involved, so you may as well just use a CSV importer or library right in your code from the get-go to pack the csv into the tables you wish. This will give you more control too.

2021-07-21
20:09 Reply: SELECT optimization for constant expression (artifact: cdc8efd3f2 user: cuz)

The name that comes to mind for this trick is: "Brute Forced Optimization". :)

11:24 Reply: SELECT optimization for constant expression (artifact: fdcfff048d user: cuz)

Nevermind, I am ignorant :)

The firs query's conditions are additive. Ignore that, I will look into a different solution.

11:03 Reply: SELECT optimization for constant expression (artifact: 10c25d97ea user: cuz)

I might be ignorant, but is not this query:

SELECT * FROM person
WHERE (?1 IS NULL OR firstname LIKE ?1)
AND (?2 IS NULL OR lastname LIKE ?2)
AND (?3 IS NULL OR birthdate == ?3)
AND (?4 IS NULL OR maidenname LIKE ?4)
ORDER BY lastname, firstname;

the same as this more appropriate should-be-index-using query:

SELECT * FROM person
 WHERE firstname LIKE ?1
    OR lastname LIKE ?2
    OR birthdate == ?3
    OR maidenname LIKE ?4
    OR (?1 IS NULL AND ?2 IS NULL AND ?3 IS NULL AND ?4 IS NULL)
ORDER BY lastname, firstname;

You could even leave out the final OR based on whether you would want to show No rows when no filters are set, or All rows in that case.

I have not tested the assumptions here, so I could be wrong about it.

2021-07-20
14:45 Reply: ROWID in compound SELECT (artifact: cec8eb3f47 user: cuz)

Row-id aliases have always worked and still works, and is the only good way to do anything "by rowid".

It's not good SQL to refer stuff in SORT BY clauses, or any other, that are not explicitly defined in your schema, and that SQLite even allows this is a specific peculiarity. It did however cause problems, especially in subqueries and views, which I believe is why the change was made.

You /can/ compile a version that allows it still, but honestly my advice is to change your queries or schemata and use explicit aliases (you did demonstrated a clear understanding of that mechanism).

2021-07-19
12:52 Reply: Populating a tree from a flat table of subcategories (artifact: 3bff990360 user: cuz)

It is quite hard to match that exact output structure, plus I'm not sure if you were just lazy when making the example or I am understanding it wrong, but the tree data does not evaluate to your example. I will accept your statement of "...to be kind of:" to mean that you were not being accurate.

That said, here is a script that achieves it (final query below) if my understanding of your request is correct. If it isn't, please say how exactly and give an example that displays accurate logic.

Since you indicated wanting to learn and not simply have a result - I have included several interim queries (including some unnecessary fields) to show what each step of the final query's CTE achieves. Please ask if anything is unclear, and good luck!

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
  -- ================================================================================

CREATE TABLE "PagesTreeTT1" (
  "ID_Page" INTEGER,
  "TxtID_Page" TEXT(25) NOT NULL,
  "ParentPage_Id" INTEGER,
  "PgSlug" TEXT(120) NOT NULL,
  "PgDescr" TEXT(200),
  "PgSort" integer(2) DEFAULT 50,
  PRIMARY KEY ("ID_Page")
);

INSERT INTO PagesTreeTT1(ID_Page, TxtID_Page, ParentPage_Id, PgSlug, PgDescr, PgSort) VALUES
 (  2, 'HistRchch  ' , NULL , 'h'  , 'Histoire d''une recherche'  ,  10 )
,(  3, 'TrvLieu    ' , NULL , 't'  , 'Trouver un lieu'            ,  10 )
,(  4, 'GDN_       ' , 2    , 'g'  , 'Gedinne - et rien d''autre' ,  20 )
,(  5, 'gTrv       ' , 2    , 'j'  , 'J''ai trouvé'               ,  30 )
,(  6, 'dots       ' , 2    , '.'  , '...'                        ,  40 )
,(  7, 'decouvert  ' , 2    , 'd'  , 'Découverte'                 ,  20 )
,(  8, 'testAccent1' , NULL , 'a'  , 'aaa éàçè'                   ,  10 )
,(  9, 'testAccent2' , 7    , 'b'  , 'bbb &$^~'                   ,  20 )
,( 10, 'SsLaForge  ' , 7    , 's'  , 'Sous la Forge - Copinette-' ,  30 )
,( 11, 'inaug      ' , 7    , 'i'  , 'Inauguration'               ,  40 )
,( 12, 'chantier_  ' , NULL , 'u'  , 'Un chantier?'               ,  50 )
,( 13, 'autour     ' , NULL , 'e'  , 'Et autour de ce site'       ,  60 )
,( 14, 'infos      ' , NULL , 'ii' , 'Infos utiles'               ,  70 )
,( 15, 'WillVlg    ' , 14   , 'w'  , 'Willerzie (village)'        ,  10 )
,( 16, 'GdnCmn     ' , 14   , 'gg' , 'Gedinne (Commune)'          ,  20 )
,( 17, 'commerces  ' , 14   , 'c'  , 'Commerces'                  ,  30 )
,( 18, 'services   ' , 14   , 's'  , 'Services'                   ,  40 )
,( 19, 'VieLocale- ' , 14   , 'v'  , 'Vie locale et régionale'    ,  50 )
,( 20, 'SocioEco   ' , 19   , 'ss' , 'Socio-économie'             ,  10 )
,( 21, 'culturel   ' , 19   , 'cc' , 'Culturel'                   ,  20 )
,( 22, 'politik    ' , 19   , 'p'  , 'Politique'                  ,  30 )
,( 23, 'patrimn    ' , 19   , 'pp' , 'Patrimoine'                 ,  40 )
,( 24, 'nature     ' , 19   , 'n'  , 'Nature'                     ,  50 )
,( 25, 'about      ' , NULL , 'a'  , 'A propos'                   ,  95 )
,( 26, 'hebergt    ' , 18   , 'hh' , 'Hébergements'               ,  10 )
,( 27, 'restaur    ' , 18   , 'r'  , 'Restauration'               ,  20 )
,( 28, 'SportAct   ' , 18   , 'sa' , 'Sports - Activités'         ,  30 )
,( 29, 'brol       ' , 18   , 'bb' , 'brol'                       ,  35 )
,( 30, 'bril       ' , 18   , 'bbb', 'bril'                       ,  36 )

SELECT *
  FROM PagesTreeTT1
;

  --             |             |ParentPa-|      |                            |      
  --    ID_Page  |TxtID_Page   |ge_Id    |PgSlug|PgDescr                     |PgSort
  -- ------------|-------------|---------|------|----------------------------|------
  --       2     |HistRchch    |NULL     |   h  |Histoire d'une recherche    |  10  
  --       3     |TrvLieu      |NULL     |   t  |Trouver un lieu             |  10  
  --       4     |GDN_         |2        |   g  |Gedinne - et rien d'autre   |  20  
  --       5     |gTrv         |2        |   j  |J'ai trouvé                 |  30  
  --       6     |dots         |2        |   .  |...                         |  40  
  --       7     |decouvert    |2        |   d  |Découverte                  |  20  
  --       8     |testAccent1  |NULL     |   a  |aaa éàçè                    |  10  
  --       9     |testAccent2  |7        |   b  |bbb &$^~                    |  20  
  --      10     |SsLaForge    |7        |   s  |Sous la Forge - Copinette-  |  30  
  --      11     |inaug        |7        |   i  |Inauguration                |  40  
  --      12     |chantier_    |NULL     |   u  |Un chantier?                |  50  
  --      13     |autour       |NULL     |   e  |Et autour de ce site        |  60  
  --      14     |infos        |NULL     |  ii  |Infos utiles                |  70  
  --      15     |WillVlg      |14       |   w  |Willerzie (village)         |  10  
  --      16     |GdnCmn       |14       |  gg  |Gedinne (Commune)           |  20  
  --      17     |commerces    |14       |   c  |Commerces                   |  30  
  --      18     |services     |14       |   s  |Services                    |  40  
  --      19     |VieLocale-   |14       |   v  |Vie locale et régionale     |  50  
  --      20     |SocioEco     |19       |  ss  |Socio-économie              |  10  
  --      21     |culturel     |19       |  cc  |Culturel                    |  20  
  --      22     |politik      |19       |   p  |Politique                   |  30  
  --      23     |patrimn      |19       |  pp  |Patrimoine                  |  40  
  --      24     |nature       |19       |   n  |Nature                      |  50  
  --      25     |about        |NULL     |   a  |A propos                    |  95  
  --      26     |hebergt      |18       |  hh  |Hébergements                |  10  
  --      27     |restaur      |18       |   r  |Restauration                |  20  
  --      28     |SportAct     |18       |  sa  |Sports - Activités          |  30  
  --      29     |brol         |18       |  bb  |brol                        |  35  
  --      30     |bril         |18       |  bbb |bril                        |  36  



WITH CT(id, pid, lvl, srt, pgName) AS (
    SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page
      FROM PagesTreeTT1
     WHERE ParentPage_Id IS NULL
    UNION ALL
    SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page
      FROM CT
      JOIN PagesTreeTT1 AS P
     WHERE P.ParentPage_Id = CT.id LIMIT 1000
)
SELECT *
  FROM CT

  --  id |pid   |lvl| srt|pgName       
  -- ----|------|---|----|-------------
  --   2 |NULL  | 0 | 10 |HistRchch    
  --   3 |NULL  | 0 | 10 |TrvLieu      
  --   8 |NULL  | 0 | 10 |testAccent1  
  --  12 |NULL  | 0 | 50 |chantier_    
  --  13 |NULL  | 0 | 60 |autour       
  --  14 |NULL  | 0 | 70 |infos        
  --  25 |NULL  | 0 | 95 |about        
  --   4 |2     | 1 | 20 |GDN_         
  --   7 |2     | 1 | 20 |decouvert    
  --   6 |2     | 1 | 40 |dots         
  --   5 |2     | 1 | 30 |gTrv         
  --  16 |14    | 1 | 20 |GdnCmn       
  --  19 |14    | 1 | 50 |VieLocale-   
  --  15 |14    | 1 | 10 |WillVlg      
  --  17 |14    | 1 | 30 |commerces    
  --  18 |14    | 1 | 40 |services     
  --  10 |7     | 2 | 30 |SsLaForge    
  --  11 |7     | 2 | 40 |inaug        
  --   9 |7     | 2 | 20 |testAccent2  
  --  20 |19    | 2 | 10 |SocioEco     
  --  21 |19    | 2 | 20 |culturel     
  --  24 |19    | 2 | 50 |nature       
  --  23 |19    | 2 | 40 |patrimn      
  --  22 |19    | 2 | 30 |politik      
  --  28 |18    | 2 | 30 |SportAct     
  --  30 |18    | 2 | 36 |bril         
  --  29 |18    | 2 | 35 |brol         
  --  26 |18    | 2 | 10 |hebergt      
  --  27 |18    | 2 | 20 |restaur      



WITH CT(id, pid, lvl, srt, pgName) AS (
    SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page
      FROM PagesTreeTT1
     WHERE ParentPage_Id IS NULL
    UNION ALL
    SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page
      FROM CT
      JOIN PagesTreeTT1 AS P
     WHERE P.ParentPage_Id = CT.id LIMIT 1000
)
SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName,
       null,      null,       null,       null,       null,
       null,      null,       null,       null,       null
  FROM CT AS CTLvl0
 WHERE CTLvl0.lvl = 0
UNION ALL
SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName,
       CTLvl1.id, CTLvl1.pid, CTLvl1.lvl, CTLvl1.srt, CTLvl1.pgName,
       null,      null,       null,       null,       null
  FROM CT AS CTLvl0
  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1
UNION ALL
SELECT CTLvl0.id, CTLvl0.pid, CTLvl0.lvl, CTLvl0.srt, CTLvl0.pgName,
       CTLvl1.id, CTLvl1.pid, CTLvl1.lvl, CTLvl1.srt, CTLvl1.pgName,
       CTLvl2.id, CTLvl2.pid, CTLvl2.lvl, CTLvl2.srt, CTLvl2.pgName
  FROM CT AS CTLvl0
  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
  JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id
 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2

  --  id |pid   |lvl| srt|pgName       |null  |null  |null  |null  |null         |null  |null  |null  |null  |null         
  -- ----|------|---|----|-------------|------|------|------|------|-------------|------|------|------|------|-------------
  --   2 |NULL  | 0 | 10 |HistRchch    |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   3 |NULL  | 0 | 10 |TrvLieu      |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   8 |NULL  | 0 | 10 |testAccent1  |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --  12 |NULL  | 0 | 50 |chantier_    |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --  13 |NULL  | 0 | 60 |autour       |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --  25 |NULL  | 0 | 95 |about        |NULL  |NULL  |NULL  |NULL  |NULL         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   2 |NULL  | 0 | 10 |HistRchch    |4     |2     |1     |20    |GDN_         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   2 |NULL  | 0 | 10 |HistRchch    |5     |2     |1     |30    |gTrv         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   2 |NULL  | 0 | 10 |HistRchch    |6     |2     |1     |40    |dots         |NULL  |NULL  |NULL  |NULL  |NULL         
  --   2 |NULL  | 0 | 10 |HistRchch    |7     |2     |1     |20    |decouvert    |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |15    |14    |1     |10    |WillVlg      |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |16    |14    |1     |20    |GdnCmn       |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |17    |14    |1     |30    |commerces    |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |NULL  |NULL  |NULL  |NULL  |NULL         
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |NULL  |NULL  |NULL  |NULL  |NULL         
  --   2 |NULL  | 0 | 10 |HistRchch    |7     |2     |1     |20    |decouvert    |10    |7     |2     |30    |SsLaForge    
  --   2 |NULL  | 0 | 10 |HistRchch    |7     |2     |1     |20    |decouvert    |11    |7     |2     |40    |inaug        
  --   2 |NULL  | 0 | 10 |HistRchch    |7     |2     |1     |20    |decouvert    |9     |7     |2     |20    |testAccent2  
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |20    |19    |2     |10    |SocioEco     
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |21    |19    |2     |20    |culturel     
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |24    |19    |2     |50    |nature       
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |23    |19    |2     |40    |patrimn      
  --  14 |NULL  | 0 | 70 |infos        |19    |14    |1     |50    |VieLocale-   |22    |19    |2     |30    |politik      
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |28    |18    |2     |30    |SportAct     
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |30    |18    |2     |36    |bril         
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |29    |18    |2     |35    |brol         
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |26    |18    |2     |10    |hebergt      
  --  14 |NULL  | 0 | 70 |infos        |18    |14    |1     |40    |services     |27    |18    |2     |20    |restaur      




WITH CT(id, pid, lvl, srt, pgName) AS (
	SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page
	  FROM PagesTreeTT1
	 WHERE ParentPage_Id IS NULL
	UNION ALL
	SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page
	  FROM CT
	  JOIN PagesTreeTT1 AS P
	 WHERE P.ParentPage_Id = CT.id LIMIT 1000
), PT(PgPostA, PgPostB, PgPostC) AS (
	SELECT CTLvl0.srt, null, null
	  FROM CT AS CTLvl0
	 WHERE CTLvl0.lvl = 0
	UNION ALL
	SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, null
	  FROM CT AS CTLvl0
	  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
	 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1
	UNION ALL
	SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, CTLvl0.srt||'-'||CTLvl1.srt||'-'||CTLvl2.srt
	  FROM CT AS CTLvl0
	  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
	  JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id
	 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2
 )
SELECT DISTINCT PgPostA,PgPostB,PgPostC
  FROM PT
 ORDER BY PgPostA,PgPostB,PgPostC

  -- PgPostA|PgPostB|PgPostC   
  -- -------|-------|----------
  --    10  |NULL   |NULL      
  --    10  |10-20  |NULL      
  --    10  |10-20  |10-20-20  
  --    10  |10-20  |10-20-30  
  --    10  |10-20  |10-20-40  
  --    10  |10-30  |NULL      
  --    10  |10-40  |NULL      
  --    50  |NULL   |NULL      
  --    60  |NULL   |NULL      
  --    70  |NULL   |NULL      
  --    70  |70-10  |NULL      
  --    70  |70-20  |NULL      
  --    70  |70-30  |NULL      
  --    70  |70-40  |NULL      
  --    70  |70-40  |70-40-10  
  --    70  |70-40  |70-40-20  
  --    70  |70-40  |70-40-30  
  --    70  |70-40  |70-40-35  
  --    70  |70-40  |70-40-36  
  --    70  |70-50  |NULL      
  --    70  |70-50  |70-50-10  
  --    70  |70-50  |70-50-20  
  --    70  |70-50  |70-50-30  
  --    70  |70-50  |70-50-40  
  --    70  |70-50  |70-50-50  
  --    95  |NULL   |NULL      



WITH CT(id, pid, lvl, srt, pgName) AS (
	SELECT ID_Page, ParentPage_Id, 0, PgSort, TxtID_Page
	  FROM PagesTreeTT1
	 WHERE ParentPage_Id IS NULL
	UNION ALL
	SELECT ID_Page, ParentPage_Id, lvl+1, PgSort, TxtID_Page
	  FROM CT
	  JOIN PagesTreeTT1 AS P
	 WHERE P.ParentPage_Id = CT.id LIMIT 1000
), PT(PgPostA, PgPostB, PgPostC) AS (
	SELECT CTLvl0.srt, null, null
	  FROM CT AS CTLvl0
	 WHERE CTLvl0.lvl = 0
	UNION ALL
	SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, null
	  FROM CT AS CTLvl0
	  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
	 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1
	UNION ALL
	SELECT CTLvl0.srt, CTLvl0.srt||'-'||CTLvl1.srt, CTLvl0.srt||'-'||CTLvl1.srt||'-'||CTLvl2.srt
	  FROM CT AS CTLvl0
	  JOIN CT AS CTLvl1 ON CTLvl1.pid = CTLvl0.id
	  JOIN CT AS CTLvl2 ON CTLvl2.pid = CTLvl1.id
	 WHERE CTLvl0.lvl = 0 AND CTLvl1.lvl = 1 AND CTLvl2.lvl = 2
 ), DT(PgPostA, PgPostB, PgPostC) AS (
	SELECT DISTINCT
	       CASE WHEN PgPostB IS NULL THEN IFNULL(PgPostA,'') ELSE '' END,
	       CASE WHEN PgPostC IS NULL THEN IFNULL(PgPostB,'') ELSE '' END,
	       IFNULL(PgPostC,'')
	  FROM PT
	 ORDER BY PgPostA,PgPostB,PgPostC
)
SELECT PgPostA, PgPostB, PgPostC
  FROM DT
;

  -- PgPostA|PgPostB|PgPostC   
  -- -------|-------|----------
  --    10  |       |          
  --        |10-20  |          
  --        |       |10-20-20  
  --        |       |10-20-30  
  --        |       |10-20-40  
  --        |10-30  |          
  --        |10-40  |          
  --    50  |       |          
  --    60  |       |          
  --    70  |       |          
  --        |70-10  |          
  --        |70-20  |          
  --        |70-30  |          
  --        |70-40  |          
  --        |       |70-40-10  
  --        |       |70-40-20  
  --        |       |70-40-30  
  --        |       |70-40-35  
  --        |       |70-40-36  
  --        |70-50  |          
  --        |       |70-50-10  
  --        |       |70-50-20  
  --        |       |70-50-30  
  --        |       |70-50-40  
  --        |       |70-50-50  
  --    95  |       |          

================================================================================

2021-07-17
22:54 Reply: (Deleted) (artifact: 557c89d139 user: cuz)

Those are wrapper errors in the wrapper (interface) you use, it's not SQLite errors.

I should think the Pyqt5 forum or usergroup should be able to shed some light on it.

With the error you get I would assume that "VisitorName" (which seems to be a string/text) is being pushed into a column that isn't so dedicated - not that SQLite would care about it, but the interface (wrapper) you use in Pyqt5 might care.

Do you have the database schema? Show how the table was created and we may perhaps have more thoughts, but honestly, you are far more likely to find help from the Pyqt5 people.

22:27 Edit reply: (Deleted) (artifact: 89ee54c961 user: cuz)
cur.execute(
"INSERT INTO Visitors (NAME, PHONE, COMPANY, PERSON, EXPLANATION, CARD_NO, DATE, GIRIS_SAATI, CIKIS_SAATI, ID) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)", 
(VisitorsName, VisitorsPhone, VisitorsCompany, VisitorsPersonDepartment, VisitorsExplanation, VisitorsCardNo, VisitorsDate, VisitorsIn, VisitorsOut)
)
22:26 Reply: (Deleted) (artifact: 1d51f050b1 user: cuz)
cur.execute(
"INSERT INTO Visitors (NAME, PHONE, COMPANY, PERSON, EXPLANATION, CARD_NO, DATE, GIRIS_SAATI, CIKIS_SAATI) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)", 
(VisitorsName, VisitorsPhone, VisitorsCompany, VisitorsPersonDepartment, VisitorsExplanation, VisitorsCardNo, VisitorsDate, VisitorsIn, VisitorsOut, ID)
)
2021-07-15
23:57 Reply: Should SQLite be able to optimize this query? (artifact: 19acdf0b27 user: cuz)

They do on all historical versions of SQLite. But if I omit the ORDER BY clauses ... I'm not sure what the SQL standards say about ORDER BY clauses on VIEWs, but the result here seems counter-intuitive to me.

It isn't intuitive, but strictly an order clause that is not in the outer query is irrelevant to the outer query results, though not irrelevant to an inner query result, like inside GROUP_CONCATS or Windows functions.

Consider:

SELECT a,b FROM (SELECT a FROM ta ORDER BY a), tb ORDER BY b;
The "ORDER BY a" is as useful as the vestigial leg bones in a Whale and I'm all for disregarding it, even where a later "ORDER BY b" is not present.

Regarding historic use and breaking backward - what does pragma reverse_unordered_selects do with your example counter-intuitive query?

If the query output under that pragma is reversed, then all is well and good and I vote for changing it.
If however that query actually remains correctly ordered under said pragma then I for one might have some systems breaking. Like Larry and others, I would have used such queries - though I always sanitize production systems using said pragma in testing - but if that is no longer a sufficient check, I might run into trouble.

Personally I will get over it though, happily even if the change brings other optimizations, but others may disagree.

2021-07-14
14:15 Reply: Help with a query (artifact: 8c6e211067 user: cuz)

As I said, I am not an expert so, vague human words is all I have but thank you for your input, I am a quick learner once pointed in the right direction.

Not a problem and quite understandable.

What will go a really long way is saying why you want half the sum? Do you not want the average? Half the sum is constantly proportional to the sum, it infers no value in and of itself.

Another good step is to show the table as in show some set of values, maybe make up your own table with 10 rows or so (or more if needed to make the point clear).... Then show from that table, what would be the desired output a correct query should produce.

10 brownie points if you can state the table and rows in actual SQL so any of us can copy paste it to our favourite SQLite/SQL editor and make the queries without having to translate all your human vagueness into data first. If it's easy for us to do, likely a lot more people will assist.

2021-07-13
13:40 Reply: My Database is being deleted after each restart of my code. How can I fix it? (artifact: e7663192aa user: cuz)

This must be a contender for the gold-medalist in the least-specific-questions category.

Your question sounds like this to me:

I've decided to learn to drive, and use a car for it. I'm kinda doing ok so far, but every time I look in my garage, there is no car there.
Why is it happening?

We could spend all day guessing.

Could you tell us please:

  • Which operating system,
  • What is the file path (including name) of the SQLite that is opened (and then magically disappearing),
  • How are you opening it,
  • Are you using a wrapper, library, compiled-in SQLite, or something else?
  • Can you open the file with another SQLite tool or the CLI? At least before it disappears?
  • Have you ever seen the file appear?

We are not politicians, we can't inform public opinion on the skantest of suggestions, we need hard evidence and understanding to form ideas or suggestions.

2021-07-11
11:19 Reply: Unstack one column to multiple (artifact: a3726af92d user: cuz)

The number of fields for each record also varies.

That's the problem - if we do not have any idea how many rows (fields) there may be per record, there is no way to do what you want.

If the row (field) count was constant per record, the problem would be trivial to solve, since we would know the first row means the description, the second row is the make, then the product detail identifiers etc. etc.

But now we don't know what the rows may be, so it is impossible to do. Unless you have some rule that may help, like "If there are 10 rows exactly then row 5 means X and row 7 means Y etc, and if there are exactly 12 rows, then row 6 is X and row 8 is Y, etc.

Also, if you need to do this one time, I would suggest it is easier to just drop it in a spreadsheet and do it, or write a small bit of code to do it. If this is something that needs importing regularly, then write some more robust code to do it. SQL cannot solve your problem.

10:12 Reply: Why can you write triggers using functions from extensions? How does that work? (artifact: 5479482659 user: cuz)

Well, sounds like you found the culprit, the magic is less dark when the agent facilitating it is understood.

Given the magic is repeatable and part of the core of your system, I can add that if your app and framework will be replicated as-is on target user machines, then you should have no problem using said features freely and copiously.

If however your DB is ever intended to be shared to any other App or inspected or used with some SQLite DB management tool, that library and perhaps some accompanying documentation will need to be made available and described well.

Thus, answering your original question: There is no "byte-code" that gets saved into the DB file and its continued functioning is wholly dependent on the availability and attaching of needed byte-code used in internal DDL/SQL/UDFs, on the target system.

I've often mused at the beauty of having this - save .dll/.so files as BLOBs in a special table, and on the target system have it used automatically - perhaps by saving temporarily in temp storage, or alongside journal/.shm files etc. and loading it automatically when needed.

However, the World of vulnerabilities that would open up is too insane to contemplate. Every DB file would become a Trojan horse by design. So perhaps not. :)

Good luck!

2021-07-10
21:33 Reply: Why can you write triggers using functions from extensions? How does that work? (artifact: a9f795200b user: cuz)

That sounds like dark magic.... are you sure that worked?

Was the extension library not merely "remembered"? Does it work when you remove the library completely (delete the .dll/.so from the disk)?

So far as I understood, you had to load the library every time to use it in the way you suggest (which is rather easily automated).

To ensure I (and others) understand correct, this is the claim:

  • Load an external library containing a function that does not exist in vanilla SQLite.
  • Use said function in a function-based Index or Trigger.
  • Unload the library/Restart the app, no more library, trying to use the function in a normal SQL statement produces an error.
  • The Index/Trigger however still works when used/triggered.

If any of those statements do not hold, please indicate which and how.

2021-07-09
15:19 Reply: not able to update rows (artifact: 0c85689007 user: cuz)

Larry is correct about this being clearly using the interface application of some other vendor, perhaps saying which application might help. I know it's not SQLiteSpeed nor DB Browser for SQLite since I know their error codes, in fact that error seems like it is reported by a wrapper of sorts being used by whatever application you are using.

Either way, the reason I reply is that if only you would post the actual SQL SELECT statement (using the claimed != operator), then there is a probability we could already tell you what is wrong, regardless of how the App or Wrapper deals with it.

The Schema would be useful too. Is that perhaps happening on a WITHOUT ROWID table?

2021-07-07
14:17 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: 581da19742 user: cuz)

Yes, I've noted this too, and as Mr. Hound reported, the whether any single row is updated or not is irrelevant, it's more to do with if any row on a specific page is changed that decides if it gets written to disk - that is, assuming we are correct in thinking that SQLite checks it so. The non changes may be brought about by a completely other mechanism.

Either way, I'm thinking more and more that SQLite writes only when needed and any query parts to try and "help" it is a waste of CPU cycles - but I am still not 100% sure.

12:42 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: f521b099e7 user: cuz)

Agreed Keith (and Simon in a later post), thanks for posting and this is all well and good, but the original question which I am still not 100% sure of, although what you said "seems to suggest", is this:

Does SQLite specifically have an optimization that do not actually write rows that did not change during an update statement, YES or NO? Does it depend on the VFS or not?

i.e. does it repaint Green cars to Green, or doesn't it, or does it not know? (regardless of what it reports back)

Put another way, and what we really want to know: Is it helpful for the OP to add code to his queries to avoid updating rows that already contain the correct values, or will SQLite already avoid rewriting those internally? (in which case he is wasting effort and cpu cycles for no effect).

What it returns in the "changes()" value is a separate thought, perhaps needing better documentation as noted before, but of no consequence in answering this question.

12:29 Reply: Repeat values n times (artifact: 5164c386d1 user: cuz)

find difficult to practically implement it. I want to learn basics of it.

There's been a quite satisfactory answer already, so this is just some thoughts added to it, since the CTE addition is one of my all-time favourite things in SQL, and specifically, in SQLite.

Note that this explanation is for basic use and basic understanding, there is a lot more to CTEs and more correct terms, but this is the plain English simplified version.

In a recursive common table expression there are two queries, the initial query that establishes the first value(s) and the recursed query that builds on the output repeatedly, until the LIMIT is reached or the WHERE clause yields FALSE.

To tell the query we will be using CTEs, we start with the words "WITH" or "WITH RECURSIVE". (The "Recursive" is optional in many SQL engines, including SQLite, but it helps show your intent to a next person who reads the query). The CTE statements start with a name for the CTE which can be used later in the normal query (which follows after the CTE section), an optional section in parentheses to name the columns, the keyword "AS" and the CTE query, again in parentheses - like this:

WITH RECURSIVE
CTEName(col1, col2, ...) AS (

  ... Initial CTE Query here ...

  UNION ALL

  ... Recursive CTE Query here ...

)
... The rest of the normal Query here ...

The initial query does nothing more than establish the first or base values to work from, and the second (recursive) query selects from the output of the CTE's output, selecting from itself, hence the term "Recursive". The two queries are union-ed together by the UNION ALL clause.

That's the entire structure, no more, no less. It's simple and elegant. To see how it works, we can try some simple things:

Make a counter that outputs a list of numbers, let's say the numbers from 5 to 12, in a column named "ExampleNo".

First step is to think about the process. We need to start from 5 (that answers our question of what to select for the initial query inside the CTE), then we need to select it, and the value after it (itself + 1) and continue adding 1 until we reach 12. We need to name the CTE something useful (let's say "CountCTE") and have its one and only column named "ExampleNo" so that we can refer to it in the normal query at the end.

That CTE might look like this:

WITH RECURSIVE
CountCTE(ExampleNo) AS (        -- CTE Definition
    SELECT 5                    -- Initial Query
    UNION ALL
    SELECT ExampleNo + 1        -- Recursive Query
      FROM CountCTE             -- Selected from itself (recursion)
     WHERE ExampleNo < 12       -- Stop when ExampleNo >= 12
)
SELECT ExampleNo                -- Normal Query
  FROM CountCTE                 -- using the above CTE
;

  -- The output:

  --   ExampleNo 
  --   ---------
  --       5     
  --       6     
  --       7     
  --       8     
  --       9     
  --      10     
  --      11     
  --      12     

That's all there is to it.

How it works: The initial query produces one line of output, imagine that it goes to sit in a FIFO buffer list ready to send to the output. Before it gets output to the normal query though, the recursive query has an opportunity to read any new rows added to the output buffer and add any new SELECT output rows according to its own SELECT clause. So on the first iteration the recursive query reads the 5 that is already in the buffer, does its own select operation (SELECT ExampleNo + 1 ...) which yields 6, and then adds that to the output buffer list.

But now, since there is a new item in the output buffer (6), the recursive query again has the opportunity to first read it and use it in the recursive select, this time yielding 7. Again there is a new item in the buffer, and again the recursive query reads it... etc. etc. and this recursion will continue into infinity if we do not stop it at some point. Luckily when we get to 12, the WHERE clause says that we can only select values from the buffer where ExampleNo < 12... so this time the recursive query yields no lines, so we do not output anything to the buffer, so there is no new buffer lines to read, and the recursion ends there.

Note that the query stops at ExampleNo < 12, which means the last ExampleNo to be processed inside the recursive query is 11, but the output is (SELECT ExampleNo + 1 ...) and therefore the last output we see coming out of it is indeed 11 + 1, which is the 12 we see.

Take care to not state queries that will go into infinite loops. Another good way to ensure the loop does not last forever is to use a LIMIT clause, which in our query should be 7 since we needed the 7 numbers (5..12) which could make our query look like this:

WITH RECURSIVE
CountCTE(ExampleNo) AS (
    SELECT 5
    UNION ALL
    SELECT ExampleNo + 1
      FROM CountCTE
     LIMIT 7
)
SELECT ExampleNo
  FROM CountCTE
;

How to limit the output depends - if we knew we wanted exactly 20 lines of output, regardless what they are, then LIMIT is best. If we wanted the last output line to be an exact value, regardless of how many lines it takes to reach that value, then a WHERE clause is best. You are also welcome to combine them, perhaps as a way to ensure avoiding infinite loops.

Lastly, I've only shown queries above using one column, but you can use multiple columns and do recursive calculation on any and all of them as long as you remember the idea of whatever goes into the output buffer will be passed through the recursive query and may produce more output buffer items. Also, the initial query may indeed be a full complex query with multiple items, perhaps even from another table.

Here are some quick example use cases:

  • 1. Get a list of 5th multiples, 5th modulos, and squares of the first 20 numbers:
WITH RECURSIVE
MathCTE(Num, Fifth_Multi, Fifth_Mod, Square) AS (
    SELECT 0 ,0 ,0, 0
    UNION ALL
    SELECT Num + 1,  Num * 5,  Num % 5,  Num * Num
      FROM MathCTE WHERE Num < 20
)
SELECT * FROM MathCTE WHERE Num > 0
;

  --             | Fifth_- |         |      
  --      Num    |  multi  |Fifth_Mod|Square
  -- ------------|---------|---------|------
  --       1     |    0    |    0    |   0  
  --       2     |    5    |    1    |   1  
  --       3     |    10   |    2    |   4  
  --       4     |    15   |    3    |   9  
  --       5     |    20   |    4    |  16  
  --       6     |    25   |    0    |  25  
  --       7     |    30   |    1    |  36  
  --       8     |    35   |    2    |  49  
  --       9     |    40   |    3    |  64  
  --      10     |    45   |    4    |  81  
  --      11     |    50   |    0    |  100 
  --      12     |    55   |    1    |  121 
  --      13     |    60   |    2    |  144 
  --      14     |    65   |    3    |  169 
  --      15     |    70   |    4    |  196 
  --      16     |    75   |    0    |  225 
  --      17     |    80   |    1    |  256 
  --      18     |    85   |    2    |  289 
  --      19     |    90   |    3    |  324 
  --      20     |    95   |    4    |  361 

Note that I start with 0,0,0,0 in the initial query, and then in the normal query I simply excluded that result. This allows me to make the CTE do all the work and not needing to calculate the first query by hand - which is not just better for lazy people like me, but also safer in ensuring consistency.

  • 2. Print all dates for the next 15 days from today:
WITH RECURSIVE
Days(DayDate) AS (
    SELECT date('now')
    UNION ALL
    SELECT date(DayDate,'+1 day') FROM Days LIMIT 15
)
SELECT DayDate,
       strftime('%Y',DayDate) AS Year,
       strftime('%W',DayDate) AS WeekOfYear,
       strftime('%w',DayDate) AS DayOfWeek
  FROM Days
;


  --             |      |WeekOfYe-|         
  -- DayDate     | Year |    ar   |DayOfWeek
  -- ------------|------|---------|---------
  -- 2021-07-07  | 2021 |    27   |    3    
  -- 2021-07-08  | 2021 |    27   |    4    
  -- 2021-07-09  | 2021 |    27   |    5    
  -- 2021-07-10  | 2021 |    27   |    6    
  -- 2021-07-11  | 2021 |    27   |    0    
  -- 2021-07-12  | 2021 |    28   |    1    
  -- 2021-07-13  | 2021 |    28   |    2    
  -- 2021-07-14  | 2021 |    28   |    3    
  -- 2021-07-15  | 2021 |    28   |    4    
  -- 2021-07-16  | 2021 |    28   |    5    
  -- 2021-07-17  | 2021 |    28   |    6    
  -- 2021-07-18  | 2021 |    28   |    0    
  -- 2021-07-19  | 2021 |    29   |    1    
  -- 2021-07-20  | 2021 |    29   |    2    
  -- 2021-07-21  | 2021 |    29   |    3    

  • 3. Lastly, expanding on all the previous examples, some more technical example. Here we have a made-up table of event dates, but we want to display them in a calendar format, so we need to show days for which there are no calendar entries. This seemingly simple request is impossible to do without a full calendar date table or a recursive CTE. For size consideration, it is limited to the month of February, 2021. It also shows off how to use multiple CTEs in one query, how CTE's can be joined to other tables (and other CTEs), and the use of the VALUES function to make a CTE table from constants.

Please note that these are by far not the only ways or necessarily best ways to do everything, but it does show the concepts in action.

CREATE TABLE Events(
  EventDate NUMERIC,
  EventDescription TEXT
);

INSERT INTO Events VALUES
 ('2021-02-13','John''s birthday')
,('2021-02-14','Valentine''s day')
,('2021-02-18','Fishing Trip with Jesse')
,('2021-02-27','Deadline - Artemis project')
;

WITH RECURSIVE
Days(DayDate) AS (
    SELECT date('2021-02-01')         -- Start of February
    UNION ALL
    SELECT date(DayDate,'+1 day')
      FROM Days
     WHERE DayDate < '2021-02-28'     -- End of February
     LIMIT 31
), 
WeekDays (DayNo, DayName) AS (
    VALUES (0, 'Sunday'),             -- Constants specified as VALUES
	   (1, 'Monday'),
	   (2, 'Tuesday'),
	   (3, 'Wednesday'),
	   (4, 'Thursday'),
	   (5, 'Friday'),
	   (6, 'Saturday')
)
SELECT WeekDays.Dayname,  Days.DayDate,  IFNULL(Events.EventDescription,'') AS Event
  FROM Days
  JOIN WeekDays ON WeekDays.DayNo = CAST(strftime('%w',DayDate) AS INT)
  LEFT JOIN Events ON Events.EventDate = Days.DayDate
  ORDER BY Days.DayDate
;


  -- DayName    |DayDate     |Event                       
  -- -----------|------------|----------------------------
  -- Monday     |2021-02-01  |                            
  -- Tuesday    |2021-02-02  |                            
  -- Wednesday  |2021-02-03  |                            
  -- Thursday   |2021-02-04  |                            
  -- Friday     |2021-02-05  |                            
  -- Saturday   |2021-02-06  |                            
  -- Sunday     |2021-02-07  |                            
  -- Monday     |2021-02-08  |                            
  -- Tuesday    |2021-02-09  |                            
  -- Wednesday  |2021-02-10  |                            
  -- Thursday   |2021-02-11  |                            
  -- Friday     |2021-02-12  |                            
  -- Saturday   |2021-02-13  |John's birthday             
  -- Sunday     |2021-02-14  |Valentine's day             
  -- Monday     |2021-02-15  |                            
  -- Tuesday    |2021-02-16  |                            
  -- Wednesday  |2021-02-17  |                            
  -- Thursday   |2021-02-18  |Fishing Trip with Jesse     
  -- Friday     |2021-02-19  |                            
  -- Saturday   |2021-02-20  |                            
  -- Sunday     |2021-02-21  |                            
  -- Monday     |2021-02-22  |                            
  -- Tuesday    |2021-02-23  |                            
  -- Wednesday  |2021-02-24  |                            
  -- Thursday   |2021-02-25  |                            
  -- Friday     |2021-02-26  |                            
  -- Saturday   |2021-02-27  |Deadline - Artemis project  
  -- Sunday     |2021-02-28  |                            

Hope that offers some clarity, and Good luck!

2021-07-06
16:10 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: d7ad3388b1 user: cuz)

I am sure you would agree that changes() should report actual inserts and rewrite of rows on update, regardless of whether the latter changed any bits.

I don't agree, or disagree. "Changes()", like any other function, should report what its documentation claims for it to report. Whether or not this involves actual "bits on disk" changing or not I cannot seem to find reference to in its documentation, so have no basis to argue one or way or the other.

What now transpires, regardless of what "changes()" report, is the question of "Do actual bits change on disk?", or more succinctly, does a physical file write happen if no bits have changed, or not?

If the answer is NO, then the act of trying to prevent writes by adding a lot of extra time-consuming conditions is a fool's errand. If the answer is YES, that process may well be worth the effort.

You will find that MSSQL and MySQL for instance internally avoid writes that are not needed but then also report so in their respective "row_count()" or similar functions, though options exist to change this behaviour to show all rows matching the search rather than those actually changed, if needed.

I do not really care how SQLite reports it, as long as it is documented so, and understanding how it functions to know whether or not it is worth adding code to avoid unnecessary writes or not.

I would love a definitive answer on that.

PS: Upon re-reading my post I think some confusion comes from this sentence:

Whatever the reason, you may have good reason to actually avoid it...

Which can be interpreted as me warning off the habit of checking to avoid writes, but in fact, with that unfortunate phrase, by "avoid it" I really meant "avoiding the updates yourself" - as in I precisely encouraged what the OP did and is in full agreement with your statement.

I realise now also that a lot of deliberation water flowed under the bridge, yet the original question remains unanswered. I'm not sure there is a better way to do it (i.e. avoid the updates) than what the OP described, should SQLite not avoid it internally.

13:47 Edit reply: Shortcut to change a non changing update or upsert in a noop (artifact: 1c6520321a user: cuz)

Well, I did some testing by updating some stuff that did not need updating and querying the change counts, with astonishing results - SQLite actually updates them.

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE t(a,b);

INSERT INTO t(a,b) VALUES
 (1, 'John')
,(2, 'Joan')
,(3, 'Jane')
,(4, 'Joanne')
,(5, 'Jenna')
,(6, 'Jack')
;

SELECT * FROM t;

  --       a     |b       
  -- ------------|--------
  --       1     |John    
  --       2     |Joan    
  --       3     |Jane    
  --       4     |Joanne  
  --       5     |Jenna   
  --       6     |Jack    


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jenna' WHERE a > 4;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       2     

This same test in, for instance MySQL, yields 0 values where changes were not needed.

Possibilities I can think of:

  • 1 "Changes()" here reports the mathematical execution (honoured requests) rather than physical data writes,
  • 2 or perhaps the SQLite devs figured that the incidence of UPDATEs that do NOT actually alter row contents are so low during typical operation that the overhead of first reading the data to "check-if-needed" is more expensive than just always writing,
  • 3 or maybe the trigger mechanisms need the actual writing to do their thing,
  • 4 or maybe it is because I use WAL journaling which always writes,
  • 5 or perhaps it is a genuine overlooked optimization opportunity (though I have some difficulty believing this hypothesis).

Whatever the reason, you may have good reason to actually avoid it, though the habit may cause slower execution (as per point 2 above).

I'm now quite curious.

13:44 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: a33cdb887a user: cuz)

Well, I did some testing by updating some stuff that did not need updating and querying the change counts, with astonishing results - SQLite actually updates them.

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE t(a,b);

INSERT INTO t(a,b) VALUES
 (1, 'John')
,(2, 'Joan')
,(3, 'Jane')
,(4, 'Joanne')
,(5, 'Jenna')
,(6, 'Jack')
;

SELECT * FROM t;

  --       a     |b       
  -- ------------|--------
  --       1     |John    
  --       2     |Joan    
  --       3     |Jane    
  --       4     |Joanne  
  --       5     |Jenna   
  --       6     |Jack    


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jane' WHERE a=3;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       1     


UPDATE t SET b='Jenna' WHERE a > 4;
SELECT Changes();

  --   Changes() 
  -- ------------
  --       2     

This same test in, for instance MySQL, yields 0 values where changes were not needed.

Possibilities I can think of: 1 "Changes()" here reports the mathematical execution (honoured requests) rather than physical data writes, 2 or perhaps the SQLite devs figured that the incidence of UPDATEs that do NOT actually alter row contents are so low during typical operation that the overhead of first reading the data to "check-if-needed" is more expensive than just always writing, 3 or maybe the trigger mechanisms need the actual writing to do their thing, 4 or maybe it is because I use WAL journaling which always writes, 5 or perhaps it is a genuine overlooked optimization opportunity (though I have some difficulty believing this hypothesis).

Whatever the reason, you may have good reason to actually avoid it, though the habit may cause slower execution (as per point 2 above).

I'm now quite curious.

12:46 Reply: Shortcut to change a non changing update or upsert in a noop (artifact: af6b15dcf1 user: cuz)

This should not be needed, the SQL engines usually all do this internally, for the exact reason you are trying to do it.

I'm not 100% sure right this moment that SQLite does it, or how effective it is, but I have to think that with all the years of chasing faster an faster total execution times and squeezing every last drop of efficiency out of the engine, the SQLite devs would not have neglected this rather obvious first point of optimization.

Have you any observations/measurements that leads you to think the opposite?

2021-07-02
10:30 Reply: Incorrect Result From SQLite Query (artifact: a2a5608d9f user: cuz)

It shouldn't matter - See example below where I make text columns and sometimes populate text and sometimes numbers, SQLite is ambivalent:

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
================================================================================================

CREATE TABLE report_A (
   rows INTEGER PRIMARY KEY,
   amount TEXT,
   rate TEXT
 );


INSERT INTO report_A VALUES
  ( 1,100 , 0.120 )
 ,( 2, 50 , 0.150 )
 ,( 3,'20', 0.005 )
 ,( 4, 60 ,'0.130')
 ,( 5, 10 ,'0.150')
 ,( 6,'67', 0.005 )
 ,( 7, 50 , 0.001 )
 ,( 8,'87','0.006')
 ,( 9,'12','0.005')
 ,(10, 43 , 0.120 )
 ;


SELECT rows, amount, (amount * rate) AS cost
 FROM report_A

  --     rows    |amount|     cost
  -- ------------|------|---------
  --       1     |  100 |     12.0
  --       2     |  50  |      7.5
  --       3     |  20  |      0.1
  --       4     |  60  |      7.8
  --       5     |  10  |      1.5
  --       6     |  67  |    0.335
  --       7     |  50  |     0.05
  --       8     |  87  |    0.522
  --       9     |  12  |     0.06
  --      10     |  43  |     5.16


Still a good idea to run the typeof() query though, just in case we learn something else. My bet is however the program he uses is getting it wrong - I think it's the same program recently mentioned in another thread where it didn't deal correctly with Windows UAC - perhaps it's just a tad old.

2021-06-30
11:23 Reply: Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8 (artifact: cc1757bd91 user: cuz)

Indeed, I realized after. So I'd like to change and resubmit my original reply to just:

+1 to having the docs mention a sensible lower bound for -DSQLITE_MAX_FUNCTION_ARG.

11:15 Reply: Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8 (artifact: 72c640bdb5 user: cuz)

I think I realize more specific what the complaint it after trying it. You did not know there would be a 9-argument function in the automated scripts and so wish the documentation for "-DSQLITE_MAX_FUNCTION_ARG" to mention care when setting it lower than X (which seemingly could be X=9)

11:09 Reply: Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8 (artifact: 891699e083 user: cuz)

That sqlite_rename_column() function has 9 arguments. Your max was 8, but 9 max is needed to allow that 9-argument function.

I'm not sure what part of that is surprising or needs to be better documented? Are you perhaps suggesting that the max does not apply to internal and UDF functions? (which I'm in support of for at least internals).

As for the question "Is 9 enough?" - Who knows? do you have UDFs that take more? Do you use any internal functions that do? Am I sorely misunderstanding your premise?

10:50 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: faa4d5d9ce user: cuz)

Hang on, did you say Delphi 7? As in not recent XE7, but the very ancient version from the 1990's? That might be fixed by simply using compatibility mode. Also, that version would not compile exe's that would run well on Windows 10, it did not have manifests at all, and a few other shortcomings. It was also exclusively 32-bit, as was all exe's it made, so all DLLs used must be 32bit.

That error seems very unspecific though.

You should really upgrade, and if you don't like the new expensive Delphi's you could use the very fantastic (and much better in my opinion) open source FPC (Free Pascal Compiler) and Lazarus IDE which you will be right familiar with coming from D7, and you'd be able to use 99% of your existing source code as-is.

If you insist on making D7 work on a Win10 PC, well that sounds interesting to me and I'm willing to have a look with you, but this thread has been quite off-topic for this forum already and now it's crossing into the abyss. Mail me (ryansmithhe at gmail dot com) to discuss further without polluting this forum.

2021-06-29
21:14 Reply: Best practice: Save partial dates (artifact: 36c3c6b043 user: cuz)

This is a great added comment, sorting is almost always important, and moreso with dates, so it is good to consider.

Another thought - avoid placeholder characters that can be used for string manipulation or insertion, like ? and : (which carry meaning in SQL parameters), or {, }, or / which are frequently used in Pearl/PHP strings, file paths, etc.

None of these should be a real problem when following good quoting or escaping practices, but for me, the less need to escape/quote anything, the better.

18:03 Reply: Best practice: Save partial dates (artifact: 7775aefef5 user: cuz)

You know what they say: Great minds think alike... and fools never differ.

Sighs of relief that you did not propose the same placeholder character :)

17:46 Reply: Best practice: Save partial dates (artifact: c2dd0c03a9 user: cuz)

Saving partial dates is not a "practice" by any stretch of the meaning of the word, even though you may not be the first ever to need to do it.

If it isn't a "practice", then there can be no "best practice".

You may as well ask "What's considered as the 'correct' way to eat ice-cream vinaigrette?". Hopefully nobody knows.

What you need to consider is the use case, and any potential future calculation you might be doing on it.

If 'twas me...

I would put it in a string of exact length with placeholders, perhaps periods for legibility's sake, so that it makes the ISO8601 form (for example): yyyy-mm-dd with placeholder like Asterisk, space, period, or such. Preferably things that will not be trimmed (like spaces) and that will not be confused with real date-time separators, like "-" and ":".

So that the following date representations become:

    complete date:    2021-06-29
    year-month:       2021-06-..
    year:             2021-..-..
    only month:       ....-06-..  
    only day:         ....-..-29

etc.

This way you can easily make a function or regex to ensure it is of the correct length, correct form, you know where every part of it is by character index and in general can see ata glance while looking over a list of them, which are complete and which miss what parts.

Downside: It'll take around 35% more space on average given a random distribution of incomplete dates, but it may matter much more or much less for your use case, depending on whether you "mostly" have complete dates, or "mostly" incomplete dates, if space is a consideration.

10:31 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: 912539ad9d user: cuz)

using a different table in ...appdata... although it says it is reading it fro "C:newff"

It's fun when a completely unrelated problem in a different software, or hardware/OS errors on the machine, makes you think your software is broken.

In case you are interested, this is why the first replies to your problem statement started suggesting the UAC virtualization might be the culprit.

TLDR:UAC+Old Software does this --------

Long ago in Windows and its historic unsafe file systems, software (and virii) had the unsafe ability to write anywhere in the system. Windows attempted to fix this by preventing software, or making them ask permission, to write to any place deemed "unsafe".

Problem is this would break a lot of legacy software, so Windows added the "helpful" UAC virtualization that, if you (as the older software) try to write to a place deemed unsafe and you did not ask to do so (Elevate) or state your intents in the Windows executable manifests, it would take the file you are trying to write, say "c:securefoldermyfile.txt" and internally redirect (soft-link) it to a "safe" space over in the user's own /AppData folder.

As you can imagine, older programs (like the one you've used) that did not adapt to play nice with the manifests and UAC requirements, would fall prey to this problem. To add insult to injury, a user could simply turn the UAC off, and then everything would work again, but there was no way for software to know whether a file it was accessing was being virtualized or not.

The amount of confusion this "magic" caused is hard to measure, but I can confirm it is measured in blood and tears - you being the latest victim.

/TLDR

As to your problem, it does sadly seem something is amiss on that computer, because the things you are trying to do should work fine, as you noted, and indeed it does on other places, as you also noted, and Windows 7 already had UAC, so that's not the difference. You are also correct in that the hostname is of no consequence in that DB object.

Is there any sort of drive-encryption or file-encryption software active on any of the computers? We've also seen antivirus software preventing write access while it is checking on a file. Either way, this seems a system-related problem.

Good luck!

01:27 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: 5eadf7a7c6 user: cuz)

Ok, well, verdict is in then. Your file exists in the place you think it exists, your file works and it should all run smoothly.

I'm now starting to think perhaps sqlitestudio is a victim of the UAC virtualization or such, since DB Browser and your code seem to agree.

Perhaps an arbiter can be called in, would you mind trying the Delphi-made SQliteSpeed to open the file and poke around in it? Or better yet, if you are ok with using a CLI, use the sqlite3.exe CLI tool downloadable from sqlite.org and open the DB and confirm the tables presence or absence mentioned before.

Use one of those tools to also add tables/data and see if your program sees the same.

If the theory about the UAC holds, you should find a file with the exact same name but hidden somewhere in the appdata folder of your user. You can search for it there using the windows searcher to confirm.

00:01 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: 82d4eae770 user: cuz)

Apologies, that's "fmOpenRead" and "fmShareDenyNone" which is defined in the same unit as TFileStream, so it must be available once spelt correctly.

The code for the Values thing would actually be of the form "ValueFromIndex[n]" or "Values['name']" but the stuff you posted already answered that question.

The only important (fixed) code to still run is:

  try
    with TFileStream.Create(sqlconnection1.params.Values['Database'], fmOpenRead or fmShareDenyNone) do begin
      SetLength(a,15);
      ReadBuffer(a[1],15);
      Free;
    end;
  except
    on Ex:Exception do a:='Exception: '+Ex.Message;
  end;

  Memo1.Lines.Add('SQLite Mark: '+String(a));

This time I did syntax check it on my side, so the copy paste should work directly.

This just reads the first 15 bytes from the file and will tell us if we see the correct file, if it is accessible, if it can be opened by you/your program, and if it is indeed a valid SQLite file. If so, it should say something like "sqlite format 3", or else, give a telling error.

2021-06-28
19:50 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: 5a43637b48 user: cuz)

Well if all you say is true, then there is nothing wrong with any of what you done, nothing wrong with SQLite, nor Delphi, and the answer is something like "Something weird is afoot on your system".

But since, I'm an unbeliever in magic, could you do the following:

Drop a TMemo on the main form, make it nice and big. I'm assuming the default name of "Memo1"

in the button click add this code:

procedure ....
  var n : Integer;
      a : AnsiString;
begin  
  Memo1.Clear;
  for n:=0 to sqlconnection1.params.count-1 do 
    memo1.Lines.Add(sqlconnection1.params[n]);

  Memo1.Lines.Add('');
  Memo1.Lines.Add('Driver:     '+sqlconnection1.DriverName);
  Memo1.Lines.Add('lib:        '+sqlconnection1.LibraryName);
  Memo1.Lines.Add('Connection: '+sqlconnection1.ConnectionName );

  Memo1.Lines.Add('Metadata:   '+
    IntToStr(Integer(sqlconnection1.ColumnMetaDataSupported)));

  Memo1.Lines.Add('Filecheck1: '+ 
    IntToStr(Integer(FileExists('c:\newff\myfile.db')));

  Memo1.Lines.Add('Filecheck2: '+ 
    IntToStr(Integer(FileExists(sqlconnection1.params.Values[2])));

  try
    with TFilestream.Create(sqlconnection1.params.Values[2],fOpenRead or fmShareDenyNone) do begin
      SetLength(a,15);
      ReadBuffer(a[1],15);
      Free;
    end;
  except 
    on Ex:Exception do a:='Exception: '+Ex.Message;
  end;

  Memo1.Lines.Add('SQLite Mark: '+String(a));

end;

I typed it from my head so please correct any syntax errors.

Run it and paste us the resulting text in the memo.

If the compiler fails on any of those that will also be very telling, so please let us know.

13:17 Reply: cannot create tables in sqlitestudio than other apps can see (artifact: 01c62713af user: cuz)

Having some bit of Delphi experience I can tell you that the above errors might not be at all what you think. It should work perfectly well.

I typically use a direct import of the SQLite DLL but the built-in Data-objects should work just fine too, though sometimes setting up the connection can be finicky and you have to watch out for any auto-magic transactions being started on your behalf. You could try the free SQLiteSpeed from here sqlitespeed.com to see a working Delphi SQLite engine, and if that system opens the file correctly then it should rule out Delphi being a problem. (You are also welcome to our unit code that deals with the SQLite DLL interfacing).

Other things to consider might be UAC messing with virtualizing the files so the file you "think" you are opening might not be the one you are indeed opening. Some older versions of Delphi used to not play well with Windows manifests and using correct user folders and the like.

I think the issues you are seeing are just some combination of the above, there are not much else I can imagine that will go wrong. SQLite is an abnormally solid piece of kit, and Delphi, while an overly expensive dev environment and having some bugs in near every IDE version, has a rather solid and well-maintained compiler and component library (what you pay all that money for).

If the above does not help to solve your problem, perhaps showing some code or posting a project on some file-share would help to enlighten.

11:16 Reply: Am I doing SQL wrong? (artifact: 72a0b08203 user: cuz)

Adding to what Dan proposed and what I noted, perhaps another suggestion (which always makes it easier for me to follow) is to use the comparisons in step, meaning the Boolean test:

(B > A) AND (B < C)
is better stated (for at least human intuition) following the mathematics notion of: [ A < B < C ]as:
(A < B) AND (B < C)

This means your CASE could be more intuitively expressed as:

CASE
  -- Testing Col1's in-the-middle-ness:
  WHEN Col2 < Col1 AND Col1 < Col3 THEN Col1 -- Note: We need 2 tests
  WHEN Col3 < Col1 AND Col1 < Col2 THEN Col1 

  -- Testing Col2's in-the-middle-ness:
  WHEN Col1 < Col2 AND Col2 < Col3 THEN Col2
  WHEN Col3 < Col2 AND Col2 < Col1 THEN Col2 

  -- Testing Col3's in-the-middle-ness:
  WHEN Col1 < Col3 AND Col3 < Col2 THEN Col3
  WHEN Col2 < Col3 AND Col3 < Col1 THEN Col3 

  ELSE -1 -- Some columns are Equal in this case.
END
11:06 Reply: Am I doing SQL wrong? (artifact: c24e36b098 user: cuz)

Your expectation is simply wrong.

I will take the second line and demonstrate the three tests for clarity:

Col1 = 21, Col2 = 12 and Col3 = 13. therefore:
Case
  Col1 > Col2 (--> True)  AND Col1 < Col3 (--> False) : Result: False
  Col2 > Col1 (--> False) AND Col2 < Col3 (--> True)  : Result: False
  Col3 > Col1 (--> False) AND Col3 < Col2 (--> False) : Result: False

That's 3 False evaluations, which leaves the ONLY possible result whatever is in the ELSE, hence returning -1.

Boolean math is at times somewhat hard for humans to intuitively test, but writing down the tests as above might help.

Best of luck.

More ↓