Re: [sqlite] Efficient usage of sqlite

[Simon Slavin]

On 2 Jan 2012, at 7:35am, Baruch Burstein wrote:

> On Sun, Jan 1, 2012 at 8:12 PM, Simon Slavin  wrote:
> 
>>> First, don't worry about inefficient.  What you need to worry about is
>>> not-efficient-enough.  For instance, your app taking 1/10th of a second
>>> slower is not a problem, but if your app becomes too slow to be fun to use,
>>> that's a problem.
>> 
>> By inefficient I meant space-wise, not time-wise.

Same argument.  Don't worry about saving every last possible byte.  Worry 
instead about saving enough bytes that you can usefully fit the game on disk.  
Don't worry about how long it'll take to download because it'll be zipped by 
then anyway.

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

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.

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

[Baruch Burstein]

On Sun, Jan 1, 2012 at 8:12 PM, Simon Slavin  wrote:

>
> On 1 Jan 2012, at 5:27pm, Baruch Burstein wrote:
>
> > I need a file format to hold a bunch of resources for my program. I
> thought
> > of using SQLite. However, I am debating two formats. The
> > more convenient one for me would put every few resources in
> > a separate table. However, this would result in small tables. Am I right
> > that this is very inefficient in SQLite? The other option would be to
> put a
> > bunch of unrelated resources in one table. Is this more efficient?
>
> First, don't worry about inefficient.  What you need to worry about is
> not-efficient-enough.  For instance, your app taking 1/10th of a second
> slower is not a problem, but if your app becomes too slow to be fun to use,
> that's a problem.
>
> By inefficient I meant space-wise, not time-wise.



> A reason to split resources up into many tables would be that each
> resource has different columns and you need to do cross-column searches.
>  Is this what you have ?
>
> If instead you just have different types of resource, just make another
> column in your table and put the thing you'd expected to be the table name
> in that column.
>
> 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)


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

[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] How to insert control characters into a table

[Yuriy Kaminskiy]

Kai Peters wrote:
> Hi,
> 
> how can I insert a control character like carriage return?
> 
> Something like:
> 
> update fielddefs set choices = 'Male' || '\r' || 'Female' where id = 2

update ... 'Male' || X'0D' || 'Female' ...

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

Re: [sqlite] search

[Durga D]

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/listinfo/sqlite-users
> >> ___
> >> 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
> >
> ___
> 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]

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] Transcoding?

[Simon Slavin]

On 1 Jan 2012, at 7:40pm, Zbigniew wrote:

> Reading the contents of the blog
> http://milky.manishsinha.net/2009/03/30/sqlite-with-c/ tried to follow
> the tips to access SQLite database from C, but there's a problem:
> actually database should keep UTF-8 encoded data. No problem, when
> one's using LATIN1 - but I tried LATIN2 strings, and they were
> inserted just "as they were".
> 
> Not sure: did I miss something in SQLite docs (any transcoding
> function available?) - or one has to transcode all the strings before
> insertion on his own, e.g. using iconv()?

You cannot correctly use LATIN2 or LATIN1 with SQLite.  SQLite handles only 
UTF-8 and UTF-16 correctly.  Do anything else and you're on your own -- some 
stuff works, some doesn't.  In other words, your last question is right: if 
your original text isn't UTF then you must create or use a library routine to 
convert it to UTF.

Also note that some of the source code on that page hasn't been corrected 
correctly.  Some editor he's using has changed apostrophes (') to directed 
quotes (‘), (’).  I think he's spotted some but not others.  Directed quotes 
will not work correctly.  It is correct to use apostrophes for quoting text 
strings.

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

[sqlite] Transcoding?

[Zbigniew]

Reading the contents of the blog
http://milky.manishsinha.net/2009/03/30/sqlite-with-c/ tried to follow
the tips to access SQLite database from C, but there's a problem:
actually database should keep UTF-8 encoded data. No problem, when
one's using LATIN1 - but I tried LATIN2 strings, and they were
inserted just "as they were".

Not sure: did I miss something in SQLite docs (any transcoding
function available?) - or one has to transcode all the strings before
insertion on his own, e.g. using iconv()?

Surely someone met the problem before... maybe some code example?
-- 
regards,
Z.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient usage of sqlite

[Simon Slavin]

On 1 Jan 2012, at 5:27pm, Baruch Burstein wrote:

> I need a file format to hold a bunch of resources for my program. I thought
> of using SQLite. However, I am debating two formats. The
> more convenient one for me would put every few resources in
> a separate table. However, this would result in small tables. Am I right
> that this is very inefficient in SQLite? The other option would be to put a
> bunch of unrelated resources in one table. Is this more efficient?

First, don't worry about inefficient.  What you need to worry about is 
not-efficient-enough.  For instance, your app taking 1/10th of a second slower 
is not a problem, but if your app becomes too slow to be fun to use, that's a 
problem.

A reason to split resources up into many tables would be that each resource has 
different columns and you need to do cross-column searches.  Is this what you 
have ?

If instead you just have different types of resource, just make another column 
in your table and put the thing you'd expected to be the table name in that 
column.

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

[Kit]

2012/1/1 Baruch Burstein :
> I need a file format to hold a bunch of resources for my program. I thought
> of using SQLite. However, I am debating two formats. The
> more convenient one for me would put every few resources in
> a separate table. However, this would result in small tables. Am I right
> that this is very inefficient in SQLite? The other option would be to put a
> bunch of unrelated resources in one table. Is this more efficient?

It is preferable to have fewer large tables, mainly due to ease of
maintenance, but in the case of independent data that can be
otherwise. Best to try both.

What count of tables are we talking? Hundreds or thousands are not a problem.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Efficient usage of sqlite

[Baruch Burstein]

I need a file format to hold a bunch of resources for my program. I thought
of using SQLite. However, I am debating two formats. The
more convenient one for me would put every few resources in
a separate table. However, this would result in small tables. Am I right
that this is very inefficient in SQLite? The other option would be to put a
bunch of unrelated resources in one table. Is this more efficient?


-- 
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] How to insert control characters into a table

[Kees Nuyt]

On Sat, 31 Dec 2011 13:16:58 -0800, Kai Peters 
wrote:

>
>Hi,
>
>how can I insert a control character like carriage return?
>
>Something like:
>
>update fielddefs 
>   set choices = 'Male' || '\r' || 'Female' where id = 2

Line endings can be included in text literals:

UPDATE fielddefs
   SET choices = 'Male' || '
' || 'Female'
   WHERE id = 2;

There's no need for concatenation here, this would have the same result:

UPDATE fielddefs 
   SET choices = 'Male
Female' 
   WHERE id = 2;


-- 
Regards,

Kees Nuyt

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