Skip to Content
Author's profile photo Former Member

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 | for creating and maintaining my SQLite databases. This little PHP-File based tool provides phpMyAdmin-like functionality for SQLite databases.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Virinchy Panangipalli
      Virinchy Panangipalli

      Interesting one , I shall try this one to weekend.

      EDIT: What is the version of OData service that would be generated by Default?



      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Virinchy

      When downloading the sources from github, a running example for the file database "ODataTest.sqlite" is ready to use (just make sure you'll create the correct sub dir "ODataTest".

      Afterwards you can use the example as well as the test client.

      Looking forward to hearing about your trial results

      Author's profile photo Former Member
      Former Member

      We use the OData v2 spec, because we really only want to implement the basic OData feature set.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Werner implemented Navigation Properties last night, ready on GitHub.....