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[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
