Category: Code

Software Development

Pretending that SQLite has Stored Procedures and Functions

SQLite is marvellous. The fact that it doesn't have SQL syntax for stored procs and functions is usually not a handicap because it has an interface for the consuming application to register functions, which means you get to write your functions in your preferred programming language. Win all round.

But sometimes you do wish you could do it in SQL.

The lack of Stored Procedures is usually ok—you can just use scripts. Variables are easy to do: create a one-row temporary table and call it args or var. Working around the lack of Functions seems harder but in fact, you can program functions with Views. You can use CTEs in a View definition, so you can build up complex calculations. And CTEs allow recursion so you have Turing completeness.

As an example, the Exponent function as a View:

Drop Table if Exists Args ; Create Table Args as Select 5.5 as Base, 4 as Exponent ;

Drop View If Exists Power;
Create View Power As
    WITH RECURSIVE pow(exponent, exponent_remainder, base, result) as (
        --FIRST EXPRESSION
        SELECT exponent, exponent-1 , base, base
        FROM Args

        union all
        --SECOND EXPRESSION
        select Args.exponent, pow.exponent_remainder -1, pow.base, pow.result * pow.base
        from Args
        join pow on Args.exponent = pow.exponent
        where pow.exponent_remainder >= 0
    )
    select pow.result
    from pow
    where pow.exponent_remainder = 0;

and now you ‘call the function’ with:

Update Args set Base=2.5, Exponent=5; Select Result from Power;

The elements of the workaround are:

  1. A one-row table for function arguments
  2. A view which can refer to the arguments table and do the calculation. Since you can use CTEs to do recursion, you could in principle programming anything this way.

In similar style, here's an Exponential function which lets you specify how many significant digits you want the result to, default to about 7 digits of accuracy. This time we call the Args (X,Y,Z,p4,p5,…)

Drop Table if Exists Args ;
Create Table Args as Select 1 as X, 2 as Y, 3 as Z, 4 as p4, 5 as p5, 6 as  p6;

Drop View If Exists Exp;
Create View Exp As
    WITH RECURSIVE exp1(X, N, term, approx, accuracy ) as (
        --FIRST EXPRESSION
        SELECT X, 1, X, 1+X, Max(Min(Y, 1),0.00000000000000001)   FROM Args

        Union All
        --SECOND EXPRESSION
        Select X, N + 1, term * X / (N + 1), approx + term * X / (N + 1), accuracy
        From exp1
        Where  term / approx > accuracy Or N <3
    )
    Select approx as Result From exp1 Order By N Desc Limit 1;

And then:

Update Args Set X=22.0, Y=0.00000000000001;
Select * from Exp;
#
# > 3584912846.1315813 # Exp(22) correct to 14 digits

Links

Installing and using SQLite extensions on macOs (and maybe Windows & Linux too)

Installing and using SQLite extensions on macOs

SQLite is brilliant and … lite. Deliberately. Even for maths functions the view is “it's really, really easy to add extensions and we don't want to bloat the core.”

This is fine if you are used to C development. This page is for if you aren't. The first section is specific to macOs, which is the “hardest” case. Linux and Windows are easier and you can skip the first section.

1. For macOs: Be able to load SQLite extensions

  1. Install SQLite from homebrew, because the apple-shipped SQLite will probably not allow you to load extensions. If you try to load an extension, you will just get a "not authorized" error. 
    brew install sqlite

    Note that homebrew tells you that it has not put sqlite in the path because the apple-shipped version is in the path. Fix this either by editing your profile file to extend the path, or else by adding a link to the updated sqlite3 in /usr/local/bin:

    ln -s /usr/local/opt/sqlite/bin/sqlite3 /usr/local/bin/
  2. Now if you start sqlite3 you should see a new improved version number: 
    SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.

2. Download a Loadable Module

  1. We'll take Spatialite as a great example. Get the .dylib file (macOs) or .dll (for Windows or Linux) or .so file (for Linux) for your extension and confirm it is somewhere you can find it. For Windows the homepage has links to 7z archive file containing the loadable module and sqlite.exe too. For macOs: 
    brew install libspatialite
# ... lots of homebrew output ...

