How to manage a sqlite3 database on a squashfs filesystem?

hello, guys i developed a mini webapp to run on a small card with openwrt backfire (they are far behind with this version but for what i have to do it is perfect). I have tested the app and everything and now i need to create a package to install it at the compile time of openwrt.

The application uses a sqlite3 database that in tests i loaded into memory (/ tmp) and some js/css libraries that I loaded into the / www folder. My problem is in writing and reading the database. Having manually loaded the db in the temporary folder everything works but how can i package the application and load the db into memory at each system start, that is, how should I manage everything, do you have any advice? i have little experience about this, moreover i would like to avoid using file system also in writing such as jffs2 to avoid the wear of the flash memory for too many operations that i would do on the database but i am open to any valid solution.

about the card i use, this is it https://forum.openwrt.org/t/porting-openwrt/131706

Writing?
A read/write sqlite3 database on a squashfs filesystem?

Squashfs is a compressed read-only file system for Linux.
You can compress and write the whole database into squashfs at once, but you can't later update/write any individual item in it.

What are you trying to accomplish regarding writing?

Based on that "operations that I would do on the database", I think that squashfs in unsuitable for you.

1 Like

Actually, evaluating the thing better, i would use the database only to save some configurations, so the write operations are minimal and for reading there is no limit with the spi flash memory.

Yes, you are perfectly right!

What do you advise me to do if i change the file system to jffs2 for example? are there any better locations to put the database?

What i was wondering however is where the configuration files are saved, by default even if the file system is squashfs the manufacturer releases the card with a small webapp that has settings and the status is kept even after reboot if you change the parameters.

The normal OpenWrt approach has been

  • read-only squashfs rootfs +
  • read-write overlay with jffs2 (earlier, nor flash) or ubi (with nand flash)

config files and user-installed packages are stored into the read-write part.

1 Like

Thanks for the explanation. Currently the configuration is as follows...

dev:    size   erasesize  name
mtd0: 00010000 00010000 "boot"
mtd1: 00010000 00010000 "cdb"
mtd2: 006e0000 00010000 "firmware"
mtd3: 000e0000 00010000 "linux"
mtd4: 005fffd0 00010000 "rootfs"
mtd5: 00900000 00010000 "rwfs"


Filesystem                Size      Used Available Use% Mounted on
/dev/root                 2.6M      2.6M         0 100% /
devtmpfs                 14.5M         0     14.5M   0% /dev
tmpfs                    14.5M      2.3M     12.2M  16% /tmp
mini_fo:/tmp/etc          2.6M      2.6M         0 100% /etc

I imagine that the read-only + write.. part is supported in this case by mini_fo, which to date has been supplanted by OverlayFS (cit wiki .. . Used to merge two filesystems, one read-only and the other writable...) right?

How can i compile this type of configuration? i tried to compile and flash the image generated having also selected jffs2 as support * firmware-jffs2-64k.bin rather than firmware-squashfs.bin but it does not start.

Based on the above data from you, I imagine that

  • the read-only part is mtd2=mtd3+mtd4 (firmware = kernel + rootfs)
  • the read-write part is the mtd5 (rwfs)

But I have no idea if you device has overlayfs compiled in. You should look into the sources of that device in order to fully see things.
Or you might try mounting the rwfs separately.

It is possible that the manufacturer does not use overlayfs at all, but stores settings etc. directly into the separate rwfs without combining it to rootfs.

You might look for clues in the kernel boot log.

free_area_init_node: node 0, pgdat 80265bb0, node_mem_map 81000000
  Normal zone: 64 pages used for memmap
  Normal zone: 0 pages reserved
  Normal zone: 8128 pages, LIFO batch:0
