Updating SQLite database when updating a Xamarin Forms App for iOS and Andriod

Deploying a SQLite Database in a Xamarin Project

I use SQLite databases in some of my Xamarin projects and hence there is a requirement to make sure that anyone who has my application has the updated database when I do an update through Google Play or the Apple Store.

So in a situation where you have a pre-populated SQLite database, the first thing to do is to copy the database into the deployed location. This procedure is done when the application starts up by checking if the database already exists or not.

You add the SQLite database into the Assets folder for Android and into the Resources folder for iOS.

(Right click on the project Assets folder or Resources folder, select “add existing” and then browse to the SQLite file and select it).

In your Android project create a new class file FileAccessHelper.cs at the root of the Android project.

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

namespace SQLiteDeploy.Droid
{
   public class FileAccessHelper
   {

    /// <summary>
    /// This copies the database over if it does not exist on the Andriod device
    /// </summary>
    /// <param name="dbPath">The path of the database on the Andriod device</param>
    /// <param name="SQLiteAsset">The database name in the assets folder</param>
    public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
       {
       if (!File.Exists(dbPath))
          {
             using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
             {
                using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                {
                 byte[] buffer = new byte[2048];
                 int length = 0;
                 while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                    {
                       bw.Write(buffer, 0, length);
                    }
                 }
              }
           }

        }

     }
}

In the MainActivity.cs file:

 

//Copy over database
var SQLiteAsset = "SQLite.db3"; //database name
//get path of database
var documentsPath = System.Environment.GetFolderPath(System.Environment
                                   .SpecialFolder.MyDocuments);
var path = System.IO.Path.Combine(documentsPath, SQLiteAsset);
FileAccessHelper.CopyDatabaseIfNotExists(path, SQLiteAsset);

In your iOS project create a new class file FileAccessHelper.cs at the root of the iOS project.

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Foundation;
using UIKit;

namespace SQLiteDeploy.iOS
{
     public class FileAccessHelper
     {

        /// <summary>
        /// This copies the database over if it does not exist on the iOS device
        /// If the file does not exist in dbPath, then copy it from SQLiteAsset location
        /// </summary>
        /// <param name="dbDestination">The path of the database on the iOS device</param>
        /// <param name="SQLiteAsset">The database name in the installation folder</param>
        public static void CopyDatabaseIfNotExists(string dbDestination, string SQLiteAsset)
        {

           //If file does not exist, then copy file to it from the resources folder
           //located at NSBundle.MainBundle.PathForResource
           if (!File.Exists(dbDestination))
           {
              var ext = Path.GetExtension(SQLiteAsset);
              var name = Path.GetFileNameWithoutExtension(SQLiteAsset);
              var dbSource = NSBundle.MainBundle.PathForResource(name, ext);

              //check if database exists at source first.
              if (File.Exists(dbSource))
              {
                  File.Copy(dbSource, dbDestination, true);
              }
            }
          }

     }
}

In the iOS Main.cs file:

 

     //Copy over database
     var SQLiteAsset = "SQLite.db3"; //database name

     var documentsPath = System.Environment.GetFolderPath(System.Environment
                                     .SpecialFolder.MyDocuments);
     var dbDestinationFolder = System.IO.Path.Combine(documentsPath, SQLiteAsset);

     //path is the full folder and file name, SQLLiteAssest is just the name of the file
     FileAccessHelper.CopyDatabaseIfNotExists(dbDestinationFolder, SQLiteAsset);

How do I test if it works?

Note that you can only test your application by completly deleting the application from the tablet or phone and then re-deploying it because the database may have already been copied over in a previous deployment.

 

Updated application but with new data in the database

When you want to deploy a new version of a database where the application is already available on the Google Play store and Apple store, then you need to make sure that the original database gets overwritten with the new database on the customer phone or tablet if different data is needed. The only way I can see to do that at the moment is to read the file size and compare it with the deployed value. This can be done in the FileAccessHelper.cs file.

The following is a modified version of FileAccessHelper.cs

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;

namespace KitchenTimer.Droid
{
       public class FileAccessHelper
       {

