SQLite question about INSERT

[DeanH]

This isn't a PureBasic question as such. I'm usin SQLite in PureBasic with a fairly large database having 20 tables. One of the tables is a list of searchable key words. There are only two text fields: WORD and ITEM. Both can be duplicated but there should not be a duplicate record having both the same WORD and ITEM values. My question is: Is is possible to add a row and check for this condition in the one INSERT statement? Maybe by adding a WHERE? If so, what would be the syntax? Or is there an alternate easy way to deal with it?

At the moment I am first doing a SELECT to see if a record exists with both the word and item and if the result from NextDataBaseRow =0 then adding a new record via INSERT. It just seems a bit awkward.
;See if the condition exists
A$="SELECT * FROM Keywords WHERE WORD = '" + Word$ + "' AND ITEM = '" + Item$ + "' "
DatabaseQuery(0,A$)
R=NextDatabaseRow(0)
FinishDatabaseQuery(0)
;Condition does not exist so insert new record
If R=0
DatabaseUpdate(0,"INSERT INTO Keywords VALUES '" + Word$ + "', '" + Item$ + "'")
EndIf

Another minor question: with a table in which the first field is text and may or may be duplicated, does a CREATE INDEX need to be indexed (for searching speed) or is it automatically indexed? The SQLite documentation seems a bit vague about this. It appears that PRIMARY KEY is automatically indexed but that does not allow duplicates.

[citystate]

you could create a primary key to your table based on WORD+ITEM - an attempt to add a duplicate duple should return an error that you could catch

[DeanH]

That might certainly work. However, my intuition says there should be a way to do this, sort of a "Insert only If this conditon is true".

[skywalk]

If you want to pass the problem to SQLite, try...

SQL Data Constraints

Ex. CREATE TABLE MyTable(id INTEGER PRIMARY KEY, myword TEXT UNIQUE, UNIQUE(id, myword))

Or put your data into PB Maps.
Each addition to a Map will force a unique "key".
Then Insert the final MapKey contents into the SQLite Table.

[citystate]

I think your intuition is right, give this a go:

Code: Select all

A$="INSERT INTO Keywords SELECT '"+WORD$+"','"+ITEM$
A$+"' FROM DUAL WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = '"+WORD$
A$+"' AND ITEM = '"+ITEM$+"')"
DatabaseUpdate(0,A$)

edit: nm - this should only work with mysql *sad panda* the search continues...

edit2:
try this, it should work for sqlite:

Code: Select all

A$="INSERT INTO Keywords SELECT '"+WORD$+"','"+ITEM$
A$+"' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = '"+WORD$
A$+"' AND ITEM = '"+ITEM$+"')"
DatabaseUpdate(0,A$)

[DeanH]

Sorry, tried it but that approach didn't work. It involves unique instances of non-repeatable data and that isn't the situation I have. This table is basically a search index of key words. It contains a list of words (possibly hundreds of thousands) as one field and the keys (text) of items linked to records in another table. An item record has text fields and the index table contains words from the item text fields so they can be searched. Example: search for CATS. Searching the index table returns all item keys that have that word in any of several possible item fields. SELECT * Keywords WORD='CATS'. That's fine, works well. What I'm looking for is whether it is possible, when updating, to easily add a record to Keywords where there is no record containing both the word itself (CATS) and the item reference key. I was hoping there might be a simple solution either via CREATE or INSERT. I've been doing a lot of research via Google and still have not come up with that. It may not even be possible.

The option of using 'dual' involes a separate table that has to be created. Trying to avoid that.

[DeanH]

Citystate: I think that may have worked! Here's my test program.

UseSQLiteDatabase()
table1$="CREATE TABLE Keywords (Word TEXT, Item TEXT)"

OpenDatabase(0, ":memory:", "", "")
DatabaseUpdate(0,table1$)
DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Red','1')")
DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Blue','1')")
DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Green','1')")
DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Red','2')")
DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Blue','2')")
;Try to insert Green,1 again, which it should not
A$="INSERT INTO Keywords SELECT 'Green','1' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = 'Green' AND ITEM = '1')"
DatabaseUpdate(0,A$)
;Try to insert Green,2 which it should as that combination is not in the list
B$="INSERT INTO Keywords SELECT 'Green','2' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = 'Green' AND ITEM = '2')"
DatabaseUpdate(0,B$)

A$="SELECT * FROM Keywords"
DatabaseQuery(0,A$)
While NextDatabaseRow(0)
Debug GetDatabaseString(0, 0)+" , "+ GetDatabaseString(0, 1)
Wend
CloseDatabase(0)
End

The results should be:
Red , 1
Blue , 1
Green , 1
Red , 2
Blue , 2
Green , 2
The entry Green , 1 should not be repeated.

[skywalk]

DeanH, why do you need the extra SQL queries instead of the SQLite UNIQUE Constraint?