Built 1 zonelists in Zone order, mobility grouping on.  Total pages: 8128
Kernel command line: root=/dev/mtdblock4 mem=0x2000000@0 console=ttyS0 kgdboc=ttyS0 rootfstype=squashfs noinitrd
PID hash table entries: 128 (order: -3, 512 bytes)
Dentry cache hash table entries: 4096 (order: 2, 16384 bytes)
Inode-cache hash table entries: 2048 (order: 1, 8192 bytes)
Primary instruction cache 16kB, VIPT, 4-way, linesize 16 bytes.
Primary data cache 16kB, 4-way, VIPT, no aliases, linesize 16 bytes
Memory: 29640k/32768k available (2108k kernel code, 3128k reserved, 347k data, 120k init, 0k highmem)
SLUB: Genslabs=12, HWalign=16, Order=0-3, MinObjects=0, CPUs=1, Nodes=1
Hierarchical RCU implementation.
NR_IRQS:16
console [ttyS0] enabled
Calibrating delay loop... 511.18 BogoMIPS (lpj=2555904)
Mount-cache hash table entries: 512
devtmpfs: initialized
NET: Registered protocol family 16
bio: create slab <bio-0> at 0
Switching to clocksource CHEETAH
NET: Registered protocol family 2
IP route cache hash table entries: 1024 (order: 0, 4096 bytes)
TCP established hash table entries: 1024 (order: 1, 8192 bytes)
TCP bind hash table entries: 1024 (order: 0, 4096 bytes)
TCP: Hash tables configured (established 1024 bind 1024)
TCP reno registered
NET: Registered protocol family 1
ttyS0 is enabled
plat_pwr_init() successful
[vcom_probe, 570]: vcom add ttyS0
Cheetah Ether driver version 0.0.1
squashfs: version 4.0 (2009/01/31) Phillip Lougher
Registering mini_fo version $Id$
JFFS2 version 2.2. (NAND) (SUMMARY)     2001-2006 Red Hat, Inc.
msgmni has been set to 57
io scheduler noop registered
io scheduler deadline registered (default)
HDLC line discipline maxframe=4096
N_HDLC line discipline registered.
PPP generic driver version 2.4.2
PPP Deflate Compression module registered
PPP BSD Compression module registered
PPP MPPE Compression module registered
NET: Registered protocol family 24
PPPoL2TP kernel driver, V1.0
Camelot Watchdog Timer: heartbeat 60 sec
Registered led device: cheetah_gpio6
Registered led device: cheetah_gpio7
GRE over IPv4 tunneling driver
TCP cubic registered
NET: Registered protocol family 10
NET: Registered protocol family 17
802.1Q VLAN Support v1.8 Ben Greear <greearb@candelatech.com>
All bugs added by David S. Miller <davem@redhat.com>
cheetah-flash cheetah-flash.0: sfd->cbase=af000020
cheetah-flash cheetah-flash.0: uniform_64k_flash0 (16384 Kbytes)


cheetah_flash_probe:995 ksize:917504

Creating 6 MTD partitions on "cheetah-flash":
0x000000000000-0x000000010000 : "boot"
0x000000010000-0x000000020000 : "cdb"
0x000000020000-0x000000700000 : "firmware"
0x000000020000-0x000000100000 : "linux"
0x000000100030-0x000000700000 : "rootfs"
mtd: partition "rootfs" must either start or end on erase block boundary or be smaller than an erase block -- forcing read-only
mtd: partition "rootfs" set to be root filesystem
0x000000700000-0x000001000000 : "rwfs"
VFS: Mounted root (squashfs filesystem) readonly on device 31:4.
devtmpfs: mounted
Freeing unused kernel memory: 120k freed
mini_fo: using base directory: /etc
mini_fo: using storage directory: /tmp/etc
CDB kernel module init
fuse init (API version 7.13)
device eth0 entered promiscuous mode
device eth0 left promiscuous mode
CPU_CLK = 320000000
SYS_CLK = 150590000
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
eth0: no IPv6 routers present
eth0.0: no IPv6 routers present
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
phy_status: phyno(1) sts(7808) debug(7808)
eth0.0: no IPv6 routers present

In the menuconfig kernel I have this possible configuration by default...

Schermata 2022-07-26 alle 21.34.22

if i change to extra partition I can specify the offset

Schermata 2022-07-26 alle 21.34.44



