Renaming a Table

Renaming a table is a very simple task, as you only need to issue the RENAME clause in the ALTER TABLE SQLite command. However, a couple of caveats must be respected. First, you cannot move or copy a table from one database to another, either within the same file or in another database file. While we will look at this feature in more depth in a later chapter, SQLite allows for multiple databases in a single database file. Second, if you have foreign-key constraints defined, you will need to disable them first.

Foreign keys with references will be renamed as a result the renaming, both in the table being altered and in the referenced table. However, any indexes, views, and/or triggers that reference a table that is renamed must be manually modified.

ALTER TABLE main.producer RENAME main.wineries

SELECT sql from main.sqlite_master

SELECT sql FROM main.sqlite_master WHERE type = ‘view’ or type = ‘trigger’

PRAGMA foreign_keys=OFF

DROP viewname
DROP triggername
DROP indexname

PRAGMA foreign_keys=ON

UPDATE main.sqlite_master
SET sql = ‘ new creatw table query’
WHERE type = ‘table’
AND name = ‘your table name’

Adding Columns

Adding columns to a SQLite database table is the second actual way of altering a SQLite database table. By this I mean that the ALTER command is only available for these two use cases. As with the renaming, the Add Column functionality has a few caveats that you need to adhere to in order to add columns to a SQLite database table; otherwise, you can define a new column using the CREATE TABLE statement.

• The new column cannot be defined as a primary key.

• The added column cannot have a date-time default value defined using the following functions: CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP.

• If the column is set to NOT NULL, then you must provide a default value.

• If you are adding a column that will be used as reference to a foreign key, then the column must be set to NULL as a default value.

• Each new column must be added individually.

To add a column to an existing table, you simply need to run a SQL query; for example:

Alter table main.tablename
Add column columnname datatype default value

Here are some actual examples:

Create table main.country(id integer  primary key not null autoincrement, name varchar not null)

Alter table main.country
Add column continent varchar null

Alter table main.country
Add column population integer null

Once the query is executed using sqlite3_exec or sqlite3_prepare_v2, sqlite3_step, and sqlite3_finalize, the alterations will be completed.

Re-indexing a Table

The third type of modification you can do to a table is to re-index its index. You can specify a single index to rebuild, or all of them, if there is more than one in a table. Likewise, if you have a collation sequence to arrange data in a database, you can re-index the collation sequence, and all indexes associated with the collation name will be re-indexed.

Re-indexing an index or collation sequence is accomplished using the REINDEX command followed by the schema name or collation name, as follows:

REINDEX collation-name
REINDEX main.table_name
REINDEX main.table_index_name

It is always useful to rebuild an index on a regular basis or after loading, reloading, or deleting a dataset from a table. This will also ensure that any unused space is removed and the sequence of the index is optimized, thus ensuring the fast data access.

Binary

The BINARY collation sequence algorithm uses the memcmp() C function to compare two text values regardless of string encoding. memcmp() compares the first byte-area size of two strings. The BINARY collate option uses comparison operators (==, <, >, !=, IS, IS NOT, =>, <=) to compare two values.

NoCase

The NoCase collation sequence type compares two text values by converting the 26 ASCII uppercase characters into their lowercase equivalents. Note that it only compares ASCII because full UTF conversion would make the tables manageable.

Rtrim

Lastly, the RTRIM type is the same as BINARY except that it first trims any trailing spaces to the text values to be compared.

To use the collating sequence, you would add COLLATE to the column definition; for example:

CREATE TABLE Strings(
Id INTEGER PRIMARY KEY,
String1, //defaults to binary
String2 COLLATE BINARY,
String3 COLLATE NOCASE,
String4 COLLATE RTRIM
)

sqlite3_create_collation

Of course, you can create your own collation sequence by implementing the sqlite3_create_collation function . The function has three variants, whose logic is provided here:

int sqlite3_create_collation(
  sqlite3,
  const char *zName,
  int eTextRep,
  void *pArg,
  int(*xCompare)(void*,int,const void*,int,const void*)
);

The sqlite3_create_collation has five arguments. The first is the pointer to the database connection. In Swift this is represented by a COpaquePointer. The second argument, const char *zName, is the name of the collation sequence module. The third, eTextRep, is the text encoding of the string callback function and must implement one of the following types:

