Seeking Advice on a new project - PHP or WE?

Targets Web
1

Forgive my somewhat lengthy message, but I’d like to make the right decision from the outset to avoid the pain of reversing it later. Here’s the story:

I’ve developed in PHP for many years (Since 2000 - 2001ish at least). I’m very comfortable with the language, the paradigms it uses, the environments it runs in, etc. I’m a competent Linux admin, and handle taking care of many servers that perform various tasks using a typical LAMP stack. Whenever I approach a new web-based project, I’ve always used PHP in the past.

Today, however, for the first time I’m considering using WE for my latest project.

I’ve developed in Xojo since it was known as RealBasic Version 4, and have made my living for the last several years off of desktop apps I’ve written in Xojo and its predecessors. I’m very comfortable writing for the desktop in Xojo, and understand all the nuances of the language and framework, and know what not to do etc.

Here’s my conundrum: One of my biggest products on the desktop uses built-in SQLiteDatabase with encryption to store / secure customer data. This has always worked well for the desktop, and I’ve never (to my knowledge) had users lose their data. I like the compactness and self-contained nature of sqlite databases, and love how easy they are to work with and use.

My upcoming version will feature some “cloud services” that will store and synchronize customer data on a server setup I’ll need to build. My first inclination was to set up a LAMP stack on our in-house servers or perhaps on an Amazon EC2 and write some web services in php that will interact directly with the desktop over TCP. But I’d really like to leave the customer data encrypted, and the typical LAMP setup does not do this well. I can secure access to the MySQL instance, but the data is not encrypted on disk. Further, the SQL spec is implemented differently between SQLite and MySQL (particularly in schema-altering statements) so keeping the structure and data in the client side database in sync with the server side will rely on some sql translation. I see that as a potential place for bugs, and want to keep things as simple as possible.

I’ve explored the possibility of opening the native SQLite databases from my desktop app from within PHP, but that appears to be not-very-possible due to the selection of encryption algorithm with the SEE extension Xojo uses from the SQLite author.

So, I’m considering building the server component of this whole thing in pure Xojo. I’d handle all the sockets, data transfer and communication in Xojo, and likely not even bother installing Apache, php, or mysql on the server at all. The typical use case for this will be to keep data from client A in sync with client B (and vice-versa) in near real time, so when a record is updated in B it is propagated through the web server to A as well. I also need to support offline clients that only connect occasionally and receive updates in groups.

