Re: [sqlite] Efficient usage of sqlite
On Mon, Jan 2, 2012 at 6:49 PM, Simon Slavin wrote: > > On 2 Jan 2012, at 2:46pm, Baruch Burstein wrote: > > > It is my first game, as I am just learning game programming. It is a > > sokoban clone. > > If it's your first anything, you may be worrying about the wrong thing. > Get your game working first, any old how. Worry about libraries and file > sizes later. All the tiny details like that will join up and sap your > strength if you worry about them at this stage. > > Good luck with it. > > Simon. > > Thank you for the advice. I finished the game and am now looking for ways to improve what I have. -- Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook ___ 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, 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
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
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] Generated SQL from Skip and Take (EntityFramework)
Steffen Mangold wrote: > > "The Skip(n) method is not optimally transformed into SQL. Consider the following > LINQ expression: > If I'm understanding your issue correctly, it should be fixed as of 2011-07-09: https://system.data.sqlite.org/index.html/info/76e929f694 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Column Constraints
The diagrams for CREATE TABLE indicate that multiple column constraints can be defined for a column. I understand that a column can have multiple constraints of different types (UNIQUE, NOT NULL, PRIMARY KEY, etc) but not quite sure about foreign keys. Is it valid for one column to reference multiple table/column pairs? -- Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search
Durga,
> I think, it may not support for search with wild chars. like vil*
> Can I get wild char search in leaf node or parent node with XML?
About wildchar, sedna will support it, but I don't think so about
prefixed wildchar. You can join the forum and ask it there. I'm sorry,
I'm also new in this xml dbms, and just want to share the idea behind
that.
Another alternative you can use postgresql ltree, and I think it will
support your all requirement. You can read the documentation here
http://www.postgresql.org/docs/9.1/static/ltree.html (full of features
and operators) and this is the live project example
http://www.dmoz.org/
regards,
-aris
On 1/2/12, Durga D wrote:
> Dear Aris,
>
> Wish you a happy new year.
>
> I agree.
>
> I think, it may not support for search with wild chars. like vil*
>
> Can I get wild char search in leaf node or parent node with XML?
>
> Thanks,
> Durga.
>
> On Fri, Dec 30, 2011 at 8:05 PM, Aris Setyawan wrote:
>
>> Hi Durga,
>>
>> Another alternative, you can use an xml database. It will fix your
>> problem easily using xquery, like this:
>>doc('region')//country/title/text() -> it will show all region you have
>>doc('region')//village/title/text() -> it will show all village you
>> have
>>
>> You also can use selection too (where condition).
>> http://en.wikibooks.org/wiki/XQuery/XPath_examples
>> http://sedna.org
>>
>> SQLite with fts is my favorite, but for tree like data structure I
>> will use xml database.
>>
>> -aris
>>
>> On 12/28/11, Durga D wrote:
>> > Dear Michael.Black.
>> >
>> > It's correct.
>> >
>> >I need to design database to store file paths and their info like
>> > size. I have an idea
>> > item(file or folder), level0(imm. parent), level1(grand
>> > parent)
>> > to level160(ancestor), type(file type or folder type).
>> >
>> > primary key: (item, level0 to level160)
>> >
>> > Is it correct approach? This is from server side. Need to store
>> > millions of records.
>> >
>> > Need optimum relationship between folders and files uniquely.
>> >
>> > for ex: c:/mydocs/home/a.doc
>> > c:/mydocs/office/agreement.doc
>> >
>> > insertion of filepaths,deltion of file paths are enough. should be
>> > able
>> > to search by folder wise also.
>> >
>> >any ideas?
>> >
>> > Thanks in advance.
>> >
>> > On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS) <
>> michael.bla...@ngc.com
>> >> wrote:
>> >
>> >> I don't know if FTS or a normal table will matter here but just
>> normalize
>> >> the whole thing.
>> >>
>> >>
>> >>
>> >> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>> >>
>> >> Your level can be CO, ST, CI, VI. Or 1,2,3,4. 1,2,3,4 would be a
>> >> touch
>> >> faster.
>> >>
>> >>
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'CO','country1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'ST','state1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'CI','city1');
>> >>
>> >> INSERT INTO virfts4 VALUES(1,'VI','village1');
>> >>
>> >> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>> >>
>> >>
>> >>
>> >> You can store as many levels as you want.
>> >>
>> >>
>> >>
>> >> Michael D. Black
>> >>
>> >> Senior Scientist
>> >>
>> >> Advanced Analytics Directorate
>> >>
>> >> Advanced GEOINT Solutions Operating Unit
>> >>
>> >> Northrop Grumman Information Systems
>> >>
>> >>
>> >> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> >> on behalf of Durga D [durga.d...@gmail.com]
>> >> Sent: Tuesday, December 27, 2011 4:27 AM
>> >> To: General Discussion of SQLite Database
>> >> Subject: EXT :Re: [sqlite] search
>> >>
>> >> Thank you. I agree. It's correct.
>> >>
>> >> I already have data base with /country/state/city/village format. Is it
>> >> possible to do that while virtual table creation time?
>> >>
>> >> if yes, how?
>> >>
>> >> in case, if I have 250 levels like this ex: file system. how to do
>> >> this.
>> >> any idea?
>> >>
>> >> Thanks in advance.
>> >>
>> >> On Tue, Dec 27, 2011 at 3:38 PM, Kit wrote:
>> >>
>> >> > 2011/12/27 Durga D :
>> >> > > select * from virfts4 where residence match '/*'; -- dint work
>> >> > > how to get counties names from this db by using query?
>> >> >
>> >> > Normalize database to 1NF, e.g.
>> >> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city,
>> >> > village,
>> >> > arrivtime, duration, imagelocation);
>> >> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
>> >> > 0730, 1500,'C');
>> >> >
>> >> > then use select:
>> >> > SELECT DISTINCT country FROM virfts4;
>> >> > --
>> >> > Kit
>> >> > ___
>> >> > 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/listin
Re: [sqlite] Is it bug? or need to enable any pragma for not null and unique while creating table
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
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] sqlite crash info
On 1 Jan 2012, at 3:49pm, Lalitkumar Choudhary wrote: > I want to know one thing about sqlite crashing, you had mentioned in your > document which i had found in your website(www.sqlite.org). > Sqlite database will crash if you write some garbage value into the file. Which document on that site did you find this information in ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
On 2 Jan 2012, at 2:46pm, Baruch Burstein wrote: > It is my first game, as I am just learning game programming. It is a > sokoban clone. If it's your first anything, you may be worrying about the wrong thing. Get your game working first, any old how. Worry about libraries and file sizes later. All the tiny details like that will join up and sap your strength if you worry about them at this stage. Good luck with it. 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
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] begging for SQLite support
2012/1/2 olli.zh...@gmail.com
> Would i get your help? Thanks very much!
>
> 发自我的 iPad
>
> 以下是转发的邮件：
>
> *发件人:* Richard Hipp
> *日期:* 2012年1月2日格林尼治标准时间+0800下午10时35分39秒
> *收件人:* Olli Zhong
> *主题:* *回复： begging for SQLite support*
>
> We will be glad to help you on the SQLite mailing list:
> sqlite-users@sqlite.org
>
> 2012/1/2 Olli Zhong < olli.zh...@gmail.com>
>
>> Dear D. Richard Hipp ,
>>
>> first, let me send the most sincere wishes to you, Happy New Year！
>>
>> i have used SQLite embeded db system. I have successfully transplanted
>> it into our project, which can be run perfectly on Microsoft Window
>> platform. while on the ARM9, ADS1.2 platform, unfortunately, it has a
>> thorny issue, and i have studied and searched all over the internet
>> and all of my co-workers and friends, there are no results.
>>
>> the issure as follow:
>> when i join the SQLite code into my project, and use it, such as a
>> simple db operate
>> int main()
>> {
>>char szFile[32] = "MY DB.db";
>>char *szError = 0;
>>sqlite *mpDB = sqlite_open(szFile, 0, &szError);
>>
>
You should be using SQLite3, not SQLite2. SQLite2 is really old and
obsolete.
Documentation for how to open an SQLite3 database is here:
http://www.sqlite.org/c3ref/open.html
> }
>>
>> there're no erros in the Compiling and Linking. but when i debug the
>> project, the procedure can not pass the language c library
>> initialization function "void __rt_lib_init ( void )",
>>
>>bl __rt_lib_init//can not pass
>>bl main;
>> so my procedure can not enter into main function , the program run fails.
>> but when i delete the SQLite codes, the procedure recover normal.
>>
>>
>> what's the reason? would i get your help? thanks very much.
>>
>>
>> ――
>> olli.zhong
>> General Manager
>> Shenzhen Zony Medical Electronics Co.,Ltd. China
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
>
--
D. Richard Hipp
d...@sqlite.org
___
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 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] Efficient usage of sqlite
Baruch Burstein wrote: > My resources are a bunch of sound and image files, level data files, > script files and other game data stuff. Instead of distributing my game > with about 20-30 small (some very small) files, I thought I would roll all > the files into some kind of archive. Why not use an existing archive format - say, a ZIP file? There's no shortage of libraries, open source and otherwise, to work with ZIP files. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generated SQL from Skip and Take (EntityFramework)
Steffen Mangold wrote: > SELECT name FROM employee ORDER BY name ASC LIMIT 99 OFFSET > > Of course this only works if the LINQ expression has a Take(m) clause > specified besides Skin(n). If Take(m) is not specified you > could work around by inserting a fake "LIMIT" clause, eg.: > > SELECT name FROM employee ORDER BY name ASC LIMIT (SELECT count(*) FROM > employee) OFFSET ... or you could just write "LIMIT -1". Negative values mean "no limit". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
On Mon, Jan 2, 2012 at 2:20 PM, Ivan Shmakov wrote: > > Baruch Burstein writes: > > > Which brought me to wonder if storing 5-10 tables with some of them > > having <500 bytes of data may be very inefficient. > > Which kind of game data takes so little space, I wonder? > It is my first game, as I am just learning game programming. It is a sokoban clone. -- Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite crash info
On Sun, Jan 1, 2012 at 10:49 AM, Lalitkumar Choudhary < lalitkumar.choudh...@lnties.com> wrote: > Hi, > > I want to know one thing about sqlite crashing, you had mentioned in your > document which i had found in your website(www.sqlite.org). > Sqlite database will crash if you write some garbage value into the file. > May i know exactly what is the garbage value? Is it any value different > from datatype or anything else, can u please give one example? > The SQLite database engine will not crash or segfault or otherwise cause your program to stop running if it encounters garbage data in a file. But it will return SQLITE_CORRUPT, indicating that your database is corrupt. > > Thanks & Regards > Lalit Kumar Choudhary > Software Engineer > L&T Integrated Engineering Services > KIADB, Hebbal Industrial Area > Hootagalli, Mysore 570018, Karnataka > Mob +91 9731003527 > > > > > > > Larsen & Toubro Limited > > www.larsentoubro.com > > This Email may contain confidential or privileged information for the > intended recipient (s) If you are not the intended recipient, please do not > use or disseminate the information, notify the sender and delete it from > your system. > ___ > 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
Re: [sqlite] sqlite crash info
On Sun, Jan 1, 2012 at 4:49 PM, Lalitkumar Choudhary < lalitkumar.choudh...@lnties.com> wrote: > ...Sqlite database will crash if you write some garbage value into the > file. May i know exactly what is the garbage value? Is it any value > different from datatype or anything else, can u please give one example? > That wording does not mean a specific value, but is intended to mean something like "if you modify the database file from outside the sqlite3 API, results are undefined" where "undefined" means "almost certainly corruption." For example: echo "123" >> mydbfile that could effectively corrupt it even though it doesn't actually modify any bytes used by the db. (Whether or not that _does_ corrupt it depends largely on how sqlite3 tracks the logical end-of-file.) -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite crash info
Hi, I want to know one thing about sqlite crashing, you had mentioned in your document which i had found in your website(www.sqlite.org). Sqlite database will crash if you write some garbage value into the file. May i know exactly what is the garbage value? Is it any value different from datatype or anything else, can u please give one example? Thanks & Regards Lalit Kumar Choudhary Software Engineer L&T Integrated Engineering Services KIADB, Hebbal Industrial Area Hootagalli, Mysore 570018, Karnataka Mob +91 9731003527 Larsen & Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s) If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Generated SQL from Skip and Take (EntityFramework)
Hi, i would like to push an old question again to the users. In original it has written to the old forum bei "peter77" on 10-17-2008. But now I have the same problem. Here the question: "The Skip(n) method is not optimally transformed into SQL. Consider the following LINQ expression: (from e in _context.EMPLOYEE orderby e.NAME select e.NAME).Skip().Take(99); This generates the following SQL: SELECT [Var_8_1].[NAME] AS [NAME] WHERE NOT (EXISTS (SELECT [Var_8_3].[NAME] AS [NAME] FROM ( SELECT [Extent1].[NAME] AS [NAME] FROM [EMPLOYEE] AS [Extent1] ORDER BY [Extent1].[NAME] ASC LIMIT ) AS [Var_8_3] WHERE ([Var_8_1].[NAME] = [Var_8_3].[NAME]) OR (([Var_8_1].[NAME] IS NULL) AND ([Var_8_3].[NAME] IS NULL ORDER BY [Var_8_1].[NAME] ASC LIMIT 99 This is very slow if the employee table has a large number of recods, even if the NAME column is indexed. A much more optimal (and shorter) SQL would be: SELECT name FROM employee ORDER BY name ASC LIMIT 99 OFFSET Of course this only works if the LINQ expression has a Take(m) clause specified besides Skin(n). If Take(m) is not specified you could work around by inserting a fake "LIMIT" clause, eg.: SELECT name FROM employee ORDER BY name ASC LIMIT (SELECT count(*) FROM employee) OFFSET or just a huge number in the LIMIT clause: SELECT name FROM employee ORDER BY name ASC LIMIT 1000 OFFSET " Regards Steffen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
On 2/1/2012 3:47 PM, Simon Slavin wrote: 5 or 10 tables is not a problem. If you were talking about 50 to 100 tables, that might start to be a problem. But yes, you can store any number of files in the following way: CREATE TABLE resources ( pathAndName TEXT PRIMARY KEY, content BLOB) Compressing the resulting SQLite database often results in very small files. ...and if it's going to be a read-only dataset and there's a budget that allows a one-time charge of US$2500 to be incurred, I would highly recommend HWACI's CEROD extension that encrypts and compresses the data store so that to the world outside, it appears to be a "proprietary" format while your code sees it as a simple SQLite3 database.. Best Regards, Mohit. 2/1/2012 | 9:22 PM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
2012/1/2 Baruch Burstein : > My resources are a bunch of sound and image files, level data files, > script files and other game data stuff. Instead of distributing my game > with about 20-30 small (some very small) files, I thought I would roll all > the files into some kind of archive. I started to roll my own format, when > it occurred to me that sqlite may be well suited for this. Which brought me > to wonder if storing 5-10 tables with some of them having <500 bytes of > data may be very inefficient. I don't want to substitute a 20K file for my > <10K of files. I know this is not a lot, but it still bothers me, like what > when I have a game with 500M of files? (you never know, it may happen!). No > searching is needed (except once for the key to load a resource) Such data in SQLite take up less space than in the file system. You can save data in one table, see above. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient usage of sqlite
> Baruch Burstein writes: […] > My resources are a bunch of sound and image files, level data files, > script files and other game data stuff. Instead of distributing my > game with about 20-30 small (some very small) files, I thought I > would roll all the files into some kind of archive. I started to > roll my own format, when it occurred to me that sqlite may be well > suited for this. The ar(1) archive format [1] may also be considered. In particular, the Debian project distributes pre-built binaries for its software in ar archives (known as Debian package files, or .deb's, there), which consist of a metadata part (control.tar.gz) and the packaged files themselves (data.tar.gz.) However, if the intent is to associate a variety of metadata with each of the data files, using SQLite seems to me like a more natural solution. [1] http://en.wikipedia.org/wiki/Ar_(Unix) > Which brought me to wonder if storing 5-10 tables with some of them > having <500 bytes of data may be very inefficient. Which kind of game data takes so little space, I wonder? > I don't want to substitute a 20K file for my <10K of files. I know > this is not a lot, but it still bothers me, like what when I have a > game with 500M of files? (you never know, it may happen!). No > searching is needed (except once for the key to load a resource) -- FSF associate member #7257 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
