Sign in to follow this  
Followers 0
cag8f

Basic SQLite question

42 posts in this topic

I'm an amateur programmer who has never worked with databases. I *think* that using databases (namely SQLite) can help with some of my Autoit projects, but I'm having trouble determining exactly how. I'm hoping the database literati here can help explain a particular aspect about databases that is giving me trouble.

My simplified situation is this. A user enters his height and weight into a program and, based on that height/weight combination, the program returns a group, either "A" or "B." To accomplish this using an Excel spreadsheet, I would enter every single height/weight combination, with its corresponding group. The program would read in the user data, then, using loops and conditionals, return the group.

My database question is: Can using SQLite instead of Excel be beneficial in terms of coding efficiency, processing speed, memory usage, or any other aspect? If so, how? Mainly I'm interested in saving speed, but I'm also interested to know what other aspects would be affected.

Sorry for the long, broad, vague question. I'm not looking for someone to tell me how to do something. I'm looking for someone to assure me that SQLite (rather than a simple spreadsheet) is right for me, and to hopefully steer me towards some documentation that may be beneficial to me. I posted this to the SQLite mailing list last week but apparently am still waiting for moderator approval.

Thanks in advance.

Share this post


Link to post
Share on other sites



You question made it on the mailing list and some answers were posted there.

The summary (and my opinion as well) is that if you only have a list of 10 ranges of 2 measurements simply using an array of constants will do the job of choosing that user entries make him/her belong to group A or B.

So obviously your question is something else: do you need to store a large enough number of people data? Then SQLite might be a solution.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Thanks for the reply. The last email I received from the SQLite mailing list told me that my question was pending moderator approval. How do I view replies? edit: nevermind, I see the offline page.

>> do you need to store a large enough number of people data?

Yes. I presented the small, simplified scenario just to illustrate my issue. My actual scenario involves upwards of 10 criteria (e.g. height, weight, age, etc), with upwards of 200 user-entered possibilities each. I'd like to know in what ways SQLite would be advantageous in this situation. I'm having trouble ascertaining how using SQLite would be faster.

Edited by cag8f

Share this post


Link to post
Share on other sites

If you have that many criteria then SQLite is probably the best way to go. It would also add the benefit of allowing you to store much more application data in the same container, and permit making statistics on use, changing profiles at will, etc.

Furthermore an SQLite database is portable accross any OS and any hardware platform seemlessly since every implementation uses the exact same file format.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanks.

>> SQLite is probably the best way to go.

Right, I figured that. But I'm trying to ascertain why exactly. Would using SQLite result in faster processing times? less memory usage? Would it just be easier to implement? something else? a combination?

Share this post


Link to post
Share on other sites

A combination. Instead of running loops thru hard-coded values in several arrays, you can get the result with a single query. Certainly a much flexible design and less coding means less room for bugs. I can't see any memory or CPU bound in such a simple function.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Am I correct though that I will still have to hard-code each potential outcome into the database? i.e. I still have to enter into the database that a height of 75 and weight of 170 corresponds to group A, a height of 80 and a weight of 175 corresponds to group B, etc.

>> I can't see any memory or CPU bound in such a simple function

Are you saying that the SQLite method would be faster and/or use less memory than the Excel method?

edit: Also, a noob question--how do I reply to a single user in the SQLite mailing list? I see that people have responded to the question I posted to the mailing list. But when I click 'reply via email' at the bottom, I get an error page saying, "Requested archive does not exist."

Edited by cag8f

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

cage8f,

Does this

My actual scenario involves upwards of 10 criteria (e.g. height, weight, age, etc), with upwards of 200 user-entered possibilities each.

mean that each row (person?) has a possibility of 10 characteristics and that there may be 200 rows (people) or that each criteria has a possiblity of 200 different values?

I'm not sure that we have enough definition to suggest a resonable solution regarding implementation.

kylomas

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

@kylomas: Each of the 10 different criteria has a potential of 200 potential values. A user would enter 1 of 200 potential heights, 1 of 200 potential weights, 1 of 200 potential ages, etc until the user did this or 10 different criteria. The program would then return the user's group.

Share this post


Link to post
Share on other sites

What determines the group they get put into? Because it sounds like a simple math problem at this point.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

The program would determine the group each user is assigned. The program would already know which height/weight combinations are assigned to which groups. It just waits for a user to enter his height/weight combination, then assigns him a group.

Share this post


Link to post
Share on other sites

That didn't answer the question I asked, what determines which group they get put into (a or B)? Because it seems like the criteria would fall into ranges, and that could be done with something as simple as a Switch statement rather than making it so hard.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

The person writing the computer program would initially set all of the height/weight/group combinations. Then a user would enter his height and weight into the program, and the program would assign the user a group. The method the program used to do that is the reason for my post. Would a database solution be advantageous, keeping in mind that the actual scenario involves hundreds of thousands of potential combinations.

Share this post


Link to post
Share on other sites

Ok, you're not going to answer the question I asked I guess. I have asked you twice now what in your process will determine which group the person gets put into and twice you have repeated useless information. I am aware that the program will do it, what I don't know is how the program is supposed to figure out which group a person gets put into depending on the criteria you ask for. If you can answer that very simple question, we can probably proceed. If you won't/can't answer that, I will leave it up to others to help you.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

Sorry I'm a database noob here so I guess I'm not understanding your question correctly. I think your question is very similar (if not identical) to the question I'm asking in my original post. Yes I want my program to decide what group to assign each user. I know that one way to do this is to hard-code all of the potential height/weight/group combinations into an external data file (e.g. Excel), have the user enter his height/weight, have the program loop through all the potential height/weight/group combinations in that external data file, and output the user's group. I am wondering if a more advantageous method would use databases instead of looping over an external data file. Does that answer your question?

Edited by cag8f

Share this post


Link to post
Share on other sites

Not in the least.

You have 2 groups, A and B.

You have questions being asked to the users.

The answers to these questions somehow make them fall into one of the 2 groups

How do the group A people get assigned to group A and how do the group B people get assigned to group B after answering these questions.

There has to be some way to figure out how to assign them to one of the 2 groups by the questions asked, and the answers they give.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Thanks for your patience but we don't seem to be getting very far.

>> There has to be some way to figure out how to assign them to one of the 2 groups by the questions asked, and the answers they give.

Here's the process:

1. External data file is created. This data file contains every single height/weight/group combination. Assume the computer program has decided each of these combinations based on his own whimsy.

2. Program asks 2 questions: "What is your height?" and "What is your weight?"

3. Program accesses this data file, searches for the line containing the user's height and weight, and returns the corresponding group.

Your issue is a little ambiguous to me--it it regarding step 1 or step 3?

Share this post


Link to post
Share on other sites

Instead of using data tables, why don't you use a more universal algorithm like this page shows, using a simple of more sophisticated classification (age, ethnical criterion, etc)?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Wouldn't it be easier to determine their group by some kind of algorithm that the height and weight fit into? In the way that a person's BMI is determined by the weight divided by the height, you don't need a chart or a database, you just need 2 numbers divided into each other. If the height/weight ratio is below a certain number they're in group A, if it's equal to or above a certain number they're in B.

I think you're WAY over-complicating it for what you're trying to do.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Yes in this simple height/weight scenario, with only 2 criteria, a chart or database might be unnecessary. But I'm posing these questions because my actual scenario involves ~10 criteria, each with ~200 possibilities. My actual scenario also does not have an algorithm (e.g. body mass index) to determine each group. Would a database solution to such a scenario be ideal in terms of processing speed and memory usage?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0