# -*- coding: iso-8859-15 -*-
from setuptools import setup
+requires = [
+ 'sqlalchemy',
+ 'geoalchemy'
+ ]
+
setup(name='wrpylib',
- version='0.3.1',
+ version='0.4.0',
description='Winterrodeln Python Library',
author='Philipp Spitzer',
author_email='philipp.spitzer@winterrodeln.org',
+ install_requires = requires,
packages=['wrpylib']
)
"""Contains functions that maintain/update the cache tables."""
from sqlalchemy import schema
from sqlalchemy.sql import select
+from sqlalchemy.sql.expression import func as sqlfunc
+import geoalchemy.functions as geofunc
+from osgeo import ogr
import formencode
from wrpylib import mwdb, wrmwdb, mwmarkup, wrmwmarkup
transaction.rollback()
raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
transaction.commit()
+
+
+def update_wrregioncache(connection):
+ """Updates the wrregioncache table from the wiki.
+ It relays on the table wrsledruncache to be up-to-date.
+ No exceptions should be raised under normal circumstances.
+
+ >>> from sqlalchemy.engine import create_engine
+ >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
+ >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
+ >>> update_wrregioncache(engine.connect())
+ """
+ metadata = schema.MetaData()
+ wrregion = wrmwdb.wrregion_table(metadata)
+ wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
+ wrregioncache = wrmwdb.wrregioncache_table(metadata)
+
+ transaction = connection.begin()
+
+ # Delete all existing entries in wrregioncache
+ # We rely on transactions MySQL InnoDB
+ connection.execute(wrregioncache.delete())
+
+ # Query all combinations of sledruns and regions
+ sel = select([wrregion.c.id.label('wrregion_id'), wrregion.c.border.label('border'), wrsledruncache.c.page_id, wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude], geofunc.gcontains(wrregion.c.border, sqlfunc.point(wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude)))
+ ins = wrregioncache.insert()
+
+ # Refill wrregioncache
+ point = ogr.Geometry(ogr.wkbPoint)
+ result = connection.execute(sel)
+ for row in result:
+ point.SetPoint(0, row.position_longitude, row.position_latitude)
+ if point.Within(ogr.CreateGeometryFromWkb(row.border.geom_wkb)):
+ connection.execute(ins.values(wrregion_id=row.wrregion_id, page_id=row.page_id))
+
+ # commit
+ transaction.commit()
+
tables in MediaWiki easy. The module uses sqlalchemy to access the database.
"""
from sqlalchemy import Table, Column, types, schema
+from geoalchemy import GeometryExtensionColumn, MultiPolygon
def wrreport_table(metadata):
)
+def wrregion_table(metadata):
+ """Returns the sqlalchemy Table representing the "wrregion" Winterrodeln table in MediaWiki.
+ Current table definition.
+ * version 1.5 (introduction)
+ :param metadata: metadata = sqlalchemy.MetaData()
+ """
+ return Table("wrregion", metadata,
+ Column("id", types.Integer, primary_key=True),
+ Column("name", types.Unicode(50)),
+ Column("page_id", types.Integer),
+ GeometryExtensionColumn("border", MultiPolygon(2, 4326))
+ )
+
+
+def wrregioncache_table(metadata):
+ """Returns the sqlalchemy Table representing the "wrregioncache" Winterrodeln table in MediaWiki.
+ Current table definition.
+ * version 1.5 (introduction)
+ :param metadata: metadata = sqlalchemy.MetaData()
+ """
+ return Table("wrregioncache", metadata,
+ Column("id", types.Integer, primary_key=True),
+ Column("wrregion_id", types.Integer, schema.ForeignKey('wrregin.id')),
+ Column("page_id", types.Integer)
+ )
+