/* the default MTD partition layout is designed to use with OpenWRT */
#define MTDPART_BOOT_OFS           0x00000000
#define MTDPART_BOOT_SIZE          0x00010000
#define MTDPART_CDB_OFS            (MTDPART_BOOT_OFS + MTDPART_BOOT_SIZE)
#define MTDPART_CDB_SIZE           0x00010000
#if defined(CONFIG_CHEETAH_BACKUP_IMAGE_BUILD)
    #define MTDPART_LINUX_OFS          (CONFIG_CHEETAH_MTD_SIZE - CONFIG_CHEETAH_BACKUP_SIZE)
    #define MTDPART_LINUX_SIZE         0x00200000
    #define MTDPART_ROOTFS_OFS         (MTDPART_LINUX_OFS + MTDPART_LINUX_SIZE)
    #define MTDPART_ROOTFS_SIZE        (CONFIG_CHEETAH_BACKUP_SIZE - MTDPART_LINUX_SIZE)
    #define MTDPART_FIRMWARE_OFS       (MTDPART_CDB_OFS + MTDPART_CDB_SIZE)
    #define MTDPART_FIRMWARE_SIZE      (CONFIG_CHEETAH_MTD_SIZE - CONFIG_CHEETAH_BACKUP_SIZE - MTDPART_FIRMWARE_OFS - MTDPART_RWFS_SIZE)
#else
    #define MTDPART_LINUX_OFS          (MTDPART_CDB_OFS + MTDPART_CDB_SIZE)
    #define MTDPART_LINUX_SIZE         0x00200000
    #define MTDPART_ROOTFS_OFS         (MTDPART_LINUX_OFS + MTDPART_LINUX_SIZE)
    #define MTDPART_ROOTFS_SIZE        (CONFIG_CHEETAH_MTD_SIZE - CONFIG_CHEETAH_BACKUP_SIZE - MTDPART_ROOTFS_OFS - MTDPART_RWFS_SIZE)
    #define MTDPART_FIRMWARE_OFS       MTDPART_LINUX_OFS
    #define MTDPART_FIRMWARE_SIZE      (MTDPART_LINUX_SIZE + MTDPART_ROOTFS_SIZE)
#endif
#if defined(CONFIG_MTD_RWPART_EXTRA_PARTITION)
    #define MTDPART_RWFS_OFS           CONFIG_MTD_RWPART_EXTRA_PARTITION_OFFSET
    #define MTDPART_RWFS_SIZE          (CONFIG_CHEETAH_MTD_SIZE - CONFIG_MTD_RWPART_EXTRA_PARTITION_OFFSET - CONFIG_CHEETAH_BACKUP_SIZE)
#else
    #define MTDPART_RWFS_SIZE          0
#endif
#ifndef CONFIG_CHEETAH_BACKUP_IMAGE
    #define CONFIG_CHEETAH_BACKUP_SIZE 0
#endif
#if (MTDPART_ROOTFS_SIZE <= 0)
#error "No Space for rootfs!!"
#endif

#if (CHEETAH_VERSION_CODE < CHEETAH_VERSION(2,0))
#error "Don't Support Cheetah Version 1.0"
#endif
struct mtd_partition cheetah_partitions[] =
{
    {
        .name       = "boot",
        .size       = MTDPART_BOOT_SIZE,
        .offset     = MTDPART_BOOT_OFS,
    },
    {
        .name       = "cdb",
        .size       = MTDPART_CDB_SIZE,
        .offset     = MTDPART_CDB_OFS,
    },
    {
        .name       = "firmware",
        .size       = MTDPART_FIRMWARE_SIZE,
        .offset     = MTDPART_FIRMWARE_OFS,     //MTDPART_OFS_APPEND
    },
    {
        .name       = "linux",
        .size       = MTDPART_LINUX_SIZE,
        .offset     = MTDPART_LINUX_OFS,        //MTDPART_OFS_APPEND
    },
    {
        .name       = "rootfs",
        .size       = MTDPART_ROOTFS_SIZE,      //MTDPART_SIZ_FULL
        .offset     = MTDPART_ROOTFS_OFS,       //MTDPART_OFS_APPEND
    },
#if defined(CONFIG_MTD_RWPART_EXTRA_PARTITION)
    {
        .name       = "rwfs",
        .size       = MTDPART_RWFS_SIZE,
        .offset     = MTDPART_RWFS_OFS,
    },
#endif
    /* ALWAYS keep this part at the end of the structure to for indicating the partition numbers */
    {
        .size       = 0,
        .offset     = 0,
    },
};

and it appears to be rwfs

Ok, i managed to get it all working!

Schermata 2022-07-28 alle 02.52.50

now i can write :writing_hand: :writing_hand:

@hnyman thanks for the support, i appreciated!