|
Post by pierre on Dec 29, 2022 7:13:22 GMT -5
OK I understand. Thanks Dan.
|
|
|
Post by meerkat on Dec 31, 2022 6:54:27 GMT -5
BOM. Bill of Materials are always somewhat complex. This is my attempt to produce an indented bill of materials.
I've used this type routine to produce family trees from ancestry data. In my particular case, I have 38,000 names. And the tree on some people is 5 to 10 pages long.
This routine is simple compared to other documented routines to get a BOM. The trade off is this probably takes more time.
On my computer, running it in RB takes 10ms, and running it in LB takes 753ms.
Here is a sample and again it runs completely in memory.
begTime = time$("ms")
' -------- Make memory DB ------------------- sqliteconnect #mem, ":memory:"
' -------------------- Create bom table ------------------------------------ mem$ = "CREATE TABLE bom ( bomNum, lvl, explored, itemNum, ofItemNum, chain )" #mem execute(mem$)
' --------------------- Create item table ----------------------------------- mem$ = "CREATE TABLE item ( itemNum SMALLINT(3), ofItemNum SMALLINT(3), descr VARCHAR(22) )" #mem execute(mem$)
' -------------------- Make some item records ------------------------------
mem$ = "INSERT INTO item VALUES (1,1,'Table') ,(2,16,'Brass Plate') ,(3,1,'Top') ,(4,1,'Leg Left Front') ,(5,1,'Leg Left Back') ,(6,16,'Lock Washer') ,(7,1,'Lef Right Front') ,(8,1,'Leg Right Back') ,(9,1,'Trim Front') ,(10,4,'Bolt 1/2 inch') ,(11,9,'Half inch screw') ,(12,9,'Paint Varnish') ,(13,9,'Screw 1 inch') ,(14,9,'Trim Design') ,(15,16,'Key Pin') ,(16,1,'Trim Ends') ,(17,16,'Twist Lock') ,(18,10,'Washer 1/2 inch') ,(19,18,'Washer Lock') ,(20,19,'Washer Lock') ,(21,18,'Bramble Apt C') ,(22,18,'Bramble Apt D')" #mem execute(mem$)
print "---------- show item list ---------------------" mem$ = "SELECT * FROM item order by itemNum" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows
WHILE #mem hasanswer() #row = #mem #nextrow() itemNum$ = #row itemNum$() ofItemNum$ = #row ofItemNum$() descr$ = #row descr$() print itemNum$;"|";ofItemNum$;"|";descr$ WEND
print "-------------------------------------" ' -------------------------------- ' User picks item number ' Place it in the bom table ' -------------------------------- itemNum = 1 bomNum = 1
mem$ = "INSERT INTO bom VALUES(1,1,'N',";itemNum;",";itemNum;",'0000')" #mem execute(mem$)
' -------------- let the bom begin ---------------------
x$ = "" cma$ = "" rows = 1 chainNum = 0 WHILE rows > 0 mem$ = "SELECT *,bom.rowid FROM bom WHERE explored <> 'Y' LIMIT 1" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows WHILE #mem hasanswer() #row = #mem #nextrow() itemNum$ = #row itemNum$() rowid$ = #row rowid$() chain$ = #row chain$()
mem$ = "UPDATE bom SET explored = 'Y' WHERE rowid = ";rowid$ #mem execute(mem$)
cma$ = "" mem$ = "SELECT * FROM item WHERE itemNum <> ";itemNum$;" AND ofItemNum = ";itemNum$ #mem execute(mem$) x$ = "" WHILE #mem hasanswer() #row = #mem #nextrow() itemNum$ = #row itemNum$() ofItemNum$ = #row ofItemNum$()
chainNum = chainNum + 1 chain1$ = chain$ + right$("0000";chainNum,4) x$ = x$ + cma$ + "(";bomNum;",";bomNum;",'N',";itemNum$;",";ofItemNum$;",'";chain1$;"')" cma$ = "," WEND if x$ <> "" then mem$ = "INSERT INTO bom VALUES ";x$ #mem execute(mem$) end if WEND WEND
' ----------------------------------- ' bom complete ' Print indented Bill of Materials ' -----------------------------------
mem$ = "SELECT * FROM bom LEFT JOIN item ON item.itemNum = bom.itemNum ORDER BY chain" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows print "There are ";rows;" items in the BOM" WHILE #mem hasanswer() #row = #mem #nextrow() itemNum$ = #row itemNum$() descr$ = #row descr$() ofItemNum$ = #row ofItemNum$() explored$ = #row explored$() chain$ = #row chain$() indent$ = left$("................",len(chain$) / 4) indent$ = left$(indent$;itemNum$;" ",12) print indent$;chr$(9);descr$;chr$(9);chain$ WEND print "=============== Thats all folks ===============================" endTime = time$("ms") print "Total Time:";endTime - begTime
#mem disconnect() end
|
|
|
Post by meerkat on Jan 3, 2023 12:40:12 GMT -5
Export and Import CSV files. I'm almost embarrassed to show this, because almost everyone knows how to export and import CSV files from a DB. And there are soo many ways to do it. Just in case someone is interested, here is one way.
Timing LB = 161 ms RB = 52 ms
begTime = time$("ms")
' ----- make memory DB sqliteconnect #sql, ":memory:"
sql$ = "CREATE TABLE tbl(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r)" #sql execute(sql$)
sql$ = "INSERT INTO tbl VALUES (2,0,'Y','M0210','T','L13','01','180.15','0','0','17118','17118','0','0','0','0','187',''), (2,0,'Y','M0209','T','L19','02','108.59','0','0','10274','10274','0','0','0','0','186',''), (2,0,'Y','M0208','T','L18','03','52.52','0','0','4948','4948','0','0','0','0','185',''), (2,0,'Y','M0207','T','L20','04','162.65','0','0','15255','15255','0','0','0','0','184',''), (2,0,'Y','M0206','T','L03','05','56.99','0','0','5322','5322','0','0','0','0','183',''), (2,0,'Y','M0205','T','L19','06','160.65','0','0','14936','14936','0','0','0','0','182',''), (2,0,'Y','M0204','T','L13','07','170.85','0','0','15814','15814','0','0','0','0','181',''), (2,0,'Y','M0203','T','L10','08','56.23','0','0','5181','5181','0','0','0','0','180',''), (2,0,'Y','M0202','T','L02','09','185.58','0','0','17024','17024','0','0','0','0','179',''), (2,0,'Y','INIT','F','','0','10','0','105872','0','105872','0','0','0','0','178',''), (3,0,'Y','M0213','T','L04','11','10.14','0','0','976','976','0','0','0','0','0',''), (3,0,'Y','M0214','T','L01','12','137.51','0','0','13291','13291','0','0','0','0','0',''), (3,0,'Y','M0215','T','L20','13','188.14','0','0','18262','18262','0','0','0','0','0',''), (3,0,'Y','M0216','T','L18','14','203.91','0','0','19876','19876','0','0','0','0','0',''), (3,0,'Y','M0217','T','L12','15','133.02','0','0','13020','13020','0','0','0','0','0',''), (3,1,'Y','INIT', 'F','','999.99','0','0','116834','0','116834','0','0','0','0','0',''), (3,1,'N','M0211','T','L01','17','362','0','15900','30979','15979','0','0','0','0','0','1999-01-03 08:15:00'), (3,1,'N','M0212','T','L09','18','413','0','18300','36356','18356','0','0','0','0','0','1999-01-03 08:15:00'), (3,1,'N','M0213','T','L04','19','22','0','970','1876','976','0','0','0','0','0','1999-01-03 08:15:00'), (3,1,'N','M0214','T','L01','20','295','0','13260','26291','13291','0','0','0','0','0','1999-01-03 08:15:00')"
#sql execute(sql$)
open "a.csv" for output as #f ' -------- change these if you want a different output ----------------- csvEnc$ = "'" ' enclosed with csvSep$ = "," ' seperator csvEol$ = ";" ' end of line character shoHdr$ = "Y" ' show header (Y/N) howMany = 20 ' how many records to csv
sql$ = "SELECT * FROM tbl" if howMany > 0 then sql$ = sql$ + " LIMIT ";howMany 'does user want a limit
#sql execute(sql$)
if shoHdr$ = "Y" then colNames$ = #sql columnnames$() print colNames$ end if
WHILE #sql hasanswer() a$ = #sql nextrow$(chr$(215)) a$ = strRep$(a$,"'","\'") ' escape the single quotes a$ = strRep$(a$,"""","\""") ' escape the double quotes a$ = csvEnc$;strRep$(a$,chr$(215),csvEnc$;csvSep$;csvEnc$);csvEnc$;csvEol$ ' to your format print a$ print #f,a$ WEND print "----------------------- End of Output ----------------------" close #f open "a.csv" for input as #f txt$ = input$(#f, lof(#f)) close #f
print "----------------------- CSV File ---------------------------------" print txt$
a$ = strRep$(txt$,";"+chr$(13), "),"+chr$(13)+"(") 'format to sql a$ = "INSERT INTO tbl VALUES (";left$(a$,len(a$) - 3) 'get rid of ending ( print " ------------------- File ready to load ---------------------------" print a$
' ------------------------------------------------ ' clear the tbl and load the csv file back
sql$ ="DELETE FROM tbl" ' erase tbl records #sql execute(sql$) #sql execute(a$) ' load CSV data back
#sql disconnect()
endTime = time$("ms") print "Run Time:";endTime - begTime;" ms"
end
' -------------------------------- ' string replace rep str with ' -------------------------------- FUNCTION strRep$(str$,rep$,with$) ln = len(rep$) ln1 = ln - 1 i = 1 while i <= len(str$) if mid$(str$,i,ln) = rep$ then strRep$ = strRep$ + with$ i = i + ln1 else strRep$ = strRep$ + mid$(str$,i,1) end if i = i + 1 WEND END FUNCTION
|
|
|
Post by meerkat on Feb 7, 2023 13:28:34 GMT -5
Another BOM method using the SQLite RECURSIVE function. It's suprisingly fast.
This code uses a in-memory parts file. The part table has a partID. So that it knows what part of a BOM assembly it belongs it has a ofPartId of the part it belongs to. It also has a quantity (qty) of how many parts it needs in the BOM. This code produces a BOM that also keeps track of the level.
sqliteconnect #sql, ":memory:"
sql$ = "CREATE TABLE part ( partId VARCHAR(4), ofPartId VARCHAR(4), name VARCHAR(22), qty DECIMAL(5,2) );"
#sql execute(sql$) sql$ = " INSERT INTO part VALUES ('1','','part 1',1) ,('2','1','part 2',1) ,('3','1','part 3',1) ,('4','3','part 4',1) ,('5','3','part 5',1) ,('6','3','part 6',1) ,('7','4','part 7',1) ,('8','5','part 8',1) ,('9','5','part 9',1) ,('a','5','part a',1) ,('b','6','part b',1) ,('c','7','part c',1) ,('d','7','part d',1) ,('e','2','part e',10) ,('11','','part 11',11) ,('21','11','part 21',22) ,('31','11','part 31',33) ;" #sql execute(sql$)
sql$ = " WITH RECURSIVE bom(partId, ofPartId, name, qty, level) AS ( SELECT partId, ofPartId, name, qty, 1 AS level FROM part WHERE partId = '1' UNION ALL SELECT p.partId, p.ofPartId, p.name, p.qty, bom.level + 1 AS level FROM bom JOIN part p ON bom.partId = p.ofPartId ) SELECT partId, ofPartId, name, qty, level FROM bom; " #sql execute(sql$) rows = #sql ROWCOUNT() 'Get the number of rows print "|PartId","|OfPartId","|Name","|Qty","|Level","|" WHILE #sql hasanswer() #row = #sql #nextrow() partId$ = #row partId$() ofPartId$ = #row ofPartId$() name$ = #row name$() qty = #row qty() level = #row level()
print "|";partId$,"|";ofPartId$,"|";left$("******",level);name$,"|";qty,"|";level,"|" WEND
#sql disconnect() wait
|
|
|
Post by pierre on Feb 8, 2023 6:04:17 GMT -5
Interesting! I am not very familiar with recursive functions, so here I have something to study carefully.
Thanks !
pierre
|
|
|
Post by meerkat on Feb 9, 2023 3:48:15 GMT -5
Interesting! here I have something to study carefully. While studying, maybe you can figure out how to get each item to show all it's levels before going to the next item. What this does is show it in level sequence. I think there may be a way to sort it somehow so it shows each item and it's complete levels. Or maybe I need to create another memory table with the stuff needed to get each level's complete BOM Thanks for the help.. Dan
|
|
|
Post by meerkat on Feb 10, 2023 5:25:22 GMT -5
The WITH RECURSIVE clause in SQL is used to create a Common Table Expression (CTE). A CTE is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is not physically stored as a table, but rather is just a named subquery that can be used to simplify complex queries.
The WITH RECURSIVE clause allows you to define a recursive query, which means a query that references itself in the FROM clause. This allows you to perform operations such as walking a tree-like structure, calculating the Fibonacci sequence, or finding the transitive closure of a graph.
Here is an example that shows how to use the WITH RECURSIVE clause:
WITH RECURSIVE fibonacci(n, f) AS ( SELECT 1, 1 UNION ALL SELECT n + 1, f + fibonacci.f FROM fibonacci WHERE n < 20 ) SELECT * FROM fibonacci; In this example, the CTE fibonacci is defined to calculate the first 20 terms of the Fibonacci sequence. The WITH RECURSIVE clause is used to specify that the query references itself, and the UNION ALL clause is used to combine the results from each iteration of the query.
|
|
|
Post by meerkat on Feb 16, 2023 6:17:15 GMT -5
The WITH RECURSIVE is useful in a lot of ways. Any time you want a series, or want a loop, this lets you do it. For example suppose you had a database of sales. The sales has a date and qty. You don't care about the item, you only want a sales figure for each day of the month regardless if that day had sales or not. Some days may have multiple sales. Regardless you want one sales amount per every day. Here is an example that starts on a given date through a given date. To get each day it adds 1 as '+1 day' to get every date. With simple mods to the query you could just as easily done it by week, or month.."
'-- Create the sales table in memory
sqliteconnect #mem, ":memory:"
sql$ = "CREATE TABLE sales ( itemId VARCHAR(4), saleDate DATE, name VARCHAR(22), qty DECIMAL(5,2) );" #mem execute(sql$)
sql$ = "INSERT INTO sales VALUES ('1','2023-01-01','item 1',1), ('2','2023-01-01','item 2',11), ('3','2023-01-03','item 3',21), ('4','2023-01-05','item 4',31), ('5','2023-01-07','item 5',41), ('6','2023-01-09','item 6',51), ('7','2023-01-11','item 7',16), ('8','2023-01-11','item 8',17), ('9','2023-01-12','item 9',18), ('a','2023-01-15','item a',19), ('b','2023-01-17','item b',12), ('c','2023-01-21','item c',19), ('d','2023-01-23','item d',22), ('e','2023-01-24','item e',20), ('11','2023-01-27','item 11',21), ('21','2023-01-29','item 21',22), ('31','2023-01-30','item 31',33);" #mem execute(sql$)
'-- Generate a list of dates for the month sql$ = "WITH RECURSIVE dates(date) AS ( SELECT date('2023-01-01') UNION ALL SELECT date(date, '+1 day') FROM dates WHERE date < date('2023-01-31') ) SELECT d.date, COALESCE(SUM(s.qty), 0) AS totSales FROM dates d LEFT JOIN sales s ON d.date = s.saleDate GROUP BY d.date ORDER BY d.date;" #mem execute(sql$) colNames$ = #mem columnnames$() print colNames$
WHILE #mem hasanswer() #mrow = #mem #nextrow() date$ = #mrow date$() totSales = #mrow totSales() print date$;" ";totSales;left$(*********************************";totSales) WEND end
|
|
|
Post by metro on Feb 17, 2023 22:34:42 GMT -5
very interesting Dan, how complicated is it to GROUP BY name within each date? I really need to find the time to study these nested queries, there are so many possibilities. having too much fun in the shed ATM.
|
|
|
Post by meerkat on Feb 18, 2023 1:49:26 GMT -5
If all you want is a GROUP BY you don't even need to mess with RECURSIVE.
'-- Create the sales table in memory
sqliteconnect #mem, ":memory:"
sql$ = "CREATE TABLE sales ( itemId VARCHAR(4), saleDate DATE, name VARCHAR(22), qty DECIMAL(5,2) );" #mem execute(sql$)
sql$ = "INSERT INTO sales VALUES ('1','2023-01-01','item 1',1), ('2','2023-01-01','item 2',11), ('1','2023-01-03','item 1',21), ('4','2023-01-05','item 4',31), ('1','2023-01-07','item 1',41), ('6','2023-01-09','item 6',51), ('7','2023-01-11','item 7',16), ('8','2023-01-11','item 8',17), ('1','2023-01-12','item 1',18), ('a','2023-01-15','item a',19), ('b','2023-01-17','item b',12), ('1','2023-01-21','item 1',19), ('d','2023-01-23','item d',22), ('e','2023-01-24','item e',20), ('11','2023-01-27','item 11',21), ('21','2023-01-29','item 21',22), ('31','2023-01-30','item 31',33);" #mem execute(sql$)
'-- Generate a list of dates for the month
sql$ = "SELECT *,sum(qty) as totSales FROM sales GROUP BY saleDate,itemId" #mem execute(sql$) print "date","| itemId","| name","| totSales"
WHILE #mem hasanswer() #mrow = #mem #nextrow() date$ = #mrow saleDate$() itemId$ = #mrow itemId$() name$ = #mrow name$() totSales = #mrow totSales() if preDate$ <> date$ and preDate$ <> "" then print preDate$ = date$ print date$,"| ";itemId$,"| ";name$,"| ";totSales
WEND end
|
|
|
Post by metro on Feb 18, 2023 3:20:33 GMT -5
Ah, simples thanks, Dan
|
|
|
Post by meerkat on Feb 25, 2023 8:25:14 GMT -5
PRIMARY KEY AUTOINCREMENT You hear a lot about making a control value of a record the PRIMARY KEY AUTOINCREMENT. It does what you expect. As you add records it increments the last value used. Basically it's nothing more than the internal rowid that is maintained anyway. So why have the extra overhead, use the rowid..
Problem with the AUTOINCREMENT field is in volatile files where you do a lot of adds, changes and deletes, that number can grow. I've had systems where downtime is not allowed and even a file with only 1000 records added, or changed, or deleted, on a daily basis there comes a time where you need to reorganize the records. Sometimes this can take hours or even days.
I always reuse the value of deleted records. The file will eventually grow to a certain size and remain at that size forever. Here is a routine that hunts for holes in the value and uses that value.
' -------- Make memory DB ------------------- sqliteconnect #mem, ":memory:"
' -------------------- Create bom table ------------------------------------ mem$ = "CREATE TABLE empMast (empNum INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, salary DECIMAL(8.2), deptId INTEGER);"
#mem execute(mem$)
mem$ = "INSERT INTO empMast (firstName,lastName,salary,deptId) VALUES ('Honey','Patel', 10100,1), ('Shweta','Jariwala', 19300,2), ('Vinay','Jariwala', 35100,3), ('Jagruti','Viras', 9500,2), ('Shweta','Rana',12000,3), ('sonal','Menpara', 13000,1), ('Yamini','Patel', 10000,2), ('Khyati','Shah', 50000.22,3), ('Shwets','Jariwala',19400,2);" #mem execute(mem$)
gosub [shoEmp] print print "****** Notice the empNum and rowId are the same***" print
mem$ = "DELETE FROM empMast WHERE empNum = 3" #mem execute(mem$) mem$ = "INSERT INTO empMast (firstName,lastName,salary,deptId) VALUES ('Vinay','Jariwala', 35100,3)" #mem execute(mem$) gosub [shoEmp] print print "**** Notice after deleting empNum 3 and inserting a new record, ****" print "**** it adds it at the end.And the rowid and empNum are the same ****" print "**** So the question is why maintain a primary key with ****" print "**** autoencrement when you can simply use the rowid ****"
print "**** Delete the empNum 10 "
mem$ = "DELETE FROM empMast WHERE empNum = 10" #mem execute(mem$) print '**** find a empty slot or add at the end " empNum = useNum("empMast","empNum") print "**** found hole in the file at ";empNum
mem$ = "INSERT INTO empMast VALUES (";empNum;",'Vinay','Jariwala', 35100,3)" #mem execute(mem$) gosub [shoEmp] print "**** Notice it reused empNum 3 ****"
end
[shoEmp] mem$ = "SELECT *,rowid as rowId from empMast" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows print "There are ";rows;" records in empMast" colNames$ = #mem columnnames$() +"," print "Column names:";colNames$ for i = 1 to 6 print word$(colNames$,i,","), next i print WHILE #mem hasanswer() #row = #mem #nextrow() empNum = #row empNum() firstName$ = #row firstName$() lastName$ = #row lastName$() salary = #row salary() deptId$ = #row deptId$() rowId = #row rowId() print empNum,firstName$,lastName$,salary,deptId$,rowId WEND RETURN
' ------------------------------------------------------------------------------------ ' Find available record number ' This does not always find the next number but finds holes in the numbers and uses it ' Therefore, you never run out of numbers and you never need to reorganize the files ' ------------------------------------------------------------------------------------ FUNCTION useNum(ffile$,ffield$) useNum = 1 mem$ = "SELECT a.";ffield$;" AS aa, c.";ffield$;" AS cc,a.";ffield$;" - 1 AS useNum FROM ";ffile$;" AS a
LEFT JOIN ";ffile$;" as c ON c.";ffield$;" = a.";ffield$;" - 1 WHERE c.";ffield$;" is null AND a.";ffield$;" > 1 ORDER BY a.";ffield$;" LIMIT 1"
#mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows if rows > 0 then result$ = #mem nextrow$(" |") aa = val(word$(result$,1,"|")) cc = val(word$(result$,2,"|")) useNum = val(word$(result$,3,"|")) else mem$ = "SELECT max(";ffield$;") as useNum FROM ";ffile$ #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows if rows > 0 then result$ = #mem nextrow$(" |") useNum = val(word$(result$,1,"|")) + 1 end if end if END FUNCTION
|
|
|
Post by tsh73 on Feb 25, 2023 10:53:06 GMT -5
Is it actually a problem?
Is this field is 64 bit integer? Then maximum values is ~10^20, and it is said to be more then seconds passed after Big Bang (~10^18, or so Google says) So I do not expect that number be overflowing.
|
|
|
Post by meerkat on Feb 25, 2023 12:16:00 GMT -5
From a SQLite limit, I doube anyone will ever reach the end. Even if it did it will try to find a empty slot.
If you don't mind large numbers, then ignore the above routine. Some people don't like typing in large numbers to look something up. I've run into the situation where some reports, invoices, pay checks, or other documents have limited space to print the empNum, custNum, or whatever. Actually more times than I thouhgt it would happen.
I looked it up.. Here is the offical explination; The rowid value starts at 1. The maximum value of the rowid column is 9,223,372,036,854,775,807. If your data reaches this maximum value and you attempt to insert a new row, SQLite will find an unused integer and uses it. If SQLite cannot find any unused integer, it will issue an SQLITE_FULL error.
|
|
|
Post by pierre on Feb 27, 2023 11:10:50 GMT -5
The above described method is very handy for taking advantage of unused 'holes' in the database and not let the primary key values rise to very large numbers. This implies IMHO that no VACUUM will be processed on the database.
Preferring the 'rowid' (the real 'hidden' primary key) above a separate 'primary key' column is not always the best solution. When the primary key of a table can be expressed as an INTEGER, the preferred solution is creating a 'INTEGER PRIMARY KEY' column, which automatically increments. This column becomes then an ALIAS for the 'rowid', and as far as I have been able to understand, the same 'rowid' B-tree structure is then used for indexing.
Relying exclusively on the 'rowid' can be problematic:
see the SQLite documentation on 'Rowid' vs 'without Rowid' tables :
".../... " "If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change. In particular the VACUUM command will change "rowids for tables that do not declare an INTEGER PRIMARY KEY. Therefore, applications should not normally access the rowid directly, "but instead use an INTEGER PRIMARY KEY. " ".../... " "All of the complications above (and others not mentioned here) arise from the need to preserve backwards compatibility for the hundreds "of billions of SQLite database files in circulation. In a perfect world, there would be no such thing as a "rowid" and all tables would be following "the standard semantics implemented as WITHOUT ROWID tables, only without the extra "WITHOUT ROWID" keywords. "Unfortunately, life is messy. The designer of SQLite offers his sincere apology for the current mess."
|
|