[sqlite] group by z HAVING count(z>12.5)=2

[YAN HONG YE]

If I wanna to know how much z>12.5 result in the database,could I use this 
following cmd?
SELECT * from TableA WHERE z in (
   SELECT z FROM TableA GROUP BY z
   HAVING count(z>12.5)=2 
);

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

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[stefanos sofroniou]

Hector, here's the link from official SQLite website about Flags for File 
opening operations: 

http://www.sqlite.org/c3ref/c_open_autoproxy.html

Unfortunately I don't know anything (yet!) about SQLite under Java. If you need 
any other kind of help, let me know.


>
> From: Hector Guilarte 
>To: sqlite-users@sqlite.org 
>Sent: Thursday, June 28, 2012 4:08 PM
>Subject: Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly
> 
>Hello?
>
>I'm starting to feel hopeless, No luck in StackOverflow and no luck here
>:-(...
>
>Well, i guess it just can't be done.
>
>On Wed, Jun 27, 2012 at 10:13 AM, Hector Guilarte wrote:
>
>> Hello everyone,
>>
>> I'm new to SQLite as well as to this list. I'm writing because I was
>> planning on using SQLite for a personal -but public- project that I wanted
>> to make available through Google App Engine. It is basically a SQLite to
>> CSV converter and a SQLite to VCard converter. In other words, I have an
>> Address Book in a SQLite database and I wanted to export it to a well-known
>> format for importing it to some other places, as CSV and as VCard.
>>
>> I already placed my question in StackOverflow.com last friday with no
>> luck, it has only been seen 21 times and the only answer I received was not
>> helpfull since it was telling me somehing like "first use something like
>> what you are trying to develop yourself and then use yours with their
>> output" (nahh, I'm kidding, but the real answer is not far from that and it
>> can be seen here:
>> http://stackoverflow.com/questions/11155537/load-sqlite-from-inputstream-in-java-as-readonly
>> )
>>
>> If somebody has an answer, even if it is "It's not possible at all, so
>> drop it" and is a stackoverflow user, feel free to go ahead and answer over
>> there to earn the points, but please post your answer here as well. Now my
>> question as I wrote it in StackOverflow:
>>
>> I have an App which receives a SQLite database to read some data and
>> export it as an CSV. I'm trying to upload it to Google App Engine but I
>> faced a huge problem which I think makes it impossible to use the GAE for
>> this app.
>>
>> The problem is that since on the GAE I can't write to the FileSystem, I
>> can't open the JDBC Connection to the SQLite file and therefore I can't
>> read the data to convert to CSV. I've been looking for other options such
>> as Google Cloud Storage, but I don't want to use my only "free trial" of it
>> on this application, and actually I don't want to have to pay ever for this
>> app after the Free Trial ends, so this is not an option.
>>
>> After a lot of research, my only guess is that I might be able to load the
>> database straight from the InputStream as I received it from the upload
>> form I'm using to get it, however, this is a 100% lucky guess and I've not
>> been able to find anything about this approach online, but I just don't
>> want to believe it can't be done with any of the existing JDBC libraries to
>> SQLite and I'm hoping somebody here will tell me how to do it.
>>
>> If the InputStream approach is not possible, but you know some other way
>> to open a SQLite DB in GAE to READ ONLY, and then dispose it, feel free to
>> comment as well...
>>
>> If there is another option like "don't use JDBC, use a socket connection
>> with a pipe to open the connection with the InputStream", I'd also like to
>> hear that, it does not HAVE to be done with JDBC.
>>
>> Thanks a lot,
>>
>> Héctor Guilarte
>>
>___
>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] how to build sqlite4 (four)?

[Stephan Beal]

I apologize for top posting and not snipping but a tablet makes that
difficult...

The problem with a db-specific function is that it makes a lot of otherwise
portable, driver-agnostic sql code not applicable to sqlite4. For example
PDO and similar abstraction layers.
On Jun 28, 2012 11:32 PM, "Simon Slavin"  wrote:

>
> On 28 Jun 2012, at 9:05pm, Nico Williams  wrote:
>
> > I think AUTOINCREMENT should imply that the column values a) must be
> > INTEGER, b) tracking the max value seen so far.  (b) is tricky because
> > it's tempting to not require an index on that column unless it's
> > constrained to be unique (implied for a primary key), but then, if
> > there is no index then ensuring that an autoincrement value is not
> > used requires a leap of faith -- but again, if not declared unique
> > then I think it's fair to assume that it isn't required to be unique.
>
> I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so
> many SQL users will assume that it's supported.
>
> But the use of AUTOINCREMENT for key fields is really something more like
> 'always be able to generate a unique value'.  There's no reason, for
> instance, why it shouldn't work for a TEXT column or even a BLOB.  So
> support for it can be presented like the max() function: find the highest
> value so far, and use it to generate one a little higher. You could even
> expose a genuine SQLite4 aggregate function for it, and insist that anyone
> who defines a new column type supply the function to be used to generate
> the next value.
>
> This will make life simpler for those working with relational databases,
> because it'll let them do
>
> BEGIN EXCLUSIVE;
>SELECT next_key_value(id) FROM authors;<-- store value here
>SELECT next_key_value(id) FROM books;  <-- store value here
>
>-- now you already know all the values you need for the rest
>INSERT INTO authors (id,name) VALUES (***,'Zenna Henderson');
>  <-- use value here
>INSERT INTO authors (authorID,title) VALUES (***,'Pilgrimage');
>  <-- use value here
>INSERT INTO shortStories (bookID,title) VALUES (***,'Shadow on the
> Moon');   <-- use value here
> END;
>
> as an alternative to using last_insert_rowid() .
>
> 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] how to build sqlite4 (four)?

[Nico Williams]

On Thu, Jun 28, 2012 at 4:32 PM, Simon Slavin  wrote:
> I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so 
> many SQL users will assume that it's supported.

Sure.  At least for UNIQUE and PRIMARY KEY fields it's possible with
reasonable semantics.  *Assuming* b-trees.  For hash-type tables it's
slightly harder.

> But the use of AUTOINCREMENT for key fields is really something more like 
> 'always be able to generate a unique value'.  There's no reason, for 
> instance, why it shouldn't work for a TEXT column or even a BLOB.  So support 
> for it can be presented like the max() function: find the highest value so 
> far, and use it to generate one a little higher. You could even expose a 
> genuine SQLite4 aggregate function for it, and insist that anyone who defines 
> a new column type supply the function to be used to generate the next value.

Interesting idea.  max() might be slow unless it means "last for the
expression's collation".  Just seek to the end of the b-tree for the
keys for the column in question, then generate a new key that would
sort right after it.

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

Re: [sqlite] how to build sqlite4 (four)?

[Simon Slavin]

On 28 Jun 2012, at 9:05pm, Nico Williams  wrote:

> I think AUTOINCREMENT should imply that the column values a) must be
> INTEGER, b) tracking the max value seen so far.  (b) is tricky because
> it's tempting to not require an index on that column unless it's
> constrained to be unique (implied for a primary key), but then, if
> there is no index then ensuring that an autoincrement value is not
> used requires a leap of faith -- but again, if not declared unique
> then I think it's fair to assume that it isn't required to be unique.

I think SQLite4 will need to support AUTOINCREMENT for INTEGERs because so many 
SQL users will assume that it's supported.

But the use of AUTOINCREMENT for key fields is really something more like 
'always be able to generate a unique value'.  There's no reason, for instance, 
why it shouldn't work for a TEXT column or even a BLOB.  So support for it can 
be presented like the max() function: find the highest value so far, and use it 
to generate one a little higher. You could even expose a genuine SQLite4 
aggregate function for it, and insist that anyone who defines a new column type 
supply the function to be used to generate the next value.

This will make life simpler for those working with relational databases, 
because it'll let them do

BEGIN EXCLUSIVE;
SELECT next_key_value(id) FROM authors;<-- store value here
SELECT next_key_value(id) FROM books;  <-- store value here

-- now you already know all the values you need for the rest
INSERT INTO authors (id,name) VALUES (***,'Zenna Henderson');   
 <-- use value here
INSERT INTO authors (authorID,title) VALUES (***,'Pilgrimage'); 
 <-- use value here
INSERT INTO shortStories (bookID,title) VALUES (***,'Shadow on the Moon');  
 <-- use value here
END;

as an alternative to using last_insert_rowid() .

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

Re: [sqlite] JDBC Drivers--which one?

[danap]

> I have found both the Xerial and Zentus SQLite JDBC Drivers on the net.
>   This leads me to wonder are there other JDBC drivers for SQLite and if
> so what are they?  Also, what are the pros and cons of the available
> drivers?  Which one is the recommended one for use with SQLite?
>
> Thanks,
>
> LA

I think you have found them. During the research for a SQLite JDBC
solution for the MyJSQLView application these are two that turned up.
The Zentus one I think is dated, while Xerial appears to be the newer.
I have noticed that the latter has not been updated since last year.
Which leads me to wonder abouts its future.

I do known the Xerial driver allowed a pure Java mode which allows no
dlls to be installed. Meaning nothing is needed besides your Java code
and the driver. This does impose a performance hit.

In my latest testing with the Xerial JDBC I have discovered a lack of
ability to handle large imports and exports to the database for inserts
and selects. A lot of tweeking was done with batch and commit/non-commit
modes to no available. I believe the JDBC is not properly handling the
memory consumption during these process. The same code works fine with
other databases, but of course these are server based not like SQLite.

danap.


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

Re: [sqlite] how to build sqlite4 (four)?

[Black, Michael (IS)]

That's the way Oracle does it too.

http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6014.htm







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 Doug Currie [doug.cur...@gmail.com]
Sent: Thursday, June 28, 2012 3:15 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] how to build sqlite4 (four)?


On Jun 28, 2012, at 4:05 PM, Nico Williams wrote:

> It's also possibly a good idea to just not have autoincrement.  Let
> the application implement it, no?  After all, it can, including via
> triggers.

