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
