Simon,
Thank you for your response.
*>>CREATE TABLE IF NOT EXISTS durTreeLevels (
treeNumber INTEGER,
levelNumber INTEGER,
nodes TEXT,
UNIQUE (treeNumber, levelNumber))*
As per your suggestion, I am planning to create two tables.
1. TreeInfo: id (primarykey autoincrement), node, parent node
2. LevelsInfo: TreeNum(primary key), levlelnum int, node, foreignkey
(TreeNum) references TreeInfo(id)
Here, for insert/update/search, we need to execute more than two on two
tables.
If I understood wrongly, please correct me.
Thanks,
Durga.
On Mon, Jan 2, 2012 at 12:31 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 2 Jan 2012, at 5:25am, Durga D wrote:
>
> > "create table if not exists durtree (id integer primary key
> autoincrement,
> > c1 varchar[260], c2 varchar[260], c3 varchar[260], c4 varchar[260], c5
> > varchar[260], c6 varchar[260], c7 varchar[260], c8 varchar[260], c9
> > varchar[260], c10 varchar[260], c11 varchar[260], c12 varchar[260], c13
> > varchar[260], c14 varchar[260], c15 varchar[260], c16 varchar[260], c17
> > varchar[260], c18 varchar[260], c19 varchar[260], c20 varchar[260], c21
> > varchar[260], c22 varchar[260], c23 varchar[260], c24 varchar[260], c25
> > varchar[260], c26 varchar[260], c27 varchar[260], c28 varchar[260], c29
> > varchar[260], c30 varchar[260], c31 varchar[260], c32 varchar[260], c33
> > varchar[260], c34 varchar[260], c35 varchar[260], c36 varchar[260], c37
> > varchar[260], c38 varchar[260], c39 varchar[260], c40 varchar[260], c41
> > varchar[260], c42 varchar[260] default null, c43 varchar[260] default
> null,
> > c44 varchar[260] default null, c45 varchar[260] default null, c46
> > varchar[260] default null, c47 varchar[260] default null, c48
> varchar[260]
> > default null, c49 varchar[260] default null, c50 varchar[260] default
> null,
> > c51 varchar[260] default null, c52 varchar[260] defaul null, c53
> > varchar[260] default null, c54 varchar[260] defaul null, c55 varchar[260]
> > default null, c56 varchar[260] default null, c57 varchar[260] default
> null,
> > c58 varchar[260] default null, c59 varchar[260] default null, c60
> > varchar[260] default null, c61 varchar[260] default null, c62
> varchar[260]
> > default null, c63 varchar[260] default null, c64 varchar[260] default
> null,
> > c65 varchar[260] default null, c66 varchar[260] default null, c67
> > varchar[260] default null, c68 varchar[260] default null, c69
> varchar[260]
> > default null, c70 varchar[260] default null, unique (c1, c2, c3, c4, c5,
> > c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20,
> c21,
> > c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35,
> c36,
> > c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50,
> c51,
> > c52, c53, c54, c55, c56, c57, c58, c59, c60, c61, c62, c63, c64, c65,
> c66,
> > c67, c68, c69, c70));"
>
> There is no such datatype as 'varchar' in SQLite. The ones you've
> specified will be treated as TEXT and SQLite will not do anything about the
> length.
> You don't need to specify DEFAULT NULL in SQLite. That's always the
> default unless you specify otherwise.
>
> Besides which, almost any schema where you have to number your columns is
> a bad schema. You should be able to hold the entire schema in your head at
> one time.
>
> > I want to make c1 to c70 as unique with default null. But, I could not
> with
> > above query. I can make c42 to c70 as default null and c1 to c70 as
> > unique. If I add default null to c41 and c40, it gets failed.
>
> The above does not make each of the c nodes unique. It makes each
> combination of c nodes unique: every single c would have to be identical in
> two rows for SQLite to reject it as violating your UNIQUE constraint.
> There's no way to make each node unique in the above schema because a node
> could be in c65 in one row and c66 in another row.
>
> A data structure more used for storing trees would look something like
> this:
>
> CREATE TABLE IF NOT EXISTS durTreeNodes (
> treeNumber INTEGER,
> parent TEXT,
> nodes TEXT NOT NULL,
> UNIQUE (treeNumber, node))
>
> This allows for trees of any height to be held efficiently. The parent
> value of the root node could be null, or 'root' or something.
>
> > objective: I am trying to store tree in a sqlite3 db depth of 70. I need
> > high performance when accessing any level of the tree.
>
> The above schema, with its very large number of columns, is not going to
> be very fast. To retrieve c70 from a row SQLite will need to count through
> 70 columns before it can get to it.
>
> If you want to retrieve an entire level of a tree at once, why not store
> it that way ?
>
> CREATE TABLE IF NOT EXISTS durTreeLevels (
> treeNumber INTEGER,
> levelNumber INTEGER,
> nodes TEXT,
> UNIQUE (treeNumber, levelNumber))
>
> In the 'nodes' column you put a list of nodes, separated by commas or
> something. This also allows trees of any height to be held efficiently.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
