to my totally unstructured approach to databases, and may I end up where
I want to be. (If I only knew where that would be :-)) I'm going to talk
about SQLite and MySQL.
I am NOT going to try to explain 'SQL' itself. Well, okay, perhaps a little but just as much as necessary to run and understand the examples. There are enough resources on the web to learn SQL. Of course, I could not entirely control myself :-) so here are a few topics...
Small, compact, fast, and integrated into PureBasic, this is the database of choice for smaller applications, that reside on a single machine. SQLite is not intended to be used with large, corporate style databases with many users and transactions simultaneously.
MySQL is an open source database that IS suitable for larger, multi user applications. I've added a section below dedicated to installing and running MySQL. The approach is pretty much the same, so with very little work all examples should work under MySQL. MySQL on PureBasic uses ODBC...
MySQL is an alternative for MySQL. It's open source and is suitable for larger, multi user applications. PureBasic comes with embedded drivers so you can use it with or without ODBC drivers. More about installing and running PostgreSQL.
What is ODBC? http://www.tech-faq.com/odbc.shtml
ODBC is an interface to access different sorts of databases, such as MS Access, dBase, DB2, AQL etcetera. ODBC needs drivers, and MicroSoft has included some of them with every copy of Windows, but for MySQL you'll need to install one (which is easy, and free).
There's a handy tool for checking our results when messing with SQLite databases, the SQLite Database Browser. I'm not sure if it's actively maintained (probably not :-)) but it seems to work well for basic things. Download the .zip file and use the program inside to investigate your SQLite database files.
You can test your SQL instructions in this program using the third tab called 'Execute SQL'. Enter the instructions and then hit the 'Execute Query' button.
13.2 Getting started: talk to me.
AKA talk to the engine...
PureBasic supports different flavours of databases. To include all routines needed for accessing SQLite databases we add to our code:
UseSQLiteDatabase()Creating a datafile is easy. Just create a normal, empty file, then reopen it using SQLite:
UseSQLiteDatabase()An empty file is of course of little use :-) So what are we going to store in it? And HOW are we going to do that?
Talking to SQL.
SQL is more or less a programming language, a little like PureBasic. We 'throw' instructions at the SQL engine, which in turn takes certain actions. PureBasic has a very limited vocubalary related to databases, as it doesn't need much... It's the SQL engine that does the real work, be it SQLite or MySQL or whatever other beast hides behind an ODBC interface.
These are the core commands we need to talk to the SQL engine:
DatabaseError() returns the last error the database ran into. With this we can get a textual response from the database engine which further specifies the problem we may have ran into.
DatabaseUpdate() sends a command to the database engine, but doesn't expect a reply. We use this to create tables, add colums, set fields etc. If this command returns a zero, then something went wrong (perhaps the SQL statement was wrong, or the database had problems). In those cases you can get more information about the error using DatabaseError().
DatabaseQuery() asks the database engine a question, and (may) trigger a number of replies. Each reply is retrieved with NextDatabaseRow(). DatabaseQuery() itself will return zero if it encountered an error.
When you are done with your query you have to call FinishDatabaseQuery() to avoid memory leaks.
a database we can have multiple tables. Each table consists of rows and
colums, like this:
CREATE TABLE cars ( firstname TEXT )In PureBasic we'd have to send that instruction to the SQL engine using:
DatabaseUpdate(1, "CREATE TABLE cars ( firstname TEXT )" )At creation time we could create multiple colums like this:
CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT )
SQL: adding columns.
We could add columns to an existing table using this:
CREATE TABLE cars ( firstname TEXT ) ;(The semicolon would allow us to deliver multiple instructions at once, it can also be used as an end of line character / statement seperator...)
You could send the above to SQLite using multiple DatabaseUpdate() commands:
UseSQLiteDatabase()Our you could send a whole range of commands to the SQLite engine in a single string, by seperating the SQL instructions with a semicolon:
UseSQLiteDatabase()... and here's another variation:
SQL: deleting tables.
Of course, we can also delete tables using:
DROP TABLE cars
SQL: the semicolon... to use or not to use?
SQL experts please correct me here. It looks like the semicolon is an optional component, which can be inserted whenever there could be doubt about the interpretation of a piece of SQL. Most of the time a CRLF at the end of each line seems to do the trick. Expert advise wanted here!
Fair warning: when spreading statements over multiple lines you may need or want to use semicolons... The results of a wrongly composed SQL statement can be disastrous...
Can't read from an empty table, so let's start with writing. (I should have called this 'writing and reading data', I guess...)
We've build our table using CREATE TABLE and now we need to put in some data. In SQLite we use INSERT and that would look something like this:
CREATE TABLE cars ( firstname TEXT , lastname TEXT , brand TEXT , colour TEXT ) ;Turning it all into a full PureBasic program would give us something like this:
UseSQLiteDatabase()Note that you can specify the columns in the INSERT statement:
INSERT INTO cars ( firstname , lastname , brand , colour ) VALUES ( "mick" , "jagger" , "cadillac" , "black" )The good part about that is you will have an easier time re-reading your code (it's sort of self documenting) and if you change the table column order, your code still will work! Unfortunately, GetDatabaseLong() etc. use the column number, not the column name :-(
In SQL we can read from a table using the SELECT keyword. If we would like to get all rows where the colour of the car is black, we'd use:
SELECT * FROM cars WHERE cars.colour = "black"Try it in the SQLite Database Brower and you will get two entries back, 'jan peter' and 'mick'. We could finetune the selection by adding more critera, for example:
SELECT * FROM cars WHERE cars.colour = "black" AND cars.brand = "volvo"Another option might be combining the results of multiple SELECT's using the keyword UNION:
SELECT * FROM cars WHERE cars.colour = "black" UNION SELECT * FROM cars WHERE cars.brand = "mack"The above should return all entries where the colour is black, and all entries where the brand is 'mack'.
To return the results in a specific order, we'd add ORDER BY:
SELECT * FROM cars WHERE cars.colour = "black" ORDER BY cars.brandAnd here's how we do it all together in PureBasic:
; survial guide 13_3_110 reading and sorting
Number of columns returned.
PureBasic has a few commands that may make life easier, though not everything exists in a basic keyword. Thus, sometimes, you have to use SQL'ish approaches. Here are two examples:
Number of rows returned.
Want to know in advance how many rows a query will return? Use the SQL COUNT function:
SELECT COUNT(*) FROM cars WHERE cars.colour = 'black' ORDER BY cars.brandIn PureBasic:
UseSQLiteDatabase()Remeber to try out your queries using the SQLite Database Browser or the MySQL Query browser before you test them from within PureBasic. It will save you a lot of time... Or you could write your own little database browser (and post the result on the PureBasic forum to help other users)...
You can change any field using the following SQL command UPDATE. The following line changes each and every colour of the cars belonging to the person with the last name 'balkenende':
UPDATE cars SET colour = 'red' WHERE lastname = 'balkenende' ;Or perhaps we'll decide to remove all red cars:
DELETE FROM cars WHERE colour = 'red' ;The two examples above show cases where the semicolon may help to avoid problems, as you are allowed to spread (some) instruction over two lines:
DELETE FROM carsHere's a complete program which creates a table and inserts, changes, and deletes data:
13.4 Data types.
Regular (if there is regular...) SQL supports different data types. There are however many different implementations. SQLite keeps it relative easy: you can read and write in any type, regardless of the defined column type. This is NOT the case with other SQL implementations!
DatabaseUpdate(1, "CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB)" )
; add some text strings
DatabaseUpdate(1, "INSERT INTO types VALUES ( 'text1' , 'text2' , 'text3' , 'text4' ) ;" )
; now add some integer numbers
DatabaseUpdate(1, "INSERT INTO types VALUES ( 5 , 6 , 7 , 8 ) ;" )
Whilst SQLite converts the datatypes for us on the fly, we still need to store them into PureBasic variables. For your convenience we got a few different models on offer...
For reading we can use GetDatabaseLong() and its brethern, as listed below:
For writing we have to embed our variables into the SQL INSERT string, like this:
13.5 The Committed Programmer.
To be committed, or not to be committed, that's the question.
And indeed it is.
In SQLite, data is immediately stored in the database ('autocommit') as soon as a query or update is send. That is, if we don't tell SQLite to wait... It is possible to group a number of instructions together, and have them executed at once. If, for some reason, we want to cancel our transaction, we can do a rollback before we commit ourselves...
UseSQLiteDatabase()The code above will create a database, prepares 4 values to insert, and then commits the change. It will then do so again, but won't commit the data this time. If you use the SQLite Database Browser to browse the resulting database file, you will find it only contains one row.
Again, the Survival Guide is not about teaching you SQL or even SQLite :-) so have a good look at the SQLite documentation for the different transaction types.
The extracted SQLite instructions (doesn't work when entered on the 'Execute SQL' panel inside the SQLite Database Browser, perhaps because that one uses an older version of SQLite?)...
CREATE TABLE types ( text TEXT , integer INTEGER , real REAL , blob BLOB) ;
13.6 Who calls me a Blob?
I never would. Never. At least not in your face :-)
A blob is a mechanism to store binary information (images etc.) in your database. Until 4.40b1 we had to use alternative tricks, but now we have native blob's... Here are the options:
Linking to images.
It may sound funny, but sometimes the right way to store images inside a database is not to store them inside the database... euh... argh! :-)
Seriuosly, imagine you have a relative small database and a large collection of AutoCad drawings, stored on some drive. Those drawings are updated using AutoCad, so AutoCad needs access to them. It would be a pain in the ... if you would have to export the drawings each and every time from the database, store them locally, edit them, then import them back into the database.
In those cases it may be a very valid option to simply create a text field with a path to the document.
One note though: it may be wise to think in advance how to deal with (absolute) paths, and what must be done if files are moved from one drive to another... Manual renaming may not be such a 'comfortable' option...
So, how can we store binary informaton then? We could store them in a string (though we have to make sure the resulting string complies to the rules, ie. no CHR(0) NULL characters are allowed). One way to do this is use Base64 encoding, this is an old encoding scheme used to transport binary data over media that do not support binary data. The example from the help file shows how to use this with a string as an example. Of course nothing would stop you to encode an image, just point towards the place in memory where the image is located, and pass on the length... See the help file.
(By the way, you need to switch off 'Enable ASM Inline Support' in 'Compiler Options' for the sample below, otherwise the PureBasic compiler thinks test, dec and enc are assembly instructions :-))
test.s = "This is a test string!"The big disadvantage of this approach is the size increase, typically more than 30%... This happens because Base64 encoding tries to avoid any character that could cause problems to 'text only' applications...
Yenc is the modern day version of Base64. It's immense popular due to its use in newsgroups where it is used to spread binaries. PureBasic does not have a native Yenc onboard but you can code one.
Ah. Still working on this... more to come once it's done...
4.40b1 brought us the Blob. (Actually, I think McDonalds was first, but I might be wrong :-)) There's little explanation in the not-yet-ready docs of the beta, but thanks to my exceptional sleuthing talents (and the answers in the forum :-)) here's what's going on:
UseSQLiteDatabase()Again: what is a blob?
(No, it's not a McDonalds customer :-) ...and for the lawyers amongst us: this is artistic freedom, not a specific snarl at a specific company. Personally, IMHO etc. I think the hamburgers are horrible, but compared to the cardboard pieces the great belgian chain Quick serves they are haute cuisine...B ut that's personal opinion. Artistic freedom. Disclaimers, right? Sigh. Let's go on.)
With the statement SetDatabaseBlob() we prepare some data to be 'blobbed'. If you look at the DatabaseUpdate() statement in the sample code above, you will find two questionmarks. We're using these questionmarks as placeholders, and these questionmarks / placeholders will be replaced with with our blob data upon execution.
In the example above I used two longs (each 4 bytes long). The position in memory where a.l resides is my first blob. The position in memory where b.l resides is my second blob. The SECOND parameter of SetDatabaseBlob() tells PureBasic which questionmark / placeholder will be replaced with the blob. These placeholders are numbered from zero upwards. Zoom in on these three lines:
DatabaseUpdate(0, "INSERT INTO rubbish ( blob1_data , nouse , blob2_data ) VALUES ( ? , '1' , ? ) ;")
Zoom in on the sample code above, and look for these three lines:
GetDatabaseBlob(0,0,@a,4)Remember: we used column 0 for the first blob, column 1 for a long that's of no use in this example ;-) and column 2 for the second blob. Here we refer to the actual column number
The second parameter of GetDatabaseBlob() speficies the table column number. This is NOT the same as the 'index' used by SetDatabaseBlob().
The command DatabaseColumnSize() returns the size of the specified column of the selected row. This means you can insert a blob and later retrieve first the blob size, allocate memory, then retrieve the blob data using the following approach:
buffer_l.i = DatabaseColumnSize( 0 , 0 )
MySQL is one of the most well known SQL server packages. Note that there is dispute about the usability / legality in a commercial environment, due to some license restrictions. It appears to me that you can use it, but that you will have to use ODBC to avoid license issues. You may consider PostgreSQL as an alternative.
this is not a tutorial on SQL, MySQL, or ODBC. I'm only interested in setting
things up so I can use PureBasic to mess around with them :-) Still it
is worth it to have another look at the issue in case we'd become too succesful
Some notes on the license issue... I'm not a lawyer (otherwise I would be working for a patent troll, be indencently rich, and not be writing this :-)) so take the following at face value... In other words, my humble opinion, your mileage may vary, at your own risk, disclaimers etc. etc. etc...
Unless you BUY a license of MySQL you need to adhere to the GPL. As far as I can tell nothing is going to stop you from using MySQL in a non-commercial or commercial project, as long as you adhere to the license. The key is the term 'derivative work'.
1. Embedding GPL'ed SOURCE in your code would clearly force you to use the GPL license for your whole product.
2. STATIC LINKING of a GPL'ed library makes the GPL'ed code and resulting program part of your program and thus forces you to use the GPL license.
3. DYNAMIC LINKING is a bit more tricky. Strictly spoken the functionality of the DLL becomes part of your program, but the code itself does not. The jury is still out on this one. If your program would use functionality exposed by an under GPL developed and distributed DLL, which DLL was developed and distributed for such a purpose, and which DLL is not distributed as part of your program, then you're probably not obliged to go GPL. However, it's inside the grey zone.
4. If the whole GPL'ed program is an integral element of your solution, and you would install it (perhaps automatically) as part of your (commercial) solution, you'd be entering the grey zone as well. Most definitely if you would hide such an installation from the user (as part of a commercial package).
The above doesn't stop you from using MySQL. Using functionality provided / exposed by MySQL ODBC connector software, or communicating with the MySQL server is not considered derivative work so should be fine. You (probably) cannot automagically install MySQL and / or the MySQL ODBC connector, and you (definitely) cannot embed a MySQL library in your program. But nothing is going to stop you from installing or using a MySQL platform as part of a commercial and / or non-GPL'ed solution, it just cannot be part of your program.
Too risky? Go PostgreSQL :-)
You might want to run this on a dedicated machine, server, or virtual machine. I used VirtualBox to setup such a 'dedicated' MySQL server on a virtual machine... Read more about using VirtualBox here. MySQL needs one side to be a server, and the other to be a client, but nothing is stopping you to install server and client software on the same machine. (Frankly, if you're just going to use MySQL as a simple local database, there's very little reason to use a dedicated machine, but then again why are you not using SQLite then?)
are complete packages around including tools, configuration etc. such as
Xampp. They may make your life easier, especially if you're interested
in building applications for the web. I may revisit Xampp once I find the
courage to use PureBasic for web applications... For now, it's though enough
as it is :-)
Machine and Windows.
Create a VM if you're going to use VirtualBox or something similar. You may consider assigning a fixed IP to your server. I changed the name of my dedicated MySQL server to 'sqlserver'.
1. Download the MySQL server essentials package and install it. I used 5.1 and ticked the option 'custom' as I like to think of myself as an expert (which, obviously, I am not :-))... Note: when reinstalling MySQL it couldn't start the service, no matter what I tried. In the end, I cloned a new VM and reinstalled... (I should have used a snapshot in VirtualBox but I forgot to create one :-)) I think I messed up a password which wasn't removed during de-installation, but that's the good thing about imaging and / or virtual machines... restoring is a lot easier.
2. The default port is 3306. Tick the box 'add firewall exception' if you're using Windows firewall. Note: this may not suffice, and you might have to allow traffic manually! When in doubt, check functionality by shutting down the firewall temporary. Oh, and if you are using the host name on the client to find the server, enable 'file and printer sharing' on that server otherwise the client won't find the host!
3. Tick 'best support for multilingualism'. Install as a Windows service and have it launched automatically. Also tick the option 'include bin directory in path'. Next etc....
4. Modify security settings and enter a new root password. For simplicity I used the password 'root' here... not very safe, but this is a test environment, not a production server. Next etc....
5. Hit the 'execute' button and hope it doesn't crash during installation. (It did here twice...) Next etc....
Tada. You've now got your own MySQL server up and running as a service on your virtual machine. Congratulations.
These tools make your life a little easier, unless you're a die-hard that likes the command prompt....
1. Download and install the GUI tools package and install it. I used 5.0 and did install all except the MySQL migration toolkit. Next, install, finish etc, you know the drill...
2. Under Windows Start / Programs you'll find a new folder MySql. Start the MySQL Tray Monitor. Click on it with the RMB.
3. Switch ON the option Monitor Options / Launch Monitor After Login.
4. There's another tool you'll find there called MySQL Administrator. You may want to drag it onto the desktop for quick access.
You can now test your setup...
that you can install the MySQL GUI Tools on your clients, if you want to.
Especially the MySQL Query Browser may
come in handy if you want to experiment with the SQL language itself.
Start the MySQL administrator. Use the following settings:
Start the MySQL Administrator on your new SQL server and log in as root. Select Use Administration / Add New User. I added a new user called 'user' with password 'user'... (Yes, I'm a very creative person.)
1. Start the MySQL Query Browser. This one came with the MySQL GUI Tools...
CREATE DATABASE PUREBASIC3. Execute the query (using the lightning icon, or [Control] + [Enter] ). These statements will now create a database called 'purebasic'.
In all future calls we might use the MySQL Query Browser with 'default schema' set to purebasic, we just needed that build-in 'test' database this one time to start up the query browser and create our own first database. The program is supposed to let us connect to the MySQL service without a database given, but I didn't get through without one.
is more than one way to talk to the MySQL server, but from within PureBasic
the easiest one is using ODBC.
MySQL ODBC Connecter.
1. Download the MySQL ODBC connector for Windows and run it.
2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine...
3. Add MySQL ODBC driver.
4. Hit the 'Test' button. If the ODBC connector cannot connect to our MySQL Server then most likely a firewall is causing the problem, either on the client or on the server. Try it with the firewalls turned off. If that works, you might test with the IP address of the server instead of its name, ortry
5. Run the program below. It should create a little table and produce the same results as our SQLite version...
; survival guide 13_9_110 mysqlHere's the full setup again, running MySQL in a VirtualBox VM, executing the program above in PureBasic...
From a PureBasic and SQL point of view there is little difference between MySQL and SQLite. It's important to keep in mind that MySQL is more strict on data types.
To turn the SQLite examples above into MySQL, do the following:
In SQLite with a single user application you might skip BEGIN / COMMIT but in multi user applications in a network I would strongly advise to use them.
PostgreSQL is 'the other' open source database, but it doesn't suffer from GPL issues. This means that PostgreSQL drivers / libraries can be linked with / embedded in other programs. Which is exactly what PureBasic did :-)
You will always need to install PostgreSQL on the server side. You may chose to use ODBC on the client side, or use the onboard drivers of PureBasic.
I found the installation and configuration of MySQL marginally easer, and the MySQL GUI tools are nice, especially the MySQL Query Browser. However PostgreSQL contains a similar tool, and if you look around on the Internet you'll find some alternatives, I'm sure.
needs one side to be a server, and the other to be a client, but nothing
is stopping you to install server and client software on the same machine.
(Frankly, if you're just going to use PostgreSQL as a simple local database,
there's very little reason to use a dedicated machine, but then again why
are you not using SQLite then?)
Machine and Windows.
a VM if you're going to use VirtualBox
or something similar. You may consider assigning a fixed IP to your server.
I created a new VM and changed its name to 'sqlserver'. In fact, I installed
MySQL and ProgreSQL on the same VM without any problems.
1. Download PostgreSQL 8.4.1. Use the regular package and install it.
2. The default port is 5432. Choose eventual passwords wisely. (I did not, so 'postgres' it is, everywhere :-)) Install.
3. Look for a file called 'pg_hba.conf'. It's in the PostgreSQL folders somewhere. If your local network runs in the 192.168.0.x range, then you will have to add the following line:
host all all 192.168.0.0 255.255.255.0 md54. Check your (Windows) firewall configuration. Open port 5432. You may have to enable file and printer sharing on your SQL server.
5. You may want to put the link 'Postgress pgAdmin III' on your desktop for easy access.
6. Start up pgAdmin. Connect to (doubleclick) the PostgreSQL server (localhost port 5432 user postgres password postgres).
RMB on 'databases' and add a new database called 'purebasic'.
The PostgreSQL query browser.
PostgreSQL also includes a query browser. To use it do the following:
1. Start up pgAdmin.
2. Select 'purebasic' under 'Databases'.
3. EIther select Tools / Query tool, or hit [Control] + [E]. Note that some commands that work under MySQL don't work on PostgreSQL.
Seriously, you could do an ODBC client side install, but PureBasic already contains the PostgreSQL library, so there's no real need.
Use the following code to check if your PostgreSQL setup is working:
; survival guide 13_13_100 postgresql
Of course, I couldn't help myself and had to try :-)
1. Get the ODBC connector software, the one I used I found on the PostgreSQL website, under file browser / odbc / versions / msi / psqlodbc_08_04_0100.zip.
2. Unzip and install it.
2. Look for Start / Programs / Administrative Tools / Data Sources (ODBC) and start it. This tool may be located somewhere else on your machine...
4. Add PostgreSQL ODBC driver.
6. Try the following program:
; survival guide 13_13_200 postgresql via odbc