SQLite DataBase : How to set PrimaryKey & AutoIncrement etc

[Traxgeek]

Hi,

I've managed to follow the various demo stuff to create a SQLite database within LiveCode - no real issues there
BUT what I seem unable to do is to set the first field as the Primary Key and have it Auto Increment etc...

I've downloaded and am trialling SQLite Admin which will allow me to do this outwith LiveCode but I really need to be able to do this from within LiveCode - exactly as I've done so far to detect if the DB exists and, if not, create one and then detect the existance of each of the DB tables/files and, if not, create each table/file and then all the the records within each of them.
I just don't see how to configure the various 'properties' of each field within a database table...

fyi, I've been using the SQLite example code and specifically the :

Code: Select all

put "CREATE TABLE DBImages (ID char(8), Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]   )" into sDBTableData
revExecuteSQL nDBConnectionID, sDBTableData

This all workd BUT I need tell the DB that the first field (ID) is to be the Primary Key and is to Auto Increment... etc... etc...

Can anyone kick me in the right direction please ?

Eternally grateful... Cheers

[Klaus]

Hi Traxgeek,

looks like it's time to learn some more SQL

This one got me started with SQL:
http://www.w3schools.com/sql/

Find the answers to your specific questions here:
http://www.w3schools.com/sql/sql_primarykey.asp
http://www.w3schools.com/sql/sql_primarykey.asp

I ususally use a custom property to store my "create db/table" stuff
(with some placeholders if neccessary), better readable and managable!

Best

Klaus

[Traxgeek]

Wow - now THAT was quick - I'd hardly finished typing ! Thanks a mil !!

However : errrm - understand the words... but the meaning is... rather garbled (at best )

I'm off to start reading up on SQL again !

I'm also off to look up 'custom properties' !

and you're right... setting up a DB Table in LiveCode as code on one line is horrendous - in that it's a VERY long line of code with LOTS of commas... Yeuch !

To be fair the datbase tables do get created but I failed to see the code I need to set the various field properties in addition to the (eg) char(xx) property...

Phew ! Just sooooooo much to learn.

Again, Thanks a mil.

[Klaus]

Hi Traxgeek,

until you "got" custom properties (took ME a while), you can always use a hidden field!

Add this (bold):
...
put "CREATE TABLE DBImages (ID char(8) NOT NULL AUTO_INCREMENT, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]... PRIMARY KEY (ID) )" into sDBTableData
...

Best

Klaus

[sturgis]

Think id has to be integer, and also think (not where I can easily test right now)

something like (ID integer primary key,.... ) will work.

http://www.sqlite.org/faq.html#q1 <-- another resource, sqlite specific. Seems methods differ a little between mysql and sqlite. (or maybe just more options with mysql?)

[Traxgeek]

Thanks for the help...
the 'light' IS dawning but it's dim and so very, very far away at the moment

I decided to take my DB back to just one table and monitor its creation using the (free trial) LcSQLiteAdmin app.

The idea works in that I can now 'see' the results of my furious tap, tap, tapping. Rather blunt / 'beat 'em to death' / 'trial and error' but then the various 'instructions' I have all appear to offer some error...
The instructions re: how to creat a SQLite DB from within LC are straightforward enough - and work !
As are the instructions re: adding a table !
But, I now need to go off-piste a wee bit and am trying to modify some of the properties of the first column/item in my table. Ideally I want to make it a Primary Key, Not Null and Auto_Increment (In reality, I guess an auto incrementing field cannot be null but... sticking to the script...)

Code: Select all

put "CREATE TABLE DBImages (ID char(8) NOT NULL AUTO_INCREMENT, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]... PRIMARY KEY (ID) )" into sDBTableData

doe NOT create my table

But

Code: Select all

put "CREATE TABLE DBImages (ID int, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]... PRIMARY KEY (ID) )" into sDBTableData

does and the Admin indicates I have a Primary Key property configuration on the first field ('ID') - even better...

So now I just need to add the Auto_Increment function / property to the said 'ID' and I'm 'cooking' again...

So,

Code: Select all

put "CREATE TABLE DBImages (ID int NOT NULL AUTO_INCREMENT, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]... PRIMARY KEY (ID) )" into sDBTableData

