That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use).
When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers are allowed.
In WAL mode, a writer and readers do not block each other, but there is still only one writer allowed.
your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
)
ON Message.msgID = contact_m2m_message.messageID ;
Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.
The 2nd query is not valid because you have not provided an alias for the derived table:
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Select Contact.contactNumber, Contact.contactName
FROM Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
) --<-------------------------------------- no alias
ON Message.msgID = contact_m2m_message.messageID;
To make it work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID
in the SELECT
list of the derived table (and remove columns that are not used):
SELECT
Message.messageContent, Message.messageDateTime
FROM
Message
INNER JOIN
( Select -- Contact.contactNumber, Contact.contactName,
contact_m2m_message.messageID
FROM Contact INNER JOIN contact_m2m_message
ON Contact.contactID = contact_m2m_message.contactID
) AS cm
ON Message.msgID = cm.messageID;
But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:
SELECT
m.messageContent, m.messageDateTime
FROM
Message AS m
INNER JOIN
contact_m2m_message AS cm
ON m.msgID = cm.messageID
INNER JOIN
Contact AS c
ON c.contactID = cm.contactID ;
Best Answer
Check out https://stackoverflow.com/q/784173/27310 to get an idea of how big SQLite databases can get in theory without performance problems. However, I'd suggest that you use a good database abstraction layer so you don't have to worry about the underlying database at all. That way, once you hit SQLite's working limits, you can switch over to a non-embedded RDBMS without much work.