[sqlite] [SOLVED] How do I properly import numbers from CSV?

2009-12-07 Thread Walter Dnes
On Sun, Dec 06, 2009 at 11:26:42PM -0500, Walter Dnes wrote

> I still don't understand why 2009, *WITHOUT QUOTES* would be forced
> to text, i.e. ' 2009', when imported into a field that is declared
> as integer in the create statement.

  There are actually 2 solutions...

1) Use tab-delimited if possible (not really CSV)

2) Get rid of leading/trailing spaces if you're using comma-separated
input.  E.g. this row results in text fields being imported...
   2231,615HMAK, 2005,  3, 28,  8.0, , -1.0, ,  3.5

...while this one results in mostly numeric fields being imported...
2231,615HMAK,2005,3,28,8.0, ,-1.0, ,3.5

  As an added bonus, the script that got rid of unnecessary spaces also
knocked down the 11,143,911,240 byte CSV file to "only" 5,382,671,854
bytes.  This allowed me to import the file in 3 sub-2-gigabyte pieces
versus 6 pieces that the original required.

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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Igor Tandetnik
Nicolas Williams  wrote:
> I believe the right thing to do is to normalize strings when creating
> index entries, but to leave the table data unnormalized.  You'd have
> to make the equality operator also normalize though.

I believe that's precisely what ICU collations do. Two canonically equivalent 
strings compare equal, whichever way the database happens to store them.

Igor Tandetnik


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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Nicolas Williams
On Mon, Dec 07, 2009 at 05:35:49PM -0500, Igor Tandetnik wrote:
> Alexey Pechnikov 
> wrote: 
> > The normalization is now performed by any string operation. But more
> > fast and useful to do it once at data store. 
> 
> So, which normalization form should the data store choose for me? And
> what if I need a different one?
> 
> I'd rather the database store my data exactly the way I put it in. I
> really don't want it to decide for me what my data should look like.

I believe the right thing to do is to normalize strings when creating
index entries, but to leave the table data unnormalized.  You'd have to
make the equality operator also normalize though.

That way you can have a unique text column and it will accept 
only one way, composed or decomposed, but not both.

I.e., normalization-insensitive matching, normalization-preserving.
Provides the best user experience.  If multiple systems' input methods
produce text in different normalization forms, or even unnormalized,
users will still find their data -- no surprises.  And given that
whatever systems the users are using likely can display the strings
produced by their input modes, preserving those strings unmodified gives
you the highest likelihood that the strings returned will display
properly.

(This is what Solaris implements for NFSv4, CIFS and local ZFS
filesystem access, for example.  ZFS hashes directories, and it
normalizes filenames prior to hashing, both on create and lookup, but
the directory entries are left unnormalized.)

To do this right requires support in SQLite3, even if it's provided by
an extension.  I don't recall if user-defined collation functions
provide everything you need to support this.

> >>> May be automatically dropping the BOM for
> >>> ICU collated fields is more correct way.
> >> 
> >> Why don't you do just that in your application?
> > 
> > Yes, I fix it in my application, but this problem can be produced in
> > any application. 
> 
> One person's problem is another's feature. If that other application
> doesn't want BOM in its strings, it should strip it, just like yours
> now does.

+1

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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Igor Tandetnik
Alexey Pechnikov 
wrote: 
> On Tuesday 08 December 2009 01:35:49 Igor Tandetnik wrote:
>> So, which normalization form should the data store choose for me?
>> And what if I need a different one? 
>> 
>> I'd rather the database store my data exactly the way I put it in. I
>> really don't want it to decide for me what my data should look like. 
> 
> I think the custom collation is the user solution for data
> processing.

Perhaps. Any piece of software could be thought of as a solution for data 
processing - after all, all computers do is shuffle bits around. I'm not sure 
what this general statement adds to the discussion at hand.

Igor Tandetnik


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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Alexey Pechnikov
Hello!

On Tuesday 08 December 2009 01:35:49 Igor Tandetnik wrote:
> So, which normalization form should the data store choose for me? And what if 
> I need a different one?
> 
> I'd rather the database store my data exactly the way I put it in. I really 
> don't want it to decide for me what my data should look like.

I think the custom collation is the user solution for data processing. Or may 
be a collation is only data _visualization_ mechanism?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Igor Tandetnik
Alexey Pechnikov 
wrote: 
> The normalization is now performed by any string operation. But more
> fast and useful to do it once at data store. 

So, which normalization form should the data store choose for me? And what if I 
need a different one?

I'd rather the database store my data exactly the way I put it in. I really 
don't want it to decide for me what my data should look like.

>>> May be automatically dropping the BOM for
>>> ICU collated fields is more correct way.
>> 
>> Why don't you do just that in your application?
> 
> Yes, I fix it in my application, but this problem can be produced in
> any application. 