         /// <summary>
         /// This copies the database over if it does not exist on the Andriod device
         /// </summary>
         /// <param name="dbPath">The path of the database on the Andriod device</param>
         /// <param name="SQLiteAsset">The database name in the assets folder</param>
         public static void CopyDatabaseIfNotExists(string dbPath, string SQLiteAsset)
         {
            if (!File.Exists(dbPath))
            {
               using (var br = new BinaryReader(Application.Context.Assets
                                                                    .Open(SQLiteAsset)))
               {
                  using (var bw = new BinaryWriter(new FileStream(dbPath, 
                                                                     FileMode.Create)))
                  {
                   byte[] buffer = new byte[2048];
                   int length = 0;
                   while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                     {
                        bw.Write(buffer, 0, length);
                     }
                }
            }
        }
         else
        {

           //if file does exist, compare the file sizes
           long fileDeploySize = 20480; //deployment size of file.

          try
          {

           FileInfo fSQLiteAsset2 = new FileInfo(dbPath);
           long s2 = fSQLiteAsset2.Length;

               if (s2 != fileDeploySize)
               {
                  using (var br = new BinaryReader(Application.Context.Assets
                                                                   .Open(SQLiteAsset)))
                  {
                     using (var bw = new BinaryWriter(new FileStream(dbPath,
                                                                    FileMode.Create)))
                     {
                        byte[] buffer = new byte[2048];
                        int length = 0;
                        while ((length = br.Read(buffer, 0, buffer.Length)) > 0)
                        {
                           bw.Write(buffer, 0, length);
                        }
                     }
                 }
              }

          }
          catch
          {

          }

       }

    }
  }
}

In the above code, if the database already exists, I check the size of the database file and if they are different I copy the file over to the deploy location, overwriting the existing database file. In the above example the new database file size is 20480 bytes. So you do have to enter this value manually which may not be the best method.

 

How do I create a SQLite database and add my tables and data

I use “DB Browser for SQLite” which you can download from http://sqlitebrowser.org/ for Windows and Mac computers.

 

Using SQLite in your Xamarin Projects

This briefly describes using SQLite in your Xamarin Project and provides a simple exampple of the code:

** Add sqlite-net-pcl to all projects. This is the Nuget package sqlite-net-pcl by Frank A. Krueger
** Declare interface in PCL. You need to set up a connection Interface in the PCL project
** Implement in each application project. You then need to implement the interface in each of the other projects.

So first in the PCL project, create a folder called Persistence. Then create a new class interface file called
ISQLiteDb.cs as follows:

 

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace SQLiteDeploy.Persistence
{
    public interface ISQLiteDb
    { 
      SQLiteAsyncConnection GetConnection();
    }
}

Now in both the Android Project and iOS project create a folder called Persistence. Then create a class file called SQLiteDb.cs with the following code which is the same for the Android file and iOS file:

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using Xamarin.Forms;
using SQLite;
using SQLiteDeploy.Persistence;
using SQLiteDeploy.Droid.Persistence;
using System.IO;

[assembly: Dependency(typeof(SQLiteDb))]
namespace KitchenTimer.Droid.Persistence
{
    public class SQLiteDb : ISQLiteDb
    {
       public SQLiteAsyncConnection GetConnection()
       {
        var documentsPath = System.Environment.GetFolderPath(System.Environment
                                 .SpecialFolder.MyDocuments);
        var path = System.IO.Path.Combine(documentsPath, "SQLite.db3");

        return new SQLiteAsyncConnection(path);
       }

     }
}

You can download my example of this from here:

https://github.com/paulv200/SQLiteDeploy.git

 

Do I need SSL on my Web Server for use with PayPal IPN?

PayPal upgrades:

1) Changes to PayPal security for HTTP/1.1 and TLS 1.2
2) IPN Verification Postback to HTTPS

PayPal say their target for these is June 2018

 

You may have seen an article from PayPal which talks about HTTP and TLS. This is a server issue
which is the responsibility of your web host. Essentially there is an upgrade by PayPal to ensure that
all their servers meet the latest standards and hence your web server will also require to operate
in that way. You should contact your host about this if you are unsure, although most servers will already
meet these standards.

 

The second update is using HTTPS for IPN verification.

PayPal says:

“If you are using PayPal’s instant Payment Notification (IPN) service, you will need to ensure
that HTTPS is used when posting the message back to PayPal for verification. HTTP PostBacks
will no longer be supported.”

“Merchants and partners use Instant Payment Notification (IPN) to receive notifications of
events related to PayPal transactions. The IPN message service requires that you acknowledge
receipt of these messages and validate them. This process includes posting the messages back
to PayPal for verification. In the past, PayPal has allowed the use of HTTP for these
PostBacks . For increased security going forward, only HTTPS will be allowed for PostBacks to
PayPal. At this time, there is no requirement for HTTPS on the outbound IPN call from PayPal
to the merchant’s IPN listener.”

In practice this means that you need https on your web server to send https to PayPal and
then you need to change the set up of you PHP-KeyCodes, PHP-eSeller or PHP-SecureArea
application to identify the URL of the site as https rather than http.

