SQLdeLite version 2. iOS Compatible! Open Source.

SQLdeLite
by 1701 Software, Inc.
http://www.1701software.com

SQLdeLite is an open source library that allows you to speed up your database development with Xojo.

Read more and download from GitHub at: https://github.com/1701software/SQLdeLite

Highlights:

  • Single drop in module that speeds up your development.
  • Automatically uses SQL prepared statements mitigating SQL injection attacks and speeding up database performance.
  • Quickly and easily create SQL queries by using variables representing the properties of your objects. These variables are converted to the bounded parameters in prepared statements.
  • No more string concatenation for your SQL!
  • Dynamic objects that allow for any number of properties without having to define each one in the IDE. Now it’s much faster to handle your query parameters and results.
  • Built on top of the new Xojo framework.
  • Full support for iOSSQLiteDatabase on iOS! You can use the same business logic between projects despite using different database backends.
  • Full support for all Xojo supported databases. Enable databases that require plugins by setting the appropriate constant to True (example: PLUGIN_MSSQL_ENABLED).
  • Full support for cubeSQL. Make sure to enable support by changing the PLUGIN_CUBESQL_ENABLED constant to True.
  • Valentina database is also supported by virtue of the SQLdeLite.ParameterizeSQL() method. This converts your SQL query into a Valentina compatible query with bound parameters.

The library does not expose any new database classes nor requires the usage of custom database adapters (as in “Active Record” from BKeeney Software). The module extends the built-in Xojo databases classes and provides you with two additional methods. These methods replace the default SQLSelect() and SQLExecute() methods for your database of choice.

Why use over ActiveRecord?

  • ActiveRecord can only load a record via it’s primary key which is forced to be an integer. It has the ability to load an object from a RecordSet which SQLdeLite can also do automatically (see the Advanced Features topic below).
  • ActiveRecord is not available on iOS. SQLdeLite runs everywhere Xojo runs: Console, Desktop, Web, and iOS without any modifications.
  • ActiveRecord requires you to use their database specific adapters. SQLdeLite extends the Xojo native databases .
  • ActiveRecord requires code generation using the commercial ARGen product or hand building your database classes. SQLdeLite can use classes or dynamic objects via SQLdeLite.Record.
  • SQLdeLite is HALF the size contained inside a single module.
  • SQLdeLite is built on top of the new Xojo framework and ready for the future.

=========================

Read more and download from GitHub at: https://github.com/1701software/SQLdeLite

Phillip, SQLdeLite looks interesting. I see you worked around the iOSSQLiteDatabase limitation with a variable number of query parameters with the brute force method of using 99 else if statements. Personally, I’d much rather Xojo provide a solution for <https://xojo.com/issue/40319>. This is the ONLY reason why we’ve not migrated ActiveRecord to iOS yet.

You are correct in that SQLdeLite is much lighter than ActiveRecord but you miss out on a bunch of design time features that AR has like code AutoComplete and the ability for the compiler to spot errors for you - at design time. AR will also let you know if you are missing a database column in your class.

Doing all of this at runtime like SQLdeLite is A way to do it. When we designed AR we wanted to catch more errors at design time if we can.

One minor quibble: ARGen can be used for free to generate the data classes. The free version is limited to two tables at a time but the paid version can generate hundreds of data classes and basic user interface for each table in mere minutes saving the developer many hours of tedious coding.

Good luck with SQLdeLite. It’s another great tool in the Xojo universe and it gives developers options. Options are good.

Hey Bob, thanks for taking the time to review SQLdeLite!

For iOS the brute force solution was easy enough. I do agree changing the parameterization to match other databases would be ideal. I included the tool I used to generate the code for iOS so it works well.

One thing to note is ActiveRecord and SQLdeLite have different goals. I used the ActiveRecord pattern extensively when I was programming web applications with Ruby on Rails. In Ruby you had dynamic property access to table columns so it was more akin to SQLdeLite then generating code. I do agree that generating code gives you compile time information but I don’t think that’s as important as it sounds. If you are developing a database application you likely have the schema in front of you. It doesn’t take very long to figure out that you mis-spelled a column.

Where ActiveRecord fails in my opinion is any situation with a complex query. The dependency on an integer based primary key is challenging for complicated datasets. Plus even with ARGen’s lazy loading (which is very nice) you still find yourself in a situation where it’s ALL or NOTHING. Correct me if I am wrong but there is no way to filter the columns that are returned in a query.

