selecting rows of the view via its position

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

selecting rows of the view via its position

hanno
Hello alltogether!

How can I select the predecessor or successor of a current row for comparing values? - without explicit ids on the view! (I think ids don't work for views?) Or if ids are possible, how are they implemented and can I create ID-Counters groupwise for blocks of a table??


thanks a lot and with best regards Hanno
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

Jay Sprenkle
> How can I select the predecessor or successor of a current row for comparing
> values? - without explicit ids on the view! (I think ids don't work for
> views?) Or if ids are possible, how are they implemented and can I create
> ID-Counters groupwise for blocks of a table??

What does your schema look like?



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

hanno
Hello Jay!

My example schema describes positions on a chessboard. I am aiming to make the neighbourhood relation explicit. From the line/row sorted view I want to associate to each entry the predecessors row value as northern bound of horizon and the successors as the southern bound. I'd like to do so for all axis of the board. If the problem is solved for the vertical axis, it can be solved for each other axis with appropriately sorted versions of extended_position. - So how can I get the row value of the predecessor/successor, if there is one in the same line, or just the information that there is no p/s with the same line value.
Given that case, I know that the same game starts again.
- So you see, I need a kind of groupwise iteration on that view...

Okay, now here's a snippet of the code. It's like that:

create table position (
        piece_colour VARCHAR(1),
        piece_type VARCHAR(1),
        line INT(1),
  row INT(1),
        PRIMARY KEY(line,row)    
);


create view extended_position as
select piece_colour, piece_type, line, row, row-line as diagonal, line+row-9 as counter_diagonal, row-1 as offset
        from position;

create view extended_position_by_line_row as
        select * from extended_position
        order by line asc, row desc;


Thanks a lot!
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

Jay Sprenkle
On 7/24/06, hanno <[hidden email]> wrote:

>
> Hello Jay!
>
> My example schema describes positions on a chessboard. I am aiming to make
> the neighbourhood relation explicit. From the line/row sorted view I want to
> associate to each entry the predecessors row value as northern bound of
> horizon and the successors as the southern bound. I'd like to do so for all
> axis of the board. If the problem is solved for the vertical axis, it can be
> solved for each other axis with appropriately sorted versions of
> extended_position. - So how can I get the row value of the
> predecessor/successor, if there is one in the same line, or just the
> information that there is no p/s with the same line value.
> Given that case, I know that the same game starts again.

A unique id column on the table will uniquely identify each row and
it works in views as well. There's also the ROWID column, which is
implicit in select statements:

sqlite> .schema one
CREATE TABLE one
  (
    Id                INTEGER PRIMARY KEY,
    test1             TEXT NOT NULL
  );
sqlite>
sqlite> select rowid, * from one where id > 10 limit 10;
11|11|test
12|12|test
13|13|test
14|14|test
15|15|test
16|16|test
17|17|test
18|18|test
19|19|test
20|20|test
sqlite>


I'm not sure that will help you though.
When you execute this does it return the predecessor row and you just need to
sort it?

Or do you have to execute another query to find the predecssor?
If so, just return the unique id and then it's identified by (id-1)
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

hanno
Hello Jay!

Thank you very much for your help! Unluckily I can't get rowids on views. sqlite does not declare an error, when I select rowid, * on a view. - It just prints this column empty!

The problem is, that I have to sort first and then the numbering. So I can't use the table rowids of the base table. Please let me know, if there's any kind of workaround for this...

best regards Hanno
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

Jay Sprenkle
> Thank you very much for your help! Unluckily I can't get rowids on views.
> sqlite does not declare an error, when I select rowid, * on a view. - It
> just prints this column empty!
>
> The problem is, that I have to sort first and then the numbering. So I can't
> use the table rowids of the base table. Please let me know, if there's any
> kind of workaround for this...

A couple things to try:

* Assign row id's in your code when you retrieve the results, not in
the select statement
* Select the sorted results into a temporary table then use a select,
not a view, to retrieve them. The rowid will be applied after the sort

Sorry, I didn't understand your problem well enough to give you a
better solution.
I thought all you needed was just to use the row and line columns to determine
the predecessor.
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

hanno
Hello Jay!

Assigning the ids outside sql wouldn't be neccessary because I could easily see the predecessor and successors. You are completely right, when you suggest a sql-external solution.
For better understanding I should explain my situation a little.
My real problem is, that I do all that stuff for my diploma's thesis in computerscience. I'm trying to show the abilities of relational datamodels in a deductive context. I'm trying to implement a rule base in sql that shall provide correct and complete analysis of chess situations as an example for deductive database driven monitoring.
Therefor I'm not allowed to use external mechanisms. It has to be implemented completely in sql using views as rules. So every top level analysis is implemented as a cascade of views, which are more abstract and shared by all analysis near the basis and more concrete and specialized to the top level. The top level of views is the users perspective on the system. Additionally I have to implement a frontend gui in Java/Swing with possibilities to load, save and manipulate chess situations in the usual formats....

So, I'm a bit off to real world solutions. I've to take the long and winding road ....;-)
(I've already implemented one complete version in a generate and test style, but it does not perform very good if you increase the board size and the number of pieces to let's say 800x800 and 320. So I have to find a new approach, now using the horizon of each piece to reduce the calculations neccessary to get all move possibilities,threat and cover situations.)

