On Wed, Aug 6, 2008 at 4:10 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote: > On Tue, 05 Aug 2008 17:22:05 -0500, you wrote: > >>Jeffrey Becker wrote: >>> I have a table 'SiteMap' defined as : >>> >>> Create Table SiteMap >>> ( >>> NodeID blob not null PRIMARY KEY, >>> Title text NOT NULL UNIQUE, >>> Url text NOT NULL >>> ); >>> >>> I'd like to index on the node's parent value as defined by the >>> expression ancestor(NodeID,1). 'ancestor' being a user defined >>> function. >>> However sqlite doesnt permit indicies on expressions, only columns. >>> Why is this? More importantly what's my best bet on achieveing >>> something similar? >> >>Create Table SiteMap >>( >> NodeID blob not null PRIMARY KEY, >> ParentID blob not null, >> Title text NOT NULL UNIQUE, >> Url text NOT NULL >>); >> >>Then index on (ParentID, NodeID) or something like that. >> >>-Steve > > Additionally: NodeID and ParentID shouldn't be blobs. > Integer is the most suitable type for IDs. > > http://article.gmane.org/gmane.comp.db.sqlite.general/17286/match=managing+trees+database > and > http://www.sitepoint.com/article/hierarchical-data-database > may help on maintaining trees in a relational database. > > Or search the net on "Joe Celko" and "nested sets". > -- > ( Kees Nuyt > ) > c[_]
The blobs I'm inserting are actually a binary representation designed _specifically_ to index properly. So yes they should be blobs. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
