rewrite_fix_big_endian_geometries

poetry.lock

[[package]]
name = "attrs"
version = "21.4.0"
description = "Classes Without Boilerplate"
category = "main"
optional = false
python-versions = ">=2.7, !=3.0.*, !=3.1.*, !=3.2.*, !=3.3.*, !=3.4.*"

[package.extras]
dev = ["coverage[toml] (>=5.0.2)", "hypothesis", "pympler", "pytest (>=4.3.0)", "six", "mypy", "pytest-mypy-plugins", "zope.interface", "furo", "sphinx", "sphinx-notfound-page", "pre-commit", "cloudpickle"]
docs = ["furo", "sphinx", "zope.interface", "sphinx-notfound-page"]
tests = ["coverage[toml] (>=5.0.2)", "hypothesis", "pympler", "pytest (>=4.3.0)", "six", "mypy", "pytest-mypy-plugins", "zope.interface", "cloudpickle"]
tests_no_zope = ["coverage[toml] (>=5.0.2)", "hypothesis", "pympler", "pytest (>=4.3.0)", "six", "mypy", "pytest-mypy-plugins", "cloudpickle"]

[[package]]
name = "outcome"
version = "1.1.0"
description = "Capture the outcome of Python function calls."
category = "main"
optional = false
python-versions = ">=3.6"

[package.dependencies]
attrs = ">=19.2.0"

[[package]]
name = "pymysql"
version = "0.9.3"
description = "Pure Python MySQL Driver"
category = "main"
optional = false
python-versions = "*"

[package.extras]
rsa = ["cryptography"]

[[package]]
name = "represent"
version = "1.6.0.post0"
description = "Create __repr__ automatically or declaratively."
category = "main"
optional = false
python-versions = ">=2.7, !=3.0.*, !=3.1.*, !=3.2.*, !=3.3.*"

[package.dependencies]
six = ">=1.8.0"

[package.extras]
test = ["ipython", "pytest (>=3.0.5)", "mock"]

[[package]]
name = "shapely"
version = "1.8.0"
description = "Geometric objects, predicates, and operations"
category = "main"
optional = false
python-versions = ">=3.6"

[package.extras]
all = ["pytest", "pytest-cov", "numpy"]
test = ["pytest", "pytest-cov"]
vectorized = ["numpy"]

[[package]]
name = "six"
version = "1.16.0"
description = "Python 2 and 3 compatibility utilities"
category = "main"
optional = false
python-versions = ">=2.7, !=3.0.*, !=3.1.*, !=3.2.*"

[[package]]
name = "sqlalchemy"
version = "1.3.16"
description = "Database Abstraction Library"
category = "main"
optional = false
python-versions = ">=2.7, !=3.0.*, !=3.1.*, !=3.2.*, !=3.3.*"

[package.extras]
mssql = ["pyodbc"]
mssql_pymssql = ["pymssql"]
mssql_pyodbc = ["pyodbc"]
mysql = ["mysqlclient"]
oracle = ["cx-oracle"]
postgresql = ["psycopg2"]
postgresql_pg8000 = ["pg8000"]
postgresql_psycopg2binary = ["psycopg2-binary"]
postgresql_psycopg2cffi = ["psycopg2cffi"]
pymysql = ["pymysql"]

[[package]]
name = "sqlalchemy-aio"
version = "0.15.0"
description = "Async support for SQLAlchemy."
category = "main"
optional = false
python-versions = ">=3.5"

[package.dependencies]
outcome = "*"
represent = ">=1.4"
sqlalchemy = "*"

[package.extras]
test = ["pytest (>=3)", "pytest-asyncio (>=0.8)", "pytest-trio (>=0.5.1)"]
test-noextras = ["pytest (>=3)", "pytest-asyncio (>=0.8)"]
trio = ["trio (>=0.12)"]

[metadata]
lock-version = "1.1"
python-versions = "3.8.2"
content-hash = "17902a7cc587d640b55182cafb3d79a52963ea17fce2b0d65962efd837712b35"

