Database noob questions

[AmigAtari]

Hi, all. I am not new to programming, but I have never done anything with existing database solutions. While I have created and edited my own custom databases using methods such as tables via CSV or INI parsing, I think I am ready to step it up a notch or three.

My current project will have from hundreds to thousands of records, with a couple of dozen fields in each record. I could revert back to my old ways of doing things, but I see how much you guys seem to like and use XML, SQLLite, and MySQL and it makes me think it's time to revamp my thinking.

I've searched the forums and documentation quite a bit but I'm still not quite sure where to start. There's a wealth of information on these here forums but I feel like my search results have given me such a widespread, shotgun blast kind of image of using AMS to handle databases and I have more questions than I did before searching.

Does anyone have a good recommendation for someone new to DB programming? Some of my requirements or items of no concern are:

1. Requirement - free or cheap, possibly included like XML functionality
2. Requirement - limited or no distribution requirements if possible - i.e. no additional processes or installations required
3. No concern - encryption/security is not an issue - files can be openly accessible
4. No concern - speed is not an issue - only small bits of data read/written at a time
5. No concern - size of the data is not really an issue other than it's too big for the 32k-64k INI limit

I'm looking forward to any feedback you guys can give me. Thanks in advance.

-AA

[Ulrich]

If you want a zero-install, serverless database, then SQLite is your way to go. You can find several good IDEs for designing the database, and running queries on it. SQLite is very easy to use, yet you need to learn the basics of the SQL syntax.

Ulrich

[AmigAtari]

As usual, your response is much appreciated. Thank you very much. I will begin looking in the direction you've suggested right away.

-AA

[Ulrich]

If you are just beginning with SQLite or SQL in general, you may find Tigg's excellent tutorials very helpful. These were done for an older version of AutoPlay Media Studio, yet most of the things which are explained still apply today.

Episode 1

• Enable the SQLite plugin
• Add Objects to the page to provide a project flow
• Create an SQLite Database
• Add a table to the Database
• Create a function to read the Database and populates a listbox
• Create a function that adds entries to the Database

Episode 2

• Review the SQL SELECT and INSERT statements
• Create a function that will read the database and display the information from the selected record
• Create a button to Clear the data entry fields
• Modify the Text on our Add Entry button to make it an Update Entry button

Episode 3

• Review the SQL UPDATE statements
• Create a function that will update a selected record
• Create a function that will encrypt a string
• Create a function that will decrypt a sting
• Modify our project to incorporate these new functions

Episode 4

• Review the SQL CREATE TABLE statement
• Create a password script to limit login to the application

Episode 5

• Finish the password script to secure our application
• Define some of the method behind my madness
  (Read: Why did he create the script like he did?)

Episode 6

• Review the SQL DELETE Statement
• Integrate a Delete function into our project
• Update other functions as necessary

Episode 7

• Start new project
• Discuss relational databases and why we use them
• Review the database for our project
• Create a query using a JOIN statement

Episode 8

• Review the DISTINCT and ORDER BY options when forming a query
• Discuss Aggregators available in SQLite
• Integrate code using this new information into our project

Episode 9

• Add code to make the ListBox object load quicker
• Review the database schema to identify what information should be returned in order to build an invoice
• Build a query to return information from multiple tables
• Integrate code into the project to start building an invoice

Ulrich

[AmigAtari]

This is exactly what I was looking for. Thanks!

-AA

[Nickj]

I like that Amiga comes first, just as it should

Watch out in these examples, they are for an older version and with some of the changes in the LUA scripting libray (now using 5.1) you need a slightly different syntax. The most obvious one (maybe the only really important one) is about 8 minutes in to the first tutorial...
line 10 of the scripting is for i,v in tbReturn.data do This will not work :(
You need to use for i,v in pairs tbreturn.data do

It is in the help file but it's easy to overlook

[AmigAtari]

Thanks for the additional pointers, Nickj. That pairs thing has bitten me once or twice in the past. I had used older versions of AMS/Lua a lot more than the recent versions...

I agree about the Amiga thing. However, Atari (8-bit) was like my first love where Amiga was my soul mate.

[RizlaUK]

You could try CS's INIPlus action plugin, its xml driven but uses a ini like syntax and is something your already used to

http://www.amspublic.com/action%20plugins.html?plugin=INIPlus

Personally speaking tho i would learn to use SQLite as your goner need it at some point, right?

Search the forum for TJ Tigger (and or) Tigg TV, its what got me started with SQLite way back when

[RizlaUK]

for i,v in pairs( tbreturn.data ) do

Pairs is a function, a bit like my ex misses, it needs braces!


EDIT: And ignore the Tigg TV advice, i see Ulrich was already on it!

[kingzooly]

Like Ulrich and Riz(Hope family is OK bud) have said Tigg TV's advice on that matter is one of the best going, I sure its saved many people hours of woops I messed up what do I do

I have learnt a lot from them and will be watching them again to update my brain as I have not worked with sqlite for a while now, good luck with the project.

Rex

[AmigAtari]

You could try CS's INIPlus action plugin, its xml driven but uses a ini like syntax and is something your already used to

http://www.amspublic.com/action%20plugins.html?plugin=INIPlus

Personally speaking tho i would learn to use SQLite as your goner need it at some point, right?

Search the forum for TJ Tigger (and or) Tigg TV, its what got me started with SQLite way back when

Rizla, man... that's cruel. You dangle a tasty carrot in front of me with increased capability yet such ease of use with INIPlus, and then force me to recognize that you're right - I should embrace SQLite eventually... Crap...

I'm going with SQL I believe, but I'm still checking out INIPlus as well. Not to mention Centauri's other offerings on that site. That's cool. I'm glad someone has a site up like that!

Thanks!

[AmigAtari]

Like Ulrich and Riz(Hope family is OK bud) have said Tigg TV's advice on that matter is one of the best going, I sure its saved many people hours of woops I messed up what do I do

I have learnt a lot from them and will be watching them again to update my brain as I have not worked with sqlite for a while now, good luck with the project.

Rex

Thanks, kingzooly. I've already been watching a few of his videos to get an overview. I'll be re-watching them when I get down to the nitty-gritty coding. I know SQLite isn't too big of a concept to grasp, but so far in my life I've managed to skate by on purposefully avoiding DB programming almost completely. Now I don't have a choice, it seems.

Thanks for all the pointers, everyone.

[RizlaUK]

Rizla, man... that's cruel.

HaHaHaHa!, Soz!

I'm going with SQL I believe

Good Stuff, you wont regret it, lol

I'm glad someone has a site up like that!

http://www.imagine-programming.com/

http://http://www.amsplugins.com/

http://www.mindquake.com.br/

and my site will be back soon!

[RizlaUK]

Rex: the kids are good, all i care about now, the misses, history!!, being single is great, forgot how much fun life could be!!

[kingzooly]

Rex: the kids are good, all i care about now, the misses, history!!, being single is great, forgot how much fun life could be!!

I have to say I am enjoying married life and our little boy is helping test how well we deal with no sleep lol he's 3 weeks old at 23:49 GMT 0

What will your site be about? and yes these old resources still kicking about are god sends, stange been here as a green member lol