Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Jay A. Kreibich]

On Tue, Jan 03, 2012 at 11:57:46AM +0530, Durga D scratched on the wall:
> Kreibich,
> 
> You understood the problem perfectly. Thank you.
> 
> *>>I  would just suggest using a single composite path value, >>rather
> than trying to break the path value down into >>individual components
> and store those each in their own >>column.*
> 
> I already with single column (/root/local/bin/create_db), but search
> and listing is very difficult with single query. for ex: under root, lising
> distinct folders and files.

  True.  LIKE and substr() could get you fairly far, but it might take
  two or three queries.  REGEXP would get you even further, but that
  requires extra support.

  You could simply do both.  Have a "path" column and a "parent"
  column, and use the appropriate data for the task.  You are,
  conceptually, duplicating data, and there are issues with that.
  Manipulations (insert/update/delete) become a bit more costly, and
  the database won't be able to maintain consistancy between the two
  columns using simple constraints.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Petite,

   Thank you for your material.

   I will go through it.

Thanks,
Durga.

On Tue, Jan 3, 2012 at 1:02 AM, Petite Abeille wrote:

>
> On Jan 2, 2012, at 5:25 PM, Jay A. Kreibich wrote:
>
> >  Oracle has some syntax short-cuts to deal with this, but they're
> non-standard.
>
> Recursive 'with' clause anyone?
>
> "goodbye Connect By or: the end of hierarchical querying as we know it"
>
> http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
>
>
> http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#i2077142
>
>
> Regarding hierarchical data, in addition to Joe Celkos nice book, here are
> a couple of links summarizing different structures:
>
> Models for hierarchical data
> http://www.slideshare.net/billkarwin/models-for-hierarchical-data
>
> What are the Options for Storing Hierarchical Data in a Relational
> Database?
>
> http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database
>
> Aside from the materialized path approach, I personally like so-called
> "closure tables":
>
> http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
>
> And  if you feel ambitious, matrix encoding might be for you:
>
> http://vadimtropashko.files.wordpress.com/2011/07/ch5.pdf
>
> As always, YMMV as to which one of these structures work best for a given
> scenario.
>
> ___
> 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

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

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  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 

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Kreibich,

You understood the problem perfectly. Thank you.

*>>I  would just suggest using a single composite path value, >>rather
than trying to break the path value down into >>individual components
and store those each in their own >>column.*

I already with single column (/root/local/bin/create_db), but search
and listing is very difficult with single query. for ex: under root, lising
distinct folders and files.  So, I am planning to  redesign the db for
filepaths. I will go through Joe books and let you know.

Thanks in advance.

Thanks,
Durga.

On Mon, Jan 2, 2012 at 9:55 PM, Jay A. Kreibich  wrote:

> On Mon, Jan 02, 2012 at 07:01:29AM +, Simon Slavin scratched on the
> wall:
> >
> > On 2 Jan 2012, at 5:25am, Durga D wrote:
> >
> > > "create table if not exists durtree  (id integer primary key
> autoincrement,
>
> [...]
>
> > 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.
>
>   In the sense of being a properly normalized and theoretically ideal
>  schema, I would agree, but the real world has a tendency of getting
>  in the way of theoretically ideals.
>
>  "Normalized until it hurts, denormalize until it works."
>
> > > 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.
>
>   Exactly?  If he's looking to store a tree that allows direct access
>  to specific nodes, the numbered columns are essentially storing a path
>  through the tree.  Any single node can have multiple children (or
>  itself be a node), so the individual columns don't need to be unique.
>  The path as a whole, however, must be unique to represent a single node.
>
> > 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.
>
>   I'm not following you.  Many types of trees allow leaf nodes at
>  different levels.
>
> > 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.
>
>   *Held* efficiently, perhaps-- but not accessed.  What you're
>  describing is the "Adjacency Model", which is the most basic way to
>  store tree-like structures in SQL.  It has some serious drawbacks,
>  however.  Standard SQL has no type of recursive or looping features,
>  meaning "walking" a tree to find a specific node, even when the full
>  path is known, takes multiple queries.  In specific, it takes as many
>  queries as the node is deep in the tree.  This makes it extremely
>  slow.  Oracle has some syntax short-cuts to deal with this, but they're
>  non-standard.
>
>  Your unique constraint is also radically different.  You're forcing
>  nodes to be unique across whole trees, while the original schema only
>  forces them to be unique if they have the same parent (which is
>  usually what most people want).
>
>  These compromises are are part of the reason there are so many different
>  models to store tree-based structures in an SQL database (or even in
>  many "NoSQL" systems, for that matter).  Each method has specific
>  advantages and disadvantages.  Some are compact to store, but slow to
>  access, others (like the "nested set") are quick to access, but very
>  expensive to modify.
>
>  Using a "path" based approach is definitely a valid approach.  Most of
>  the time it is done with a single column, however, using some type of
>  delimiter for the tree levels.  For example, have one column with the
>  value "/usr/local/bin" rather than {c1="usr", c2="local", c3="bin"}.
>  You can then use a UNIQUE constraint on just that one column.
>  Wild-card LIKE matches allow access to sub-trees.  Using a single
>  composite path value also eliminates a specific depth limit.
>
> > > 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.
>
>   That's hardly a big deal, especially if the common case is that most
>  of the columns are NULL.
>
> > If you want to retrieve an entire level of a tree at once, why not
> > store it that way ?
>
>   He's not

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Kees Nuyt]