[metadata.files]
attrs = [
    {file = "attrs-21.4.0-py2.py3-none-any.whl", hash = "sha256:2d27e3784d7a565d36ab851fe94887c5eccd6a463168875832a1be79c82828b4"},
    {file = "attrs-21.4.0.tar.gz", hash = "sha256:626ba8234211db98e869df76230a137c4c40a12d72445c45d5f5b716f076e2fd"},
]
outcome = [
    {file = "outcome-1.1.0-py2.py3-none-any.whl", hash = "sha256:c7dd9375cfd3c12db9801d080a3b63d4b0a261aa996c4c13152380587288d958"},
    {file = "outcome-1.1.0.tar.gz", hash = "sha256:e862f01d4e626e63e8f92c38d1f8d5546d3f9cce989263c521b2e7990d186967"},
]
pymysql = [
    {file = "PyMySQL-0.9.3-py2.py3-none-any.whl", hash = "sha256:3943fbbbc1e902f41daf7f9165519f140c4451c179380677e6a848587042561a"},
    {file = "PyMySQL-0.9.3.tar.gz", hash = "sha256:d8c059dcd81dedb85a9f034d5e22dcb4442c0b201908bede99e306d65ea7c8e7"},
]
represent = [
    {file = "Represent-1.6.0.post0-py2.py3-none-any.whl", hash = "sha256:99142650756ef1998ce0661568f54a47dac8c638fb27e3816c02536575dbba8c"},
    {file = "Represent-1.6.0.post0.tar.gz", hash = "sha256:026c0de2ee8385d1255b9c2426cd4f03fe9177ac94c09979bc601946c8493aa0"},
]
shapely = [
    {file = "Shapely-1.8.0-cp310-cp310-manylinux_2_17_aarch64.manylinux2014_aarch64.whl", hash = "sha256:1c5632cedea6d815b61eb4c264da1c3f24a8ce2ceba2f74e30fba340ca230563"},
    {file = "Shapely-1.8.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl", hash = "sha256:d4ce1f18a0c9bb6b483c73bd7a0eb3a5e90676bcc29b9c27120236e662195c9d"},
    {file = "Shapely-1.8.0-cp36-cp36m-macosx_10_9_x86_64.whl", hash = "sha256:4e8cdffeec6d0c47ed1eb215ec4e80c024ac05be6ded982061c1e1188034f22f"},
    {file = "Shapely-1.8.0-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl", hash = "sha256:83d10f8b47a7568fc90063f72da62cda201dc92ecadf80cc00c015babc48e11f"},
    {file = "Shapely-1.8.0-cp36-cp36m-win32.whl", hash = "sha256:78b3a46dadd47c27e658d5e8d9006b4b1eb9b7ab947b450059225dcee799a18f"},
    {file = "Shapely-1.8.0-cp36-cp36m-win_amd64.whl", hash = "sha256:0e640d6da59172d679270f0dfd88128b6ae7c57df864a030dd858ff924c307fc"},
    {file = "Shapely-1.8.0-cp37-cp37m-macosx_10_9_x86_64.whl", hash = "sha256:68bdf463f7a609fbed42bbded18fa74c82a5741251984a5597d070060f4286f4"},
    {file = "Shapely-1.8.0-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.whl", hash = "sha256:2c3cc87e66cbffd00ce0457c03969b64935752824bf43a1cd61f21cf606997d6"},
    {file = "Shapely-1.8.0-cp37-cp37m-win32.whl", hash = "sha256:bd84d993a0e8e07f5ebb4c67794d5392fdd23ce59a7ccc121900f2080f57989a"},
    {file = "Shapely-1.8.0-cp37-cp37m-win_amd64.whl", hash = "sha256:796b15a483ac37c2dc757654186d0e064a42fb6f43cb9d1ff65d81cd0c92a84e"},
    {file = "Shapely-1.8.0-cp38-cp38-macosx_10_9_x86_64.whl", hash = "sha256:622f62d2b2da81dd40841a56db0f78bcf9f9af7a83c7d5f5dc9bcb234aa650ba"},
    {file = "Shapely-1.8.0-cp38-cp38-manylinux_2_17_aarch64.manylinux2014_aarch64.whl", hash = "sha256:26b43b69dfeb8a8cb27aacf5597134baf12337845c2bacb01809540c20d3d904"},
    {file = "Shapely-1.8.0-cp38-cp38-manylinux_2_5_x86_64.manylinux1_x86_64.whl", hash = "sha256:cfb9d72d255af1a484e3859f4c9bb737950faf1d16c21d2b949ffc4ba5f46147"},
    {file = "Shapely-1.8.0-cp38-cp38-win32.whl", hash = "sha256:f304243b1f4d7bca9b3c9fdeec6565171e1b611fb4a3d6c93efc870c8a75958c"},
    {file = "Shapely-1.8.0-cp38-cp38-win_amd64.whl", hash = "sha256:8917a91430126165cfa4bc2b4cf168121e37ff0c8657134e7398c597ca1fe934"},
    {file = "Shapely-1.8.0-cp39-cp39-macosx_10_9_x86_64.whl", hash = "sha256:19b54cd840883fd71cce98fd94916d1731eed8a32c115eb082b3ed24e631be02"},
    {file = "Shapely-1.8.0-cp39-cp39-manylinux_2_17_aarch64.manylinux2014_aarch64.whl", hash = "sha256:13cbb959863cec32d48e2cffdc4bb81828bc3b0fa4256c9b2b32edac5021a0e4"},
    {file = "Shapely-1.8.0-cp39-cp39-manylinux_2_5_x86_64.manylinux1_x86_64.whl", hash = "sha256:7e1aebf4f1b2fbef40152fd531216387fcf6fe4ff2d777268381979b63c7c779"},
    {file = "Shapely-1.8.0-cp39-cp39-win32.whl", hash = "sha256:83145eda2e582c2046d1ecc6a0d7dbfe97f492434311124f65ea60f4e87a6b65"},
    {file = "Shapely-1.8.0-cp39-cp39-win_amd64.whl", hash = "sha256:9b54ebd8fa4b78320f6d87032fe91363c7c1bf0f8d4a30eb93bca6413f787fd5"},
    {file = "Shapely-1.8.0.tar.gz", hash = "sha256:f5307ee14ba4199f8bbcf6532ca33064661c1433960c432c84f0daa73b47ef9c"},
]
six = [
    {file = "six-1.16.0-py2.py3-none-any.whl", hash = "sha256:8abb2f1d86890a2dfb989f9a77cfcfd3e47c2a354b01111771326f8aa26e0254"},
    {file = "six-1.16.0.tar.gz", hash = "sha256:1e61c37477a1626458e36f7b1d82aa5c9b094fa4802892072e49de9c60c4c926"},
]
sqlalchemy = [
    {file = "SQLAlchemy-1.3.16-cp27-cp27m-macosx_10_13_x86_64.whl", hash = "sha256:8d8c21e9d4efef01351bf28513648ceb988031be4159745a7ad1b3e28c8ff68a"},
    {file = "SQLAlchemy-1.3.16-cp27-cp27m-win32.whl", hash = "sha256:083e383a1dca8384d0ea6378bd182d83c600ed4ff4ec8247d3b2442cf70db1ad"},
    {file = "SQLAlchemy-1.3.16-cp27-cp27m-win_amd64.whl", hash = "sha256:128f6179325f7597a46403dde0bf148478f868df44841348dfc8d158e00db1f9"},
    {file = "SQLAlchemy-1.3.16-cp36-cp36m-macosx_10_13_x86_64.whl", hash = "sha256:6056b671aeda3fc451382e52ab8a753c0d5f66ef2a5ccc8fa5ba7abd20988b4d"},
    {file = "SQLAlchemy-1.3.16-cp36-cp36m-manylinux1_x86_64.whl", hash = "sha256:7025c639ce7e170db845e94006cf5f404e243e6fc00d6c86fa19e8ad8d411880"},
    {file = "SQLAlchemy-1.3.16-cp36-cp36m-manylinux2010_x86_64.whl", hash = "sha256:e18752cecaef61031252ca72031d4d6247b3212ebb84748fc5d1a0d2029c23ea"},
    {file = "SQLAlchemy-1.3.16-cp36-cp36m-win32.whl", hash = "sha256:0a690a6486658d03cc6a73536d46e796b6570ac1f8a7ec133f9e28c448b69828"},
    {file = "SQLAlchemy-1.3.16-cp36-cp36m-win_amd64.whl", hash = "sha256:d00b393f05dbd4ecd65c989b7f5a81110eae4baea7a6a4cdd94c20a908d1456e"},
    {file = "SQLAlchemy-1.3.16-cp37-cp37m-macosx_10_13_x86_64.whl", hash = "sha256:114b6ace30001f056e944cebd46daef38fdb41ebb98f5e5940241a03ed6cad43"},
    {file = "SQLAlchemy-1.3.16-cp37-cp37m-manylinux1_x86_64.whl", hash = "sha256:13d48cd8b925b6893a4e59b2dfb3e59a5204fd8c98289aad353af78bd214db49"},
    {file = "SQLAlchemy-1.3.16-cp37-cp37m-manylinux2010_x86_64.whl", hash = "sha256:211a1ce7e825f7142121144bac76f53ac28b12172716a710f4bf3eab477e730b"},
    {file = "SQLAlchemy-1.3.16-cp37-cp37m-win32.whl", hash = "sha256:68d78cf4a9dfade2e6cf57c4be19f7b82ed66e67dacf93b32bb390c9bed12749"},
    {file = "SQLAlchemy-1.3.16-cp37-cp37m-win_amd64.whl", hash = "sha256:2dc57ee80b76813759cccd1a7affedf9c4dbe5b065a91fb6092c9d8151d66078"},
    {file = "SQLAlchemy-1.3.16-cp38-cp38-macosx_10_13_x86_64.whl", hash = "sha256:43078c7ec0457387c79b8d52fff90a7ad352ca4c7aa841c366238c3e2cf52fdf"},
    {file = "SQLAlchemy-1.3.16-cp38-cp38-manylinux1_x86_64.whl", hash = "sha256:bbb545da054e6297242a1bb1ba88e7a8ffb679f518258d66798ec712b82e4e07"},
    {file = "SQLAlchemy-1.3.16-cp38-cp38-manylinux2010_x86_64.whl", hash = "sha256:5b1bf3c2c2dca738235ce08079783ef04f1a7fc5b21cf24adaae77f2da4e73c3"},
    {file = "SQLAlchemy-1.3.16-cp38-cp38-win32.whl", hash = "sha256:3e625e283eecc15aee5b1ef77203bfb542563fa4a9aa622c7643c7b55438ff49"},
    {file = "SQLAlchemy-1.3.16-cp38-cp38-win_amd64.whl", hash = "sha256:7d98e0785c4cd7ae30b4a451416db71f5724a1839025544b4edbd92e00b91f0f"},
    {file = "SQLAlchemy-1.3.16.tar.gz", hash = "sha256:7224e126c00b8178dfd227bc337ba5e754b197a3867d33b9f30dc0208f773d70"},
]
sqlalchemy-aio = [
    {file = "sqlalchemy_aio-0.15.0-py2.py3-none-any.whl", hash = "sha256:b979de6749bc4685ca7b2427bdff1d963383fba3779fac06f0c20890739359c2"},
    {file = "sqlalchemy_aio-0.15.0.tar.gz", hash = "sha256:3f3d92c02049f499f6d6beb4fd4eb7b95c154d2c7e47876695a3b7cba2a55537"},
]

