SQLite - general db manipulation questions

[iCode]

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

[Zedna]

update dynamic
set id = id - 1

[mikell]

#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

[jchd]

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.

[iCode]

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

[jchd]

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

[iCode]

"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()

[jchd]

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

[iCode]

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

[iCode]

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

[jchd]

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()

[iCode]

 

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;")

[jchd]

 

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.

[iCode]

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()