Database Exports

Background

The files written to external media by rekordbox for use in player hardware contain a wealth of information that can be used in place of queries to the remotedb server on the players, which is important because they can be obtained from the players’ NFS servers, even if there are four players in use sharing the same media. Under those circumstances, remotedb queries are impossible. This document shares what has been learned so far about the files, and how to interpret them.

Database Exports

The starting point for finding track metadata from a player is the database export file, which can be found within rekordbox media at the following path:

/PIONEER/rekordbox/export.pdb

(If you are using the Crate Digger FileFetcher to request this file, use that path as the filePath argument, and use a mountPath value of /B/ if you want to read it from the SD slot, or /C/ to obtain it from the USB slot).

The file is a relational database format designed to be efficiently used by very low power devices (there were deployments on 16 bit devices with 32K of RAM). Today you are most likely to encounter it within the Pioneer Professional DJ ecosystem, because it is the format that their rekordbox software uses to write USB and SD media which can be mounted in DJ controllers and used to play and mix music.

The file consists of a series of fixed size pages. The first page contains a file header which defines the page size and the locations of database tables of different types, by the index of their first page. The rest of the pages consist of the data pages for all of the tables identified in the header.

Each table is made up of a series of rows which may be spread across any number of pages. The pages start with a header describing the page and linking to the next page. The rest of the page is used as a heap: rows are scattered around it, and located using an index structure that builds backwards from the end of the page. Each row of a given type has a fixed size structure which links to any variable-sized strings by their offsets within the page.

As changes are made to the table, some records may become unused, and there may be gaps within the heap that are too small to be used by other data. There is a bit map in the row index that identifies which rows are actually present. Rows that are not present must be ignored: they do not contain valid (or even necessarily well-formed) data.

The majority of the work in reverse-engineering this format was performed by Henry Betts and Fabian Lesniak, to whom I am hugely grateful.

File Header

Unless otherwise stated, all multi-byte numbers in the file are stored in little-endian byte order. Field names used in the byte field diagrams match the IDs assigned to them in the Kaitai Struct specification,[1] unless that is too long to fit, in which case a subscripted abbreviation is used, and the text will mention the actual struct field name.

Numbers in this document

Values within packets, packet lengths, and byte offsets are all shown in hexadecimal in code font. Other numbers are in normal body text font and are decimal.

The first page begins with the file header, shown below. The header starts with four zero bytes, followed by a four-byte integer, len_page at byte 04, that establishes the size of each page (including this first one), in bytes. This is followed by another four-byte integer, num_tables at byte 08, which reports the number of different tables that are present in the file. Each table will have a table pointer entry in the “Table pointers” section of the file header, described below, that identifies and locates the table.

0123456789abcdef00000000len_pagenum_tablesnextu0010unknownsequence0000000020Table Pointers30i+00
File header.

The four-byte integer nextu at byte 0c has an unknown purpose, but Mr. Lesniak named it next_unused_page and said “Not used as any empty_candidate, points past the end of the file.” The four-byte integer sequence, at byte 14, was described “Always incremented by at least one, sometimes by two or three.” and I assume this means it reflects a version number that rekordbox updates when synchronizing to the exported media.

Finally, there is another series of four zero bytes, and then the header ends with the list of table pointers which begins at byte 1c. There are as many of these as specified by num_tables, and each has the following structure:

0123456789abcdeftypeemptycfirst_pagelast_page
Table pointer.

Each Table Pointer is a series of four four-byte integers. The first, type, identifies the type of table being defined. The known table types are shown in below. The second value, at byte 04 of the table pointer, was called empty_candidate by Mr. Lesniak. It may link to a chain of empty pages if the database is ever garbage collected, but this is speculation on my part.

Table 1. Table types.
Type Name Meaning

00

tracks

Track metadata: title, artist, genre, artwork ID, playing time, etc.

01

genres

Musical genres, for reference by tracks and searching.