So in ActiveRecord you cannot do:

SELECT firstName, lastName, emailAddress FROM Employees

You also cannot do sub-queries or filtering on the JOIN. For instance you might have a situation where you want to filter specific elements of the Join table:

SELECT firstName, lastName, emailAddress, salary FROM Employees JOIN Salaries ON Employees.UUID = Salaries.empUUID WHERE Employees.UUID = ‘UUID’ AND Salaries.salaryEffectiveDate >= ‘2015-01-01’

In ActiveRecord you would be forced to create (or let ARGen generate) two classes with the properties of each table. Then you would have to use the ActiveRecord lazy loading to pull all salary information from the salary table and loop through to find the record that matches your criteria.

This ultimately results in less performant queries. ActiveRecord can only load based on primary key so it loads ALL the columns returned to it. What if I want a dynamic value like:

SELECT firstName, lastName, emailAddress, salary, (SELECT FROM Employees JOIN Salaries ON Employees.UUID = Salaries.empUUID WHERE Employees.UUID = ‘UUID’ AND Salaries.salaryEffectiveDate >= ‘2014-01-01’ AND Salaries.salaryEffectiveDate <= ‘2015-01-01’) AS previousSalary FROM Employees JOIN Salaries ON Employees.UUID = Salaries.empUUID WHERE Employees.UUID = ‘UUID’ AND Salaries.salaryEffectiveDate >= ‘2015-01-01’

In that scenario a new column called ‘previousSalary’ only exists for the life of this query. I would not have a previousSalary property in my Employees class. Of course I could add one but that’s not going to be filled in most of my queries (since it’s not necessary).

Of course ActiveRecord provides a mechanism to load a class based on the results in any arbitrary RecordSet. The problem here is:

  1. No SQL parameterization when you build your own SQL query.
  2. String concatenation leads to errors.
  3. ActiveRecord really did very little for you here.

SQLdeLite solves this by using the dynamic properties like in a dynamic language like Ruby. Now I can pass ANY arbitrary query to SQLdeLite and the results will be filled into my dynamic object (or I can use the RecordSet). There is no need for me to create custom classes to handle more complex RecordSet values or to even define the classes at all. Plus I get all the speed and safety benefits of SQL prepared statements which ActiveRecord completely leaves you on your own to do if loading by the primary key is not sufficient for your needs.

One should not under-estimate the performance penalty of loading more information then you need for a given query.

The really awesome thing here though is that since ActiveRecord facilitates loading a generated class from a RecordSet you can actually use SQLdeLite and ActiveRecord together. Use ActiveRecord as you always did and then use SQLdeLite when you want fast, safe queries of any arbitrary complexity. Best of both worlds.

[quote=286647:@Phillip Zedalis]Where ActiveRecord fails in my opinion is any situation with a complex query. The dependency on an integer based primary key is challenging for complicated datasets. Plus even with ARGen’s lazy loading (which is very nice) you still find yourself in a situation where it’s ALL or NOTHING. Correct me if I am wrong but there is no way to filter the columns that are returned in a query.

[/quote]

Please excuse me if what I write here is too naive…

I don’t use Active record so I am not sure how it works , but I have considered writing a class generation for SQLite only that is sort of half and half (If I understand both SQDelite and Active Record). Where you get some compile time checking but some things are only dealt with at runtime.

As a first pass I envisioned being able to pass in Where and order Clauses when creating an instance of the class. That class would have methods can return records from tables that use parent table entries as foreign keys (as class instances of that table) if called.

It still often would return more data than needed - but only from records you care about.

Of course that still has a lot of limitations as it is basically for single table queries…

What might be able to lessen that is to be able to auto-generate read-only Xojo classes for views again being able to pass in Where and Order Causes

Unfortunately to much else keeps coming up for me to find the time for this.

  • karen

SQLdeLite does allow you to pass in normal objects and use their typed properties. So it kinda supports both models. Ultimately the issue for SQL database binding with prepared statements is knowing the types.

At some point in the SQL generation process you need to know the name of a field, and the type and be able to use those values where appropriate.

The Javascript world has query builders that are more flexible. You could see something like:

var sql = select(firstName, lastName, phoneNumber).from(table).where(lastName = ‘Zedalis’).where(firstName = ‘Phillip’)