One person's problem is another's feature. If that other application doesn't 
want BOM in its strings, it should strip it, just like yours now does.

Igor Tandetnik

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


Re: [sqlite] Recipe to safely move/rename a database?

2009-12-07 Thread raf
Simon Slavin wrote:

> 
> On 7 Dec 2009, at 9:58pm, Chris Eich wrote:
> 
> > On my Linux platform, I find that the INSERT
> > INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably
> > long (it involves about 30MB of data).
> 
> Do you have a transaction around all the INSERT commands ?  This will speed 
> it up many fold.

there aren't multiple insert commands.
only a single insert command was mentioned.
it would be a single transaction already.

i have no advice, i'm just pointing out that
this advice seems to be based on the false
assumption that multiple transactions are
involved (because this is the most common
reason why things slow down).

> Another thing which will increase speed is to DROP all indexes (apart
> from PRIMARY KEY) before the INSERT commands, and recreate them
> afterwards.
>
> There's no reason why you shouldn't pursue the technique you
> mentioned, but I thought I'd give you an alternative you might prefer.
> 
> Simon.

cheers,
raf

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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Alexey Pechnikov
Hello!

On Tuesday 08 December 2009 01:07:54 Igor Tandetnik wrote:
> Alexey Pechnikov 
> wrote: 
> > Yes, the BOM is on the original string. But with ICU collation we can
> > see that 17 symbols string is equal to 16 symbols string. I think
> > this result is not right.
> 
> What's the basis for this belief? It's not at all uncommon for two Unicode 
> strings of different length (in codepoints) to collate equal - for example, 
> they could be canonically equivalent but in different normalization forms, or 
> contain weightless characters such as a zero-width non-breaking space 
> (U+FEFF), also known as BOM.

The normalization is now performed by any string operation. But more fast and 
useful to do it once at data store.
> 
> > May be automatically dropping the BOM for
> > ICU collated fields is more correct way.  
> 
> Why don't you do just that in your application?

Yes, I fix it in my application, but this problem can be produced in any 
application.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Recipe to safely move/rename a database?

2009-12-07 Thread Simon Slavin

On 7 Dec 2009, at 9:58pm, Chris Eich wrote:

> On my Linux platform, I find that the INSERT
> INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably
> long (it involves about 30MB of data).

Do you have a transaction around all the INSERT commands ?  This will speed it 
up many fold.

Another thing which will increase speed is to DROP all indexes (apart from 
PRIMARY KEY) before the INSERT commands, and recreate them afterwards.

There's no reason why you shouldn't pursue the technique you mentioned, but I 
thought I'd give you an alternative you might prefer.

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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Igor Tandetnik
Alexey Pechnikov 
wrote: 
> Yes, the BOM is on the original string. But with ICU collation we can
> see that 17 symbols string is equal to 16 symbols string. I think
> this result is not right.

What's the basis for this belief? It's not at all uncommon for two Unicode 
strings of different length (in codepoints) to collate equal - for example, 
they could be canonically equivalent but in different normalization forms, or 
contain weightless characters such as a zero-width non-breaking space (U+FEFF), 
also known as BOM.

> May be automatically dropping the BOM for
> ICU collated fields is more correct way.  

Why don't you do just that in your application?

Igor Tandetnik

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


[sqlite] Recipe to safely move/rename a database?

2009-12-07 Thread Chris Eich
I have a scenario where I want to move 99+% of the records from one database
to another, initially empty but for a set of table definitions (in practice,
copied from a template file). On my Linux platform, I find that the INSERT
INTO archive.my_table SELECT * FROM my_table WHERE (...) takes unreasonably
long (it involves about 30MB of data).

What I would rather do is: 1) move the current database file from its
current location to the archive location, 2) create a new current database
(from the same template I use now for the archive) and 3) copy back, from
archive to current, the rows that should *not* be archived (deleting them
from the archive afterward).

Clearly, I'll need to create a lock on the current database before moving
it, but I can foresee complications related to the "behind-the curtain"
filesystem operations being performed. If someone has worked out all the
pitfalls of this scenario, I'd appreciate a recipe.

Thanks,

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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Alexey Pechnikov
Hello!

On Monday 07 December 2009 22:29:47 Igor Tandetnik wrote:
> What do you mean by "SQLite stores" the BOM? Are you saying that you are 
> passing in a string without the BOM, and SQLite spontaneoulsy manufactures 
> one? Double-check your application - I suspect you'll find that you are 
> actually passing strings with BOM to SQLite to begin with, and it just stores 
> them faithfully.