02

artists

Artists, for reference by tracks and searching.

03

albums

Albums, for reference by tracks and searching.

04

labels

Music labels, for reference by tracks and searching.

05

keys

Musical keys, for reference by tracks, searching, and key matching.

06

colors

Color labels, for reference by tracks and searching.

07

playlist_tree

Describes the hierarchical tree structure of playlists and folders grouping them.

08

playlist_entries

Links tracks to playlists, in the right order.

0d

artwork

File paths of album artwork images.

10

columns

Details not yet confirmed.

13

history

Records the tracks played during performances.

Other than the type, the two important values are first_page at byte 08 and last_page at byte 0c. These tell us how to find the table. They are page indices, where the page containing the file header has index 0, the page with index 1 begins at byte len_page, and so on. In other words, the first page of the table identified by the current table pointer can be found within the file starting at the byte len_page × first_page.

The table is a linked list of pages: each page contains the index of the next page after it. However, you need to keep track of the last_page value for the table, because it tells you not to try to follow the next page link once you reach the page with that index. (If you do keep going, you will start reading pages of some different table.) The structure of the table pages themselves are described in the next section.

As far as we know, the remainder of the first page after the table pointers is unused.

Table Pages

The table header is followed by the table pages themselves. These each have the size specified by len_page in the above diagram, and the following structure:

0123456789abcdef00000000page_indextypenext_page0010unknown1unknown2nrsu3u4pffreesuseds20u5numrlu6u7heap30i+00row groupsi+00i+10ofs19ofs18ofs17ofs16rowpf1ofs15ofs14i+20ofs13ofs12ofs11ofs10ofs9ofs8ofs7ofs6i+30ofs5ofs4ofs3ofs2ofs1ofs0rowpf0u8
Table page.

Data pages all seem to have the header structure described here, but not all of them actually store data. Some of them are “strange” and we have not yet figured out why. The discussion below describes how to recognize a strange page, and avoid trying to read it as a data page.

The first four bytes of a table page always seem to be zero. This is followed by a four-byte value page_index which identifies the index of this page within the list of table pages (the header has index 0, the first actual data page the index 1, and so on). This value seems to be redundant, because it can be calculated by dividing the offset of the start of the page by len_page, but perhaps it serves as a sanity check.

This is followed by another four-byte value, type, which identifies the type of the page, using the values shown in the preceding table. This again seems redundant because the table header which was followed to reach this page also identified the table type, but perhaps it is another sanity check, or an alternate way to tell, when following page links, that you have reached the end of the table you are interested in. Speaking of which, the next four-byte value, next_page, is that link: it identifies the index at which the next page of this table can be found, as long as we have not already reached the final page of the table, as described in File Header.

The exact meaning of unknown1 is unclear. Mr. Flesinak said “sequence number (0→1: 8→13, 1→2: 22, 2→3: 27)” but I don’t know how to interpret that. Even less is known about unknown2 . But num_rows_small at byte 18 within the page (abbrviated nrs in the byte field diagram above) holds the number of rows that are present in the page, unless num_rows_large (below) holds a value that is larger than it (but not equal to 1fff). This seems like a strange mechanism for dealing with the fact that some tables (like playlist entries) have a lot of very small rows, too many to count with a single byte. But then why not just always use num_rows_large?

The purpose of the next two bytes are is also unclear. Of u3 Mr. Flesniak said “a bitmask (first track: 32)”, and he described u4 as “often 0, sometimes larger, especially for pages with a high number of rows (e.g. 12 for 101 rows)”.

Byte 1b is called page_flags (abbrviated pf in the diagram). According to Mr. Flesniak, “strange” (non-data) pages will have the value 44 or 64, and other pages have had the values 24 or 34. Crate Digger considers a page to be a data page if page_flags&40 = `0`.

