NewsForumsCodeLogsGallerySpecsSearch

Using Brucey's SQLite Mod

BlitzMax Forums/BlitzMax Beginners Area/Using Brucey's SQLite Mod

Arabia(Posted 2013) [#1]
First Questions: Had to reinstall and finally got it all working using Blide (rebuild Modules), not that it matters too much, but I can't get the standard MaxIDE to rebuild the Modules, it's greyed out in the menu. Any ideas?

Just going through this demo from the SQLite Mod:

SuperStrict

Framework BaH.DBSQLite
Import brl.filesystem


DeleteFile("maxtest.db")

Local db:TDBConnection = LoadDatabase("SQLITE", "maxtest.db")


If Not db Then
	DebugLog("Didn't work...")
	End
End If

Local names:String[][] = [ ..
	["Alfred", "Aho"],  ..
	["Brian", "Kernighan"],  ..
	["Peter", "Weinberger"]]

If db.isOpen() Then

	Local s:String = "CREATE TABLE person (id integer primary key AUTOINCREMENT, " + ..
	  " forename varchar(30)," + ..
	  " surname varchar(30) )"

	db.executeQuery(s)

	If db.hasError() Then
		errorAndClose(db)
	End If
	
	' transaction test :-)
	db.StartTransaction()

	If db.hasError() Then
		errorAndClose(db)
	End If

	DebugLog(names.Length)
	For Local i:Int = 0 Until names.length
		db.executeQuery("INSERT INTO person values (NULL, '" + names[i][0] + "', '" + names[i][1] + "')")
		If db.hasError() Then
			errorAndClose(db)
		End If
		
	Next
	
	' commit our changes :-)
	db.Commit()

	If db.hasError() Then
		errorAndClose(db)
	End If

	Local query:TDatabaseQuery = db.executeQuery("SELECT * from person")
	If db.hasError() Then
		errorAndClose(db)
	End If

	While query.nextRow()
		Local record:TQueryRecord = query.rowRecord()
		
		DebugLog("Name = " + TDBString(record.value(1)).value + " " + TDBString(record.value(2)).value)
	Wend
	
			
	db.close()
	
End If

Function errorAndClose(db:TDBConnection)
	DebugLog(db.error().toString())
	db.close()
	End


This works fine. However, if I try to incorporate my own stuff into this program - i.e. if I put a Graphics 800,600 at the top of the code, or a Print statement within it to print something to the console I get an error :


Compile Error: Identifier 'Graphics' not found
Build Error: failed to compile C:/Users/John/Desktop/Cricket/databasetest.bmx



Any ideas what I'm missing here?


Brucey(Posted 2013) [#2]

Compile Error: Identifier 'Graphics' not found


It's because of the use of "Framework BaH.DBSQLite"

Framework sets the base module for your application. If you need to use other modules that this base module doesn't itself require, then you need to "Import" each of them.

On the other hand, if you don't use Framework, *ALL* of the BRL/Pub modules will be included in your application by default. Then you need to Import any modules that are not part of BRL/Pub.

I tend to use Framework for all my stuff. Other people don't. Each to their own ;-)


xlsior(Posted 2013) [#3]
But I can't get the standard MaxIDE to rebuild the Modules, it's greyed out in the menu. Any ideas?


That normally means that Blitzmax doesn't see the environment variable that specifies the MinGW installation location.
Either you forgot to set it, or you didn't reboot your computer after doing so.

And FWIW: There's a free piece of software called 'framework assistant' that will automagically create the framework/import statements for your program.
Download link: http://homepage.ntlworld.com/config/fa/fa.zip


Arabia(Posted 2013) [#4]
Thanks guys.

@Brucey - Yeah I figured it had something to do with the Framework and figured I might need to include the other stuff I was using, was just puzzled that I didn't need it previously but did now. Your explanation clears it up.

@xlsior - I had previously used the Framework assistant, just hadn't got around to downloading it again after my last hard drive crash. And to your other point why MaxIDE can't build modules, I doubt I've restarted the laptop since I don't normally (unless it crashes) so that is likely the cause. Cheers.

One more little problem has since shown up. Everything working on the laptop, now trying to get it working on the desktop (Same OS - Win 7) and GCC can't be found despite user variable MinGW being set to C:\MinGW and C:\MinGW\Bin being added to the Path System variable.

I can't run C:\MinGW\Bin\GCC so the file is there, but run it from outside this directory or don't specify the full path and I get a 'gcc' is not recognized.

Edit: The modules build fine using Blide or MaxIDE, just can't run GCC (not that I need to) from the command prompt without specifying the full path which is weird.


xlsior(Posted 2013) [#5]
Changes to the PATH don't take effect until after a reboot either.


Arabia(Posted 2013) [#6]
Changes to the PATH don't take effect until after a reboot either.



Actually rebooted twice, checked spelling of everything twice (since I was tired and also had a few drinks) but still nothing.

Anyway, modules compile despite me being able to run GCC from any directory without supplying the full path.


Arabia(Posted 2013) [#7]
Still messing around with this, got my own table set up and I'm getting this error after my db.commit()

DebugLog:(1) Error committing transaction : cannot commit - no transaction is active : Successful result

Yet if I open up the database in SQLite Database Browser I can see that my table was created and that there is data in it.

This is the code that is throwing the error, even though it's working and adding the record to the table:

s = "CREATE TABLE batting (playerID INTEGER PRIMARY KEY AUTOINCREMENT, " + ..
	" country varchar(20), player varchar(30), spanStart numeric, spanEnd numeric, " + ..
	" matches numeric, innings numeric, no numeric, runs numeric, hs numeric, " + ..
	" hsno varchar(1), avg numeric, bf numeric, sr numeric, hundreds numeric, " + ..
	" fifties numeric, ducks numeric, fours numeric, sixes numeric)"
	
db.executeQuery(s)

db.executeQuery("INSERT INTO batting values (Null, 'Country', 'Player', 1, 2, 3, 4, 5, 6, 7, 'N', 1, 2, 3, 4, 5, 6, 7, 8)")

db.Commit()


Any ideas why I'm getting this error?


Henri(Posted 2013) [#8]
Hello,

you need db.StartTransaction() to couple with db.Commit() if doing transactions, otherwise leave db.Commit() out and your query is executed immediately.


-Henri


Arabia(Posted 2013) [#9]

Hello,

you need db.StartTransaction() to couple with db.Commit() if doing transactions, otherwise leave db.Commit() out and your query is executed immediately.


-Henri



Thanks Henri, works a treat now.

If I know my SQL from 20+ years ago (and I'm sure I probably don't) then I only need to worry about using transactions if I also want to be able to rollback the database, is that correct?


Henri(Posted 2013) [#10]
Also you should test that query was succesfull before proceeding.

Like:
db.ExecuteQuery(s)

If db.HasError()
     Notify db.error().toString() ; db.Close()
EndIf



-Henri


Henri(Posted 2013) [#11]
To previous:

Yes, that is correct.


EDIT:

Here is your example in full. Here both queries would fail if one of them gives an error

db.StartTransaction()
If db.HasError() ErrorAndClose(db)

s = "CREATE TABLE batting (playerID INTEGER PRIMARY KEY AUTOINCREMENT, " + ..
	" country varchar(20), player varchar(30), spanStart numeric, spanEnd numeric, " + ..
	" matches numeric, innings numeric, no numeric, runs numeric, hs numeric, " + ..
	" hsno varchar(1), avg numeric, bf numeric, sr numeric, hundreds numeric, " + ..
	" fifties numeric, ducks numeric, fours numeric, sixes numeric)"
	
db.executeQuery(s)
If db.HasError() ErrorAndClose(db)

db.executeQuery("INSERT INTO batting values (Null, 'Country', 'Player', 1, 2, 3, 4, 5, 6, 7, 'N', 1, 2, 3, 4, 5, 6, 7, 8)")
If db.HasError() ErrorAndClose(db)

db.Commit()
If db.HasError() ErrorAndClose(db)


Function ErrorAndClose(db:TDBConnection)
	Notify db.error().toString() ; db.Close()
EndFunction



-Henri


Arabia(Posted 2013) [#12]
SQLite Tutorial <- The first SQL Tutorial I looked at after a Google search, already found a heap of stuff that I've forgotten you could do with SQL.

If you are writing any program with a serious amount of data, SQL and Brucey's Modules are really worth learning, makes life a whole lot easier once you get the hang of it :)


Arabia(Posted 2013) [#13]

Also you should test that query was succesfull before proceeding.



Yep, I will do once I get a bit further into what I'm doing.


Henri(Posted 2013) [#14]
Did a little update. See above


-Henri


Arabia(Posted 2013) [#15]
Thanks Henri.


Brucey(Posted 2013) [#16]
then I only need to worry about using transactions if I also want to be able to rollback the database, is that correct?

Yep. Usually if you are doing a bunch of inserts/updates and having one fail would require you to forget everything you've done. Without the transaction, you'd have to go back and undo everything you did by hand…

Since SQLite is (mostly) ANSI compatible, you can interchange most SQL between it and others, which makes life a bit easier if you are looking for examples of SQL to do things, as most (non-db specific) examples would drop straight into SQLite.


Arabia(Posted 2013) [#17]
It's going to be interesting to see how SQLite performs with a lot of data.

So far I've just got to the stage where I'm parsing a text file and adding the data to the database.

Using my laptop which is a Pentium 2.2GHz, 2Gb RAM running Win 7 64-bit it takes just under 1 minute to add all the records. Hoping that running queries is a hell of a lot faster than adding records. I'll mess around with some queries shortly and see how they perform.

The database I'm anticipating having will be BIG, so performance may well be an issue. Should I possibly be looking at another database? Being open source and used by some pretty big companies I'd have thought that SQLite would be right up there in terms of performance.

EDIT: Just tried my a couple of queries and put some WHERE and SORT clauses in and even with printing the results the time taken is under 1 second, so speed doesn't look like it will be an issue :)

Also having a look at a couple of the other examples that Brucey provided with his module and it looks like there may be a more efficient way of inserting records, not that it is really an issue, the data will be imported only once and waiting a couple of minutes for this isn't a problem.


Brucey(Posted 2013) [#18]
Prepared statements are always recommended for any common queries.

Also, indexes will improve data retrieval times.

You may also find doing bulk inserts inside a transaction may speed things up a bit.

How big is BIG?


Arabia(Posted 2013) [#19]
How Big? It's hard to say while I'm still working on it, need to make sure I set up the database correctly, but I'd imagine 20+ tables with some tables having in excess of 4000 records.

I consider this big, but in grand scheme of things it probably isn't all that big compared to what databases are made to handle.

I'm working again on a sport simulation program that I first started in BM a couple of years ago, just the table that holds the players details is 2600+ records.


Derron(Posted 2013) [#20]
Insertion will be really fast if you use the "transaction"-style brucey suggested. (Think of it as: write all in one step instead of openfile-write-closefile each time)

Queries will be slow if you have many fields which get concenated for boolean comparison ("join teams as joinedteam ON joinedteam.id = player.team where attack>10 and speed in (10,11,12) and ...")

The last time I worked with sqlite was sqlite2, don't know what kind of db"types" it supports now but on mysql/mariadb you can choose between innodb and myisam (and more) ... innodb is slower on inserts but has restrictionhandling and I think better triggers.
So the right type will have impact too.

bye
Ron


Arabia(Posted 2013) [#21]
I will look more into the transaction style insertion, it doesn't seem all the difficult. But once I have the tables set up with data, there won't be that much going on in terms of inserting new records, it will just be maybe 10-20 records at a time being updated with the odd new record being added, not 2000+ records at a time, so speed for this part is not really a major issue - just a matter of giving it the raw text CSV file, going off and have a dinner and come back at it will be done.

The queries are really the important part, being a simulation I'm planning on having results for every international game ever played in the database (the sport in question is Cricket) and up to yesterday there have been 2088 matches played, so in order to calculate player rankings a query will have to be run for example

If the match being simulated is for the year 1990 between Australia & England then I'll need to get

1. A list of players that are eligible (i.e. only those players who were active during this year) - that's an easy and quick query and then
run the follow queries which will require tallying up information to get averages etc. which I'd assume is rather more time intensive processor & database wise:
2. Their stats (complete stats of every game they played and their average)
3. Their stats against the team they are going to play(so how an Australian performs V England)
4. Their stats for how they play in a particular country against that team as some players perform better during home series than they do abroad.

So overall it's a pretty complex database, once I get a fair bit more done I'll be able to run some queries and see if what I want to achieve is doable or not.

Of course I could always just provide these stats straight into tables, but then this would mean constant downloading of updated stats for the users (if I ever finish this game) when a new real life match is played.

Now that I've got the basics down of storing and retrieving, the next step is to work out how to most efficiently store the 2000+ existing score cards in the database and run some queries to see how it performs. Should be interesting :)


Brucey(Posted 2013) [#22]
...with some tables having in excess of 4000 records


Heh... so not very big in the scheme of things.

I don't think you need to worry about size/speed issues with such limited sets of data. If you do find it's not as fast as you expect, it could be down to issues with your SQL.

In my day-job I'm dealing with systems handling millions of new row data per day, which is where you start to get concerned about speeds ;-)


Gotta love stats though... I've got a bookcase full of F1 and American Football stats books... and have written some stats apps for pbem games I've played. Fun fun fun :-)


Henri(Posted 2013) [#23]
I can confirm that SQLite performs at amazing speed.

Few pointers:

1. If doing more than one query with same query structure (only datavalues change) then use prepared statement. It saves a lot of time and is not so resource consuming. With prepared statement just make sure to loop through all rows in resultset if you use SELECT-statement (get all data with while..wend loop) otherwise statement might fail.

2. If you use Max() SQL-function to get maximum value in some table and that field is indexed, remember that SQLite is optimised to use only one Max() per statement and preferably nothing more. This way the result is almost instant even if there are million records.


-Henri


Arabia(Posted 2013) [#24]
Quick question (which one of you guys probably already know the answer to so I don't have to do it myself lol) - if you have a table with the following values:

4
8
3
null
5

If you find the average, does it come out as 4 (20 / 5 results) or 5 (20 / 4 - ignores the Null)?

Just trying to work out to store some info as records kept over 130 years ago aren't what they are now - i.e. some information that is recorded today was not recorded back then.


Brucey(Posted 2013) [#25]
According to the documentation, avg()
returns the average value of all non-NULL X within a group
.
Which implies it ignores null values and only calculates the average based on actual numbers.

If you want a handy util for playing with sqlite databases, there is a plugin for Firefox called SQLite Manager, with which you can experiment more easily.
It even supports import/export, useful for posting examples in forums ;-)
DROP TABLE IF EXISTS "test";
CREATE TABLE "test" ("numbers" INTEGER);
INSERT INTO "test" VALUES(4);
INSERT INTO "test" VALUES(8);
INSERT INTO "test" VALUES(3);
INSERT INTO "test" VALUES(NULL);
INSERT INTO "test" VALUES(5);



SELECT AVG(numbers) from test;


Have fun!


Henri(Posted 2013) [#26]
SQLite Expert personal is great standalone tool to create sqlite databases in more visual manner. You can also type your statements straight into box and hit execute to test your queries. And it's free

-Henri


Arabia(Posted 2013) [#27]
Thanks guys, I'll give both a look.