SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fim on November 19, 2018, 09:10:22 am

Title: Using rowid zero
Post by: Fim on November 19, 2018, 09:10:22 am
I have a database of just over 60 tables. The names are always 3 letters, such as ARB, ART, FAB. Now I intend to insert a table description in each table on rowid = 0.
Is it good or bad?
/Fim W.
Title: Re: Using rowid zero
Post by: cj on November 19, 2018, 12:18:44 pm
No problem unless:
"The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY."
Title: Re: Using rowid zero
Post by: Fim on November 19, 2018, 01:37:19 pm
Thanks,
I'll skip it all.
/Fim W
Title: Re: Using rowid zero
Post by: Paul Squires on November 19, 2018, 04:57:23 pm
Create table #61 and put your table descriptions there. Two fields... table name, table description.
Title: Re: Using rowid zero
Post by: Fim on November 20, 2018, 04:56:59 am
Paul,
That's what I've solved now.
/Fim
Title: Re: Using rowid zero
Post by: cj on November 20, 2018, 10:21:52 am
Thanks, Paul for bringing up using table 61.
Unique keys can be created as needed for titles, totals, anything ...

#INCLUDE "sqlitening.inc"     'onthefly.bas
THREADED ts AS STRING         'combine results
'NOTE: if sql uses LIKE or SUBSTR a full table SCAN is always performed  instead of indexed SEARCH.
'If someone knows how to use search instead of scan using LIKE, please let us know!

FUNCTION PBMAIN () AS LONG

LOCAL sql,s AS STRING

slOpen "junk.db3","C"
slexe "drop table if exists t61"
slexe "create table t61(t61key text primary key,t61answer) without rowid"

slexe "insert or replace into t61 values('t01-client','1')"
slexe "insert or replace into t61 values('t01-total' ,'2')"
slexe "insert or replace into t61 values('t01-client','1000')" 'replace client
slexe "insert or replace into t61 values('t01-total' ,'2000')" 'replace total

GetData "select t61answer from t61 where t61key = 't01-client'"
GetData "select t61answer from t61 where substr(t61key,1,3) ='t01'" 'SCANS entire table
? ts
END FUNCTION

SUB Getdata(sql AS STRING)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
IF UBOUND(sArray)> 0 THEN ts+= sql + $CR + JOIN$(sArray(),",") + $CR + $CR
END SUB
Title: Re: Using rowid zero
Post by: cj on November 20, 2018, 02:38:46 pm
'Upsert without rowid might be optimal

'A big advantage of binding is text doesn't have to be encoded with single quotes (strings and even files can be insert or updated into column "as is".)
'A good example of this is trying to save a SQL statement that has single quotes, commas and (punctuation) that would be very hard to insert.
'The Upsert is definitely optional, but highly suggest using binding.

$Insert ="Insert into t1 values(?,?) ON conflict(c1) DO update SET c2=?"
#INCLUDE "sqlitening.inc" 'upsert.bas

FUNCTION PBMAIN () AS LONG
slOpen "upsert.db3","C"
slexe  "create table if not exists t1(c1 text primary key,c2 text) without rowid"
Upsert "key1","Jane Doe"
Upsert "key1","Heidi Klum"
END FUNCTION

FUNCTION Upsert(sKey AS STRING, sData AS STRING) AS LONG
LOCAL sBind AS STRING
sBind  = slBuildBindDat(sKey, "T") +_   'bind key as text
          slBuildBindDat(sData,"T") +_   'insert
          slBuildBindDat(sData,"T")      'on conflict (update)
slexebind $Insert,sBind
END FUNCTION