Yes, the BOM is on the original string. But with ICU collation we can see that 
17 symbols string is equal to 16 symbols string.
I think this result is not right. May be automatically dropping the BOM for ICU 
collated fields is more correct way.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite bug report - large databases only - 'database or disk is full'

2009-12-07 Thread pirx

SQLite bug report
 
Summary:
--

error message:

   Error: near line 2: database or disk is full

It happens with plenty of disk space available and with 'unlimited' database 
size.
It does not happen on all systems. It does not happen on small databases.

 
Details:
--


The error occurs on 
- windows server 2003 SP2 64bit, windows server 2009 64bit
- windows vista, windows 7 rc1, windows 7 - all 64-bit

The error does _not_ occur on 
- windows server 2003 SP2 32bit, windows xp sp3 32 bit

SQLite3 versions affected: all the ones released in 2009, possibly all the 
earlier ones, too.


The bug was 'distilled' with the reproduction described below. 



Reproduction
--

We (1) create a table in the database, (2) create an input file; (3) import the 
input file into the table mulltiple times.

Using the files attached below the error did occur during the third/fourth 
import, at database size around 22-28gb.



Additional information and observations


1. This is not a new bug - it's been around for the last year, probably two - 
but it was difficult to spot and isolate.

2. The problem is 'touchy' - with different random seed in the generated table 
the problem may occur at 45gb db size instead of 20-30gb. When I tried to use, 
as input data, the same line repeated millions of times - the problem did not 
occur at all - tests were aborted by me at 160-260 gb database size.

3.The problem does _not_ depend _purely_ on the data being imported. The 
reproduction script loads the same data set multiple times and fails on the 
third/fourth time - so it is the database size which triggers it.

4. When the input file is smaller (1gb instead of 8gb), the problem still 
occurs in the 20-30gb database size range - so there is nothing magical about 
the number of imports.

5. The test database here is created with pragma page_size = 32768. The same 
error message occurs for other page sizes and at various cache sizes. 

6. In some other tests (not using to the scripts here, with different data) the 
import was good (about 20gb size) but an attempt to create an index on the 
imported data resulted in the same error diagnostic - after about 30mins of 
running.

7. This is not an SQLITE3 problem - when using .NET wrapper for SQLite or using 
any of the admin tools - the problems occurs in the same area, even though 
messages are not reported the same way by these tools [and I am getting 
sometimes 'database image in malformed' after such tools].

 
 


 
Scripts to reproduce the bug
--

 
++
== awk program to produce the table - save as: t1b.w
BEGIN {
srand(13) # assure all tests have the same data
for (i=0;i inpb.txt
 
echo - load table with data sets the first 10 times
time /T
for %%i in (1,2,3,4,5,6,7,8,9,10) do c:\apps\sqlite3 tstb.db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] SQLite version 3.6.21

2009-12-07 Thread D. Richard Hipp
SQLite version 3.6.21 is now available on the SQLite website:  
http://www.sqlite.org/

SQLite version 3.6.21 is a monthly maintenance release of SQLite.   
Upgrading from prior versions is optional.  Version 3.6.21 features an  
enhancement to the sqltie3_trace() interface such that the values of  
bound parameters are inserted into the SQL output emitted by  
sqlite3_trace(), making application diagnostics easier.  Version  
3.6.21 also features general performance improvements and a rework of  
the FTS3 full-text search extension for improved robustness and  
performance.

SQLite version 3.6.21, as all versions of SQLite since 3.6.17, has  
been tested to 100% MC/DC and branch coverage using multiple  
independently developed test harnesses.

As always, please let us know if you encounter any difficulties with  
this or any other SQLite release.

D. Richard Hipp
d...@hwaci.com



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


Re: [sqlite] The byte order mark problem in fields with ICUcollation

2009-12-07 Thread Igor Tandetnik
Alexey Pechnikov 
wrote: 
> The SQLite in the field with ICU collation does store the BOM in the
> start of string.

What do you mean by "SQLite stores" the BOM? Are you saying that you are 
passing in a string without the BOM, and SQLite spontaneoulsy manufactures one? 
Double-check your application - I suspect you'll find that you are actually 
passing strings with BOM to SQLite to begin with, and it just stores them 
faithfully.

Igor Tandetnik


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


Re: [sqlite] Effect of VACUUM when very low on disk space

2009-12-07 Thread Simon Slavin

On 7 Dec 2009, at 2:27pm, Nick Shaw wrote:

> When the file exceeds a certain size, I DELETE a specific number of
> records, then VACUUM the file to get the size back below required
> limits.  This works fine, however what happens to the VACUUM command if
> there is insufficient disk space for SqLite to write out the cleaned up
> copy of the database?  I assume it will fail, but the documentation
> doesn't specifically say how much disk space is required during a VACUUM
> operation.  The newly vacuumed file's size should end up being equal to
> or less than the existing file's size, so I assume I'll need at least
> the current database's size of disk space free, but will it ever require
> more space than that to perform the VACUUM (e.g. from other temporary
> files)?

Because there is no documentation about how these things work, even if we 
answered your question, the answer might change in a future version.  For 
example, VACUUM might go from rewrite-in-place to writing a fresh copy of the 
entire file.  It should definitely do this if it notices database corruption 
while VACUUMing.

In similar situations to yours I have instead created a huge pointless file to 
take up disk space pointlessly.  The routine that recovers data deletes this 
huge file (giving it guaranteed free space to work) then does the recovery, 
then creates a huge dummy file again.  This technique means that not only will 
your automated system have a chance to work but you will also be able to free 
up enough space to do emergency recovery stuff manually.

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


Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Shane Harrelson
As others have said, there are lots of ways to store the image data directly
in the DB with BLOBs, encoding, etc.   Alternatively, you could store the
pics separate from the DB and just store the path to the pic file in the DB.

-Shane


On Sun, Dec 6, 2009 at 8:35 PM, Ted Rolle, Jr.  wrote:

> From what I read, it is necessary to have a programmatic interface to
> put images into a database.  True?
>
> ---
> 
> 3.14159265358979323846264338327950  Let the spirit of pi spread
> 2884197169399375105820974944592307  all around the world!
> 8164062862089986280348253421170679  http://pi314.at PI VOBISCUM!
> 
> ___
> 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] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps

>Could you please give me a step by step on how to do that? Not via a
>small program written in a programming language, but only via the
>sqlite3 shell, as both you and I agree that was the implied intent of
>the OP's question.

Sorry, I'm no vxWorks, Unix, Linux, MacOS, Windows, AS400, Symbian, 
you_name_it ... guru.  I just can't cite you a portable combination of 
command-line tools to do that.

I took the question differently, don't read me backwards.  I didn't 
think of  manual input on individual sqlite3 command line, but rather 
building some SQL input file.  That one has to use a complex 
combination of dump, grep, awk, sed, emacs,  whatever_tool_exists, to 
have lines like:

insert into pics(id, pic) values ($id$, x'$pic$');

built and replacing $id$ by the required id and $pic$ by a hex dump 
doesn't seem unfeasible.  I took the question to mean: "Is it necessary 
to write a dedicated SQLite program to insert pics or other binary data 
into an SQLite base".  My answer was that no, suitable combination of 
command-line style tools can do it, with a short example of what needs 
to be done.  That a particular environment doesn't offer a ready to use 
facility and one has to make a generic "utility" corresponding to the 
pseudo-code:

open argument filename as binary
foreach byte b  fprintf to stdout b as %02x
close files

is not in my view "SQLite development".  No SQLite header needed for 
that, no SQLite API or wrapper.  If ever such utility doesn't readily 
exist and can't be simulated on a particular platform is something 
else.  That utility can be used on any base and does only depend on the 
OS used.

Look, to come up with a working example, here's what I did:

Local $h = FileOpen("D:\2009-12-07_024519.bmp", 16)
Local $v = FileRead($h)
FileClose($h)
ConsoleWrite("x'" & Hex($v) & "'" & @lf)

Then I copied the console output and used it inside my favorite SQLite 
manager thusly:

insert into t (d) values 
(x'424D3E003600280002000100010018000800C40EC40E0001');

I wrote the four line "utility"  above in [Windows] AutoIt just because 
I was lazy enough to question myself about what available command-line 
utility or combination thereof could produce the same result quickly, 
DOS, Windows or MinGW or else.

Now, just by curiosity, I just tried to dig out a _really_ dusty 
dump.exe dated ... 24/10/1987 from the brave DOS time and it produces:

D:\>dump test.bmp
H  42 4D 3E 00 00 00 00 00 00 00 36 00 00 00 28 00   'BM>...6...(.'
0010H  00 00 02 00 00 00 01 00 00 00 01 00 18 00 00 00   ''
0020H  00 00 08 00 00 00 C4 0E 00 00 C4 0E 00 00 00 00   '..D...D.'
0030H  00 00 00 00 00 01 FF FF FF FF FF FF 00 00 '..'

Name and contents differ because I dropped the initial test file from 
yesterday.

Nevertheless, I believe it's easy to use a less prehistoric "dump" (or 
equivalent) then grep (or equivalent) the output to produce what's needed.

That subsequent regexp should be considered "programmatic" or not is up 
to the reader.  But then, even entering _anything_ on any shell of any 
OS can be considered "programmatic", whatever that means.

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


