DEV Community: Paul Lefebvre

SQLite New Features

Paul Lefebvre — Thu, 12 Jan 2023 18:44:26 +0000

If you haven't used SQLite in a while, there are a few great new features that have been added in the last year or so.

STRICT tables
PRAGMA table_list
RIGHT and FULL OUTER JOIN
Built-in JSON support

More about each of these below.

STRICT Tables

One of SQLite’s most unusual capabilities is that it does not care about what data goes into a column. Although you could specify a type for a column, it was really more of a suggestion as other types of data could be put into the column. This behavior is different than most other relational databases and can sometimes be a source of confusion.

Now you can create your tables using the STRICT keyword to force them to require column types and to force the column types to always be checked when putting data into them.

You are still limited to INT, INTEGER, REAL, TEXT and BLOB. That means there is still no DATE or DATETIME type like you might find in other databases. Instead use TEXT with YYYY-MM-DD format.

However, an ANY type was added which essentially allows you to clearly state that the column can contain anything. This allows you to have a mixture of specific types and generic types in your STRICT tables.

Note that the STRICT keyword goes at the end of the CREATE TABLE command:

CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID)) STRICT;

The SQLite docs have more information about the new STRICT table feature.

PRAGMA table_list

Previously if you wanted to get the list of table using SQL you had to directly query the sqlite_master table.

Now there is a simple PRAGMA that can do the same thing:

PRAGMA table_list

It returns a list of tables and some other details about the table (which may change over time according to the SQLite docs).

RIGHT and FULL OUTER JOIN

Joining tables is a common task with SQL. The most common type of join is an INNER JOIN where only the rows common to both tables are included in the result. Other less common types of joins include LEFT OUTER, RIGHT OUTER and FULL OUTER (sometimes OUTER is omitted when referring to these types of joins).

SQLite has had support for LEFT OUTER joins for a long time, but support for RIGHT OUTER and FULL OUTER were missing. But now they are here, giving your more complicated queries better compatibly with the “big name” databases.

Learn more about these types of joins at W3 schools.

JSON Support

I’ve saved the big one for last: your SQL databases can now work with JSON data within columns.

Here is a sample table to work with with some JSON data that is stored in the players column:

CREATE TABLE team(id INTEGER PRIMARY KEY, Name TEXT, players TEXT); INSERT INTO TEAM VALUES (NULL, 'Seagulls', '[ {"Name":"Bob","position":"1B"}, {"Name":"Tom","position":"2B"} ]') INSERT INTO TEAM VALUES (NULL, 'Pigeons', '[ {"Name":"Bill","position":"1B"}, {"Name":"Tim","position":"2B"} ]') INSERT INTO TEAM VALUES (NULL, 'Crows', '[ {"Name":"Betty","position":"1B"}, {"Name":"Tina","position":"2B"} ]')

Let’s say you want to get the first player on each team. Without SQLite JSON support you would have to pull out the JSON column data and parse it out separately. But now you can do it with this SQL like this:

SELECT players -> 0 FROM team

The above SQL says: for each row fetch the first array element from the JSON data in players.

This is how you would list all the players on all the teams:

SELECT team.Name, json_each.value -> 'Name' FROM team, json_each(team.players)

And if you want to get the actual value without the quotes, you can use the ->> operator (and also rename the result):

SELECT team.Name, json_each.value ->> 'Name' As PlayerName FROM team, json_each(team.players)

The SQLite JSON support can do much, much more which you can read about on the SQLite JSON doc page.

Retro Computing is Fun

Paul Lefebvre — Wed, 11 Jan 2023 21:42:29 +0000

About a year ago I started a retrocomputing newsletter/blog that I call Goto 10.

That is of course a play on the old-school BASIC programming languages that I used when first learning how to code. The canonical program is:

10 PRINT "HELLO" 20 GOTO 10

Not all the posts there are about software development or programming, but several of them have been about using BASIC or Pascal.