Or with PostgreSQL-style sequences

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

(and maybe SERIAL 
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL 
)

e

___
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] how to build sqlite4 (four)?

[Marc L. Allen]

>> Too many SQLite3 apps assume a rowid.  But I agree that not having a rowid 
>> unless one is defined is the
>> correct thing to do.

Darn right they do.  I'm relatively new to SQLite, but from what I've seen all 
across the web,  good use of the convenient rowed field is considered a best 
practice.  I'm surprised it's being removed.

>> I think AUTOINCREMENT should imply that the column values a) must be 
>> INTEGER, b) tracking the max value
>> seen so far.  (b) is tricky because it's tempting to not require an index on 
>> that column unless it's constrained to 
>> be unique (implied for a primary key), but then, if there is no index then 
>> ensuring that an autoincrement value 
>> is not used requires a leap of faith -- but again, if not declared unique 
>> then I think it's fair to assume that it isn't 
>> required to be unique.

Why not just keep the maximum value stored in the metadata for the column?

>> It's also possibly a good idea to just not have autoincrement.  Let the 
>> application implement it, no?  After all, it
>> can, including via triggers.

Gah!  Remove functionality?  That also forces the application to maintain the 
maximum seen, requiring an index or a secondary table.  You're just pushing the 
problem onto the user and I don't think that's the place for it.

Marc 

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

Re: [sqlite] how to build sqlite4 (four)?

[Doug Currie]

On Jun 28, 2012, at 4:05 PM, Nico Williams wrote:

> It's also possibly a good idea to just not have autoincrement.  Let
> the application implement it, no?  After all, it can, including via
> triggers.

Or with PostgreSQL-style sequences

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

(and maybe SERIAL 
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-SERIAL 
)

e

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

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 4:09 PM, Alexey Pechnikov wrote:

> Will be covering indices on views available as replacement of materialized
> views?
>

No.  I don't know how to do that.


>
> 2012/6/28 Simon Slavin 
>
> > First, the important bit:
> >
> > "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
> not
> > going away."
> >
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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
>



-- 
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] SQLite4 (don't scream)

[Alexey Pechnikov]

Will be covering indices on views available as replacement of materialized
views?

2012/6/28 Simon Slavin 

> First, the important bit:
>
> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not
> going away."
>
> Now the URL:
>
> 
>
> Just thought some people might enjoy reading and thinking about it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] how to build sqlite4 (four)?

[Nico Williams]

On Thu, Jun 28, 2012 at 2:48 PM, Richard Hipp  wrote:
> Note that SQLite4 does not have a rowid (at last not always) so the whole
> concept of autoincrement will need to be revisited.  I don't think the old
> SQLite3 way of doing autoincrement makes sense any more.  Not good sense
> anyhow.  We (the community) needs to talk more about what ought to be done
> here.

A change so big that it alone justifies the new major version number.

Too many SQLite3 apps assume a rowid.  But I agree that not having a
rowid unless one is defined is the correct thing to do.

I think AUTOINCREMENT should imply that the column values a) must be
INTEGER, b) tracking the max value seen so far.  (b) is tricky because
it's tempting to not require an index on that column unless it's
constrained to be unique (implied for a primary key), but then, if
there is no index then ensuring that an autoincrement value is not
used requires a leap of faith -- but again, if not declared unique
then I think it's fair to assume that it isn't required to be unique.

It's also possibly a good idea to just not have autoincrement.  Let
the application implement it, no?  After all, it can, including via
triggers.

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

[sqlite] question about sqlite4_open() flags

[Stephan Beal]

(i hope i'm not flooding the list too much with v4. If this should move to
the dev list or gmail, just point me in the right direction.)

i found that there is no open_v2() in v4, and the signature has changed a
small amount. My code used to look like:

rc = flags
? sqlite3_open_v2( nameBuf, >db, flags, NULL )
: sqlite3_open( nameBuf, >db );

but now looks like:

rc = sqlite4_open( NULL, nameBuf, >db, /*flags, */0 );

note the commented-out "flags" value (which is optionally set to 0 or some
value suitable for open_v2(), e.g. SQLITE_OPEN_READWRITE and friends.

Is un-commenting that the proper way to use the flags now? It wasn't clear
to me from the docs in sqlite4.h.

If that's not correct, how do we now (or can we at all) apply
SQLITE_OPEN_xxx?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:23 PM, Stephan Beal  wrote:

> i'm racing to be the first to adapt his db access abstraction layer to
> sqlite4 and i've hit my first stumbling block...
>

i hope this qualifies as "first" ;)

http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/timeline?r=sqlite4

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 9:48 PM, Richard Hipp  wrote:

> Note that SQLite4 does not have a rowid (at last not always) so the whole
> concept of autoincrement will need to be revisited.  I don't think the old
> SQLite3 way of doing autoincrement makes sense any more.  Not good
> sense anyhow.  We (the community) needs to talk more about what ought to be
> done here.


An off-the-cuff idea: maybe register an auto-increment callback function on
a per-db basis, with the library providing a default impl which behaves
more or less like v3/mysql? i have no idea if that architecturally makes
sense, though.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Richard Hipp]

On Thu, Jun 28, 2012 at 3:22 PM, Stephan Beal  wrote:

> A follow-up:
>
> i've got it linking now, but i am seeing different behaviour between
> autoincrement fields and sqliteX_last_insert_rowid(). v4 doesn't allow the
> following SQL:
>

Autoincrement isn't working yet.  I discovered that too, when I tried the
Fossil port.  I started to work on it yesterday, but keep getting
sidetracked.

Note that SQLite4 does not have a rowid (at last not always) so the whole
concept of autoincrement will need to be revisited.  I don't think the old
SQLite3 way of doing autoincrement makes sense any more.  Not good sense
anyhow.  We (the community) needs to talk more about what ought to be done
here.


>
> and when i hard-code a row ID into my test SQL it's last_insert_rowid() is
> returning <=0 (i don't yet know which - an assertion was triggered for that
> condition).
>
> i haven't yet narrowed down the problem, but wanted to point it out. If
> it's a known problem, let me know and i'll stop digging.
>
> My SQL:
>
> stephan@tiny:~/cvs/fossil/cpdo$ echo '.dump' | sqlite4 4.db
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE t(vInt INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,vDbl DOUBLE
> DEFAULT NULL,vStr VARCHAR(64) DEFAULT NULL,vTs CHAR(20) DEFAULT
> CURRENT_TIMESTAMP);
> COMMIT;
>
> that same code works "as expected" in v3.
>
> Just some arbitrary test output which might or might not be of interest...
>
> stephan@tiny:~/cvs/fossil/cpdo$ ./certify -d 'sqlite3:3.db'
> certify.cpp:605:main(): Testing against DSN [sqlite3:3.db]...
> certify.cpp:465:test_driver(): Starting tests against [sqlite3:3.db]...
> certify.cpp:323:run_select(): Done looping: row count=11
> certify.cpp:436:test_step_each(): step_each() appears to work. 11 row(s)
> step()'d.
> certify.cpp:445:test_step_each(): step_each(reference) appears to work. 11
> row(s) step()'d.
> vInt vDbl vStr vTs
> 1 1132.37 Hi, world #1 2012-06-28 19:21:23
> 2 2264.74 Hi, world #2 2012-06-28 19:21:23
> 3 3397.11 Hi, world #3 2012-06-28 19:21:23
> 4 4529.48 Hi, world #4 2012-06-28 19:21:24
> 5 42.24 bound by name 2012-06-28 19:21:24
> 6 42.24 bound by name 2012-06-28 19:21:24
> 7 42.24 NULL 2012-06-28 19:21:24
> 8 42.24 bound by name 2012-06-28 19:21:24
> 1971 12.08 Hi again from chainer::bind(). 2012-06-28 19:21:24
> 1972 11.21 Hi from chainer::bind(). 2012-06-28 19:21:24
> 1975 11.21 read back string: 2012-06-28 19:21:24
> certify.cpp:456:test_step_each(): step_each(const reference) appears to
> work.
> certify.cpp:479:test_driver(): Qualified identifier: [foo]
> certify.cpp:631:main(): Done! rc=0
>
> stephan@tiny:~/cvs/fossil/cpdo$ ./certify -d 'sqlite4:4.db'
> certify.cpp:605:main(): Testing against DSN [sqlite4:4.db]...
> certify.cpp:465:test_driver(): Starting tests against [sqlite4:4.db]...
> certify.cpp:610:main(): DB EXCEPTION: code=19: t.vInt may not be NULL
> certify.cpp:631:main(): Done! rc=1
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 9:22 PM, Stephan Beal  wrote:

> i haven't yet narrowed down the problem, but wanted to point it out. If
> it's a known problem, let me know and i'll stop digging.
>

A couple more details:

schema:

CREATE TABLE t(
vInt INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
vDbl DOUBLE DEFAULT NULL,
vStr VARCHAR(64) DEFAULT NULL,
vTs CHAR(20) DEFAULT CURRENT_TIMESTAMP
);

insert which fails in v4 but not in v3:

INSERT INTO t (vInt,vDbl,vStr) VALUES(NULL,?,?)

If i refactor that to explicitly set vInt to some (unique) value then the
insert passes but last_insert_rowid() is returning 0. If i remove my rowid
assertion, the inserts can be shown to have succeeded:

stephan@tiny:~/cvs/fossil/cpdo$ echo '.dump' | sqlite4 4.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(vInt INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,vDbl DOUBLE
DEFAULT NULL,vStr VARCHAR(64) DEFAULT NULL,vTs CHAR(20) DEFAULT
CURRENT_TIMESTAMP);
INSERT INTO "t" VALUES(1,1132.37,'Hi, world #1','2012-06-28 19:37:44');
INSERT INTO "t" VALUES(2,2264.74,'Hi, world #2','2012-06-28 19:37:44');
INSERT INTO "t" VALUES(3,3397.11,'Hi, world #3','2012-06-28 19:37:44');
INSERT INTO "t" VALUES(4,4529.48,'Hi, world #4','2012-06-28 19:37:44');
COMMIT;