[sqlite] Murmurhash2 function as new extension

2009-12-07 Thread Alexey Pechnikov
Hello!

See
http://mobigroup.ru/files/sqlite-ext/murmurhash/
and a few words on russian
http://geomapx.blogspot.com/2009/12/murmurhash-20.html

The examples:
select murmurhash(1,2,3);
1074609160
sqlite> select murmurhash('hello',99);
3350841100
sqlite> select murmurhash('hi!',1);
2372833641
sqlite> select murmurhash('q',0);
2030783509
sqlite> select murmurhash('test');
1026673864
sqlite> select murmurhash();


P.S. The inet extension is updated some times ago. I recommend
for all users to get the new version:
http://mobigroup.ru/files/sqlite-ext/md5/
The tests set is extended, see ipv4-ext.sql

The versioning extension for tables history logging and versioning/replication
will be in production soon. I did change the base concept for total 
simplification
and more usability.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Sqlite3.exe command line tool - winXP

2009-12-07 Thread Wilson, Ronald
My mistake - I meant:

sqlite> .schema

if sqlite is telling you that the table doesn't exist, the .schema command 
should tell you what tables exist.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of dave lilley
> Sent: Monday, December 07, 2009 2:06 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Sqlite3.exe command line tool - winXP
> 
> 
> Well I've tried the following
> 
> sqlite3  dbfile
> I get put to the command line.
> 
> typed in the following and below is the results.
> 
> sqlite> .show
>  echo: off
>   explain: on
>   headers: on
>  mode: explain
> nullvalue: ""
>output: stdout
> separator: "|"
> width: 4 13 4 4 4 13 2 13
> sqlite> .schema ?table? accounts;
> sqlite> .schema ?accounts?;
> sqlite>
> 
> So I don't know if it's me not understanding the syntax or not.
> 
> I have 5 tables one of them *is* accounts.
> 
> thanks for your reply.
> 
> 
> 2009/12/7 Wilson, Ronald 
> 
> > what tables do you have?
> > sqlite> schema;
> >
> > from my mobile 434.851.1612
> >
> > On Dec 5, 2009, at 11:31 PM, "dave lilley"  wrote:
> >
> > >
> > > I have been trying to (without much joy) get a list of rows from a
> > > table
> > > within an sqlite3 DB.
> > >
> > > I've been starting from the CMD prompt sqite3 
> > > dropping into sqlite3 cmd prompt i type in
> > >
> > > sqlite> select * from accounts;
> > > SQL error: no such table: accounts
> > > sqlite>
> > >
> > > Now I've used SQLite Database manager 3.1 to create the tables (at
> > > least 5
> > > in the one db) and have been able to access the tables until i
> > > started to
> > > entry text for a blob field and now the DB manager falls over!
> > >
> > > I am using ruby 1.8.6 as my dev language an suppose i should put it
> > > to that
> > > community BUT my issue of not being able to generate a list of data
> > > from an
> > > sql statement when i know the names of the tables does belong here.
> > >
> > > any help would be appreciated.
> > >
> > > dave.
> > > ___
> > > 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] Putting images into SQLite.

2009-12-07 Thread P Kishor
On Mon, Dec 7, 2009 at 9:55 AM, Jean-Christophe Deschamps  
wrote:
> Hi Puneet,
>
>>Yes, that seems like a reasonable interpretation of the OP's question,
>>one I also understood. One thing I don't understand though,
>>Jean-Christophe, even though one can enter base64 encoded "images"
>>into the db via the sqlite shell, how does one create the base64
>>encoded images? One would need a way to do that on the shell command
>>line no? Not to mention, how inconvenient it would be to do for any
>>"meaningful" image, as you can see from your own 2x1 pixels image
>>example.
>
> But the hex isn't base64 encoded at all: it's merely a hex byte after
> byte linear dump, something that is obvious to get without any
> dependancy on SQLite or the environment.

I still don't understand your strategy above. Where am I, the user,
supposed to get the "hex byte after byte linear dump" from? Imagine, I
have a photograph of you called jcd.jpg sitting on my hard disk at
~/pics/jcd.jpg and I want to insert it as a blob into

CREATE TABLE pics (id INTEGER PRIMARY KEY, pic BLOB);

via the sqlite3 shell.

Could you please give me a step by step on how to do that? Not via a
small program written in a programming language, but only via the
sqlite3 shell, as both you and I agree that was the implied intent of
the OP's question.




