How to convert xonsh history from json to sqlite?

[anki-code]

From #4001

[anki-code]

My solution for xonsh 0.9.24 on Ubuntu:

1. You have json backend for history.
2. Add $XONSH_HISTORY_BACKEND = 'sqlite' to .xonshrc
3. Run xonsh to create ~/.local/share/xonsh/xonsh-history.sqlite and exit from all xonsh sessions.
4. Open bash and run cd ~/.local/share/xonsh, run vim json2sqlite.xsh, copy and paste:

import json, sqlite3

conn = sqlite3.connect('xonsh-history.sqlite')
cur = conn.cursor()
sql = 'INSERT INTO xonsh_history ({}) VALUES ({});'

i = 0
for f in g`*.json`:
    sessionid = f.replace('xonsh-','').replace('.json', '')

    try:
        j = json.load(open(f))
    except:
        print(f'Skip {f}: json error')
        continue

    for c in j['data']['cmds']:
        row = {
            'inp': c['inp'],
            'rtn': c['rtn'],
            'tsb': c['ts'][0],
            'tse': c['ts'][1],
            'sessionid': sessionid
        }
        columns = ', '.join(row)
        values = ', '.join(['?']*len(row))
        cur.execute(sql.format(columns, values), tuple(row.values()))
        i += 1
conn.commit()

print(f'Inserted {i} commands.')

5. Run xonsh json2sqlite.xsh
6. Run xonsh and check history info and check that you have a history from sqlite database
7. Optionally remove json history: rm xonsh-*.json json2sqlite.xsh

PS: also the code around history could be found in #1614

(Optionally) Drop duplicates from sqlite history

Drop duplicates from sqlite history (xonsh 0.9.24):

-- Get the structure from `sqlite_web @$(history file)`
CREATE TABLE xonsh_history_new (
  inp TEXT,
  rtn INTEGER,
  tsb REAL,
  tse REAL,
  sessionid TEXT,
  out TEXT,
  info TEXT,
  frequency INTEGER default 1
);

INSERT INTO xonsh_history_new 
    SELECT inp, min(rtn), min(tsb), min(tse), min(sessionid), min(out), min(info), sum(frequency)
    FROM xonsh_history
    GROUP BY 1
    
-- -- Execute carefully:
-- DELETE * FROM xonsh_history;
-- INSERT INTO xonsh_history SELECT * FROM xonsh_history_new;
-- DROP TABLE xonsh_history_new;

[eugenesvk]

I think there is a missing field in the parser
'cwd': c['cwd'],
and the new table creation query

  frequency INTEGER default 1,
  cwd TEXT

And DELETE * FROM xonsh_history; should be DELETE FROM xonsh_history; without the * as that gives an error