If you're interested in this sort of thing, I encourage you to check it out at:

https://www.goto10retro.com

Here are some posts that you might find interesting:

XojoTalk 037 – 2020 Release 2 Extravaganza

Paul Lefebvre — Fri, 04 Dec 2020 19:28:41 +0000

Xojo Engineers Paul and Travis talk about Xojo 2020 Release 2 and Apple M1 Macs.

Download mp3.

Topics

XojoTalk 036 – 2020 Release 1

Paul Lefebvre — Mon, 31 Aug 2020 16:23:22 +0000

Xojo Engineers Paul and Greg talk about Xojo 2020 Release 1, Web 2.0 and other things.

Download mp3.

Topics

Mistel Barocco MD770 Split Mechanical Keyboard Review

Paul Lefebvre — Sat, 29 Aug 2020 20:11:49 +0000

As a software developer, my keyboard matters to me. Personally I do not like standard full-size keyboards because I find the number pad forces me to move the mouse too far to the right.

So I've always preferred the smaller (ten keyless or TKL) designs, the smaller of which mostly look like laptop keyboards.

For the past 12-13 years I've been using a Kinesis Freestyle keyboard (Mac version), which is a split TKL design. This means that the keyboard is literally split into two halves, which are connected by a cord.

The benefit of such a design is that you are able to keep your shoulders and wrists nice and straight when typing. With a normal keyboard, such as with a laptop, your wrists are twisted slightly to line up with the keys which can cause strain after a while.

I liked the Kinesis Freestyle 2, but it was pretty large and took up a lot of space in my keyboard tray. They keys were also nothing special, just standard mushy, membrane keys that you find on most low-cost keyboards.

A month or so ago I started doing some research and stumbled upon the world of mechanical keyboards. I had a TKL mechanical keyboard a while ago but found the clicky Cherry MX Blue switches it used to be far too noisy for me. It also was not split so I stopped using it when I went to the Kinesis. But now I learned there are other types of switches! And there are even split mechanical keyboards. I had no idea and down the rabbit hole I went.

It turns out Kinesis made a mechanical keyboard, the FreeStyle Pro, which is essentially and updated FreeStyle 2. They also make a "gaming" version that has RGB lighting, the Freestyle Edge, for a bit more money.

I seriously considered these two but they are pretty expensive and large. I did like the programmability feature and Kinesis was a quality company, but I kept looking.

I then came across the Mistel Barocco MD770 split keyboard, which was available with or without RGB lighting. This one was smaller than the Kinesis and also a bit cheaper.

I also had to decide what type of switch to get. The most common are Cherry MX Blue (very clicky and tactile), Cherry MX Brown (not clicky, but provides some tactile feedback) and Cherry MX Silent Red (not clicky, not tactile).

I decided I would choose Brown as they seemed to match what I thought I'd prefer in a keyboard.

I watched videos, read reviews and in the end I went with the Mistel Barocco MD770 with RGB lighting.

Mistel Barocco MD770 RGB

I have to say that I really like the Cherry MX brown switches. They are not clicky at all, but you do hear pleasant sounds from them while typing. The keys themselves require slightly more pressure to activate than standard mushy membrane keyboards so that takes a bit of time to get used to. But the feel is wonderful. The keycaps themselves also have a great feel to them, although I'd prefer a larger bump on the F & J keys to make it a little easier to find the home row. After a bit of practice I am back to my usual 80-85 WPM, so I can't complain.

The black color is gorgeous and it looks especially nice with the orange keycaps.

I knew this going in, but the LEDs are not all that practical. They are fun, however. Because the keycaps are not translucent in any way, the LEDs cannot light them up at all. Instead it's more of an underflow. This is fine and I think it looks nice, but it doesn't help illuminate the keys in the dark. I tend to use a solid red at medium brightness which I find is easy on the eyes. It was only $20 more for the LED feature and even though it's not super-useful, I think the fun factor makes it worth it.

