def get_ws_layers(gs_url, gs_user, gs_pwd, workspaces, model_number=None, latest_is_failure=False):
"""
Get the layers belonging to a workspace.
:param str gs_url: geoserver rest url
:param str gs_user: geoserver username
:param str gs_pwd: geoserver password
:param list workspaces: list of geoserver workspaces
:param str model_number: model number of interest
:return: list of tuples of form (workspace_name, [list of layers belonging to the workspace])
:rtype: list
"""
all_results = []
for workspace in workspaces:
spdss_ws = GeoServerWorkspace(gs_url, gs_user, gs_pwd, workspace)
ws_layers = spdss_ws.get_ws_layers()
unique_ws_layers = list(set(ws_layers))
cleaned_layers = clean_layer_names(unique_ws_layers, model_number) # full list of layers
if latest_is_failure:
db = SqliteDB()
try:
query_results = db.query_db(workspace=workspace)
already_cached_layers = [query_dict['layer'] for query_dict in query_results] # layers that have already been cached
layers_to_be_cached = [layer for layer in cleaned_layers if layer not in already_cached_layers]
layer_list = layers_to_be_cached # list of layers that haven't been cached because of a previous script exception
except: # probably means the database doesn't exist
layer_list = cleaned_layers
else:
layer_list = cleaned_layers
ws_results = (workspace, layer_list)
all_results.append(ws_results)
return all_results
def __init__(self, conf):
self.conf = conf
if conf['type'].upper() == "MYSQL":
self.db = MysqlDB(conf)
elif conf['type'].upper() == "SQLITE":
self.db = SqliteDB(conf)
else:
return False
def __init__(self, db_name, create=False):
self.auto_commit = False
# Database definitions
self.DATABASE_NAME = db_name
self.TABLE_NAME = 'CEP'
self.CREATE_TABLE = """
CREATE TABLE {0} (
ID INTEGER PRIMARY KEY,
CEP TEXT,
CIDADE INT,
ESTADO INT,
BAIRRO INT,
LOGRADOURO NUMERIC,
DESCRICAO TEXT
);
""".format(self.TABLE_NAME)
self.TABLE_FIELDS = [
'ID', 'CEP', 'CIDADE', 'ESTADO', 'BAIRRO', 'LOGRADOURO',
'DESCRICAO'
]
self.INSERT = 'INSERT INTO {0} ({1}) VALUES (?, ?, ?, ?, ?, ?, ?);'.format(
self.TABLE_NAME, ', '.join(self.TABLE_FIELDS))
self.UPDATE = 'UPDATE {0} SET {{0}} = ? WHERE ID = ?;'.format(
self.TABLE_NAME)
self.SELECT_ALL = 'SELECT {{0}} FROM {0} ORDER BY ID'.format(
self.TABLE_NAME)
self.SELECT_FILTER = 'SELECT {{0}} FROM {0} WHERE {{1}}'.format(
self.TABLE_NAME)
SqliteDB.__init__(self, self.DATABASE_NAME, create)
conn = self.open()
if (conn is not None):
sql = "SELECT count(1) FROM sqlite_master WHERE type='table' AND name='{0}';".format(
self.TABLE_NAME)
cursor = conn.cursor()
cursor.execute(sql)
table_exists = int(cursor.fetchone()[0])
if (not bool(table_exists)):
print('Tabela não existe! {0}'.format(self.TABLE_NAME))
self.connection.execute(self.CREATE_TABLE)
self.connection.commit()
else:
raise Exception('Não foi possível abrir [{0}]'.format(
self.DATABASE_NAME))
def execute_seed_request(gwc_url, gs_user, gs_pwd, cache_data, grid='EPSG:4326',
tile_format='image/png8', zoom_start=0,
zoom_stop=3, threads=1, progress_check=5, exclude_layers=(),
latest_is_failure=False, seed_type='seed'):
"""
Generate seeding xml and post it to Geoserver for each layer in a workspace.
The starting zoom level defaults to 0.
The progress of the tile caching will be printed to the console. In addition,
a rough record of the layers cached is logged to seeding.log.
:param str gwc_url: geoserver geowebcache rest url
:param str gs_user: geoserver username
:param str gs_pwd: geoserver password
:param list cache_data: list of tuples containing the workspace name and a dictionary; the dictionary contains keys: layer_name, style_name
.. seealso :: :function:'get_layer_styles'
:param str grid: full name of the gridset to be used
:param str tile_format: format that the tile images should be generated in
:param int grid_number: integer value of the gridset
:param int zoom_start: the minimum zoom level that should be cached
:param int zoom_stop: the maximum zoom level that should be cached
:param int threads: number of threads to be used when generating a tile cache
:param float progress_check: interval in seconds between checks to GeoServer for progress on a caching job
:param exclude_layers: iterable containing the names of layers from the workspace(s) that should not be cached; defaults to an empty tuple
:type exclude_layers: list or tuple
:return: requests objects from posting the seed requests
:rtype: list
"""
# sparrow-flowline-reusable:51N2043963353 - 6.67 GB... need more disk quota with zoom_stop=10
# setup some basic logging
db = SqliteDB()
if not latest_is_failure:
try:
db.destroy_db()
except:
pass
# create a new database if the last script run worked
# otherwise append to the existing database
db.create_db()
logging.basicConfig(filename='seed_log.log',
filemode='w',
level=logging.INFO,
format='%(asctime)s %(message)s'
)
request_resps = []
job_ids_with_tiles = []
for cache_datum in cache_data:
ws_name, layer_params = cache_datum
layer_count = 'Total layers for {workspace_name}: {layer_count}'.format(workspace_name=ws_name,
layer_count=len(layer_params)
)
logging.info(layer_count)
for layer_param in layer_params:
layer_name = layer_param['layer_name']
started = 'Started - {workspace}:{layer}'.format(workspace=ws_name, layer=layer_name)
logging.info(started)
if layer_name in exclude_layers:
finished = 'Did not cache - {workspace}:{layer}'.format(workspace=ws_name, layer=layer_name)
seed_request = finished
print(datetime.datetime.now())
print(finished)
else:
style_name = layer_param['style_name']
sp_gwc = GeoWebCacheSetUp(gwc_url, gs_user, gs_pwd, ws_name,
layer_name, cert_verify=False
)
# deal with overlay layers that do not have image/png8 as a caching option
if ws_name in OVERLAY_WORKSPACES:
tiling_config = sp_gwc.get_tile_cache_config()
config_content = tiling_config[1]
mime_formats = config_content['GeoServerLayer'].get('mimeFormats', [])
# check if image/png8 is one of the mimeFormats
# if it is not, add it
if tile_format not in mime_formats:
mime_formats.append(tile_format)
updated_cache_config_xml = sp_gwc.create_disable_enable_cache_xml(format_list=tuple(mime_formats),
style=style_name,
gridset_name=grid
)
update_cache_config = sp_gwc.disable_or_enable_cache(payload=updated_cache_config_xml)
update_config_message = 'Updated layer parmeters for {workspace}:{layer} - {status_code}'.format(workspace=ws_name,
layer=layer_name,
status_code=update_cache_config.status_code
)
print(update_config_message)
logging.info(update_config_message)
seed_xml = sp_gwc.create_seed_xml(style=None,
tile_format=tile_format,
gridset_id=grid,
zoom_start=zoom_start,
zoom_stop=zoom_stop,
threads=threads,
seed_type=seed_type
)
seed_request_attempts = 0
while seed_request_attempts < 4:
try:
seed_request = sp_gwc.seed_request(seed_xml)
seed_request_attempts = 0
break
except ConnectionError:
seed_request_attempts += 1
seed_request_connection_error = 'Encountered a connection error ({0}).'.format(seed_request_attempts)
print(seed_request_connection_error)
logging.info(seed_request_connection_error)
if seed_request_attempts == 3:
abort_message = 'Encountered a connection error {0} times. Aborting script.'.format(seed_request_attempts)
print(abort_message)
logging.info(abort_message)
raise SuccessiveConnectionError
time.sleep(progress_check*2)
url_message = 'Request URL: {0}'.format(seed_request.url)
status_code_message = 'Status: {0}'.format(seed_request.status_code)
print(url_message)
print(status_code_message)
array_length = 1
while array_length > 0:
attempts = 0
while attempts < 4:
try:
status = sp_gwc.query_task_status()
attempts = 0 # reset attempts to 0
break
except ConnectionError:
attempts += 1
conn_err_message = 'Encountered a connection error ({0}).'.format(attempts)
print(conn_err_message)
logging.info(conn_err_message)
if attempts == 3: # only try a total of 4 times
connection_error_message = 'Encountered a connection error {0} times. Aborting script.'.format(attempts)
print(connection_error_message)
logging.info(connection_error_message)
raise SuccessiveConnectionError
time.sleep(progress_check*2) # provide sometime for the server to respond
print(datetime.datetime.now())
status_message = '{workspace}:{layer} - {progress}'.format(workspace=ws_name,
layer=layer_name,
progress=status[1]
)
print(status_message)
long_array = status[1]['long-array-array']
try:
thread0 = long_array[0]
tile_count = thread0[1]
job_id = thread0[3]
job_tile_count = (job_id, tile_count)
if job_tile_count not in job_ids_with_tiles:
job_ids_with_tiles.append(job_tile_count)
except IndexError:
pass
array_length = len(long_array)
time.sleep(progress_check)
finished = 'Finished - {workspace}:{layer}'.format(workspace=ws_name, layer=layer_name)
complete_dt = str(datetime.datetime.now())
# keep track of the layers that have already been cached
# save time by checking this database when re-running after a failure
db.insert_data(workspace=ws_name, layer=layer_name, complete_datetime=complete_dt)
logging.info(finished)
request_resps.append(seed_request)
tile_counts = []
for job_tile_tuple in job_ids_with_tiles:
tile_count = float(job_tile_tuple[1])
tile_counts.append(tile_count)
print(tile_counts)
tile_sum = sum(tile_counts)
print('Total tiles: {0}'.format(tile_sum))
return request_resps
class DB(object):
"""
修改服务器上的配置文件/etc/my.cnf，在对应位置添加以下设置:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
"""
def __init__(self, conf):
self.conf = conf
if conf['type'].upper() == "MYSQL":
self.db = MysqlDB(conf)
elif conf['type'].upper() == "SQLITE":
self.db = SqliteDB(conf)
else:
return False
# config = {
# 'host': conf['host'],
# 'port': conf['port'],
# 'user': conf['user'],
# 'passwd': conf['passwd'],
# 'charset':'utf8mb4', # 支持1-4个字节字符
# 'cursorclass': pymysql.cursors.DictCursor
# }
# self.conn = pymysql.connect(**config)
# self.conn.autocommit(1)
# # for thread-save
# self.lock = threading.Lock()
# self.create_db(conf['database'])
# self.conn.select_db(conf['database'])
# # cache table cols
# self.table_cols = {}
# for t in self.show_tables():
# self.table_cols[t] = self.get_table_column_name(t)
def show_database(self):
c = self.conn.cursor()
sql = 'SHOW DATABASES'
Log.debug('DB -> %s' % sql)
c.execute(sql)
return [r['Database'] for r in c.fetchall()]
def show_tables(self):
c = self.conn.cursor()
sql = 'SHOW TABLES'
Log.debug('DB -> %s' % sql)
c.execute(sql)
return [r['Tables_in_' + self.conf['database']] for r in c.fetchall()]
def create_db(self, db_name):
"""
@brief Creates a database
@param db_name String
"""
if self.conf['database'] not in self.show_database():
sql = 'CREATE DATABASE IF NOT EXISTS %s CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' % db_name
Log.debug('DB -> %s' % sql)
self.execute(sql)
def create_table(self, table, cols):
"""
@brief Creates a table in database
@param table String
@param cols String, the cols in table
"""
if table not in self.table_cols:
sql = 'CREATE TABLE IF NOT EXISTS %s(id int primary key auto_increment, %s) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' % (
table, cols)
Log.debug('DB -> %s' % sql)
self.execute(sql)
self.table_cols[table] = ['id'] + [
c.strip().split(' ')[0] for c in cols.split(',')
]
def delete_table(self, table):
"""
@brief Delete a table in database
@param table String
"""
if table in self.table_cols:
sql = "DROP TABLE IF EXISTS %s" % table
Log.debug('DB -> %s' % sql)
self.execute(sql)
self.table_cols.pop(table)
def table(self, value):
self.db.table(value)
return self
def field(self, value="*"):
self.db.field(value)
return self
def where(self, value="1=1"):
self.db.where(value)
return self
def order(self, value=None):
self.db.order(value)
return self
def groupby(self, value=None):
self.db.groupby(value)
return self
def limit(self, value=None):
self.db.limit(value)
return self
def page(self, value):
self.db.page(value)
return self
def union(self, union_type="all", table_name=""):
self.db.union(union_type, table_name)
return self
def sql_join(self,
join_type="INNER",
table_name="",
table_alias=None,
join_on=False):
self.db.sql_join(join_type, table_name, table_alias, join_on)
return self
def add(self, value):
self.db.add(value)
def save(self, value):
self.db.save(value)
def insert(self, table, value):
"""
@brief Insert a row in table
@param table String
@param value Tuple
"""
# col_name = self.table_cols[table][1:]
col_name = value.keys()
# print array_join(value.values(), ',')
# # print col_name
# # print value.values()
# return
sql = "INSERT INTO %s(%s) VALUES (%s)" % (table, str(
','.join(col_name)), array_join(value.values(), ','))
Log.debug('DB -> %s' % sql)
# print sql
self.execute(sql)
def update(self, table, value, where):
col_name = value.keys()
setfield = []
for key, valu in value.items():
if valu is None:
valu = ''
afield = "%s = %s" % (key, ("'" + valu + "'" if isinstance(
valu, basestring) else valu))
setfield.append(afield)
sql = "update %s set %s " % (table, str(','.join(setfield)))
if isinstance(where, dict):
swhere = []
for wkey, wvalue in where.items():
awhree = "%s = %s" % (wkey, ("'" + wvalue + "'" if isinstance(
wvalue, basestring) else wvalue))
swhere.append(awhree)
sql = sql + " where %s" % (str(' and '.join(swhere)))
elif isinstance(where, str):
sql = sql + where
else:
return 0
# print sql
Log.debug('DB -> %s' % sql)
self.execute(sql)
def insertmany(self, table, values):
"""
@brief Insert many rows in table
@param table String
@param values Array of tuple
"""
col_name = self.table_cols[table][1:]
sql = 'INSERT INTO %s(%s) VALUES (%s)' % (
table, ','.join(col_name), ','.join(['%s'] * len(values[0])))
Log.debug('DB -> %s' % sql)
self.execute(sql, values)
def select(self):
"""
@brief select all result from table
@param table String
@param field String
@param condition String
@return result Tuple
"""
return self.db.select()
def get_table_column_name(self):
"""
@brief select all result from table
@param table String
@return result Array
"""
return self.db.get_table_column_name()
def execute(self, sql, values=None):
"""
@brief execute sql commands, return result if it has
@param sql String
@param value Tuple
@return result Array
"""
# c = self.conn.cursor()
# self.lock.acquire()
# hasReturn = sql.lstrip().upper().startswith("SELECT")
# result = []
# try:
# if values:
# c.executemany(sql, values)
# else:
# c.execute(sql)
# if hasReturn:
# result = c.fetchall()
# except Exception, e:
# Log.error(traceback.format_exc())
# self.conn.rollback()
# finally:
# self.lock.release()
# if hasReturn:
# return result
return self.db.execute(sql, values)
def delete(self, table, where):
"""
@brief execute sql commands, return result if it has
@param table String
@param field String
@param condition String
"""
sql = "DELETE FROM %s" % (table)
if isinstance(where, dict):
swhere = []
for wkey, wvalue in where.items():
awhree = "%s = %s" % (wkey, ("'" + wvalue + "'" if isinstance(
wvalue, basestring) else wvalue))
swhere.append(awhree)
sql = sql + " where %s" % (str(' and '.join(swhere)))
elif isinstance(where, basestring):
sql = sql + where
else:
return 0
Log.debug('DB -> %s' % sql)
self.execute(sql)
def close(self):
"""
@brief close connection to database
"""
Log.debug('DB -> close')
# 关闭数据库连接
self.conn.close()
