rowid

[cedric]

Hello,

I understand that rowid is based on the order datas have been saved on disk.

I mean that if I open a table with :
  'db-select/order/desc * my-table ID'
then
  'db-select/where * my-table [rowid = 1]'
will not return the record with the highest ID. but the first that have been saved on disk.

Of course, this would have been more like a "recordset mechanism". But it would be really smart  to have a "rowid" based on the query that have been last submitted.

So, Ashley, do you think this could happen in a next rebDB release?

Bye,

  Cedric.

[Ashley]

A "recordset mechanism" is fine in a single-user environment, but in a multi-user (or multi-process) environment *without transaction support* each statement has to be evaluated in isolation, so the current implementation won't be changing soon.

Note that if you just want to get the last row, then the following will do the trick:

Code:

db-select/where * my-table [rowid = last]

You can also get the last n rows (reverse sorted if desired) using:

Code:

db [select * from my-table where [rowid between 6 last] order by id desc]

Although being able to replace the "6" with "(last - 4)" would be better. Food for thought.

[Guest]

I didn't meant a full "recordset mechanism".

I was wondering that while a query is done, there could be, perhaps, a function that would build  "dynamically" a row identificator based on the query.

lets name that "dynamically builded" row "row_x" and use the same populate table "my-table" in your rebDB quick-guide.

we do: db-select * my-table
then doing: db-select/where id my-table [row_x = 1] would return [1]
But if we do: db-select/order/desc * my-table id
then doing db-select/where id my-table [row_x = 1] would return [10]

That is.

cedric.
 

[Ashley]

Ah, I think I understand now. While rowid acts upon rows in the table *in the order they are stored*, row_x acts upon rows *in the order they are retrieved*? So:

Code:

db [select id from my-table where [rowid = 2]]

will return "2", while:

Code:

db [select id from my-table where [row_x = 2] order by ID desc]

would return "9". Correct? I havn't come across this behaviour in the databases I've worked with, what DB are you using that does this? Is the reqirement for row_x really just a requirement for a LIMIT clause (so you can "page through" a large number of rows)? Do you have a link to any online doco that describes its use?

[Guest]

Yes, you catch what I was meaning.

Well, what make me thing of this behavior is that when first submiting a query the whole datas are load into memory until db-close is called.
Then, I would expect that datas loaded in memory would reflect the query I last called.

Imagine I need to display the last 5 rows of "my-table" in reverse order by date. I would do something like:

Code:

m: db-select/order/desc [date note] my-table date
i: 1
forskip m 2 [print [first m second m]if i = 5 [break] i: i+1]

No let say that this row_x exists. I would do:

Code:

db-select/order/desc * my-table date
; there's now datas in memory reflecting the query above
for i 1 5 1 [
 print [db-select/where [date note] my-table [row_x = i]
]

But, I admit, I'm not a database guru and there's proprably no database common fundamentals in what I'm saying.

cedric.

[Ashley]

Well, what make me thing of this behavior is that when first submiting a query the whole datas are load into memory until db-close is called.
Then, I would expect that datas loaded in memory would reflect the query I last called.

Not quite. When a table is first referenced the entire contents are memory-mapped. Subsequent statements then use a buffer to store results, but subsequent statements (perhaps from a different user / process) will overwrite that buffer with their own results. In short, if you want a statement to use the results of a previous statement then you have to save these intermediate results to be able to use them.

But, I admit, I'm not a database guru and there's proprably no database common fundamentals in what I'm saying.

Yes and no. In many databases, in addition to using 'rowid in the 'where clause 'rowid can be used as a pseudo-column, which lets you write:

Code:

cursor: db [select [rowid ID] from my-table order by ID desc]

then

Code:

repeat i 5 [
   db compose/deep [select * from my-table where [rowid = (i * 2 - 1)]]
]

I will be adding this feature to v1.2 (due early March) and it should address the kind of issues you have.