Kea migrate memfile leases to PostgreSQL

memfile2pg.sql

BEGIN;

CREATE OR REPLACE FUNCTION inet2bigint(inet) RETURNS bigint AS
$$
        SELECT ($1::inet - '0.0.0.0'::inet)::bigint;
$$ LANGUAGE 'sql';


DROP CAST IF EXISTS (inet AS bigint);

CREATE CAST (inet AS bigint)
        WITH FUNCTION inet2bigint(inet)
        AS IMPLICIT;

DROP TABLE IF EXISTS lease4_staging;

CREATE TABLE lease4_staging (
    address text ,
    hwaddr text,
    client_id text,
    valid_lifetime text,
    expire text,
    subnet_id text,
    fqdn_fwd text,
    fqdn_rev text,
    hostname text,
    state text,
    user_context text
);

\copy lease4_staging from '/tmp/kea-leases4.csv'  delimiter ',' CSV header;

INSERT INTO lease4
SELECT
    address::inet::bigint,
    CONCAT('\x', REPLACE(hwaddr, ':',''))::bytea,
    CONCAT('\x', REPLACE(client_id, ':',''))::bytea,
    valid_lifetime::bigint,
    to_timestamp(expire::int),
    subnet_id::bigint,
    fqdn_fwd::boolean,
    fqdn_rev::boolean,
    hostname::text,
    state::bigint,
    user_context
FROM
    lease4_staging;

-- wipe staging object
DROP CAST (inet AS bigint);
DROP TABLE lease4_staging;
DROP FUNCTION inet2bigint(inet);

COMMIT;