pyproject.toml

[tool.poetry]
name = "rewrite_fix_big_endian_geometries"
version = "0.1.0"
description = ""
authors = ["Your Name <you@example.com>"]

[tool.poetry.dependencies]
python = "3.8.2"
sqlalchemy = "^1.3.16"
sqlalchemy_aio = "^0.15.0"
PyMySQL = "^0.9.3"
shapely = "^1.7.0"
SQLAlchemy = "1.3.16"
Shapely = "^1.8.0"

[tool.poetry.dev-dependencies]

[build-system]
requires = ["poetry>=0.12"]
build-backend = "poetry.masonry.api"

rewrite_fix_big_endian_geometries.py

#!/bin/env python3

# Usage: poetry run python rewrite_fix_big_endian_geometries.py --db-connect-spec='mysql+pymysql://farm:farm@localhost:3306/farm'

import datetime
from math import sin, cos, tan, pi
import math
import os.path
import sys
import time

import argparse
import asyncio
import logging
import re
import shapely.wkb
import shapely.wkt
from sqlalchemy import func, and_, distinct, Float
import sqlalchemy as sa
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.sql.expression import select, join, Label, cast, update, bindparam
from sqlalchemy_aio import ASYNCIO_STRATEGY


class CallableAccessor(object):
    def __init__(self, c):
        self._c = c

    def __getattr__(self, name):
        return self._c(name)