Bytes 1c-1d are called free_size (abbreviated frees in the diagram), and store the amount of unused space in the page heap (excluding the row index which is built backwards from the end of the page); used_size at bytes 1c-1d (abbreviated useds) stores the number of bytes that are in use in the page heap.

Bytes 20-21, u5 , are of unclear purpose. Mr. Flesniak labeled them “(0→1: 2).”

Bytes 22-23, num_rows_large (abbrviated numrl in the diagram) hold the number of entries in the row index at the end of the page when that value is too large to fit into num_rows_small (as mentioned above), and that situation seems to be indicated when this value is larger than num_rows_small, but not equal to 1fff.

u6 at bytes 24-25 seems to have the value 1004 for strange pages, and 0000 for data pages. And Mr. Flesniak describes u7 at bytes 26-27 as “always 0 except 1 for history pages, num entries for strange pages?”

After these header fields comes the page heap. Rows are allocated within this heap starting at byte 28. Since rows can be different sizes, there needs to be a way to locate them. This takes the form of a row index, which is built from the end of the page backwards, in groups of up to sixteen row pointers along with a bitmask saying which of those rows are still part of the table (they might have been deleted). The number of row index entries is determined, as described above, by the value of either num_rows_small or num_rows_large.

The bit mask for the first group of up to sixteen rows, labeled rowpf0 in the diagram (meaning “row presence flags group 0”), is found near the end of the page. The last two bytes of the page (u8) have an unknown purpose, and the rowpf0 bitmask takes up the two bytes that precede them. The low order bit of this value will be set if row 0 is really present, the next bit if row 1 is really present, and so on. The two bytes before these flags, labeled ofs0, store the offset of the first row in the page. This offset is the number of bytes past the end of the page header at which the row itself can be found. So if row 0 begins at the very beginning of the heap, at byte 28 in the page, ofs0 would have the value 0000.

As more rows are added to the page, space is allocated for them in the heap, and additional index entries are added at the end of the heap, growing backwards. Once there have been sixteen rows added, all of the bits in rowpf0 are accounted for, and when another row is added, before its offset entry ofs16 can be added, another row bit-mask entry rowpf1 needs to be allocated. And so the row index grows backwards towards the rows that are being added forwards, and once they are too close for a new row to fit, the page is full, and another page gets allocated to the table.

Table Rows

The structure of the rows themselves is determined by the type of the table, using the values shown in Table types.

Album Rows

Album rows hold an album name and ID along with an artist association, with the structure shown below. The unknown value at bytes 00-01 seems to usually have the values 80 00. It is followed by a two-byte value Mr. Flesniak called index_shift, although I don’t know what that means, and another four bytes of unknown purpose. But at bytes 08-0b we finally find a value we have a use for: artist_id holds the ID of an artist row associated with this track row. This is followed by id, the ID of this track row itself, at bytes 0c-0f. We assume that there are index tables somewhere that would let us locate the page and row index of a record given its table type and ID, but we have not yet found and figured them out.

0123456789abcdefu1ishiftunknown2artist_idid0010unknown3u4on
Album row.

This is followed by five more bytes with unknown meaning, and the final byte in the row, ofs_name is a pointer to the track name (labeled on in the byte field diagram). To find the location of the name, add ofs_name bytes to the address of the start of the track row itself. The name itself is encoded in a surprisingly baroque way, explained in DeviceSQL Strings.

Artist Rows

Artist rows hold an Artist name and ID, with the structure shown in Artist row with nearby name or Artist row with far name. The subtype value at bytes 00-01 determines which variant is used. If the artist name was allocated close enough to the row to be reached by a single byte offset, offset, subtype has the value 0060, and the row has the structure in Artist row with nearby name. If the name is too far away for that, subtype has the value 0064 and the row has the structure in Artist row with far name.

01234567890060ishiftidu1on
Artist row with nearby name.
0123456789ab0064ishiftidu1onofar
Artist row with far name.

