Sign in to follow this  
Followers 0
iCode

SQLite - general db manipulation questions

14 posts in this topic

i want a db with 10 rows and 3 colums; id, short, long

when a new row is inserted, i automatically want to delete the oldest row, then re-order the id column - so it's sort of like a cascading list, if you will

i just started playing with SQLite and i'm having trouble figuring out how to 1) delete the oldest row using a TRIGGER and 2) re-order the numbers in the id column

ex...

id    short              long
-----------------------------------------
1    some...            some text here
2
3
...

this is where i'm at so far...

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hSqlQuery, $aRow, $sMsg
Local $sStrLong = "hello world, it sure is a nice damn day outside, eh?"
Local $sStrShort = StringLeft($sStrLong, 20)
Local $sqlDbFile = @ScriptDir & "\db.sqlite"

FileDelete($sqlDbFile)

_SQLite_Startup()

_SQLite_Open(@ScriptDir & "\db.sqlite") ; Open/create a permanent disk database

_SQLite_Exec(-1, "CREATE TABLE dynamic (id, short, long);") 

For $i = 1 To 10
    _SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('" & $i & "','','');") ; INSERT Data
Next

_SQLite_Exec(-1, "CREATE TRIGGER t_del AFTER INSERT ON dynamic BEGIN DELETE FROM dynamic WHERE id = '1'; END")

_SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('10','" & $sStrShort & "','" & $sStrLong & "');") ; INSERT Data

_SQLite_Close() ; DB is a regular file that could be reopened later

_SQLite_Shutdown()

so the above leaves me with rows in the id colum from 2 to 10, then another 10 which is the last INSERT

what i need to do is change 2 to 1, 3 to 2, 4 to 3, etc.

i think i need something like "UPDATE SET column-name = 'id'" in the TRIGGER, or maybe a For-Next loop, but that's where i'm stuck


FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

#include <SQLite.au3>
#include <SQLite.dll.au3>
#Include <Array.au3>

Local $hSqlQuery, $aRow, $sMsg
Local $sStrLong = "hello world, it sure is a nice damn day outside, eh?"
Local $sStrShort = StringLeft($sStrLong, 20)
Local $sqlDbFile = @ScriptDir & "\db.sqlite"

FileDelete($sqlDbFile)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\db.sqlite") ; Open/create a permanent disk database
_SQLite_Exec(-1, "CREATE TABLE dynamic (id, short, long);") 

For $i = 1 To 10
    _SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('" & $i & "','" & $i & "','');") ; INSERT Data
Next

_SQLite_Exec(-1, "DELETE FROM dynamic WHERE id='1';")
_SQLite_Exec(-1, "UPDATE dynamic SET id=id-1;")
_SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('10','" & $sStrShort & "','" & $sStrLong & "');") ; INSERT Data

Local $array
_SQLite_GetTable2d (-1, "SELECT * FROM dynamic;", $array, 0, 0)
_ArrayDisplay($array)

_SQLite_Close() ; DB is a regular file that could be reopened later
_SQLite_Shutdown()

I guess there is certainly a better way, but this works...

Edit

Thanks Zedna :)

Edited by mikell

Share this post


Link to post
Share on other sites

Here's how I'd do it, without refering to relative values for ID. If ID can't be autoincrement, lest add another column to store the value you want and keep ID integer primary key autoincrement.

CREATE TABLE [dyn] (
  [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [Short] CHAR, 
  [Long] CHAR);

CREATE TRIGGER [trInsId]
AFTER INSERT
ON [dyn]
BEGIN
     delete from dyn where id not in (select id from dyn order by id desc limit 10);
END;

I didn't do the renumbering as it's a silly idea. I can elaborate further if needed but only later today or late tomorrow. The idea is that you don't need that value and it's against the relational concept (SQL deals with unordered sets) to ordinate rows this way.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Here's how I'd do it, without refering to relative values for ID. If ID can't be autoincrement, lest add another column to store the value you want and keep ID integer primary key autoincrement.

...

I didn't do the renumbering as it's a silly idea. I can elaborate further if needed but only later today or late tomorrow. The idea is that you don't need that value and it's against the relational concept (SQL deals with unordered sets) to ordinate rows this way.

 

shows you how much i know about SQL :)

yes, autoincrementing the id colums is what i want ... i think -- if i understand you right, you're saying i don't need the id column to query data? far as i know there is no numbering scheme in SQL, so how would i know what row to pull from? in my case i need a row number (i don't care what the data is)

 

thanks to you others also :)


FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

In you need the first (oldest) row, then use:

select short, long from dyn limit 1;

In you need the third row, then use:

select short, long from dyn limit 1 offset 2;

In you need the third & fourth rows, then use:

select short, long from dyn limit 2 offset 2;

In you need the last (newest) row, then use:

select short, long from dyn order by id desc limit 1;

or, equivalently:

select short, long from dyn limit 1 offset 9;

EDIT: fixed typos!

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

"In" confused me - i think you meant "If" -- i understand now -- i think i'll stick with the id col since it seems easier to me, i think

so this is what i have, but AUTOINCRIMENT isn't incrimenting as i expect - instead it's working exactly as it should, which isn't what i need :)

i want the nums in the id col to always range from 1 to 10, and this is giving me 2 to 11 (see 3rd line up from the end)...

#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $hSqlQuery, $aRow, $sMsg
Local $sqlDbFile = @ScriptDir & "\db.sqlite"

FileDelete($sqlDbFile)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\db.sqlite")

_SQLite_Exec(-1, "CREATE TABLE [dynamic] (" & _
    "[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
    "[short] CHAR," & _
    "[long] CHAR); " & _
    "CREATE TRIGGER [t_inc_id] " & _
    "AFTER INSERT " & _
    "ON [dynamic] " & _
    "BEGIN " & _
    "delete from dynamic where id not in (select id from dynamic order by id desc limit 10); " & _
    "END;")

_SQLite_Exec(-1, "INSERT INTO dynamic (short,long) VALUES ('sart','first');")

For $i = 1 To 9
    _SQLite_Exec(-1, "INSERT INTO dynamic (short,long) VALUES ('hi','hiya');")
Next

; so far we have 1 to 10 in the id column, but then when we insert again the range becomes 2 to 11 - i need 1 to 10
_SQLite_Exec(-1, "INSERT INTO dynamic (short,long) VALUES ('10','should be row 10 but it is 11');")

_SQLite_Close()
_SQLite_Shutdown()

FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

If you insist on that, then make the trigger look like:

CREATE TRIGGER [trInsId]
AFTER INSERT
ON dyn
BEGIN
     delete from dyn where id not in (select id from dyn order by id desc limit 10);     
     update dyn set id = id - 1;     
     update sqlite_sequence set seq = seq - 1 where name = "dyn";
END

Of course the name "dyn" may need to be changed to the actual name of your table. I'm just posting sketches here.

Note that for this to work you need to re-create your table or change sqlite_sequence manually (using SQLite Expert for instance to avoid having to write code for that).

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

i'm not insisting on anything, i think i am simply under the wrong impression that i need that id col and i guess i don't

i will give it a shot with the method of querying you suggested and see how that goes

thanks allot for your help


FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

i'm still stuck - i'm looking at the docs and what you have provided and i can't find any examples that show how to delete a row without referring to a row id column or data in a cell

since i only ever want a max of 10 rows, i need to delete the oldest row (first row) when a new one is added, so, using your query example (select short, long from dyn limit 1;)  i tried this a few different ways in the trigger and i haven't gotten it to work...

"BEGIN " & _
"delete from dynamic short, long limit 1; " & _
"END;")
--> Query:    CREATE TABLE [dynamic] ([short] CHAR,[long] CHAR); CREATE TRIGGER [t_inc_id] AFTER INSERT ON [dynamic] BEGIN delete from dynamic short, long limit 1; END;
--> Error:    near "short": syntax error

so i tried (short,long) and 'short,long' and a couple others and failed ;)


FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

Sorry, I've been distracted elsewhere.

Does this simpler schema work like you want, where rank is the value in 1..10?

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include  <Array.au3>

Local $hSqlQuery, $aRow, $sMsg
Local $sqlDbFile = @ScriptDir & "\db.sqlite"

FileDelete($sqlDbFile)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\db.sqlite")

_SQLite_Exec(-1, _
    "Drop table if exists dynamic;" & _
    "CREATE TABLE dynamic (" & _
    "Rank integer not null default 0," & _
    "Short CHAR," & _
    "Long CHAR); " & _
    "CREATE TRIGGER t_ins " & _
    "After INSERT ON dynamic " & _
    "BEGIN " & _
    "delete from dynamic where rowid not in (select rowid from dynamic order by rowid desc limit 10);" & _
    "update dynamic set rank = (select count(*) from dynamic d where dynamic.rowid >= d.rowid);" & _
    "END;")

