I haven’t done ANYTHING with networking yet so I would appreciate some advice on where to get started.
I’m writing an app which is both for single users as well as groups.
Unfortunately PostGres, MySQL, etc are not an option as Database administration is way beyond my users comfort zone.
Those apps have to be able to communicate with each other to synchronize their data (most likely SQLite databases) as new records could have ben added locally (like when you are out of the lab) or in a “main” lab database.
I’m thinking of a “Server app” which handles the main database.
My questions as a total networking-newbie:
Which secure networking protocol should I use?
How can I send RecordSets? Binary? As XML? Json? Plain text?
Markus, if you have a Windows computer available (A Windows 7 computer with modest specs will do very well), you may want to take a look at MS SQL Server (The Express Edition is free, and in small to medium projects, it performs just as well as the big iron versions). It is very easy to set-up and maintain. Perhaps it is just because I am used to it, but I find it much much easier to manage than Postgre. Express with Advanced Services includes the Management Studio and basically anything you need to run a multi-user project on it. . MS SQL Server Express Edition is in my opinion vastly superior to SQLite and its server variants.
The down side is that you have a bit more work to do to connect to it from a Mac of a Linux workstation. There are threads on the subject on the Forum.
@Louis: Thanks, but MS SQL Server is not an option. Many labs are Mac only, and it still requires administration.
Basically my solution needs to be completely transparent and self-contained. The most I can expect of a user is to enter the IP of the server (though auto-discovery would be better).
How about SAP Advantage web platform?, or local? They claim “No Administration”. (Well, such a claim coming from SAP AG needs to be taken with a certain level of caution…) I gather that you can use built-in web services to access and post data.
Thanks guys but I’m not looking for third-party tools.
I need to have full control over my software. It might not be as good or full featured as CubeSQL or MS SQL or whatever, but I can adapt it to my needs - which are NOT filled by any of the commercial solutions. For example a user might need to display his company logo on the server app
you could build a web app that uses handlespecialurl to build out an API. recieve queries and send responses via json
then display the json in a listbox. you can then store the data in sqllite database
it would be fairly trivial to get a proof of concept up and running, i think there is an example in xojo web.
@Eli Ott: I had looked into CubeSQL. I’m also aware that CubeSQL Amin is open source and gave it a try just now. Nice, but too complicated and at the same time not suitable as is. It is still too much written from the point of view of a computer person, not the point of view of an end user.
Just an example: How to give privileges to a user (not a group) to access certain tables in the database but not others? It should be straight-forward but isn’t.
The success of FileMaker is build purely on making these things easy. My app needs to be even easier than FileMaker.
In one of my apps, I manage security within the app. The app is designed in transactions. At the beginning of each transaction, before any window is shown, a security check is done, resulting in either access or a message box telling why access is not granted. Individual user profiles are maintained in a set of tables in the database (there is an admin transaction to manage that), where each transaction is allowed or disallowed, allowed for display only, etc. Users are also graded as user, limited user, admin, superadmin (me, basically). Typically, users are limited to their own organizational units (but I have it configurable almost ad nauseam). Admins are given larger rights. I get everything (writing the app does come with certain privileges, but I can lock myself out of the app). In short, you can manage accesses within the app and keep full control without resorting to the built-in security of the RDBMS. It keeps the connections easier to manage overall. And the whole thing can be managed with just a few methods in a User class.
That said, I also implemented some security on the database itself, to provide specific accesses to the app user, and admin accesses to a small number of superadmins, and to hopefully keep everyone else away.
In short, I did not manage table access, but I chose instead to manage the content that is accessible wthin the tables. I would think that this approach is easily adaptable to the requirement that you described. It does require that the tables and entries are keyed to suitable organization or other actionable parameters. For example, a customer is maintained in several tables. One is a common table where the customer number is assigned. Specific sales data is maintained in a separate table where the key includes organisational unit information. A sales person can only access clients that can be selected with the organisational units granted to that sales person. A Master Data administrator can access any base table entry, but could be restricted to only some of the sales data.
I did a sample project for a newbie last year that you could use to start. Server ProjectClient Project
These are both native Xojo projects using Eddies demo database. You’ll need to adjust the location of the database file in the app.open event of the server project.
There is no attempt at security so you’ll have to make your own.
