Download - Taking it all offline with SQL Anywhere
![Page 1: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/1.jpg)
Taking it all Offline with SQL AnywhereEric Farrar, Product ManagerSybase iAnywhereSeptember 17, 2008
![Page 2: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/2.jpg)
Simple MySQL and PHP Blog
![Page 3: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/3.jpg)
The Offline Problem
![Page 4: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/4.jpg)
The Offline Problem
� Why not user Gears?
� App must be written in JavaScript
� No PHP code reuse
� Lack of encryption, security, strict referential integrity, etc
� How do you sync?
� What do we need to host an offline version of the application?
� Web Server
� Database
� PHP installation
� Synchronization
![Page 5: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/5.jpg)
Enter SQL Anywhere
� SQL Anywhere is an enterprise caliber, full-featured, SQL relational database
� Sybase SQL Anywhere ≠ Sybase ASE
� Over 15 years old, over 10 million deployed seats
� Roots in the embedded and mobile space
� Supports
� triggers, stored procedures (SQL, PHP, Perl, Java, .NET), materialized views, full text search, full referential integrity, FIPS approved encryption, …
� 10 megabyte footprint
� 1 user � 1000s of users
� 1 Megabytes � 100s of Gigabytes
![Page 6: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/6.jpg)
SQL Anywhere and PHP
� Newly re-written module for PHP
� API based off of the mysql API
� sasql_<FUNCTION NAME>()
� Supports:
� Prepared statements
� Stored procedures with INOUT parameters
� Multiple-result sets
![Page 7: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/7.jpg)
What We Need
� Built-in HTTP server
� HTTP
� SOAP
� It is a database
� External Environments:
� PHP
� Perl
� .NET
� Java
� C (external libraries)
![Page 8: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/8.jpg)
What We Need Cont.
� Built-in synchronization
� MySQL
� Oracle
� MS SQL Server
� IBM DB2
� SQL Anywhere
� Sybase ASE
![Page 9: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/9.jpg)
Stand-Alone Application
MySQLApache
+ PHP
PHP
Files
![Page 10: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/10.jpg)
MobiLink and Synchronization
� MobiLink acts as a ‘sync server’
� Makes a regular database connection to consolidated database
� Handles all synchronization traffic
� Set up in farms to scale out (> 100,000 syncing remotes)
� Write sync scripts to handle business logic of sync
� SQL (native dialect of consolidated database)
� .NET
� Java
� Conflicts detected at column or row level
![Page 11: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/11.jpg)
Adding MobiLink
MobiLink Server
![Page 12: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/12.jpg)
Client Sync (dbmlsync)
� Uploads all changes to database since last sync
� All synchronization happens in a SINGLE transaction
� Any failures will cause a full rollback
� Sends both current value, and original synced value of rows
� Can be run on demanded, or scheduled
![Page 13: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/13.jpg)
Sync!!
MobiLink ServerClient Sync Server
![Page 14: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/14.jpg)
Syncing Files
MobiLink ServerClient Sync Server
Offline
PHP
Files
![Page 15: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/15.jpg)
Storing Files in the Database
MobiLink ServerClient Sync Server
Offline
PHP
Files
![Page 16: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/16.jpg)
Conflicts
� What if two people change the same blog post?
� Posts are stored a single row in the database
� Need to ‘merge’ the rows
� Solution:
� Use diff3 utility as our custom business logic
� diff3 requires:
� Original
� My changed copy
� Their changed copy
![Page 17: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/17.jpg)
SQL Anywhere as a Server
� While this example has used SQL Anywhere as the remote, SQL Anywhere can also be used as the server
![Page 18: Taking it all offline with SQL Anywhere](https://reader033.vdocuments.us/reader033/viewer/2022052215/55529b3db4c905e8128b4f05/html5/thumbnails/18.jpg)
Where to get more information
� SQL Anywhere Web Forum
� http://groups.google.com/group/sql-anywhere-web-development/
� My Blog – “Peering Behind the Browser”
� http://iablog.sybase.com/efarrar
� SQL Anywhere PHP Module page
� http://www.sybase.com/detail?id=1019698