SQLite Questions

[chuckm]

Hi All,

After having to be away for over a year or so, I'm back to trying to suss out how to best work with LC. My current project is to create a SQLite database driven program that allows users to enter data into simple tables and then use that data to create complex collections using one-to-many and many-to-many relationships. I'll throw out that I've been developing apps in a multitude of languages for the last 40+ years and I'm very familiar with SQL across more databases than I care to count. I'm also familiar twitch the message chain and have used many MVC frameworks. However, LC continues to be far more challenging to create apps with than any of my other alternatives (which is why I'm focusing on it again). I've read through the tutorials on SQLite and databases in general and have several questions about creating an app to run across multiple platforms (including iOS and Android).

1. Is it better to create a single global connection to the database or to open and close the database for each query? My gut feeling is that it probably doesn't matter, but it would seem that opening a single global connection for the duration of the run of the application would be better than the constant chatter of opening and closing the connection.

2. Per best practices, if I am creating a global connection id for the database where should the database be opened and closed? StackOpen seems to feel right, but PreStackOpen may be better for opening and StackClose seems best for closing the stack. All of the example code has the user opening the database by clicking on a button but if the database already exists I'd think it would be better to attempt to open it, and if there is no database, then possibly trying to recreate it would make sense.

Best regards,

Chuck

[sphere]

You've just answered your own questions.

Open the DB when opening the stack
close it when closing the stack
If it does not exist, create it

[bogs]

You've just answered your own questions.

Well, except this one -

Is it better to create a single global connection to the database or to open and close the database for each query? My gut feeling is that it probably doesn't matter, but it would seem that opening a single global connection for the duration of the run of the application would be better than the constant chatter of opening and closing the connection.

I'm no DB expert, but my gut feeling is that your gut feeling is probably right. I've seen it explained both ways, the amount of pros and cons for each came out about equal.

About the only exception I've seen is where (for whatever reason) you might have a possibility of loosing connection to the db (power failure, network issue, whatever) and can't afford to not have the data post or retrieve. Opening and checking the connection each time ensures the connection to the db didn't just go away silently somewhere in-between your last and next call to it.

I'm probably explaining that poorly, but hopefully it makes sense to you.

[dunbarx]