The point of my question here is not to ask how to handle the synchronization of data (that’s a nut I’ve already cracked… see here: http://www.kimballlarsen.com/2008/09/23/homegrown-client-server-data-sharing/) - my point is to find out if WE is mature enough to handle this yet? I’ll have around 2000 users, with several hundred active at any given time. Typical interactions will be of the form that the client publishes an update, or a client requests updates from the server. I’ll do everything as client pull requests, not pushing anything from the server. A typical client may poll the server for updates every 10 seconds or so. If I have 200 active clients, this translates to an average of 200 x 6 = 1200 requests per minute or so, or around 20 requests / second. Most requests will not incur heavy loads, but will require the server process to connect to the requesting client’s database file (stored on the server), perform a query, collect the results, wrap them in an XML document, encrypt that document, then send it back down to the client. Occasionally I’ll have a client that will need to request a several-megabytes-large (say, 3-5 mb) file that will need to be send down to the client. The server MUST be able to handle other requests while this is happening.

I’m comfortable writing server / client stuff, and happy to multithread the snot out of everything, such that each incoming request is handled by its own thread.

I’ll likely not write any web pages or UI for this app - its sole interface will be the web services themselves (i.e., the socket event handlers).

So, enough with the background, here are the questions:

  1. What “gotchas” has anyone found with WE when trying to do something larger scale like this?
  2. Is WE an appropriate tool for this in the first place?
  3. How stable is WE? Memory leaks?
  4. Should I try to do this deployed as several fastcgi instances, or should I go for the monolithic self-contained deploy?
  5. Anything else I should consider before moving forward?

My users are world-wide, so this needs to be available 24-7. I can’t put up with needing to restart a server instance or software on that server to keep it running happily.

I’m currently leaning towards doing this in a LAMP stack. Can you convince me to go with Xojo instead?

2

If you’re not going to have a UI, then why use WE at all? Just use a console app. WE is basically a console app with a UI framework bolted on. I would have a daemonized console app listen for connections, then spawn off a helper app for each connection/task.

3

I could possibly see the usefulness of writing a UI that is used for back-office tasks such as monitoring the server, etc. So while I’m not planning a UI yet, it is possible I’d like to add one later. I think starting with a WE app in the first place will make this simpler down the road.

Can anyone speak to how much extra overhead is involved with a WE app vs a console app?

4

Hi Kimball have you decided yet? PHP or WE?

5

HI @Philip Fu - My requirements centered around being able to access encrypted SQLite databases on the server side. PHP cannot do this natively, but I am still uncomfortable trusting WE to the core of my business. I found that CubeSQL Server solved my problem: It is an RDBMS that uses Sqlite for the database storage, and it can natively use encrypted databases created in other Xojo apps. As a bonus, it comes with a set of PHP classes that leverage sockets to talk to the server. Also, the devs of CubeSQL are VERY responsive and helpful. I reported a bug and they had a release which fixed it within 24 hours.

So, I’m using PHP to write all my webservices, and CubeSQL to store all my data. So far it is working out well (though I am still in development - public release across our product line is going to happen Q1 2014, hopefully).

The other nice thing about this approach is that if I do decide to switch to a WE project for the webservices, CubeSQL can plug into that as well… so down the road when deployment on WE becomes easier / standardized, I can swap out PHP if I want.

6

CubeSQL is fantastic.

7

I think choosing something fast, reliable and proven like php is a wise choice.

I’ve often thought about purchasing and using CubeSQL myself but so far I’ve chosen not to because of two reasons :

Maybe I am incorrect about these two issues and if so someone please correct me.

  1. Every time you want to scale or setup a different business (using another copy of the db) with another VPS you need to make a new purchase of CubeSQL. For me wanting the unlimited connections license that would mean each new droplet (VPS) I’d set up would cost me another $600.00 to run CubeSQL on it. All of a sudden it’s not a smart financial situation.

  2. I like the option of being able to change server operating systems without an additional cost.
    If I were to purchase Linux licenses for VPS instances then later switched to my own mac running OS X server I don’t know if I’d be able to switch without repurchasing new CubeSQL licenses for OS X. That wouldn’t be cheap.

Those are my two reasons I didn’t purchase CubeSQL; it’s too costly (for me) and it doesn’t make good sense for my needs.

8

Thank you Philip. From an experienced developer, this is reassuring. I’ve always wondered what was the advantage of this type of product, especially since the introduction of WAL mode. Primarily to access the database simultaneously from WE and other services/apps?

9

The advantage for us is primarily in management. With CubeSQL you can connect remote applications to a central repository of databases (which can be encrypted same as Xojo). This is useful in WE scenarios where you want a database server. I don’t care for each app having it’s own SQLite database littering around in different places on the filesystem.

10

ok, thank you Phillip. It is easy to switch to CubeSQL? (from a WE app with Sqlite/Wal mode)

11

Yes, several ways to migrate to it.

12

Biggest hurdle will be you’ll need to change your connection stuff to use CubeSQL vs SQLite (despite both being SQLite).

13

ok, thank you Phillip!

14

On the other hand… If you use WE/SQLite and need outside access to your database, you can provide it very simply with a special URL. You could provide a low-level SQL select/execute API or a higher level app-specific API.

My only hesitation with CubeSQL, which is what led me to develop Studio Stable Database a few years ago, is the use of a database plugin. I’m a fan of more transparency with middleware.

15

[quote=48955:@Brad Hutchings]On the other hand… If you use WE/SQLite and need outside access to your database, you can provide it very simply with a special URL. You could provide a low-level SQL select/execute API or a higher level app-specific API.

[/quote]

yes, very interesting. I have not yet had time to take care of that, but I’ll have to look at this handleSpecialUrl/threads/Sqlite management. Thank you Brad!