> BTW, having images stored
> direct in hex blobs without any convoluted encoding allows smart
> database managers to display the images directly in resultset grid,
> which I find very convenient.
>
> I didn't mean to enforce this as a convenient way to handle routine
> operation, except if the particular situation demands it.  I fully
> agree with your remark that it isn't very practical, but I've slowly
> discovered that SQLite is so flexible that it shows up in many
> environments where one wouldn't expect a database layer to simply exist
> in the first place.
> I've no clue as why the OP asked that and what his actual constraints
> really are, but if he needs a text only, command-line only,
> no-specific-program way to have his job done, then SQLite is still his
> friend and not a stumbling block.
>
> That's all of SQLite glory to offer workable solutions to incredingly
> strange situations!
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps
Hi Puneet,

>Yes, that seems like a reasonable interpretation of the OP's question,
>one I also understood. One thing I don't understand though,
>Jean-Christophe, even though one can enter base64 encoded "images"
>into the db via the sqlite shell, how does one create the base64
>encoded images? One would need a way to do that on the shell command
>line no? Not to mention, how inconvenient it would be to do for any
>"meaningful" image, as you can see from your own 2x1 pixels image
>example.

But the hex isn't base64 encoded at all: it's merely a hex byte after 
byte linear dump, something that is obvious to get without any 
dependancy on SQLite or the environment.  BTW, having images stored 
direct in hex blobs without any convoluted encoding allows smart 
database managers to display the images directly in resultset grid, 
which I find very convenient.

I didn't mean to enforce this as a convenient way to handle routine 
operation, except if the particular situation demands it.  I fully 
agree with your remark that it isn't very practical, but I've slowly 
discovered that SQLite is so flexible that it shows up in many 
environments where one wouldn't expect a database layer to simply exist 
in the first place.
I've no clue as why the OP asked that and what his actual constraints 
really are, but if he needs a text only, command-line only, 
no-specific-program way to have his job done, then SQLite is still his 
friend and not a stumbling block.

That's all of SQLite glory to offer workable solutions to incredingly 
strange situations!

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


[sqlite] BUG: The byte order mark problem in fields with ICU collation

2009-12-07 Thread Alexey Pechnikov
Hello!

The SQLite in the field with ICU collation does store the BOM in the start of 
string. This bahaviour produce some 
difficulty resolving problems.

select length(name),x.name, hex(x.name) from (select distinct name from 
const_telephony_direction where delete_date IS NULL) as x;
...
17|sovintel-konmark|EFBBBF736F76696E74656C2D6B6F6E6D61726B
...

The hex sequence EFBBBF is UTF-8 byte order mark.

The right value is
16|sovintel-konmark|736F76696E74656C2D6B6F6E6D61726B



.schema const_telephony_direction
CREATE TABLE const_telephony_direction (
...
name text collate russian not null, -- группа направлений
...
);

pragma collation_list;
0|russian
1|NOCASE
2|RTRIM
3|BINARY


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread P Kishor
On Mon, Dec 7, 2009 at 9:15 AM, Jean-Christophe Deschamps  
wrote:
>
>>What is "programatically"
>>
>>How, in any meaningful way, is this different than running a shell
>>command (program of an extremely brief size)?
>
> I took the OP's phrasing to mean that he needed a way to do it with
> e.g. command-line available programs, but in any case without having to
> use a programming language to develop an ad hoc code to do it.
>


Yes, that seems like a reasonable interpretation of the OP's question,
one I also understood. One thing I don't understand though,
Jean-Christophe, even though one can enter base64 encoded "images"
into the db via the sqlite shell, how does one create the base64
encoded images? One would need a way to do that on the shell command
line no? Not to mention, how inconvenient it would be to do for any
"meaningful" image, as you can see from your own 2x1 pixels image
example.



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Jean-Christophe Deschamps

>What is "programatically"
>
>How, in any meaningful way, is this different than running a shell 
>command (program of an extremely brief size)?

I took the OP's phrasing to mean that he needed a way to do it with 
e.g. command-line available programs, but in any case without having to 
use a programming language to develop an ad hoc code to do it.


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


[sqlite] Effect of VACUUM when very low on disk space

2009-12-07 Thread Nick Shaw
Hi all,

 

I'm currently writing some code that attempts to keep an SqLite database
file below a certain file size (the embedded PC it is running on has a
wonderful side effect that when the disk runs out of disk space, it
blue-screens Windows and you can't boot the device after that - how
helpful!).

 

When the file exceeds a certain size, I DELETE a specific number of
records, then VACUUM the file to get the size back below required
limits.  This works fine, however what happens to the VACUUM command if
there is insufficient disk space for SqLite to write out the cleaned up
copy of the database?  I assume it will fail, but the documentation
doesn't specifically say how much disk space is required during a VACUUM
operation.  The newly vacuumed file's size should end up being equal to
or less than the existing file's size, so I assume I'll need at least
the current database's size of disk space free, but will it ever require
more space than that to perform the VACUUM (e.g. from other temporary
files)?

 

