I need a PHP script that securely and quickly syncs the database structure and data of a local SQLite DB with an online mySQL DB. This project is for the online script only.
I have a downloadable software application (known as the 'software' from here on) that creates and manages a local SQLite DB. The software is capable of pulling data from the SQLite DB in any format (XML, SQL etc...) and send it to the server for processing and input into the online mySQL DB.
The local SQLite DB can be modified by the software and the online mySQL database can be modified by another source as well. So they both need to stay in sync with each other.
In addition, there can be multiple local SQLite DB's on various computers since the software can be used on multiple computers.
1. The database will consist of 2 - 4 tables. The software will check to see if the database has been created online. If not, it will create the database online based on a table structure provided.
2. Once we know the database is available online the software will ask the database for any new entries. To get new entries, each table has a 'dateCreated' column. The software will ask the online DB for all entries after a specified date (stored within the software).
3. Once the software has the new online entries, it will send the new local entries it has to the online DB. The software will have a column labeled 'modified' that knows whether or not it has synced this column with the online DB. So once it sends the local data to the server, the server needs to let the software know if the data has been successfully entered online. If it has, the software will set the modified column accordingly.
4. For deleting records there will be a table with the following columns;
UUID, tableName, dateDeleted
The server will store the deleted UUID, which table it is from and the date deleted so that the local DB can download this data and delete the appropriate rows. Likewise, the local DB will have the same details that will be synced with the online DB.
5. (once the above is completed) There also needs to be a way for the software to communicate **securely** with the web server to update the table structure if needed. For example, it needs to be able to;
a) add a new column of a specified type (so send the column name and type along with the table it belongs to and the default value. The online version should communicate a success or failure)
b) add data to a specific column
c) possibly other additions
Note: The local sqlite DB's never have to have their structure changed via syncing. Any structure changes will be a one way sync from the local DB to the online DB.
In order to ensure the integrity of the data, we rely on a UUID column instead of an integer primary key. That means that since there are multiple local DB sources, it doesn't matter when the data is added to the online mysql source.
Because the server needs to sync with a local software, the server should have standardized error codes that is sent to the software when we have any errors that can then be shown to the user. So instead of using error strings, we simply send a set of standardized error codes depending if something went wrong.
In addition to the error code, there needs to be some kind of log with additional details such as an error in the SQL, etc...
We want to make sure that only communication from the software is processed by the online script. All communication should be sent with a hash that is made up of the data and a secret code that must be validated before any data is processed. In addition, the script should only accept data from a specific HTTP header.
The script should work with both SSL and non SSL connections.
I would also discuss security implementations with the chosen developer to make sure communication comes from the current sources only.
19 freelancere byder i gennemsnit $671 for dette job
Hi I work towards providing reliable, relevant and robust IT solutions at most competitive prices to my customers. I ensure 100% customer satisfaction so lets start Thanks
Sir , I have huge experice in PHP as well as Database , I can provide you long term service . Please give me the chance i will return you quality work Thx!
Hi, im expert in data integration tools. I have more than 10 years of experience in databases. I can make your project quickly and efficiently. I have a proccess like you need running in my ovh server. Regards