In either case, subtype is followed by the unexplained two-byte value found in many row types that Mr. Flesniak called index_shift, and then by id, the ID of this artist row itself, at bytes 04-07, an unknown value at byte 08, and ofs_name_near at byte 09 (labeled on), the one-byte name offset used only in the first variant.

If subtype is 0064, the value of ofs_name_near is ignored, and instead the two-byte value ofs_name_far (labeled ofar) is used.

Whichever name offset is used, it is a pointer to the artist name. To find the location of the name, add the value of the offset to the address of the start of the artist row itself. This gives the address of a DeviceSQL string holding the name, with the structure explained in DeviceSQL Strings.

Artwork Rows

Artwork rows hold an id (which tracks refer to) and the path at which the corresponding album art image file can be found, with the structure shown below. Note that in this case, the DeviceSQL string path is embedded directly into the row itself, rather than being located elsewhere in the heap through an offset. The structure of the string itself is still as described in DeviceSQL Strings.

0123456789abcdefidpath0010i+00
Artwork row.

Color Rows

Color rows hold a numeric color id (which controls the actual color displayed on the player interface) at bytes 05-06 and a text label or name starting at byte 08 which is a DeviceSQL string shown in the information panel for tracks that are assigned the color. The rows have the structure shown below. There are several bytes in the row that are not yet known to have any meaning.

0123456789abcdefunknown1u2idu3name0010i+00
Color row.

Regardless of the names assigned to the colors by the user, the row id values map to the following colors in the user interface of rekordbox and on CDJs:

Table 2. Colors displayed.
ID Meaning

0

No color

1

Pink

2

Red

3

Orange

4

Yellow

5

Green

6

Aqua

7

Blue

8

Purple

Genre Rows

Genre rows hold a numeric genre id (which tracks can be assigned) at bytes 00-03 and a text name starting at byte 04 which is a DeviceSQL string. The rows have the structure shown below:

0123456789abcdefidname0010i+00
Genre or Label row.

Key Rows

Key rows represent musial keys. They hold a numeric id (which tracks can be assigned) at bytes 00-03 and a text name starting at byte 08 which is a DeviceSQL string. (There seems to be a second copy of the ID at bytes 04-07.) The rows have the structure shown below:

0123456789abcdefidid2name0010i+00
Key row.

Label Rows

Label rows represent record labels. They hold a numeric genre id (which tracks can be assigned) at bytes 00-03 and a text name starting at byte 04 which is a DeviceSQL string. The rows have the structure shown in Genre or Label row, above.

Playlist Tree Rows

Playlist tree rows are used to organize the hierarchical structure of the playlist menu. There is probably an index somewhere that makes it possible to find the right rows directly when loading a playlist, but we have not yet figured out how indices work in DeviceSQL databases, so Crate Digger simply reads all the rows and builds its own in-memory index of the tree.

Playlist tree rows can either represent a playlist “folder” which contains other folders and playlists, or a regular playlist which holds only tracks. The rows are identified by an id at bytes 0c-0f, and also contain a parent_id at bytes 00-03 which is how the hierarchical structure is represented: the contents of a folder are the other rows in this table whose parent_id folder is equal to the id of the folder.

Similarly, the tracks that make up a regular playlist are the Playlist Entry Rows whose playlist_id is equal to this row’s id.

Each playlist tree row also has a text name starting at byte 14 which is a DeviceSQL string displayed when navigating the hierarchy, a sort_order indicator at bytes 08-0b (this may be the same value used to select sort orders when requesting menus using the dbserver protocol, shown in the packet analysis, but this has not yet been confirmed), and a value that specifies whether the row defines a folder or a playlist. In the Kaitai Struct, this value is called raw_is_folder, is found at bytes 10-13, and has a non-zero value for folders. For convenience, the struct also defines a derived value, is_folder, which is a boolean.

The rows have the following structure:

0123456789abcdefparent_idunknownsort_orderid0010raw_is_foldername20i+00
Playlist Tree row.

