Provide OData-Services for your SQLite File-Database by copy&paste – Part 1

Me and my colleguesWerner Schwarz and Patrick Wenger we like SAPUI5 and OpenUI5 a lot. I think it’s a great, easy-to-use technology and with SAP Web IDE improving continuously, full WYSIWYG-capabillities are getting closer and closer. I’d like to use OpenUI5 in a lot of private projects in my spare time.

However, in my optinion SAPUI5 is only fun when using it in combination with OData and corresponding OData-Services (especially when working with SAP Web IDE). However, how would you create an OData-Service in “normal/private” live? Only few of us have a SAP Gateway on their night table….

I investigated quite some time and found several approaches. I think all of them are either only half-baked (like every approach using PHP-based OData Producer Library OData Producer Library for PHP – Home, as it only provides Read-Support and you have to generate the files for every database you want to provide a service for) or way too work-intensive to use for rapid prototyping (like using JPA for a specific database schema: xml – How to expose a MySQL database as OData – Stack Overflow).

We wanted something very simple and generic: We wanted to create a generic tool enabling us to provide OData-Services for ANY SQLite database only by copy&paste the corresponding files to a default PHP web hosting – without any special software-systems, further programming or file-generating.

Therefor we (well, mostly Werner) created the php-sqlite-odata library. Check out Werner’s GitHub Repository: slup/php-sqlite-odata · GitHub

By copying these files and providing a few configuration information in the Config.class.php-File you can easily provide full OData CRUD-Services for any SQLite-Database. You can also add these Services as Destinations in the SAP HANA(Trial) Cockpit and using them right away in the SAP Web IDE.

We’re already covering a lot of the OData standard llike:

• Create
• Read
• Update
• Delete
• Metadata
• count
• inline-count

But as you can see in the documented limitations sections, not the  full OData-Standard is covered yet. Next steps ahead are:

• BLOB-Datatype
• Filtering
• Navigation Links

Also, no security aspects like SQL Injection etc. has been covered yet. So if you want to test it or (even better) improve it, please fork Werner on GitHub 🙂

Any comment or contribution is very welcome!

In the next part of this Blog serie I’ll provide a step-by-step tutorial using this library in combination with SAP Web IDE,

PS: I’m using phpSQLiteAdmin download | SourceForge.net for creating and maintaining my SQLite databases. This little PHP-File based tool provides phpMyAdmin-like functionality for SQLite databases.