Is it ok to have the sqlite file on the dropbox and use by multiple user NOT at the same time?? will this cause corruption or encounter problem??
it should be fine, but I did not try it personnaly so …
If 2 users use it at the same time and have Dropboc Sync running OR if there is ANY issue while syncing (lost connection, currupted files, …), you will end up with multiple copys of your Database File.
Dropbox had once a DB API, but they dropped it (no phun intended…): https://www.dropbox.com/developers-v1/datastore
I would not recommend to store a Database within a Dropbox Folder.
I would highly recommend not to open databases in shared folders.
It’s always possible that dropbox synchronizes and replace the file in use or change parts of it. If SQLIte than writes to the file with invalid cache data, it’s corrupted.
You’d better make a temp copy first in a temp folder, edit that and later copy back.
thanks everyone…
and even beyond the problems with sharing it to multiple people every time you write to it DropBox will upload it. I was doing some writing and just saving the text files to drop box directly. Every time the software did an auto save, which was every few seconds it seemed, drop box would upload the file again. It was a constant stream of upstream traffic while I was working. As soon as I noticed what it was doing I stopped it, but if youve got a good sized flat file database it will be uploading it over and over and over constantly as anyone is using it. Better to do something like copy the file to a local place to work on it, then place a lockfile on dropbox or other shared file system, then when your client is done copy it back to the dropbox folder. If any other user tries to log in at the same time the software can find their lock file and present them with a dialog saying that the database is already in use.
There are issues with file management in such synced folders too. I added a feature recently to my regular day job project where the database could be backed up each night to a shared folder for further backup. I used a naming convention with the database.001.zip databast.002.zip and so forth for a weeks worth of data in there at any one time. To manage them I would delete .007 and rename the rest and then save to .001. This works fine in a regular folder, but the iCloud drive folder choked on it. After I deleted the .007 file and renamed the .006 file it would actually copy back down the .007 file adding another number. So they build up .007 02.zip .007.03.zip etc! I was unable to make it allow me to delete a file and immediately replace it with a new one so I switched to a date naming scheme and that solved the problem.
[quote=259471:@Sascha S]If 2 users use it at the same time and have Dropboc Sync running OR if there is ANY issue while syncing (lost connection, currupted files, …), you will end up with multiple copys of your Database File.
Dropbox had once a DB API, but they dropped it (no phun intended…): https://www.dropbox.com/developers-v1/datastore
I would not recommend to store a Database within a Dropbox Folder.[/quote]
OP said “not at the same time” … so ?
Even if it will guarantied and never ever ever ever be used more then 1 time at a time, sync can still happen later and collide, fail and so on.
[quote=259471:@Sascha S]If 2 users use it at the same time and have Dropboc Sync running OR if there is ANY issue while syncing (lost connection, currupted files, …), you will end up with multiple copys of your Database File.
Dropbox had once a DB API, but they dropped it (no phun intended…): https://www.dropbox.com/developers-v1/datastore
I would not recommend to store a Database within a Dropbox Folder.[/quote]
I am a storage (SAN/NAS/Object/etc) expert (or at least that is what people keep telling me). I would not ever store a database on a shared folder (like dropbox/box/one drive/sky drive/google drive/etc) nor on a NAS shares (NFS/AFP/CIFS/SMB). If the network connection goes away for just even a little while you get corruption. Now I would keep “backups” or “copies” of the database file on the NAS but not run it from there.
Over the years I have dealt with soo many times the datafiles getting corrupted that I have lost count. Not just SQLite but all sorts of datafiles.
Words to live by.
Could someone give me a pointer to good practice about this please. Ive been using a SQLite single user database successfully for years and now I want to share the data with another user. I thought Dropbox would be the way to go but clearly its not. Ive looked at the Connecting to Databases video, http://developer.xojo.com/webinar-connecting-to-db
which strongly discourages multi user use of SQLite. However, SQLite.org says that more than one user can work with a database at the same time
http://sqlite.org/faq.html#q5
So Id like to know if I can continue to use SQLite, if so where the file should be stored and how to connect to it. I should say that I want the data held remotely so that both users can get to it from different locations if necessary.
[quote=360650:@Phil Abel]Could someone give me a pointer to good practice about this please. Ive been using a SQLite single user database successfully for years and now I want to share the data with another user. I thought Dropbox would be the way to go but clearly its not. Ive looked at the Connecting to Databases video, http://developer.xojo.com/webinar-connecting-to-db
which strongly discourages multi user use of SQLite. However, SQLite.org says that more than one user can work with a database at the same time
http://sqlite.org/faq.html#q5
So Id like to know if I can continue to use SQLite, if so where the file should be stored and how to connect to it. I should say that I want the data held remotely so that both users can get to it from different locations if necessary.[/quote]
@Phil Abel SQLite can be used multi user if the tool connecting to it supports SQLite MultiUser AND all users are not the same machine. Putting SQLite on Dropbox or NAS/CIFS share or something similar doesnt guarantee writes/updates to the file in FIFO order nor do they guantee that they are written in a given time frame. This will cause data corruption and/or data loss. I deal with this type of data corruption/loss daily (not always related to SQLite but with data files).
As @Christian Schmitz has suggested you can use cubeSQL or server RDMS systems like PostgreSQL or MySQL/MariaDB. cubeSQL uses SQLite under the hood but it is a server that you talk to the server and it maintains the writing to the local disk that it has. It is like a cross between SQLite and RDMS.
if you decide to take the plunge into a real RDMS system, please look at PostgreSQL.
@scott boss @Christian Schmitz Thank you both very much. CubeSQL it is.