Playlist Entry Rows

Playlist entry rows list the tracks that belong to a particular playlist, and also establish the order in which they should be played. They have a very simple structure, shown below, containing only three values. The entry_index at bytes 00-03 specifies the position within the playlist at which this entry belongs. The track_id at bytes 04-07 identifies the track to be played at this position in the playlist, by corresponding to the id of a row in the Track table, and the playlist_id at bytes 08-0b identifies the playlist to which it belongs, by corresponding to the id of a row in the Playlist Tree.

0123456789abentry_indextrack_idplaylist_id
Playlist Entry row.

Track Rows

Track rows describe audio tracks that can be played from the media export, and provide many details about the music including links to other tables like artists, albums, keys, and others. They have the structure shown below:

0123456789abcdefu1ishiftbitmasksample_ratecomposer_id0010file_sizeunknown2u3u4artwork_id20key_idorig_artist_idlabel_idremixer_id30bitratetrack_numbertempogenre_id40album_idartist_ididdiscnplayc50yearsdepthduru5cidru6u7ofs060ofs1ofs2ofs3ofs4ofs5ofs6ofs7ofs870ofs9ofs10ofs11ofs12ofs13ofs14ofs15ofs1680ofs17ofs18ofs19ofs20
Track row.

The first two bytes, labeled u1, have an unknown purpose; they usually are 24 followed by 00. They are followed by the unexplained two-byte value found in many row types that Mr. Flesniak called index_shift, and a four-byte value he called bitmask, although we do not know what the bits mean. The value at bytes 08-0b, sample_rate, is the first one we have a solid understanding of: it holds the playback sample rate of the audio file, in samples per second (this will be 0 if it is unknown or variable).

Bytes 0c-0f hold the value composer_id which identifies the composer of the track, if known, as a non-zero id value of an Artist row. The size of the audio file, in bytes, is found in file_size at bytes 10-13. This is followed by an unknown four-byte value, u2, which may be another ID, and two unknown two-byte values, u3 (about which Mr. Flesniak says “always 19048?”) and u4 (“always 30967?”).

If there is cover art for the track, there will be a non-zero value in artwork_id (bytes 1c-1f), identifying the id of an Artwork row.

If a dominant musical key was identified for the track there will be a non-zero value in key_id (bytes 20-23), which represents the id of a Key row. If the track is known to be a remake, the non-zero Artist row id of the original performer will be found at bytes 24-27 in original_artist_id. If there is a known record label for the track, the non-zero value in label_id (bytes 28-2b) will link to the id of a Label row id. Similarly, if there is a known remixer, there will be a non-zero value in remixer_id (bytes 2c-2f) linking to the id of an Artist row.

The field bitrate at bytes 30-33 stores the playback bit rate of the track, and track_number at bytes 34-37 holds the position of the track within its album. tempo at bytes 38-3b holds the playback tempo of the start of the track in beats per minute, multiplied by 100 (in order to support a precision of BPM). If there is a known genre for the track, there will be a non-zero value in genre_id at bytes 3c-3f, representing the id of a Genre row.

If the track is part of an album, there will be a non-zero value in album_id at bytes 40-43, and this will be the id of an Album row. The Artist row id of the primary performer associated with the track is found in artist_id at bytes 44-47. And the id of the track itself is found in id at bytes 48-4b. If the album is known to consist of multiple discs, the disc number on which this track is found will be in disc_number at bytes 4c-4d. And the number of times the track has been played is found in play_count (bytes 4e-4f).

The year in which the track was recorded, if known, is in year at bytes 50-51. The sample depth of the track audio file (bits per sample) is in sample_depth at bytes 52-53. The playback time of the track (in seconds, at normal speed) is in duration at bytes 54-55. The purpose of the next two bytes, labeled u5, is unknown; they seem to always hold the value 29.

