7
$\begingroup$

I have an SQLite database with about 13 "tables" represented as a file and stored in the WolframCloud with public permissions located at the following URL:

 chinook="https://www.wolframcloud.com/obj/schandler/Public/chinook.sqlite";

I want to provide access to it and let people import whatever tables they want from it. Now, when that same database was hosted on my hard drive (or, actually, on my private Dropbox account), it was simple. I just did the following:

 schema=RelationalDatabase[FindFile[path<>"chinook.sqlite"]]

And that worked. But I've tried various permutations (and embarrassingly more) of the following, none of which work.

 RelationalDatabase[chinook];
 RelationalDatabase[URL[chinook]];
 RelationalDatabase[CloudObject[chinook]];
 RelationalDatabase[CloudGet[CloudObject[chinook]]];
 RelationalDatabase[DatabaseReference[URL[chinook]]];

Can someone help?

I'd like to be able to let people access the whole Database via RelationalDatabase[something] or tables from the Database via RelationalDatabase[{table1,table2,...tablem},something]

And, yes, I could let people download the file from the public cloud object to their hard drive and write RelationalDatabase[FindFile[location]], but I REALLY want to save them that step. And, yes, I could make the file public on my Dropbox account, but I'd prefer for various reasons to host this on the WolframCloud.

Thanks! (I'm cross posting on community.wolfram.com due to urgency)

$\endgroup$
  • $\begingroup$ Are you using DatabaseConnect? $\endgroup$ – kickert Jul 14 at 18:38
  • $\begingroup$ No, I need to be able to use the file via RelationalDatabase. $\endgroup$ – Seth Chandler Jul 14 at 18:57
  • $\begingroup$ This sounds like a use case for the Wolfram Private Cloud. You could ask WRI to provide you with a license and for support. $\endgroup$ – Rolf Mertig Jul 14 at 21:24
4
$\begingroup$

I received an answer from Wolfram Research Technical Support. They say that it is not presently possible to access a file-based relational database (like SQLite) by specifying a URL. You have to get the underlying file at the URL, copy it to a local drive, and then treat it like a file. I have requested that the developers see if this limitation can be removed or, at a minimum, the documentation for the RelationalDatabase function make that limitation more clear.

$\endgroup$
  • 3
    $\begingroup$ There are plans to support SQLite in the cloud. What I can say is that at present, it is more a limitation of the cloud than the new relational databases functionality. $\endgroup$ – Leonid Shifrin Jul 17 at 15:34
3
$\begingroup$

Yes, but there appears to be a work-around:

Upload the SQLite database to the cloud and make a note of its URL.

ClearAll["Global`*"]
fileSchema = RelationalDatabase[
   FindFile["ExampleData/ecommerce-database.sqlite"]];
c = CloudSave[fileSchema];
c[[1]]
(** "https://www.wolframcloud.com/obj/f799c3e1-cdf2-4eff-858c-20fa001afe11"  **)

Now the original SQLite database is accessible as

ClearAll["Global`*"]
cloudSchema = CloudGet[
  "https://www.wolframcloud.com/obj/f799c3e1-cdf2-4eff-858c-20fa001afe11"]
cloudSchema["Tables"]

(** {"employees", "customers", "payments", "orderdetails", "offices", "productlines", "products", "orders"}  **)
$\endgroup$
  • $\begingroup$ Thanks for the suggestion. It's very interesting. When I run your exact code, it works just fine. But, when I run it using my SqLite database (one containing baseball data and downloaded from a reputable site), it works just fine on my machine (I can see the tables, convert it into an EntityStore,etc.). However, when I try CloudSave, it gives me "CloudObject:: An unknown error occurred". So I am wondering if there is something odd either about the Wolfram e-commerce file or something odd with my file. $\endgroup$ – Seth Chandler Jul 22 at 0:33
  • $\begingroup$ I may have gotten to the bottom of it. I renamed my file to have an .sqlite suffix instead of a .db suffix. The schema now uploads just fine to the Cloud and can now be retrieved just fine. It seems very odd to me that the WolframCloud would care what the suffix is, but perhaps it does. Anyway, a thanks to @LouisB for suggesting what may be a solution to a problem that I had previously been informed was not solvable without workarounds. And a thanks to all those who provided assistance. $\endgroup$ – Seth Chandler Jul 22 at 0:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.