However, LC continues to be far more challenging to create apps with than any of my other alternatives (which is why I'm focusing on it again).

I am curious. Do you mean that LC per se is challenging, or that its support of and interaction with SQLite is?

I cannot believe it can possibly be the former.

Craig Newman

[sphere]

Well, except this one -

Open the DB when opening the stack

That's the global connection

Opening and checking the connection each time ensures the connection to the db didn't just go away silently somewhere in-between your last and next call to it.

This is a valuable hint

By the way sqlite is perfect to store local things in. before i used text files. Which can be even more tricky to work with when you use them as a sort of DB.

[mrcoollion]

By the way sqlite is perfect to store local things in. before i used text files. Which can be even more tricky to work with when you use them as a sort of DB.

As an alternative between using a text file and a database for storage of local things I use an array to store data in and save it as a text file. When you need it is very easy to retrieve and read back into an array and because it has a structure already (the array) it is also very easy to get the data you need out of the array.

Of-coarse this is not the way to go with a multi user application . In that case you at least need to use a SQlite database.

Just my 5 cents added to the pocket.

Paul

[sphere]

Just my 5 cents added to the pocket.

That's certainly not cheap, as normally one add's their 2 cents.

But since we don't use the one and two cents anymore, 5 cents (a Stuiver) is better

[bogs]

I like sqlite, but have little problem using flat files or even stacks themselves. In fact, since Craig (I think, it has been about a year or so) introduced me to saving stuff in stack files, I just LOVE using them for all kinds of havoc. I guess it has become my 'goto' storage method.

[chuckm]

However, LC continues to be far more challenging to create apps with than any of my other alternatives (which is why I'm focusing on it again).

I am curious. Do you mean that LC per se is challenging, or that its support of and interaction with SQLite is?

I cannot believe it can possibly be the former.

Craig Newman

Oh, the drollness...

I'm always up for a challenge (especially since I'm nearing retirement age). However, if the younger people aren't using LC because of the learning curve, the product will die as its audience dies. The group of teens I was working with who are aiming at CompSci gave up on LC because they determined they could do their projects in Python or Node.js much more quickly due to the large amount of available literature that contains examples and walkthroughs of simple projects like multi-table address books where the project starts out with a single screen/table and iterates to include additional features.That's the way most developers learn stuff these days. They just don't have the patience or tenacity to muddle through things when they can easily find examples of what they're trying to do. To be honest, LC should be as simple as Visual Basic (which is to a greater or lesser extent a kind of HyperCard).

I don't have the expertise with LC to write those tutorials but someone who does could make a nice piece of change by doing ebooks or Udemy/Coursera/etc. online courses. I don't want to be harsh because I can see a lot of potential with the product, but I am concerned that failing to capture a younger audience does not bode well for the future.

[chuckm]

You've just answered your own questions.

Open the DB when opening the stack
close it when closing the stack
If it does not exist, create it

So, I just added the following code to the first card per the openStack Api in the docs:

Code: Select all

on openStack
   global gConId
   put revOpenDatabase("sqlite", "data/database.sqlite") into tConId
   if tConId is "" then
      answer warning "Problem creating or accessing database!"
   else
      answer information "Database connected. Your connection id is: " & tConId
      put tConId into gConId
   end if
end openStack

on closeStackRequest
   revCloseDatabase gConId
end closeStackRequest

I got the following message in an answer box:

Database connected. Your connection id is: Database Error: Unable to open the database file.

So obviously something is rotten in the state of Denmark. The database exists, and to the best of my knowledge is valid because I can query it from the command line as well as from several different GUI tools. There's no password, so I'm wondering if the database has to be in the same directory as the LiveCode app. This is a great example of posted code in the forums, StackOverflow, etc. that fails to work although it may largely be due to my lack of understanding. All I'm trying to do to start is just open the database and yet the code fails and to be honest, the error message is worthless. I'll apologize in advance if the comment comes across as offensive because it is not meant to be so, but I speak plainly.

[chuckm]

You've just answered your own questions.

Well, except this one -

Is it better to create a single global connection to the database or to open and close the database for each query? My gut feeling is that it probably doesn't matter, but it would seem that opening a single global connection for the duration of the run of the application would be better than the constant chatter of opening and closing the connection.

I'm no DB expert, but my gut feeling is that your gut feeling is probably right. I've seen it explained both ways, the amount of pros and cons for each came out about equal.

About the only exception I've seen is where (for whatever reason) you might have a possibility of loosing connection to the db (power failure, network issue, whatever) and can't afford to not have the data post or retrieve. Opening and checking the connection each time ensures the connection to the db didn't just go away silently somewhere in-between your last and next call to it.

I'm probably explaining that poorly, but hopefully it makes sense to you.

Thanks for your response. The database resides on the same system as the app. I wouldn't write any sort of distributed app using SQLite because there are better databases for that sort of thing. However, I do get your point about connections over a network mysteriously dropping.

[chuckm]

The initial problem may be somewhat solved although I'm a little mystified (looking through the dictionary for an explanation right now). The forward slash that separates the filename from the containing directory name apparently should have been a backslash. I work mostly under Unix so this didn't really occur to me as being an issue. So that being discovered, I am now getting the Sqlite database to open. I don't recall seeing the backslash mentioned as a file path separator so I'll take that as a miss on my part.

Oddly, the tutorial here http://lessons.livecode.com/m/4071/l/74 ... put-output uses the forward slash which may be where I got the notion that the path separator was handled under the covers...

[bogs]

The group of teens I was working with who are aiming at CompSci gave up on LC because they determined they could do their projects in Python or Node.js much more quickly due to the large amount of available literature that contains examples and walkthroughs of simple projects like multi-table address books where the project starts out with a single screen/table and iterates to include additional features.

Hello again chuckm,
Glad your sorting it out.

I don't know if your aware or not, the current dictionary includes a guide section -

The 'guiding light'...

Of which the topics are further broken down with lessons from the site on individual topics...

Pick your poison...

...which can be tackled more or less in a linear fashion, progressing from beginner to more advanced.

Aside from that, Mark Schonewille and Andre Garzia have written books specifically about Livecode programming, both aimed at beginner to at least intermediate level programming.

Aside from those books, there are a wealth of other sites which cover most topics that come readily to mind, including a few with the kind of topics you mentioned, not to mention a university style course.

And of course, there is youtube as well

There are also the scripting conferences provided by Jacque, which covers a LOT, but makes it easy by breaking it down and giving you more than one way to cover the material.

There is also this stack file which I found useful enough to separate out of the predecessor to Lc, Metacard. It is a *little* older than the scripting conferences, but *most* of the information in it is difficult to find on your own. No SQL stuff, mind you.

All of the above (except for the books and the livecode app academy) is available to you for $0.00, the cost is the amount of attention you'd like to pay. Even the books are very reasonably priced.

It is a lot to take in, in some cases, but hopefully those links will help you get started better.

[chuckm]

Hi!,

I had pointed them to most of those resources and I know that at least half of them worked through a lot of the online materials. For whatever reason, they came away pretty frustrated and came to the conclusion, right or wrong, that there was more of a future with Python or Javascript/Node.js and didn't want to spend any additional time on it. For my part, it's more of trying to find the "how's" and understand the "why's".

For example, I'm closing the database in the closeStackRequest method because that seems to be where many people think it should go. However, on a Mac, it appears that I also need to capture an Apple event if the user clicks the Quit button. That took a surprising amount of time to track down where I would hope that there is a "standard" best practices guide that covers that sort of thing. I guess it just hasn't been written yet.

I've read most of the tutorials but to be honest, I'm still struggling with where to put things at times. For example, I would have thought that the openStack message would need to come to the stack rather than be sent to a card. Likewise, I was surprised that I need to trap the closeStackRequest rather than the closeStack method as well as trapping an Apple event for the Quit option. I realize that those are all separate events/messages, but it's not spelled out very well in the materials I've read so far. I'll go re-read things, but the learning curve, for what ever reason, is much steeper for LC and that poses an issue for adoption by a lot developers and businesses in my experience.

With regard to the teens, they're in high school and most of them are in the gifted program, so they're quite experienced at self-learning. Their comments boiled down to "LC looks interesting, but the learning curve is too steep to get things done." I suspect a lack of patience and some familiarity with the other languages/frameworks came into play as contributing factors, but I'm going to withhold personal judgment until I've spent more time with LC. I'm aware that everything is difficult at the beginning so I'm prepared to stay at it for a while. The kids, not so much but they have different deadlines and constraints than I do.

However, I'll restate that getting some experts together to do video courses for Udemy that are project-based and work an application through multiple iterations would go a long way towards easing the learning curve for most developers.

Just my tuppence.

Best regards,

Chuck

[sturgis]

If the default folder is pointing to the location that contains your "data" folder, I think it should locate and open up your database correctly. If this is happening in the IDE, its likely that the default folder is pointing to a wrong, and likely un-writeable location. Check to see what the default folder is set to. If its correct, you can check to see if: there is a file "data/database.sqlite" If both of these check out, you likely don't have permission to write directly to the folder in question. (what os, and where is the file? ) Depending on the OS, it might be necessary to copy the file to a better location for use. My guess is a permissions issue with the location because if you have permission in LC to open a file, it would have created it with no error rather than telling you it couldn't do it.

You've just answered your own questions.

Open the DB when opening the stack
close it when closing the stack
If it does not exist, create it

So, I just added the following code to the first card per the openStack Api in the docs:

Code: Select all

on openStack
   global gConId
   put revOpenDatabase("sqlite", "data/database.sqlite") into tConId
   if tConId is "" then
      answer warning "Problem creating or accessing database!"
   else
      answer information "Database connected. Your connection id is: " & tConId
      put tConId into gConId
   end if
end openStack

on closeStackRequest
   revCloseDatabase gConId
end closeStackRequest

I got the following message in an answer box:

Database connected. Your connection id is: Database Error: Unable to open the database file.

So obviously something is rotten in the state of Denmark. The database exists, and to the best of my knowledge is valid because I can query it from the command line as well as from several different GUI tools. There's no password, so I'm wondering if the database has to be in the same directory as the LiveCode app. This is a great example of posted code in the forums, StackOverflow, etc. that fails to work although it may largely be due to my lack of understanding. All I'm trying to do to start is just open the database and yet the code fails and to be honest, the error message is worthless. I'll apologize in advance if the comment comes across as offensive because it is not meant to be so, but I speak plainly.