But sqlite4_last_insert_rowid() returns 0 in each case.

If i change my table def to remove NOT NULL from vInt and change my INSERT
such that i bind NULL to vInt (expecting autoincrement behaviour), i get a
"may not be null" constraint violation.

i hope this has informed more than confused...

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

A follow-up:

i've got it linking now, but i am seeing different behaviour between
autoincrement fields and sqliteX_last_insert_rowid(). v4 doesn't allow the
following SQL:

and when i hard-code a row ID into my test SQL it's last_insert_rowid() is
returning <=0 (i don't yet know which - an assertion was triggered for that
condition).

i haven't yet narrowed down the problem, but wanted to point it out. If
it's a known problem, let me know and i'll stop digging.

My SQL:

stephan@tiny:~/cvs/fossil/cpdo$ echo '.dump' | sqlite4 4.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(vInt INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,vDbl DOUBLE
DEFAULT NULL,vStr VARCHAR(64) DEFAULT NULL,vTs CHAR(20) DEFAULT
CURRENT_TIMESTAMP);
COMMIT;

that same code works "as expected" in v3.

Just some arbitrary test output which might or might not be of interest...

stephan@tiny:~/cvs/fossil/cpdo$ ./certify -d 'sqlite3:3.db'
certify.cpp:605:main(): Testing against DSN [sqlite3:3.db]...
certify.cpp:465:test_driver(): Starting tests against [sqlite3:3.db]...
certify.cpp:323:run_select(): Done looping: row count=11
certify.cpp:436:test_step_each(): step_each() appears to work. 11 row(s)
step()'d.
certify.cpp:445:test_step_each(): step_each(reference) appears to work. 11
row(s) step()'d.
vInt vDbl vStr vTs
1 1132.37 Hi, world #1 2012-06-28 19:21:23
2 2264.74 Hi, world #2 2012-06-28 19:21:23
3 3397.11 Hi, world #3 2012-06-28 19:21:23
4 4529.48 Hi, world #4 2012-06-28 19:21:24
5 42.24 bound by name 2012-06-28 19:21:24
6 42.24 bound by name 2012-06-28 19:21:24
7 42.24 NULL 2012-06-28 19:21:24
8 42.24 bound by name 2012-06-28 19:21:24
1971 12.08 Hi again from chainer::bind(). 2012-06-28 19:21:24
1972 11.21 Hi from chainer::bind(). 2012-06-28 19:21:24
1975 11.21 read back string: 2012-06-28 19:21:24
certify.cpp:456:test_step_each(): step_each(const reference) appears to
work.
certify.cpp:479:test_driver(): Qualified identifier: [foo]
certify.cpp:631:main(): Done! rc=0

stephan@tiny:~/cvs/fossil/cpdo$ ./certify -d 'sqlite4:4.db'
certify.cpp:605:main(): Testing against DSN [sqlite4:4.db]...
certify.cpp:465:test_driver(): Starting tests against [sqlite4:4.db]...
certify.cpp:610:main(): DB EXCEPTION: code=19: t.vInt may not be NULL
certify.cpp:631:main(): Done! rc=1


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 8:35 PM, Stephan Beal  wrote:

> http://localhost:8080/artifact/3a695fc9bb272a4f5ca0d1a8cf540d61b52ff534
>
> (i strongly suspect that link will work for you ;)
>
> contains Table::tnum.
>

That's a lie - tnum is in Index in that version.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 8:25 PM, Richard Hipp  wrote:

> My makefile uses gcc.  Why don't you change yours to gcc too, to see if
> that helps.
>

Strangely enough, no. i can't quite explain this because i'm on trunk and:

http://localhost:8080/artifact/3a695fc9bb272a4f5ca0d1a8cf540d61b52ff534

(i strongly suspect that link will work for you ;)

contains Table::tnum. My local sqliteInt.h, however, does not (it is
documented but the member is missing or was moved to Index::tnum).

After hacking my makefile a bit:

stephan@tiny:~/cvs/fossil/sqlite4$ clm
rm -f *.o sqlite4 sqlite4.exe libsqlite4.a sqlite4.h opcodes.*
rm -f lemon lemon.exe lempar.c parse.* sqlite*.tar.gz
rm -f mkkeywordhash mkkeywordhash.exe keywordhash.h
rm -f
rm -f *.da *.bb *.bbg gmon.out
rm -rf tsrc target_source
rm -f testloadext.dll libtestloadext.so
rm -f amalgamation-testfixture amalgamation-testfixture.exe
rm -f fts3-testfixture fts3-testfixture.exe
rm -f testfixture testfixture.exe
rm -f threadtest3 threadtest3.exe
rm -f sqlite4.c fts?amal.c tclsqlite4.c
rm -f sqlite4_analyzer sqlite4_analyzer.exe sqlite4_analyzer.c
stephan@tiny:~/cvs/fossil/sqlite4$ make BCC='gcc -g' TCC='gcc -Wall'
tclsh ./tool/mksqlite4h.tcl . >sqlite4.h
gcc -g -o mkkeywordhash -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
-DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_NO_SYNC=1
./tool/mkkeywordhash.c
./mkkeywordhash >keywordhash.h
gcc -g -o lemon ./tool/lemon.c
cp ./src/lempar.c .
cp ./src/parse.y .
rm -f parse.h
./lemon -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
-DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_NO_SYNC=1 parse.y
mv parse.h parse.h.temp
awk -f ./tool/addopcodes.awk parse.h.temp >parse.h
cat parse.h ./src/vdbe.c | \
awk -f ./tool/mkopcodeh.awk >opcodes.h
gcc -Wall -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
-DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_NO_SYNC=1 -I. -I./src -I.
 -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/alter.c
In file included from src/sqliteInt.h:315:0,
 from src/alter.c:15:
./sqlite4.h:3446:1: warning: "/*" within comment [-Wcomment]
gcc -Wall -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
-DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_NO_SYNC=1 -I. -I./src -I.
 -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/analyze.c
In file included from src/sqliteInt.h:315:0,
 from src/analyze.c:117:
./sqlite4.h:3446:1: warning: "/*" within comment [-Wcomment]
src/analyze.c: In function 'openStatTable':
src/analyze.c:177:24: error: 'Parse' has no member named 'regRoot'
src/analyze.c:183:23: error: 'Table' has no member named 'tnum'
src/analyze.c: In function 'analyzeOneTable':
src/analyze.c:472:11: error: 'Table' has no member named 'tnum'
src/analyze.c:679:52: error: 'Table' has no member named 'tnum'
make: *** [analyze.o] Error 1



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Richard Hipp]

My makefile uses gcc.  Why don't you change yours to gcc too, to see if
that helps.

On Thu, Jun 28, 2012 at 2:14 PM, Stephan Beal  wrote:

> On Thu, Jun 28, 2012 at 7:35 PM, Richard Hipp  wrote:
>
> > My makefile has the following:
> >
>
> Thanks for the tips. My initial problem was that sqlite4.h was 0 bytes due
> to a failed build before i had installed tclsh and clang. That of course
> caused grief. Removing it and rebuilding got me further. Now i'm at missing
> members:
>
> stephan@tiny:~/cvs/fossil/sqlite4$ make
> tclsh ./tool/mksqlite4h.tcl . >sqlite4.h
> clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
> -DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
> -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
> -I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/alter.c
> In file included from src/alter.c:15:
> In file included from ./src/sqliteInt.h:315:
> ./sqlite4.h:3446:1: warning: '/*' within block comment [-Wcomment]
> /*
> ^
> 1 warning generated.
> clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
> -DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
> -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
> -I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/analyze.c
> In file included from src/analyze.c:117:
> In file included from ./src/sqliteInt.h:315:
> ./sqlite4.h:3446:1: warning: '/*' within block comment [-Wcomment]
> /*
> ^
> src/analyze.c:177:26: error: no member named 'regRoot' in 'struct Parse'
>  aRoot[i] = pParse->regRoot;
> ~~  ^
> src/analyze.c:183:25: error: no member named 'tnum' in 'struct Table'
>  aRoot[i] = pStat->tnum;
> ~  ^
> src/analyze.c:472:13: error: no member named 'tnum' in 'struct Table'
>  if( pTab->tnum==0 ){
>    ^
> src/analyze.c:679:54: error: no member named 'tnum' in 'struct Table'
>sqlite4VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
>     ^
> 1 warning and 4 errors generated.
> make: *** [analyze.o] Error 1
>
> i tried a clean/rebuild to ensure(?) that any other stale/mis-generated
> files weren't causing the problem, but the results are the same.
>
> stephan@tiny:~/cvs/fossil/sqlite4$ f info
> project-name: SQLite4
> repository:   /home/stephan/cvs/fossil/sqlite4/../sqlite4.fsl
> local-root:   /home/stephan/cvs/fossil/sqlite4/
> project-code: 3cfe49689a940b1ecc726558ce7f7d295ab6cadd
> checkout: 262705a2e8ffdbd0e2647167a4ac5445ad8f2aa7 2012-06-28 17:58:26
> UTC
> parent:   f105bf9ee9c1a7b304a3ee7575339e0178aa993e 2012-06-28 14:57:39
> UTC
> tags: trunk
> comment:  In lsmview.tcl, how additional information about levels on
> mouse-
>  over. (user: drh)
>
>
> PS: i find it interesting that you chose clang over cc or gcc as the
> default compiler. Not complaining, just observing.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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] Example database with lots of "types"?

[danap]

> I'm working on getting the Mac version of the SQLite ODCB connector fully
> functional. I'm having some problems getting examples of lots of
different data
> types - for instance, my northwind copy has a decimal stored as a varchar.
>
> Does anyone have a small test DB they would be willing to part with so I
could see
> lots of different data types and what happens when they come through the
adaptor?

During the development for MyJSQLView for SQLite we created a test tables
that includes all the data types supported in SQLite. As Simmon indicated
there is not much there. These test scripts and a db file are in the
download for the application. If you do not want to download, then here
is links to the script and db. Enjoy design dress for Princess Leia.

Dana M. Proctor
MyJSQLView Project Manager
http://dandymadeproductions.com/projects/MyJSQLView/temp/




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

Re: [sqlite] Regarding the subclassing model of sqlite4

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:36 PM, Stephan Beal  wrote:

> In the off chance that i might influence such a design decision in
> sqlite4, here's the article:
>
> http://wanderinghorse.net/computing/papers/DoingOOInC.pdf
>
>
And to put my money where my mouth is, so to speak, i'd volunteer to do
such a port for v4 (for v3 it's obviously out of the question).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:35 PM, Richard Hipp  wrote:

> My makefile has the following:
>

Thanks for the tips. My initial problem was that sqlite4.h was 0 bytes due
to a failed build before i had installed tclsh and clang. That of course
caused grief. Removing it and rebuilding got me further. Now i'm at missing
members:

stephan@tiny:~/cvs/fossil/sqlite4$ make
tclsh ./tool/mksqlite4h.tcl . >sqlite4.h
clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
-DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
-DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
-I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/alter.c
In file included from src/alter.c:15:
In file included from ./src/sqliteInt.h:315:
./sqlite4.h:3446:1: warning: '/*' within block comment [-Wcomment]
/*
^
1 warning generated.
clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
-DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
-DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
-I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/analyze.c
In file included from src/analyze.c:117:
In file included from ./src/sqliteInt.h:315:
./sqlite4.h:3446:1: warning: '/*' within block comment [-Wcomment]
/*
^
src/analyze.c:177:26: error: no member named 'regRoot' in 'struct Parse'
  aRoot[i] = pParse->regRoot;
 ~~  ^
src/analyze.c:183:25: error: no member named 'tnum' in 'struct Table'
  aRoot[i] = pStat->tnum;
 ~  ^
src/analyze.c:472:13: error: no member named 'tnum' in 'struct Table'
  if( pTab->tnum==0 ){
    ^
src/analyze.c:679:54: error: no member named 'tnum' in 'struct Table'
sqlite4VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
     ^
1 warning and 4 errors generated.
make: *** [analyze.o] Error 1

i tried a clean/rebuild to ensure(?) that any other stale/mis-generated
files weren't causing the problem, but the results are the same.

stephan@tiny:~/cvs/fossil/sqlite4$ f info
project-name: SQLite4
repository:   /home/stephan/cvs/fossil/sqlite4/../sqlite4.fsl
local-root:   /home/stephan/cvs/fossil/sqlite4/
project-code: 3cfe49689a940b1ecc726558ce7f7d295ab6cadd
checkout: 262705a2e8ffdbd0e2647167a4ac5445ad8f2aa7 2012-06-28 17:58:26
UTC
parent:   f105bf9ee9c1a7b304a3ee7575339e0178aa993e 2012-06-28 14:57:39
UTC
tags: trunk
comment:  In lsmview.tcl, how additional information about levels on
mouse-
  over. (user: drh)


PS: i find it interesting that you chose clang over cc or gcc as the
default compiler. Not complaining, just observing.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Simon Slavin]

On 28 Jun 2012, at 6:04pm, Roger Binns  wrote:

> http://news.ycombinator.com/item?id=4168645

LOL.  Those Hacker News guys are hardcore.  Make some of my mailing lists look 
almost civil.

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

[sqlite] Regarding the subclassing model of sqlite4

[Stephan Beal]

Hello again,

(@non-developers: this post will not interest you in the slightest. @devs:
it may or may not interest you in the slightest.)

Regarding the subclassing model of sqlite4, where one subclasses by
creating an over-sized struct and padding it with their fields...

About a year ago i wrote a detailed article which demonstrates an alternate
approach to OO in C which is, IMO, an improvement over the more
conventional approach used by sqlite. The current model coincidentally runs
on all modern compilers (apparently) but officially has undefined
behaviour. C does not require that the pointer address will be the same for
both types when a base type pointer is cast to a derived type this way (at
least that's my understanding, and i apologize for not having a link handy).

In the off chance that i might influence such a design decision in sqlite4,
here's the article:

http://wanderinghorse.net/computing/papers/DoingOOInC.pdf

Happy Hacking!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to build sqlite4 (four)?

[Richard Hipp]

On Thu, Jun 28, 2012 at 1:23 PM, Stephan Beal  wrote:

> Hiho!
>
> i'm racing to be the first to adapt his db access abstraction layer to
> sqlite4 and i've hit my first stumbling block...
>

Everything is still pretty makefile-touchy.  Remember, this is like
pre-alpha code.  It works, but just barely.  And things are changing
rapidly.

My makefile has the following:

OPTS += -DLSM_MUTEX_NONE
OPTS += -DSQLITE_DEBUG=1 -DLSM_DEBUG=1
OPTS += -DHAVE_GMTIME_R
OPTS += -DHAVE_LOCALTIME_R
OPTS += -DHAVE_MALLOC_USABLE_SIZE
OPTS += -DHAVE_USLEEP
OPTS += -DSQLITE_MEMDEBUG=1
OPTS += -DSQLITE_NO_SYNC=1 -DLSM_NO_SYNC=1
OPTS += -DSQLITE_OMIT_ANALYZE
OPTS += -DSQLITE_OMIT_AUTOMATIC_INDEX
OPTS += -DSQLITE_OMIT_BTREECOUNT
OPTS += -DSQLITE_OMIT_VIRTUALTABLE=1
OPTS += -DSQLITE_OMIT_XFER_OPT
OPTS += -DSQLITE_THREADSAFE=0

I'm not sure how much of the above is necessary for the build.  But on my
box, "make sqlite4.c" and "make test" both work.


>
> stephan@tiny:~/cvs/fossil/sqlite4$ make
> clang -g -o mkkeywordhash -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
> -DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
> -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1
> ./tool/mkkeywordhash.c
> ./mkkeywordhash >keywordhash.h
> clang -g -o lemon ./tool/lemon.c
> cp ./src/lempar.c .
> cp ./src/parse.y .
> rm -f parse.h
> ./lemon -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
> -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_ENABLE_FTS4
> -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 parse.y
> mv parse.h parse.h.temp
> awk -f ./tool/addopcodes.awk parse.h.temp >parse.h
> cat parse.h ./src/vdbe.c | \
> awk -f ./tool/mkopcodeh.awk >opcodes.h
> clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
> -DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
> -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
> -I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/alter.c
> In file included from src/alter.c:15:
> ./src/sqliteInt.h:445:9: error: unknown type name 'sqlite_int64'
> typedef sqlite_int64 i64;  /* 8-byte signed integer */
>^
> ./src/sqliteInt.h:446:9: error: unknown type name 'sqlite_uint64'
> typedef sqlite_uint64 u64; /* 8-byte unsigned integer */
>^
> In file included from src/alter.c:15:
> In file included from ./src/sqliteInt.h:628:
> ./src/vdbe.h:171:25: error: unknown type name 'sqlite4'
> Vdbe *sqlite4VdbeCreate(sqlite4*);
>^
> ./src/vdbe.h:192:30: error: unknown type name 'sqlite4'
> void sqlite4VdbeDeleteObject(sqlite4*,Vdbe*);
> ^
> ./src/vdbe.h:207:1: error: unknown type name 'sqlite4'
> sqlite4 *sqlite4VdbeDb(Vdbe*);
> ^
> ./src/vdbe.h:211:1: error: unknown type name 'sqlite4_value'
> sqlite4_value *sqlite4VdbeGetValue(Vdbe*, int, u8);
> ^
> In file included from src/alter.c:15:
> In file included from ./src/sqliteInt.h:629:
> ./src/storage.h:131:9: error: unknown type name 'sqlite4_kvsize'
> typedef sqlite4_kvsize KVSize;
>^
> ./src/storage.h:134:27: error: must use 'struct' tag to refer to type
> 'sqlite4_env'
> int sqlite4KVStoreOpenMem(sqlite4_env*, KVStore**, const char *, unsigned);
>  ^
>  struct
> ./src/storage.h:135:27: error: must use 'struct' tag to refer to type
> 'sqlite4_env'
> int sqlite4KVStoreOpenLsm(sqlite4_env*, KVStore**, const char *, unsigned);
>  ^
>  struct
> ./src/storage.h:137:3: error: unknown type name 'sqlite4'
>  sqlite4*,
>  ^
> ./src/storage.h:176:27: error: unknown type name 'sqlite4'
> int sqlite4KVStorePutMeta(sqlite4*, KVStore *p, int, int, unsigned int*);
>  ^
> In file included from src/alter.c:15:
> In file included from ./src/sqliteInt.h:631:
> ./src/os.h:78:19: error: must use 'struct' tag to refer to type
> 'sqlite4_env'
> int sqlite4OsInit(sqlite4_env*);
>  ^
>  struct
> ./src/os.h:79:25: error: must use 'struct' tag to refer to type
> 'sqlite4_env'
> int sqlite4OsRandomness(sqlite4_env*, int, unsigned char*);
>^
>struct
> ./src/os.h:80:26: error: must use 'struct' tag to refer to type
> 'sqlite4_env'
> int sqlite4OsCurrentTime(sqlite4_env*, sqlite4_uint64*);
> ^
> struct
> ./src/os.h:80:40: error: unknown type name 'sqlite4_uint64'
> int sqlite4OsCurrentTime(sqlite4_env*, sqlite4_uint64*);
>   ^
> In file included from src/alter.c:15:
> ./src/sqliteInt.h:662:17: error: unknown type name 'sqlite4_context'
>  void (*xFunc)(sqlite4_context*,int,sqlite4_value**); /* Regular function
> */
>^
> ./src/sqliteInt.h:662:38: error: unknown type name 'sqlite4_value'
>  void (*xFunc)(sqlite4_context*,int,sqlite4_value**); /* Regular function
> */
> ^
> ./src/sqliteInt.h:663:17: 