results in a backward step and no table in my DB... hmmm...
Changing the line to :

Code: Select all

put "CREATE TABLE DBImages (ID int AUTO_INCREMENT, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc]... PRIMARY KEY (ID) )" into sDBTableData

gets me someways in the right direction (in that the table is created) BUT looking like (monitored within SQLiteAdmin) :
ID int (Auto_Increment,Namechar50)
DoB char(10
...
as my first table column/field name... arghhh !!
various connotations/plays on the wording of the auto_increment property above just provides one of the above two responses - both infuriating...

Anyone any further ideas please ? Surely it just CAN'T be THIS hard !.... Can it ?...

As an aside, but I'm guessing this will probably affect everyone messing about with SQL and LC, I use the

Code: Select all

revCloseDatabase nDBConnectionID

to close the DB at the end of each of my wee tests and I exit SQLiteAdmin BUT I still can't delete the DB in Explorer / Finder until I close the complete LiveCode SDK/IDE or (at least) remove my pp in development from the IDE/SDK (REALLY frustrating and even more time consuming!) Is there a way to disconnect/release/disassociate the DB file from the 'app' in the LiveCode SDK so that I can simply delete it before starting the next test (This is concerning me not just for now, but for the futurre and my ongoing DB 'work of art' (!) with LiveCode.

I know this is a long one, so please do give yourself a pat on the back and a KitKat if you got this far without falling asleep
I'm sure the point I make is really simple to those 'in the know' but otherwise (me !) it'll drive you insane...

Thanks a million in advance.

[Klaus]

Hi Traxgeek,

I think a "PRIMARY KEY" needs to be defined as an INTEGER!

Searched my archives and found this one in one of my custom properties,
it does work with SQLite, as I used this successfully in one of my earlier projects!
...
CREATE TABLE "XXX" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "name" TEXT, ...)
...

Best

Klaus

[Traxgeek]

What a HERO !

You're an absolute STAR Sir !

Can't thank you enough.

For others and in an attempt to cut short others' future struggles along these lines (assuming they too are still awake by this time !) :
UPPERCASE / lowrcase seems NOT to matter.

Yep, gotta be an INTEGER.
Whilst int works by itself and the PRIMARY KEY (ID) works too (at the end of the declaration), the PRIMARY KEY... part HAS to be at the end
However, INTEGER PRIMARY KEY does work ! (Note the use of the FULL INTEGER word if it's to be followeed by PRIMARY KEY)

AUTOINCREMENT replaces AUTO_INCREMENT

and the order is VERY important too : So, as you correctly state Klaus, this works :

Code: Select all

"CREATE TABLE DBImages (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, Name char(50), DoB char(10), DateJoined char(10) [,etc ,etc ,etc] )" into sDBTableData

So, the long and the short (after having said a million thank yous to Klaus !) is that simply adding a 'property' to a CREATE TABLE script may mean one also has to update/modify some of the existing mnemonic codes/properties to suit the new addition.

Furthermore, using SQLiteAdmin as a visual confirmation tool (even if it's only the free/30 day trial version) does enable one to 'see' the DB, its table(s) and the field properties as they exist (on your harddisk) as your code progresses...

Just my thoughts anyway !

[Adrian]

Another site that is useful in explaining some of the workings of SQLite is http://sqlite.awardspace.info/syntax/localindex.htm. There is also http://www.sqlite.org/lang.html as more reference. (Note the home page seems not to be workng for me, but the search field will lead you to correct pages).

A couple of other points that may be useful reference for you and others later:

1. You don't have to define the type or size of fields in SQLite. More importantly, if you do define this, it doesn't limt what can go in a field (such as limiting size), apart from defining the overall type (called 'affinity'): one of text, numeric, integer, real or none. It is, though, usually good practice to define types and sizes, in case your code is later used with a different database.

2. You only need to define 'integer primary key'. The other parts, such as 'not null' and 'unique' are implicit.

3. Even if you don't define one, your table will have a primary key - i.e. a unique numeric id for each record. This is the rowid. Defining a specific field with integer primary key means you can define the name of this unique numeric identifier for your records. That is, you are defining an alias for the rowid.

4. You can have one or more fields forming a primary key, which is a different thing to the integer primary key.

I bope that's useful background.

Cheers,

Adrian