2

Several years ago I wrote a desktop application, a small accounting system, in Delphi 7, that is in user in a medium-sized company. The code belongs to me. I ported another Accounting system that I had built years before (Delphi/IB), to use SQL Server and added new business rules, in my free time, then I gave the application to the company. (No contracts, no license, simply the code is mine, no one is arguing that).

It currently works by connecting directly to the database. Each user in the application is a user in the database. I have roles defined for each form the user is able to see, and also they matches DB roles in SQL Server, so, not even by mistake you would be able to see data you shouldn't be. I also managed to mangle the passwords so that the user (a normal user, not a hacker) won't ever be able to connect directly to the DB even if it knows the ip/port of the server. It has worked in this way for years.

A new IT management, lots of new people, are frown upon my application. Take note that this company also use SAP for many of the branches and my accounting system servers the purpose of a sort of a consolidation system. They don't like the Client/Server architecture and are pointing fingers to security risk and what not. I agree my C/S architecture and the way I connect to the database doesn't feel robust.

I suppose I need "something" in the middle. But researching for 3-tier application I'm drowned in a sea of articles about 3-tier in the same application, I mean, just a separation of data within the same application, while I think what I need is a 2nd layer of software between my desktop app and the database. I guess I would need an application server too, may be?

I'm also fluent in C# and lately I have written more desktop apps in C# then in Delphi, so I could migrate my Delphi code to C# if needed. Since the code is mine, this is sort of an investment, I might capitalize in the future. That's why, besides the request from IT, I'm also interested in making this more robust.

I'm looking for an article to get me started in order to write such piece of software. WCF, Midas, ORM?

Without wanting to look pretentious, I'm looking forward to create a robust platform, like that used in SAP-ERP. So I could grow the application adding new modules, like controlling, payroll, billing, etc and turn it into a some sort of small ERP.

