portalimport

portalimport.py

#!/usr/bin/env python2
#-*- coding: utf8 -*-
import sqlite3

IMPORT = True
NEW_SCHEMA = "CREATE TABLE user(serial INTEGER PRIMARY KEY, name TEXT, surname TEXT, nickname TEXT, created timestamp DEFAULT CURRENT_TIMESTAMP, firstValid timestamp DEFAULT NULL, lastValid timestampt DEFAULT NULL, pubkey varchar(8192));"
NEW_DB = 'shackspacekey.sqlite'

OLD_SCHEMA = "CREATE TABLE user (Givenname varchar(64), Surname varchar(64),serialnumber varchar(64),Created timestamp DEFAULT CURRENT_TIMESTAMP,FirstValidDate timestamp,LastValidDate timestamp,PrivateKey varchar(3300),PublicKey varchar(730),Fingerprint varchar(64));"
OLD_DB = ''

def get_db(name):
    conn = sqlite3.connect(name)
    cur = conn.cursor()
    return cur, conn


def data_import():
    oldcur, oldconn = get_db(OLD_DB)
    newcur, newconn = get_db(NEW_DB)
    try:
        newcur.execute(NEW_SCHEMA)
    except sqlite3.OperationalError:
        if raw_input("table exists, continue? [y/n] ").lower() != 'y':
            print('aborting')
            return
    oldcur.execute("SELECT serialnumber, Givenname, Surname, Created, FirstValidDate, PublicKey FROM user")

    for user in oldcur.fetchall():
        newcur.execute("INSERT INTO user (serial, name, surname, created, firstValid, lastValid, pubkey) VALUES (?, ?, ?, ?, ?, ?, ?)", (user[0], user[1], user[2], user[3], '2023-04-02', user[4], user[5]))

    newconn.commit()
    newconn.close()
    oldconn.close()

def nickchanges():
    cur, conn = get_db(NEW_DB)
    cur.execute("SELECT serial, name, surname FROM user WHERE nick IS NULL")

    for user in cur.fetchall():
        print("\nEin shackie namens " + user[1] + ' ' + user[2])
        print("\nnick (leer für Vorname): ")

        nick = raw_input()

        if nick == "":
            nick = user[1]

        cur.execute("UPDATE user SET nick="+nick+" WHERE serial="+str(user[0]))

    print("All users done!")


if __name__ == '__main__':
    if IMPORT: 
       data_import()
    else:
        nickchanges()