object_store: Directory Support

**Is your feature request related to a problem or challenge? Please describe what you are trying to do.**

Currently object_store treats directories as an implementation detail:

* LocalFileSystem automatically creates them on-demand (and optionally deletes them apache/arrow-rs#5978)
* There is no mechanism to unambiguously refer to a directory (#7026)
* Various implementations filter out directories when listing (e.g. [azure](https://github.com/apache/arrow-rs/blob/main/object_store/src/azure/client.rs#L1013))

This ensures portability but has a couple of downsides:

* There is no way to delete directories in LocalFileSystem
* There is no way to create or delete objects with trailing `/` (#7026)

A pattern used by some tools is to create empty objects with a trailing `/` to represent directories on stores that don't natively support directories. However, these pseudo directories do not really behave like normal directories:

* A filename can be created without its parent directory existing
* A directory can be deleted without deleting its children

Additionally, creating such pseudo directories on stores with native directories is at best confusing, if it is even possible.

This inherent inconsistency makes it very hard to devise a coherent API

**Describe the solution you'd like**

I think we should at least do the following:

* Better document the current situation w.r.t directories
* Skip over pseudo directories when listing (we have no mechanism to create them, so whatever is creating them should manage their lifecycle)

I'd also be interested if people have any ideas of a coherent way to support a notion of directories

**Describe alternatives you've considered**

**Additional context**

Start by reviewing the LocalFileSystem directory behavior and the listing logic in object_store/src/azure/client.rs around line 1013, then compare how other implementations treat directories. The work is complete when directory behavior is clearly documented and the project has an agreed, coherent rule for handling pseudo directories and directory listing.

Written by the indexing model from the issue text.