Code: Select all

  Debug "-- Using SQLite Constraints --"
  DDL$ = "CREATE TABLE Keywords (Word TEXT, Item TEXT, UNIQUE(Word, Item))"
  OpenDatabase(0, ":memory:", "", "")
  If DatabaseUpdate(0,DDL$)
    ri = DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Red','1')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Blue','1')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Green','1')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Red','2')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Blue','2')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Green','1')")  ;<-- Fails constraint
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Green','2')")
    ri + DatabaseUpdate(0,"INSERT INTO Keywords VALUES ('Green','1')")  ;<-- Fails constraint
  EndIf
  Debug Str(ri) + " unique Keywords inserted."
  ; The following code is unnecessary.
  ; Try to insert Green,1 again, which it should not
  ; A$="INSERT INTO Keywords SELECT 'Green','1' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = 'Green' AND ITEM = '1')"
  ; DatabaseUpdate(0,A$)
  ; Try to insert Green,2 which it should as that combination is not in the list
  ; B$="INSERT INTO Keywords SELECT 'Green','2' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = 'Green' AND ITEM = '2')"
  ; DatabaseUpdate(0,B$)
  ; Try to insert Green,1 again which should already exist in the list
  ; B$="INSERT INTO Keywords SELECT 'Green','1' WHERE NOT EXISTS (SELECT * FROM Keywords WHERE WORD = 'Green' AND ITEM = '1')"
  ; DatabaseUpdate(0,B$)
  A$="SELECT * FROM Keywords"
  Debug "-- " + A$ + " --"
  DatabaseQuery(0,A$)
  ri = 0
  While NextDatabaseRow(0)
    ri + 1
    Debug LSet(Str(ri),4) + LSet(GetDatabaseString(0, 0),10) + LSet(GetDatabaseString(0, 1),4)
  Wend
  CloseDatabase(0)
  Debug "-- Using PB Map() --"
  NewMap mapW.s(128)    ; map to track uniqueness
  ; Build unique List
  mapW("Red,1")   = "1"
  mapW("Blue,1")  = "1"
  mapW("Green,1") = "1.1"   ;<-- To be dropped by LIFO rule
  mapW("Red,2")   = "2"
  mapW("Blue,2")  = "2"
  mapW("Green,1") = "1.2"   ;<-- To be dropped by LIFO rule
  mapW("Green,2") = "2"
  mapW("Green,1") = "1.3"   ;<-- Accepted since 'Last in'.
  ri = MapSize(mapW())
  Debug Str(ri) + " unique Keywords inserted."
  ri = 0
  ForEach mapW()
    ri + 1
    Debug LSet(Str(ri),4) + LSet(MapKey(mapW()),10) + LSet(mapW(),4)
  Next

Debug Results:

Code: Select all

-- Use SQLite Constraints --
6 unique Keywords inserted.
-- SELECT * FROM Keywords --
1   Red       1   
2   Blue      1   
3   Green     1   
4   Red       2   
5   Blue      2   
6   Green     2   
-- Using PB Map() --
6 unique Keywords inserted.
1   Green,1   1   
2   Green,2   2   
3   Red,1     1   
4   Red,2     2   
5   Blue,1    1   
6   Blue,2    2   

[DeanH]

Thank you for that. I am a beginner with SQL, only been using it a month or so, I am under considerable pressure to completely change a very complex database system contained in several meg of source code in as short a time as possible. I had not explored the UNIQUE term and I still do not fully understand all the SQL syntax. The data handling system I was using previously was simple, reliable and fast but started to generate memory allocation errors when the program was run in Win7. It was completely fine in XP.

I cannot use a mapped list in this instance.

[skywalk]

Ok, I only mentioned Maps for speed. You can do a lot of the data manipulation in PB code and array sorting before committing to SQLite queries.
I cannot speak to your memory issues. Windows 7 has proven no less stable than Windows XP for me.

[DeanH]

I've only been using Win7 for a few months, too. That's when everything blew up. I work for a government agency and they're very slow to approve operating system upgrades. (I am dreading Win8.) One thing I have noticed is that a PB program will keep processing even though the Debug window hasn't stopped filling. It is like putting things onto a window is almost in another, slower memory stream. Perhaps that's what was happening...the program was literally getting ahead of the data being written and handled to the file, literally tripped over itself. All the functions were in a DLL. I found that closing and reopening the file at intervals helped but it did not compeletly eliminate the memory allocation errors (which appeared almost at random). I've also noticed that progressbars do not totally fill in W7 where they did in XP, despite identical settings and source code. As far as stability is concerned, I agree, it is about the same.

I'm pretty familiar with mapped and linked lists, I use them all the time. I have noticed, though, that it can be much slower to load a whole big list (hundreds of thousands of records) into a map than to search and update a Btree indexed file directly.

[DeanH]

Does anyone know of a good, friendly forum for SQLite users, especially beginners? I keep running into things I have difficulty solving. While I am doing all the programming in PureBasic, because my questions involve SQLite rather than PB, they might be better put in a different forum. My current question involves constructing a SELECT statement that examines two different tables, returning the contents of a field (unique, no duplicate entries) from one table and counting the number of times that entry appears duplicated in a field in another table. If anyone here thinks they can assiste, I'd be glad to PM about it. Thanks.

[citystate]

wouldn't something like this do the trick?

Code: Select all

select a.unique_field, count(b.duplicated_field)
from table_1 a, table_2 b
where a.unique_field = b.duplicated_field
group by a.unique_field