Creating a Web SQLite databases

Databases
1

I know about databases and desktop apps but I know next to nothing about web apps. What is the code I need to create a new web SQLite database and where does the database file go?

2

It should be pretty similar to desktop.

You can review:

  • Examples - Databases - Supported Engines - SQLite - SQLite (Web)
  • Examples - Sample Apps - EddiesElectronics - Web

and check if there are differences with the desktop version:

  • Examples - Databases - Supported Engines - SQLite - SQLite (Desktop)
  • Examples - Sample Apps - EddiesElectronics - Desktop
3

Thanks, I am looking at that but there is masses of stuff in there about creating tables etc and all I want to know is how to create the database in the first place. Surely there is a line or two of code to do this, but I can’t find it.

4

Ok, maybe you are reviewing EddiesElectronics and got lost, then looked at SQLite (Web) and is too simple and this like create the database but you can’t find it, right?

Dim dbFile As New FolderItem("example.sqlite", FolderItem.PathModes.Native)

you can change that line to:

Dim dbFile As FolderItem= SpecialFolder.Desktop.Child("exampleweb.sqlite")

and the file will be created on your desktop.

The SQLite (Web) example is pretty simple and divided in steps, a) create the file (db), b) create table, c) add sample data, d) show sample data

This helps?

5

You create a database on the web exactly like you do on the desktop. The only restrictions are 1) you may not have access to the command line, so you have to create it in code, and 2) you may be limited to where you can create the database.

It may be as simple as

db = New SQLiteDatabase
db.DatabaseFile = SpecialFolder.ApplicationData.Child("example.sqlite")
db.CreateDatabase

Of course the new database will not have any tables, so you have to create them in code, which is why you see all that mess in the examples.

If you have ftp access to your hosting account, you can create the db on your desktop and copy it up to your website.

A key consideration is, put your database in a non-public location. Your hosting will have a public html folder where your web app goes, so it is accessible from the outside, as well as private locations that are not accessible directly. Your web app, however, can access them internally. It’s an important security consideration.

1 Like
6

Yes, I think so, Alberto, although I was expecting to make some kind of “Create” statement. At the moment, I haven’t got a web licence or a remote server to deploy the app on, so I am just working in Xojo on my Mac and from my desktop experience am used to putting a database file somewhere in my file system.

In my first web app, I absolutely need a database, as well as other things, so if this code works in my tester and then works when I have a web licence and a place to deploy the web app, then that should be all I need. I expected to have to tell the app where to place/find the database file but your second example seems to suggest it could go into Special Folder.Desktop and if that means the database file will be sitting on the host account next to the app then that should work but maybe I will have to put the xojo_binary_project file on my Mac Desktop to make the tester work?

Tim, thanks. Your suggestion implies that the compiled app on the server (probably a Xojo Cloud server) can find SpecialFolder.ApplicationData. I expect it can and I know my Mac can in desktop apps, so presumably that will work in my tester. I appreciate your comments about the database file security. I don’t know at this stage what exactly will be accessible on the hosting account or whether there will be ftp access but I will make sure the db file is somewhere secure.

Steve

7

Just so you know… SpecialFolder.ApplicationData returns Nil on Xojo Cloud. It would be better to use SpecialFolder.Documents or SpecialFolder.SharedDocuments if you’re planning to use that service.

8

OK, thanks everyone for the help. This code seems to work and creates two folders in my Mac’s Documents folder with an encrypted SQL DatabaseFile inside the innermost. Presumably I can go on from here and create a Tables and Fields structure. Then when I deploy online, I just need to modify the app slightly and copy the DatabaseFile across to the place where it is expected or even retain the tables setup method.

I assume the app will need to open the database and the dbfile in its Opening Event, in case it ever needs restarting on the host. Do I need to do anything about Sessions? I haven’t encountered these before.

var f as FolderItem = SpecialFolder.Documents
var pq, pqrs as FolderItem
If f <> Nil then
  if f.exists then
    pq = f.Child("pqDox")
    If not pq.exists then
      pq.CreateFolder
    end
    pqrs = pq.Child("pqrsdbDox")
    if not pqrs.exists then
      pqrs.CreateFolder
    end
    var dummy as New SQLiteDatabase
    dummy.DatabaseFile = pqrs.Child("pq.sqlite")
    dummy.EncryptionKey = "realPWkeytogohere"
    dummy.CreateDatabase
  end
end
9

Each Session should have its own database property and connection. Do not connect in app.Opening and try to share a connection between sessions. Also, turn on WAL in the database.

11

OK thanks, Tim. I was having a problem getting the db database to be a property of the Session but seem to have sorted that now. Xojo was confusing me by allowing me to create a “New SQLiteDatatbase” property and then automatically removing the space between New and SQLiteDatabase. Of course it didn’t work. Instead I created an SQLiteDatabase property and then a line

db = New SQLiteDatabase

at the start of the Session Opening event.

As with the Encrypt instruction, I have inserted

db.WriteAheadLogging = True

before Creating the database. It didn’t cause any problems and I can Connect so I am assuming that WAL is working.