_SQLite_Exec(-1, "INSERT INTO dynamic (short, long) VALUES ('sart', 'first');")

For $i = 1 To 9
    _SQLite_Exec(-1, "INSERT INTO dynamic (short, long) VALUES ('hi" & $i & "', 'hiya');")
Next

Local $rows, $nrows, $ncols
_SQLite_GetTable2d(-1, "select * from dynamic;", $rows, $nrows, $ncols)
_ArrayDisplay($rows)

_SQLite_Exec(-1, "INSERT INTO dynamic (short, long) VALUES ('hi10', 'Good');")
_SQLite_GetTable2d(-1, "select * from dynamic;", $rows, $nrows, $ncols)
_ArrayDisplay($rows)

_SQLite_Exec(-1, "INSERT INTO dynamic (short, long) VALUES ('hi11', 'Fine');")
_SQLite_GetTable2d(-1, "select * from dynamic;", $rows, $nrows, $ncols)
_ArrayDisplay($rows)

_SQLite_Exec(-1, "INSERT INTO dynamic (short, long) VALUES ('hi12', 'OK');")
_SQLite_GetTable2d(-1, "select * from dynamic;", $rows, $nrows, $ncols)
_ArrayDisplay($rows)

_SQLite_Close()
_SQLite_Shutdown()

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

 

Sorry, I've been distracted elsewhere.

 

no appology necessary - i was lucky to have an SQL wizard reply to my post :)

Yes, that works exactly like i want it to, but if i can do the same without the rank column, then i should probably do that. If i understood you right, you seem to be applying that the numbering column is not needed. I just couldn'tfigure out the syntax to delete a row without it. This is my [broken] example...

"BEGIN " & _ 
"delete from dynamic short, long limit 1; " & _ 
"END;")

FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

 

but if i can do the same without the rank column, then i should probably do that.

Please remark that if you rename rank to ID you're quite close to the example you posted originally.

Anyway you can get rid of the rank (or id) column if you accept more dependance on SQLite specifics and more exposition of the 1 and 10 constants.

Here's an alternative schema:

CREATE TABLE [dynamo] (
  [Short] CHAR, 
  [Long] CHAR);

CREATE TRIGGER [tr_insert]
AFTER INSERT
ON [dynamo]
WHEN new.rowid > 10
BEGIN
     delete from dynamo where rowid = 1;
     update dynamo set rowid = rowid - 1;     
     update sqlite_sequence set seq = 10 where name = 'dynamo';
END;

The rank value is now the hidden rowid column.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

FINALLY!!!

after cobbling together parts of the code you were all kind enough to provide, and after allot of searching (stackoverflow) and looking at lots more code, i finally have what want

again, thanks to all who helped this not-so-sharp old man :)

#include <SQLite.au3>
#include <SQLite.dll.au3>
#Include <Array.au3>

Local $sqlDbFile = @ScriptDir & "\db.sqlite"
Local $aRows, $nRows, $nCols

FileDelete($sqlDbFile)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\db.sqlite")
_SQLite_Exec(-1, "CREATE TABLE dynamic (" & _
    "id, " & _
    "short, " & _
    "long);")

For $i = 1 To 10
    _SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('" & $i & "','short descrip #" & $i & "','long descrip');")
Next

_SQLite_GetTable2d(-1, "select * from dynamic;", $aRows, $nRows, $nCols)
_ArrayDisplay($aRows)

_SQLite_Exec(-1, "CREATE TRIGGER t_insert " & _
    "AFTER INSERT ON dynamic " & _
    "BEGIN " & _
    "DELETE FROM dynamic WHERE id <= (SELECT id FROM dynamic ORDER BY id DESC LIMIT 10, 1); " & _
    "UPDATE dynamic SET id=id-1; " & _
    "END;")

For $i = 1 To 3
    _SQLite_Exec(-1, "INSERT INTO dynamic VALUES ('11','new #" & $i & "','long descrip');")
    _SQLite_GetTable2d(-1, "select * from dynamic;", $aRows, $nRows, $nCols)
    _ArrayDisplay($aRows)
Next

_SQLite_Close()
_SQLite_Shutdown()

FUNCTIONS: WinDock (dock window to screen edge) | EditCtrl_ToggleLineWrap (line/word wrap for AU3 edit control) | SendEX (yet another alternative to Send( ) ) | Spell Checker (Hunspell wrapper) | SentenceCase (capitalize first letter of sentences)

CODE SNIPPITS: Dynamic tab width (set tab control width according to window width)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0