Note: if a customer tried to make a purchase it would still succeed, but your store would
not be notified of that fact, and your records will not reflect the sale properly. Nor
will the PHP application automatically send the information to the customer by email.

Refer to PayPal documentation:

https://www.paypal.com/au/webapps/mpp/ipn-verification-https

So do I need SSL on my Web Server when using PayPal IPN? Yes, the PostBack from PayPal will not work
and SSL will give better security for your site.

WordPress Plugin for selling License Key Codes – PHP-KeyCodes

Hello and welcome fellow programmers.

A WordPress version of PHP-KeyCodes has just been released which has most of the functionality of the standalone PHP/MySQL application PHP-KeyCodes.

The plugin can be viewed and downloaded from the following link:

https://wordpress.org/plugins/withinweb-php-keycodes/

The plugin allows you to sell per-configured license codes, pin numbers, mobile phone numbers or any other similar codes from your WordPress installation using a PayPal account.

Using Subversion for WordPress Plugins

Subversion Control for WordPress

If you write a plugin for WordPress and want to make it available on WordPress.org, you have to use Subversion to handle revisions, versions and source control.

There are a number of programs that you can use and which can be installed on a PC or a MAC. Many do use the command line which can be difficult for those not familiar with the particular commands. However, there are a number of client applications that have very good user interfaces which does make the process much easier.

SilkSVN Windows Command Line Client. https://sliksvn.com/download/
TortoiseSVN for Windows which can be downloaded at https://tortoisesvn.net/downloads.html
Versions for the Mac which can be downloaded at http://versionsapp.com

When I first began to look at WordPress and SVN, I wanted to use a visual method of updating. I wanted to click a button and for it to automatically do everything without thinking. However, I found that using a visual interface did not show the point of SVN and when I started to use a text interface, I found I understood the process much better. Also there is a lot of documentation available for the command line interface which is helpful when things go wrong.

The following are some notes that I have made over the years about using the command line system with WordPress which may help if you are having issues. It is based on the WordPress.org documentation but I have added in some extra points.

 

Command Line SVN

As I use Windows I use SilkSVN.  Download and install SilkSVN and in the cmd terminal (go to Windows Start and enter cmd.exe), type “svn” followed by the required commands. “svn help” will show help text and will show that SilkSVN is working.

The documentation that WordPress has produced is located here:

https://developer.wordpress.org/plugins/wordpress-org/how-to-use-subversion/

The only issue I have found when using SilkSVN is that you should use double quotes wherever it uses single quotes as in the standard SVN documentation.

Also, always make sure you change directory to where your local repository is located.

 

Starting a new plugin

On your computer, first create a local directory which will be your copy of the SVN repository.

In this document I will use my-local-dir to refer to the local directory.

Next, check out the pre-built repository using the co command:

my-local-dir / svn co https://plugins.svn.wordpress.org/your-plugin-name my-local-dir
> A my-local-dir/trunk
> A my-local-dir/branches
> A my-local-dir/tags
> Checked out revision 11325.

 

You will probably be asked your username and password which will be the one you were given when you registered for WordPress.org.

The above command will create a set of folders on your local computer in your local directory called “trunk”, “branches” and “tags”.

The “tags” folder is for your releases and will consist of a number of sub folders that correspond to your version numbers.

The “trunk” folder is where your development version is located.

Copy your files into the “trunk” folder and then you have to let subversion know you want to add the files into the repository:

