sqlite -...
TRANSCRIPT
![Page 1: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/1.jpg)
SQLiteA brief introduction to black magic for Mobile
Developers
![Page 2: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/2.jpg)
Custom SQLite version
![Page 3: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/3.jpg)
Custom SQLite version Installation1. Obtain the code2. Configure the native libraries.3. Build the native libraries4. Assemble the aar file
![Page 4: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/4.jpg)
Custom SQLite version Installation...or download compiled version and add is as a sub-project
dependencies { // Change "sqlite-android-3130000" to the name of the new module! compile project(':sqlite-android-3130000') }
![Page 5: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/5.jpg)
Indexes
![Page 6: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/6.jpg)
IndexesAn index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently
![Page 7: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/7.jpg)
Primary Key
![Page 8: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/8.jpg)
Primary KeyCREATE TABLE countries ( country_id INTEGER PRIMARY KEY, name text NOT NULL);
![Page 9: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/9.jpg)
Primary KeyCREATE TABLE countries ( country_id INTEGER PRIMARY KEY, name text NOT NULL);
![Page 10: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/10.jpg)
Questions
Can PRIMARY KEY be null?
![Page 11: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/11.jpg)
Question
Can PRIMARY KEY be null?
YES. Because of bug in early versions of SQLite
![Page 12: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/12.jpg)
ROWID
![Page 13: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/13.jpg)
ROWIDCREATE TABLE countries ( country_id INTEGER, name text NOT NULL);
....
SELECT * FROM countries WHERE rowid > 5
![Page 14: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/14.jpg)
ROWIDCREATE TABLE countries ( country_id INTEGER PRIMARY KEY, name text NOT NULL);
....
SELECT * FROM countries WHERE rowid > 5
![Page 15: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/15.jpg)
ROWIDCREATE TABLE countries ( country_id INTEGER PRIMARY KEY, ← Alias for ROWID name text NOT NULL);
....
SELECT * FROM countries WHERE rowid > 5
![Page 16: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/16.jpg)
Question
Can we use ROWID as a logical row ID?
![Page 17: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/17.jpg)
Question
Can we use ROWID as a logical row ID?
NO. ROWID could be changed in some circumstances (e.g. “VACUUM”)
![Page 18: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/18.jpg)
Complex Primary Key
![Page 19: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/19.jpg)
Primary KeyCREATE TABLE country_languages ( country_id integer NOT NULL, language_id integer NOT NULL, PRIMARY KEY (country_id, language_id),);
![Page 20: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/20.jpg)
Primary KeyCREATE TABLE country_languages ( country_id integer NOT NULL, language_id integer NOT NULL, PRIMARY KEY (country_id, language_id),);
![Page 21: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/21.jpg)
Question
What is faster - ROWID or Primary Key?
![Page 22: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/22.jpg)
Question
What is faster - ROWID or Primary Key?
ROWID. It is used as a key in B-Tree
![Page 23: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/23.jpg)
Autoincrement
![Page 24: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/24.jpg)
AutoincrementCREATE TABLE country_languages ( country_id integer NOT NULL AUTOINCREMENT, language_id integer NOT NULL, PRIMARY KEY (country_id, language_id),);
![Page 25: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/25.jpg)
Question
Why we need Autoincrement if we already have ROWID?
![Page 26: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/26.jpg)
Question
Why we need Autoincrement if we already have ROWID?
“the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows” docs
![Page 27: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/27.jpg)
Indexes On Expressions
![Page 28: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/28.jpg)
Indexes On ExpressionsCREATE TABLE transactions( transaction_id INTEGER PRIMARY KEY, account_id INTEGER REFERENCES account, amount INTEGER, -- in cents...);CREATE INDEX transactions_magnitude ON transactions(account_id, abs(amount));
![Page 29: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/29.jpg)
Indexes On ExpressionsSELECT * FROM transactions WHERE account_id=... AND abs(amount)>=10000;
![Page 30: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/30.jpg)
Question
Can we use Expression as a Primary Key?
![Page 31: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/31.jpg)
Question
Can we use Expression as a Primary Key?
NO. They are not unique.
![Page 32: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/32.jpg)
FTS
![Page 33: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/33.jpg)
FTSCREATE VIRTUAL TABLE email_fts USING fts5(sender, title, body)
![Page 34: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/34.jpg)
FTSSELECT * FROM email_fts WHERE email MATCH 'term' ORDER BY rank;
SELECT highlight(email, 2, '<b>', '</b>') FROM email_fts('term');
SELECT * FROM email('fts5') WHERE MATCH '"one two thr" * ';
SELECT * FROM email('fts5') WHERE MATCH 'NEAR(one two) three';
![Page 35: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/35.jpg)
Question
Do we need a Primary Key in FTS table?
![Page 36: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/36.jpg)
Question
Do we need a Primary Key in FTS table?
NO. FTS use ROWID + other indexes
![Page 37: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/37.jpg)
R*Tree
![Page 38: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/38.jpg)
R*TreeCREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX, -- Minimum and maximum X coordinate minY, maxY -- Minimum and maximum Y coordinate);
![Page 39: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/39.jpg)
R*TreeSELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND minY>=35.00 AND maxY<=35.44;
![Page 40: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/40.jpg)
Question
What type SQLite use for minX, maxX, minY, maxY?
![Page 41: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/41.jpg)
Question
What type SQLite use for minX, maxX, minY, maxY?
32bit float. Say “hello” to Roundoff Error
![Page 42: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/42.jpg)
R*TreeSELECT objname FROM demo_data, demo_index WHERE demo_data.id=demo_index.id AND contained_in(demo_data.boundary, :boundary) AND minX>=-81.0 AND maxX<=-79.6 AND minY>=35.0 AND maxY>=36.2;
![Page 43: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/43.jpg)
R*TreeCREATE TABLE demo_data( id INTEGER PRIMARY KEY, -- primary key objname TEXT, -- name of the object objtype TEXT, -- object type boundary BLOB -- detailed boundary of object);
![Page 44: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/44.jpg)
JSON
![Page 45: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/45.jpg)
JSONCREATE TABLE `users` (
`id` INTEGER, `name` TEXT, `body` JSON, PRIMARY KEY(`id`)
)
![Page 46: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/46.jpg)
JSONSELECT * FROM users WHERE json_extract(body, '$.data.phone') = 323
![Page 47: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/47.jpg)
JSONUPDATE user SET body = json_replace(body, '$.data.phone', 3) WHERE id = 1
![Page 48: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/48.jpg)
JSONCREATE TABLE `users` (
`id` INTEGER, `name` TEXT, `phones` JSON, -- [‘213-231-2’, ‘555-5533’, ‘12345’]PRIMARY KEY(`id`)
)
![Page 49: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/49.jpg)
JSONSELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%';
![Page 50: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/50.jpg)
JSONSELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%';
![Page 51: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/51.jpg)
JSONCREATE TABLE json_tree( key ANY, -- key for current element relative to its parent value ANY, -- value for the current element type TEXT, -- 'object','array','string','integer', etc. atom ANY, -- value for primitive types, null for array & object id INTEGER -- integer ID for this element parent INTEGER, -- integer ID for the parent of this element fullkey TEXT, -- full path describing the current element path TEXT, -- path to the container of the current row json JSON HIDDEN, -- 1st input parameter: the raw JSON root TEXT HIDDEN -- 2nd input parameter: the PATH at which to start);
![Page 52: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/52.jpg)
Question
What’s the difference between JSON and TEXT fields?
![Page 53: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/53.jpg)
Question
What’s the difference between JSON and TEXT fields?
No difference at all :( Because of backward compatibility
![Page 54: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/54.jpg)
Recursive
![Page 55: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/55.jpg)
RecursiveCREATE TABLE staff( name TEXT PRIMARY KEY, boss TEXT REFERENCES org, salary INT, --cents);
![Page 56: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/56.jpg)
RecursiveWITH RECURSIVE works_for_vasya(n) AS ( VALUES('Vasya') UNION SELECT name FROM staff, works_for_vasya WHERE org.boss=works_for_vasya.n )SELECT avg(salary) FROM org WHERE org.name IN works_for_vasya;
![Page 57: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/57.jpg)
DEMO
![Page 58: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/58.jpg)
![Page 59: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/59.jpg)
53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79
![Page 60: SQLite - it-perspektiva.altexsoft.comit-perspektiva.altexsoft.com/past-years/documents/2018/SQLite.pdf · // Change "sqlite-android-3130000" to the name of the new module! compile](https://reader035.vdocuments.us/reader035/viewer/2022070809/5f081dd47e708231d4206b7f/html5/thumbnails/60.jpg)