diff options
28 files changed, 3578 insertions, 0 deletions
diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..e7bf528 --- /dev/null +++ b/.gitignore @@ -0,0 +1,2 @@ +build/ +examples-test/
\ No newline at end of file diff --git a/CMakeLists.txt b/CMakeLists.txt new file mode 100644 index 0000000..43c251d --- /dev/null +++ b/CMakeLists.txt @@ -0,0 +1,217 @@ +project("csound_sqldb") + +cmake_minimum_required(VERSION 3.8) + +set(APIVERSION "6.0") + +# Release or Debug +set(CMAKE_BUILD_TYPE "Release") + +# force make to print the command lines +set(CMAKE_VERBOSE_MAKEFILE on) + +# path to Csound cmake module +set(CMAKE_MODULE_PATH ${CMAKE_MODULE_PATH} "${CMAKE_SOURCE_DIR}/cmake/Modules/") + +# set compilation flags +set(CMAKE_CXX_FLAGS ${CMAKE_CXX_FLAGS} "-std=c++11 -fpermissive -fPIC -w -DUSE_DOUBLE -DB64BIT") + +# options +option(USE_LIB64 "Set to on to set installation dir for libs to lib64" OFF) +option(USE_DOUBLE "Use doubles for audio calculations" ON) +option(CPP11 "c++11" ON) + +set(BUILDING_CSOUND_PLUGINS ON) + +# ---------------------------------------------- + +include(FindCsound) +include(FindPQXX) +include(FindSqlite3) +include(FindMySQL) + +include(CheckCCompilerFlag) +include(CheckCXXCompilerFlag) + +# ----------------------------------------------- + +function(addflag flag flagname) + check_c_compiler_flag(${flag} ${flagname}) + if (${flagname}) + # message(STATUS "Setting C flag ${flag}") + set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} ${flag}" PARENT_SCOPE) + endif() + check_cxx_compiler_flag(${flag} CXX_${flagname}) + if (CXX_${flagname}) + set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} ${flag}" PARENT_SCOPE) + endif() +endfunction(addflag) + + +MACRO(SUBDIRLIST result curdir) + FILE(GLOB children RELATIVE ${curdir} ${curdir}/*) + SET(dirlist "") + FOREACH(child ${children}) + message(STATUS "looking at ${child}") + IF(IS_DIRECTORY ${curdir}/${child}) + LIST(APPEND dirlist ${child}) + ENDIF() + ENDFOREACH() + SET(${result} ${dirlist}) +ENDMACRO() + + +MACRO(ADD_ALL_SUBDIRECTORIES directory) + subdirlist(SUBDIRS ${directory}) + message(STATUS "Found subdirs: ${SUBDIRS}") + foreach(SUBDIR ${SUBDIRS}) + set(thissubdir "${directory}/${SUBDIR}") + if(EXISTS "${directory}/${SUBDIR}/CMakeLists.txt") + message(STATUS "Adding subdir: ${thissubdir}") + add_subdirectory(${directory}/${SUBDIR}) + else() + message(WARNING "Skipping ${directory}/${SUBDIR} because no CMakeLists.txt file was found") + endif() + endforeach() +ENDMACRO() + + + +# set optimization flags +if(CMAKE_COMPILER_IS_GNUCXX OR CMAKE_COMPILER_IS_CLANG) + add_definitions(-fvisibility=hidden) + if(NATIVE) + add_definitions(-march=native) + endif() + + include(CheckCCompilerFlag) + include(CheckCXXCompilerFlag) + + addflag(-msse HAS_SSE) + addflag(-msse2 HAS_SSE2) + addflag(-mfgpath=sse HAS_FPMATH_SSE) + +endif() + +if(MINGW) + set(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -mstackrealign") + set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -mstackrealign") +endif() + +addflag(-ftree-vectorize HAS_TREE_VECTORIZE) +addflag(-ffast-math HAS_FAST_MATH) +addflag(-fomit-frame-pointer HAS_OMIT_FRAME_POINTER) + + +# ------------------------------------------------------------------- + +set(CS_FRAMEWORK_DEST "~/Library/Frameworks") + + +if(USE_LIB64) + set(LIBRARY_INSTALL_DIR "lib64") + add_definitions("-DLIB64") +else() + set(LIBRARY_INSTALL_DIR "lib") +endif() + +message(STATUS "LIBRARY INSTALL DIR: ${LIBRARY_INSTALL_DIR}") + +# ------------------------------------------------------------------- + + +if(USE_DOUBLE) + message(STATUS ">>> using doubles") + + if(APPLE) + set(CSOUNDLIB "CsoundLib64") + set(PLUGIN_INSTALL_DIR "${CS_FRAMEWORK_DEST}/${CSOUNDLIB}.framework/Versions/${APIVERSION}/Resources/Opcodes64") + else() + set(CSOUNDLIB "csound64") + set(PLUGIN_INSTALL_DIR "${LIBRARY_INSTALL_DIR}/csound/plugins64-${APIVERSION}") + endif() +else() + message(STATUS ">>> not using doubles") + if(APPLE) + set(CSOUNDLIB "CsoundLib") + set(PLUGIN_INSTALL_DIR "${CS_FRAMEWORK_DEST}/${CSOUNDLIB}.framework/Versions/${APIVERSION}/Resources/Opcodes") + else() + set(CSOUNDLIB "csound") + set(PLUGIN_INSTALL_DIR "${LIBRARY_INSTALL_DIR}/csound/plugins-${APIVERSION}") + endif() +endif() + + +# ------------------------------------------------------------------- + +# Csound opcode build +find_package(Csound) + + +set(BUILD_PLUGINS_DIR ${CMAKE_CURRENT_BINARY_DIR}) + +if(NOT CSOUND_FOUND) + message(FATAL_ERROR "Csound installation not found") +endif() + +if(NOT PQXX_FOUND AND NOT SQLITE3_FOUND AND NOT MYSQLCONNECTORCPP_FOUND) + message(FATAL_ERROR "No database libraries could be found") +endif() + +# +#if(APPLE) +# add_library(sqldb SHARED ${srcs}) +#else() +# add_library(sqldb MODULE ${srcs}) +#endif() + +set(CPPFILES src/opcodes.cpp src/connection.cpp) + + +if (PQXX_FOUND) + message(STATUS "Using PostgreSQL") + list(APPEND CPPFILES "src/postgresql.cpp") + set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -DBUILD_POSTGRES") +endif() + +if (SQLITE3_FOUND) + message(STATUS "Using SQLite3") + list(APPEND CPPFILES "src/sqlite3.cpp") + set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -DBUILD_SQLITE") +endif() + +if (MYSQLCONNECTORCPP_FOUND) + message(STATUS "Using MySQL") + list(APPEND CPPFILES "src/mysql.cpp") + set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -DBUILD_MYSQL") +endif() + + + +#include_directories(/usr/include) +include_directories(${CSOUND_INCLUDE_DIRS}) +include_directories(include) + +if (PQXX_FOUND) + link_libraries(${PQXX_LIBRARIES}) + include_directories(${PQXX_INCLUDE_DIRECTORIES}) +endif() + +if (SQLITE3_FOUND) + link_libraries(${SQLITE3_LIBRARIES}) + include_directories(${SQLITE3_INCLUDE_DIRS}) +endif() + +if (MYSQLCONNECTORCPP_FOUND) + link_libraries(${MYSQLCONNECTORCPP_LIBRARIES}) + include_directories(${MYSQLCONNECTORCPP_INCLUDE_DIRS}) +endif() + +add_library(sqldb SHARED ${CPPFILES}) + +set_target_properties(sqldb PROPERTIES + RUNTIME_OUTPUT_DIRECTORY ${BUILD_PLUGINS_DIR} + LIBRARY_OUTPUT_DIRECTORY ${BUILD_PLUGINS_DIR}) + +install(TARGETS sqldb LIBRARY DESTINATION "${PLUGIN_INSTALL_DIR}" ) + @@ -0,0 +1,502 @@ + GNU LESSER GENERAL PUBLIC LICENSE + Version 2.1, February 1999 + + Copyright (C) 1991, 1999 Free Software Foundation, Inc. + 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + Everyone is permitted to copy and distribute verbatim copies + of this license document, but changing it is not allowed. + +[This is the first released version of the Lesser GPL. It also counts + as the successor of the GNU Library Public License, version 2, hence + the version number 2.1.] + + Preamble + + The licenses for most software are designed to take away your +freedom to share and change it. By contrast, the GNU General Public +Licenses are intended to guarantee your freedom to share and change +free software--to make sure the software is free for all its users. + + This license, the Lesser General Public License, applies to some +specially designated software packages--typically libraries--of the +Free Software Foundation and other authors who decide to use it. You +can use it too, but we suggest you first think carefully about whether +this license or the ordinary General Public License is the better +strategy to use in any particular case, based on the explanations below. + + When we speak of free software, we are referring to freedom of use, +not price. Our General Public Licenses are designed to make sure that +you have the freedom to distribute copies of free software (and charge +for this service if you wish); that you receive source code or can get +it if you want it; that you can change the software and use pieces of +it in new free programs; and that you are informed that you can do +these things. + + To protect your rights, we need to make restrictions that forbid +distributors to deny you these rights or to ask you to surrender these +rights. These restrictions translate to certain responsibilities for +you if you distribute copies of the library or if you modify it. + + For example, if you distribute copies of the library, whether gratis +or for a fee, you must give the recipients all the rights that we gave +you. You must make sure that they, too, receive or can get the source +code. If you link other code with the library, you must provide +complete object files to the recipients, so that they can relink them +with the library after making changes to the library and recompiling +it. And you must show them these terms so they know their rights. + + We protect your rights with a two-step method: (1) we copyright the +library, and (2) we offer you this license, which gives you legal +permission to copy, distribute and/or modify the library. + + To protect each distributor, we want to make it very clear that +there is no warranty for the free library. Also, if the library is +modified by someone else and passed on, the recipients should know +that what they have is not the original version, so that the original +author's reputation will not be affected by problems that might be +introduced by others. +^L + Finally, software patents pose a constant threat to the existence of +any free program. We wish to make sure that a company cannot +effectively restrict the users of a free program by obtaining a +restrictive license from a patent holder. Therefore, we insist that +any patent license obtained for a version of the library must be +consistent with the full freedom of use specified in this license. + + Most GNU software, including some libraries, is covered by the +ordinary GNU General Public License. This license, the GNU Lesser +General Public License, applies to certain designated libraries, and +is quite different from the ordinary General Public License. We use +this license for certain libraries in order to permit linking those +libraries into non-free programs. + + When a program is linked with a library, whether statically or using +a shared library, the combination of the two is legally speaking a +combined work, a derivative of the original library. The ordinary +General Public License therefore permits such linking only if the +entire combination fits its criteria of freedom. The Lesser General +Public License permits more lax criteria for linking other code with +the library. + + We call this license the "Lesser" General Public License because it +does Less to protect the user's freedom than the ordinary General +Public License. It also provides other free software developers Less +of an advantage over competing non-free programs. These disadvantages +are the reason we use the ordinary General Public License for many +libraries. However, the Lesser license provides advantages in certain +special circumstances. + + For example, on rare occasions, there may be a special need to +encourage the widest possible use of a certain library, so that it becomes +a de-facto standard. To achieve this, non-free programs must be +allowed to use the library. A more frequent case is that a free +library does the same job as widely used non-free libraries. In this +case, there is little to gain by limiting the free library to free +software only, so we use the Lesser General Public License. + + In other cases, permission to use a particular library in non-free +programs enables a greater number of people to use a large body of +free software. For example, permission to use the GNU C Library in +non-free programs enables many more people to use the whole GNU +operating system, as well as its variant, the GNU/Linux operating +system. + + Although the Lesser General Public License is Less protective of the +users' freedom, it does ensure that the user of a program that is +linked with the Library has the freedom and the wherewithal to run +that program using a modified version of the Library. + + The precise terms and conditions for copying, distribution and +modification follow. Pay close attention to the difference between a +"work based on the library" and a "work that uses the library". The +former contains code derived from the library, whereas the latter must +be combined with the library in order to run. +^L + GNU LESSER GENERAL PUBLIC LICENSE + TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION + + 0. This License Agreement applies to any software library or other +program which contains a notice placed by the copyright holder or +other authorized party saying it may be distributed under the terms of +this Lesser General Public License (also called "this License"). +Each licensee is addressed as "you". + + A "library" means a collection of software functions and/or data +prepared so as to be conveniently linked with application programs +(which use some of those functions and data) to form executables. + + The "Library", below, refers to any such software library or work +which has been distributed under these terms. A "work based on the +Library" means either the Library or any derivative work under +copyright law: that is to say, a work containing the Library or a +portion of it, either verbatim or with modifications and/or translated +straightforwardly into another language. (Hereinafter, translation is +included without limitation in the term "modification".) + + "Source code" for a work means the preferred form of the work for +making modifications to it. For a library, complete source code means +all the source code for all modules it contains, plus any associated +interface definition files, plus the scripts used to control compilation +and installation of the library. + + Activities other than copying, distribution and modification are not +covered by this License; they are outside its scope. The act of +running a program using the Library is not restricted, and output from +such a program is covered only if its contents constitute a work based +on the Library (independent of the use of the Library in a tool for +writing it). Whether that is true depends on what the Library does +and what the program that uses the Library does. + + 1. You may copy and distribute verbatim copies of the Library's +complete source code as you receive it, in any medium, provided that +you conspicuously and appropriately publish on each copy an +appropriate copyright notice and disclaimer of warranty; keep intact +all the notices that refer to this License and to the absence of any +warranty; and distribute a copy of this License along with the +Library. + + You may charge a fee for the physical act of transferring a copy, +and you may at your option offer warranty protection in exchange for a +fee. +^L + 2. You may modify your copy or copies of the Library or any portion +of it, thus forming a work based on the Library, and copy and +distribute such modifications or work under the terms of Section 1 +above, provided that you also meet all of these conditions: + + a) The modified work must itself be a software library. + + b) You must cause the files modified to carry prominent notices + stating that you changed the files and the date of any change. + + c) You must cause the whole of the work to be licensed at no + charge to all third parties under the terms of this License. + + d) If a facility in the modified Library refers to a function or a + table of data to be supplied by an application program that uses + the facility, other than as an argument passed when the facility + is invoked, then you must make a good faith effort to ensure that, + in the event an application does not supply such function or + table, the facility still operates, and performs whatever part of + its purpose remains meaningful. + + (For example, a function in a library to compute square roots has + a purpose that is entirely well-defined independent of the + application. Therefore, Subsection 2d requires that any + application-supplied function or table used by this function must + be optional: if the application does not supply it, the square + root function must still compute square roots.) + +These requirements apply to the modified work as a whole. If +identifiable sections of that work are not derived from the Library, +and can be reasonably considered independent and separate works in +themselves, then this License, and its terms, do not apply to those +sections when you distribute them as separate works. But when you +distribute the same sections as part of a whole which is a work based +on the Library, the distribution of the whole must be on the terms of +this License, whose permissions for other licensees extend to the +entire whole, and thus to each and every part regardless of who wrote +it. + +Thus, it is not the intent of this section to claim rights or contest +your rights to work written entirely by you; rather, the intent is to +exercise the right to control the distribution of derivative or +collective works based on the Library. + +In addition, mere aggregation of another work not based on the Library +with the Library (or with a work based on the Library) on a volume of +a storage or distribution medium does not bring the other work under +the scope of this License. + + 3. You may opt to apply the terms of the ordinary GNU General Public +License instead of this License to a given copy of the Library. To do +this, you must alter all the notices that refer to this License, so +that they refer to the ordinary GNU General Public License, version 2, +instead of to this License. (If a newer version than version 2 of the +ordinary GNU General Public License has appeared, then you can specify +that version instead if you wish.) Do not make any other change in +these notices. +^L + Once this change is made in a given copy, it is irreversible for +that copy, so the ordinary GNU General Public License applies to all +subsequent copies and derivative works made from that copy. + + This option is useful when you wish to copy part of the code of +the Library into a program that is not a library. + + 4. You may copy and distribute the Library (or a portion or +derivative of it, under Section 2) in object code or executable form +under the terms of Sections 1 and 2 above provided that you accompany +it with the complete corresponding machine-readable source code, which +must be distributed under the terms of Sections 1 and 2 above on a +medium customarily used for software interchange. + + If distribution of object code is made by offering access to copy +from a designated place, then offering equivalent access to copy the +source code from the same place satisfies the requirement to +distribute the source code, even though third parties are not +compelled to copy the source along with the object code. + + 5. A program that contains no derivative of any portion of the +Library, but is designed to work with the Library by being compiled or +linked with it, is called a "work that uses the Library". Such a +work, in isolation, is not a derivative work of the Library, and +therefore falls outside the scope of this License. + + However, linking a "work that uses the Library" with the Library +creates an executable that is a derivative of the Library (because it +contains portions of the Library), rather than a "work that uses the +library". The executable is therefore covered by this License. +Section 6 states terms for distribution of such executables. + + When a "work that uses the Library" uses material from a header file +that is part of the Library, the object code for the work may be a +derivative work of the Library even though the source code is not. +Whether this is true is especially significant if the work can be +linked without the Library, or if the work is itself a library. The +threshold for this to be true is not precisely defined by law. + + If such an object file uses only numerical parameters, data +structure layouts and accessors, and small macros and small inline +functions (ten lines or less in length), then the use of the object +file is unrestricted, regardless of whether it is legally a derivative +work. (Executables containing this object code plus portions of the +Library will still fall under Section 6.) + + Otherwise, if the work is a derivative of the Library, you may +distribute the object code for the work under the terms of Section 6. +Any executables containing that work also fall under Section 6, +whether or not they are linked directly with the Library itself. +^L + 6. As an exception to the Sections above, you may also combine or +link a "work that uses the Library" with the Library to produce a +work containing portions of the Library, and distribute that work +under terms of your choice, provided that the terms permit +modification of the work for the customer's own use and reverse +engineering for debugging such modifications. + + You must give prominent notice with each copy of the work that the +Library is used in it and that the Library and its use are covered by +this License. You must supply a copy of this License. If the work +during execution displays copyright notices, you must include the +copyright notice for the Library among them, as well as a reference +directing the user to the copy of this License. Also, you must do one +of these things: + + a) Accompany the work with the complete corresponding + machine-readable source code for the Library including whatever + changes were used in the work (which must be distributed under + Sections 1 and 2 above); and, if the work is an executable linked + with the Library, with the complete machine-readable "work that + uses the Library", as object code and/or source code, so that the + user can modify the Library and then relink to produce a modified + executable containing the modified Library. (It is understood + that the user who changes the contents of definitions files in the + Library will not necessarily be able to recompile the application + to use the modified definitions.) + + b) Use a suitable shared library mechanism for linking with the + Library. A suitable mechanism is one that (1) uses at run time a + copy of the library already present on the user's computer system, + rather than copying library functions into the executable, and (2) + will operate properly with a modified version of the library, if + the user installs one, as long as the modified version is + interface-compatible with the version that the work was made with. + + c) Accompany the work with a written offer, valid for at + least three years, to give the same user the materials + specified in Subsection 6a, above, for a charge no more + than the cost of performing this distribution. + + d) If distribution of the work is made by offering access to copy + from a designated place, offer equivalent access to copy the above + specified materials from the same place. + + e) Verify that the user has already received a copy of these + materials or that you have already sent this user a copy. + + For an executable, the required form of the "work that uses the +Library" must include any data and utility programs needed for +reproducing the executable from it. However, as a special exception, +the materials to be distributed need not include anything that is +normally distributed (in either source or binary form) with the major +components (compiler, kernel, and so on) of the operating system on +which the executable runs, unless that component itself accompanies +the executable. + + It may happen that this requirement contradicts the license +restrictions of other proprietary libraries that do not normally +accompany the operating system. Such a contradiction means you cannot +use both them and the Library together in an executable that you +distribute. +^L + 7. You may place library facilities that are a work based on the +Library side-by-side in a single library together with other library +facilities not covered by this License, and distribute such a combined +library, provided that the separate distribution of the work based on +the Library and of the other library facilities is otherwise +permitted, and provided that you do these two things: + + a) Accompany the combined library with a copy of the same work + based on the Library, uncombined with any other library + facilities. This must be distributed under the terms of the + Sections above. + + b) Give prominent notice with the combined library of the fact + that part of it is a work based on the Library, and explaining + where to find the accompanying uncombined form of the same work. + + 8. You may not copy, modify, sublicense, link with, or distribute +the Library except as expressly provided under this License. Any +attempt otherwise to copy, modify, sublicense, link with, or +distribute the Library is void, and will automatically terminate your +rights under this License. However, parties who have received copies, +or rights, from you under this License will not have their licenses +terminated so long as such parties remain in full compliance. + + 9. You are not required to accept this License, since you have not +signed it. However, nothing else grants you permission to modify or +distribute the Library or its derivative works. These actions are +prohibited by law if you do not accept this License. Therefore, by +modifying or distributing the Library (or any work based on the +Library), you indicate your acceptance of this License to do so, and +all its terms and conditions for copying, distributing or modifying +the Library or works based on it. + + 10. Each time you redistribute the Library (or any work based on the +Library), the recipient automatically receives a license from the +original licensor to copy, distribute, link with or modify the Library +subject to these terms and conditions. You may not impose any further +restrictions on the recipients' exercise of the rights granted herein. +You are not responsible for enforcing compliance by third parties with +this License. +^L + 11. If, as a consequence of a court judgment or allegation of patent +infringement or for any other reason (not limited to patent issues), +conditions are imposed on you (whether by court order, agreement or +otherwise) that contradict the conditions of this License, they do not +excuse you from the conditions of this License. If you cannot +distribute so as to satisfy simultaneously your obligations under this +License and any other pertinent obligations, then as a consequence you +may not distribute the Library at all. For example, if a patent +license would not permit royalty-free redistribution of the Library by +all those who receive copies directly or indirectly through you, then +the only way you could satisfy both it and this License would be to +refrain entirely from distribution of the Library. + +If any portion of this section is held invalid or unenforceable under any +particular circumstance, the balance of the section is intended to apply, +and the section as a whole is intended to apply in other circumstances. + +It is not the purpose of this section to induce you to infringe any +patents or other property right claims or to contest validity of any +such claims; this section has the sole purpose of protecting the +integrity of the free software distribution system which is +implemented by public license practices. Many people have made +generous contributions to the wide range of software distributed +through that system in reliance on consistent application of that +system; it is up to the author/donor to decide if he or she is willing +to distribute software through any other system and a licensee cannot +impose that choice. + +This section is intended to make thoroughly clear what is believed to +be a consequence of the rest of this License. + + 12. If the distribution and/or use of the Library is restricted in +certain countries either by patents or by copyrighted interfaces, the +original copyright holder who places the Library under this License may add +an explicit geographical distribution limitation excluding those countries, +so that distribution is permitted only in or among countries not thus +excluded. In such case, this License incorporates the limitation as if +written in the body of this License. + + 13. The Free Software Foundation may publish revised and/or new +versions of the Lesser General Public License from time to time. +Such new versions will be similar in spirit to the present version, +but may differ in detail to address new problems or concerns. + +Each version is given a distinguishing version number. If the Library +specifies a version number of this License which applies to it and +"any later version", you have the option of following the terms and +conditions either of that version or of any later version published by +the Free Software Foundation. If the Library does not specify a +license version number, you may choose any version ever published by +the Free Software Foundation. +^L + 14. If you wish to incorporate parts of the Library into other free +programs whose distribution conditions are incompatible with these, +write to the author to ask for permission. For software which is +copyrighted by the Free Software Foundation, write to the Free +Software Foundation; we sometimes make exceptions for this. Our +decision will be guided by the two goals of preserving the free status +of all derivatives of our free software and of promoting the sharing +and reuse of software generally. + + NO WARRANTY + + 15. BECAUSE THE LIBRARY IS LICENSED FREE OF CHARGE, THERE IS NO +WARRANTY FOR THE LIBRARY, TO THE EXTENT PERMITTED BY APPLICABLE LAW. +EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR +OTHER PARTIES PROVIDE THE LIBRARY "AS IS" WITHOUT WARRANTY OF ANY +KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE +IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR +PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE +LIBRARY IS WITH YOU. SHOULD THE LIBRARY PROVE DEFECTIVE, YOU ASSUME +THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION. + + 16. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN +WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY +AND/OR REDISTRIBUTE THE LIBRARY AS PERMITTED ABOVE, BE LIABLE TO YOU +FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR +CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE +LIBRARY (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING +RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A +FAILURE OF THE LIBRARY TO OPERATE WITH ANY OTHER SOFTWARE), EVEN IF +SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH +DAMAGES. + + END OF TERMS AND CONDITIONS +^L + How to Apply These Terms to Your New Libraries + + If you develop a new library, and you want it to be of the greatest +possible use to the public, we recommend making it free software that +everyone can redistribute and change. You can do so by permitting +redistribution under these terms (or, alternatively, under the terms of the +ordinary General Public License). + + To apply these terms, attach the following notices to the library. It is +safest to attach them to the start of each source file to most effectively +convey the exclusion of warranty; and each file should have at least the +"copyright" line and a pointer to where the full notice is found. + + <one line to give the library's name and a brief idea of what it does.> + Copyright (C) <year> <name of author> + + This library is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 2.1 of the License, or (at your option) any later version. + + This library is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public + License along with this library; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + +Also add information on how to contact you by electronic and paper mail. + +You should also get your employer (if you work as a programmer) or your +school, if any, to sign a "copyright disclaimer" for the library, if +necessary. Here is a sample; alter the names: + + Yoyodyne, Inc., hereby disclaims all copyright interest in the + library `Frob' (a library for tweaking knobs) written by James Random Hacker. + + <signature of Ty Coon>, 1 April 1990 + Ty Coon, President of Vice + +That's all there is to it! diff --git a/OpcodeDocumentation.md b/OpcodeDocumentation.md new file mode 100644 index 0000000..cf0c218 --- /dev/null +++ b/OpcodeDocumentation.md @@ -0,0 +1,111 @@ + +# Connection + +### dbconnect + idb dbconnect Stype, Spath + idb dbconnect Stype, Shost, Sdatabase, Suser, Spassword + +Connect to a database of type *Stype* and keep the reference in the handle *idb* + +SQLite connections require only the path to the database file, or *:memory:* can be used for an in-memory database. +MySQL and PostgreSQL connections require hostname/IP, database name, username and password. + + +Recommended to be run in the global orchestra space. + + ; SQLite connection + gidb dbconnect "sqlite", "/path/to/database.db" + + ; MySQL and PostgreSQL connections + gidb dbconnect "mysql", "hostname", "database_name", "username", "password" + gidb dbconnect "postgresql", "hostname", "database_name", "username", "password" + +# Executing statements +All opcodes require + + - *idb* , which is the handle created by the dbconnect opcode. + - *Squery*, which is the SQL statement to be executed. + +## i-rate query opcodes + + + +### dbexec + dbexec idb, Squery +Execute a SQL statement returning no results. + + dbexec gidb, "CREATE TABLE frequencies (frequency FLOAT)" +### dbscalar + ires dbscalar idb, Squery [, irow] [, icolumn] + Sres dbscalar idb, Squery [, irow] [, icolumn] +Return a single numeric or string value. Optionally *irow* and *icolumn* can be specified which default to 0 and 0 , ie the first value. + + inumber dbscalar gidb, "SELECT 1, 2, 3", 0, 2 + Svalue dbscalarstr gidb, "SELECT 'this', 'is', 'a', 'test'" + +### dbarray + ires[][] dbarray idb, Squery + Sres[][] dbarray idb, Squery +Return a two-dimensional numeric or string array. + + ires[][] dbarray gidb, "SELECT 1, 2 UNION SELECT 3, 4" + Sres[][] dbarray gidb, "SELECT 'this', 'is' UNION SELECT 'a', 'test'" + + +## k-rate query opcodes +All opcodes require + + - *ktrigger* , which triggers the execution of the statement when the value is 1 or -1. If -1, any future triggers are ignored. For example *ktrigger* can be set to -1 initially and the statement will execute once. + +All opcodes emit + + - *kdone* , which is set to 1 for a single k-cycle when the statement execution has completed. + +### dbexec_k + kdone dbexec_k idb, Squery, ktrigger +Execute a SQL statement returning no results. + + kdone dbexec_k gidb, "CREATE TABLE frequencies (frequency FLOAT)", -1 + +### dbscalar_k + kdone, kres dbscalar idb, Squery, ktrigger [, krow] [, kcolumn] + kdone, Sres dbscalar idb, Squery, ktrigger [, krow] [, kcolumn] +Return a single numeric or string value. Optionally *krow* and *kcolumn* can be specified which default to 0 and 0 , ie the first value. + + + kdone, knumber dbscalar_k gidb, "SELECT 1, 2, 3", ktrigger, 0, 2 + kdone, Svalue dbscalarstr_k gidb, "SELECT 'this', 'is', 'a', 'test'", ktrigger + +### dbarray_k + kdone, kres[][] dbarray_k idb, Squery, ktrigger + kdone, Sres[][] dbarray_k idb, Squery, ktrigger +Return a two-dimensional numeric or string array. Note: any operations on the result array may fail before the query has completed. Hence ideally initialise the array first, or make sure *kdone* == 1 , otherwise the accessing the array will fail. + + kdone, kres[][] dbarray gidb, "SELECT 1, 2 UNION SELECT 3, 4", -1 + kdone, Sres[][] dbarray_k gidb, "SELECT 'this', 'is' UNION SELECT 'a', 'test'", ktrigger + +## k-rate query opcodes (blocking) +These opcodes will block the execution of the k-cycle until complete so should not be used for realtime purposes. Ie offline rendering or special operations (eg in example 7) should be fine. + + +### dbexec_kb + dbexec_kb idb, Squery +Execute a SQL statement returning no results. + + dbexec_kb gidb, "CREATE TABLE frequencies (frequency FLOAT)" + +### dbscalar_kb + kres dbscalar_kb idb, Squery [, krow] [, kcolumn] + Sres dbscalarstr_kb idb, Squery [, krow] [, kcolumn] +Return a single numeric or string value. Optionally *krow* and *kcolumn* can be specified which default to 0 and 0 , ie the first value. + + knumber dbscalar_kb gidb, "SELECT 1, 2, 3", 0, 2 + Svalue dbscalar_kb gidb, "SELECT 'this', 'is', 'a', 'test'" + +### dbarray_kb + kres[][] dbarray_kb idb, Squery + Sres[][] dbarray_kb idb, Squery +Return a two-dimensional numeric or string array. + + kres[][] dbarray_kb gidb, "SELECT 1, 2 UNION SELECT 3, 4" + Sres[][] dbarray_kb gidb, "SELECT 'this', 'is' UNION SELECT 'a', 'test'" diff --git a/README.md b/README.md new file mode 100644 index 0000000..a73a9f8 --- /dev/null +++ b/README.md @@ -0,0 +1,52 @@ +## Introduction +csound-sqldb provides database access opcodes which allow for the querying of data in SQL databases at i- and k-rate. +MySQL, PostgreSQL and SQLite3 are supported and are used with the same opcodes after the initial connection. The opcodes have currently been tested on Linux only. + +The opcodes rely on changes made after the official 6.13.0 release of Csound so while awaiting the next release the Csound source will need to be obtained from the develop branch of https://github.com/csound/csound/ otherwise they will not build. + +## Opcode overview +The opcodes are detailed in full in OpcodeDocumentation.md +Aside from the connection opcode, there are three groups available for each to allow for design options particularly when considering database latency/performance: + + - i-rate : to be used when db latency is particularly low, in global orchestra space or not running in realtime etc. + - k-rate : these execute in a separate thread invoked by a trigger, and emit a trigger when complete so can always safely be used in audio generating instruments. + - k-rate (blocking) : these execute at k-rate but block the instrument execution so would be used in special cases such as non-realtime analysis instruments (eg in example 7) or when generating audio offline etc. + +## Requirements + + - Csound libraries + - Cmake + - At least one database development library from the three below. Opcodes will support whichever of these databases can be found, which will be indicated when running the initial cmake command. + +### MySQL +Connector/C++ (https://dev.mysql.com/downloads/connector/cpp/) + + # apt get install libmysqlcppconn-dev + +### PostgreSQL +libpqxx (http://pqxx.org/development/libpqxx/wiki/DownloadPage) + + # apt get install libpqxx-dev + +### SQLite +libsqlite (https://www.sqlite.org/download.html) + + # apt get install libsqlite3-dev + + +## Building +Create a build directory at the top of the source tree, execute *cmake ..*, *make* and optionally *make install* as root. If the latter is not used/possible then the resulting libsqldb.so can be used with the *--opcode-lib* flag in Csound. +eg: + + git clone https://github.com/1bpm/csound-sqldb.git + cd csound-sqldb + mkdir build && cd build + cmake .. + make && sudo make install + +## Examples +A number of examples are included in the examples directory. Generally the syntax of each opcode is agnostic to the database type used, so the different techniques in each can be used for any database type. + + + +By Richard Knight 2019
\ No newline at end of file diff --git a/cmake/Modules/FindCsound.cmake b/cmake/Modules/FindCsound.cmake new file mode 100644 index 0000000..e55b269 --- /dev/null +++ b/cmake/Modules/FindCsound.cmake @@ -0,0 +1,29 @@ +# Try to find the Csound library. +# Once done this will define: +# CSOUND_FOUND - System has the Csound library +# CSOUND_INCLUDE_DIRS - The Csound include directories. +# CSOUND_LIBRARIES - The libraries needed to use the Csound library. + +if(APPLE) +find_path(CSOUND_INCLUDE_DIR csound.h HINTS /Library/Frameworks/CsoundLib64.framework/Headers +"$ENV{HOME}/Library/Frameworks/CsoundLib64.framework/Headers") +else() +find_path(CSOUND_INCLUDE_DIR csound.h PATH_SUFFIXES csound) +endif() + +if(APPLE) +find_library(CSOUND_LIBRARY NAMES CsoundLib64 HINTS /Library/Frameworks/CsoundLib64.framework/ +"$ENV{HOME}/Library/Frameworks/CsoundLib64.framework") +else() +find_library(CSOUND_LIBRARY NAMES csound64 csound) +endif() + +include(FindPackageHandleStandardArgs) +# handle the QUIETLY and REQUIRED arguments and set CSOUND_FOUND to TRUE +# if all listed variables are TRUE +find_package_handle_standard_args(CSOUND + CSOUND_LIBRARY CSOUND_INCLUDE_DIR) +mark_as_advanced(CSOUND_INCLUDE_DIR CSOUND_LIBRARY) + +set(CSOUND_INCLUDE_DIRS ${CSOUND_INCLUDE_DIR}) +set(CSOUND_LIBRARIES ${CSOUND_LIBRARY} ) diff --git a/cmake/Modules/FindMySQL.cmake b/cmake/Modules/FindMySQL.cmake new file mode 100644 index 0000000..08f536a --- /dev/null +++ b/cmake/Modules/FindMySQL.cmake @@ -0,0 +1,68 @@ +# - Try to find Mysql-Connector-C++ +# Once done, this will define +# +# MYSQLCONNECTORCPP_FOUND - system has Mysql-Connector-C++ installed +# MYSQLCONNECTORCPP_INCLUDE_DIRS - the Mysql-Connector-C++ include directories +# MYSQLCONNECTORCPP_LIBRARIES - link these to use Mysql-Connector-C++ +# +# The user may wish to set, in the CMake GUI or otherwise, this variable: +# MYSQLCONNECTORCPP_ROOT_DIR - path to start searching for the module + +set(MYSQLCONNECTORCPP_ROOT_DIR + "${MYSQLCONNECTORCPP_ROOT_DIR}" + CACHE + PATH + "Where to start looking for this component.") + +if(WIN32) + find_path(MYSQLCONNECTORCPP_INCLUDE_DIR + NAMES + mysql_connection.h + PATHS + "C:\\Program Files" + HINTS + ${MYSQLCONNECTORCPP_ROOT_DIR} + PATH_SUFFIXES + include) + + find_library(MYSQLCONNECTORCPP_LIBRARY + NAMES + mysqlcppconn + mysqlcppconn-static + HINTS + ${MYSQLCONNECTORCPP_ROOT_DIR} + PATH_SUFFIXES + lib) + +else() + find_path(MYSQLCONNECTORCPP_INCLUDE_DIR + mysql_connection.h + HINTS + ${MYSQLCONNECTORCPP_ROOT_DIR} + PATH_SUFFIXES + include) + + find_library(MYSQLCONNECTORCPP_LIBRARY + NAMES + mysqlcppconn + mysqlcppconn-static + HINTS + ${MYSQLCONNECTORCPP_ROOT_DIR} + PATH_SUFFIXES + lib64 + lib) +endif() + +mark_as_advanced(MYSQLCONNECTORCPP_INCLUDE_DIR MYSQLCONNECTORCPP_LIBRARY) + +include(FindPackageHandleStandardArgs) +find_package_handle_standard_args(MysqlConnectorCpp + DEFAULT_MSG + MYSQLCONNECTORCPP_INCLUDE_DIR + MYSQLCONNECTORCPP_LIBRARY) + +if(MYSQLCONNECTORCPP_FOUND) + set(MYSQLCONNECTORCPP_INCLUDE_DIRS "${MYSQLCONNECTORCPP_INCLUDE_DIR}") # Add any dependencies here + set(MYSQLCONNECTORCPP_LIBRARIES "${MYSQLCONNECTORCPP_LIBRARY}") # Add any dependencies here + mark_as_advanced(MYSQLCONNECTORCPP_ROOT_DIR) +endif()
\ No newline at end of file diff --git a/cmake/Modules/FindPQXX.cmake b/cmake/Modules/FindPQXX.cmake new file mode 100644 index 0000000..724079b --- /dev/null +++ b/cmake/Modules/FindPQXX.cmake @@ -0,0 +1,33 @@ +# - Find libpqxx +# Find the libpqxx includes and client library +# This module defines +# PQXX_INCLUDE_DIRS +# PQXX_LIBRARIES +# PQXX_FOUND + +include (FindPackageHandleStandardArgs) + +find_path (PQXX_INCLUDE_DIRS + NAME + pqxx + PATHS + /usr/include + /usr/local/include + PATH_SUFFIXES + pqxx + DOC + "Directory for pqxx headers" +) + +find_library (PQXX_LIBRARIES + NAMES + pqxx +) + +FIND_PACKAGE_HANDLE_STANDARD_ARGS("PQXX" + "libpqxx couldn't be found" + PQXX_LIBRARIES + PQXX_INCLUDE_DIRS +) + +mark_as_advanced (PQXX_INCLUDE_DIR PQXX_LIBRARY)
\ No newline at end of file diff --git a/cmake/Modules/FindSqlite3.cmake b/cmake/Modules/FindSqlite3.cmake new file mode 100644 index 0000000..9c99ae5 --- /dev/null +++ b/cmake/Modules/FindSqlite3.cmake @@ -0,0 +1,37 @@ +# Copyright (C) 2007-2009 LuaDist. +# Created by Peter Kapec <kapecp@gmail.com> +# Redistribution and use of this file is allowed according to the terms of the MIT license. +# For details see the COPYRIGHT file distributed with LuaDist. +# Note: +# Searching headers and libraries is very simple and is NOT as powerful as scripts +# distributed with CMake, because LuaDist defines directories to search for. +# Everyone is encouraged to contact the author with improvements. Maybe this file +# becomes part of CMake distribution sometimes. + +# - Find sqlite3 +# Find the native SQLITE3 headers and libraries. +# +# SQLITE3_INCLUDE_DIRS - where to find sqlite3.h, etc. +# SQLITE3_LIBRARIES - List of libraries when using sqlite. +# SQLITE3_FOUND - True if sqlite found. + +# Look for the header file. +FIND_PATH(SQLITE3_INCLUDE_DIR NAMES sqlite3.h) + +# Look for the library. +FIND_LIBRARY(SQLITE3_LIBRARY NAMES sqlite3) + +# Handle the QUIETLY and REQUIRED arguments and set SQLITE3_FOUND to TRUE if all listed variables are TRUE. +INCLUDE(FindPackageHandleStandardArgs) +FIND_PACKAGE_HANDLE_STANDARD_ARGS(SQLITE3 DEFAULT_MSG SQLITE3_LIBRARY SQLITE3_INCLUDE_DIR) + +# Copy the results to the output variables. +IF(SQLITE3_FOUND) + SET(SQLITE3_LIBRARIES ${SQLITE3_LIBRARY}) + SET(SQLITE3_INCLUDE_DIRS ${SQLITE3_INCLUDE_DIR}) +ELSE(SQLITE3_FOUND) + SET(SQLITE3_LIBRARIES) + SET(SQLITE3_INCLUDE_DIRS) +ENDIF(SQLITE3_FOUND) + +MARK_AS_ADVANCED(SQLITE3_INCLUDE_DIRS SQLITE3_LIBRARIES) diff --git a/examples/1-sqlite-basic.csd b/examples/1-sqlite-basic.csd new file mode 100644 index 0000000..8177d4a --- /dev/null +++ b/examples/1-sqlite-basic.csd @@ -0,0 +1,41 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 1 + + print some data from a sqlite3 database at i-rate + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + +; sqlite3 takes a file path, or :memory: for a temporary in-memory database +; gidb dbconnect "sqlite", "/tmp/example.db" +gidb dbconnect "sqlite", ":memory:" + +instr start_example + + ; print a random value + ires1 dbscalar gidb, "SELECT RANDOM()" + print ires1 + + ; print the second column (1) of the first row (0) + ires2 dbscalar gidb, "SELECT 99, 98", 0, 1 + print ires2 +endin + + +</CsInstruments> +<CsScore> +i "start_example" 0 1 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/2-sqlite-krate.csd b/examples/2-sqlite-krate.csd new file mode 100644 index 0000000..d6a370f --- /dev/null +++ b/examples/2-sqlite-krate.csd @@ -0,0 +1,95 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 2 + + Use a metro to trigger the insertion of points from a line into a sqlite database, + then read back the points in reverse order and play back with enveloping + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + + +; sqlite3 takes a file path, or :memory: for a temporary in-memory database +;gidb dbconnect "sqlite", ":memory:" +gidb dbconnect "sqlite", "/tmp/example.db" + + +instr start_example + + ; create the table with just one float column + kdone1 dbexec_k gidb, "CREATE TABLE IF NOT EXISTS frequencies (freq float)", -1 + + ; execute when create table statement has completed - clear out if the table does exist + kdone2 dbexec_k gidb, "DELETE FROM frequencies", kdone1 + + ; execute when delete has completed + schedkwhen kdone2, 0, 0, "db_insert", 0, 5 + schedkwhen kdone2, 0, 0, "db_select", 5, 10 + + +endin + + +; insert frequency values from line into database at k rate +instr db_insert + + ; generate some data and print + kfreq line 440, p3, 1000 + ktrigger metro 5 + printk 0.2, kfreq + + ; insert snapshot with each metro (as long as the last query is still not pending) + Squery sprintfk "INSERT INTO frequencies (freq) VALUES (%f)", kfreq + kdone dbexec_k gidb, Squery, ktrigger + +endin + + +; read frequency values from database at i rate: obtain the values in reverse order +instr db_select + + ; get all values in descending order into an array + ires[][] dbarray gidb, "SELECT freq FROM frequencies ORDER BY freq desc" + itimestep = 0.3 + index = 0 + itime = 0 + ilast = 0 + + ; loop through the values and play the frequency with some basic interpolation between last current values + while (index < lenarray(ires)) do + if (ilast != 0) then + event_i "i", "playpitch", itime, itimestep, ilast, ires[index][0] + endif + ilast = ires[index][0] + index += 1 + itime += itimestep * 0.8 + od +endin + + +; basic oscillator instrument with envelope +instr playpitch + ipitch1 = p4 + ipitch2 = p5 + kamp linseg 0, p3*0.2, 1, p3*0.6, 1, p3*0.2, 0 + kpitch line ipitch1, p3, ipitch2 + aout oscil 0.8, kpitch + outs aout*kamp, aout*kamp +endin + +</CsInstruments> +<CsScore> +i "start_example" 0 15 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/3-postgresql-basic.csd b/examples/3-postgresql-basic.csd new file mode 100644 index 0000000..053ee07 --- /dev/null +++ b/examples/3-postgresql-basic.csd @@ -0,0 +1,100 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 3 + + Run some queries on a PostgreSQL database using just inbuilt functions and system tables + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + + +; connect: type, hostname or IP, database name, username, password +gidb dbconnect "postgresql", "localhost", "databasename", "username", "password" + + +; print a random float from the database +instr execscalar + ires dbscalar gidb, "SELECT RANDOM()" + print ires +endin + + +; print the current_timestamp from the database +instr execscalarstr + Sres dbscalar gidb, "SELECT current_timestamp || '\n'" + prints Sres +endin + + +; print some float columns from the database activity statistics table +instr execarray + ires[][] dbarray gidb, "SELECT datid, pid, usesysid FROM pg_stat_activity" + printarray ires +endin + + +; print some string columns from the database activity table +instr execarraystr + Sres[][] dbarray gidb, "SELECT datname, usename, state, query_start FROM pg_stat_activity" + irow = 0 + + ; loop through as printarray does not support multidimensional string arrays + while (irow < lenarray(Sres)) do + icol = 0 + while (icol < 3) do + Sitem sprintf "%d, %d : %s\n", irow, icol, Sres[irow][icol] + prints Sitem + icol += 1 + od + irow += 1 + od + +endin + + +; print the current_timestamp from the database server twice per second, and print when the query has been executed +instr execscalar_k + ktrigger metro 2 + kdone, Sres dbscalar_k gidb, "SELECT 'now = ' || current_timestamp || '\n'", ktrigger + if (kdone == 1) then + printks "%s", 0, Sres + endif +endin + + +; print 2x2 random floats from the database every second +instr execarray_k + ktrigger metro 1 + kdone, kres[][] dbarray_k gidb, "SELECT RANDOM(), RANDOM() UNION SELECT RANDOM(), RANDOM()", ktrigger + if (kdone == 1) then + printk2 kres[0][0] + printk2 kres[0][1] + printk2 kres[1][0] + printk2 kres[1][1] + endif + +endin + + +</CsInstruments> +<CsScore> + +i "execscalar" 0 1 +i "execscalarstr" 2 1 +i "execarray" 4 1 +i "execarraystr" 6 1 +i "execscalar_k" 7 5 +i "execarray_k" 12 5 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/4-postgresql-analysis.csd b/examples/4-postgresql-analysis.csd new file mode 100644 index 0000000..38a82a6 --- /dev/null +++ b/examples/4-postgresql-analysis.csd @@ -0,0 +1,104 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 4 + + Pitch track an oscillator, inserting the time, frequency and amplitude values in a PostgreSQL database + as fast as it will accept, at k-rate. + Read back the values at i-rate in the db_play instrument and create events to mimic the original oscillator. + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + + +; connect: type, hostname or IP, database name, username, password +gidb dbconnect "postgresql", "localhost", "databasename", "username", "password" + + +; create table if it doesn't exist. truncate if it does +instr start_example + kdone1 dbexec_k gidb, "CREATE TABLE IF NOT EXISTS analysis (time FLOAT, rms FLOAT, freq FLOAT)", -1 + kdone2 dbexec_k gidb, "TRUNCATE TABLE analysis", kdone1 + + ; schedule next steps + if (kdone2 == 1) then + schedule "db_analyse", 0, 3 + schedule "db_play", 3.5, 3.5 + turnoff + endif +endin + + +instr db_analyse + + ; the oscillator + a1 oscil abs(oscil(1, 0.6)), linseg(440, p3*0.2, 800, p3*0.2, 220, p3*0.2, 220, p3*0.2, 350, p3*0.2, 440) + + ; pitch/rms and time tracking + kcps, krms pitchamdf a1, 220, 800, 0, 0 + ktime timeinsts + + ; listen back to the pitch track output + a1 oscil krms, kcps + + ; insert the time, rms and frequency values as fast as the database will accept + kdone init 1 + kdone dbexec_k gidb, sprintfk("INSERT INTO analysis (time, rms, freq) VALUES (%f, %f, %f)", ktime, krms, kcps), 1 + + ; declick end + aout = a1 * linseg(1, p3*0.99, 1, p3*0.01, 0) + outs aout, aout +endin + + +instr db_play + + ; get all of the values + idata[][] dbarray gidb, "SELECT time, rms, freq FROM analysis" + + ; loop through + index = 0 + while (index < lenarray(idata)) do + + ; set the duration to be the time to the next row multiplied by an overlap + if (index == lenarray(idata) - 1) then + iduration = 1 + else + iduration = (idata[index+1][0] - idata[index][0]) * 3.2 + endif + + ; create the event accordingly + event_i "i", "oscillator", idata[index][0]*2, iduration, idata[index][1], idata[index][2] + index += 1 + od +endin + + +instr oscillator + + ; basic oscillator + iamp = p4 + ifreq = p5 + a1 oscil iamp * 0.5, ifreq + + ; envelope to try and take away the quantised type sound + aout = a1 * linseg(0, p3*0.45, 1, p3*0.1, 1, p3*0.45, 0) + outs aout, aout +endin + +</CsInstruments> +<CsScore> +i "start_example" 0 7 + + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/5-mysql-basic.csd b/examples/5-mysql-basic.csd new file mode 100644 index 0000000..bf8e857 --- /dev/null +++ b/examples/5-mysql-basic.csd @@ -0,0 +1,110 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 3 + + Run some queries on a MySQL database using just inbuilt functions and system tables + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + + +; connect: type, hostname or IP, database name, username, password +gidb dbconnect "mysql", "hostname", "databasename", "user", "password" + + +; print a random float from the database +instr execscalar + ires dbscalar gidb, "SELECT RAND()" + print ires +endin + + +; print the current_timestamp from the database +instr execscalarstr + Sres dbscalar gidb, "SELECT CONCAT(NOW(), '\n')" + prints Sres +endin + + +; print some float columns from the database activity statistics table +instr execarray + Sql = {{ + SELECT INDEX_LENGTH, AVG_ROW_LENGTH, TABLE_ROWS + FROM INFORMATION_SCHEMA.TABLES + LIMIT 10 + }} + ires[][] dbarray gidb, Sql + printarray ires +endin + + +; print some string columns from the database activity table +instr execarraystr + Sql = {{ + SELECT TABLE_NAME, TABLE_COMMENT, ROW_FORMAT + FROM INFORMATION_SCHEMA.TABLES + LIMIT 10 + }} + Sres[][] dbarray gidb, Sql + irow = 0 + + ; loop through as printarray does not support multidimensional string arrays + while (irow < lenarray(Sres)) do + icol = 0 + while (icol < 3) do + Sitem sprintf "%d, %d : %s\n", irow, icol, Sres[irow][icol] + prints Sitem + icol += 1 + od + irow += 1 + od + +endin + + +; print the current_timestamp from the database server twice per second, and print when the query has been executed +instr execscalar_k + ktrigger metro 2 + kdone, Sres dbscalar_k gidb, "SELECT CONCAT('now = ', NOW(), '\n')", ktrigger + if (kdone == 1) then + printks "%s", 0, Sres + endif +endin + + +; print 2x2 random floats from the database every second +instr execarray_k + ktrigger metro 1 + kdone, kres[][] dbarray_k gidb, "SELECT RAND(), RAND() UNION SELECT RAND(), RAND()", ktrigger + if (kdone == 1) then + printk2 kres[0][0] + printk2 kres[0][1] + printk2 kres[1][0] + printk2 kres[1][1] + endif + +endin + + +</CsInstruments> +<CsScore> + +i "execscalar" 0 1 +i "execscalarstr" 2 1 +i "execarray" 4 1 +i "execarraystr" 6 1 +i "execscalar_k" 7 5 +i "execarray_k" 12 5 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/6-mysql-randomnotes.csd b/examples/6-mysql-randomnotes.csd new file mode 100644 index 0000000..036b63a --- /dev/null +++ b/examples/6-mysql-randomnotes.csd @@ -0,0 +1,99 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 3 + + Insert some frequencies and durations to a MySQL database + then query/play random pairs + + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 +seed 0 + + +; connect: type, hostname or IP, database name, username, password +gidb dbconnect "mysql", "localhost", "databasename", "username", "password" + + +; initialise the example +instr start_example + + ; create tables if not existing/truncate if existing and fill with frequency and duration data + dbexec gidb, "CREATE TABLE IF NOT EXISTS frequencies (freq FLOAT)" + dbexec gidb, "TRUNCATE TABLE frequencies" + dbexec gidb, "CREATE TABLE IF NOT EXISTS durations (dur FLOAT)" + dbexec gidb, "TRUNCATE TABLE durations" + dbexec gidb, "INSERT INTO frequencies (freq) VALUES (277.2), (349.272), (415.24559), (523.35359), (391.96079)" + dbexec gidb, "INSERT INTO frequencies (freq) SELECT freq*2 FROM frequencies" + dbexec gidb, "INSERT INTO durations (dur) VALUES (0.1), (0.2), (0.4), (0.8), (1), (1.2)" + + ; run the sequencer instrument for 30 seconds + event_i "i", "sequence", 0, 30 +endin + + +; random sequencer instrument +instr sequence + + ; query to return random frequency/duration pair + Sql = {{ + SELECT f.freq, d.dur + FROM frequencies f + JOIN (SELECT dur FROM durations ORDER BY RAND() LIMIT 1) d + ORDER BY RAND() + LIMIT 1 + }} + + ; array needs to be initialised or schedkwhen complains + kevent[][] init 1, 2 + + ; query twice per second + ktrig metro 3 + + ; query on metro returning the frequency/duration pair + kdone, kevent[][] dbarray_k gidb, Sql, ktrig + + ; schedule the note + schedkwhen kdone, 0, 0, "osc", 0, kevent[0][1], kevent[0][0] + +endin + + +; play a note +instr osc + + ; pick a random waveform + ifn = int(random(1, 4)) + + ; set frequency and envelope + ifreq = p4 + kamp line 0.5, p3, 0 + + ; play it + a1 oscil kamp, ifreq, ifn + outs a1, a1 +endin + + + + +</CsInstruments> +<CsScore> +f1 0 16384 10 1 +f2 0 16384 10 1 0.5 0.3 0.25 0.2 0.167 0.14 0.125 .11 +f3 0 16384 10 1 0 0.3 0 0.2 0 0.14 0 .111 +f4 0 16384 10 1 1 1 1 0.7 0.5 0.3 0.1 + +; begin example +i "start_example" 0 1 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/7-violsqlite.csd b/examples/7-violsqlite.csd new file mode 100644 index 0000000..cabc753 --- /dev/null +++ b/examples/7-violsqlite.csd @@ -0,0 +1,172 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 7 + + A rudimentary pitch tracking concatenative resynthesizer as such + + This creates a sqlite database in memory, and then uses the opcode getpitches to scan through a + sound file in non-realtime, storing detected pitches and the relevant offset time of the pitch in + the database. The example file is a descending violin glissando. + + When done, the instrument "playmatches" is scheduled twice which plays an oscillator varying in pitch, + which is pitch tracked and the nearest frequency is found in the database, prompting the "segment" + instrument to be scheduled with the relevant offset. + + + 1. for 20s in which nearest matches are played only if the detected pitch has changed outside of a threshold + 2. as above but with matches played continuously + + The result is that the violin segments picked should moreorless match what the oscillator is doing. + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 + + +; create an in-memory sqlite database and create a table +gidb dbconnect "sqlite", ":memory:" +dbexec gidb, "CREATE TABLE pitches (time REAL, cps REAL)" + +; file of ascending piano pitches +gSfile = "sounds/violin.wav" +gifn ftgen 0, 0, 0, 1, gSfile, 0, 0, 0 + +; detect pitches and insert time/cps to database given a filename, in non-realtime +opcode getpitches, 0, S + Sfile xin + ktimek timeinstk + ktime timeinsts + klast init -1 + kcount init 0 + + ; run in the first single k-rate cycle + if (ktimek == 0) then + + ; get file length in k-cycles + ilen filelen Sfile + kcycles = ilen * kr +loop: + ; read file and track pitch + ain diskin2 Sfile, 1 + ;ain butterbp ain, 500, 250 + koct, kamp pitch ain, 0.01, 6, 12, 6, 12, 60 + + ; only take action if pitch has changed + kchanged changed2 koct + if (kchanged == 1) then + + ; only store if cps is reasonably different from the last value + kcps = cpsoct(koct) + + if (1==1) then + + ; insert to database: the dbexec_kb opcode is k-rate but blocking/synchronous. + ; simpler to use for the non-realtime operation as regular _k opcodes are threaded/asynchronous + ktime = kcount / kr + Squery sprintfk "INSERT INTO pitches (time, cps) VALUES (%f, %f)", ktime, kcps + dbexec_kb gidb, Squery + klast = kcps + endif + endif + loop_lt kcount, 1, kcycles, loop + + endif +endop + + + +; begin the example: find pitches and then schedule the next step +instr start_example + getpitches gSfile + schedkwhen 1, 0, 0, "playmatches", 0, 20, 0 + schedkwhen 1, 0, 0, "playmatches", 20, 20, 1 + turnoff + +endin + + + +; pitch follow a descending oscillator and attempt to find a similar cps in the database, then schedule segment accordingly +instr playmatches + kdone init 0 ; for when the select query is done + klast init 0 ; last pitch played, in order to avoid repeats + + + ; oscillator + k1 linseg 400, p3, 200 + ktime linseg 0.001, p3, 2 + k2 oscil k1, ktime + ain oscil 1, abs(k2)+550, 1 + + ; track it + koct, kamp pitch ain, 0.01, 5, 10, 6, 12 + + ; only take action when the tracked pitch has changed + kchanged changed2 koct + if (kchanged == 1) then + + ; (very roughly) get a near frequency match from the database + kcps = cpsoct(koct) + + ; fairly nasty looking query for obtaining the nearest value + SquerySource = {{ + SELECT time FROM ( + SELECT time, cps FROM ( + SELECT time, cps FROM pitches WHERE cps >= %f ORDER BY cps ASC LIMIT 1 + ) + UNION SELECT time, cps FROM ( + SELECT time, cps FROM pitches WHERE cps < %f ORDER BY cps DESC LIMIT 1 + ) + ) x ORDER BY ABS(cps - %f) ASC LIMIT 1 + }} + + Squery sprintfk SquerySource, kcps, kcps, kcps + kdone, kpos dbscalar_k gidb, Squery, kchanged + + ;schedule the notes + if (p4 == 1) then + schedkwhen kdone, 0, 0, "segment", 0, 0.2, kpos + else + if (kpos < klast*0.8 || kpos > klast*1.2) then + schedkwhen kdone, 0, 0, "segment", 0, 0.2, kpos + klast = kpos + endif + endif + endif + + + ; uncomment to hear the oscillator as well as pitch matched output + ;outs ain*0.01, ain*0.01 +endin + + + +; play part of the sound file given a skip time with a basic envelope +instr segment + il = ftlen(gifn) + isec = il/sr + ist = sr*p4 + icps = 1/isec + aphs phasor icps + andx = aphs * il + aout tablei andx+ist, gifn + kamp linseg 0, p3*0.3, 1, p3*0.4, 1, p3*0.3, 0 + outs aout*0.1*kamp, aout*0.1*kamp +endin + + +</CsInstruments> +<CsScore> +f1 0 16384 10 1 0 0.3 0 0.2 0 0.14 0 .111 ; square wave + +i"start_example" 0 1 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/examples/sounds/violin.wav b/examples/sounds/violin.wav Binary files differnew file mode 100644 index 0000000..c490e1c --- /dev/null +++ b/examples/sounds/violin.wav diff --git a/examples/toCheck/7-sqlite-pitchmatcher.csd b/examples/toCheck/7-sqlite-pitchmatcher.csd new file mode 100644 index 0000000..1d45234 --- /dev/null +++ b/examples/toCheck/7-sqlite-pitchmatcher.csd @@ -0,0 +1,140 @@ +<CsoundSynthesizer> +<CsOptions> +-odac +</CsOptions> +<CsInstruments> +/* + EXAMPLE 9 + + A rudimentary pitch tracking concatenative resynthesizer + + This creates a sqlite database in memory, and then uses the opcode getpitches to scan through a + sound file in non-realtime, storing detected pitches and the relevant offset time of the pitch in + the database. The example file is a series of ascending piano notes. + + When done, the instrument "playmatches" is scheduled. This plays a descending oscillator + which is pitch tracked and then a similar pitch is found from the database, prompting the "segment" + instrument to be scheduled with the relevant offset. + The result is that the piano notes/sound file segments picked should moreorless match what the + oscillator is doing. + +*/ + +sr = 44100 +kr = 4410 +nchnls = 2 +0dbfs = 1 + + +; create an in-memory sqlite database and create a table +gidb dbconnect "sqlite", ":memory:" +dbexec gidb, "CREATE TABLE pitches (time REAL, cps REAL)" + +; file of ascending piano pitches +gSfile = "sounds/piano.wav" + + +; detect pitches and insert time/cps to database given a filename, in non-realtime +opcode getpitches, 0, S + Sfile xin + ktimek timeinstk + ktime timeinsts + klast init -1 + kcount init 0 + + ; run in the first single k-rate cycle + if (ktimek == 0) then + + ; get file length in k-cycles + ilen filelen Sfile + kcycles = ilen * kr +loop: + ; read file and track pitch + ain diskin2 Sfile, 1 + koct, kamp pitch ain, 0.01, 7, 11, 6, 12, 10, 8 + + ; only take action if pitch has changed + kchanged changed2 koct + if (kchanged == 1) then + + ; only store if cps is reasonably different from the last value + kcps = cpsoct(koct) + if (kcps > klast*1.1 || kcps < klast*0.9) then + + ; insert to database: the dbexec_kb opcode is k-rate but blocking/synchronous. + ; simpler to use for the non-realtime operation as regular _k opcodes are threaded/asynchronous + ktime = kcount / kr + Squery sprintfk "INSERT INTO pitches (time, cps) VALUES (%f, %f)", ktime, kcps + dbexec_kb gidb, Squery + klast = kcps + endif + endif + loop_lt kcount, 1, kcycles, loop + + endif +endop + + + +; begin the example: find pitches and then schedule the next step +instr start_example + getpitches gSfile + schedkwhen 1, 0, 0, "playmatches", 0, 20 + turnoff + +endin + + + +; pitch follow a descending oscillator and attempt to find a similar cps in the database, then schedule segment accordingly +instr playmatches + kdone init 0 ; for when the select query is done + klast init 0 ; last pitch played, in order to avoid repeats + + ; descending oscillator + k1 linseg 2000, p3, 200 + ain oscil 1, k1, 1 + + ; track it + koct, kamp pitch ain, 0.01, 7, 11, 6, 12, 10, 8 + + ; only take action when the tracked pitch has changed + kchanged changed2 koct + if (kchanged == 1) then + + ; (very roughly) get a near frequency match from the database + kcps = cpsoct(koct) + Squery sprintfk "SELECT time FROM pitches WHERE cps >= %f LIMIT 1", kcps + kdone, kpos dbscalar_k gidb, Squery, kchanged + + ; don't repeat notes (try the schedkwhen outside of the if block for continuous play) + if (kpos != klast) then + schedkwhen kdone, 0, 0, "segment", 0, 0.2, kpos + klast = kpos + endif + endif + + ; uncomment to hear the oscillator as well as pitch matched output + ;outs ain*0.01, ain*0.01 +endin + + + +; play part of the sound file given a skip time with a basic fade in/out +instr segment + iskip = p4 + kamp linseg 0, p3*0.1, 1, p3*0.8, 1, p3*0.1, 0 + a1 diskin2 gSfile, 1, iskip + aout = a1*kamp * 0.1 + outs aout, aout +endin + + +</CsInstruments> +<CsScore> +f1 0 16384 10 1 0 0.3 0 0.2 0 0.14 0 .111 ; square wave + +i"start_example" 0 1 + +</CsScore> +</CsoundSynthesizer>
\ No newline at end of file diff --git a/include/connection.h b/include/connection.h new file mode 100644 index 0000000..e67ea27 --- /dev/null +++ b/include/connection.h @@ -0,0 +1,90 @@ +/* + connection.h + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#ifndef CONNECTION_H +#define CONNECTION_H +#include <plugin.h> +#include "logindata.h" + +#define EXEC 0 +#define SCALAR 1 +#define SCALARSTRING 2 +#define ARRAY 3 +#define ARRAYSTRING 4 + +#define POSTGRES 0 +#define SQLITE 1 +#define MYSQL 2 + +#ifdef BUILD_SQLITE +#include "sqlite.h" +#endif + +#ifdef BUILD_POSTGRES +#include "postgresql.h" +#endif + +#ifdef BUILD_MYSQL +#include "mysql.h" +#endif + + +// tried to do this with templates and inheritance, failed with various approaches + + + +struct ConnectionData { + void* mutex; +#ifdef BUILD_SQLITE + SqliteConnection* sqlite; +#endif +#ifdef BUILD_POSTGRES + PostgresConnection* postgres; +#endif +#ifdef BUILD_MYSQL + MySQLConnection* mysql; +#endif + int type; + bool open; + void Init(csnd::Csound* csound, LoginData* login); + void Close(csnd::Csound* csound); + void Exec(char* sql); + MYFLT Scalar(char* sql, int row, int col); + char* ScalarString(char* sql, int row=0, int col=0); + void ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array); + void ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array); +}; + +struct QueryData { + ConnectionData* connection; + char* sql; + int queryType; + int row; + int col; + ARRAYDAT* array; +}; + + + +ConnectionData* getConnection(csnd::Csound* csound, MYFLT handle); + +#endif /* CONNECTION_H */ + diff --git a/include/logindata.h b/include/logindata.h new file mode 100644 index 0000000..8a62e6b --- /dev/null +++ b/include/logindata.h @@ -0,0 +1,34 @@ +/* + logindata.h + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#ifndef LOGINDATA_H +#define LOGINDATA_H + +struct LoginData { + int dbType; + char* dbHost; + char* dbName; + char* dbUser; + char* dbPass; +}; + +#endif /* LOGINDATA_H */ + diff --git a/include/mysql.h b/include/mysql.h new file mode 100644 index 0000000..1abc6e3 --- /dev/null +++ b/include/mysql.h @@ -0,0 +1,48 @@ +/* + mysql.h + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#ifndef MYSQL_H +#define MYSQL_H + +#include <plugin.h> +#include <cppconn/driver.h> +#include <cppconn/resultset.h> +#include "connection.h" + +namespace mysql = sql; + +struct MySQLConnection { + mysql::Driver* driver; + mysql::Connection* conn; + void Init(csnd::Csound* csound, LoginData* login); + void Close(csnd::Csound* csound); + void Exec(char* sql); + mysql::ResultSet* Query(char *sql); + MYFLT Scalar(char* sql, int row, int col); + char* ScalarString(char* sql, int row, int col); + void ToArray(mysql::ResultSet* result, csnd::Csound* csound, ARRAYDAT* array, bool asString); + void ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array); + void ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array); +}; + + +#endif /* MYSQL_H */ + diff --git a/include/postgresql.h b/include/postgresql.h new file mode 100644 index 0000000..d7db5d9 --- /dev/null +++ b/include/postgresql.h @@ -0,0 +1,44 @@ +/* + postgresql.h + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#ifndef POSTGRESQL_H +#define POSTGRESQL_H + +#include <plugin.h> +#include <pqxx/pqxx> +#include "connection.h" + +struct PostgresConnection { + pqxx::connection* conn; + void Init(csnd::Csound* csound, LoginData* login); + void Close(csnd::Csound* csound); + void Exec(char* sql); + pqxx::result Query(char *sql); + MYFLT Scalar(char* sql, int row, int col); + char* ScalarString(char* sql, int row, int col); + void ToArray(pqxx::result result, csnd::Csound* csound, ARRAYDAT* array, bool asString); + void ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array); + void ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array); +}; + + +#endif /* POSTGRESQL_H */ + diff --git a/include/sqlite.h b/include/sqlite.h new file mode 100644 index 0000000..4e2b0c3 --- /dev/null +++ b/include/sqlite.h @@ -0,0 +1,44 @@ +/* + sqlite.h + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#ifndef XSQLITE3_H +#define XSQLITE3_H + +#include <plugin.h> +#include <sqlite3.h> +#include "connection.h" + +struct SqliteConnection { + sqlite3* conn; + void Init(csnd::Csound* csound, LoginData* login); + void Close(csnd::Csound* csound); + void Exec(char* sql); + sqlite3_stmt* Query(char *sql); + MYFLT Scalar(char* sql, int row, int col); + char* ScalarString(char* sql, int row, int col); + int RowCount(sqlite3_stmt* stmt); + void ToArray(sqlite3_stmt* result, csnd::Csound* csound, ARRAYDAT* array, bool asString); + void ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array); + void ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array); +}; + +#endif /* SQLITE3_H */ + diff --git a/src/connection.cpp b/src/connection.cpp new file mode 100644 index 0000000..5ac0aa1 --- /dev/null +++ b/src/connection.cpp @@ -0,0 +1,218 @@ +/* + connection.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include <plugin.h> +#include "connection.h" + + +const char* badDatabase = "database type not supported"; +const char* badConnection = "connection not open"; + +// tried to do this with templates, failed with various approaches +// hence the copious number of switches etc (horrible?). Due a revisit/refactor. + +void ConnectionData::Init(csnd::Csound* csound, LoginData* login) { + type = login->dbType; + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres = (PostgresConnection*) csound->malloc(sizeof(PostgresConnection)); + postgres->Init(csound, login); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite = (SqliteConnection*) csound->malloc(sizeof(SqliteConnection)); + sqlite->Init(csound, login); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql = (MySQLConnection*) csound->malloc(sizeof(MySQLConnection)); + mysql->Init(csound, login); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + open = true; +} + +void ConnectionData::Close(csnd::Csound* csound) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres->Close(csound); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite->Close(csound); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql->Close(csound); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + open = false; +} + +void ConnectionData::Exec(char* sql) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres->Exec(sql); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite->Exec(sql); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql->Exec(sql); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } +} + +MYFLT ConnectionData::Scalar(char* sql, int row=0, int col=0) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->Scalar(sql, row, col); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->Scalar(sql, row, col); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->Scalar(sql, row, col); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +char* ConnectionData::ScalarString(char* sql, int row=0, int col=0) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ScalarString(sql, row, col); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ScalarString(sql, row, col); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ScalarString(sql, row, col); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +void ConnectionData::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ArrayQuery(sql, csound, array); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ArrayQuery(sql, csound, array); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ArrayQuery(sql, csound, array); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +void ConnectionData::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ArrayQueryString(sql, csound, array); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ArrayQueryString(sql, csound, array); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ArrayQueryString(sql, csound, array); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} diff --git a/src/mysql.cpp b/src/mysql.cpp new file mode 100644 index 0000000..040670e --- /dev/null +++ b/src/mysql.cpp @@ -0,0 +1,145 @@ +/* + mysql.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include <plugin.h> +#include <iostream> +#include <cppconn/driver.h> +#include <cppconn/exception.h> +#include <cppconn/resultset.h> +#include <cppconn/statement.h> +#include "mysql_connection.h" +#include "connection.h" +#include "mysql.h" +namespace mysql = sql; + + +void MySQLConnection::Init(csnd::Csound* csound, LoginData* login) { + driver = get_driver_instance(); + char host[256]; + snprintf(host, 256, "tcp://%s:3306", login->dbHost); + + conn = driver->connect(host, login->dbUser, login->dbPass); + conn->setSchema(login->dbName); + + if (conn->isClosed()) { + throw std::runtime_error("connection not established"); + } +} + +void MySQLConnection::Close(csnd::Csound* csound) { + conn->close(); + delete conn; +} + +void MySQLConnection::Exec(char* sql) { + mysql::Statement* stmt = conn->createStatement(); + stmt->execute(sql); + delete stmt; +} + +mysql::ResultSet* MySQLConnection::Query(char* sql) { + mysql::Statement* stmt = conn->createStatement(); + mysql::ResultSet* result = stmt->executeQuery(sql); + delete stmt; + return result; +} + +MYFLT MySQLConnection::Scalar(char* sql, int row=0, int col=0) { + mysql::ResultSet* res = Query(sql); + mysql::ResultSetMetaData* meta = res->getMetaData(); + int colCount = meta->getColumnCount(); + if (col > colCount - 1) { + throw std::runtime_error("column number out of range"); + } + + res->next(); + MYFLT result = (MYFLT) res->getDouble(col + 1); + + delete res; + + return result; +} + +char* MySQLConnection::ScalarString(char* sql, int row=0, int col=0) { + mysql::ResultSet* res = Query(sql); + mysql::ResultSetMetaData* meta = res->getMetaData(); + + int colCount = meta->getColumnCount(); + if (col > colCount - 1) { + throw std::runtime_error("column number out of range"); + } + + int rowIndex = 0; + for (int rowIndex = 0; rowIndex <= row; rowIndex++) { + res->next(); + } + char* result = res->getString(col + 1).c_str(); + + delete res; + + return result; +} + + +void MySQLConnection::ToArray(mysql::ResultSet* result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + mysql::ResultSetMetaData* meta = result->getMetaData(); + int colNum = meta->getColumnCount(); + int rowNum = result->rowsCount(); + int totalResults = colNum * rowNum; + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = rowNum; + array->sizes[1] = colNum; + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int colIndex; + int index = 0; + + while (result->next()) { + colIndex = 0; + while (colIndex < colNum) { + if (asString) { + char* item = result->getString(colIndex + 1).c_str(); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = (MYFLT) result->getDouble(colIndex + 1); + } + colIndex++; + index++; + } + } + delete result; +} + +void MySQLConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void MySQLConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} diff --git a/src/opcodes.cpp b/src/opcodes.cpp new file mode 100644 index 0000000..3527f83 --- /dev/null +++ b/src/opcodes.cpp @@ -0,0 +1,749 @@ +/* + opcodes.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ +#include <atomic> +#include <plugin.h> +#include <iostream> +#include "connection.h" + +const char* dbname = "::dbconnection%d"; +const char* badHandle = "cannot obtain connection from handle"; + +#define LOCK(connection) csound->get_csound()->LockMutex(connection->mutex); +#define UNLOCK(connection) csound->get_csound()->UnlockMutex(connection->mutex); + +/* + * Obtain connection from global variables by handle + */ +ConnectionData* getConnection(csnd::Csound* csound, MYFLT handle) { + char buffer[32]; + snprintf(buffer, 32, dbname, (int)handle); + return (ConnectionData*) csound->query_global_variable(buffer); +} + + +/* + * Create connection in global variables returning handle + */ +MYFLT CreateHandle(csnd::Csound* csound, ConnectionData** connection) { + char buffer[32]; + int handle = 0; + snprintf(buffer, 32, dbname, handle); + while ((*connection = (ConnectionData*) csound->query_global_variable(buffer)) != NULL) { + snprintf(buffer, 32, dbname, ++handle); + } + csound->create_global_variable(buffer, sizeof(ConnectionData)); + *connection = (ConnectionData*) csound->query_global_variable(buffer); + + return FL(handle); +} + +/* + * Thread for k rate query handling + */ +class QueryThread : public csnd::Thread { + std::atomic_bool spinlock; + std::atomic_bool on; + ConnectionData* connection; + QueryData* queryData; + int sleepTime; + +public: + bool pending; // if there is a query waiting to be processed + char* charData; + int charSize; + char* error; + int status; + MYFLT flData; + bool done; + int ident; + QueryThread(csnd::Csound *csound, ConnectionData* connection, QueryData* queryData) : + Thread(csound), + done(false), + queryData(queryData), + charSize(1), + status(0), + spinlock(false), + pending(false), + on(true), + connection(connection), + sleepTime(10) {}; + + + uintptr_t run() { + while(on) { + lock(); + if (pending) { + LOCK(connection); + try { + switch (queryData->queryType) { + case SCALARSTRING: { + std::string resultString = connection->ScalarString(queryData->sql, queryData->row, queryData->col); + if (charData != NULL) { + csound->free(charData); + } + charData = csound->strdup(resultString.c_str()); + charSize = resultString.length() + 1; + } + break; + case SCALAR: { + flData = connection->Scalar(queryData->sql, queryData->row, queryData->col); + } + break; + case EXEC: { + connection->Exec(queryData->sql); + + } + break; + case ARRAY: { + connection->ArrayQuery(queryData->sql, csound, queryData->array); + } + break; + case ARRAYSTRING: { + connection->ArrayQueryString(queryData->sql, csound, queryData->array); + } + break; + } + status = 0; + done = true; + pending = false; + } catch (const std::exception &e) { + status = 1; + done = true; + pending = false; + error = csound->strdup(e.what()); + } + UNLOCK(connection); + } + unlock(); + csound->sleep(sleepTime); + } + return 0; + } + + void do_query(char* sql, int row=0, int col=0) { + if (queryData->sql != NULL) { + //csound->free(queryData->sql); + } + + queryData->sql = sql; + queryData->row = row; + queryData->col = col; + pending = true; + + } + + void lock() { + while (spinlock == true) { + csound->sleep(sleepTime); // have as one k cycle ?? // was 100 + } + spinlock = true; + } + + void unlock() { + spinlock = false; + } + + void stop() { + on = false; + } +}; + + + + +struct dbconnect_full : csnd::Plugin<1, 5> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "SSSSS"; + ConnectionData* connection; + + int init() { + csound->plugin_deinit(this); + outargs[0] = CreateHandle(csound, &connection); + connection->mutex = csound->get_csound()->Create_Mutex(0); + + STRINGDAT &dbType = inargs.str_data(0); + STRINGDAT &dbHost = inargs.str_data(1); + STRINGDAT &dbName = inargs.str_data(2); + STRINGDAT &dbUser = inargs.str_data(3); + STRINGDAT &dbPass = inargs.str_data(4); + LoginData* login = (LoginData*) csound->malloc(sizeof(LoginData)); + + try { + if (!strcmp(dbType.data, "postgresql")) { + login->dbType = POSTGRES; + } else if (!strcmp(dbType.data, "mysql")) { + login->dbType = MYSQL; + } else { + return csound->init_error("database type not supported"); + } + login->dbHost = dbHost.data; + login->dbName = dbName.data; + login->dbUser = dbUser.data; + login->dbPass = dbPass.data; + + connection->Init(csound, login); + + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + return OK; + } + + int deinit() { + bool error; + LOCK(connection); + try { + connection->Close(csound); + } catch (const std::exception &e) { + error = true; + } + UNLOCK(connection); + csound->get_csound()->DestroyMutex(connection->mutex); + return (error)? NOTOK: OK; + } +}; + +struct dbconnect_short : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "SS"; + ConnectionData* connection; + + int init() { + csound->plugin_deinit(this); + outargs[0] = CreateHandle(csound, &connection); + connection->mutex = csound->get_csound()->Create_Mutex(0); + + STRINGDAT &dbType = inargs.str_data(0); + STRINGDAT &dbName = inargs.str_data(1); + + LoginData* login = (LoginData*) csound->malloc(sizeof(LoginData)); + try { + if (strcmp(dbType.data, "sqlite")) { + return csound->init_error("database type not supported"); + } + login->dbType = SQLITE; + login->dbName = dbName.data; + + connection->Init(csound, login); + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + return OK; + } + + int deinit() { + bool error; + LOCK(connection); + try { + connection->Close(csound); + } catch (const std::exception &e) { + error = true; + } + UNLOCK(connection);; + csound->get_csound()->DestroyMutex(connection->mutex); + return (error)? NOTOK: OK; + } +}; + + + + + +/* + * Base struct for threaded k-rate queries + */ +template <std::size_t N, std::size_t M> struct DBPluginBaseK : csnd::Plugin<N, M> { + using csnd::Plugin<N, M>::inargs; + using csnd::Plugin<N, M>::outargs; + using csnd::Plugin<N, M>::csound; + ConnectionData* connection; + QueryThread query; + bool singleRun; + bool singleComplete; + + int setup(int queryType, int row=0, int col=0, ARRAYDAT* array=NULL) { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + csound->plugin_deinit(this); + + QueryData* queryData = (QueryData*) csound->malloc(sizeof(QueryData)); + queryData->queryType = queryType; + + queryData->row = row; + queryData->col = col; + + if (array) { + queryData->array = array; + } + + if (inargs[2] == FL(-1)) { + singleRun = true; + } + + csnd::constr(&query, csound, connection, queryData); + return OK; + } + + int deinit() { + query.stop(); + query.join(); + csnd::destr(&query); + return OK; + } + + MYFLT kcycle(int row=0, int col=0) { + outargs[0] = FL(0); + if (singleComplete && singleRun) { + return OK; + } + + if (UNLIKELY(query.done)) { + if (query.status) { + return csound->perf_error(query.error, this); + } + if (singleRun) { + singleComplete = true; + } + query.lock(); + query.done = false; + query.unlock(); + outargs[0] = FL(1); + } + + if (UNLIKELY(!query.pending && + (inargs[2] == FL(1) || + singleRun) + )) { + query.lock(); + query.do_query(csound->strdup(inargs.str_data(1).data), row, col); + query.unlock(); + } + + return OK; + } + +}; + + +// threaded k rate opcodes + +struct dbexec_k : DBPluginBaseK<1, 3> { + static constexpr char const *otypes = "k"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(EXEC); + } + + int kperf() { + return kcycle(); + } +}; + + + +struct dbscalar_k : DBPluginBaseK<2, 5> { + static constexpr char const *otypes = "kk"; + static constexpr char const *itypes = "iSkOO"; + + int init() { + return setup(SCALAR, (int)inargs[3], (int)inargs[4]); + } + + int kperf() { + int response = kcycle((int)inargs[3], (int)inargs[4]); + outargs[1] = query.flData; + return response; + } +}; + + +struct dbscalarstr_k : DBPluginBaseK<2, 5> { + static constexpr char const *otypes = "kS"; + static constexpr char const *itypes = "iSkOO"; + + int init() { + return setup(SCALARSTRING, (int)inargs[3], (int)inargs[4]); + } + + int kperf() { + int response = kcycle((int)inargs[3], (int)inargs[4]); + STRINGDAT &result = outargs.str_data(1); + result.size = query.charSize; + result.data = query.charData; + return response; + } +}; + + +struct dbarray_k : DBPluginBaseK<2, 3> { + static constexpr char const *otypes = "kk[][]"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(ARRAY, NULL, NULL, (ARRAYDAT*) outargs(1)); + } + + int kperf() { + return kcycle(); + } + +}; + + +struct dbarraystr_k : DBPluginBaseK<2, 3> { + static constexpr char const *otypes = "kS[][]"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(ARRAYSTRING, NULL, NULL, (ARRAYDAT*) outargs(1)); + } + + int kperf() { + return kcycle(); + } +}; + + + + +struct dbexec : csnd::InPlug<2> { + static constexpr char const *otypes = ""; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, args[0]))) { + return csound->init_error(badHandle); + } + + STRINGDAT &sql = args.str_data(1); + LOCK(connection); + try { + connection->Exec(sql.data); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + UNLOCK(connection); + + } +}; + + + +struct dbscalar : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "iSoo"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + try { + LOCK(connection); + outargs[0] = connection->Scalar(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + +struct dbscalarstr : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "S"; + static constexpr char const *itypes = "iSoo"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + STRINGDAT &result = outargs.str_data(0); + try { + LOCK(connection); + std::string resultString = connection->ScalarString(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + result.size = resultString.length() + 1; + result.data = csound->strdup(resultString.c_str()); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + + +struct dbarray : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "i[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQuery(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + +struct dbarraystr : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "S[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQueryString(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + + +// k rate blocking opcodes + + +struct dbexec_kb : csnd::InPlug<2> { + static constexpr char const *otypes = ""; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, args[0]))) { + return csound->init_error(badHandle); + } + return OK; + + } + + int kperf() { + STRINGDAT &sql = args.str_data(1); + try { + LOCK(connection); + connection->Exec(sql.data); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + +struct dbscalar_kb : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "k"; + static constexpr char const *itypes = "iSOO"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + try { + LOCK(connection); + outargs[0] = connection->Scalar(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + +struct dbscalarstr_kb : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "S"; + static constexpr char const *itypes = "iSOO"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + + STRINGDAT &sql = inargs.str_data(1); + STRINGDAT &result = outargs.str_data(0); + try { + LOCK(connection); + std::string resultString = connection->ScalarString(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + result.size = resultString.length() + 1; + result.data = csound->strdup(resultString.c_str()); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + + +struct dbarray_kb : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "k[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQuery(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + +struct dbarraystr_kb : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "S[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQueryString(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + + + + +#include <modload.h> + +void csnd::on_load(csnd::Csound *csound) { + + csnd::plugin<dbconnect_full>(csound, "dbconnect.f", csnd::thread::i); + csnd::plugin<dbconnect_short>(csound, "dbconnect.s", csnd::thread::i); + + csnd::plugin<dbexec>(csound, "dbexec", csnd::thread::i); + csnd::plugin<dbscalar>(csound, "dbscalar", csnd::thread::i); + csnd::plugin<dbscalarstr>(csound, "dbscalar.S", csnd::thread::i); + csnd::plugin<dbarray>(csound, "dbarray", csnd::thread::i); + csnd::plugin<dbarraystr>(csound, "dbarray.S", csnd::thread::i); + + csnd::plugin<dbexec_k>(csound, "dbexec_k", csnd::thread::ik); + csnd::plugin<dbscalar_k>(csound, "dbscalar_k", csnd::thread::ik); + csnd::plugin<dbscalarstr_k>(csound, "dbscalar_k.S", csnd::thread::ik); + csnd::plugin<dbarray_k>(csound, "dbarray_k", csnd::thread::ik); + csnd::plugin<dbarraystr_k>(csound, "dbarray_k.S", csnd::thread::ik); + + csnd::plugin<dbexec_kb>(csound, "dbexec_kb", csnd::thread::ik); + csnd::plugin<dbscalar_kb>(csound, "dbscalar_kb", csnd::thread::ik); + csnd::plugin<dbscalarstr_kb>(csound, "dbscalar_kb.S", csnd::thread::ik); + csnd::plugin<dbarray_kb>(csound, "dbarray_kb", csnd::thread::ik); + csnd::plugin<dbarraystr_kb>(csound, "dbarray_kb.S", csnd::thread::ik); + +}
\ No newline at end of file diff --git a/src/postgresql.cpp b/src/postgresql.cpp new file mode 100644 index 0000000..53cd102 --- /dev/null +++ b/src/postgresql.cpp @@ -0,0 +1,135 @@ +/* + postgresql.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include <plugin.h> +#include <iostream> +#include <pqxx/pqxx> +#include "connection.h" +#include "postgresql.h" + + + +void PostgresConnection::Init(csnd::Csound* csound, LoginData* login) { + //conn = (pqxx::connection*) csound->malloc(sizeof(pqxx::connection)); + char connectionString[256]; + + snprintf(connectionString, 256, + "dbname=%s user=%s password=%s hostaddr=%s", + login->dbName, login->dbUser, login->dbPass, login->dbHost + ); + + conn = new pqxx::connection(connectionString); + + // ignore notices + std::auto_ptr<pqxx::noticer> np(new(pqxx::nonnoticer)); + conn->set_noticer(np); + + if (!conn->is_open()) { + throw std::runtime_error("Connection not open"); + } +} + +void PostgresConnection::Close(csnd::Csound* csound) { + if (conn->is_open()) { + conn->disconnect(); + } + delete conn; +} + +void PostgresConnection::Exec(char* sql) { + pqxx::nontransaction nt(*conn); + nt.exec(sql); +} + +pqxx::result PostgresConnection::Query(char* sql) { + pqxx::nontransaction nt(*conn); + pqxx::result result(nt.exec(sql)); + return result; +} + +MYFLT PostgresConnection::Scalar(char* sql, int row=0, int col=0) { + pqxx::result result = Query(sql); + + // checks as libpqxx not throwing if this happens + if (row > result.size() - 1) { + throw std::runtime_error("row number out of range"); + } + if (col > result[row].size() -1) { + throw std::runtime_error("column number out of range"); + } + + return result[row][col].as<MYFLT>(); +} + +char* PostgresConnection::ScalarString(char* sql, int row=0, int col=0) { + pqxx::result result = Query(sql); + + // checks as libpqxx not throwing if this happens + if (row > result.size() - 1) { + throw std::runtime_error("row number out of range"); + } + if (col > result[row].size() -1) { + throw std::runtime_error("column number out of range"); + } + + return result[row][col].c_str(); + +} + + +void PostgresConnection::ToArray(pqxx::result result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + int totalResults = result.size() * result[0].size(); + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = result.size(); + array->sizes[1] = result[0].size(); + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int index = 0; + for (int rowNum = 0; rowNum < result.size(); ++rowNum) { + const pqxx::result::tuple row = result[rowNum]; + for (int colNum = 0; colNum < row.size(); ++colNum) { + const pqxx::result::field field = row[colNum]; + if (asString) { + char* item = field.c_str(); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = field.as<MYFLT>(); + } + index++; + } + } +} + +void PostgresConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void PostgresConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} diff --git a/src/sqlite3.cpp b/src/sqlite3.cpp new file mode 100644 index 0000000..824e165 --- /dev/null +++ b/src/sqlite3.cpp @@ -0,0 +1,159 @@ +/* + sqlite3.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include <plugin.h> +#include <iostream> +#include <sqlite3.h> +#include "connection.h" +#include "sqlite.h" + + +void SqliteConnection::Init(csnd::Csound* csound, LoginData* login) { + int result = sqlite3_open(login->dbName, &conn); + std::cout << "Y"; + if (result) { + throw std::runtime_error("connection not established"); + } +} + +void SqliteConnection::Close(csnd::Csound* csound) { + sqlite3_close(conn); +} + +void SqliteConnection::Exec(char* sql) { + sqlite3_stmt* stmt = Query(sql); + int rc = sqlite3_step(stmt); + rc = sqlite3_finalize(stmt); +} + +sqlite3_stmt* SqliteConnection::Query(char* sql) { + sqlite3_stmt* stmt = NULL; + int rc = sqlite3_prepare_v2(conn, sql, -1, &stmt, NULL); + if (rc != SQLITE_OK) { + throw std::runtime_error(sqlite3_errmsg(conn)); + } + return stmt; +} + +MYFLT SqliteConnection::Scalar(char* sql, int row=0, int col=0) { + sqlite3_stmt *stmt = Query(sql); + int colCount = sqlite3_column_count(stmt); + int rc = sqlite3_step(stmt); + int rowIndex = 0; + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + if (rowIndex == row) { + + if (col > colCount -1) { + rc = sqlite3_finalize(stmt); + throw std::runtime_error("column number out of range"); + } + MYFLT result = (MYFLT) sqlite3_column_double(stmt, col); + rc = sqlite3_finalize(stmt); + return result; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); + throw std::runtime_error("no result"); +} + +char* SqliteConnection::ScalarString(char* sql, int row=0, int col=0) { + sqlite3_stmt *stmt = Query(sql); + int colCount = sqlite3_column_count(stmt); + int rc = sqlite3_step(stmt); + int rowIndex = 0; + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + if (rowIndex == row) { + + if (col > colCount -1) { + rc = sqlite3_finalize(stmt); + throw std::runtime_error("column number out of range"); + } + char* result = sqlite3_column_text(stmt, col); + rc = sqlite3_finalize(stmt); + return result; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); + throw std::runtime_error("no result"); + +} + +int SqliteConnection::RowCount(sqlite3_stmt* stmt) { + int rowCount = 0; + int rc = sqlite3_step(stmt); + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + rc = sqlite3_step(stmt); + rowCount ++; + } + rc = sqlite3_reset(stmt); + return rowCount; +} + +void SqliteConnection::ToArray(sqlite3_stmt* stmt, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + int colNum = sqlite3_column_count(stmt); + int rowNum = RowCount(stmt); + int totalResults = colNum * rowNum; + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = rowNum; + array->sizes[1] = colNum; + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int colIndex; + int rowIndex; + int index = 0; + int rc = sqlite3_step(stmt); + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + colIndex = 0; + while (colIndex < colNum) { + if (asString) { + char* item = sqlite3_column_text(stmt, colIndex); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = (MYFLT) sqlite3_column_double(stmt, colIndex); + } + colIndex ++; + index++; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); +} + +void SqliteConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void SqliteConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} |