That’s a poor example but it kinda shows how you can use methods to construct your query. Javascript is a bit different though in that it has less actual types than SQLite. It doesn’t have much to reason about because it’s completely dynamic so it can figure it all out at runtime. It’s aesthetically pleasing but computationally expensive.

In SQLdeLite it’s essentially a search/replace for properties and SQL variables. So when you type:

SELECT * FROM Employees JOIN Salaries ON Employees.id = Salaries.empId WHERE Salaries.Salary > $Salary

All you need is an object (any object) to have a ‘Salary’ property. SQLdeLite then calculates the appropriate data type for that property and parameterizes your query to protect you from SQL injection. The other benefit to prepared statements is the database query engine can cache the underlying query plan it used to produce the results thus subsequent queries are faster. This varies by database engine.

So Karen to append to your thought process at some point you have to decide what’s more important to you. The compiler letting you know you mis-spelled a column or the ability to write a safe query in seconds.

Remember that pre-mature optimization is a waste of developer time. You can always come back to a SQLdeLite query and optimize it further if the dynamic introspection properties are what’s slowing you down. It’s doubtful though because database I/O is more expensive than CPU time in most environments.

Hi Phillip,
please could you complete the SQLDeLite example with a SQLdeLiteExecute (update/insert) and CreateInsertStatement too.

Thanks

Luciano

p.s.
I’ve tried with Chinook_Sqlite.sqlite database :

Dim _record As New SQLdeLite.Record
_record.LastName = “Tremblay”
_record.Phone = “02-931991”

App.db.SQLdeLiteExecute(“UPDATE Customer SET Phone = $Phone WHERE LastName = $LastName”, _record)

but doesn’t work…

so,
should be a bug :

Dim _record As New SQLdeLite.Record

_record.Phone = “Tremblay”
_record.LastName = “02-931991”

App.db.SQLdeLiteExecute(“UPDATE Customer SET Phone = $LastName WHERE LastName = $Phone”, _record)

it work. seem wrong parameters order.

[quote=286735:@luciano monti]

it work. seem wrong parameters order.[/quote]

My bad, I had parameter positioning in a different project. I’m using embedded classes in the module to keep it tidy and easy to drop into other projects. The downside is you can’t use external items so I had a couple different versions scattered around.

You should be all set.

[quote=286708:@luciano monti]Hi Phillip,
please could you complete the SQLDeLite example with a SQLdeLiteExecute (update/insert) and CreateInsertStatement too.

[/quote]

Dim _record As New SQLdeLite.Record

_record.Phone = “Tremblay”
_record.LastName = “02-931991”

// This creates a valid SQL INSERT statement for the SQLdeLite.Record.
Dim _insertStatement As String
_insertStatement = _record.CreateInsertStatement(_db, _record)

// If you pass ‘True’ as the third parameter then table and column names will be enclosed in quotations (for PostgreSQL).
_insertStatement = _record.CreateInsertStatement(_db, _record, True)

// Then you can call SQLExecute
_db.SQLExecute(_insertStatement)

Updates today:

  • Added String support to support old framework Xojo classes. To enable string support for TextLiteral’s navigate to the SQLDeLite.Record class and rename the ‘Operator_Lookup_STRINGSUPPORT’ method to just ‘Operator_Lookup’. It will join the other overloaded methods and now String support is enabled.
  • Fixed issue with positioning of parameters.

Why enable string support? Well in the IDE strings are TextLiteral’s. For instance

Dim _record As New SQLdeLite.Record
_record.Name = “Phillip Zedalis”

The part “Phillip Zedalis” is a TextLiteral. The IDE will auto-convert that to Text in your record. If you need old framework String support then you need to enable it. However TextLiteral’s will no longer work because the IDE can’t decide if it wants the TextLiteral to be a String or Text. So you end up doing this.

Dim _record As New SQLdeLite.Record
Dim _name1 As Text = “Phillip Zedalis”
Dim _name2 As String = “Phillip Zedalis”
_record.Name1 = _name1
_record.Name2 = _name2

That would only work if String support has been ENABLED. However since SQLdeLite uses the NEW framework internally you should work around using Strings and use Text instead if at all possible.

[quote=286764:@Phillip Zedalis]Dim _record As New SQLdeLite.Record

