SQLite Forum

Timeline
Login

20 forum posts by user skywalk

2021-09-04
13:13 Reply: about "strict" mode (artifact: 4e69f93b37 user: skywalk)
Arrg, I was quoting from your datatypes link?
..."the storage class of the text is converted to INTEGER or REAL"
If insertions have decisions to make in non-STRICT mode, my natural assumption is those are bypassed in STRICT mode. 
Why dance around my question with so many words?
Fewer decisions = faster. Yes|No?
You do mention STRICT insertions have more decisions, but may be faster overall?
I guess I have to do tests.
2021-09-02
17:03 Reply: about "strict" mode (artifact: a1387204b3 user: skywalk)

On performance, it is exactly the affinity issue in that link which confuses me. In strict mode, why all these checks required and dropping them would not improve insertion time? "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL."

15:28 Reply: about "strict" mode (artifact: 6c0f19f142 user: skywalk)
Of course there is a finite list of REAL identifiers. 
Are you implying FLOAT8,FLOAT4,etc?
I thunk there would be SINGLE and DOUBLE, but SQLite defaults all to DOUBLE internally for REAL?

On performance, does SQLite store data internally as both TEXT and REAL for floating data in non strict mode?
14:13 Reply: about "strict" mode (artifact: e290b32e85 user: skywalk)
I prefer this strict mode but chose poorly for my REAL name :(
I use "DOUBLE" instead of REAL. Is there any chance this will be accepted?
...Old notes to self...
SQLite has 5 fundamental datatypes:
SQLITE3
SQLITE_INTEGER     = 1     ; 64-bit signed integer, INTEGER, SMALLINT, etc
SQLITE_FLOAT       = 2     ; 64-bit IEEE floating point number, DECIMAL(10,5), DATETIME, DATE, BOOLEAN, etc
SQLITE_TEXT        = 3     ; String, CHAR, VARCHAR(20), etc
SQLITE_BLOB        = 4
SQLITE_NULL        = 5
STRING CONSTANTS For DDL Statements:
SQLITE_INTEGER     = "INTEGER" ; 64-bit signed integer, INTEGER, SMALLINT, etc
SQLITE_FLOAT       = "DOUBLE"  ; 64-bit IEEE floating point number, DECIMAL(10,5), DATETIME, DATE, BOOLEAN, etc
SQLITE_TEXT        = "TEXT"    ; String, CHAR, VARCHAR(20), etc
SQLITE_BLOB        = "BLOB"
SQLITE_NULL        = "NULL"
...
Will there be a performance advantage using PRAGMA strict=ON;?
2021-05-12
12:55 Reply: Fast way to insert rows in SQLite (artifact: 3694675536 user: skywalk)

How does WITHOUT ROWID affect performance?

2021-05-07
12:57 Reply: User Defined Functions (artifact: 7f8ccfa88d user: skywalk)
The tragedy is the lengths people go to avoid answering a question.
2021-04-15
14:28 Reply: Pragma compile_options does not show some options (artifact: 8f8a6e8cc3 user: skywalk)
Yaaay!
I stumbled on this before when debugging external tools using precompiled sqlite binaries.
For clarity, are all options runtime tracked or we must continue to report missing pragma and compiler options?
2020-12-14
22:56 Reply: SQLite3 Extension (artifact: 3a1a5d236d user: skywalk)

Nice! I see a sqrt(). :)

15:12 Reply: DB Browser Crash (artifact: 6d8f6e15ef user: skywalk)
2020-09-23
13:54 Reply: sqlite-gui application (artifact: f82cf01006 user: skywalk)

Ok, Latest exe crashes on [Right-Mouse-Clk]-Export to csv of recent query results.

2020-09-16
18:06 Reply: sqlite-gui application (artifact: 2c1e0e03c7 user: skywalk)
I am fine with the gui and its speed and lack of bloat.

The [CR]'s were in addition to [CR+LF]'s. 
Thus, 1 empty line in the exported csv.

I will get you a db when I have more time.

You really should add support for blobs. 
All my databases have at least 1 blob column.
13:31 Reply: sqlite-gui application (artifact: b5778bd056 user: skywalk)
Close but not ready for primetime. 
No blob is nogo.
And I crashed it with simple export to csv.
A smaller export inserted [CR]'s at end of every row?
2020-06-29
14:26 Reply: Can of worms - Easiest way to build a UI for Sqlite? (artifact: e7ef85b9e1 user: skywalk)
I use DB Browser for SQLite(https://nightlies.sqlitebrowser.org/latest/) for prototyping and querying.
If you also want a visual guide for prototyping with ER diagrams, there is DBeaver, (https://dbeaver.io/download/).
Both are open source.
2020-05-30
14:10 Reply: SQLite turns 20 (artifact: 7628155462 user: skywalk)

Please sir, may we have 20 more?!

2020-04-24
14:44 Reply: (Deleted) (artifact: 4fda6cd73b user: skywalk)
Please don't delete your error condition. That is a terrible tease. :(
This fix solved a critical JOIN query(https://sqlite.org/forum/forumpost/c8f99ee0ba) in my code, but my brain could not determine that from the patch comment:
"Fix problems in the constant propagation optimization that were introduced by check-in [1c3e5c20a9e6f501]."
Your end use case can save others time in determining root cause of failures.
2020-04-06
22:54 Edit reply: JOIN query behaves differently with/without trailing ON? (artifact: 85738a0268 user: skywalk)
Yes thanks, this is coming back to me.
Now my sqlite3.dll has no exports. 
How to enable that in the Makefile?

# These are additional compiler options used for the core library.
#
!IFNDEF CORE_COMPILE_OPTS
!IF $(DYNAMIC_SHELL)!=0 || $(FOR_WIN10)!=0
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS) -DSQLITE_API=__declspec(dllexport)
!ELSE
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS)
!ENDIF
!ENDIF

Bypassing nmake, I get a working dll with the following cmd:
c:\sqlite-snapshot-202004061816>cl sqlite3.c -DSQLITE_THREADSAFE -DSQLITE_DQS=0 -DSQLITE_MAX_ATTACHED=125 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll
22:47 Reply: JOIN query behaves differently with/without trailing ON? (artifact: 80cc622b7a user: skywalk)
Yes thanks, this is coming back to me.
Now my sqlite3.dll has no exports. 
How to enable that in the Makefile?

# These are additional compiler options used for the core library.
#
!IFNDEF CORE_COMPILE_OPTS
!IF $(DYNAMIC_SHELL)!=0 || $(FOR_WIN10)!=0
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS) -DSQLITE_API=__declspec(dllexport)
!ELSE
CORE_COMPILE_OPTS = $(CORE_CCONV_OPTS)
!ENDIF
!ENDIF
21:59 Reply: JOIN query behaves differently with/without trailing ON? (artifact: 3c3b1e5887 user: skywalk)

