Add more extensive test suite of nanoarrow usage

[vyasr]

nanoarrow is designed to be a lightweight dependency easily consumed as part of the build process of its dependents. Although primarily designed around vendoring, in practice there are numerous ways in which a consumer might expect to be able to use nanoarrow, including:

• Building against a preinstalled (dynamic or static) nanoarrow (Export CMake config #350, Tests fail linking against libarrow.so built in Conda environment #254)
• Building against a prebuilt, not installed nanoarrow build directory.
• Building against a copy of nanoarrow fetched at build time (Unable to build shared library with FetchContent #364)
• Vendoring the files directly. IIUC in this case the expectation is that the dist/ files are the ones that are meant to be vendored, and those files are produced by building setting the NANOARROW_BUNDLE flag on (automated by this GHA).

Additionally, users may wish to use dynamic or static nanoarrow libraries.

Given this range of use cases, I think it would be very helpful to add explicit testing of usage of nanoarrow in different scenarios. A good way to do this would probably be to add more examples since it looks like those are also being built in CI. If there is interest in this. I am happy to volunteer my time to contribute some of these and improve nanoarrow's CMake along the way since I know CMake can be a bit tricky to work with. I can open issues and add associated examples + fixes as I find them. Handling this systematically would IMHO be a very useful way to increase consumer confidence in being able to use nanoarrow reliably.

[vyasr]

@paleolimbot would love to hear your thoughts here, or if you have alternative ideas!

[paleolimbot]

All of these are great ideas! Our CMake setup definitely needs improving...nanoarrow should definitely work in all the circumstances you listed.

Adding some examples of CMake projects to the examples directory you linked would be very helpful...I don't have a lot of experience with how CMake is used in the wild but I am game to help make the examples build if you can provide them!