iCode Posted August 4, 2013 Share Posted August 4, 2013 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) Link to comment Share on other sites More sharing options...
Zedna Posted August 4, 2013 Share Posted August 4, 2013 update dynamic set id = id - 1 Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
mikell Posted August 4, 2013 Share Posted August 4, 2013 (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 August 4, 2013 by mikell Link to comment Share on other sites More sharing options...
jchd Posted August 4, 2013 Share Posted August 4, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
iCode Posted August 4, 2013 Author Share Posted August 4, 2013 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) Link to comment Share on other sites More sharing options...
jchd Posted August 4, 2013 Share Posted August 4, 2013 (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 August 4, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
iCode Posted August 4, 2013 Author Share Posted August 4, 2013 "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) Link to comment Share on other sites More sharing options...
jchd Posted August 4, 2013 Share Posted August 4, 2013 (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 August 4, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
iCode Posted August 4, 2013 Author Share Posted August 4, 2013 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) Link to comment Share on other sites More sharing options...
iCode Posted August 4, 2013 Author Share Posted August 4, 2013 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) Link to comment Share on other sites More sharing options...
jchd Posted August 4, 2013 Share Posted August 4, 2013 Sorry, I've been distracted elsewhere. Does this simpler schema work like you want, where rank is the value in 1..10? expandcollapse popup#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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
iCode Posted August 5, 2013 Author Share Posted August 5, 2013 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) Link to comment Share on other sites More sharing options...
jchd Posted August 5, 2013 Share Posted August 5, 2013 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Solution iCode Posted August 5, 2013 Author Solution Share Posted August 5, 2013 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 expandcollapse popup#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) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now