Also, is there any SqLite command I can use to get the database file's
size?  I'm currently using the Win32 API call GetFileSizeEx() to get the
file's size which works fine, but does SqLite itself know the database
file's size when it has the database open?  It would seem more
gracefully coded if I didn't have to open a separate handle to the
database file just to get the file size out.

 

Thanks,

Nick.

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


Re: [sqlite] Putting images into SQLite.

2009-12-07 Thread Teg
Hello Bruce,

Monday, December 7, 2009, 1:20:10 AM, you wrote:

BR> On Dec 6, 2009, at 6:17 PM, Jean-Christophe Deschamps wrote:

>> I was simply replying to the OP's actual question:
>> 
> From what I read, it is necessary to have a programmatic interface to
> put images into a database.  True?
>> 
>> So, no, it isn't _necessary_ (but recommended). 

BR> What is "programatically"

BR> How, in any meaningful way, is this different than running a
BR> shell command (program of an extremely brief size)?

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

Does the shell command have an interface for specifying on disk objects
to insert as blobs? Might be a neat addition.

"INSERT INTO BLA.'file:/tmp/bbies.jpg'..."

I do it programmatically. I wrote an image interface class for
inserting, deleting and searching on hashes, filenames and meta-data.

Before the parameterized inserts were the norm, I used to encode my
blobs before insert. Using the newer interface, it's pointless to
encode. I crank the page size up to max for my image databases too.



-- 
Best regards,
 Tegmailto:t...@djii.com

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


Re: [sqlite] char,ascii function in sqlite

2009-12-07 Thread Jean-Christophe Deschamps

>hi , im using sqlite3 in debain
>
>i want to check  the first character should not be an special character.
>[by before insert trigger]
>how to do it?
>i thought of using  ascii function but i didnt find in sqlite.

No such function is part of the SQLite core.  But I wrote an extension 
offering that function, and a lot more.  Drop me a mail if you're 
interessed in the C source.

Cheers.

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


Re: [sqlite] Cache size tuning

2009-12-07 Thread Pavel Ivanov
Maybe 'pragma cache_size'?

Pavel

On Fri, Dec 4, 2009 at 10:05 PM, Richard Klein
 wrote:
> Does SQLite provide any tools to help the
> developer tune the database cache size?
>
> Thanks,
> - Richard Klein
> ___
> 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] sqlite3_exec() returns SQLITE_OK but Databaseshowsdifferent result

2009-12-07 Thread souvik.datta

Hi Pavel,

Thanks a lot. You are right. I missed out a sqlite3_finalize() and that 
prevented the commit.

Regards,
Souvik

-Original Message-
From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Mon 12/7/2009 6:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite3_exec() returns SQLITE_OK but 
Databaseshowsdifferent result
 
> 1) You are opening a different file than the one you think you are opening. 
> E.g. you are using a relative path to the file, and the workding directory is 
> not what you expect it to be.
>
> 2) You are starting an explicit transaction (see BEGIN) and forgetting to 
> commit it.

3) You are starting to execute some SELECT statement and don't finish
it (do not execute sqlite3_reset or sqlite3_finalize). It prevents
SQLite from committing anything no matter if you issue COMMIT
explicitly or rely on auto-committing.


Pavel

On Mon, Dec 7, 2009 at 7:33 AM, Igor Tandetnik  wrote:
> souvik.da...@wipro.com wrote:
>> I am accessing the same database from two different processes. From one of 
>> the process , I am able
>> to create tables in runtime but when I am trying to create a table from 
>> another process in runtime on the same DB
>> I am not able to create so. The strange part is that I find
>> sqlite3_exec() is returning retCode as SQLITE_OK. But then , when
>> I am going and checking the DB, I am not able to see the table.
>
> There are two common causes for this.
>
> 1) You are opening a different file than the one you think you are opening. 
> E.g. you are using a relative path to the file, and the workding directory is 
> not what you expect it to be.
>
> 2) You are starting an explicit transaction (see BEGIN) and forgetting to 
> commit it.
>
> Igor Tandetnik
>
> ___
> 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


Please do not print this email unless it is absolutely necessary. 

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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


Re: [sqlite] char,ascii function in sqlite

2009-12-07 Thread Pavel Ivanov
As Simon said use substr() to get first character and use cast(X'FF'
as text) to convert some hexadecimal character code to symbol (in my
example the code is FF = 255). But there's no way to convert character
into its code, so you cannot do any arithmetics with it though you
probably don't need it because comparison seems to be enough and it's
perfectly good with text data type.

Pavel

