• Welcome, Guest. Please login.
 
September 23, 2020, 01:29:57 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Bern Ertl

496
You've got Questions? We've got Answers! / error handling
November 21, 2008, 06:46:39 pm
Am I correct in assuming that SQLitening functions may return any of the SQLitening or SQLite error codes (-19 to 101)?

If so, would it also be correct to say that the following error codes represent situations where it might be possible to retry the command successfully (pending a change in conditions):

    -8 = Access denied
   -18 = Error sending or receiving message"
     3 = Access permission denied
     5 = The database file is locked
     6 = A table in the database is locked
    10 = Some kind of disk I/O error occurred
    15 = NOT USED. Database lock protocol error
    23 = Authorization denied

I'm not sure what these errors represent:
   100 = sqlite_step() has another row ready
   101 = sqlite_step() has finished executing
497
I see global return errors flag referenced a lot in the function descriptions in the SQLitening.TXT file.  What does this mean?
498
TTDS required the client app to ping the server occassionally.

Looking at the SQLiteningServer code, it appears that SQLitening is checking for closed sockets.  I'm not sure how that compares to the ping/timeout system.
499
You've got Questions? We've got Answers! / Exits
November 18, 2008, 04:46:30 pm
Fred, I'm not sure how to create the "exits" as you call them without modifying the source for the server (causing additional work in the future to reconcile with any updates), so I am wondering if you might adapt something like this in a future release:

SQLiteningServer.BAS
Quote...
'============================<[ Globals ]>=============================
GLOBAL glFlags AS LONG                       'General purpose flags
...
GLOBAL glLogFileNumber AS LONG               'File number for the log

'=========================<[ Exits File ]>=========================
#INCLUDE "Exitmacros.inc"


...

'========================<[ Connection Main ]>=========================
FUNCTION ConnectionMain(BYVAL rqpAddr AS QUAD PTR ) AS LONG
...
   ' Close Tcp and log it
   TCP CLOSE llTcpFileNumber
   IF (llFlags AND &H01) AND (glFlags AND %gbfLogConnDcon) THEN LogIt "Dcon #" & FORMAT$(llTcpFileNumber) & "  " & IIF$(ERR = 57 OR ERR = 0, "", "Unknown Reason")

   'Call an Exit Macro
   mDisconnected


END FUNCTION
...


ExitMacros.INC
Quote'  ==========================================================================
'  |                                                                        |
'  | SQLitening Server - Exit Macros                                        |
'  |                                                                        |
'  ==========================================================================

MACRO mDisconnected
   'Put exit code here
END MACRO

500
Do you have a short example of what you mean by an exit?  Is this what the empty arCallExtension() function in the SQLiteningServer.BAS file was intended to be?
501
Quote from: JoeByrne on November 18, 2008, 02:36:36 pmOr you can create a timer on the server.  Whenever there is access from the client, reset the timer for that computer to zero.  Every 'x' number of seconds, minutes, or hours, poll your timers and see who hasn't connected in awhile.  Then you can assume those connections are dead and force the user to re-authenticate next time they connect.


I had assumed that SQLitening was already doing this (similar to how TTDS manages remote connections for Tsunami).  I'm still getting my feet wet here, so if made a mistake here, could somone explain how SQLitening detemines if a connection is dropped?

~~~

The more I look at the SQLiteningServerMonitor/log file option, the more problems I see.  The biggest problem I see is how to ensure that the log file doesn't get trimmed between monitoring sessions (so that disconnect notifications aren't missed). 
502
I thought I'd create a new discussion thread to focus specifically on this issue (as originally touched upon here).