• SQLITE_UTF8

• SQLITE_UTF16LE

• SQLITE_UTF16BE

• SQLITE_UTF16

• SQLITE_UTF16_ALIGNED

The next argument, *pArg, is an application pointer for the first argument of the callback function. The last argument is the callback function. sqlite3_create_collation_v2 is similar to the first except that it provides a Destroy argument, and sqlite3_create_collation16 provides the functionality to create collation sequences in a native 16 bits. See the following:

int sqlite3_create_collation_v2(
  sqlite3*,
  const char *zName,
  int eTextRep,
  void *pArg,
  int(*xCompare)(void*,int,const void*,int,const void*),
  void(*xDestroy)(void*)
);
int sqlite3_create_collation16(
  sqlite3*,
  const void *zName,
  int eTextRep,
  void *pArg,
  int(*xCompare)(void*,int,const void*,int,const void*)
);

All three functions can add, modify, and delete collation sequences from a SQLite database. In Swift these functions are implemented in the sqlite3.h header file as follows:

sqlite3_collation_needed
sqlite3_collation_needed16

They both take the same arguments as the versions in C. These functions are abbreviated versions of the former functions and are accessible as well through the standard SQLite C API. You would need to define these functions as COpaquePointers, UnsafeMutablePointer, Int32, and UnsafePointer<Int8 > respectively. For the 16-bit version, the last argument would be a 16-bit UnsafePointer rather than an UnsafePointer<Int8 > .

Deleting Tables

DROP TABLE schema.table_name

Deleting Views

DROP VIEW schema.view_name

Deleting Indices

DROP INDEX schema.index_name

Deleting Triggers

DROP TRIGGER schema.trigger_name

Deleting Collation Sequences

Custom collation functions are deleted when you invoke the Destroy argument or when the database connection is closed using the sqlite3_close function.

The JSON Extension

The JSON functions , officially known as the json1 extension, are a fairly new addition to SQLite. They aren’t available with the standard API. You need to install them manually if you need to use them. These functions are loaded at run-time. Using the JSON functions, you can store and parse data in the JSON format. The extension includes the functions listed here:

• Json(json)

• Json_array

• Json_array_length(json)

• Json_array_length(json. Path)

• Json_extract

• Json_insert

• Json_object

• Json_replace

• Json_remove

• Json_set

• Json_type(json)

• Json_type(json, path)

• Json_valid

• Json_group_array

• Json_group_object

• Json_each

• Json_tree

The JSON functions that have json as a first parameter must be a valid JSON object, a number, a string, or a null value. Number and Null are interpreted as SQLite data types. The PATH argument must be a valid, well-formed path value that begins with $.

To load the json1 extension, you need to implement the loadable interface using sqlite3_load_extension, which is available in the C API through the bridge. You also have the sqlite3_enable_load_extension, which can disable the loading of extensions to prevent security leaks. See the following:

                sqlite3_load_extension                                  (db: COpaquePointer,zFile: UnsafePointer<Int8>, zProc: UnsafePointer<Int8>, pzErrMsg:UnsafeMutablePointer<UnsafeMutablePointer<Int8>>)

                sqlite3_enable_load_extension                                  (db: COpaquePointer, onoff: Int32)

The extension functions must be called when the database is open and running so that the extensions can be loaded as needed.

The first argument is the pointer to the SQLite database engine. The second argument is the file for the shared library. For the json1 extension, the file is not included in the default sqlite3.dylib library. The third argument is the entry point for the extension, while the fourth is a pointer for the error message:

var db:COpaquePointer?=nil
let lib_file: UnsafePointer<Int8>? = nil
let proc: UnsafePointer<Int8>?=nil
let err_json_msg:UnsafeMutablePointer<UnsafeMutablePointer<Int8>>? = nil

func load_extension()->Void{
      sqlite3_load_extension(db, lib_file, proc, err_json_msg)
    }

I have created a reference project using the sqlite3.c and sqlite3.h files from the actual code instead of the Xcode-supported sqlite3.dylib library.