my-local-dir / svn add trunk/*

 

Now check in the changes:

my-local-dir / svn ci -m "Check in to trunk"

 

Now copy the trunk files to the required tag folder.  In this example I am going to create a folder 1.0.0

my-local-dir / svn cp trunk tags/1.0.0
> A tags/1.0.0

 

and commit the changes:

my-local-dir / svn ci -m "tagging version 1.0.0"

So now on the remote repository you should have your released files in the tag/1.0.0 and your current working development files will also be in the “trunk” folder.

 

Creating a new version

We want to create a new version of our plugin so we:
* Modify the readme.txt with the new version number.
* Modify the main php file with the new version number.
* Update the trunk folder so that it contains the latest files.
* Copy the trunk folder to a new tags folder using SVN.
* Check in the changes using SVN.

Essentially the method is the same as described above but with a few more checks.

On your local computer, make sure that your trunk files are up to date.

We can do this by using the SVN update command. (First, change your directory to where the SVN local directory is situated):

my-local-dir / svn update

 

Files in the remote repository are downloaded and update the local files.

Copy over the file or files to your local trunk folder and then let subversion know you want to add those files back:

my-local-dir / svn add trunk/*

 

Now commit the changes:

my-local-dir / svn ci –m “add new files to trunk”

 

Now do a status display:

my-local-dir / svn stat

You can see the meaning of the status information further at the end of this article.

 

At this point, you may have to delete files / folders by:

my-local-dir / svn delete 

In some cases, you may have to use –force to force the deletion.

 

When you do:

my-local-dir / svn stat

you should now see those files/folders marked for deletion.

 

Deleting only deletes it form the local copy and only schedules if for deletion from the repository. You have to then commit the changes by:

my-local-dir / svn ci -m "New files update"
> Sending trunk/my-plugin.php
> Transmitting file data .
> Committed revision 11327.

 

That updates the files in the trunk folder and deletes any marked files.

Now that you have the files checked in to the trunk folder, you can copy over the files to a tag folder as described above.

 

You do this by:

my-local-dir / svn cp trunk tags/2.0
> A tags/2.0

 

Now, as always, check in the changes.

my-local-dir / svn ci -m "tagging version 2.0"
> Adding tags/2.0
> Adding tags/2.0/my-plugin.php
> Adding tags/2.0/readme.txt
> Committed revision 11328.

 

The assets folder

Use the assets folder for files like screenshots, banners and icons for wordpress.org site. Note that the assets folder is a separate folder in the root of the folder structure and is not a subfolder of the trunk or tag folder.

Update as the assets folder follows.

 

Navigate to the local folder which is the root of the SVN, then enter:

my-local-dir / svn add assets/*
>  A assets/sceenshot-1.png
>  A assets/sceenshot-2.png

The “add assets/*” identifies all files in the assets folder.

 

After you add all your files, you need to check in the changes back to the central repository using ci:

my-local-dir / svn ci -m “Add assets”
> Adding assets/screenshot-1.png
> Adding assets/screenshot-2.png
> Transmitting file data
> Committed revision 1124545

 

Status Command

A useful command is “svn status” which gives information about the state of the repository.

 my-local-dir / svn stat

 

If you see ? in the list of files or folders, it means that they are not under svn control. In which case do “svn add
To use this command as with all the other commands, you need to change directory on your local computer to the root folder of the SVN.

U: Working file was updated
G: Changes on the repository were automatically merged into the working copy
M: Working copy is modified
C: This file conflicts with the version in the repository
?: This file is not under version control
!: This file is under version control but is missing or incomplete
A: This file will be added to version control (after commit)
A+: This file will be moved (after commit)
D: This file will be deleted (after commit)
S: This signifies that the file or directory has been switched from the path of the rest of the working copy (using svn switch) to a branch
I: Ignored
X: External definition
~: Type changed
R: Item has been replaced in your working copy. This means the file was scheduled for deletion, and then a new file with the same name was scheduled for addition in its place.
L : Item is locked
E: Item existed, as it would have been created, by an SVN update.

 

Problems, problems, problems

If you get a message ‘folder name’ is scheduled for addition, but is missing then look at the following document:

https://benohead.com/svn-file-scheduled-addition-missing/

Also look at:

http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.delete.html

The method that I have found to use is rm (remove), or you may use the delete command. So you do something like:

my-local-dir / svn rm –force c:\csv\withinweb_wp_keycodes\withinweb-php-keycodes\tags\2.0.0\views

Once you have removed all the problems you should be able to commit.

Note that you may have change the local directory to the file or folder that you are going to delete or you have to enter the full path name of the file or folder.

 

Checking differences

Checking files are at the correct version can be done by doing a diff.

An HTML5 Audio Player using a Customized Image

HTML 5 is able to play audio in a browser without the need for a plugin. In the long run, HTML 5 will probably put an end to audio plugins such as Microsoft Windows Media Player, Silverlight and Adobe Flash.

The audio player download provided here consists of JavaScript and css that give a customized version of the HTML 5 audio. You may use it as is on a web page or you may want to modify it to show a different image. At present there is no WordPress plugin for the code.

This code is also used in the PHP-eSeller application for audio previews.

 

Click here for a full description

 

Click here to download

New version of PHP-eSeller PHP Shopping Cart Digital Goods

PHP-eSeller has been updated to include fully responsive displays on the admin and public shopping cart displays so that they work well on all types of devices, mobile, tablets and desktop.

PHP-eSeller

Some of the cart displays now use the JQuery Datatable system that can be easily modified to change formatting and add in other options.

The JQuery Datatable plugin is well documented, is very extensive and has many features and functions.

Other shopping cart displays are based on the latest version of Twitter Bootstrap which is a framework to ensure that displays work well on different device types. Bootstrap is a well known framework which can be easily changed and modified to work with your particular layout. It is also very well documented with many forum sites.

PHP-eSeller has been completely re-written and is fully compatible with PHP 7 servers.

 

Twitter Bootstrap PHP Cart System

Live Demo

 

Datatable Cart System with HTML Audio Preview and no formatting

Live Demo

 

Datatable Cart System including Twitter Bootstrap

Live Demo

Sell software license keys and pin numbers with a WordPress Plugin

PHP application to sell key codesWordPress PHP-KeyCodes is a WordPress Plugin to sell software license keys, product keys, serial keys, mobile phone codes and any other pre-generated license codes.

The plugin is available from here:

WordPress PHP-KeyCodes

It is similar to the stand alone application PHP-KeyCodes which is described on:

https://www.withinweb.com/phpkeycodes/

If you are a software developer, then using license keys is a way to ensure that your products are safe and secure.  You use this application to automatically distribute the codes after payment from PayPal.

The pin numbers are listed in the database one entry per line.  When a purchase is made, PayPal sends an IPN notification to the plugin which then extracts the first pin number, sends it to the purchaser and then removes that pin number from the list.

The email sent to the purchaser contains the pin number or key code, and you will receive a copy of the email.

The sales history listing also identifies which pin number has been sold to the purchaser.

A local test system is included which allows you to test without connecting to PayPal.

Setting a value in the Lower Limit entry box causes an email to be sent to the administrator when the minimum number of key codes has been reached.

The system has a high level of security in that the license key code will not be sent out until the correct PayPal return code is received with the correct purchase values.  The system checks that the database value is the same as the amount that has been spent, and the currency code is the correct value.  This ensures that any alteration to the button code will not work.

The html code for the PayPal buy now buttons can be easily placed on your WordPress pages.

Installation

The installation into WordPress is the same as for any plugin as is the procedure for upgrades which ensures the plugin is kept up to date.

WordPress PHP-KeyCodes

A guide to sell digital downloads online using a PHP Shopping Cart

Digital goods, electronic goods, digital products or e-goods are anything you can sell that is in a digital format.  The types of products include ebooks, software, website templates, music, videos, license codes, ringtones and pdf tutorials.  The costs of adding a digital item to a shopping system is very small so it can be a great way make a steady income.

A PHP shopping cart with add to cart buttons can be combined with selling digital downloads as used in the PHP-eSeller application.

Here are just a few of the advantages to selling digital downloads using a specially designed PHP shopping cart:

• No inventory – you have no stock levels to maintain so you have no storage problems or supply problems.
Costs are the same to sell one or thousands digital goods.
No postage costs.
• The customer will instantly receive the product using a digital download system.
• The transaction is quickly completed so you have the money in your account straight away.

Some online services specialize in selling digital goods, including invoicing, payment, and delivering the digital copy for you.  In that case, you have to pay some sort of fee to the service provider.

Alternatively you can have a PHP shopping cart on your web site that you install yourself or get a developer to install for you.

Choosing a PHP Shopping cart can be difficult, here’s some guidelines to help you get started:

1) Fees

The payment system that the shopping system uses will have some kind of transaction fees.  This will vary so check what they require and what you can afford. Selling through PayPal is often a convenient way and they use a transaction fee.

2) Integration with your website

Make sure the shopping cart can be integrated with your website.  This may be by modifying a template system or by adding buttons to your existing web pages.

3) Features

Here’s a few of the features that might be helpful:
* Automatic product download
* Have the option of no shipping
* The ability to simultaneous sell tangible goods and e-goods

4) Security

It’s important that the purchase made by the customer is secure. You should expect your customer, after purchasing your product, to receive a secure link to immediately download the purchased item. The link should expire within a certain amount of time.

5) Easy to Use

Make sure the transaction is smooth and easy for your customer, you want to make it easy so that your customer come back for more.

Here’s a great eCommerce guide from my site at:

http://withinweb.com/global/hints_tips_and_tricks/digital_files.pdf

on further concepts for digital downloads.

The product PHP-eSeller is designed to sell digital goods from your web site using a PHP shopping cart system.  PHP-eSeller has a number of carts, and displays which enable you to integrate it to an existing site.

  Click here to go to PHP-eSeller description  

New version of PHP Subscription Manager

The PHP Subscription Manager PHP-SecureArea has been updated to give administration pages that are more responsive and easier to use.

PHP-SecureArea enables you to set up a subscription manager in PHP and a demo version of the new administration pages can be seen at:

https://www.withinweb.com/phpsecureareademos/admin/index.php

The PHP membership script uses the facilities of PayPal to automatically allow customers access to secure areas of your web site after a valid payment has been received.

For full information go to:

https://www.withinweb.com/phpsecurearea/