ls /usr/local/lib/*spatialite*
# /usr/local/lib/libspatialite.dylib
# /usr/local/lib/mod_spatialite.dylib
# /usr/local/lib/libspatialite.7.dylib
# /usr/local/lib/mod_spatialite.7.dylib
  2. Add the directory /usr/local/lib to your LD_PATH if it isn't already there. (The alternative to this step is, in the next step, to use the absolute path to load the module.)
    • echo $LD_PATH #Check if you already have it
    • export LD_PATH="$LD_PATH:/usr/local/lib"
    • Edit your profile file to make the change repeatable. For instance:
    • zsh : echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.zshrc
    • bash: echo 'export LD_PATH="$LD_PATH:/usr/local/lib"' >> ~/.bash_profile
    • fish: echo 'set -x LD_PATH "$LD_PATH:/usr/local/lib"' >> ~/.config/fish/config.fish
  3. Start sqlite again and now load the module. There are two ways to do it, either should work:
    • Either with .load :
      .load mod_spatialite.dylib #*if you set LD_PATH above*
      .load /full/path/to/the/file/mod_spatialite.dylib # *if you didn't*
    • Or with Select:

      Select load_extension('mod_spatialite');

    Either way you should now be able to select the spatialite version number:

    select spatialite_version() ;
# 4.3.0a

3. Other Extensions

Spatialite was the easy example because there are pre-compiled binaries available for all platforms. Other extensions mostly exist as .c files. But good news! Many of them are single files and easy to compile and install.

  1. Download some extensions, usually as a single .c file

    For instance, look at https://www.sqlite.org/contrib and notice extension-functions.c at the bottom of the page. Let's install this, which has common maths, string and stats functions functions such as power(), exp(), reverse(), stdev() :

  2. Having downloaded the .c file, compile it in your download directory.

    For macOs:

    gcc -g -fPIC -dynamiclib extension-functions.c -o extension-functions.dylib

    For Windows, use one of:

    gcc -g -shared YourCode.c -o YourCode.dll
cl YourCode.c -link -dll -out:YourCode.dll

    For linux/*nix:

    gcc -g -fPIC -shared YourCode.c -o YourCode.so
  3. Copy it to your lib directory and use it. e.g. for macOs: 
    mv extension-functions.dylib /usr/local/lib/
sqlite3
> .load extension-functions.dylib
Select sqrt(10), log(exp(2)) ;
# sqrt(10)          log( exp(2) )
# ----------------  -------------
# 3.16227766016838  2.0

Even more extensions

There are more extensions in the SQLite repository which you can download and install from the .c file in the same way. https://sqlite.org/src/file/ext/misc/ includes files for json & csv formatting, regex, uuids, and other requirements.

If you have a not-latest version of SQLite installed, you may need the advice on Forums - How do I compile the newest .... I ended up with:

for f in *.c
  set b (basename -s .c $f)
  gcc -g -fPIC  -DSQLITE_INNOCUOUS=0 -DSQLITE_VTAB_INNOCUOUS=0  -DSQLITE_VTAB_DIRECTONLY=0  -DSQLITE_SUBTYPE=0 -dynamiclib $f -o $b.dylib
  rm -r $b.dylib.dSYM
end

4. Making it permanent-ish

You can use the file ~/.sqliterc to permanently include your loaded functions. Here's mine:

.headers ON
.mode column
.load extension-functions.dylib

For the same functionality on another machine, you must replicate these steps. The sqlite developers' solution would be, compile your own distribution of sqlite with all the bits you want.

References

fish shell quickstart for converting bash scripts

After some years of bash and PowerShell, and some hours of using fish, I've realised that expansion & predictive typeahead are good features in a shell, whereas “be a great programming language” is less important than I thought: because there is no need to write scripts in the language of your shell.

Fish has slicker typeahead and expansions than bash or even PowerShell. But to switch to a fish shell, you do still have to convert your profile & start-up scripts. So here's my quick-start guide for converting bash to fish.

  • Do this first: at the fish prompt type help. Behold! the fish documentation in your browser is much easier to search than man pages are.
  • Calmly accept that fish uses set var value instead of var=value. Roll your eyes if it helps.
  • Use end everywhere that bash has fi, done, esac, braces {} etc. e.g. function definition is done with function ... end. The keywords do and then are redundant everywhere, just remove them. else has a semicolon after it. case requires a leading switch(expr).
  • There is no [[ condition ]] but [ ... ] or test ... work. Type help test to see all the file and numeric tests you expect, such as if [ -f filename ] etc. string and regex conditionals are done with the string match command (see below). You can replace [[ -f this && -z that || -z other ]] with [ -f this -a -z that -o -z other ] but see below for how fish can also replace || and && constructions with or and and statements.
  • But first! type help string to see the marvels of proper built-in string commands.
  • Replace function parameters $*, $1, $2 etc with $argv, $argv[1], $argv[2] etc. If that makes you scowl, then type help argparse. See! That's much better than kludging about in bash.
  • Remove the $ from $(subcommand) leaving just (subcommand). Inside quotes, take the subcommand outside the quote: "Today is $(date)" becomes "Today is "(date). (Recall that quotes in bash & fish don't work at all like quotes in most programming languages. Quote marks are not token delimiters and a"bc"d is a valid single token and is parsed identically to each of abcd , "abcd", abc'd').
  • Replace heredocs with multi-line literal strings and standard piping syntax. However, note that if you pipe or read to a variable, the default multiline behaviour is to split on newline and generate an array. Defeat this by piping through string split0 – see https://fishshell.com/docs/current/index.html#command-substitution

Search-and-replace Script Snippets

Here is my hit-list of things to search and replace to convert a bash shell to fish. These resolved almost all of my issues in converting a few hundred lines of bash script to fish.

FromToNotes
var=valueset var value
export var=valueset -x var value
export -f functionnameredundant.Just remove it
alias abbr='commandstring'(no change)alias syntax is accepted as an abbreviation for a function definition since fish 3
command $(subshell commmand)
command `subshell commmand`		command (subshell command)
OR
command (subshell commmand | string split0)		Just remove the $ but keep the ()

See below for when you want to add string split0
command "$(subshell commmand)"command (subshell command)Remove both the $ and the quotes ""to make this work
if [[ condition ]] ; then this ; else that ; fiif [ condition ] ; this ; else ; that ; endSee below for more on Fish's multine and and or syntax.
if [[ number != number ]] ; then this ; else that ; fiif [ number -ne number ] ; this ; else ; that ; endSee below for more on Fish's multine and and or syntax.
while condition ; do something ; donewhile condition ; something ; end
$*$argv
$1, $2$argv[1], $argv[2]But see help argparse
if [[ testthis =~ substring ]] if string match -q '*substring*' testthisstring match without -r does glob style testing
if [[ testthis =~ regexpattern ]] if string match -rq regexpattern testthisstring match with -r does regex testing
[ guardcondition ] && command
[ guardcondition ] || command		works as isBut see or and and below for when it's more complex
var=${this:-$that}if set -q this ; set var $this ; else ; set var $that ; end
cat > outfile <<< "heredoc"
cat > outfile <<< "multiline … heredoc"		echo "multiline … heredoc" | cat > outfile no heredocs, but multiline strings are fine
NB printf is better than echo for anything complicated, in any shell.
if [[ -z $this && $that=~$pattern ]]if [ -z $this ] ; and string match -rq $pattern $that ;
content=$(curl $url)set content (curl $url | string split0)without the pipe to string split0, content will be split on newlines to an array of lines.

Fish's multine and and or syntax

Fish has a multiline and and or syntax that may be clearer than && and || in both conditionals and guarded commands. It is less terse.

[ condition ]

and do this

or do that

That said, && and || are still valid in commands :

[ condition ] && do this || do that

Other gotchas

  • You may have to read up on how fish does parameter expansion, and especially handling spaces, differently to bash.
  • Pipe & subcommand output to multiline strings or arrays: set x (cat myfile.txt) will set x to an array of the lines of myfile.txt. To keep x as a single multine string, use string split0 : set x (cat myfile.txt | string split0)

Official tips for new fishers:

See the FAQ at https://fishshell.com/docs/3.0/faq.html

Other Examples of Software Architecture with UML (2002)

This example is from 2002, by IBM and RDA Corp, published at https://www.ibm.com/developerworks/rational/library/content/03July/2000/2000/2000_RDAEStoreAbbrevSAD.doc.

I'm happy with this style of S.A.D, but there some things I would change if I was in an organisation that produced this kind of document. Broadly, I would related the views back to the structure of the 4+1 approach, rather than simply listing 6 apparently unrelated views.

  1. Put the “+1” back in, in the form of a Use Case and Context View.
  2. Add a Domain Model & Glossary to to the Logical View. To my mind, these are the centrepiece of the Logical View.
  3. Notice that the Security View is more a narrative of how the requirement is met than it is a model of the system. I would follow Rozanski & Wood's terminology and call it the Security Perspective, and/or put it in a Quality Attributes section after the views.
    Also, I don't believe that Security is the only important Quality Attribute. I'd add sections for them.
  4. Make the Data View a child view of the Logical View. In this example document, physical database details have been explicitly excluded. This suggests to me that the database is owned by a different team to the team developing this system, and the Data View is effectively the “interface” provided by the database team to the team developing this system. In that case, I would show the database on a Context diagram as an external dependency.
  5. I would rename Implementation View back to Development View, and use it to
    1. link to to the CI/CD pipeline, which in my mind entirely replaces all technical detail that you might put in the Implementation View.
    2. Discuss the human factors, team organisation and ownership. The interesting question about the top-level packages is, who owns them?

“

1       Introduction

The architecture of a software system requires six distinct views, each view focusing on different aspects of the system. Its purpose is to communicate the major components of the system, how it is structured, the system process flows, and major interfaces. From a high level, the goal is to examine the system from several different perspectives, each providing a different “view” in order to capture all critical system features. A brief description of the six architecture views is provided as follows:  

Deployment View – This view documents the physical topology of the system modeled in the Deployment Model.  It includes each computer in the implementation and describes how they are interconnected.  The configuration for each node is also specified – Operating system, databases, Commercial off-the shelf (COTS), and custom applications.

Logical View – The logical view documents the Design Model, which defines the layers of the application and the primary classes within each layer.  The system architect identifies patterns of functionality and creates common mechanisms to provide this functionality to several areas across the application. 

Data View – Classes in the logical view are classified as transient or persistent.  The persistent classes are mapped to structures on disk, usually into a combination of rows in a relational database. An entity-relationship data model describes the database schema.  This view also communicates how the Object-Oriented classes are mapped to the relational tables.

Process (Concurrency) View – This view focuses on the concurrency aspects of the system and how they contend for shared resources (i.e., transaction semantics, etc.). The process view documents the independent threads of execution within the system and describes how they communicate.  It also lists the resources in contention by these threads and the transaction model for maintaining integrity with these resources.

Implementation View – This view maps the classes in the Logical View to physical source files and combines the files into deployable components.  The implementation view also tracks the dependencies among the components.

Security View – This view focuses on how the system identifies end users, grants authorization to them based on their identity, ensuring integrity of the system and of the data and properly tracking and auditing of system activity.

Note: This is a brief overview of the architecture.  The complete eStore SAD is included in the download.

2       Deployment View

The configuration view presents the topology and its physical and logical connections.

3       Logical View

The logical view presents the core design of the system.  It presents the primary classes that collaborate to implement the system functionality.  It contains the following subsections:

3.1      Three Software Layers

The application is structured along three distinct layers – UI Layer, Business Layer and Data Layer.


UI Layer: Responsible for authentication, presentation and managing session state.

Business Layer: Exports the business objects defined in the Solution Model class analysis.  Maintains no knowledge of presentation.  Insulates the UI Layer from database design.  Responsible for complex business rule logic.

Data Layer: Responsible for managing persistent data and transactions.  Maps business objects to physical relational tables.  Responsible for data integrity, transactions and data intensive business rules such as unique name on a column.

3.2      Business Layer Interface

The following diagram illustrates the classes and their relationships exported from the business layer to the presentation layer.

Figure 1: UI Session Stack Class Diagram

 

3.3      Business Layer Implementation

Pet Store’s business layer is implemented by leveraging the services provided by IT FlightPlan’s Software Library.  The business classes exported by the business layer are either BusinessClasses or ReferenceClasses.  IT FlightPlan’s Software Library exports a BusinessClass base, abstract class and an IReferenceClass interface.  The following diagram shows how the classes in the business layer are implemented – the session BusinessClasses inherit from BusinessClass while the cached classes implement the IReferenceClass interface.

 

4       Data View

 

The Database Model is presented as a logical view, physical view and data dictionary.  The logical data model is included in this overview.

 

 

4.1      E/R Model

4.1.1     
Logical View

5       Process View (Concurrency)

The Process View focuses on the parallel processing aspects within the system.  eStore deals with concurrency in two distinct areas -

  1. The CPU/process/thread design

2.     Transaction design for shared resources,

A.    Records in the PetStore database – single data source

B.     Updates to PetStore database and the Credit Authorizer

C.     Shared objects in memory

5.1      CPU/Process/Thread Design

This diagram shows the major threads and context switches involved with processing a web request.  IIS allocates a pool of worker threads to process HTTP requests and posts.

5.2      Transaction Design

 

5.2.1      PetStore Database transactions

All transactions are encapsulated within a call from the BusinessLayer to a stored procedure.  Any SQL errors within a stored procedure force a rollback and the error is raised to the BusinessLayer. 

 

Account data integrity is maintained through optimistic concurrency.  A unique version timestamp is returned with each head Account object. 

 

5.2.2      Updates to PetStore database and the Credit Authorizer

There is one point in the application where there are two distinct data sources, so this leads to a critical region in the code.  ConfirmAddresses invokes CreditAuthorizer to charge the customer’s credit card.  If successful, it returns a credit authorization code, which is stored in the Payment when the Order is saved.  We have an exposure in the case where the charge is successful, but Order.save() fails.  CreditAuthorizer does not support two-phased commit transactions, so RDA Pet Store will continue to reconcile its daily charges with the Credit Authorizer at the close of each business day. 

5.2.3      Shared Objects in Memory

The static reference objects that are shared across all sessions have only one instance per web server.  All sessions refer to this single instance.  Since these classes are loaded when the application starts and are not modified, semaphores are not required for synchronization.

6       ImplementationView

The implementation view describes how the software is physically contained in files and how files combine to form components.  At the highest level, the application is comprised of packages.

 

The 3 layers outlined in the Logical View (UI, Business and Data) are implemented with the above packages as follows –

UI Layer:

  • RDAEStore[CS, VB]  - all aspx, code behinds, images and Pet Store UI utilities

·       ITFlightPlanSWLib  - generic UI utilities to bind/unbind attributes to UI controls

Business Layer:

  • BusinessLayer[CS, VB]  - all of the Pet Store business classes

·       ITFlightPlanSWLib packages – reusable mechanisms the provide persistence and error handling\

Data Layer:

·       DataLayer – the Pet Store tables, stored procedures and reference data

·       ITFlightPlanSWLib – Generic wrapper interface to access ADO

7       Security View

The security view describes how the system implements the security requirements specified in the Detailed Spec.  Security design is presented in the following sub-areas.

ü  User Identification & Authentication – How does the system identify users and verify it is them?

ü  Authorization – Once authenticated, who is allowed to do what?

ü  Data Integrity and Privacy – Ensure the integrity of the data is not compromised.

ü  Non-repudiation and Auditing – Ensure the end user cannot cover up their tracks.  Record access to the data so we know who’s done what.

7.1      User Identification & Authentication

  • eStore uses ASP.Net Forms authentication to authenticate users.
  • UserIDs and passwords are stored in the RDAPetStore database.  Passwords are first “sha1”, 40-byte encrypted before being stored in the database.
  • The eStore application itself authenticates with its own SQL Server userID.  The ID and password is stored in the web.config file.  The only the database objects granted to the eStore userID are execute grants to the eStore stored procedures.

7.2      Authorization - Data Entitlement

eStore uses a single stored procedure – getAccountForUserID – to fetch the account information for a particular user and stores it in the session stack. 

Since the authenticated user contained in the session stack, eStore relies on the integrity of .Net sessions to ensure that users can only access their own accounts. 

7.3      Data Integrity and Privacy

V1.0 of RDA Pet Store provides minimal protection of data.  Passwords are encrypted before they are stored in the database.

Extending Pet Store to be more secure, we would use SSL to transmit password and credit card information.

7.4      Non-repudiation &Auditing

Each table in the database provides last modified by and last modified timestamp columns.  The authenticated userID parameter on the save stored procedure is set in these columns on all inserts and updates.