Re: [sqlite] how to build sqlite4 (four)?

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:23 PM, Stephan Beal  wrote:

> stephan@tiny:~/cvs/fossil/sqlite4$ make
> clang -g -o mkkeywordhash -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -
>

Sorry, should have added:

stephan@tiny:~/cvs/fossil/sqlite4$ clang --version
clang version 2.9 (tags/RELEASE_29/final)
Target: i386-pc-linux-gnu
Thread model: posix

Ubuntu 11.10.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to build sqlite4 (four)?

[Stephan Beal]

Hiho!

i'm racing to be the first to adapt his db access abstraction layer to
sqlite4 and i've hit my first stumbling block...

stephan@tiny:~/cvs/fossil/sqlite4$ make
clang -g -o mkkeywordhash -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
-DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
-DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1
./tool/mkkeywordhash.c
./mkkeywordhash >keywordhash.h
clang -g -o lemon ./tool/lemon.c
cp ./src/lempar.c .
cp ./src/parse.y .
rm -f parse.h
./lemon -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R -DHAVE_LOCALTIME_R
-DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP -DSQLITE_ENABLE_FTS4
-DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 parse.y
mv parse.h parse.h.temp
awk -f ./tool/addopcodes.awk parse.h.temp >parse.h
cat parse.h ./src/vdbe.c | \
awk -f ./tool/mkopcodeh.awk >opcodes.h
clang -g -O0 -Wall -fstrict-aliasing -DSQLITE_DEBUG=1 -DHAVE_GMTIME_R
-DHAVE_LOCALTIME_R -DHAVE_MALLOC_USABLE_SIZE -DHAVE_USLEEP
-DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE -DSQLITE_NO_SYNC=1 -I. -I./src
-I.  -I./ext/rtree -I./ext/icu -I./ext/fts3 -I./ext/async -c src/alter.c
In file included from src/alter.c:15:
./src/sqliteInt.h:445:9: error: unknown type name 'sqlite_int64'
typedef sqlite_int64 i64;  /* 8-byte signed integer */
^
./src/sqliteInt.h:446:9: error: unknown type name 'sqlite_uint64'
typedef sqlite_uint64 u64; /* 8-byte unsigned integer */
^
In file included from src/alter.c:15:
In file included from ./src/sqliteInt.h:628:
./src/vdbe.h:171:25: error: unknown type name 'sqlite4'
Vdbe *sqlite4VdbeCreate(sqlite4*);
^
./src/vdbe.h:192:30: error: unknown type name 'sqlite4'
void sqlite4VdbeDeleteObject(sqlite4*,Vdbe*);
 ^
./src/vdbe.h:207:1: error: unknown type name 'sqlite4'
sqlite4 *sqlite4VdbeDb(Vdbe*);
^
./src/vdbe.h:211:1: error: unknown type name 'sqlite4_value'
sqlite4_value *sqlite4VdbeGetValue(Vdbe*, int, u8);
^
In file included from src/alter.c:15:
In file included from ./src/sqliteInt.h:629:
./src/storage.h:131:9: error: unknown type name 'sqlite4_kvsize'
typedef sqlite4_kvsize KVSize;
^
./src/storage.h:134:27: error: must use 'struct' tag to refer to type
'sqlite4_env'
int sqlite4KVStoreOpenMem(sqlite4_env*, KVStore**, const char *, unsigned);
  ^
  struct
./src/storage.h:135:27: error: must use 'struct' tag to refer to type
'sqlite4_env'
int sqlite4KVStoreOpenLsm(sqlite4_env*, KVStore**, const char *, unsigned);
  ^
  struct
./src/storage.h:137:3: error: unknown type name 'sqlite4'
  sqlite4*,
  ^
./src/storage.h:176:27: error: unknown type name 'sqlite4'
int sqlite4KVStorePutMeta(sqlite4*, KVStore *p, int, int, unsigned int*);
  ^
In file included from src/alter.c:15:
In file included from ./src/sqliteInt.h:631:
./src/os.h:78:19: error: must use 'struct' tag to refer to type
'sqlite4_env'
int sqlite4OsInit(sqlite4_env*);
  ^
  struct
./src/os.h:79:25: error: must use 'struct' tag to refer to type
'sqlite4_env'
int sqlite4OsRandomness(sqlite4_env*, int, unsigned char*);
^
struct
./src/os.h:80:26: error: must use 'struct' tag to refer to type
'sqlite4_env'
int sqlite4OsCurrentTime(sqlite4_env*, sqlite4_uint64*);
 ^
 struct
./src/os.h:80:40: error: unknown type name 'sqlite4_uint64'
int sqlite4OsCurrentTime(sqlite4_env*, sqlite4_uint64*);
   ^
In file included from src/alter.c:15:
./src/sqliteInt.h:662:17: error: unknown type name 'sqlite4_context'
  void (*xFunc)(sqlite4_context*,int,sqlite4_value**); /* Regular function
*/
^
./src/sqliteInt.h:662:38: error: unknown type name 'sqlite4_value'
  void (*xFunc)(sqlite4_context*,int,sqlite4_value**); /* Regular function
*/
 ^
./src/sqliteInt.h:663:17: error: unknown type name 'sqlite4_context'
  void (*xStep)(sqlite4_context*,int,sqlite4_value**); /* Aggregate step */
^
./src/sqliteInt.h:663:38: error: unknown type name 'sqlite4_value'
  void (*xStep)(sqlite4_context*,int,sqlite4_value**); /* Aggregate step */
 ^
fatal error: too many errors emitted, stopping now [-ferror-limit=]
20 errors generated.
make: *** [alter.o] Error 1


What do i need to do?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 7:09 PM, E. Timothy Uy  wrote:

> That's kind of cool, but how about $twentyseven!
>

$c1...$c27 (c==column)?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[E. Timothy Uy]

That's kind of cool, but how about $twentyseven!


> On Thu, Jun 28, 2012 at 12:57 PM, Robert Myers 
> wrote:
>
> > Aghhh (just kidding)
> >
> > One request for a change - make bind and column start with the same
> index.
> > I always have to look up which one is zero based and which one is one
> based.
> >
>
> Right now, porting an app from SQLite3 to SQLite4 is mostly a global
> search/replace of "sqlite3"->"sqlite4".  (Maybe a little more than
> thatbut not
> much.)  Changing the index base for sqlite4_bind would really
> complicate matters.
>
> Recommend you not use sqlite4_bind() directly, but rather us named
> parameters (ex: "@one", ":two", "$three") with
> sqlite4_bind_parameter_index():
>
>sqlite4_bind_int(pStmt, sqlite4_bind_parameter_index(pStmt, "$three"),
> 3);
>
> That way, you never have to worry about miscounting the "?" parameters in
> your query and being off-by-one in your bindings.
>
>
> >
> > Rob
> >
> >
> > On 6/28/2012 10:57 AM, Simon Slavin wrote:
> >
> >> First, the important bit:
> >>
> >> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
> >> not going away."
> >>
> >> Now the URL:
> >>
> >>  http://www.sqlite.org/src4/doc/trunk/www/design.wiki>
> >> >
> >>
> >> Just thought some people might enjoy reading and thinking about it.
> >>
> >> Simon.
> >> __**_
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> 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<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 12:57 PM, Robert Myers  wrote:

> Aghhh (just kidding)
>
> One request for a change - make bind and column start with the same index.
> I always have to look up which one is zero based and which one is one based.
>

Right now, porting an app from SQLite3 to SQLite4 is mostly a global
search/replace of "sqlite3"->"sqlite4".  (Maybe a little more than
thatbut not
much.)  Changing the index base for sqlite4_bind would really
complicate matters.

Recommend you not use sqlite4_bind() directly, but rather us named
parameters (ex: "@one", ":two", "$three") with
sqlite4_bind_parameter_index():

sqlite4_bind_int(pStmt, sqlite4_bind_parameter_index(pStmt, "$three"),
3);

That way, you never have to worry about miscounting the "?" parameters in
your query and being off-by-one in your bindings.


>
> Rob
>
>
> On 6/28/2012 10:57 AM, Simon Slavin wrote:
>
>> First, the important bit:
>>
>> "SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is
>> not going away."
>>
>> Now the URL:
>>
>> 
>> >
>>
>> Just thought some people might enjoy reading and thinking about it.
>>
>> 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
>



-- 
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] SQLite4 (don't scream)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/12 08:57, Simon Slavin wrote:
> Just thought some people might enjoy reading and thinking about it.

There has also been discussion on Hacker News and Reddit:

 http://news.ycombinator.com/item?id=4168645

 http://www.reddit.com/r/programming/comments/vp9uh/sqlite4_the_design/

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/sjp0ACgkQmOOfHg372QR7HACghy3WA1rXUlXtgUkJMncEGFPa
SwsAnA3T1tpMN9QsdXqUq7W/4961Dgmo
=JzxV
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Ryan Johnson]

On 28/06/2012 12:30 PM, Cory Nelson wrote:

On Thu, Jun 28, 2012 at 11:20 AM, Stephan Beal wrote:


On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
wrote:


Now the URL:



Just thought some people might enjoy reading and thinking about it.


FWIW, my 0.02 Euros regarding this line:

"SQLite4 makes use of standard data types such as size_t, int64_t,
uint64_t,
and others."


size_t does not have a specified size and causes all sorts of grief in
porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
fixed-size integers defined in inttypes.h, and not size_t. There is of
course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
there are free drop-in replacements available here:
http://code.google.com/p/msinttypes/


