constants.py

SQL_BEACONS = {
    "SELECT":"SELECT beacon FROM beacons WHERE gid = %s;",
    "UNIQUE":"SELECT COUNT(*) FROM beacons WHERE %s = %s;",
    "EDIT":"SELECT {fields} FROM beacons WHERE gid = %s;",
    "DELETE":"DELETE FROM beacons WHERE gid = %s;",
    "INSERT":"INSERT INTO beacons({fields}) VALUES ({values}) RETURNING gid;",
    "UPDATE":"UPDATE beacons SET {set} WHERE {where};",
    "BEARDIST":"SELECT CASE WHEN count(*) = 0 THEN FALSE ELSE TRUE END \
                FROM beardist WHERE beacon_to = (SELECT beacon FROM \
                beacons WHERE gid = %s);",
}

SQL_PARCELS = {
    "SELECT":"SELECT parcel_id FROM parcel_lookup WHERE parcel_id = %s;",
    "EDIT":"SELECT l.parcel_id, array_agg(s.gid ORDER BY s.sequence) \
            FROM ( SELECT b.gid, d.parcel_id, d.sequence FROM beacons b \
            INNER JOIN parcel_def d ON d.beacon = b.beacon) s JOIN \
            parcel_lookup l ON s.parcel_id = l.parcel_id WHERE \
            l.parcel_id = %s GROUP BY l.parcel_id;",
    "AUTOCOMPLETE":"SELECT parcel_id FROM parcel_lookup WHERE available;",
    "UNIQUE":"SELECT COUNT(*) FROM parcel_lookup WHERE parcel_id = %s;",
    "AVAILABLE":"SELECT available FROM parcel_lookup WHERE parcel_id = %s;",
    "INSERT":"INSERT INTO parcel_def(parcel_id, beacon, sequence) \
              VALUES (%s, %s, %s);",
    "INSERT_GENERAL": "INSERT INTO parcel_def(parcel_id, beacon, sequence) \
              VALUES %s;",
    "DELETE":"DELETE FROM parcel_def WHERE parcel_id = %s;",
}

SQL_BEARDIST = {
    "AUTO_SURVEYPLAN":"SELECT array_agg(plan_no) FROM survey;",
    "AUTO_REFERENCEBEACON":"SELECT array_agg(beacon) FROM beacons \
        WHERE beacon NOT IN (SELECT beacon_to FROM beardist WHERE \
        beacon_to NOT IN (SELECT ref_beacon FROM survey));",
    "EXIST_REFERENCEBEACON":"SELECT ref_beacon FROM survey where \
        plan_no = %s;",
    "EXIST_BEARDISTCHAINS":"SELECT bd.bearing, bd.distance, \
        bd.beacon_from, bd.beacon_to, b.location, b.name FROM beardist \
        bd INNER JOIN beacons b ON bd.beacon_to = b.beacon WHERE \
        bd.plan_no = %s;",
    "INDEX_REFERENCEBEACON":"SELECT i.column_index::integer FROM (SELECT \
        row_number() over(ORDER BY c.ordinal_position) -1 as \
        column_index, c.column_name FROM information_schema.columns c \
        WHERE c.table_name = 'beacons' AND c.column_name NOT IN ('geom', \
        'gid') ORDER BY c.ordinal_position) as i WHERE i.column_name = \
        'beacon';",
    "IS_SURVEYPLAN":"SELECT CASE WHEN COUNT(*) <> 0 THEN TRUE ELSE FALSE \
        END FROM survey WHERE plan_no = %s;",
    "INSERT_SURVEYPLAN":"INSERT INTO survey(plan_no, ref_beacon) \
        VALUES(%s, %s);",
    "UPDATE_LINK":"SELECT beardistupdate(%s, %s, %s, %s, %s, %s, %s, %s);",
    "DELETE_LINK":"DELETE FROM beacons WHERE beacon = %s;",
    "INSERT_LINK":"SELECT beardistinsert(%s, %s, %s, %s, %s, %s, %s);"
}