Perhaps the biggest issue I have with the keyboard is that it does not have great Mac support. First, there's no way to update the firmware from a Mac. I had to hook the keyboard up to a Windows PC to do that. And second, although there is a DIP switch to enable Mac mode, which essentially flips the Alt & Windows keys and changes the function keys to do their Mac commands (volume, play, skip, launchpad, etc.), when it is enabled there is no way to use the actual function keys as function keys. On a regular Mac keyboard you would press FN+F8 to get F8 instead of doing the Play/Pause thing, but there's no way to do that on this keyboard.

Instead what I did was flip the DIP to just swap Alt & Windows key and then used Karabiner Elements app on Mac to re-map certain F-keys to the values I wanted. Essentially I really wanted F1 & F2 to pass through as is because I use them often in development tools.

I really like that they included all the extra keycaps from a full keyboard set (and a keycap puller). I used several of these to represent my Karabiner Elements re-mapped function keys.

Speaking of re-mapping keys, you can also re-map keys directly on the keyboard itself, in one of several layers. I have re-mapped the left spacebar to be a backspace and the CapsLock key to be another enter key. This is a great feature, although I've yet to see a way to remove a re-mapped key without wiping all the mappings on the keyboard layer.

Lastly, the most important feature of this keyboard is that it is split. The overall compact size of the MD770 makes it really easy to adjust to your preferences.

I like that this includes the little nubs so you can tent it, which is what I use. I don't use a large separation, maybe 3-5 inches, but you can put them very far apart if you wanted. It would probably be more ergonomic if the keyboard itself was more flat, rather than having the back be slightly higher than the front.

So overall, this is a wonderful keyboard and perhaps one of the best split keyboards available.

Recommended!

How Installing a Dishwasher Made Me a Better Software Developer

Paul Lefebvre — Fri, 26 Jun 2020 17:35:44 +0000

I installed a dishwasher recently. This is a big deal for me as I'm a software developer and not really much of a handyman. I own few tools and always worry about breaking something. Let me tell you a story of why installing a dishwasher helped remind me how to be a better software developer.

Our 17 year-old dishwasher, didn’t quite die, but it was getting quite loud and starting to rust out in places. It was time for a new one. After some research, we settled on a Maytag which was ordered over the phone from a locally-owned appliance store.

During the ordering, they asked if we wanted to pay extra for delivery and installation. Normally that wouldn’t be a problem, but that did mean we’d have to wait a while before it would arrive and we also wasn’t too keen on having someone in the house. So we decided to pick it up and that I would install it myself.

A quick bit of research told me that when installing a dishwasher there are essentially three things to worry about:

The drain hose
The electrical power
The hot water line

While removing the old dishwasher I was able to verify the hot water line had a shutoff that worked and that the dishwasher was connected to its own circuit. I was able to see how the drain house connected to the drain pipe and I was able to example the hot water line, which was a copper pipe. Disconnecting everything was easy, although the hot water line was difficult to reach under the dishwasher.

After getting the new dishwasher home in the evening, I opened the box and found the instructions so I could review them before starting. Overall the steps were as I thought, but it would be important to go slow to make sure everything went smoothly, so I decided to start in the morning so I’d be fresh. I estimated it would take 1-2 hours to install the dishwasher.

Hooking up the drain line was easy. Hooking up the (direct-wire) power was also straightforward. The last step was to hook up the water line, which I mentioned earlier was a copper pipe. One thing to know about copper pipe is that it is not very flexible. The pipe also had its own connection nut which means I couldn’t use the new ones that came with the new dishwasher.

Try as I might, I could not get a hookup that did not leak water all over the place. I just couldn’t get past it and I started to fear that I’d have to call in a professional. It turns out I was was not stuck, however. After consulting with my brother-in-law, he suggested that a flexible metal hose for the water line would likely solve my connections issues, so I drove to Lowe’s to pick one up.