stdint was made available in VC++ 2010, though inttypes is still missing.
Probably not an issue -- I'm not sure how a public API would need inttypes
anyway. Also, perhaps you are seeing size_t be misused. A blanket "please
don't use" is nonsense.
I tend to agree with Simon on this. size_t is only useful when 
expressing the amount of memory something might involve, when 
constrained only by the size of the current machine's address space. 
Major examples would include sizeof(), malloc(), and strlen(). C++ 
std::vector::size() is an anti-pattern, since the number of elements in 
a vector is not a number of bytes. However, size_t isn't particularly 
helpful even when used "correctly." Any 32-bit portable code can safely 
use uint32_t everywhere: it's equivalent to size_t on 32-bits, and it 
will silently convert to size_t whenever needed on a 64-bit machine; if 
the code is 64-bit only -- most likely to allow for allocations larger 
than 4GB -- then uint64_t can be used everywhere instead.


$0.02
Ryan


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

Re: [sqlite] SQLite4 (don't scream)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 28/06/12 09:20, Stephan Beal wrote:
> size_t does not have a specified size and causes all sorts of grief in 
> porting i/o-based APIs between 32/64 bits,

I have been one of the people complaining loudest about not using types
like size_t.  However the complaints are not directed to the internals of
SQLite where it is absolutely appropriate to use an explicitly sized type
for cases where a particular size is needed.

The area where it does matter is in various APIs such as sqlite3_bind_text
which takes an int for the length of the string.  This should be
size_t/ssize_t not int.  In all the open source code I looked at at the
time, they were all written as though size_t was used so there was
arbitrary truncation going on.  Various attacks were even possible due to
the discrepancy in how sizes were measured between the rest of the program
and SQLite.  They have been somewhat mitigated since then, but I'm still
sure that a dedicated cracker could figure out ways of exploiting this.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk/sjjEACgkQmOOfHg372QTPwACggVC6nosFsJb2caXff1DEhkLh
/zwAoNtgC/bMDnj9yXa6EkulX5QsizOR
=PcBR
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:57 PM, Robert Myers  wrote:

> One request for a change - make bind and column start with the same index.
> I always have to look up which one is zero based and which one is

one based.
>

That particular convention comes not from sqlite3, but from SQL. i have no
idea why, but every SQL API i've ever used does it that way. Doing it
different in sqlite4 would cause all kinds of porting grief, IMO.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Robert Myers]

Aghhh (just kidding)

One request for a change - make bind and column start with the same 
index. I always have to look up which one is zero based and which one is 
one based.


Rob

On 6/28/2012 10:57 AM, Simon Slavin wrote:

First, the important bit:

"SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going 
away."

Now the URL:



Just thought some people might enjoy reading and thinking about it.

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] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:52 PM, Stephan Beal  wrote:

> fully specialized). _Which_ fixed-size int type it collides with is
> platform-dependent and leads to convoluted workarounds like this:
>
>
> http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1207
>

And here's even one which was added explicitly to work around a
platform-dependent collision of long long and sqlite3_int64:

http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1270

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:46 PM, Stephan Beal  wrote:

> 64-bits. size_t has at least the following drawbacks compared to using a
> fixed-size integer type:
>

- In C++ size_t is often problematic in template specializations because in
invariably collides with one of the other integer types (which might also
be fully specialized). _Which_ fixed-size int type it collides with is
platform-dependent and leads to convoluted workarounds like this:

http://code.google.com/p/v8-juice/source/browse/convert/include/cvv8/detail/convert_core.hpp#1207

(that particular ugly hack is for long vs long-long, but the same applies
to size_t)

Sticking with fixed-size integers simply bypasses a whole range of
downstream unsightliness, and a solution which bypasses problems by its
very nature (as opposed to requiring extra effort to work around them) is
always the winning solution in my book.

So i don't mind at all making the blanket statement, "avoid size_t in new
APIs."

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 6:30 PM, Cory Nelson  wrote:

> Probably not an issue -- I'm not sure how a public API would need inttypes
> anyway.

Also, perhaps you are seeing size_t be misused. A blanket "please
> don't use" is nonsense.
>

Hi, Cory! That's what i thought to until i started porting my i/o-centric
APIs to 64-bits. size_t has at least the following drawbacks compared to
using a fixed-size integer type:

- No standard size, so it is not terribly useful in platform-independent
file formats.

- There are no standard printf()/scanf() specifiers for it, which means
those funcs cannot be used with size_t or ifdefs or casts are needed to
handle them portably. All of the fixed-size integer types have portable
scanf/printf specifiers except that int8_t and scanf has a caveat due to
the compiler upgrading a (int8_t*) to a wider pointer (at least on gcc,
where it warns if you try to pass (int8_t*) to sscanf and friends).

- Structs which contain size_t members cannot have guaranteed sizes, so
they cannot be portably serialized without writing routines to do the
numeric conversion and check the value's range on the target platform. This
adds complication to any sort of deserialization involving size_t.

The way i "solve" this in my libraries is to have a config option for
however many bits the lib supports, e.g. 16-64, and then define a
lib-specific type, my_lib_size_t, aliasing the appropriate fixed-size type.
This is essentially what sqlite does with sqlite3_int64 and friends, though
i don't know if sqlite guarantees _minimum_ or _absolute_ integer lengths.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 (don't scream)

[Richard Hipp]

On Thu, Jun 28, 2012 at 12:20 PM, Stephan Beal wrote:

> On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
> wrote:
>
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
>
> FWIW, my 0.02 Euros regarding this line:
>
> "SQLite4 makes use of standard data types such as size_t, int64_t,
> uint64_t,
> and others."
>

Since I wrote that, I've gone back into the code and changes a lot of those
into sqlite4_int64, etc.  Keep in mind that not just the code, but the
documentation is also a work in progress :-)



>
>
> size_t does not have a specified size and causes all sorts of grief in
> porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
> fixed-size integers defined in inttypes.h, and not size_t. There is of
> course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
> there are free drop-in replacements available here:
> http://code.google.com/p/msinttypes/
>
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> 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] SQLite4 (don't scream)

[Cory Nelson]

On Thu, Jun 28, 2012 at 11:20 AM, Stephan Beal wrote:

> On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin 
> wrote:
>
> > Now the URL:
> >
> > 
> >
> > Just thought some people might enjoy reading and thinking about it.
> >
>
> FWIW, my 0.02 Euros regarding this line:
>
> "SQLite4 makes use of standard data types such as size_t, int64_t,
> uint64_t,
> and others."
>
>
> size_t does not have a specified size and causes all sorts of grief in
> porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
> fixed-size integers defined in inttypes.h, and not size_t. There is of
> course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
> there are free drop-in replacements available here:
> http://code.google.com/p/msinttypes/


stdint was made available in VC++ 2010, though inttypes is still missing.
Probably not an issue -- I'm not sure how a public API would need inttypes
anyway. Also, perhaps you are seeing size_t be misused. A blanket "please
don't use" is nonsense.

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

Re: [sqlite] SQLite4 (don't scream)

[Stephan Beal]

On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin  wrote:

> Now the URL:
>
> 
>
> Just thought some people might enjoy reading and thinking about it.
>

FWIW, my 0.02 Euros regarding this line:

"SQLite4 makes use of standard data types such as size_t, int64_t, uint64_t,
and others."


size_t does not have a specified size and causes all sorts of grief in
porting i/o-based APIs between 32/64 bits, in my experience. PLEASE use the
fixed-size integers defined in inttypes.h, and not size_t. There is of
course one notable caveat: MSC does not support inttypes.h/stdint.h BUT
there are free drop-in replacements available here:
http://code.google.com/p/msinttypes/


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Adolfo J . Millan]

>
>  Mensaje original 
> De: Simon Slavin 
> Para:  a...@zator.com,General Discussion of SQLite Database 
> 
> Fecha:  Thu, 28 Jun 2012 15:43:26 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
> 
>
>
>On 28 Jun 2012, at 12:12pm, ajm@zatorcom wrote:
>

>I hope Richard's answer tells you what you want to know.  He runs the
>team that makes SEE.
>

Of course I know, DRH is the pope in this matter :-)

>Just in case you're actually looking for this because you're used to it
>with other DBMSen, I wondered whether you might be asking not about
>encrypted storage on disk but about the communications between your app
>and the database server.  Having these communications encrypted is a
>requirement for some installations and I'm used to being asked this
>question.
>

No I,m using SQLite from some years ago, and mine is just a desktop app. Any 
way thanks for your advice.

I must think about the solution proposed in the DRH response.

--
Adolfo


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

[sqlite] SQLite4 (don't scream)

[Simon Slavin]

First, the important bit:

"SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not 
going away."

Now the URL:



Just thought some people might enjoy reading and thinking about it.

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

[sqlite] Not sure how to interrupt this

[Jeff Archer]

>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:57:06 EDT 2012
>Did you prepare the INSERT statement in between running CREATE TABLE and
CREATE INDEX,
>by any chance? Or perhaps CREATE TABLE for a different table?

No, the CREATE INDEXs were done right after the CREATE TABLE.
I have a wrapper function, Execute(), that does the Prepare, Bind, Step,
Reset, Finalize sequence.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Sylvain Pointeau]

It will not be possible with SQLite but it would be possible using H2.
http://www.h2database.com/html/advanced.html#file_system

maybe you could convert the sqlite to H2 somewhere in your process?

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

Re: [sqlite] Not sure how to interrupt this

[Pavel Ivanov]

I think SQLITE_SCHEMA can happen even if you run things like CREATE
TABLE or ALTER TABLE on the same connection. Also ATTACH DATABASE and
DETACH DATABASE should invalidate all statements prepared before that.

Pavel


On Thu, Jun 28, 2012 at 9:58 AM, Marc L. Allen
 wrote:
> Oh.. you're positing a second party.  Ok.. now I'm interested to see if there 
> was one.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Thursday, June 28, 2012 9:55 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Not sure how to interrupt this
>
> Marc L. Allen  wrote:
>> How could the schema have changed?
>
> Someone ran CREATE TABLE or VACUUM or similar on the database (possibly via a 
> different connection).
> --
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Pavel Ivanov]