Craig Stevensson
  • 137
  • 5
  • 1
    Have you considered making it a web application instead of desktop tool? – johannes Oct 21 '14 at 20:07
  • off-site resource recommendations are explicitly off-topic per [help/on-topic]. See http://meta.programmers.stackexchange.com/questions/6483/why-was-my-question-closed-or-down-voted/6487#6487 – gnat Oct 21 '14 at 20:17
  • ...see also: **[Where to start?](http://meta.programmers.stackexchange.com/a/6367/31260)** – gnat Oct 21 '14 at 20:18
  • I don't think this is "where to start" question. I'm asking for technologies names. Similar question to mine has been asked without being downvoted, in the area of how to securely manage database connections in a desktop application. And how can a broad an opinion based question like this be upvoted? http://programmers.stackexchange.com/questions/154733/my-boss-decided-to-add-a-person-to-blame-field-to-every-bug-report-how-can-i/154741#154741 It's not even technical at all! – Craig Stevensson Oct 21 '14 at 20:27
  • The question you linked is also off-topic, but attracted enough positive attention to avoid being closed. –  Oct 21 '14 at 22:05
  • 1
    @Snowman that question also appears to predate the review queue (earliest reviews on P.SE are from August 2012) and later attempts to close it didn't meet the necessary number of people when it entered the review queue a second time ( http://programmers.stackexchange.com/review/close/73856 ). It also has 8 deleted answers –  Oct 21 '14 at 22:55
  • 3
    And how could my question get enough attention if you doomed it within 20 minutes of publication? See I got 2 long, well elaborated answers already, this is a serious question and I understood it could not belong to stackoverflow, so I came here. I can't go to Yahoo answers with this! – Craig Stevensson Oct 22 '14 at 17:49
  • 4
    @CraigStevensson That it was "doomed" within 20 minutes is actually a good thing. Better than wasting a lot of people's time when it's closed 3 days later, or having a ton of answers out of sync as you continually try to improve the question to meet our guidelines. I suggest you go to [chat]. – Doval Oct 22 '14 at 18:28
  • 2
    This is a good example of why I no longer participate on Programmers.SE. It's just StackOverflow Part Deux, and is utterly utterly useless. – Warren P Nov 20 '14 at 15:38
  • @craig-stevensson: Consider adding a middle-tier server via embarcadero's DataSnap or RemObject's DataAbstract. I have more experience with RemObjects DataAbstract. It is exactly the solution I needed for the problem I had, which is very much like yours. Nobody can connect to the database directly. They can only hit the encrypted HTTPS server endpoints I built using DataAbstract. – Warren P Nov 20 '14 at 15:40

2 Answers2

7

Sorry, but this is madness. Rewriting a working application in a different language because someone doesn't like the style of data access plumbing?! It's needless expense, effort, disruption, and risk. Where's the ROI? What is the time-to-value? If the question describes the full reasoning, standard go/no-go business metrics cannot possibly support rip-and-replace.

This isn't because I disagree. Direct-to-the-database connections have some real downsides, such as limited scalability, and likely violation of the least-privilege security approach. They lock data interpretation into client apps, making multi-platform apps, long version tails, and combination tasks such as data analytics more challenging. And Delphi 7 is getting a little long in the tooth. Etc. etc.

If you were a commercial software company, needed to greatly increase the number of concurrent users, needed to operate with less trusty users or on less secure networks, or had a specific feature need that direct-to-database connections impeded or precluded, I'd be the first to concur with "update your architecture" or "modernize your tooling." But from your question at least, those don't seem to be the case. You are a medium-sized company; the software appears to be doing its job; and there is no clear motivation for a clean slate rebuild or major roto-tilling. That makes this a major update to conform to someone's architectural preferences. That violates primum non nocere ("first, do no harm") for questionable benefit.

This is exacerbated by the likelihood that you don't have good test suites to confirm that a new clean-piece-of-paper build works correctly. (Don't feel too bad about that--most shops don't for their internal apps.) And if it's accounting software, getting data wrong can have extremely negative business consequences. All of this shouts "this will end badly!"--or at least, "you will eventually be unhappy that you decided to rebuild something that already worked for shaky reasons, rather than work on something that could genuinely help the company." I've seen too many clean-slate projects end that way.

Major roto-tilling or start-fresh can work--but you have to have substantial backing, management and users that accept it will take a while and introduce disruption along the way, and most important, a solid reason for going that way.

Instead, I'd suggest you examine what incremental update paths you might have from Delphi 7, and develop a more incremental plan and a more benefits-based motivation for making substantial changes.

Jonathan Eunice
  • 9,710
  • 1
  • 31
  • 42
  • 5
    +1 Sounds very much like they want him to fail during "the great rewrite", so they have a reason to throw the existing solution out and replace it by something else (not necessarily better). – JensG Oct 22 '14 at 22:11
2

Not clear whether you are saying your CIO's issue with your design is the persistent DB connection, or the fact that you use application users mapped to DB users. Neither is necessarily BAD architecture, depending on the requirements. There are many applications that do the same thing, even high end stuff that runs on Oracle Enterprise.

And, even if you eliminate the persistent, direct connection, a competent admin (much less hacker) running your app locally, with or without access to the DB server will be able to connect directly to the database if they work hard enough. Why is that a problem? Sure you don't want it to be public facing, but internal apps can be managed with firewall rules, or co-resident app + db on the same server. Traditionally, if we embed a database engine in an app we don't change the port. The security can't be based on obscurity. Either make sure the app is co-resident so there is no DB network connection except localhost, or ensure it is using an encrypted session, that removes the issue of network snooping, but leaves local hacking possibilities. Outside of that, what is the problem?

Are you not able to dedicate a database instance to the app? Perhaps he wants your app to co-exists on a SQL Server instance with other apps. If so, I can understand the requirement, and it is a good thing. Your choice to map users to real DB users was not a great choice, but it isn't end of the world, that is more of an old school design that works, but limits your options (requires dedicated database). Now that it works, you have to weigh the costs. Maybe your CIO already has.

Moving on, a local server (local LAN) for locally installed desktop application is the most common model. Otherwise, you either move to a full SaaS web model, or use disconnected synchronization. It isn't an OLTP web server so I don't understand the issue with persistent sessions. If he is stuck on this point, there is still some good in rearchitecting it...here are some options, with benefits.

You can port a connected SQL design to a disconnected SOA design by creating a service "agent" (WCF services serving up data calls). You need to map all of your queries over to service calls, implement them in a service layer, then deploy that service layer as the front-end to your database. You can still use fat client data grids all you like, but the data flows via WCF or Data Services from app to broker, broker to DB, and if you need to get fancy, you can implement IQueryable and your own custom data source to allow arbitrary query criteria. I'm not saying it is painless and cheap to transition, but it works. The advantage is you can move your data server anywhere on the Internet (cloud) and your fat app can still work over HTTP/HTTPS. C# / .NET is perfect for this. It's service interoperability is very rich.

If your DB is not local on a LAN/WAN, and performance/latency is an issue, there are other options that I've also had success with, such as implementing a local SQLAnywhere database, replicate the schema of the master DB, and use Mobilink to synchronize against the consolidated database. There are a lot of commercial products with embedded SQLAnywhere or Ultalite DBs, or SQLLite DBs that use this architecture. (Hate to give away tricks of my trade but...)

codenheim
  • 2,963
  • 16
  • 18