• Welcome, Guest. Please login.
 
April 18, 2019, 09:55:09 am

News:

Welcome to the SQLitening support forums!


Using rowid zero

Started by Fim, November 19, 2018, 09:10:22 am

Previous topic - Next topic

Fim

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.
Fim W

cj

No problem unless:
"The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY."

Fim

Thanks,
I'll skip it all.
/Fim W
Fim W

Paul Squires

Create table #61 and put your table descriptions there. Two fields... table name, table description.

Fim

Paul,
That's what I've solved now.
/Fim
Fim W

cj

November 20, 2018, 10:21:52 am #5 Last Edit: November 20, 2018, 10:28:35 am by cj
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

cj

'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