But thank you so much for your good support and help! And if something crosses your mind to solve the id problem with views in sql please tell me how!
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

Jay Sprenkle
On 7/24/06, hanno <[hidden email]> wrote:

>
> Hello Jay!
>
> Assigning the ids outside sql wouldn't be neccessary because I could easily
> see the predecessor and successors. You are completely right, when you
> suggest a sql-external solution.
> For better understanding I should explain my situation a little.
> My real problem is, that I do all that stuff for my diploma's thesis in
> computerscience. I'm trying to show the abilities of relational datamodels
> in a deductive context. I'm trying to implement a rule base in sql that
> shall provide correct and complete analysis of chess situations as an
> example for deductive database driven monitoring.
> Therefor I'm not allowed to use external mechanisms. It has to be

Ouch, that's an Interesting constraint!  Should be a real challenge.
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

Andrew Piskorski
In reply to this post by hanno
On Mon, Jul 24, 2006 at 09:13:28AM -0700, hanno wrote:
>
> Hello Jay!
>
> Assigning the ids outside sql wouldn't be neccessary because I could
> easily see the predecessor and successors. You are completely right,
> when you suggest a sql-external solution.

Do you mean that, you would like to do a query with an order by, and
then in each row for that same query, easily refer to columns from
OTHER (either earlier or later) rows in the ordered result set?

If so, what you want are the very useful SQL:2003 "OLAP" functions for
dealing with ordered data.  These include "lead" and "lag", among
others.  But, SQLite does not support that.  Oracle and DB2 do.
FYI, here are some links to further discussion of the SQL:2003 OLAP
functionality:

  "SQL:2003 OLAP/windowing functions?", 2006-01-25
  http://sourceforge.net/mailarchive/forum.php?thread_id=9560540&forum_id=39803

  "SQL Window/OLAP functions", Wed, 12 Oct 2005
  http://www.mail-archive.com/sqlite-users@.../msg10816.html

  "Re: [sqlite] Slow SQL statements", Mon, 23 May 2005
  http://www.mail-archive.com/sqlite-users@.../msg08223.html

  "Re: [sqlite] windowing functions != recursive functions",  Thu, 13 Oct 2005
  http://www.mail-archive.com/sqlite-users@.../msg10855.html
  http://www.mail-archive.com/sqlite-users@.../msg10854.html

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

deductive relational databases Re: [sqlite] selecting rows of the view via its position

Andrew Piskorski
In reply to this post by hanno
On Mon, Jul 24, 2006 at 09:13:28AM -0700, hanno wrote:
> Subject: Re: [sqlite] selecting rows of the view via its position

> My real problem is, that I do all that stuff for my diploma's thesis in
> computerscience. I'm trying to show the abilities of relational datamodels
> in a deductive context. I'm trying to implement a rule base in sql that
> shall provide correct and complete analysis of chess situations as an
> example for deductive database driven monitoring.

Well, I'm no expert on this, but keep in mind that "Standard SQL" !=
"relational model".  They are related, but not necessarily the same.
(In many of his writings, C.J. Date complains in detail that SQL is
not properly relational.)

What you're trying to do sounds related to the (older) research work
on "deductive databases", e.g., Aditi.  All the various forward and
(particularly) backward chaining systems are probably at least
peripherally relevent.  E.g., the Prolog, Mercury, and Oz languages;
the Oz/Mozart and CLIPS toolkits; and various others too.

  http://www.cs.mu.oz.au/research/aditi/
  http://www.cs.mu.oz.au/research/mercury/
  http://www.mozart-oz.org/
  http://www.ghg.net/clips/CLIPS.html
  http://openacs.org/forums/message-view?message_id=44805

Also, (as far as my limited understanding of they underlying theory
goes) the relational model is fundamentally un-ordered.  I'm told
there is some academic work out there on other, more powerful models
which natively understand order.  I think it would make more sense to
call that a "vector relation model", but apparently what it's actually
called is a "set model"!  I have not read it, but this book seems to
cover that subject:

  "The Set Model for Database and Information Systems"
  by Mikhail M. Gilula
  http://www.amazon.com/gp/product/0201593793/102-7773671-0297757

Some of the gurus of the vector-oriented languages (APL, K, J, A+,
etc.), particularly the K folks, have implemented relational-like
database systems.  I strongly suspect they grok this un-ordered
vs. ordered distinction, and are taking advantage of their vector
languages' native understanding of and optimization for ordered data.
I don't know whether or not that's related to Gilula's "set model"
above, though.

Ordered data is perhaps also related to column-store (rather than the
more usual row-store, like SQLite, Oracle, etc.) databases.  MonetDB
is one such (and OpenSource) column-store database.

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

hanno
In reply to this post by Jay Sprenkle
Thank you Jay, thank you Andrew!
@Andrew: I will check out the links you posted me about the new sql2003 features. - I'm not bound to sqlite, but till now I preferred it. Perhaps it's now time to check out something new.
Reply | Threaded
Open this post in threaded view
|

Re: selecting rows of the view via its position

hanno
In reply to this post by Andrew Piskorski
Hello Andrew!

I got a little into OLAP functions this morning and I'm really surprised by it! A great tool to define complex business logic in a db! (And perhaps more than just that...)
It fits perfectly to my problem. Now I have to learn how to define an own appropriate olap function for my db.

I'll go on reading about olap now!