On Mon, Dec 7, 2009 at 5:24 AM, Simon Slavin  wrote:
>
> On 7 Dec 2009, at 5:56am, greensparker wrote:
>
>> i want to check  the first character should not be an special character.
>> [by before insert trigger]
>> how to do it?
>> i thought of using  ascii function but i didnt find in sqlite.
>
> substr(X,Y,Z)
>
> will get you the first character.  I'm not sure what you mean by 'special', 
> but the core functions are here:
>
> http://www.sqlite.org/lang_corefunc.html
>
> 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] sqlite3_exec() returns SQLITE_OK but Database showsdifferent result

2009-12-07 Thread Pavel Ivanov
> 1) You are opening a different file than the one you think you are opening. 
> E.g. you are using a relative path to the file, and the workding directory is 
> not what you expect it to be.
>
> 2) You are starting an explicit transaction (see BEGIN) and forgetting to 
> commit it.

3) You are starting to execute some SELECT statement and don't finish
it (do not execute sqlite3_reset or sqlite3_finalize). It prevents
SQLite from committing anything no matter if you issue COMMIT
explicitly or rely on auto-committing.


Pavel

On Mon, Dec 7, 2009 at 7:33 AM, Igor Tandetnik  wrote:
> souvik.da...@wipro.com wrote:
>> I am accessing the same database from two different processes. From one of 
>> the process , I am able
>> to create tables in runtime but when I am trying to create a table from 
>> another process in runtime on the same DB
>> I am not able to create so. The strange part is that I find
>> sqlite3_exec() is returning retCode as SQLITE_OK. But then , when
>> I am going and checking the DB, I am not able to see the table.
>
> There are two common causes for this.
>
> 1) You are opening a different file than the one you think you are opening. 
> E.g. you are using a relative path to the file, and the workding directory is 
> not what you expect it to be.
>
> 2) You are starting an explicit transaction (see BEGIN) and forgetting to 
> commit it.
>
> Igor Tandetnik
>
> ___
> 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] SQL selecting in two related tables?

2009-12-07 Thread Tim Romano
I had responded to Jean-Denis Muys as follows:
> select M.title
> from Magazine as M
> join
> (
> select distinct issn, issues from subscription
> ) as SubscriptionVariants
>
> on SubscriptionVariants.issn = M.issn
> order by M.title, SubscriptionVariants.issues
>
>   

But I neglected to add the issues column to the outer select; it should 
have read:

select M.title, SubscriptionVariants.issues
from Magazine as M
join
(
select distinct issn, issues from subscription
) as SubscriptionVariants

on SubscriptionVariants.issn = M.issn
order by M.title, SubscriptionVariants.issues


Regards
Tim Romano


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


Re: [sqlite] sqlite3_exec() returns SQLITE_OK but Database showsdifferent result

2009-12-07 Thread Igor Tandetnik
souvik.da...@wipro.com wrote:
> I am accessing the same database from two different processes. From one of 
> the process , I am able
> to create tables in runtime but when I am trying to create a table from 
> another process in runtime on the same DB
> I am not able to create so. The strange part is that I find
> sqlite3_exec() is returning retCode as SQLITE_OK. But then , when
> I am going and checking the DB, I am not able to see the table.

There are two common causes for this.

1) You are opening a different file than the one you think you are opening. 
E.g. you are using a relative path to the file, and the workding directory is 
not what you expect it to be.

2) You are starting an explicit transaction (see BEGIN) and forgetting to 
commit it.

Igor Tandetnik

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


Re: [sqlite] char,ascii function in sqlite

2009-12-07 Thread Simon Slavin

On 7 Dec 2009, at 5:56am, greensparker wrote:

> i want to check  the first character should not be an special character.
> [by before insert trigger]
> how to do it?
> i thought of using  ascii function but i didnt find in sqlite.

substr(X,Y,Z)

will get you the first character.  I'm not sure what you mean by 'special', but 
the core functions are here:

http://www.sqlite.org/lang_corefunc.html

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


Re: [sqlite] Sqlite3.exe command line tool - winXP

2009-12-07 Thread Simon Slavin

On 7 Dec 2009, at 7:05am, dave lilley wrote:

> sqlite3  dbfile
> I get put to the command line.

How many bytes long is this file 'dbfile' ?

> typed in the following and below is the results.
> 
> sqlite> .show
> echo: off
>  explain: on
>  headers: on
> mode: explain
> nullvalue: ""
>   output: stdout
> separator: "|"
>width: 4 13 4 4 4 13 2 13
> sqlite> .schema ?table? accounts;
> sqlite> .schema ?accounts?;
> sqlite>
> 
> So I don't know if it's me not understanding the syntax or not.
> 
> I have 5 tables one of them *is* accounts.

Type '.schema' without any quotes or question-marks.  Do you get any response 
to that ?

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