On Mon, 2 Jan 2012 20:32:32 +0100, Petite Abeille
 wrote:

>Recursive 'with' clause anyone?
>
>"goodbye Connect By or: the end of hierarchical querying as we know it"
>http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it
>
>http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#i2077142
>
>
>Regarding hierarchical data, in addition to Joe Celkos nice book, here are a 
>couple of links summarizing different structures:
>
>Models for hierarchical data
>http://www.slideshare.net/billkarwin/models-for-hierarchical-data
>
>What are the Options for Storing Hierarchical Data in a Relational Database?
>http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database
>
>Aside from the materialized path approach, I personally like so-called 
>"closure tables":
>
>http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html
>
>And  if you feel ambitious, matrix encoding might be for you:
>
>http://vadimtropashko.files.wordpress.com/2011/07/ch5.pdf
>
>As always, YMMV as to which one of these structures work best for a given 
>scenario.

Great resources, thanks!


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Petite Abeille]

On Jan 2, 2012, at 5:25 PM, Jay A. Kreibich wrote:

>  Oracle has some syntax short-cuts to deal with this, but they're 
> non-standard.

Recursive 'with' clause anyone?

"goodbye Connect By or: the end of hierarchical querying as we know it"
http://technology.amis.nl/blog/6104/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchical-querying-as-we-know-it

http://docs.oracle.com/cd/E11882_01/server.112/e10592/statements_10002.htm#i2077142


Regarding hierarchical data, in addition to Joe Celkos nice book, here are a 
couple of links summarizing different structures:

Models for hierarchical data
http://www.slideshare.net/billkarwin/models-for-hierarchical-data

What are the Options for Storing Hierarchical Data in a Relational Database?
http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database

Aside from the materialized path approach, I personally like so-called "closure 
tables":

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html

And  if you feel ambitious, matrix encoding might be for you:

http://vadimtropashko.files.wordpress.com/2011/07/ch5.pdf

As always, YMMV as to which one of these structures work best for a given 
scenario.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Jay A. Kreibich]

On Mon, Jan 02, 2012 at 07:01:29AM +, Simon Slavin scratched on the wall:
> 
> On 2 Jan 2012, at 5:25am, Durga D wrote:
> 
> > "create table if not exists durtree  (id integer primary key autoincrement,

[...]

> 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.

  In the sense of being a properly normalized and theoretically ideal
  schema, I would agree, but the real world has a tendency of getting
  in the way of theoretically ideals.

  "Normalized until it hurts, denormalize until it works."

> > 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.

  Exactly?  If he's looking to store a tree that allows direct access
  to specific nodes, the numbered columns are essentially storing a path
  through the tree.  Any single node can have multiple children (or
  itself be a node), so the individual columns don't need to be unique.
  The path as a whole, however, must be unique to represent a single node.

> 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.

  I'm not following you.  Many types of trees allow leaf nodes at
  different levels.

> 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.

  *Held* efficiently, perhaps-- but not accessed.  What you're
  describing is the "Adjacency Model", which is the most basic way to
  store tree-like structures in SQL.  It has some serious drawbacks,
  however.  Standard SQL has no type of recursive or looping features,
  meaning "walking" a tree to find a specific node, even when the full
  path is known, takes multiple queries.  In specific, it takes as many
  queries as the node is deep in the tree.  This makes it extremely
  slow.  Oracle has some syntax short-cuts to deal with this, but they're
  non-standard.

  Your unique constraint is also radically different.  You're forcing
  nodes to be unique across whole trees, while the original schema only
  forces them to be unique if they have the same parent (which is
  usually what most people want).

  These compromises are are part of the reason there are so many different
  models to store tree-based structures in an SQL database (or even in
  many "NoSQL" systems, for that matter).  Each method has specific
  advantages and disadvantages.  Some are compact to store, but slow to
  access, others (like the "nested set") are quick to access, but very
  expensive to modify.

  Using a "path" based approach is definitely a valid approach.  Most of
  the time it is done with a single column, however, using some type of
  delimiter for the tree levels.  For example, have one column with the
  value "/usr/local/bin" rather than {c1="usr", c2="local", c3="bin"}.
  You can then use a UNIQUE constraint on just that one column. 
  Wild-card LIKE matches allow access to sub-trees.  Using a single
  composite path value also eliminates a specific depth limit.

> > 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.

  That's hardly a big deal, especially if the common case is that most
  of the columns are NULL.