Once I got the new hose and hooked it up, things were much better. I still had a different leak that was easily solved by tightening another connection.

It was now time for a quick test to see if there were any problems. And a problem there was. When I turned on the water and power, the dishwasher immediately displayed an error code. After a bit of panic and re-reading of the instructions it turned out that in my rush to test, I forgot to re-attach the leak sensor (which was disconnected to reach the power hookups) so the dishwasher was thinking it had a water leak. This was kind of like a bug in my code.

After putting that back in place and resetting the power, the dishwasher starter without errors. Success! Ship it!

Well, not quite. Now all that was left was “polishing” so to speak. I had to adjust the height of the dishwasher so it fit properly under the cabinet. That took a while as each leg had to be manually adjusted. Sliding it into the space was also slow as it was a snug fit. Then I had to attach it to the counter so that it didn’t move.

Once it was in place, we put couple dishes in it and ran a test. It all seemed to work fine.

We added a full load and ran it again. This time it failed about half-way through with the same “leak” error as before. Upon inspection, the connector into the dishwasher was leaking again. It turned out that I had wiggled it loose with all the movement from adjusting the height and sliding it around into the cabinet. Fortunately I was able to tighten the connection to fix the leak without removing the dishwasher.

And now the dishwasher is working perfectly. It is quiet and is doing a great job on the dishes. It ended up taking me about 4 hours all together.

OK, that was a long story. What on Earth could it possibly have to do with software development? Many things, I think.

It is important to plan ahead. Understand what you need to do. Don’t just jump in and start coding without a plan. Sure, you might make some initial progress, but you’ll likely quickly become frustrated and confused when problems arise.
Take some time to read the docs. You don’t have to memorize them, but if you give them a once-over you’ll be in a better position to find something in them later should you need it.
Take your time in general. And just because you haven’t done something before doesn’t mean you can’t do it. Try new things outside your comfort zone.
Have a mentor available. In my case, my brother-in-law was able to FaceTime to answer questions. A forum can be a great place for that.
Don’t panic. Something will inevitably go wrong. If you go slowly you’ll be able to deal with and get past it. If you reach a dead end, consider alternative solutions. I could have kept fighting with the copper pipe and maybe gotten it to work, but I asked for help and found out about an alternative solution that made more sense.
Your time estimate will be inaccurate. Just accept it.
Test as early as possible. Waiting until you’re all done before testing anything means you might have to undo a lot of work to find or fix the problem. Perhaps unit testing can help you with that.
Finish (or polish) things up once you are confident all is working as you expect. This may also unveil bugs you need to fix.

I hope you found my little journey somewhat interesting and that it helps you on your software development path.

Kotlin Class Computed Properties

Paul Lefebvre — Fri, 27 Sep 2019 13:00:58 +0000

Here's a quick tip that I didn't notice when implementing a Kotlin class. I wanted to have a computed property that would return a value of a backing class's property.