def DictAccessor(d):
    return CallableAccessor(lambda name: d.get(name))


async def async_main(db):
    t = db.tables

    query = (
        select([
            t.FieldDataFieldFarmGeofield.entity_type,
            t.FieldDataFieldFarmGeofield.entity_id,
            t.FieldDataFieldFarmGeofield.deleted,
            t.FieldDataFieldFarmGeofield.delta,
            t.FieldDataFieldFarmGeofield.language,
            t.FieldDataFieldFarmGeofield.field_farm_geofield_geom,
        ])
        .select_from(t.FieldDataFieldFarmGeofield)
    )

    async with db.engine.connect() as conn:
        async with conn.begin():

            res = await (await conn.execute(query)).fetchall()

            for geofield in res:
                geometry_wkb = geofield['field_farm_geofield_geom']
                if geometry_wkb is None:
                    continue

                if geometry_wkb[:1] == b'\x01':
                    continue

                geometry = shapely.wkb.loads(geometry_wkb)

                print("Rewriting geometry for:", geofield['entity_type'], geofield['entity_id'], geometry)

                new_geometry_wkb = shapely.wkb.dumps(geometry)

                if new_geometry_wkb[:1] != b'\x01':
                    print("ERROR: Updated geometry not little-endian:", new_geometry_wkb[0], new_geometry_wkb)
                    sys.exit(1)

                data = dict(
                    b_entity_type=geofield['entity_type'],
                    b_entity_id=geofield['entity_id'],
                    b_deleted=geofield['deleted'],
                    b_delta=geofield['delta'],
                    b_language=geofield['language'],
                    field_farm_geofield_geom=new_geometry_wkb,
                )

                await conn.execute(update(t.FieldDataFieldFarmGeofield).where(and_(
                              t.FieldDataFieldFarmGeofield.entity_type == bindparam('b_entity_type'),
                              t.FieldDataFieldFarmGeofield.entity_id == bindparam('b_entity_id'),
                              t.FieldDataFieldFarmGeofield.deleted == bindparam('b_deleted'),
                              t.FieldDataFieldFarmGeofield.delta == bindparam('b_delta'),
                              t.FieldDataFieldFarmGeofield.language == bindparam('b_language'))), [data])

                await conn.execute(update(t.FieldRevisionFieldFarmGeofield).where(and_(
                              t.FieldRevisionFieldFarmGeofield.entity_type == bindparam('b_entity_type'),
                              t.FieldRevisionFieldFarmGeofield.entity_id == bindparam('b_entity_id'),
                              t.FieldRevisionFieldFarmGeofield.deleted == bindparam('b_deleted'),
                              t.FieldRevisionFieldFarmGeofield.delta == bindparam('b_delta'),
                              t.FieldRevisionFieldFarmGeofield.language == bindparam('b_language'))), [data])

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--db-connect-spec", help="The specification for connecting to the db", type=str,
                        default='mysql+pymysql://farm:farm@db:3306/farm')
    args = parser.parse_args()

    logging.basicConfig(level=logging.DEBUG)

    engine = sa.create_engine(
        args.db_connect_spec,
        # echo=True,
        strategy=ASYNCIO_STRATEGY,
        pool_pre_ping=True,
    )
    engine.hide_parameters = False
    Base = automap_base()

    # Copied from https://docs.sqlalchemy.org/en/13/orm/extensions/automap.html#overriding-naming-schemes
    def camelize_classname(base, tablename, table):
        "Produce a 'camelized' class name, e.g. "
        "'words_and_underscores' -> 'WordsAndUnderscores'"

        return str(tablename[0].upper() + \
                re.sub(r'_([a-z])', lambda m: m.group(1).upper(), tablename[1:]))

    Base.prepare(engine.sync_engine, reflect=True, classname_for_table=camelize_classname)

    db = DictAccessor({
        'engine': engine,
        'Base': Base,
        'tables': Base.classes,
    })

    loop = asyncio.get_event_loop()
    loop.run_until_complete(async_main(db))

if __name__ == '__main__':
    main()