Lets make binary (specifically sqlite3) parsing easy on playdate using Kaitai Struct!

Collaboration
,
1

Kaitai describes itself like this:

Kaitai Struct is a declarative language used to describe various binary data structures, laid out in files or in memory: i.e. binary file formats, network stream packet formats, etc.

The main idea is that a particular format is described in Kaitai Struct language (.ksy file) and then can be compiled with ksc into source files in one of the supported programming languages. These modules will include a generated code for a parser that can read the described data structure from a file or stream and give access to it in a nice, easy-to-comprehend API.

What this means is that if you know the file format of a binary file you want to parse, e.g. an SQLite3 db, or an old NES savegame, you can create a lua parser for that file that will make it much easier to consume that data in a playdate game. If you've ever tried to parse a binary file without a tool like this you will understand how useful this would be.

Ideally it would work something like this:

  1. Install the katai struct compiler
  2. Download the relevant .ksy format file (or write one yourself)
  3. Compile the format into a parser for your language of choice, e.g.:
    kaitai-struct-compiler -t lua sqlite3.ksy
  4. That will create a file sqlite3.lua, then reading the db should be this easy:
local success  = require 'sqlite3'

local db        = Sqlite3:from_file('sqlite.db')

In my case I want to be able to parse and display the contents of a certain type of SQLite3 database on the device, but there is huge potential for other uses. One that comes to mind is porting old games that use binary file formats for their data files. There are many formats already contributed to the 'format gallery, some examples:

I have spent the last couple of weeks trying to get a sqlite3 parser working in lua. Unfortunately the state of the lua side of Kaitai is pretty poor. There was a PR that tried to fix some of the problems but it seems to have been abandoned, that was followed by another PR which attempted to fix the original PR but it hasn't had activity in the last 6 months.

I managed to get my db file parsed and most of the data is there, but I think that overflow pages are not being linked correctly. I posted my experiences on the more active PR here: sqlite3: fix parser by milahu · Pull Request #661 · kaitai-io/kaitai_struct_formats · GitHub

Hopefully I will get some response there but in any case this seems like a worthwhile project, not just for my needs. Please post here or DM me if you have any questions or comments, or even better would like to help out.

thanks for your time! happy playdating :playdate_heart:

1 Like
2

Thanks!-- I didn't know about the Kaitai project. Thank you for a nice and descriptive post with examples too (-: I have a few questions and thoughts, and I want to make sure we don't have an XY problem here...

If I understand your proposal well, you want to fix existing support of SQLite binary file format in Kaitai. If so, "make binary parsing easy" in the title is inaccurate, because there is no Playdate-specific changes needed and Kaitai already has a Lua bindings generator. Is that right?

On the subject of parsing SQLite binary files. I am genuinely puzzled why one would want to go down this road. I see it as redundant, harder, and more error-prone. On one hand, the SQLite project is a great example of already highly portable, widely available, and embeddable C code. On another, Playdate SDK supports making extensions (and writing entirely) in C. I'm sure SQLite supports the ARM architecture (Playdate's CPU) and there are plenty of Lua bindings available too.

That begs a question why: why not use SQLite directly to read its database files?

On the subject of having a database on Playdate. You want to parse and display certain SQLite databases on the device. Since you want to use Kaitai to read such a database file, I make two assumptions: you don't need to modify/write the file on the device; and your reads are rather simple comparing to full-fledged SQL queries. Am I guessing right?

  • If that's so, you may want to pre-process your SQLite database files on your dev workstation and then put on Playdate only data you actually need in a simpler format you actually want. Say, you could use command-line sqlite3 programme to query data and export it into something like a CSV file. Then put a CSV file on Playdate or even convert it into a Lua table directly.

  • If you do need some database on Playdate, there are other options and existing solutions that may be more apt for your needs too.

On the subject of reading "well-known" binaries on Playdate. For one, as I've mentioned in particular, you may want to cook your binaries before putting them on Playdate. Images and videos may be a good case for that (and pdc already does this). You'd save device storage and take unnecessary computations away from Playdate.

If you need an entire file in its original form or you want a user to be able to drop their own files on a device, then having a binary parser on device makes sense to me, yes. Aforementioned NES files could make a good case if, say, you were making a NES simulator for Playdate.

To recap and if tl;dr: (-:

  • You probably don't want nor need a Kaitai parser of SQLite database files.
  • There are potential cases for a Kaitai parser on device.
  • What's your ask, again?
2 Likes
3

Thanks for the detailed reply. I'm still trying to figure out my options, so your input is greatly appreciated.

You're right about the title, and I haven't tested other binary formats, but OTOH there is literally zero examples of kaitai struct being used with lua online, so at least I can document the process in a tutorial that would be applicable to other formats.

To answer some of your questions. First off, running sqlite itself would be a superior solution, and I looked into that. There's a forum post about it and there has been some progress on getting it to work on the STM chipset but that kind of stuff is way beyond my pay grade and there is quite a bit of debate about whether it would even work on the limited hardware.

So since I don't need to write to the db, just read, and I would prefer not to have a separate utility for preparing the data, it seemed a worthwhile feature to allow users just to dump the files onto the playdate and do the parsing etc on device.

I've edited the title to be more specific.

4

Naaaah-- come on, you've linked to one yourself! (-: It seems that website has usage examples for Kaitai supported languages for its collection of structs. There is also a large test suite. And the docs page links to Understanding industrial network traffic, dissectors and Lua and Kaitai.

Ah! I am sorry I was so sure about it. I've missed the fact SQLite relies on a certain set of host OS API. Playdate provides a custom, very limited API. That poses a challenge to support SQLite in userspace. I still believe it is possible, but now not so straightforward.

I think Playdate OS runs on FreeRTOS? A quick look suggests that FreeRTOS provides some subset of POSIX API, which is likely better suited to port SQLite and make it a part of Playdate SDK... BUT!-- as Dave's mentioned in the linked forum thread, they are not going to do it. I find it reasonable.

A few quick mentions, just for the record:

  • Playdate runs on ARM Cortex M7.
  • Chips used: Rev A has STM32F7. Rev B---STM32H7.
  • The mentioned project luismeruje/SQLite-STM32 is for STM32H743ZI, which is ARM Cortex M7. However, it's intended to run on bare metal(?) and Playdate has an OS.
  • One SQLite forum post that describes how SQLite could be ported to run on ARM Cortex-M7.

Again: it largely depends on the exact kind of software you are making for Playdate.

By the way, what Lua versions does Kaitai Struct support? I've seen somewhere on their GitHub version 5.1 being mentioned. Playdate runs 5.4. There are a few small, but nevertheless incompatible changes between each of Lua 5.x releases. Shouldn't be a deal breaker, but keep that in mind.

Anyway, it begins to smell like an XY problem to me indeed. You seem to insist on having SQLite database files, why? Can you use another, lighter DB? (Some key-value embedded DB, analogues to Berkeley DB, for example.) Please, tell us: what are you making or what is your specific technical task at hand?

5

I guess I wasn't specific enough in my wording there. By 'zero examples of kaitai struct being used with lua' I mean an example of the code being used somewhere. I did the usual searches through all the major platforms and I didn't find one public repo that included a kaitai lua parser. I read that article and it is very high level, there is another similar article, but no actual implementation details.

I don't want to go into the details yet, but suffice to say it will be a consumer of a file format that I do not control, which is exactly the usual use case of kaitai, since if you did control the format then you'd presumably already know how to parse it.

I do appreciate your red team effort here, you've at least convinced me to not have this feature on the critical path.