SQLite Forum

Timeline
Login

23 forum posts by user XiongZaiBingGan

2021-05-07
11:33 Post: balance_quick() add a new leaf page which contain only 1 cell (artifact: acf7c3adae user: XiongZaiBingGan)

I found when insert a new record,if it cause overflow, insert operation invokes balance_quick(),in balance_quick(),it create a new leaf page,contain the overflow cell,make it to the right most leaf page of tree.

The right most leaf page only contain 1 cell(overflow cell),does sqlite still treat it as a b+tree page ? in my idea, m-way b-tree cell count is in (m/2-1,m-1)。

I find database file header only has 'Min leaf payload fraction' but no 'Max leaf payload fraction'.

2021-04-14
11:36 Post: why index table use b-tree not b+tree (artifact: b9e0cec903 user: XiongZaiBingGan)

Anyone knows why index table use b-tree but not b+tree? if need get a range of rowids in index table,b+tree is better than b-tree .

2021-04-12
03:51 Reply: find in b+-tree node (artifact: 16592f4526 user: XiongZaiBingGan)

I don't describe it clearly enough,i want to know when get rowid,how to use the rowid to get record from b+tree node ? I think b+tree node is a page,a page contains many records.how sqlite know rowid in which b+tree node?

03:23 Post: find in b+-tree node (artifact: 05868d7498 user: XiongZaiBingGan)

My test environment is:

#create table test (name text,age int);

#create index idx on test(name);

#table test include a record (name:'tom',age:32). My confusion is when I apply the following statement,how sqlite match the record and the b+-tree node which include the record ? statement:select * from test where name='tom';

First,I say my understanding, but I'm not sure if it's correct, so I hope you can confirm it for me. If there are any mistakes, please correct them。

##My understanding is :vm engine use tree-module service,tell tree-module vm want to get record which name='tom'.tree-module first do binary-search in a b-tree (which holds the content of the index of table test) to find rowid,then do a binary-search in b+tree (which holds the records of the table test) by rowid.

when get rowid,how to map the rowid to tree-node which include the rowid?

2021-03-13
07:56 Post: integer primary key and index table (artifact: 6d09521c57 user: XiongZaiBingGan)

The document says 'integer primary key ' is an alias for the rowid.so if a column set 'integer primary key',does sqlite create a index table to mapping this column and rowid columm?

select * from table1 where col = 123; if col is integer primary key,does this select statement do a binary search in index-table for rowid first,then use the rowid to do binary search for row data in table1?

2021-02-19
13:08 Reply: statement subtransaction journal (artifact: 678aa9c2ff user: XiongZaiBingGan)

Thanks.As the document describes,'statement journal' seems like 'save point'.

2021-02-18
12:21 Post: statement subtransaction journal (artifact: 48e2794097 user: XiongZaiBingGan)

Who can give me an example to show which scenario will use the 'statement subtransaction journal '?

2021-02-09
01:29 Reply: pending lock macro (artifact: 3f0b5ea607 user: XiongZaiBingGan)

thank you,i use linux api fcntl() set lock at offset 1GB in a file which filesize less than 1GB,it locks successful.my question has been solved.

2021-02-07
10:16 Reply: pending lock macro (artifact: 9ccdf9d1d6 user: XiongZaiBingGan)

thanks for your answer,but the docs don't describe when database size less than 1GB,which region in the file is used for the DEPEING_BYTE.do you know about the details of the implement?

04:28 Post: pending lock macro (artifact: 1697c1aa51 user: XiongZaiBingGan)

a sqlite book says:'The PENDING_BYTE macro (0x40000000, the first byte past the 1 GB boundary defines the beginning of the lock bytes) is the default byte used for setting PENDING locks.'

  • i'm not sure about is the 0x40000000 offset from the beginning of the database file ? if it is ,when the database file size less than 0x40000000(aka 1GB),where is region for the pending_byte in database file ?
2021-02-05
03:26 Post: customize min and max payload (artifact: 78b34fd916 user: XiongZaiBingGan)
  • question:
    • the page header has min and max payload value for internal page and leaf page.can i modify the min and max payload ? if i can and how to modify the value ?
    • my app scenarios is if record count is huge, i want to modify the min payload smaller to include more navigational information in a B+TREE page.can this improve the performance in search and reduce the I/O when memory is not enough to contain the entire database records.
2021-01-20
05:39 Reply: multi thread mode (artifact: 00e575dd87 user: XiongZaiBingGan)

sqlite3* db, sqlite3_stmt* stmt, int rc, and int val all defines in the myInsert() method.

in this situation, is my example code thread-safe in 'multi-thread' mode ?

#void* myInsert(void* arg)

#{

sqlite3* db = 0;

sqlite3_stmt* stmt = 0;

int val = (int)arg;

char SQL[100];

int rc;

rc = sqlite3_open("MyDB", &db); /* Open a database named MyDB */

if (rc != SQLITE_OK) {

fprintf(stderr, "Thread[%d] fails to open the databasen", val);

goto errorRet;

}

sprintf(SQL, "insert into Students values(%d)", val);

rc = sqlite3_prepare(db, SQL, -1, &stmt, 0);

