kitsunetech-com / velox-server

Platform-neutral database communication framework

Velox MVA Framework

Velox is intended as a streamlined client/server data retrieval and communication platform for websites and progressive web apps. While designed mainly for a LAMP backend, Velox also wraps SQL Server and ODBC connections into a single platform-agnostic class structure that eliminates the need to juggle syntax when using multiple data sources. Velox is comprised of both front end and back end components, designed to communicate with each other over AJAX, but decoupled in such a way that each can be used independently if necessary. Used as a whole, Velox simplifies development by implementing a CRUD interface on the front end and automating the data flow up to and back from the back-end database. The client-side component communicates with the server-side component by way of an API endpoint that parses the request and dynamically includes the appropriate query definition file, which can be set up using the provided template with only the sample SQL replaced. This allows the back-end developer to work almost entirely in SQL and build the necessary datasets to pass to the front-end with minimal effort. This repository contains the server-side component of this framework.

Requirements

The eventual goal is to make this framework as portable and platform-agnostic as possible. At this early stage in development, however, the focus is currently on a LAMP server architecture. The particular requirements for the server-side component are as follows:

• PHP 8.0.2+, with Composer 2.0+
  • One or more of the following extensions, depending on the database engine to be used:
    • MySQL / MariaDB: either mysqli or pdo_mysql
    • Microsoft SQL Server: either sqlsrv or pdo_sqlsrv (note: either of these require the Microsoft ODBC Driver for SQL Server.)
    • ODBC: either odbc or pdo_odbc, along with the necessary drivers for the desired connection
  • The xmlwriter extension, if the server-side component is to be used without the client-side component and XML output is needed
• A web server that supports PHP server-side scripting (Apache 2.4+ is specifically supported, but NGINX, IIS, and others may work as well)

Note for Developers and Potential Contributors

This framework is still in development, and is not at present suitable for integration into any serious project. When ready, it will be released under a permissive open-source license (which specific license is still under consideration), without cost. Because this is being developed for common use, any feature requests are welcome and will be considered on their merits. Feel free to drop any such requests in Issues or Discussions.

Disclaimer

As mentioned in the note above, this is under active development, and as such there is absolutely no warranty of fitness of use. If you use any part of this framework prior to official release, you do so at your own risk. Anyone who contributes code to this project does so without assuming any liability for damages that might result from its use prior to official release.

velox-server's Issues

This is causing an error: Needs to be initialized before being referenced

https://github.com/KitsuneTech-com/Velox/blob/4f837457f2e02e8d9fe6e39b4c4eac69066d42ce/php/Structures/Model.php#L97

Add support for nested queries

Allow for subsequent queries to reference prior queries by nesting them within the JSON.
This will need an adjustment of the Diff structure and API.

Add grouping and HAVING filter to Model and API

Grouping capabilities would be a handy feature to add to Velox. Considerations for this:

• Should this be handled by the database, or should Velox emulate this on a complete result set? Grouping can slow down query execution on the database, so it's possible Velox can speed this up by handling the grouping after the fact.
• If Velox handles the grouping, then the procedure needs to iterate through the Model and (probably) reorganize the data into an array (result set) of arrays (groups) of objects (rows).
• The API syntax needs to account for GROUP BY and HAVING clauses. The distinguishing factor here is that WHERE happens before grouping, and HAVING happens afterwards.

Add LIMIT clause to API calls

This will apply to select, likely as a sibling to the where property.

Separate client-side code into separate repo

Although the idea of Velox is to bridge the gap between the client and the database, it'll make more sense to separate the client-side and server-side code bases. Velox uses a JSON-based query protocol that both client-side and server-side components will support natively, but these can work independently of each other, and so there's no particular reason to keep both in the same repository.

Prior to first official version release, client-side code will be removed and added to a new Velox-Client repo, while this repo will be renamed Velox-Server and will hold the remaining server-side code.

Add support for Model-less single-result API calls

Not everything needs a full result set. For things that return a single value, the API should be able to process this without bothering with Model generation - just a single procedure.

Insert optimization for StatementSet

Currently looking into insert optimization. Currently, StatementSet inserts are done with batched PreparedStatements, but research indicates that using multi-row inserts can improve performance by 10x or better, up to a point (somewhere between 20-40 rows per statement). Might be able to build and batch dynamically to take advantage of this.

Originally posted by @skylardarkfox in #16 (comment)

Add pivot functionality to Model

Since this sort of thing is cumbersome and limited in SQL (impractical when the number of rows to be pivoted is unknown), this would be a handy feature to add to Model, which can use array logic to transpose the result rows into columns.

Rework Transaction for better sequential operation

Current behavior: the same parameters are passed to every procedure added to the Transaction

Preferred behavior: pass the parameters to only the first procedure; allow parameters of subsequent procedures to be defined by interstitial code with access to the prior procedure's result set and last affected ids

Add PhpDoc comments

Prior to first official version release, add PHPDoc comments to Velox PHP classes for better IDE support and ease of documentation.