SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Using rowid zero  (Read 110 times)

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Using rowid zero
« on: November 19, 2018, 07:40: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.
Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Using rowid zero
« Reply #1 on: November 19, 2018, 10:48:44 AM »

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

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Re: Using rowid zero
« Reply #2 on: November 19, 2018, 12:07:19 PM »

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

Paul Squires

  • Administrator
  • Master Geek
  • *****
  • Posts: 4223
    • View Profile
    • http://www.planetsquires.com
Re: Using rowid zero
« Reply #3 on: November 19, 2018, 03:27:23 PM »

Create table #61 and put your table descriptions there. Two fields... table name, table description.
Logged
Paul Squires
http://www.planetsquires.com
support@planetsquires.com

Fim

  • Guru
  • ***
  • Posts: 153
    • View Profile
    • Fims sajt
Re: Using rowid zero
« Reply #4 on: November 20, 2018, 03:26:59 AM »

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

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Using rowid zero
« Reply #5 on: November 20, 2018, 08:51: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
« Last Edit: November 20, 2018, 08:58:35 AM by cj »
Logged
CJ

cj

  • Master Geek
  • ****
  • Posts: 696
    • View Profile
Re: Using rowid zero
« Reply #6 on: November 20, 2018, 01:08: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
Logged
CJ