Similar to SQL subquery, but with some differences: stackoverflow.com/questions/706972/difference-between-cte-and-subquery
rm -f tmp.sqlite
sqlite3 tmp.sqlite 'create table t(i integer)'
sqlite3 tmp.sqlite 'insert into t values (1), (2)'
sqlite3 tmp.sqlite 'with mycte as ( select * from t ) delete from mycte where i = 1'
sqlite3 tmp.sqlite 'select * from t'How to implement Nested set model in SQL:
- stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/42781302#42781302 contains the correct left/size representation and update queries, which makes it much easier to maintain the tree without having to worry about the sizes of siblings which are constant
- stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/194031#194031 amazing ASCII art representations of the structure. Unfortunately uses a wonky left/right representation, rather than the much more natural left/size representation from the other post
Python sequence test data generation: stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python/76659706#76659706
Example: sqlite/ip.c, adapted from www.sqlite.org/loadext.html, also mentioned explained at: stackoverflow.com/questions/7638238/sqlite-ip-address-storage/76520885#76520885.
Sample usage in the test program: sqlite/test.sh.
@cirosantilli/_file/python/sympy_cheat/python/sympy_cheat/logarithm_integral.py by 
Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01
Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01pytorch.org/vision/0.13/models.html has a minimal runnable example adapted to python/pytorch/resnet_demo.py.
That example uses a ResNet pre-trained on the COCO dataset to do some inference, tested on Ubuntu 22.10:This first downloads the model, which is currently 167 MB.
cd python/pytorch
wget -O resnet_demo_in.jpg https://upload.wikimedia.org/wikipedia/commons/thumb/6/60/Rooster_portrait2.jpg/400px-Rooster_portrait2.jpg
./resnet_demo.py resnet_demo_in.jpg resnet_demo_out.jpgWe know it is COCO because of the docs: pytorch.org/vision/0.13/models/generated/torchvision.models.detection.fasterrcnn_resnet50_fpn_v2.html which explains that is an alias for:
FasterRCNN_ResNet50_FPN_V2_Weights.DEFAULTFasterRCNN_ResNet50_FPN_V2_Weights.COCO_V1The runtime is relatively slow on P51, about 4.7s.
After it finishes, the program prints the recognized classes:so we get the expected
['bird', 'banana']bird, but also the more intriguing banana.By looking at the output image with bounding boxes, we understand where the banana came from!
python/pytorch/resnet_demo_out.jpg
. The beak was of course a banana, not a beak! couldn't save system state: Minimum free space to take a snapshot and preserve ZFS performance is by Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01
Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01This BS started after the move to ZFS. The temporary solution appears to be: askubuntu.com/questions/1293685/out-of-space-on-boot-zpool-and-cant-run-updates-anymore/1374204#1374204
And then this to disable automatic snapshots in the future: askubuntu.com/questions/1233049/disable-automatic-zsys-snapshots-zfs-on-root/1279593#1279593
sudo mv /etc/apt/apt.conf.d/90_zsys_system_autosnapshot /etc/apt/apt.conf.d/90_zsys_system_autosnapshot.disabledThis has annoyed Ciro Santilli for many years, it is just too wasteful of screen space on laptops!
Or likely more generally, on GNOME desktop, which is the default desktop environment as of Ubuntu 22.10.
Update multiple rows with different values in a single SQL query by Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01
Ciro Santilli 35 Updated 2025-03-28 +Created 1970-01-01This happens when you calculate a bunch of values on your program, and then want to save the to SQL.
Vertebrates minus tetrapods.
This paraphyletic subgroup is easy to form the "acquatic only" (fishes) vs "things that come out of water" (tetrapods). Though mudfish make that distinction harder.
Which kind of makes sense, why would you want for limbs unless you are going to stay out of water!
sqlite3 ':memory:' 'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over () FROM t'-1|1
-1|2
-2|3With a possible output:
partition by:sqlite3 ':memory:' 'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over ( partition by i ) FROM t'-2|1
-1|1
-1|2rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
(0, 0),
(1, 5),
(2, 10),
(3, 14),
(4, 15),
(5, 16),
(6, 20),
(7, 25),
(8, 29),
(9, 30),
(10, 30),
(11, 31),
(12, 35),
(13, 40)
EOFShow how many neighbours each column has with Output:
val between val - 2 and val + 2 inclusive:sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1val - 1 and val + 1 inclusive instead:sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1There seems to be no analogue to HAVING for window functions, so we can just settle for a subquery for once, e.g.:which outputs:
sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
SELECT id, val, COUNT(*) OVER (
ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as c FROM t
) WHERE c > 2
EOF4|15|3
8|29|3
9|30|4
10|30|4
11|31|3 Unlisted articles are being shown, click here to show only listed articles.