_record.Phone = “Tremblay”
_record.LastName = “02-931991”

// This creates a valid SQL INSERT statement for the SQLdeLite.Record.
Dim _insertStatement As String
_insertStatement = _record.CreateInsertStatement(_db, _record)

// If you pass ‘True’ as the third parameter then table and column names will be enclosed in quotations (for PostgreSQL).
_insertStatement = _record.CreateInsertStatement(_db, _record, True)

// Then you can call SQLExecute
_db.SQLExecute(_insertStatement)[/quote]

Hi Phillip,
the code above doesn’t work.

  1. “CreateInsertStatement” doesn’t accept “_record” as parameter but “table name” as string.
  2. “_db.SQLExecute(_insertStatement)” or “_db.SQLdeLiteExecute(_insert, _record)” ?

The only successful was the code below ( but must I have had a “Select” before ) :

Dim _record As New SQLdeLite.Record
_record.LastName = “Tremblay”

Dim _rs As RecordSet
_rs = App.db.SQLdeLiteSelect(“SELECT * FROM Customer WHERE LastName = $LastName”, _record, True)

_record.FirstName = “Pippo”
_record.Customerid = _record.Customerid + 1

Dim _insert As Text
_insert = _record.CreateInsertStatement(App.db, “Test”)

App.db.SQLdeLiteExecute(_insert, _record)

[quote=286762:@Phillip Zedalis]My bad, I had parameter positioning in a different project. I’m using embedded classes in the module to keep it tidy and easy to drop into other projects. The downside is you can’t use external items so I had a couple different versions scattered around.

You should be all set.[/quote]

Now works fine ! Good job Phillip.

  1. You are correct. It extends SQLdeLiteRecord and takes two parameters: Database, TableName

  2. Keep in mind that “CreateInsertStatement” just creates a generic INSERT statement so you don’t have to build it. So you could pass it to database.SQLExecute().

Released a new stable version 2.1610.290 based on feedback from several users over the last couple months. Find the release here: https://github.com/1701software/SQLdeLite/releases

SQLdeLite looks really interesting. Wondering if you’d consider adding an example file that’s for a platform other than iOS? Trouble is I’m on Windows, and so I can’t run a Xojo iOS project even in the debugger. (and obviously Xojo users on Linux would be in the same boat)

David,

I will create an example or two showing it off. It’s a no-frills SQL library to make you more productive. If that’s true the examples won’t take long to build. :slight_smile:

Brilliant - thank you! Looking forward to giving it a whirl.

Recent updates over the last couple months:

Version 2.170213.0 - February 13nd, 2017

  • Critical update to how parameters are ordered. Complex queries may have failed due to parameters not binding correctly.

Version 2.170122.0 - January 22nd, 2017

  • cubeSQL plugin will freeze up when trying to bind empty String/Text parameters. Offset this by binding Null if the String/Text field is blank.

Version 2.1611.70 - November 7th, 2016

  • The SQLdeLite.Record.CreateInsertStatement() method now escapes single quotes properly.
  • Int64’s are handled property in 64-bit builds.
  • cubeSQL does required ordered parameters.
  • Introspection now uses the base type of the database object. This allows you to sub-class the database class without issue.

Version 2.1610.310 - October 31st, 2016

  • Fixed issue with SQLdeLite.Record.CreateInsertStatement() where strings with ’ in it were not properly escaped breaking the query.

MIT Licensed @ GitHub: https://github.com/1701software/SQLdeLite

Just curious about how to leverage SQLdeLite for the use cases where custom methods and properties are needed. Can I create my own objects and subclass them from SQLdeLite.Record to interface with the database while still being able to extend them with custom methods and properties?

Sorry for the silly question, just trying to wrap my head around it. Very interesting solution BTW!

[quote=363352:@Tiago Ribeiro]Just curious about how to leverage SQLdeLite for the use cases where custom methods and properties are needed. Can I create my own objects and subclass them from SQLdeLite.Record to interface with the database while still being able to extend them with custom methods and properties?

Sorry for the silly question, just trying to wrap my head around it. Very interesting solution BTW![/quote]

Yes you can use it that way. SQLdeLite will handle actual typed properties the same as it handles dynamic properties. So subclass from SQLdeLite.Record and you should be good to go.

The next version of SQLdeLite will allow you to use any object and it will attempt to work so you do not have to subclass it.