I want to build a log-in system to manage access in accordance with licensing restrictions.  There will be a db/table containing licensing information (# of allowed concurrent users/connections).  There will be a separate table of user accounts (with designated access levels).  The licensing restrictions only affect some of the user accounts (the ones with appropriate access levels).

Building a log-in system is not really a problem.  The client application can update the status of users who log in. My question is how to best handle dropped connections (auto-log outs)?

I'm guessing that I could write some code in the SQLiteningServerMonitor to read the SQLitening log file and analyze it for dropped connections, but how can that monitor application then communicate back with the server application?  Would it be wise to have it adjusting the db/table with the user account information directly?
503
Thanks Fred.

I think SQLitening looks promising.  Do you have any examples/tutorials for the server side processing?  I see from the ReleaseHistory.txt file included with the download that there are two possibilities:
  • SQLiteningServerMonitor
  • SQLiteningProcs/slCallProc (note: slCallProc is not mentioned/explained in the SQlitening.txt file)
Which of these would be better suited to handling functions to track connections and tie them to a user account db/table (ie. a log-in system)? 
504
Quote from: Fred Meier on November 17, 2008, 04:05:31 pm
QuoteDoes SQLitening allow multiple connections to write to the DB at the same time if they are writing to different tables, or are all write requests handled sequentially?
Sequentially for a file.  SQLite, not SQLitening, controls writing to DB.  SQLite locks the file (DB) when writing so if two tables are in same file they are both locked, if they are in separate files then only one is locked.  Placing tables in separate files may be an advantage depending upon their relationship.


Hmm.. If I understand you correctly, SQLitening is not managing the server's use of SQLite at all then.  If there is any issue with the underlying OS not handling file locking properly (as mentioned above), the databases could get corrupted with heavy concurrent use.
505
Quote from: Fred Meier on November 17, 2008, 12:48:11 pmSQLiteningServer writes a log that contains connect/disconnect entries.  These could be used for "accounting" stuff.  Nothing available to control licensing.


I imagine that connections are logged/counted at the server level only - not tied to any specific database or table, correct?  Does SQLitening maintain a simple variable counter with the number of active connections?

Quote from: Fred Meier on November 17, 2008, 12:48:11 pmI have thought of putting exits in SQLiteningServer.  These exits could call a named entry in a named DLL at strategic spots.  This is something you could consider doing on your own.  Exits to a custom Dll would allow you to easly accept future updates of SQLiteningServer.


I'm not sure I understood what you meant by exits.  Is this similar in concept to procedure calls to a plugin handler?

Quote from: Fred Meier on November 17, 2008, 12:48:11 pmHow stable is SQLitening?  Only time will tell.  I suggest you stress test it.


I plan to, but was hoping to get some feedback from some of you guys who have already been down that road.

Quote from: Fred Meier on November 17, 2008, 12:48:11 pmSQLiteningServer will automatically disconnect dropped connections.  Easy to test, just end program without doing slDisconnect.  That is the way I do most of my programs.

I plan on keeping SQLitening current with SQLite for as long as I enjoy
coding, 45+ years and counting.


Good to hear.  Thanks.

Quote from: Fred Meier on November 17, 2008, 12:48:11 pmI'm not aware of the 10 user SQLite limit.  There is no built in connection limit in SQLiteningServer.


I got that idea from this wiki page - it may not be a hard coded limit, but more of a practical one where errors become frequent.

SQLite FAQ states:
QuoteSQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.
...
However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access.


http://www.sqlite.org/faq.html#q5

Does SQLitening allow multiple connections to write to the DB at the same time if they are writing to different tables, or are all write requests handled sequentially?
506
Quote from: JoeByrne on November 16, 2008, 08:51:23 pmNothing that I'm aware of, but to be honest, these are things that really need to be coded.  There are huge numbers of possibilities in these regards, so building a 'one-size-fits-all' scheme like this would be very difficult.  That said, creating a login/connection tracking application really isn't that hard.


Hmm... I explored this problem with Don's TTDS wrapper for Tsunami and came away with the impression that connection management is best done by the service layer.  I'm not sure that server side procedures can do the job effectively.

How does SQLitening handle dropped connections (that don't log out)?

Quote from: JoeByrne on November 16, 2008, 08:51:23 pm
Quote
  • Is there a physical or practical limit on the number of connections SQLite/SQLitening can handle?


I did a little research on this, but didn't come up with anything.  Like most databases though, your biggest 'limit' will be available RAM and the way you design your database.


I'm not sure those factors affect the # of connections to the server.  I seem to recall reading something about SQLite only managing 10 concurrent users in a shared (LAN) environment (direct access).  I'm assuming SQLitening overcomes that problem by managing the connection/request pool.  Is there a built in limit to this managed pool/queue?

Quote from: JoeByrne on November 16, 2008, 08:51:23 pmSQLite is in wide scale use.  I really don't think you'd have to worry about stability.  In all the technical articles I've looked at for troubleshooting, the database has never been in question.  99.9% of reported problems are coding issues.  Having worked a lot with Tsunami (and a number of other database engines) I can say without reservation that SQLite has been extremely robust and fool-proof.

The bulk of SQLitening is being developed by Fred Meier.  However, like SQLite, Fred has made the entire SQLitening project open-source.  This means you have all the source code so adding features or fixing any bugs you might find down the road is more than doable.  Fred's coding is very good, well documented, and easy to follow.  Unlike a proprietary system like Tsunami, you're never going to be at the mercy of a single company or programmer.  Having the source code provides a lot of insurance.


Thanks for all the feedback Joe.   :)
507
Hi everyone.

I've been investigating SQLitening after accepting that Tsunami's future is uncertain.

I was pleasantly surprised to read about the recent update including server side processing.  This looks to be a viable back end solution for one of my projects, but I do have a few questions:

  • Does SQLitening offer any method to count/track connections to a database and/or table (that could be used to implement a licensing system)?  IOW, if I build an application that requires clients to store a licensing key which encodes the maximum number of allowed concurrent users, is there a mechanism that can be used to enforce it?  Complicating things a bit more, if my app has a login system where users can have different access levels, would it be possible to limit connections by access level?

  • Is there a physical or practical limit on the number of connections SQLite/SQLitening can handle?

  • How stable is SQLitening?  Roughly 90% of my client base is Fortune 500 companies and their IT depts. will complain vociferously if my app is crashing their server repeatedly.

  • Who is developing SQLitening?  What plans are there for developing and supporting the product over the next few years?  Will SQLitening be updated to take advantage of SQLite updates?