if (rc != SQLITE_OK) {

fprintf(stderr, "Thread[%d] fails to prepare SQL: %s ->

#return code %dn", val, SQL, rc);

goto errorRet;

}

rc = sqlite3_step(stmt);

if (rc != SQLITE_DONE) {

fprintf(stderr,

"Thread[%d] fails to execute SQL: %s -> return code %dn", val, SQL, #rc);

}

else {

printf("Thread[%d] successfully executes SQL: %sn", val,

SQL);

}

errorRet:

sqlite3_close(db);

return (void*)rc;

#}

03:57 Post: multi thread mode (artifact: ac88c47c46 user: XiongZaiBingGan)

i have read the document 'Using SQLite In Multi-Threaded Applications',but i don't get the real difference between the 'multi-thread' and 'Serialized' mode.

as the document says: in 'multi-thread' mode , SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

SQLITE_THREADSAFE = 2 descriptions the following: SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time.

#1.in the following code,is thread-safe when SQLITE_THREADSAFE=2(in 'multi-thread' mode)?

#2.when sqlite3_open() create 2 connection object in 2 individual thread (the 2 individual threads in same process),the 2 connection object is same or different?

####void* myInsert(void* arg)

{

sqlite3_open("MyDB", &db); /* Open a database named MyDB */

sprintf(SQL, "insert into Students values(%d)", val);

rc = sqlite3_prepare(db, SQL, -1, &stmt, 0);

rc = sqlite3_step(stmt);

sqlite3_close(db);

return NULL;

####}

####int main(void)

####{

for (i=0; i < 10; i++)

pthread_create(&t[i], 0, myInsert, (void*)i);

for (i=0; i<10; i++)

pthread_join(t[i], 0);

return 0;

####}

2021-01-18
12:35 Reply: payload and overflow page (artifact: c1506acfd2 user: XiongZaiBingGan)

thanks for your answer,more cells in a node,the b+/b-tree's depth is smaller.

2021-01-16
08:17 Post: payload and overflow page (artifact: 76e6bb3a48 user: XiongZaiBingGan)

<inside sqlite> says: Once an overflow page is allocated, as many bytes as possible are moved into the overflow pages without letting the cell size drop below the min embedded payload fraction value (in the database file header at offset 22).

what are the benefits of this? why move as many as possible ?

2021-01-09
10:54 Post: lock and low concurrency (artifact: bcf42991e4 user: XiongZaiBingGan)

hi everybody, i'm reading the book <inside sqlite> that publish in April 15, 2007.in the book ,it says: "the sqlite lock is database file oriented.so if any one transaction is writing to any part of a database file, all other transactions are prevented from reading or writing any part of the file."

in most common case,one transaction want to read/write table_1 and another transaction read or write table_2.

anyone knows does sqlite support table-oriented lock in new version?

if sqlite doesn't support table-oriented lock ,how to improve the performance in case transaction operate in different table?

in my simple idea, make an agreement at application level: 1 database-file only contains 1 table,so the lock in different tables is lock in different database file.the concurrency performance is better. anyone knows method to improve the performance ?

2021-01-02
15:07 Reply: sector flush atomic (artifact: 42eb027415 user: XiongZaiBingGan)

thanks for your careful explanation.i get it. ^_^

06:34 Post: sector flush atomic (artifact: f9fb911774 user: XiongZaiBingGan)

if the sector write is linear at disk driver layer,sqlite transaction is still atomic?

in this case: when save the page count(at journal header) to journal file,the page count is 4 bytes size,so if the power lose between the first 2 bytes save in journal file successful and last 2 bytes not save in file. then power restore,the page count at the journal file head is the first 2 bytes value,the last 2 bytes value is missed.the rollback only rewrite the frist 2 bytes count page content to database file.yeah,crictal error happens.

is my personal understanding right?

2020-12-31
12:22 Post: lock pending (artifact: 1a90e3d527 user: XiongZaiBingGan)

I have a question: the book <SQLite Database System Design and Implementation> chapter 4.2.2 says: A pending lock is always just a temporary stepping stone to the path to an exclusive lock.

is that mean when obtain exclusive lock success,the writelock at "pending byte" is unlocked ? anyone knows about it?

2020-07-15
01:50 Reply: how to check update statement really modify a record (artifact: 7f9cfd47e9 user: XiongZaiBingGan)

i just want to know if a record was modified or not,i get the answer by other post.

in the situation:

begin;

1:sqlite3_exec("insert statement...");

2:sqlite3_exec("update table set xx=abc where id=911");

3:sqlite3_exec("insert statement...");

commit;

if step 1,2,3 all success,invoke commit,otherwise rollback. in step 2 ,because of 911 is a invalid value,so update statement can't find a record which id equal to 911 in table ,i think update fail.so step 3 in skipped. the transaction rollback.but sqlite3_exec still return sqlite_ok.so i want to check a record was modified or not.

thank you all the same.:)

01:34 Reply: how to check update statement really modify a record (artifact: 42983a91e0 user: XiongZaiBingGan)

thanks ,Your suggestion solved my problem.

in myself idea,sqlite3_exec("update statement") return the modified row count may be better, because it's no need invoke sqlite3_changes() to get affected row count.

why the author don't implement sqlite3_exec return affected row count?

01:33 Reply: how to check update statement really modify a record (artifact: d072f9bac1 user: XiongZaiBingGan)

thanks ,Your suggestion solved my problem.

in myself idea,sqlite3_exec("update statement") return the modified row count may be better because it's no need invoke sqlite3_changes().

why the author don't implement sqlite3_exec return affected row count?

2020-07-14
12:34 Post: how to check update statement really modify a record (artifact: 88b9df0dff user: XiongZaiBingGan)

hi,when i use sqlite3_exec() to do "update table set column_name=value where id=911;". but if no sql syntax,the sqlite3_exec() always return SQLITE_OK even if 'where' clause is not true (no record that id equal to 911).

is there any method to check the update statement is really modify a record ?