This seemed pretty straightforward so I wrote it like this (not the actual code, but you'll get the idea):

val Age: Int = person.Age()

The problem here is that a property like this is not really a computed property, even though it may look like it since it's getting the value from a function call. Instead this property is initialized only once when its class is created and thus the function is only called once. So if your Age function returns a different value based on information that is provided later then it won't be correct.

Instead the solution is to create an actual computed property by using get like this:

val Age: Int
    get() = person.Age()

This is a read-only computed property and the get() is called each time so you'll always have the correct value from the Age() function.

Learn more about Kotlin class properties here: https://kotlinlang.org/docs/reference/properties.html

How I Learned to Program

Paul Lefebvre — Fri, 08 Mar 2019 14:46:39 +0000

Here's how I learned to program, inspired by this tweet from Marco Arment:

https://twitter.com/marcoarment/status/1101932427260411906

I first became aware of programming as a thing in early 1984 on an Atari 400 using Atari BASIC.

I started off by typing in programs from Atari computer magazine BASIC program listings and then tried changing the code to get a feel for how things worked.

I then moved on to learning from books. One of my favorites was Dr. C. Wacko's miracle guide to designing and programming your own Atari computer arcade games.

During this time I also wrote BASIC programs on paper, mostly because I wasn't always allowed to use the computer (since it was hooked up to our only TV at the time). Plus, I didn't have a way to actually save programs I wrote (a tape drive would come later), so I had to write them down to remember them.

I have a binder of stuff from this time period and it's full of programs I wrote (hand-written and printouts) and even has a rejection letter I got for the first program I submitted to a magazine!

As I began to bump into the limits of BASIC, I moved on to other languages such as Pascal and C and other computers such as the Atari ST, which had a graphical user interface similar to the Mac.

On the Atari ST I learned about event-driven programming and window management. I even made some money on an Atari ST program (JumpSTART app launcher) I wrote in Pascal, my first earnings as a programmer. I actually made of Xojo implementation of JumpStart for the 2018 Just Code Challenge and also made the original Pascal source code available on GitHub.

The fun of programming, especially with BASIC, back in the 80s was hard to beat. I was thrilled when I found about Xojo (then Realbasic) back in 2001 and these days, I think Xojo is the closest thing you can get that brings the fun back to programming. Xojo is a modern, full-blown IDE with a structured, object-oriented and thoroughly modern language. But its syntax will give you just a whiff of BASIC that will bring back fond memories.

Xojo lets you create apps quickly in a fun, fast environment that runs on whatever computer you prefer: Windows, Mac or Linux. And Xojo lets you make whatever types of apps you want: Desktop (Mac/Windows/Linux/Raspberry Pi), web or mobile (iOS, with Android in development). Xojo even lets you make text-based console apps if you want to totally relive old-school user interfaces.

Those early days are often fondly remembered, but not everything from back then was great. For example, I'd rather not go back to a world where 64K of RAM was considered a lot and I had to worry about every byte.

But if you miss those early days when programming was easy and fun, you need to take Xojo for a spin. Xojo is free to use so download it and try it for yourself. There's also a free book to help get you started. You only need to purchase a license when you decide you want to build your own apps to distribute to others. And I guarantee a Xojo license starts at less than what the tape drive for my Atari 400 cost back in 1984!

Programming Pitfalls

Paul Lefebvre — Wed, 27 Feb 2019 21:47:20 +0000

Code doesn’t care whether you are new to programming or an old pro, a citizen developer or the head of engineering, some missteps can catch any of us. Read on to learn some of the most common programming pitfalls and how to avoid them.

Ignoring Encoding

When working with Strings that come from (or are sent) outside your app, you always need to consider the encoding of the text. These days, UTF8 is the most common encoding for text and probably the one you should use most of the time. But if you’re getting text from a database or a web service or just another file that you don’t control, then you’ll want to use the DefineEncoding method to set the correct encoding of the incoming text or use ConvertEncoding to cover the encoding to something you’d rather be using, such as UTF8.

Avoiding Exception Handling

Some classes raise exceptions when something unexpected happens. If your code ignores these exceptions then this causes your app to display an error message to the user which forces them to quit the app. That’s not the best experience.

Use Try…Catch code to ensure exceptions are caught and dealt with appropriately and gracefully.

As an example, if you call XMLDocument.LoadXML and supply invalid XML, then an XMLException is raised. By catching this exception you can display a message to the user telling them the XML file they selected is invalid and asking them to choose another.

Skipping Database Error Checking

Whenever you run a database command it is possible that there was a database error that happened. Some languages might raise an exception for this, but others may require you to check for an error.

This can really help with catching subtle problems, such as a typo in a SELECT statement.

Ignoring Memory Leaks

A memory leak in your app is when it keeps reserving memory but never gives it back. Oftentimes this is not noticeable as the increased memory usage is minimal and does not typically affect 64-bit apps. In addition the memory is released when your app quits. But if you have a significant memory leak you should look into figuring out how to eliminate it.

You can determine if your app has a memory leak by checking (using the OS Task Manager or Activity Monitor) if its memory usage increases significantly as the app is used even while you are closing windows or documents that are no longer used.

It is possible you have some objects that are never going Nil and thus not releasing their memory. Normally you don’t have to worry about setting objects to Nil manually as the memory manager (garbage collection and automatic reference counting are common) cleans up things when they're no longer used. But there is a situation, called a circular reference, that can lead to objects not being set to Nil.

A circular reference means that ObjectA refers to ObjectB and ObjectB refers to ObjectA. Since neither ever has its reference count get to zero, then they cannot be released from memory.

You can either manually set things to Nil to ensure that memory is released or you can make use of the WeakRef class to help manage it.

Fuzzy Graphics in HiDPI

If your language supports it, take advantage of HiDPI (retina) displays by including larger size images. If you only include a single image it might be used at 1x size and will get scaled and look fuzzy or blurry on HiDPI screens. Instead make sure you have multiple image sizes in your project, so the better image can be used on HiDPI screens.

Not Updating to 64-bit

For macOS, you should definitely be updating your projects so they build 64-bit apps. It’s likely that the version of macOS that is released later this year will no longer support 32-bit apps at all and you’ll want to be prepared. For some tools this is trivial (in Xojo you just change a drop-down menu selection).

It’s less of a rush on Windows as 64-bit versions of Windows still fully support 32-bit apps. However, on a 64-bit version of Windows, which are becoming increasingly common, you will get better overall performance as the OS does not have to load 32-bit compatibility layers which use up memory and CPU.

On Linux, 64-bit distributions are becoming more and more common and many do not include any built-in support for 32-bit apps. So you’ll want to be able to distribute a 64-bit app if at all possible.

Cut and Paste Coding

Don't blindly use code snippets that you find in your Google, Stack Overflow or dev.to searches. Take the time to understand what the code does. Use it as a learning experience. Because if that code ever breaks in the future (for whatever reason) and you never knew how it worked, how can you expect to be able to fix it in a timely manner?

Not Checking the Docs

Everyone likes to complain about docs, but I find that far too often people don't even bother checking them before they complain! Perhaps the docs don't have the exact block of code that you want, but you can't expect the docs to write all your code for you. Use the docs to understand what something does, write small programs to test out commands and your assumptions so that you can code what you need.

I also recommend that people try reading the docs ahead of time to get an understanding of what is available to you, rather than just randomly searching for things you might need.

I think I'll stop there as I could probably go on for a while. What are some pitfalls that you've seen?

Microsoft Ending Windows 7 Support in 1 Year

Paul Lefebvre — Mon, 21 Jan 2019 20:16:00 +0000

Here’s your first reminder: Next year, on January 14, 2020 Microsoft is ending support for Windows 7. We went through this a while ago when Windows XP reached end-of-life (no one really cared when Windows Vista reached end-of-life). Windows 7 was a very popular release as it was much better than Vista. It also didn’t help that Windows 8 was not liked at all with its many UI changes.

But the time has come to start preparing to update. You will definitely want to update to Windows 10 and not Windows 8. Windows 10 is actually really nice and is updated regularly. I think you’ll find transitioning from Windows 7 to Windows 10 relatively painless. In fact, early last year Windows 10 overtook Windows 7 in usage per StatCounter. Sadly you can no longer get a free update to Windows 10 so you’ll have to purchase it, but since Microsoft has said that Windows 10 is the last version of Windows, you shouldn’t need to purchase it again.

What happens if you don’t update and want to stick with Windows 7 after January 14, 2020? According to Microsoft:

You can continue to use Windows 7, but once support ends, your PC will become more vulnerable to security risks. Windows will operate but you will stop receiving security and feature updates.

For the time being you can continue to use Windows 7 as you plan your update. Of course Xojo itself and apps made with it also continue to work on Windows 7, although you’ll want to make sure you have installed Service Pack 1.

SQLite is Not a Server

Paul Lefebvre — Fri, 11 Jan 2019 13:53:40 +0000

People often ask about a way to share a SQLite database across multiple apps. Essentially they want to know how to use SQLite as a server and the most common questions relate to putting a SQLite database file on a shared network drive and accessing the database file from apps running on multiple computers. This is a really bad idea, as even the SQLite folks will tell you. I’ll quote the relevant section from their “When to use SQLite” page:

Situations Where A Client/Server RDBMS May Work Better

Client/Server Applications

If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.

A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.

So what are your options when you want to share your database?

If you want to stick with SQLite then you’ll need to put something in front of it that can handle requests from multiple client apps. The most obvious solution is to create a web service by using a web app with WebApplication.HandleURL (or HandleSpecialURL). The web app can accept requests from multiple client apps (or any type — desktop, web, mobile, etc.), fetch the data requested from the SQLite database and then send it back as JSON. This works because the web app is the only app that is connected to the SQLite database.

The Eddie’s Electronics Web Service sample shows you how you might set this up: Examples/Communication/Web Services/EddiesWebService

Another option for setting up your own database web service is to use the Aloe open-source project, which gives you a robust framework for building web services.

To learn more about database web services, check out the Making Database Web Services video.

By the way, this is also why a regular web app can safely use SQLite with multiple users — the web app manages the multiple users but is the only app that is connected to the SQLite database.

If you want to stick with SQLite but would rather not create a web service, another option to consider is to use a product that puts a database server around SQLite. CubeSQL and Valentina have products available that can do this.

Your final option is to switch to an actual database server. PostgreSQL and MySQL are popular alternatives.

SQLite 3.25 Adds Window Functions and Improves ALTER TABLE

Paul Lefebvre — Thu, 10 Jan 2019 00:43:22 +0000

The SQLite 3.25 release had two significant changes: Window Functions and an improved ALTER TABLE command.

ALTER TABLE

If you’ve been using SQLite for a while you probably know that the ALTER TABLE command has always been pretty restrictive. It really only let you change the table name or add new columns. You could not modify or remove columns. This meant that in order to modify or remove columns from a table you typically used a multi-step process of creating the new table the way you wanted, copying the data to it and then renaming the two tables so the new table now has the name of the original table. It was a hassle. And because this was manual it would break any triggers or views that used the old name.

Now with SQLite 3.25 you can directly rename columns on a table, which will update triggers and views as necessary.

The syntax is as you might expect:

ALTER TABLE MyTable RENAME COLUMN OldColumn TO NewColumn;

In Xojo code you send this command to the database using the SQLExecute method. Here’s an example that changes a column name:

Dim sql As String = "ALTER TABLE Team RENAME COLUMN Coach To HeadCoach;"
DB.SQLExecute(sql)

Sadly you still cannot remove a column so you’ll have to resort to the manual method described above.

Window Functions

According to the SQLite docs:

A window function is a special SQL function where the input values are taken from a “window” of one or more rows in the results set of a SELECT statement.

SQLite now has these built-in Window functions:

row_number()
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(N)
lag(expr), lag(expr, offset), lag(expr, offset, default)
lead(expr), lead(expr, offset), lead(expr, offset, default)
first_value(expr)
last_value(expr)
nth_value(expr, N)

This example uses the row_number function to assign a row number based on ordered City names while still ordering the overall results by team name:

SELECT Name, City, row_number() OVER (ORDER By City) AS row_num FROM Team ORDER BY Name;

Window Functions are powerful and can get complex quickly. Read more about them at the official SQLite Window Function doc page.