Retrograde Data Massager

This repository contains a python script retrograde_massager.py that can be used to generate:

• C data structures and header files
• SQLite database scripts

It generates these files from JSON data files which describe the day by day retrograde status. This data was, in turn, generated by the retrograde-calculator.

Before proceeding to the next steps, copy the retrogrades.json from here into the top-level directory of this project.

$ wget -q 'https://raw.githubusercontent.com/alanwsmith/retrograde-data/main/daily-full-file/retrogrades.json'

The data output generated by retrograde_massager.py includes records only for the timestamps where a retrograde transition occurs for at least one celestial body (as opposed to generating a record for every timestamp in the input JSON file). This means that when looking up the retrograde status for a particular timestamp, the correct record to use is the record with the largest timestamp <= the desired timestamp. This record will contain the most recent retrograde status update.

Example 1 - Generate C data file and header file from JSON file

$ ./retrograde_massager.py c retrogrades.json retrogrades_data
Generated retrogrades_data.h.
Generated retrogrades_data.c.

Generated retrogrades_data.h:

#include <stdint.h>
#include <stddef.h>

#define NUM_CELESTIAL_BODIES 9

#define BITPOS_MERCURY 0
#define BITPOS_VENUS 1
#define BITPOS_MARS 2
#define BITPOS_JUPITER 3
#define BITPOS_SATURN 4
#define BITPOS_URANUS 5
#define BITPOS_NEPTUNE 6
#define BITPOS_PLUTO 7
#define BITPOS_MOON 8

#define MERCURY_BIT (1 << BITPOS_MERCURY)
#define VENUS_BIT (1 << BITPOS_VENUS)
#define MARS_BIT (1 << BITPOS_MARS)
#define JUPITER_BIT (1 << BITPOS_JUPITER)
#define SATURN_BIT (1 << BITPOS_SATURN)
#define URANUS_BIT (1 << BITPOS_URANUS)
#define NEPTUNE_BIT (1 << BITPOS_NEPTUNE)
#define PLUTO_BIT (1 << BITPOS_PLUTO)
#define MOON_BIT (1 << BITPOS_MOON)

struct retrograde_entry {
    uint64_t utc_timestamp;
    uint16_t retrograde_bmap;
};

extern const char *celestial_body_name[NUM_CELESTIAL_BODIES];
extern struct retrograde_entry retrograde_data[];
extern const size_t NUM_RETROGRADE_DATA_ENTRIES;

Generated retrogrades_data.c:

#include "retrogrades_data.h"

const char *celestial_body_name[] = { "mercury", "venus", "mars", "jupiter", "saturn", "uranus", "neptune", "pluto", "moon" };

struct retrograde_entry retrograde_data[] = {
    { 946684800, 0x0010 },
    { 947721600, 0x0000 },
// ...
    { 4133116800, 0x00e0 },
    { 4133894400, 0x00c0 },
};

const size_t NUM_RETROGRADE_DATA_ENTRIES = sizeof(retrograde_data) / sizeof(retrograde_data[0]);

Example 2 - C sample program

$ cd c_example
$ make
retrograde_massager.py c retrogrades.json retrogrades_data
gcc -c retrogrades_data.c -Wall
gcc -c c_example.c -Wall
gcc -o c_example c_example.o retrogrades_data.o

# The sample program takes a list of UTC timestamps and displays the bodies in
# retrograde at that time
$ ./c_example 1209958400 3556828800 # or ./c_example $(date -d "Fri Feb 10 00:00:00 PST 2006" +%s ) $(date -d "Wed Sep 16 17:00:00 PDT 2082" +%s)
ts: 1209958400
        pluto
ts: 3556828800
        mercury
        venus
        mars
        jupiter
        saturn
        uranus
        pluto

Example 3 - Generate SQLite database

$ ./retrograde_massager.py sqlite retrogrades.json test
Generated test.sqlite.

# Run the generated SQL commands to create the database file:
$ sqlite3 test.db < test.sqlite
$ sqlite3 test.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE retrograde_table (
    timestamp_seconds_utc INTEGER PRIMARY KEY
,    mercury INTEGER
,    venus INTEGER
,    mars INTEGER
,    jupiter INTEGER
,    saturn INTEGER
,    uranus INTEGER
,    neptune INTEGER
,    pluto INTEGER
,    moon INTEGER
);
INSERT INTO retrograde_table VALUES(946684800,0,0,0,0,1,0,0,0,0);
INSERT INTO retrograde_table VALUES(947721600,0,0,0,0,0,0,0,0,0);
...
INSERT INTO retrograde_table VALUES(4133116800,0,0,0,0,0,1,1,1,0);
INSERT INTO retrograde_table VALUES(4133894400,0,0,0,0,0,0,1,1,0);
COMMIT;
sqlite>

Example query for timestamp 3556828810, for all celestial bodies in the database:

sqlite> SELECT * FROM retrograde_table WHERE timestamp_seconds_utc <= 3556828810 ORDER BY timestamp_seconds_utc DESC LIMIT 1;
timestamp_seconds_utc  mercury  venus  mars  jupiter  saturn  uranus  neptune  pluto  moon
---------------------  -------  -----  ----  -------  ------  ------  -------  -----  ----
3556828800             1        1      1     1        1       1       0        1      0