> SQLite being a file on disk does make connecting from external clients effectively impossible.
Not quite impossible (if you design an appropriate VFS), but it doesn't work as well as a real client/server database, since SQLite is not designed for this use.
> SQLite does not support enums which means you're forced to use strings. ... The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string.
In SQLite you can use CHECK in a table definition to require all values of a column within a set of specific possible values.
However, I think there are some actual flaws in SQLite, such as:
1. It uses Unicode (but only partially; case-insensitive is only with ASCII). This can make it less efficient than it should be if you are dealing with ASCII only, and makes it difficult (and somewhat inefficient) to deal with non-Unicode text. Although it is possible to store non-Unicode text in a TEXT value or in a BLOB value, and to use CAST everywhere or to override the built-in functions with your own, neither is really ideal, for several reasons (including causing some optimizations to not work, making your code even longer and less efficient, etc). It is also possible to patch SQLite to do this, but then if you upgrade, you must patch that one too.
2. The URI file name mechanism is a bit messy. Using a separate argument for the parameters might be less messy.
3. There is no standard way to define the time zone used for functions that deal with local time. (You can override the function to find the current time by the VFS, but overriding conversion to local time is only possible by use of an undocumented function (which is not guaranteed to stay the same in future versions).) Better (in my opinion) would be to add such a function into the VFS (since the current time function itself is in the VFS anyways, so it should go together).
4. It might have been better to store the journal in the same file as the database. One idea how this might be done: Set the read version to 1 and the write version to 3. If you begin a write transaction, lock the database and then make copy-on-write of any modified pages into free pages, but do not change the references to the free pages in the header (so other programs still believe they are free). To commit a transaction, make a table of the required page linking changes in the file, and then temporarily change the read version to 3, and then change all of the links (so that the pages containing the old data would now be considered free), and then change the read version back to 1 (the write version will still be 3) and then unlock the file. To roll back a transaction, simply unlock the file (which will be done automatically if the program crashes); there is no need to write or delete any file. However, one disadvantage of this method is that the database file is now up to twice as big (depending on how many pages need to be modified for each transaction), and there may be other disadvantages too.
5. You need file names (which is necessary for separate journal files, anyways). However, in my opinion it might be better to pass file descriptors or stream objects. (SQLite and some other libraries annoy me that they do not do such a thing, and require file names.)
Not quite impossible (if you design an appropriate VFS), but it doesn't work as well as a real client/server database, since SQLite is not designed for this use.
> SQLite does not support enums which means you're forced to use strings. ... The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string.
In SQLite you can use CHECK in a table definition to require all values of a column within a set of specific possible values.
However, I think there are some actual flaws in SQLite, such as:
1. It uses Unicode (but only partially; case-insensitive is only with ASCII). This can make it less efficient than it should be if you are dealing with ASCII only, and makes it difficult (and somewhat inefficient) to deal with non-Unicode text. Although it is possible to store non-Unicode text in a TEXT value or in a BLOB value, and to use CAST everywhere or to override the built-in functions with your own, neither is really ideal, for several reasons (including causing some optimizations to not work, making your code even longer and less efficient, etc). It is also possible to patch SQLite to do this, but then if you upgrade, you must patch that one too.
2. The URI file name mechanism is a bit messy. Using a separate argument for the parameters might be less messy.
3. There is no standard way to define the time zone used for functions that deal with local time. (You can override the function to find the current time by the VFS, but overriding conversion to local time is only possible by use of an undocumented function (which is not guaranteed to stay the same in future versions).) Better (in my opinion) would be to add such a function into the VFS (since the current time function itself is in the VFS anyways, so it should go together).
4. It might have been better to store the journal in the same file as the database. One idea how this might be done: Set the read version to 1 and the write version to 3. If you begin a write transaction, lock the database and then make copy-on-write of any modified pages into free pages, but do not change the references to the free pages in the header (so other programs still believe they are free). To commit a transaction, make a table of the required page linking changes in the file, and then temporarily change the read version to 3, and then change all of the links (so that the pages containing the old data would now be considered free), and then change the read version back to 1 (the write version will still be 3) and then unlock the file. To roll back a transaction, simply unlock the file (which will be done automatically if the program crashes); there is no need to write or delete any file. However, one disadvantage of this method is that the database file is now up to twice as big (depending on how many pages need to be modified for each transaction), and there may be other disadvantages too.
5. You need file names (which is necessary for separate journal files, anyways). However, in my opinion it might be better to pass file descriptors or stream objects. (SQLite and some other libraries annoy me that they do not do such a thing, and require file names.)