How can I tell if a user is logged in using straight SQL based on their email address?
We have a system that is highly coupled with ExpressionEngine and cannot use the Magento API in many of the EE templates.
Edit to show current login code:
Mage::getSingleton('core/session', array('name'=>'frontend'));
$session = Mage::getSingleton('customer/session');
$session->login($ParticipantInfo['PreferedEmailAddress'],'default_password');
$session->setCustomerAsLoggedIn($session->getCustomer());
TL;DR: As far as i know, even if session data is stored in the db, there is no definite way of telling only via plain SQL.
Question would also be: Which user? Customer, admin or api user? Assuming you store session data within the file system, I could think of some options:
For API-Users, have a look at the api_session table, you can do a join with the api_user table, which stores the email address. However, there is no way, the information in these two tables will suffice, as only session id and logdate are saved for a specific user id and you have no way of telling if a session is still active.
Querying for this data those would probably be something along the lines of:
SELECT *
FROM api_user
INNER JOIN api_session ON api_user.user_id = api_session.user_id
WHERE api_user.email = "<known_email>"
Admin users are stored within admin_user, however, like for api_user, no information is stored along for session management.
Customers are stored within the customer_* tables. You can look them up in the log_visitortable:
SELECT *
FROM log_visitor_online
INNER JOIN customer_entity ON customer_entity.entity_id = log_visitor_online.customer_id
WHERE c.email = "<known_email>"
Again, no information can be retrieved, if the session is still valid. EDIT: Tim showed how to do it correctly in his answer.
No information is stored directly, if a user is logged in currently, only, when the creation date of the session. With out-of-the box functionality you should not be able to tell accurately via SQL if a user is currently logged in or not - this would be insensible at best, as magento checks the user's session's validity against the stored session data in the db/filesystem, so without the user's session data, you can determine nothing with 100% accuracy.
With a little bit of work you can hook into the session management of Magento. There's a cheat sheet for events the core ships with. You can also create you own custom events, which you may listen to and execute code upon.
The idea here would be to write a module which could store extra information on the customer (admin or api user vice versa) or within an extra module table. You can hook into the login process and set a timestamp for the api_user/customer/admin has logged in and refresh that timestamp upon a request. If a user's timestamp hasn't been refreshed for, let's say, X Seconds, you assume the user is logged in any more. You delete the user's timestamp upon the logout event.
However, this is also not 100% accurate and it heavily depends on what a user does in you system.
Anyway, I hope I could provide some insight.
lg,
flo