[Gambas-user] Issue 612 in gambas: BUG Cannot browse more of 128 records on tables "No current connection"

[Lewis Balentine]

My real point was (and I probably stated it very badly) is there is 
nothing in SQL that requires a table to have a primary key defined. Thus 
one should not assume such key exist unless it is a known DB structure. 
For a known structure there is probably an alternative aproach (orber by 
something, something ... etc). For an unknown DB structure the problem 
is more challenging. There may not be a "universal" way to produce a 
"rowid" on the fly but rather one for each particular SQL incarnation 
... and it should go without saying that such generated rowid's are ONLY 
valid for the subject query.

MySQL reference (using variables):
http://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/

PostgreSQL reference (using supplemental view based on row_number()):
http://linfiniti.com/2011/11/adding-a-counter-to-postgresql-query-results/
http://www.postgresql.org/docs/8.4/interactive/functions-window.html

My apologies for being so aggressive/hostile/reactive.
It is just I have heard too many times that SQL tables must have primary 
keys ... and it just is not true.

Lewis


On 03/28/2015 01:28 PM, Benoît Minisini wrote:
> Le 28/03/2015 19:09, Benoît Minisini a écrit :
>> Le 28/03/2015 18:58, Lewis Balentine a écrit :
>>> I have over three decades of dealing with SQL and I can still not
>>> understand how this myth got propagated.
>>>
>>> There is NO valid SQL reason why a table should be required to have a
>>> unique primary key or any predefined key for that matter. One can always
>>> use the row number (record number) if such a key is required for one's
>>> application.
>>>
>> The row number does not exist in all database systems (more precisely,
>> it is not accessible to the outside), so I can't rely on that feature to
>> identify a row uniquely.
>>
>> Maybe the row id is not standard SQL, if "standard SQL" has any meaning.
>>
>> Consequently, I need a unique index, usually the primary key.
>>
>> If you can tell me how to get the row id of a row in MySQL, PostgreSQL
>> and SQLite (mabe it has changed since the last time I looked at it), I
>> will reconsider my position. :-)
>>
>> Regards,
>>
> More explanations...
>
> I said "row number", I wanted to say "row id".
>
> MySQL has no row id concept apparently. PostgreSQL has something like
> that, but apparently not useful. SQLite has.
>
> The answer is always the same: you need a "row id"? Add a unique primary
> key based on a serial/auto-increment integer field.
>
> Now as for as "row number".
>
> I could use the "LIMIT / OFFSET" syntax to return the different part of
> a request, instead of using "LIMIT" + a criteria on the primary key.
>
> Alas PostgreSQL (for example), tells us that two identical "LIMIT /
> OFFSET" on the same request does not necessarily return the same
> records, unless you specify an predictible "ORDER BY" clause.
>
> Moreover "OFFSET" is not optimized on PostgreSQL.
>
> So I don't see a better solution than the current one.
>