Byte 58, color_id (labeled cid in the diagram), holds the color assigned to the track in rekordbox, as the id of a Color row, or zero if no color has been assigned. Byte 59, rating (labeled r in the diagram) holds the rating (0 to 5 stars) assigned the track. The next two bytes, labeled u6, have an unknown purpose, and seem to always have the value 1. The two bytes after them, labeled u7, are also unknown; Mr. Flesniak said “alternating 2 and 3”.

The rest of the track row is an array of 21 two-byte offsets that point to DeviceSQL strings. To find the start of the string, add the address of the start of the track row to the offset. The purpose of each string is described in the following table. For convenience, the strings can be accessed as Kaitai Struct instance values with the names shown in the table:

Table 3. Track offset strings.
Index Name Content

0

isrc

International Standard Recording Code, if known, in mangled format.[2]

1

texter

Unknown, named by @flesniak.

2

unknown_string_2

Unknown, “thought track number, wrong”.

3

unknown_string_3

Unknown, “strange things”.[3]

4

unknown_string_4

Unknown, “strange things” (as above).

5

message

Unknown, named by @flesniak.

6

kuvo_public

Empty or "ON".[4]

7

autoload_hotcues

Empty or "ON".[5]

8

unknown_string_5

Unknown.

9

unknown_string_6

Unknown, usually empty.

10

date_added

When the track was added to the rekordbox collection.

11

release_date

When the track was released.

12

mix_name

Name of the track remix, if any.

13

unknown_string_7

Unknown, usually empty.

14

analyze_path

File path of the track analysis.

15

analyze_date

When track analysis was performed.

16

comment

Track comment assigned by the DJ.

17

title

Track title.

18

unknown_string_8

Unknown, usually empty.

19

filename

Name of track audio file.

20

file_path

File path of track audio.

DeviceSQL Strings

Many row types store string values, sometimes by directly embedding them, but more often by storing an offset to a location elsewhere in the heap. In either case the string itself uses the strange structure described in this section. Strings can be stored in a variety of formats. The first byte of the structure, labeled length_and_kind in the parsed Kaitai Struct, identifies the encoding type and, when the value is odd, also the length (for short ASCII strings), as detailed in Short ASCII Strings.

Long ASCII Strings

If length_and_kind has the value 40, it is followed by a two-byte length field, and then followed by that many bytes of ASCII-encoded string data, as shown below:

0123456789abcdef40lengthASCII data0010i+00
Long ASCII DeviceSQL string.

Long UTF-16 Little-Endian Strings

If length_and_kind has the value 90, it is followed by a two-byte length field, an unknown byte (always 00?) and then by that many bytes of UTF-16 little-endian[2] encoded string data, as shown below:

0123456789abcdef90length00UTF-16 Little-Endian data0010i+00
Long UTF-16-LE DeviceSQL string.
ISRC Strings
When an International Standard Recording Code is present as the first string pointer in a track row, it is marked with kind 90 but does not actually hold a UTF-16-LE string. Instead, the first byte after the 00 value following the length is the value 03, and then there are length bytes of ordinary ASCII holding the ISRC. Crate Digger does not yet attempt to cope with this.

Short ASCII Strings

If length_and_kind has an odd value it is a mangled_length, labeled ml below. This means we are dealing with a short ASCII DeviceSQL string. To find the length of the string data (which immediately follows this byte), subtract 1 from mangled_length, divide it by 2, and subtract 1 again.

0123456789abcdefmlASCII data0010i+00
Short ASCII DeviceSQL string.

2. Thanks to @evilfred for discovering the ISRC strings and leading 00 byte in DeviceSQL UTF strings; we previously believed they were big-endian.
3. Often zero length, sometimes low binary values, ASCII 01 or 02 as content.
4. Apparently used rather than a simple bit flag to control whether the track information is visible on Kuvo.
5. Apparently used rather than a simple bit flag to control whether hot cues are automatically loaded for the track.