Page 1 of 1

ODBC table cache rules

Posted: Mon Oct 22, 2012 1:38 am
by swhiteman
Hi,

Trying to figure out the rules by which Wing FTP manages any runtime caches of SQL-based user and VFS info.

I've noticed, for example, that if I rename a user on the SQL side, I will see the updated username in the Wing FTP Server Admin, but if I try to change that username back to the old username (which does not exist in the db; I only have a few test users now, so it's very easy to manage), I get "User name already exists." This strongly suggests that there is some place that the SQL data is cached. Unfortunately, while I know many/most database-driven FTP servers do some caching, it's rarely documented. And for my application, I must do all my user and admin management solely in SQL side (never using Server Admin unless there's some operations-level need), yet I can't be telling people their data is updated in an unknown amount of time. In theory, if I knew it took 5 minutes or even 15m, that might be okay -- but far better is being able to get everything at runtime with no local caching, regardless of the expected connection/query overhead.

Can someone at Wing HQ please be as detailed as possible in describing what kind of real-time SQL-only manageability is possible? That includes real-time changes to passwords, additions and deletions of VFS entries, and additions and deletions of users. I need everything to be predictable so I'm never left going, "That password 'should' work now," etc.

Also (on a quite related note) are there hard limits on the total number of VFS entries you can handle? I'm looking at possibly hundreds of thousands of discrete VFSes, though only thousands of users. My current FTP solution crumbles because it caches, that is, by the developers' admission, it can't handle everybody's stuff in RAM when you get over 50-100,000 entries. I am trying to get that those people to change all the lookups to be done at runtime at user's discretion, since I actually don't have much load at all. I'd be happier to just get a new product, though.

You can compare the situation to when you decide to cache LDAP query results or always go ad hoc (I've built LDAP clients before). I've found that with a big database, caching and cache expiry/management ends up adding too much overhead, and just opening a new connection and fetching a single user profile every time is more predictable.

TIA.

Re: ODBC table cache rules

Posted: Tue Oct 23, 2012 3:10 am
by FTP
You can modify the user data via SQL operation directly when the client logged out, it means Wing FTP Server will cache something when the client logged in.

So we recommend you use Lua API to handle it.

Re: ODBC table cache rules

Posted: Tue Oct 23, 2012 4:11 am
by swhiteman
FTP wrote:So we recommend you use Lua API to handle it.
OK... so that isn't going to be possible because my SQL server is on a different VLAN and such and that's where the updates are processed. If I have to run a different process on a different server in order to update it, there's no practical purpose for SQL integration.

It sounds right now like I won't be able to use Wing FTP, but can you also answer the question about the max # of VFS entries?

Re: ODBC table cache rules

Posted: Wed Oct 24, 2012 6:05 pm
by FTP
There is no hard limits.