On Thu, Jun 28, 2012 at 9:54 AM, Hector Guilarte  wrote:
> I don't think it would.
>
> Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db",
> config.toProperties());
>
> is telling to open a connection to the file "sample.db" which is located in
> the same folder as the application is executing, but is going for a *file*.
> However, I'm going to check what configuration properties can be passed to
> the method to see if one says something about a socket, an inputStream or
> something like that.

SQLite works only with files, nothing else. You could have some luck
feeding other types of data to SQLite if JDBC supports notion of
SQLite's VFS (Virtual File System) or if you use some other language
that supports it (e.g. C/C++). But even then you will have really hard
time trying to feed socket or inputStream to it, because they are
stream-like and support only consecutive reading while SQLite needs
random access to the data. How e.g. you'll execute operation "read
1024 bytes at offset 10240" on a stream? The only way you can do that
is to read everything from stream and then give to SQLite what it
needs. But then you can save everything you read from stream into a
temporary file and open that temporary file in SQLite.

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

Re: [sqlite] Not sure how to interrupt this

[Igor Tandetnik]

Jeff Archer  wrote:
>> Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012
>> My guess is that a) you have prepared your statement with
> sqlite3_prepare_v2 (as opposed to sqlite3_prepare)...
> 
> 
> Statement was prepared with sqlite3_prepare16_v2()
> 
> Database file was created only moments earlier.

Did you prepare the INSERT statement in between running CREATE TABLE and CREATE 
INDEX, by any chance? Or perhaps CREATE TABLE for a different table?
-- 
Igor Tandetnik

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

Re: [sqlite] Complete list of companies that bought SQLite licenses?

[Simon Slavin]

On 28 Jun 2012, at 11:53am, Peter Volkis  wrote:

> Occasionally I bump into some proprietary software that's using SQLite behind 
> the curtain and I wonder if they have bought a license. I know SQLite is 
> public domain and it is not required to buy a license while using it for 
> commercial purposes. I wonder about it just out of straight curiousity, but 
> software companies are very often not happy to answer this question and it 
> gets neglected. Certain companies even consider this question very offensive. 
> Is there a public list of all companies that have bought an SQLite license or 
> this information is considered private and is not openly disclosed? Being 
> able to see which companies "play nice" would help deciding if it's worth 
> using their software.

License details are here:



tl;dr version: no license is required or even requested.  You can buy one if 
some part of your organisation requires it for legal purposes.

There's a list of well-known users here:



However, being truly public domain (not GPL, copyleft, or anything like that) 
means no license is needed, or asked for.  Consequently it's found in countless 
products from companies which would never organise the business relationships 
necessary for obtaining and paying for a license.  For instance, I recently 
found the standards SQLite headertext in the firmware update for a TV recorder, 
which is made by an obscure Korean company which would probably not pay a 
license fee.

I don't think there's much a company could do to misuse SQLite.  Claiming 
authorship or ownership over it would be illegal (in my country, at least) but 
apart from that I can't think of anything.

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

Re: [sqlite] Not sure how to interrupt this

[Marc L. Allen]

Oh.. you're positing a second party.  Ok.. now I'm interested to see if there 
was one.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Thursday, June 28, 2012 9:55 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Not sure how to interrupt this

Marc L. Allen  wrote:
> How could the schema have changed?

Someone ran CREATE TABLE or VACUUM or similar on the database (possibly via a 
different connection).
-- 
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] Not sure how to interrupt this

[Igor Tandetnik]

Marc L. Allen  wrote:
> How could the schema have changed?

Someone ran CREATE TABLE or VACUUM or similar on the database (possibly via a 
different connection).
-- 
Igor Tandetnik

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

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Hector Guilarte]

I don't think it would.

Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db",
config.toProperties());

is telling to open a connection to the file "sample.db" which is located in
the same folder as the application is executing, but is going for a *file*.
However, I'm going to check what configuration properties can be passed to
the method to see if one says something about a socket, an inputStream or
something like that.

Thanks!

On Thu, Jun 28, 2012 at 3:37 PM, Black, Michael (IS)  wrote:

> Does this article help?
>
>
> http://stackoverflow.com/questions/4574303/java-sqlite-how-to-open-database-as-read-only
>
>
>
>
>
> 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 Hector Guilarte [hector...@gmail.com]
> Sent: Thursday, June 28, 2012 8:22 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly
>
> Because I want it to run on Google App Engine. GAE doesn't allow to write
> to the FileSystem, so I can't open the JDBC Connection to the SQLite file
> and therefore I can't read the data to convert to CSV.
>
> I upload the file to Google App Engine with a HTML form and that's how I
> get it in an InputStream
>
> On Thu, Jun 28, 2012 at 3:19 PM, OBones  wrote:
>
> > If you've got the database in a stream, why can't you save it to a disk
> > file and then use this file with sqlite?
> >
> > __**_
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://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] Not sure how to interrupt this

[Jeff Archer]

>Igor Tandetnik itandetnik at mvps.org Thu Jun 28 09:38:27 EDT 2012
>My guess is that a) you have prepared your statement with
sqlite3_prepare_v2 (as opposed to sqlite3_prepare)...


Statement was prepared with sqlite3_prepare16_v2()

Database file was created only moments earlier.

Jeff Archer
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Simon Slavin]

On 28 Jun 2012, at 12:12pm, a...@zator.com wrote:

> Assuming using SEE, do is there the ability to decide what I/O operations are 
> to be performed encrypted or with the plain (as is) content?

I hope Richard's answer tells you what you want to know.  He runs the team that 
makes SEE.

Just in case you're actually looking for this because you're used to it with 
other DBMSen, I wondered whether you might be asking not about encrypted 
storage on disk but about the communications between your app and the database 
server.  Having these communications encrypted is a requirement for some 
installations and I'm used to being asked this question.

SQLite does not have a client/server architecture or anything like it.  Unless 
you make special provision for it, all access to the data is done inside the 
process of your application which asks for the operation.  Any communication 
between your app and a database server or service takes place inside your CPU.  
The only server-related concern would be if you are accessing a database file 
using file-sharing over a network.  In that case, if you are using SEE and the 
database is encrypted, any data passing over your network will be encrypted at 
the page level, which makes it difficult even to identify database records and 
fields, let alone what was requested or changed by an operation.  It's as 
secure as AES-256 (or whatever you're using) can be.

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

Re: [sqlite] Not sure how to interrupt this

[Marc L. Allen]

How could the schema have changed?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Thursday, June 28, 2012 9:38 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Not sure how to interrupt this

Jeff Archer  wrote:
> I have SQLITE_CONFIG_LOG callback installed:
> sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, 
> /*pUserData*/NULL);
> 
> I get the following message through the SQLITE_CONFIG_LOG callback 
> during the sqlite3_step():
> errcode: SQLITE_SCHEMA  (17)
> message: statement aborts at 80: [INSERT INTO [Scans](ScanID, 
> Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, 
> @Result);] database schema has changed

My guess is that a) you have prepared your statement with sqlite3_prepare_v2 
(as opposed to sqlite3_prepare), and b) the schema did in fact change between 
the time the statement was prepared and the time it got executed. In this case, 
SQLite would internally intercept SQLITE_SCHEMA (but apparently, not before 
logging it), then finalize, re-prepare and re-execute the statement.
--
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] Not sure how to interrupt this

[Igor Tandetnik]

Jeff Archer  wrote:
> I have SQLITE_CONFIG_LOG callback installed:
> sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL);
> 
> I get the following message through the SQLITE_CONFIG_LOG callback during
> the sqlite3_step():
> errcode: SQLITE_SCHEMA  (17)
> message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp,
> EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database
> schema has changed

My guess is that a) you have prepared your statement with sqlite3_prepare_v2 
(as opposed to sqlite3_prepare), and b) the schema did in fact change between 
the time the statement was prepared and the time it got executed. In this case, 
SQLite would internally intercept SQLITE_SCHEMA (but apparently, not before 
logging it), then finalize, re-prepare and re-execute the statement.
-- 
Igor Tandetnik

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

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Black, Michael (IS)]

Does this article help?

http://stackoverflow.com/questions/4574303/java-sqlite-how-to-open-database-as-read-only





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 Hector Guilarte [hector...@gmail.com]
Sent: Thursday, June 28, 2012 8:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

Because I want it to run on Google App Engine. GAE doesn't allow to write
to the FileSystem, so I can't open the JDBC Connection to the SQLite file
and therefore I can't read the data to convert to CSV.

I upload the file to Google App Engine with a HTML form and that's how I
get it in an InputStream

On Thu, Jun 28, 2012 at 3:19 PM, OBones  wrote:

> If you've got the database in a stream, why can't you save it to a disk
> file and then use this file with sqlite?
>
> __**_
> 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] Not sure how to interrupt this

[Marc L. Allen]

I think he wants to know why he is receiving what appears to be an error 
notification via the callback.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Adam DeVita
Sent: Thursday, June 28, 2012 9:05 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Not sure how to interrupt this

SQLITE DONE is what you get when you successfully run an insert.

What is the problem?
Adam

On Wed, Jun 27, 2012 at 7:02 PM, Jeff Archer  wrote:

> I am getting back SQLITE_DONE (101) from sqlite3_step() and the 
> statement is clearly being executed.
>
> SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) 
> VALUES(NULL, @Timestamp, @Timestamp, @Result);"
>
> The table has been created as:
> CREATE TABLE [Scans]
> (ScanID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> ,Timestamp   DATETIME NOT NULL UNIQUE
> ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME
> ,Result  VARCHAR
> );
> CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime, 
> Result); CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp);
>
> I have SQLITE_CONFIG_LOG callback installed:
> sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, 
> /*pUserData*/NULL);
>
> I get the following message through the SQLITE_CONFIG_LOG callback 
> during the sqlite3_step():
> errcode: SQLITE_SCHEMA  (17)
> message: statement aborts at 80: [INSERT INTO [Scans](ScanID, 
> Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, 
> @Result);] database schema has changed
>
> SQLITE_VERSION"3.7.13"  - amalgamation
>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
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] Load SQLite from InputStream in Java as ReadOnly