> If you want to retrieve an entire level of a tree at once, why not
> store it that way ?

  He's not asking about pulling an entire level, he's saying he wants
  to pull any node, at any level, with a single query.  The Adjacency
  Model (storing a "parent" value) doesn't allow this... the
  application has to "walk" the tree, making one query to get through
  each level of the tree.  A "path" model allows you to jump to any
  node (at a known path) in a single query.
  
> 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.

  Seriously?  That breaks First Normal Form.  Stuffing lists into a
  

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Igor Tandetnik]

Durga D  wrote:
> I tested with below query to create a table:
> 
> "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));"

This query works for me as written. I also added about a dozen more "default 
null" clauses, and it still worked (though, as Simon noted, "default null" is 
redundant).

What specifically seems to be the problem? You say, variously, that you "could 
not" do X, or SQLite "doesn't support" Y. What makes you believe that? What is 
it you do, exactly, what results do you observe, and how do these results 
differ from your expectations?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Simon Slavin]

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

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Thanks for your responses.

Wish you a Happy new year.

I tested with below query to create a table:

"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));"


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.

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.

Thanks  in advance,
Durga.

On Fri, Dec 30, 2011 at 11:17 PM, Simon Slavin  wrote:

>
> On 30 Dec 2011, at 4:40pm, Jay A. Kreibich wrote:
>
> > On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
> >> Hi all,
> >>
> >>I have sqlite table with 71 columns. not null for 70 and one is
> >> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for
> 70
> >> columns and unique(70 columns). It's worked fine upto 30 columns not
> null
> >> and unique(70 columns).
> >>
> >>  I need 70 columns with unique and not null. How to enable it?
> >
> >  Do you mean 70 columns that each have a single-column unique
> >  constraint, or a single unique constraint that crosses 70 columns?
>
> I think he means he has 70 columns, each of which have to be unique.
>  Which, of course, means he'll have 70 implicit indexes on that table.
>  It'll make inserting things slow, but I don't why any reason why SQLite
> shouldn't do it.  On the other hand, I wouldn't want to make SQLite update
> 70 columns each time I INSERTed a row, and I'd probably rely on my
> application to do it instead of the DBMS.
>
> Durga, here are the limits to the number of things you can have in SQLite:
>
> 
>
> No mention of a maximum number of indexes.  But frankly I'm suspicious of
> any table which has 70 columns.  You should be able to hold all the columns
> of a table in your head at once, and I lose my place long before 70 columns.
>
> 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

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Simon Slavin]

On 30 Dec 2011, at 4:40pm, Jay A. Kreibich wrote:

> On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
>> Hi all,
>> 
>>I have sqlite table with 71 columns. not null for 70 and one is
>> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
>> columns and unique(70 columns). It's worked fine upto 30 columns not null
>> and unique(70 columns).
>> 
>>  I need 70 columns with unique and not null. How to enable it?
> 
>  Do you mean 70 columns that each have a single-column unique
>  constraint, or a single unique constraint that crosses 70 columns?

I think he means he has 70 columns, each of which have to be unique.  Which, of 
course, means he'll have 70 implicit indexes on that table.  It'll make 
inserting things slow, but I don't why any reason why SQLite shouldn't do it.  
On the other hand, I wouldn't want to make SQLite update 70 columns each time I 
INSERTed a row, and I'd probably rely on my application to do it instead of the 
DBMS.

Durga, here are the limits to the number of things you can have in SQLite:



No mention of a maximum number of indexes.  But frankly I'm suspicious of any 
table which has 70 columns.  You should be able to hold all the columns of a 
table in your head at once, and I lose my place long before 70 columns.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Jay A. Kreibich]

On Fri, Dec 30, 2011 at 04:10:55PM +0530, Durga D scratched on the wall:
> Hi all,
> 
> I have sqlite table with 71 columns. not null for 70 and one is
> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
> columns and unique(70 columns). It's worked fine upto 30 columns not null
> and unique(70 columns).
> 
>   I need 70 columns with unique and not null. How to enable it?

  Do you mean 70 columns that each have a single-column unique
  constraint, or a single unique constraint that crosses 70 columns?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Richard Hipp]

On Fri, Dec 30, 2011 at 5:40 AM, Durga D  wrote:

> Hi all,
>
>I have sqlite table with 71 columns. not null for 70 and one is
> surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
> columns and unique(70 columns). It's worked fine upto 30 columns not null
> and unique(70 columns).
>

What makes you think SQLite 3.7.9 doesn't support more than 70 NOT NULL or
UNIQUE columns?  I just tried it with 100 NOT NULL and UNIQUE columns and
it works fine for me.



>
>  I need 70 columns with unique and not null. How to enable it?
>
> Thanks in advance,
> Durga.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table

[Durga D]

Hi all,

I have sqlite table with 71 columns. not null for 70 and one is
surrogate key(primary key). Sqlite3 3.7.9 doesn't support not null for 70
columns and unique(70 columns). It's worked fine upto 30 columns not null
and unique(70 columns).

  I need 70 columns with unique and not null. How to enable it?

Thanks in advance,
Durga.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users