def get_tile(filename, coord):
    """ Retrieve the mime-type and raw content of a tile by coordinate.

        If the tile does not exist, None is returned for the content.
    """
    db = _connect(filename)
    db.text_factory = bytes
    
    formats = {
        'png': 'image/png',
        'jpg': 'image/jpeg',
        'json': 'application/json',
        'pbf': 'application/x-protobuf',
        None: None
    }

    format = db.execute("SELECT value FROM metadata WHERE name='format'").fetchone()
    format = format and format[0] or None
    mime_type = formats[format]

    tile_row = (2**coord.zoom - 1) - coord.row # Hello, Paul Ramsey.
    q = 'SELECT tile_data FROM tiles WHERE zoom_level=? AND tile_column=? AND tile_row=?'
    content = db.execute(q, (coord.zoom, coord.column, tile_row)).fetchone()
    content = content and content[0] or None

    return mime_type, content

def delete_tile(filename, coord):
    """ Delete a tile by coordinate.
    """
    db = _connect(filename)
    db.text_factory = bytes
    
    tile_row = (2**coord.zoom - 1) - coord.row # Hello, Paul Ramsey.
    q = 'DELETE FROM tiles WHERE zoom_level=? AND tile_column=? AND tile_row=?'
    db.execute(q, (coord.zoom, coord.column, tile_row))

def list_tiles(filename):
    """ Get a list of tile coordinates.
    """
    db = _connect(filename)
    db.text_factory = bytes
    
    tiles = db.execute('SELECT tile_row, tile_column, zoom_level FROM tiles')
    tiles = (((2**z - 1) - y, x, z) for (y, x, z) in tiles) # Hello, Paul Ramsey.
    tiles = [Coordinate(row, column, zoom) for (row, column, zoom) in tiles]
    
    return tiles

def put_tile(filename, coord, content):
    """
    """
    db = _connect(filename)
    db.text_factory = bytes
    
    tile_row = (2**coord.zoom - 1) - coord.row # Hello, Paul Ramsey.
    q = 'REPLACE INTO tiles (zoom_level, tile_column, tile_row, tile_data) VALUES (?, ?, ?, ?)'
    db.execute(q, (coord.zoom, coord.column, tile_row, buffer(content)))

    db.commit()
    db.close()

def create_tileset(filename, name, type, version, description, format, bounds=None):
    """ Create a tileset 1.1 with the given filename and metadata.

        From the specification:

        The metadata table is used as a key/value store for settings.
        Five keys are required:

          name:
            The plain-english name of the tileset.

          type:
            overlay or baselayer

          version:
            The version of the tileset, as a plain number.

          description:
            A description of the layer as plain text.

          format:
            The image file format of the tile data: png or jpg or json

        One row in metadata is suggested and, if provided, may enhance performance:

          bounds:
            The maximum extent of the rendered map area. Bounds must define
            an area covered by all zoom levels. The bounds are represented in
            WGS:84 - latitude and longitude values, in the OpenLayers Bounds
            format - left, bottom, right, top. Example of the full earth:
            -180.0,-85,180,85.
    """

    if format not in ('png', 'jpg', 'json', 'pbf'):
        raise Exception('Format must be one of "png", "jpg", "json" or "pbf", not "%s"' % format)
    
    db = _connect(filename)

    db.execute('CREATE TABLE metadata (name TEXT, value TEXT, PRIMARY KEY (name))')
    db.execute('CREATE TABLE tiles (zoom_level INTEGER, tile_column INTEGER, tile_row INTEGER, tile_data BLOB)')
    db.execute('CREATE UNIQUE INDEX coord ON tiles (zoom_level, tile_column, tile_row)')

    db.execute('INSERT INTO metadata VALUES (?, ?)', ('name', name))
    db.execute('INSERT INTO metadata VALUES (?, ?)', ('type', type))
    db.execute('INSERT INTO metadata VALUES (?, ?)', ('version', version))
    db.execute('INSERT INTO metadata VALUES (?, ?)', ('description', description))
    db.execute('INSERT INTO metadata VALUES (?, ?)', ('format', format))

    if bounds is not None:
        db.execute('INSERT INTO metadata VALUES (?, ?)', ('bounds', bounds))

    db.commit()
    db.close()

def get_tile_metadata(filename, coord, flip_y=True):
    """ Retrieve metadata for a tile by coordinate.

        If the tile does not exist, None is returned for the content.
    """
    db = _connect(filename)
    db.text_factory = bytes

    tile_row = coord.row
    if flip_y:
        tile_row = (2**coord.zoom - 1) - coord.row # Hello, Paul Ramsey.
    q = 'SELECT updated_at FROM tiles WHERE zoom_level=? AND tile_column=? AND tile_row=?'
    content = db.execute(q, (coord.zoom, coord.column, tile_row)).fetchone()
    content = content and content[0] or None

    return "{\"updated_at\": %d, \"zoom\": %d, \"x\": %d, \"y\": %d}" % (content, coord.zoom, coord.column, tile_row)

def tileset_exists(filename):
    """ Return true if the tileset exists and appears to have the right tables.
    """
    if not exists(filename):
        return False
    
    # this always works
    db = _connect(filename)
    db.text_factory = bytes
    
    try:
        db.execute('SELECT name, value FROM metadata LIMIT 1')
        db.execute('SELECT zoom_level, tile_column, tile_row, tile_data FROM tiles LIMIT 1')
    except:
        return False
    
    return True

def tileset_info(filename):
    """ Return name, type, version, description, format, and bounds for a tileset.
    
        Returns None if tileset does not exist.
    """
    if not tileset_exists(filename):
        return None
    
    db = _connect(filename)
    db.text_factory = bytes
    
    info = []
    
    for key in ('name', 'type', 'version', 'description', 'format', 'bounds'):
        value = db.execute('SELECT value FROM metadata WHERE name = ?', (key, )).fetchone()
        info.append(value and value[0] or None)
    
    return info

    def __init__(self, layer, geopackage, tileset):
        """
        """
        sethref = urljoin(layer.config.dirpath, geopackage)
        scheme, h, path, q, p, f = urlparse(sethref)

        if scheme not in ("file", ""):
            raise Exception('Bad scheme in GeoPackage provider, must be local file: "%s"' % scheme)

        self.layer = layer
        self.geopackage = geopackage
        self.tileset = tileset
        self.db = _connect(path)
        self.db.text_factory = bytes
        self.tilequery = "SELECT tile_data FROM {tileset} WHERE zoom_level=? AND tile_column=? AND tile_row=?".format(
            tileset=tileset
        )
        (self.mime_type,) = self.db.execute(
            "SELECT mime_type FROM raster_format_metadata WHERE r_table_name=?", (tileset,)
        ).fetchone()