[Hector Guilarte]

Because I want it to run on Google App Engine. GAE doesn't allow to write
to the FileSystem, so I can't open the JDBC Connection to the SQLite file
and therefore I can't read the data to convert to CSV.

I upload the file to Google App Engine with a HTML form and that's how I
get it in an InputStream

On Thu, Jun 28, 2012 at 3:19 PM, OBones  wrote:

> If you've got the database in a stream, why can't you save it to a disk
> file and then use this file with sqlite?
>
> __**_
> 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] Load SQLite from InputStream in Java as ReadOnly

[OBones]

If you've got the database in a stream, why can't you save it to a disk 
file and then use this file with sqlite?


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

Re: [sqlite] Load SQLite from InputStream in Java as ReadOnly

[Hector Guilarte]

Hello?

I'm starting to feel hopeless, No luck in StackOverflow and no luck here
:-(...

Well, i guess it just can't be done.

On Wed, Jun 27, 2012 at 10:13 AM, Hector Guilarte wrote:

> Hello everyone,
>
> I'm new to SQLite as well as to this list. I'm writing because I was
> planning on using SQLite for a personal -but public- project that I wanted
> to make available through Google App Engine. It is basically a SQLite to
> CSV converter and a SQLite to VCard converter. In other words, I have an
> Address Book in a SQLite database and I wanted to export it to a well-known
> format for importing it to some other places, as CSV and as VCard.
>
> I already placed my question in StackOverflow.com last friday with no
> luck, it has only been seen 21 times and the only answer I received was not
> helpfull since it was telling me somehing like "first use something like
> what you are trying to develop yourself and then use yours with their
> output" (nahh, I'm kidding, but the real answer is not far from that and it
> can be seen here:
> http://stackoverflow.com/questions/11155537/load-sqlite-from-inputstream-in-java-as-readonly
> )
>
> If somebody has an answer, even if it is "It's not possible at all, so
> drop it" and is a stackoverflow user, feel free to go ahead and answer over
> there to earn the points, but please post your answer here as well. Now my
> question as I wrote it in StackOverflow:
>
> I have an App which receives a SQLite database to read some data and
> export it as an CSV. I'm trying to upload it to Google App Engine but I
> faced a huge problem which I think makes it impossible to use the GAE for
> this app.
>
> The problem is that since on the GAE I can't write to the FileSystem, I
> can't open the JDBC Connection to the SQLite file and therefore I can't
> read the data to convert to CSV. I've been looking for other options such
> as Google Cloud Storage, but I don't want to use my only "free trial" of it
> on this application, and actually I don't want to have to pay ever for this
> app after the Free Trial ends, so this is not an option.
>
> After a lot of research, my only guess is that I might be able to load the
> database straight from the InputStream as I received it from the upload
> form I'm using to get it, however, this is a 100% lucky guess and I've not
> been able to find anything about this approach online, but I just don't
> want to believe it can't be done with any of the existing JDBC libraries to
> SQLite and I'm hoping somebody here will tell me how to do it.
>
> If the InputStream approach is not possible, but you know some other way
> to open a SQLite DB in GAE to READ ONLY, and then dispose it, feel free to
> comment as well...
>
> If there is another option like "don't use JDBC, use a socket connection
> with a pipe to open the connection with the InputStream", I'd also like to
> hear that, it does not HAVE to be done with JDBC.
>
> Thanks a lot,
>
> Héctor Guilarte
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not sure how to interrupt this

[Adam DeVita]

SQLITE DONE is what you get when you successfully run an insert.

What is the problem?
Adam

On Wed, Jun 27, 2012 at 7:02 PM, Jeff Archer  wrote:

> I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement
> is clearly being executed.
>
> SQL: "INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL,
> @Timestamp, @Timestamp, @Result);"
>
> The table has been created as:
> CREATE TABLE [Scans]
> (ScanID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> ,Timestamp   DATETIME NOT NULL UNIQUE
> ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME
> ,Result  VARCHAR
> );
> CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime,
> Result);
> CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp);
>
> I have SQLITE_CONFIG_LOG callback installed:
> sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL);
>
> I get the following message through the SQLITE_CONFIG_LOG callback during
> the sqlite3_step():
> errcode: SQLITE_SCHEMA  (17)
> message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp,
> EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database
> schema has changed
>
> SQLITE_VERSION"3.7.13"  - amalgamation
>
> Jeff Archer
> Nanotronics Imaging
> jsarc...@nanotronicsimaging.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I rename a column called IN?

[Black, Michael (IS)]

Quote it:



sqlite> create table x(in);
Error: near "in": syntax error
sqlite> create table x("in");
sqlite> insert into x values(1);
sqlite> select * from x;
1
sqlite> select in from x;
Error: near "in": syntax error
sqlite> select "in" from x;
1
sqlite> update x set in=3;
Error: near "in": syntax error
sqlite> update x set "in"=3;
sqlite> select "in" from x;
3



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 schaeufe...@web.de [schaeufe...@web.de]
Sent: Wednesday, June 27, 2012 11:20 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] How do I rename a column called IN?

Hi all,

I am no programmer, and I only know a small number of very basic SQLite 
commands, and I have a serious problem: I need to change values in a column 
that some fool named IN like the operator (I think IN is an operator). 
Normally, I use the command line

UPDATE name_of_table SET xyz=10 WHERE xyz<10;

in order to change values in a column, and it works well, and that's all I ever 
need to do. However, everytime I try to access this particular column called 
IN, I get errors that I believe are due to the column's name. The column can be 
read but not modified. Is there a way to address and to change the values in 
it? It was obviously created but how can I change it?

Regards,

Martin
--
 Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für iPhone und 
Android.
https://produkte.web.de/freemail_mobile_startseite/
___
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] Complete list of companies that bought SQLite licenses?

[Peter Volkis]

Occasionally I bump into some proprietary software that's using SQLite behind 
the curtain and I wonder if they have bought a license. I know SQLite is public 
domain and it is not required to buy a license while using it for commercial 
purposes. I wonder about it just out of straight curiousity, but software 
companies are very often not happy to answer this question and it gets 
neglected. Certain companies even consider this question very offensive. Is 
there a public list of all companies that have bought an SQLite license or this 
information is considered private and is not openly disclosed? Being able to 
see which companies "play nice" would help deciding if it's worth using their 
software. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I rename a column called IN?

[schaeuferle]

Hi all,

I am no programmer, and I only know a small number of very basic SQLite 
commands, and I have a serious problem: I need to change values in a column 
that some fool named IN like the operator (I think IN is an operator). 
Normally, I use the command line

UPDATE name_of_table SET xyz=10 WHERE xyz<10;

in order to change values in a column, and it works well, and that's all I ever 
need to do. However, everytime I try to access this particular column called 
IN, I get errors that I believe are due to the column's name. The column can be 
read but not modified. Is there a way to address and to change the values in 
it? It was obviously created but how can I change it?

Regards,

Martin
-- 
 Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für iPhone und 
Android.
https://produkte.web.de/freemail_mobile_startseite/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Richard Hipp]

On Thu, Jun 28, 2012 at 7:12 AM,  wrote:

> Hi:
>
> Assuming using SEE, do is there the ability to decide what I/O operations
> are to be performed encrypted or with the plain (as is) content?
>

Encryption is turned on and off at the database level.  So either an entire
database file is completely encrypted or none of it is.  You cannot encrypt
some rows and leave others plaintext.

But you can use the ATTACH  command
to connect two or more database files to your database connection at the
same time, so that they look to your application as if they are a single
file, and some subset of those files can be encrypted while another subset
is plaintext.  So if you can arrange your schema such that encrypted
content goes in one set of tables, and you put those tables in one database
file, and your plaintext data goes in a separate set of tables and those
tables go in a different database file, then you can have some content
encrypted and other content not encrypted.


>
> I'm planning an application with only selected data encripted (in each row
> of certain tables, there is a flag showing the encrypted/plain state).
>
> Thanks in advance.
>
> >
> >  Mensaje original 
> > De: Richard Hipp 
> > Para:  Paul Vercellotti , General Discussion of
> SQLite Database 
> > Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> > Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
> >
> >
> >SEE is a drop-in replacement for public-domain SQLite.  In other words, it
> >will read and write ordinary unencrypted database files, and it will do so
> >with no speed penalty.
> >
> >For performance sensitive applications, what developers sometimes do is
> >break up their content into sensitive and non-sensitive, storing each in
> >separate databases, and only encrypt the sensitive content.  SEE is able
> to
> >open both databases at once (using the ATTACH command) and do joins on
> >tables between the two databases, even though only one of the two is
> >encrypted.
> >
>
>
> ___
> 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 Encryption Extension Performance?

[ajm]

Hi:

Assuming using SEE, do is there the ability to decide what I/O operations are 
to be performed encrypted or with the plain (as is) content?

I'm planning an application with only selected data encripted (in each row of 
certain tables, there is a flag showing the encrypted/plain state).

Thanks in advance.

>
>  Mensaje original 
> De: Richard Hipp 
> Para:  Paul Vercellotti , General Discussion of SQLite 
> Database 
> Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
>
>SEE is a drop-in replacement for public-domain SQLite.  In other words, it
>will read and write ordinary unencrypted database files, and it will do so
>with no speed penalty.
>
>For performance sensitive applications, what developers sometimes do is
>break up their content into sensitive and non-sensitive, storing each in
>separate databases, and only encrypt the sensitive content.  SEE is able to
>open both databases at once (using the ATTACH command) and do joins on
>tables between the two databases, even though only one of the two is
>encrypted.
>


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

[sqlite] JDBC Drivers--which one?

[L Anderson]

I have found both the Xerial and Zentus SQLite JDBC Drivers on the net. 
 This leads me to wonder are there other JDBC drivers for SQLite and if 
so what are they?  Also, what are the pros and cons of the available 
drivers?  Which one is the recommended one for use with SQLite?


Thanks,

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