headers
Kees Nuyt | 1 Jan 2012 11:40
Picon

Re: How to insert control characters into a table

On Sat, 31 Dec 2011 13:16:58 -0800, Kai Peters <kpeters@...>
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,
(Continue reading)

Permalink | Reply | 0 comments |
headers
Baruch Burstein | 1 Jan 2012 18:27
Picon
Gravatar

Efficient usage of sqlite

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
Permalink | Reply | 11 comments |
headers
Kit | 1 Jan 2012 18:54
Picon

Re: Efficient usage of sqlite

2012/1/1 Baruch Burstein <bmburstein@...>:
> 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
Permalink | Reply | 0 comments |
headers
Simon Slavin | 1 Jan 2012 19:12

Re: Efficient usage of sqlite


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.
Permalink | Reply | 9 comments |
headers
Zbigniew | 1 Jan 2012 20:40
Picon
Gravatar

Transcoding?

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.
Permalink | Reply | 1 comment |
headers
Simon Slavin | 1 Jan 2012 20:49

Re: Transcoding?


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.
Permalink | Reply | 0 comments |
headers
Durga D | 2 Jan 2012 06:25
Picon

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

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,
(Continue reading)

Permalink | Reply | 9 comments |
headers
Durga D | 2 Jan 2012 06:28
Picon

Re: search

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 <aris.sety@...> 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
(Continue reading)

Permalink | Reply | 1 comment |
headers
Yuriy Kaminskiy | 2 Jan 2012 07:37
Picon

Re: How to insert control characters into a table

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' ...
Permalink | Reply | 0 comments |
headers
Simon Slavin | 2 Jan 2012 08:01

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


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,
(Continue reading)

Permalink | Reply | 7 comments |

Gmane