The project is in GitHub ( https://github.com/kevlangdo/load_sqlite_json_extension ) and is experimental. I haven’t tested it, and it is outside of the context of this book, but it gives an example of how to load the json extension in an iOS project.

Creating Functions using Swift

Other than the SQLite functions included with the API and the extensions, you can create your own and attach them to your database. These extensions are written in C.

For the next iOS app project, the Wineries, I will need a function that can convert liters into ounces and vice versa. The code must be written in C using, in part, the SQLite API to convert values to and from SQLite.

SQLite values are as follows:

SQLITE_API const void *SQLITE_STDCALL sqlite3_value_blob(sqlite3_value*);
SQLITE_API int SQLITE_STDCALL sqlite3_value_bytes(sqlite3_value*);
SQLITE_API int SQLITE_STDCALL sqlite3_value_bytes16(sqlite3_value*);
SQLITE_API double SQLITE_STDCALL sqlite3_value_double(sqlite3_value*);
SQLITE_API int SQLITE_STDCALL sqlite3_value_int(sqlite3_value*);
SQLITE_API sqlite3_int64 SQLITE_STDCALL sqlite3_value_int64(sqlite3_value*);
SQLITE_API const unsigned char *SQLITE_STDCALL sqlite3_value_text(sqlite3_value*);
SQLITE_API const void *SQLITE_STDCALL sqlite3_value_text16(sqlite3_value*);
SQLITE_API const void *SQLITE_STDCALL sqlite3_value_text16le(sqlite3_value*);
SQLITE_API const void *SQLITE_STDCALL sqlite3_value_text16be(sqlite3_value*);
SQLITE_API int SQLITE_STDCALL sqlite3_value_type(sqlite3_value*);
SQLITE_API int SQLITE_STDCALL sqlite3_value_numeric_type(sqlite3_value*);

To create a SQLite function for Swift, you need to follow these steps:

• As shown in Figure 4-1, add a C file using the C File template under the iOS categories. For my function, I will name it sizeconverter because it will convert volume from liters to ounces and vice versa. Xcode will ask if you want to add a header file; say yes.

  

  Figure 4-1. C File template

• Next, you need to add C logic to the C file and import whatever C library into the header. You can also define your functions' signatures in the header file, which I am doing for this example. For example, I am adding the #include directive for the sqlite3.h header file.

• Then, you need to add the C file to your bridge file using the #import statement (see the code that follows).

• As shown in Figure 4-2, you need to add your header file to Build Phases > Compile Sources .

  

  Figure 4-2. Build phases

The code for the function (sizeconverter.c) is shown here, along with the header file and the changes to the SQLite3Bridge:

//
//  SQlite3Bridge.h
//  Db Mgr
//
//  Created by Kevin Languedoc on 2016-05-20.
//  Copyright © 2016 Kevin Languedoc. All rights reserved.
//

#ifndef SQlite3Bridge_h
#define SQlite3Bridge_h

#endif /* SQlite3Bridge_h */

// Add this code to import the sqlite3 header. The code above is supplied by the template
#import <sqlite3.h>
#import "sizeconverter.h"
The Adjust SQLite3Bridge Header File                                                                  
//
//  sizeconverter.h
//  Db Mgr
//
//  Created by Kevin Languedoc on 2016-07-03.
//  Copyright © 2016 Kevin Languedoc. All rights reserved.
//

#ifndef sizeconverter_h
#define sizeconverter_h

#include <stdio.h>
#include <sqlite3.h>

static void sizeconverter(sqlite3_context *context, int argc,   sqlite3_value **argv);

#endif /* sizeconverter_h */
The sizeconverter                                                     header file
//  sizeconverter.c
//  Db Mgr
//
//  Created by Kevin Languedoc on 2016-07-03.
//  Copyright © 2016 Kevin Languedoc. All rights reserved.
//

#include "sizeconverter.h"
#include "SQlite3Bridge.h"

static void sizeconverter(sqlite3_context *context, int argc,   sqlite3_value **argv)
{

    double result = 0.0;
    const char *liter;
    const char *ounce;
    const char *us;
    const char *uk;

    us = "us";
    uk = "uk";
    liter = "l";
    ounce = "o";

    if (argc==3) {
        double from = sqlite3_value_double(argv[0]); // original volume
        const unsigned char *to = sqlite3_value_text(argv[1]); // liters or ounces
        const unsigned char *country = sqlite3_value_text(argv[2]); // us or uk

        const double us_uom = 33.8140226; // 1 Liter = 33.8140226 Ounces [Fluid, US]
        const double uk_uom = 35.195079; // 1 Liter = 35.195 079 Ounces [UK]

        if (country == (const unsigned char*)us && to == (const unsigned char*)liter) {
            result = from * us_uom;
        }else if (country == (const unsigned char*)uk && to == (const unsigned char*)liter){
            result = from * uk_uom;
        }else if (country == (const unsigned char*)us && to == (const unsigned char*)ounce){
            result = from / us_uom;
        }else if (country == (const unsigned char*)uk && to == (const unsigned char*)ounce){
            result = from / uk_uom;
        }

    }
    return sqlite3_result_double(context, result);
}

This is a simple C function that takes three arguments: the original volume, the target unit of measure, and the country, which can be United States or United Kingdom, since these have different liter conversion values because their ounces are based on either the Imperial system or the American system. Most other countries use the metric system.

With the function done and configured, we only need to use it in the Db Mgr app and attach it to the database.

Using Functions in a SQLite Database using Swift

Using custom SQLite functions requires using sqlite3_create_function. The first argument is the SQLite database pointer, which is a COpaquePointer; the second argument is the name of the function as a UTF-8 encoding string; the third parameter is the number of input parameters; and the fourth parameter is the text encoding type the function prefers. In my case, it is UTF-8, which is pretty standard, but it will accept any of the supported encodings. The fifth parameter is an arbitrary pointer that allows access through the function using the sqlite3_user_data() function . The last three arguments are pointers that implement the function or aggregate:

 func createSQLiteFunction()->Enums.SQLiteStatusCode{
        let funcname:String = “sizeconverter”
        return Enums.SQLiteStatusCode(rawValue:sqlite3_create_function(db, funcname.cString (String.encoding.utf8)!, 3, SQLITE_UTF8, nil, sizeconverter, nil, nil))!
}

Once the function is connected, you can use the function elsewhere in your INSERT, SELECT, or UPDATE queries, which we will look at later in this book in the chapters covering those subjects.

Foreign_key_check

PRAGMA schema.foreign_key_check;  
PRAGMA schema.foreign_key_check(table-name);

The foreign_key_check PRAGMA runs against the database or table to check for any foreign-key violations. It returns a row for each violation, so you could use the sqlite3_step function to retrieve the returning value. The resulting value has four columns, as follows:

• The name of the name containing the reference.

• The second value is the rowindex where the violation occurred.

• The third column is the table that the foreign key refers to.

• The last column is the foreign-key name.

Foreign_key_list

The Foreign_key_listPRAGMA returns the list of foreign keys in a database. It returns one row for each foreign-key constraint.

PRAGMA foreign_key_list(table-name)

Integrity_check

The integrity_checkPRAGMA does a sanity check on the entire database looking for missing indexes, broken records, out-of-order records, missing pages, and UNIQUE or NOT NULL violations, among other verifications. The results are returned as a single column describing the issue. If no problems are found, the returning column will contain the “OK” value only. The N refers to the max number of errors to return. It defaults to 100.

PRAGMA schema.integrity_check;  
PRAGMA schema.integrity_check(N)

Automatic_index

The automatic_indexPRAGMA allows the developer to query, set, or clear the automatic index on a table.

PRAGMA automatic_index;  
PRAGMA automatic_index = boolean;

Busy_timeout

This PRAGMA expression enables you to get the current timeout or set the busy timeout for query execution.

PRAGMA busy_timeout;  
PRAGMA busy_timeout = milliseconds;

Shrink_memory

This PRAGMA expression frees up memory as much as possible. This is handy for large databases or long-running queries. This is similar in concept to a garbage collector.

PRAGMA shrink_memory

Auto-vacuum

This PRAGMA removes empty space from the page_file, thus freeing up memory and shrinking the size on disk.

PRAGMA schema.auto_vacuum;
PRAGMA schema.auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;