Best practices for distributing to universities/companies

Hi. I am writing to see what you all do when distributing software to companies who buy licenses to your applications whose employees/students would sign in with their Windows credentials to access the program yet without breaching your terms of agreement (not allowable over a network).

Here’s my scenario. My programs are used in many colleges and universities across the nation. I know this is not the best practice, and I am planning on updating this piece… I have my database files stored in the Documents folder of the user’s machine. For a student who buys the program on their own, this isn’t an issue since they can read/write to the databases. However, I’ve been getting some calls lately from schools who need to install the software as an administrator. The program installs just fine, but the database files do not install to the students’ Documents folders, since this folder is user-specific (ends up storing on their admin accounts). I’ve been having the schools IT departments create a general login for all their students to use on one dedicated machine. For most, this is working just fine, but I usually have to end up speaking with their IT depts at some point to advise they should do this, and some are very hesitant to do this. I spoke with a government-run institution a few weeks ago, and they said there is absolutely no way they can/will create a general login for the students for security reasons.

Can you please share what the best approach to this is? Keeping in mind that I’d like to keep it so they don’t make it accessible over a network (buy one license and allow it to be run on 100 different computers). Would storing the database files in the App data resolve this?

Thank you much!

No, because ApplicationData is still user specific. You would need to use Shared Application Data, but you’re going to run into problems with that folder in the more recent OS’s (Win7/8, Mac Lion and newer). I allow easy setup on a LAN with a network license only to my program. That is not to say that a savvy Admin couldn’t still allow all users with a single copy, but I haven’t found a way around that.
I will be interested to see what others say.

The Shared Application Data folder could work, but as Roger says newer versions of Windows do not provide write-access to it.

Perhaps you could create this “database” file when the app first launches. It sounds like your are placing it in Documents when the app is installed. Or course I have no idea if that would work with your licensing scheme.

Paul’s idea is about the easiest to do. Keep a template copy of the user files with the app. On start up check the documents folder for the user files, if not found confirm that the user is a valid customer and if so copy over the user files.

Thanks guys. I have two database files. One is pre-filled with data (exam questions, for read only), and the other is a blank one (users db which keeps track of their progress, needs write access).

What are your thoughts of database storage in the cloud that the program can connect to? I have been testing this out with my latest release to collect data and storing in MySQL. Though, it’s not always saving for some reason. A concern with this to me is that the end student users who may have the program installed on their laptops and are not connected to the internet at that point will not be able to access the files.

I dont have any software at a college or university that I know of. But I have written several to large companies (like Global 100) that have to push it out to the employees. If you package it up in a format that the admins can push out to the remote desktops (.pkg for mac, .msi for windows, etc) then the app can be installed with elevated privileges for the install then can run as needed. Now for the database file, I would take Paul’s suggestion and create it at launch time if it doesnt already exist for that user.

Not sure how your code works. In my code, if the database file doesnt exist, I create a new one including staging any data that I need to.

Now if you have particular questions related to the Higher Education martket (Colleges/Universities) there are several people on the forums that work directly/indirectly for them.

I can certainly consider doing the create database upon install option. I would assume I can somehow do this also for the pre-filled database. Where would you suggest this be housed? Is the Documents folder, as I am currently doing, appropriate? It sounds like the Shared Application Data folder may not be the best approach with newer versions of Windows not supporting write access. After talking with some of the schools’ IT guys, they say their documents files become wiped out periodically (end of semesters), and sometimes even nightly.

we store out databases in SpecialFolder.ApplicationData.Child( “company name” ). database OR SpecialFolder.ApplicationData.Child( “company name” ).child( “application name” ). database

“company name” = “nocturnal coding monkeys” for us, you would use your company name.

That is user specific folder and may or may not be wiped. Most admins dont wipe that directory but (no one) cant promise that they wont.

This is helpful, thank you all

I need to open this conversation back up as I have a followup question. I’ve determined how to create a new database file and store it in the AppData folder if the file is not already present. One of the files is a pre-filled db of about 750 rows of questions/answers. I’m looking for some best practices on how to fill this table to the user if the db is not present on startup.

In testing, I tried the following:

  • Store the pre-filled db on a MySQL server (I’m using one with my GoDaddy account)
  • On first startup, if files are not present, create the empty question/answer db, connect to the server, then copy all the rows to the newly created db

This worked, however:

  • The elapsed time was nearly 2 minutes to complete the operation. Could be a PIA for the user to have to wait for this
  • What if the user is not connected to the internet or for some reason they cannot connect to my server? I would need a “backdoor” approach to filling the db

So my question is… Is there a better way to achieve this? Is there a way to include the table rows in the app itself, such as an Excel spreadsheet or other file (if Excel, can this work for users who may not have Excel installed on their computers?). Again, I know I can just include the files in the distribution package, as I’ve been doing this currently, but the issue is primarily for colleges/universities using the program and want to install the program onto one computer but each student can log in with their own credentials and use the program.

Other suggestions:

  • If the DB is pre-populated, you could just include the SQLite DB file with the app (in a Resources) folder and then copy the DB file out to AppData when the app is first launched.
  • If you need to handle updating of questions and answers, you could do what you are doing now but rather than loading the questions and answers from a remote MySQL DB, load them from a SQLiteDB that you include with the app.

Thanks Paul. I didn’t know we can include SQLite dbs with the app itself. So if the db is added to the project list and then built, it is then added? How do you make calls to this file to do this copy-over? Like where on the user’s computer would this Resources folder be?


You just use a Copy File Build Step to copy the SQLite DB to the Resources folder (which is located next to the app itself).

In your code you point a FolderItem to it and then copy it to AppData.

Thanks Paul. I’m not entirely sure what that means. Are there any documents on how to do this or any examples in Xojo you’re aware of?

Build Automation is covered in User Guide Book 3. Add a Copy File Build Step to your build target and then drag the SQLite DB into it and set the destination to Resources.

You’d use a FolderItem to access files, so there is not much specific about that. You’d just get the file that was just copied to your build with something like this (pseudo-code, not tested):

[code]// Find original DB file
Dim sourceDB As FolderItem
sourceDB = GetFolderItem(“Resources”).Child(“MasterDB.sqlite”) // Test to be sure this matches where the file actually is

If sourceDB <> Nil And sourceDB.Exists Then
// Create destination folder
Dim appData As FolderItem = SpecialFolder.ApplicationData
Dim appFolder As FolderItem = appData.Child(“MyAppFolder”)

// Copy DB to destination
End If

Thanks Paul. This is very helpful! I’ll do some playing around to see if I can get this to work.