Whew!! Thanks for the quick reply. I cannot remember? Is the download page compiled from latest code or must I build from source?

Thanks for SQLite!

21:45 Post: JOIN query behaves differently with/without trailing ON? (artifact: ede763f636 user: skywalk)
I have racked my brain to understand why the newish SQLite3.dll behaves differently for this query?
I only want to retrieve 1 row from this table.
2   12000.0   31.2      26.12    -12.11   -12.22   1.12  
If I comment out the ON clause, it works.
But, I never had to do that in earlier versions of SQLite3.dll.

Please advise why I see 3 rows now?

I also confirmed this behavior manually with DB Browser For SQLite.
Older versions yield only 1 row.
Newest version yields 3 rows! :(

-- Create SQLite database --
CREATE TABLE TD(`n(id)` INTEGER NOT NULL PRIMARY KEY, `TI(id)` INTEGER DEFAULT -999, `DieSN` TEXT DEFAULT '-999', `Die(n)` INTEGER DEFAULT -999, `Die(wm)` DOUBLE DEFAULT -999, `DieX(um)` INTEGER DEFAULT -999, `DieY(um)` INTEGER DEFAULT -999, `DieZ(um)` INTEGER DEFAULT -999, `DieM(mm)` DOUBLE DEFAULT -999, `DieA(°)` DOUBLE DEFAULT -999, `Bin(n)` INTEGER DEFAULT -999, `TT(s)` DOUBLE DEFAULT -999, `TTp(s)` DOUBLE DEFAULT -999, `n(n)` INTEGER DEFAULT -999, `TPG(n)` INTEGER DEFAULT -999, `T(C)` DOUBLE DEFAULT -999, `Vdd(V)` DOUBLE DEFAULT -999, `Idd(A)` DOUBLE DEFAULT -999, `RFPath` TEXT DEFAULT '-999', `Pi(dBm)` DOUBLE DEFAULT -999, `F(MHz)` DOUBLE DEFAULT -999, `S21(dB)` DOUBLE DEFAULT -999, `S11(dB)` DOUBLE DEFAULT -999, `S22(dB)` DOUBLE DEFAULT -999, `NF(dB)` DOUBLE DEFAULT -999);
-- INSERT DATA --
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,1,-999, -999, -999, -999, -999, -999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0206,6000, 26.06, -6.11, -6.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0212,12000,26.12,-12.11,-12.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,2,0.0218,18000,26.18,-18.11,-18.22,-999);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0306,6000, -999, -999, -999, 1.06);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0312,12000,-999, -999, -999, 1.12);
INSERT INTO TD (`TI(id)`,`Die(n)`,`TPG(n)`,`Idd(A)`,`F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)`,`NF(dB)`) VALUES (1,1,3,0.0318,18000,-999, -999, -999, 1.18);
-- 7 TD rows inserted.
-- JOIN + ON Query --
SELECT r1.`F(MHz)` AS `F(MHz)`,r2.`Idd(A)`*1e3 AS `Idd(mA)`,
r1.`S21(dB)` AS `S21(dB)`,r1.`S11(dB)` AS `S11(dB)`,r1.`S22(dB)` AS `S22(dB)`,
r2.`NF(dB)` AS `NF(dB)`
FROM
(SELECT `F(MHz)`,`S21(dB)`,`S11(dB)`,`S22(dB)` FROM TD WHERE
`TI(id)` = 1 AND
`Die(n)` = 1 AND
`F(MHz)` = 12000 AND
`TPG(n)` = 2) AS r1
JOIN
(SELECT `Idd(A)`,`F(MHz)`,`NF(dB)` FROM TD WHERE
`TI(id)` = 1 AND
`Die(n)` = 1 AND
`F(MHz)` = 12000 AND
`TPG(n)` = 3) AS r2
ON r1.`F(MHz)` = r2.`F(MHz)`

-- JOIN + ON Query Results --
6000.0    31.2      26.06    -6.11    -6.22    1.12     
12000.0   31.2      26.12    -12.11   -12.22   1.12     
18000.0   31.2      26.18    -18.11   -18.22   1.12
2020-03-18
15:19 Reply: Renaming a database (artifact: fef112992d user: skywalk)
Filenames are absolutely data, else the risk of corruption would not be grave.
That said, renaming a shared asset is not good engineering.