Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Alex Bronstein]

Thanks, everyone, for your responses on Tuesday. I wasn't on the mailing
list, so didn't receive them in my email, but I am now, and I can see the
responses on
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg118979.html.
Much appreciated!

> Can you show us some place where using 999 parameters is a reasonable way
to use SQLite ?

The use-case I have is that I'm a maintainer of Drupal
<https://www.drupal.org/>, a CMS written in PHP. In Drupal, we support
multiple database backends. Typically, a production site will operate on
MySQL or PostgreSQL, but we also support SQLite, which is convenient for
local testing. We have a database abstraction API that allows module and
site developers to write code like:

$all_results = $db->select('table_foo')
  ->fields('table_foo', ['field_1', 'field_2'])
  ->condition('field_3', $large_array, 'IN')
  ->execute()
  ->fetchAll();

The implementation of execute() above ends up calling a PHP PDO
<https://www.php.net/manual/en/book.pdo.php> statement that's more or less
as follows:

$statement = $pdo->prepare("SELECT field_1, field_2 FROM table_foo WHERE
field_3 IN (?, ?, ... ?)");
return $statement->execute($large_array);


A similar use case is for insertion. For example, a module or site
developer can call:

$upsert = $connection->upsert('kv')->key('k')->fields(['k', 'v']);
foreach ($large_associative_array as $key => $value) {
  $upsert->values(['k' => $key, 'v' => $value]);
}
$upsert->execute();

The execute() above ends up calling a PHP PDO statement that's appropriate
for the database backend. On SQLite prior to 3.24 it's more or less:

$statement = $pdo->prepare("INSERT OR REPLACE INTO kv (k, v) VALUES ((?,
?), (?, ?), ... (?, ?))");
return $statement->execute($values);


In the case of the multi-row insert/upsert, we can change our
implementation to execute multiple PDO statements in succession in order to
keep each one below the 999 limit.

In the case of the IN (...), thank you for your suggestions to use a temp
table. Because there can be multiple calls to ->condition(), including
nested ones, it might be a bit tricky for us to get the temp tables
implemented correctly. We'd need to track multiple temp tables and then
drop them at the correct time, which would break the current encapsulation
of our API, but it's potentially doable with some refactoring. However,
your temp table idea inspired me to come up with an alternate solution of
using json_each(). So that first example could be implemented as:

$statement = $pdo->prepare("SELECT field_1, field_2 FROM table_foo WHERE
field_3 IN (select value from json_each(?))");
return $statement->execute([json_encode($large_array)]);


If you're curious, I posted a proof of concept patch for both the upsert
and the large IN condition to
https://www.drupal.org/project/drupal/issues/2031261#comment-13454464.


In summary, yes, I think we can change things in Drupal to work within the
999 limit. I think there might be other PHP projects that could benefit
from a higher limit (for example, ones that don't have Drupal's abstraction
API around PDO), but I don't know enough specifics about those to argue on
their behalf.

Thanks again for your consideration and insights.

Alex.


On Tue, Feb 4, 2020 at 10:27 AM Alex Bronstein 
wrote:

> Prepared statements are good practice and recommended in places such as
> https://www.php.net/manual/en/pdo.prepared-statements.php. There are use
> cases for using them with many items in an IN() clause, or when inserting
> many rows in a single INSERT statement. In such cases, you can easily end
> up with more than 999 parameters.
>
> While sqlite can be compiled with a larger SQLITE_MAX_VARIABLE_NUMBER
> flag, there are situations where the application developer doesn't have
> control over how the system libraries are compiled. For example, a given
> PHP application could run on either a stock Debian/Ubuntu installation, a
> stock Fedora/RHEL/CentOS installation, or other systems. Debian compiles
> sqlite with SQLITE_MAX_VARIABLE_NUMBER=25
> <https://sources.debian.org/src/sqlite3/3.27.2-3/debian/rules/#L50> (issue
> <https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=717900>) whereas
> Fedora doesn't set the flag
> <https://git.centos.org/rpms/sqlite/blob/c8/f/SPECS/sqlite.spec#_152> (
> issue <https://bugzilla.redhat.com/show_bug.cgi?id=1798134>), so gets
> sqlite's default.
>
> According to some of the answers on
> https://stackoverflow.com/questions/6581573/what-are-the-max-number-of-allowable-parameters-per-database-provider-type,
> PostgreSQL and some other databases support a 16 bit parameter count (64K
> parameters). Given current memory availability, can sqlite's default be
> raised to something similar to that?
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Alex Bronstein]

Prepared statements are good practice and recommended in places such as
https://www.php.net/manual/en/pdo.prepared-statements.php. There are use
cases for using them with many items in an IN() clause, or when inserting
many rows in a single INSERT statement. In such cases, you can easily end
up with more than 999 parameters.

While sqlite can be compiled with a larger SQLITE_MAX_VARIABLE_NUMBER flag,
there are situations where the application developer doesn't have control
over how the system libraries are compiled. For example, a given PHP
application could run on either a stock Debian/Ubuntu installation, a stock
Fedora/RHEL/CentOS installation, or other systems. Debian compiles sqlite
with SQLITE_MAX_VARIABLE_NUMBER=25
 (issue
) whereas
Fedora doesn't
set the flag
 (issue
), so gets sqlite's
default.

According to some of the answers on
https://stackoverflow.com/questions/6581573/what-are-the-max-number-of-allowable-parameters-per-database-provider-type,
PostgreSQL and some other databases support a 16 bit parameter count (64K
parameters). Given current memory availability, can sqlite's default be
raised to something similar to that?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CVE's opened on 3.30.1 status

[Raitses, Alex]

Thanks a lot for the prompt response,
As far as I found in Fossil repository fixes for all CVE's , excepting   
erroneously submitted CVE-2019-19646, were merged to Fossil.
Can you please estimate next official release of SQLite including these fixes?

Regards,
Alex


-Original Message-
From: drhsql...@gmail.com  On Behalf Of Richard Hipp
Sent: Tuesday, December 24, 2019 6:31 PM
To: SQLite mailing list 
Cc: Raitses, Alex 
Subject: Re: [sqlite] CVE's opened on 3.30.1 status

On 12/24/19, Raitses, Alex  wrote:
> Hi,
> Can you please update on status of the following CVE’s submitted on 3.30.1?
> CVE’s link to patches references GitHub branch, however I could find 
> corresponding submits to Fossil repository.
> CVE’s list:
> https://nvd.nist.gov/vuln/detail/CVE-2019-19244
> https://nvd.nist.gov/vuln/detail/CVE-2019-19603
> https://nvd.nist.gov/vuln/detail/CVE-2019-19242
> https://nvd.nist.gov/vuln/detail/CVE-2019-19646
> https://nvd.nist.gov/vuln/detail/CVE-2019-19645

None of these CVEs describe actual vulnerabilities, at least not for the 
typical use-case for SQLite.

If you have an unusual application in which you allow unauthenticated users to 
submit arbitrary SQL to your application, then four of these CVEs describe a 
denial-of-service opportunity to an attacker.  In other words, an attacker who 
can present arbitrary SQL queries (and DDL statements) to the application can 
cause the application to crash.
Not many applications fall into that category, though.  The only application 
that I know of that does this is the Chrome web-browser.

How does your application use SQLite?   Do you allow anonymous
attackers to present arbitrary SQL to your application?   If not, then
none of this applies to you.

The CVE-2019-19646 describes a bug in a new feature of SQLite that has not yet 
been released.  CVE-2019-19646 was apparently submitted in error.  
Unfortunately, we do not know of any mechanism to correct erroneous CVEs.  Do 
you?

All of the problems described by the CVEs you list have been fixed.
In fact, most of the CVEs you list point to the check-in that fixes the 
problem, in a GitHub mirror of the SQLite repository.

The SQLite developers do not issue or track CVEs.  CVEs against SQLite are 
issued by third-parties, typically third-parties who are running fuzzers 
against the SQLite, and usually without the consultation or approval of the 
SQLite developers.

--
D. Richard Hipp
d...@sqlite.org
-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CVE's opened on 3.30.1 status

[Raitses, Alex]

Hi,
Can you please update on status of the following CVE’s submitted on 3.30.1?
CVE’s link to patches references GitHub branch, however I could find 
corresponding submits to Fossil repository.
CVE’s list:
https://nvd.nist.gov/vuln/detail/CVE-2019-19244
https://nvd.nist.gov/vuln/detail/CVE-2019-19603
https://nvd.nist.gov/vuln/detail/CVE-2019-19242
https://nvd.nist.gov/vuln/detail/CVE-2019-19646
https://nvd.nist.gov/vuln/detail/CVE-2019-19645


Regards,
Alex

-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CVE-2019-19317

[Raitses, Alex]

Hello,
CVE-2019-19317 (https://nvd.nist.gov/vuln/detail/CVE-2019-19317) was submitted 
on SQLite.
As far as I can see the patch is already submitted. Can you confirm please?
Do you have estimation for the fixed version release?


Thanks in advance,
Regards,
Alex

-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Klocwork static analysis report

[Raitses, Alex]

Hello,
Please find attached Klocwork static analysis report for “C source code as an 
amalgamation”, version 3.30.1 (sqlite3.c).
Can you please review the report attached and update which bugs can be fixed.


Regards,
Alex
-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.30.0 in about two weeks.

[Raitses, Alex]

Hello,
I have noticed that security CVE 
(https://nvd.nist.gov/vuln/detail/CVE-2019-16168) has been submitted on SQLite. 
As far as I can see the patch was submitted to the trunk.
Will CVE patch be included in the 3.30.0?

Regards,
Alex

-Original Message-
From: sqlite-users  On Behalf Of 
Richard Hipp
Sent: Thursday, September 26, 2019 9:26 PM
To: General Discussion of SQLite Database 
; sqlite-dev 

Subject: [sqlite] SQLite version 3.30.0 in about two weeks.

Our plan is to release SQLite version 3.30.0 in about two weeks - on or about 
2019-10-10.  Please review the change log

https://www.sqlite.org/draft/releaselog/3_30_0.html

And perhaps download, build, and test the latest snapshot.  Please let us know 
if you encounter any problems or concerns.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
-
Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for
the sole use of the intended recipient(s). Any review or distribution
by others is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete all copies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unexpected "out of memory" error in CLI edit() function

[Alex Kirchhoff]

Hello,

I've encountered some unexpected behavior with the CLI's edit()
function.  In particular, when passing a BLOB to edit(), and the
result of the editor is an empty file, I get an out of memory error:

$ sqlite3 :memory: "select edit(x'', 'true')"
Error: out of memory

This works fine with TEXT values rather than BLOB values.  I suspect
this is a bug.

Thanks,
Alex

P.S.: I am not subscribed to this mailing list, so please CC me
directly on any replies.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_db_filename returns an empty string when null pointer is promised

[Alex Alabuzhev]

Hi,

https://www.sqlite.org/c3ref/db_filename.html:

> If there is no attached database N on the database connection D, or if
database N is a temporary or in-memory database, then a NULL pointer is
returned.

However, when called for :memory: db the function actually returns "".

Looking at the code:

/*
** Return the full pathname of the database file.
**
** Except, if the pager is in-memory only, then return an empty string if
** nullIfMemDb is true.  This routine is called with nullIfMemDb==1 when
** used to report the filename to the user, for compatibility with legacy
** behavior.  But when the Btree needs to know the filename for matching to
** shared cache, it uses nullIfMemDb==0 so that in-memory databases can
** participate in shared-cache.
*/
SQLITE_PRIVATE const char *sqlite3PagerFilename(Pager *pPager, int
nullIfMemDb){
  return (nullIfMemDb && pPager->memDb) ? "" : pPager->zFilename;
}

- as the comment says, it returns an empty string in case of in-memory mode
(although "nullIfMemDb" confusingly implies null).

I have no idea who is correct here - the code or the documentation - but
one of them should probably be corrected?

Thanks.

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

Re: [sqlite] Simple way to import GPX file?

[Alex M]

On 12/9/18 14:43, Winfried wrote:
> Thanks to both of you. Problem solved :-)
> 
> 

Long term, look at spatial related tools. Like Spatialite built on top
of Sqlite, or ogr2ogr which is part of gdal (www.gdal.org). That can
convert gpx to csv in one command, or even sql possibly (it does work
with spatialite). Also gbsbabel is a classic GPS data conversion tool.

Thanks,
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] XOR operator

[Alex Henrie]

2017-10-06 1:28 GMT-06:00 Clemens Ladisch <clem...@ladisch.de>:
> Alex Henrie wrote:
>> I wanted to use the XOR operator in a query today, but then found out
>> that SQLite doesn't support it.
>
> For boolean values, "a XOR b" = "a <> b".
> For binary values, "a XOR b" = "(a | b) - (a & b)".

Thank you, I had forgotten that exclusive-or is equivalent to
not-equals for boolean values. Still, it would be a little nicer if I
could write a == b XOR c == d instead of (a == b) != (c == d). It
would be even more readable for non-boolean values: a XOR b instead of
(NOT NOT a) != (NOT NOT b).

Anyway, thanks for the help. I'd love to see boolean XOR in SQLite,
but if not, at least I have a solution now.

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

[sqlite] XOR operator

[Alex Henrie]

Dear SQLite developers,

I wanted to use the XOR operator in a query today, but then found out
that SQLite doesn't support it. MySQL and Microsoft SQL both have XOR.
Would you consider adding it to SQLite too?

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

Re: [sqlite] When is data committed on one connection seen on another?

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Olivier Mascia 

> If I'm permitted: you're wrong. 

Ha, yes, well if our initial threading model is anything to go by, I think 
you're on safe ground with that assertion. 

> Unless you have a very complex schema (I would say at the very least more 
> than on hundred tables and other create statement), opening a SQLite 
> connection is lightweight, 

We currently have 500 tables and 1500 triggers in the schema.   Perhaps that is 
why we didn't have much luck having one connection per thread or opening a 
connection per access.  Perhaps our schema needs a rework, would one table with 
a million rows be better than 500 tables with 2000 rows each? 

We were considering adding a view per table too, that would make it 3000 
elements in the schema, if 100 is considered a lot are we attempting to use 
sqlite in a manner that it is not suited for?  We may need to consider another 
alternative if that's the case, although that would be a shame. 

> is lightweight, especially compared to whatever experience you 
> might have had with quite any other SQL system. 

Sadly we are not comparing sqlite with another SQL DB.  We are attempting to 
replace an in-house memory resident non-sql database with sqlite.  So our 
comparison is between a) reading from memory and b) opening a connection and 
reading from sqlite. 

> you intend to loose on both of them (pooling connections 
> and sharing them across threads at the cost of mutual exclusion contention 
> to get it working). 

Believe me that we would like nothing more than to do what you suggest, we will 
be looking into any way possible to get this implemented in the optimal manner. 
 Thanks a lot for giving your advice. 

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

Re: [sqlite] When is data committed on one connection seen on another?

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Igor Tandetnik 

> Statement execution starts with the first sqlite3_step after sqlite3_prepare 
> or the most recent sqlite3_reset; and ends with sqlite3_reset or 
> sqlite3_finalize. 

Thanks makes sense.  Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is data committed on one connection seen on another?

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Simon Slavin 

> It is not enough to lock the _prepare, lock the _step()s, and lock the 
> _finalize. 
> If they're sharing a connection with other threads then the lock has to be 
> placed at the start of the prepare and be released at the end of the 
> finalize. 
> Don't forget that they're all part of the same transaction. 

Got it, it's the set of the 3 operations that should be serialized.  Thanks. 

The finalize is the end of the implicit transaction, correct?  Assuming that it 
is the finalize for the only statement being executed on that connection.  Then 
if the same thread did another prepare/step/finalize on that same connection it 
would be in a new implicit transaction.  Do I have that right? 

> This is why you don't generally share a connection between simultaneous 
> threads.  If the threads have different connections and you let SQLite handle 
> the locking things work properly. 

Hmm yes, I'm hearing that repeatedly.  We will attempt to identify why using a 
lot of connections is using such a huge amount of memory and maybe we will be 
able to go that route. 

Thanks again 
Alex 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is data committed on one connection seen on another?

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Hick Gunter 

> Sharing a connection between threads makes it practically impossible for any 
> one thread to tell when a transaction begins or ends. From the point of view 
> of the database connection, the first statement to begin processing opens a 
> transaction and the last statement to end processing (which could be in a 
> totally different thread) closes it. IT ia all too easy to have a thread open 
> a 
> transaction and forget about it later. This causes all the other threads to 
> see 
> consistent (not stale) data. Enabling "read uncommitted" may alleviate the 
> sypmtoms, but it does not remove the cause. 

Seems like if we are going to share a single connection we would need to ensure 
that only one operation is happening at one time.  I'm hoping that when you say 
above "and the last statement to end processing...closes it" means that if we 
guarantee to have just one set of prepare/step/finalize happening at one time 
on a connection then the next set will always start a new transaction.  Does 
that sound correct? 

> Your assertions "a deleted row on one connection is found by a select on the 
> other" and "BEGIN/DELETE/COMMIT and SELECT is happening in the same 
> thread" is perfectly consistent if BEGIN/DELETE/COMMIT happens on one 
> connection and SELECT on the other. 
  

Yes that is exactly what we were doing.  BEGIN/DELETE/COMMIT happened on one 
connection and SELECT on the other.  Ooops. 

> As already stated, and per my own experience, each thread should have it's 
> own connection and do whatever it needs to do there, without interference 
> from other threads. 

I appreciate this point.  Early prototyping indicated that this might not be 
possible for our system, which makes me a little nervous if that is what most 
users end up doing.  We will definitely take another look at not sharing 
connections. 

Danke sehr 
Alex 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is data committed on one connection seen on another?

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Simon Slavin 

> Your description of your fault suggests that at least two of your 
> threads/processes are trying to use the same connection to the database at 
> the same time 

We have a bunch of reads happening at the same time on one connection. The 
writes were serialized with a write lock but the reads were just doing whatever 
they wanted.  It seems like this is our mistake. 

> > We are trying to implement a system where all writes occur on one 
> connection ... Does it 
> sound like we are doing something fundamentally wrong by trying to share 
> connections across threads in this way? 
> 
> If you don't have your own mutex locks, then yes. Don't try to set up a 
> situation where two transactions are happening at the same time with the 
> same connection. The standard way to do that correctly is to give each 
> thread its own connection to the database and let SQLite do all the locking 
> necessary. However it's not difficult to make your own mutex system work, 
> it just seems like your own one isn't working properly. 
> 

Great info.  I don't think we can afford to have a connection per thread. We 
have an arbitrarily large number of threads coming and going all the time and a 
combination of the amount of memory each connection is taking up and how long 
it takes to connect to the db it looks like limiting the number of connections 
would be the direction we go if we can get it to work. Maybe a pool of 
connections. 

We'll try serializing the reads on one connection and see what performance that 
gives us. It seems like we will be giving up a lot of parallelism, but as we 
are not sure yet exactly how the locking in the DB works that may not be as bad 
system impact as we fear. 

Thanks for the info 
Alex 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 105, Issue 13

[Alex Ward]

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Igor Tandetnik 

> Realize that a transaction is a property of a connection, not a thread or a 
> query. Suppose thread A starts a read at time T and ends it at time 
> T+20; and thread B starts a read on the same connection at time T+10 and 
> ends it at T+30. Both reads are part of the same unbroken transaction lasting 
> (at least) from T to T+30. If there's an update committed at T+5 on a 
> different 
> connection, neither read would see it, even though thread B started after it. 

Gnash, yup this does sound like our problem.  We have unfettered access to the 
read connection by any thread without a lock.  Our failure cases seem to always 
look like: 

a) Thread 1 executes a SELECT on connection A but gets swapped out after the 
sqlite3_step 
b) Thread 2 executes a DELETE on connection B, this completes 
c) Thread 2 executes a SELECT on connection A this completes but sees the 
deleted row still there 
d) Thread 1 completes SELECT from a) on connection A 

Looks like we will need to lock access to the read connection.  In my original 
post I said we had done that and it didn’t help, but we just locked the step, 
not the prepare/step/finalize so I'm guessing if we lock all three we will be 
ok.  Just to clarify, when you talked about 'starting' and 'ending' a read can 
I take that to mean the time between sqlite3_prepare and sqlite3_finalize? 

Thanks for the info 
Alex 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When is data committed on one connection seen on another?

[Alex Ward]

We think we are seeing the case where a read on one WAL mode Sqlite database 
connection using the C API is reading stale data after a transaction has 
committed data changes on another connection.  

For instance, a deleted row on one connection is still found by a select on the 
other.  The BEGIN/DELETE/COMMIT then SELECT (prepare/step/finalize for each) is 
happening in the same thread.We expected the commit of the write to be the 
point in time after which any read would read that committed data.  This does 
not seem to be the case here.  
What is the standard idiom to avoid stale data while still allowing all but a 
writing thread not to see uncommitted data?

Is there a window of time between a commit on one connection and the data being 
available on another connection?  Is that deterministic?  When is the WAL mode 
"end mark" moved on a connection past other changes?  Is it possible that we 
have an end mark on the read connection that is still behind the write on the 
other connection?  What would trigger it to move?

Would other threads doing reads in parallel on the same connection affect when 
the end mark is moved?  In our test we serialized reads so there could only be 
one happening at a time in an attempt to remove any chance of this.  But we 
still saw stale data.  

We are trying to implement a system where all writes occur on one connection 
(in a SQL transaction where a writing thread would see the uncommitted data) 
and all other reads on a second connection.  Does it sound like we are doing 
something fundamentally wrong by trying to share connections across threads in 
this way?

Any advice greatly appreciated
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Downloading SQLite issue

[Alex Fender]

I tried with Chrome and Firefox. I am using the default unzip tool in Mac.
I double click the zip folder and then it unpacks a new folder to my
desktop. When I open that folder, I then see the Sqlite programs. I double
click to open and it says damaged.

On Fri, Jul 8, 2016 at 7:52 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 8 Jul 2016, at 1:20pm, Alex Fender <a...@funnelscience.com> wrote:
>
> > After I download and unzip. I click and open Sqlite3 by double clicking.
> > When I open the file, it says damaged and move to the trash.
>
> Works fine on my Mac.
>
> Which web browser are you using to download ?
> Which program is used when you unzip the file ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Alex Fender

Funnel Scientist

*Funnel Science Internet Marketing LLC *

*Office* 972-867-3100 <%28972%29%20867-3100> *| Cell* 214-625-9023 *| *
FunnelScience.com <http://www.funnelscience.com/>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Downloading SQLite issue

[Alex Fender]

After I download and unzip. I click and open Sqlite3 by double clicking.
When I open the file, it says damaged and move to the trash.

On Thu, Jul 7, 2016 at 9:27 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 7/7/16, Alex Fender <a...@funnelscience.com> wrote:
> > When I download SQLite for Mac, when I extract the file out of the zip
> and
> > open, the files are damaged. How can I download a file that is not
> damaged?
>
> I click on the link using Firefox and it downloads and unzips to my
> Mac just fine.
>
> What are you doing that is not working for you?
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Alex Fender

Funnel Scientist

*Funnel Science Internet Marketing LLC *

*Office* 972-867-3100 <%28972%29%20867-3100> *| Cell* 214-625-9023 *| *
FunnelScience.com <http://www.funnelscience.com/>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Downloading SQLite issue

[Alex Fender]

When I download SQLite for Mac, when I extract the file out of the zip and
open, the files are damaged. How can I download a file that is not damaged?

-- 

Alex Fender

Funnel Scientist

*Funnel Science Internet Marketing LLC *

*Office* 972-867-3100 <%28972%29%20867-3100> *| Cell* 214-625-9023 *| *
FunnelScience.com <http://www.funnelscience.com/>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] rand_s and Windows 2000

[Alex Alabuzhev]

Hi guys,

SQlite 3.12 and newer uses rand_s in winRandomness() implementation.

rand_s depends <https://msdn.microsoft.com/en-us/library/sxtz2fa8.aspx> on
RtlGenRandom <https://msdn.microsoft.com/library/windows/desktop/aa387694> API
(aka Advapi32.dll::SystemFunction036), which is only available in Windows
XP and later.

Could you please consider using rand() instead, or, say, using rand_s()
only if SystemFunction036 is present in advapi32.dll, or any other
apprropriate way to remove this dependency?

Thanks.

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

[sqlite] setting cache_size to 0 right after opening a connection is ignored

[Ousherovitch Alex]

This issue seems to have been introduced in one of the recent releases - 
setting PRAGMA cache_size to zero is a no-op and the default (2000 pages) will 
be used if it's done right after opening a database.
The reason seems to be that sPragmaNames entry for cache_size has mPragFlag 
value of zero, it probably has to be PragFlag_NeedSchema, to avoid this issue.

[sqlite] Mozilla wiki 'avoid SQLite'

[Alex Bowden]

> On 15 Jun 2015, at 15:44, Scott Robison  wrote:
> 
> Too many of these technology based discussions (whether languages or
> operating systems or text editors or database engines or whatever) break
> down into almost a religious fervor of "this is the one true

You mean like your comment from this morning?

"The use of languages higher than C result in slow bloated code. A language 
that calls a language that calls a language. Simple programs become 
multi-megabyte resource hogs.?

Frame it.  Put it over your bed.  And use it to remind you to put your brain in 
gear, before opening your mouth.

[sqlite] Mozilla wiki 'avoid SQLite'

[Alex Bowden]

Oh dear.  So you think that a high level language is one that does things by 
calling a lower level language?  Stop embarrassing yourself.

A high level language is one where the language designers are free to use 
whatever structural concepts best fit the problems that the language is 
designed to address,  whereas a low level language is one where the language 
designers are restricted to using structural concepts that map directly onto an 
assumed hardware model.

The assumption that a low level language compiles to assembler is nonsense.  I 
don?t remember any of them doing this before C, and even later,  many compilers 
still compile directly to machine code.

Assuming that higher level languages compile to lower level languages is also 
nonsense.  And even for ones that do, and a classic example of a language 
designed to do this would be Ratfor, then provided that the lower level 
language is compiling to machine code, then the only thing that is being slowed 
is the build process.  Not runtime execution.   

Now 20 years ago, folk like you, but better informed, would have argued that 
well written lower level language code was more efficient than higher level 
language code.  And sometimes they would have been correct.  But note the 
qualification of well written.  And even then, this was debatable.  If a 
language provides efficient higher level data structures, or fine grain 
synchronisation of multiple threads,  then the user is more likely to use them 
than if they have to create them themselves for each platform.

However today, it is rarely true.

Good modern optimisers can achieve efficiency that few, if any, low level 
language programmers will ever achieve.

And the thing is, that those compilers can do far broader optimisation of 
higher level languages that don?t expose features like the ability to write to 
a random address.  The optimisation potential of C, beyond very local 
optimisation, is minimal.

I?ve been running the same little benchmark across every hardware and language 
platform that I?ve used, for over 30 years.  

And I confess, I was very surprised when Java passed C in about 2005.  And yes, 
that was Java compiled to byte codes with runtime JIT compilation of the byte 
codes to machine code.  But it was faster.

The trend hasn?t reversed.



> On 14 Jun 2015, at 23:42, Scott Doctor  wrote:
> 
> On 6/14/2015 3:00 PM, Simon Slavin wrote:
>> The result is that that higher level the language you write in, the better.
> 
> I disagree. The use of languages higher than C result in slow bloated code. A 
> language that calls a language that calls a language. Simple programs become 
> multi-megabyte resource hogs. I agree that C compilers are able to optimize 
> assembler code to a level that hand-coded assembler probably could not 
> achieve. The problem is that higher level languages are not compiling to 
> assembler, but compiling to the language below it.
> 
> 
> Scott Doctor
> scott at scottdoctor.com
> --
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Alex Mandel]

On 03/31/2015 05:17 PM, Mark Romero wrote:
> Hey Everyone:
> 
> Was there a recent Windows 7 64-bit update that affected SQLite?
> 
> A few months ago one of the programs that I use (Lightroom) started to have
> long periods where it would stop responding. It wouldn't crash, but it
> would just keep my CPU pegged at 100% and the program would stop
> responding. This would happen at random times.
> 
> It never acted like this before, and I think it started acting funny just
> right after a win 7 64-bit automatic update.
> 
> The SLQlite "server" is built into Lightroom, so I have no access to the
> code.
> 
> One thing that is strange is that Adobe has another program (Adobe Camera
> Raw) which is nearly identical to lightroom except that Lightroom uses
> SQLite to store data while Adobe Camera Raw writes information into a
> separate file (an XMP Side Cart file). ACR runs fine, while Lightroom has
> problems.
> 
> So if you know of a recent windows 7 update that may affect SQLite, please
> let me know. maybe there are drivers I can download somewhere to fix the
> problem.
> 
> Thanks in advance.
> 

Do you have trouble using SQlite outside of Lightroom?
If not, then you should pursue this issue with the makers of Lightroom.
SQlite is not a server, nor is it likely to even be in the registry of
windows unless you have the sqlite command line application.

This doesn't rule out that a Windows update caused the issue (it's well
known that such updates can cause issues), however the right place to
start asking questions is the exact software in use.

Thanks,
Alex

Re: [sqlite] Using ICU case folding support

[Alex Loukissas]

On Wed, Mar 19, 2014 at 8:36 AM, Alex Loukissas <a...@maginatics.com> wrote:

> On Wed, Mar 19, 2014 at 8:32 AM, Dan Kennedy <danielk1...@gmail.com>wrote:
>
>> On 03/19/2014 09:44 PM, Aleksey Tulinov wrote:
>>
>>>
>>> I've created test database:
>>>
>>>
>>> sqlite> CREATE TABLE test (x COLLATE NOCASE);
>>> sqlite> INSERT INTO test VALUES ('s');
>>> sqlite> INSERT INTO test VALUES ('S');
>>> sqlite> INSERT INTO test VALUES ('ё'); -- Russian e with diacritic
>>> sqlite> INSERT INTO test VALUES ('Ё'); -- Russian E with diacritic
>>>
>>> Then created index in ICU-disabled SQLite version:
>>>
>>> sqlite> SELECT 'ё' LIKE 'Ё';
>>> 0
>>> sqlite> .schema
>>>
>>> CREATE TABLE test (x COLLATE NOCASE);
>>> sqlite> CREATE INDEX idx_x ON test (x);
>>>
>>> Then tried it in ICU-enabled SQLite version:
>>>
>>
>> ICU-enabled or nunicode-enabled?
>>
>> ICU does not modify the behaviour of existing collation sequences. So
>> there is no problem there (apart from the original problem - that the ICU
>> extension does not provide anything that can be used to create a
>> case-independent collation sequence).
>>
>> An index is a sorted list. And queries like this:
>>
>>
>>  sqlite> SELECT * FROM test WHERE x = 'ё';
>>>
>>
>> do a binary search of that list to find keys equal to 'ё'. But to do a
>> binary search of an ordered list, you need to be using a comparison
>> function compatible with that used to sort the list in the first place. Say
>> I have the following list, sorted using a unicode aware NOCASE collation:
>>
>>   (Ä, ä, Ë, ë, f)
>>
>> Also assume that all characters in the list have umlauts adorning them.
>>
>> Then I open the db using regular SQLite and try searching for "ä".
>> Obviously the binary search fails - the first comparison compares the seek
>> key "ä" with "Ë", incorrectly concludes that the key "ä" is larger than "Ë"
>> and goes on to search the right-hand side of the index. The search fails.
>>
>> Then say this search is part of a delete operation to remove a row from
>> the database. The table row itself might be removed correctly, but the
>> corresponding index key is not - because a search fails to find it. At that
>> point you have an inconsistent table and index. A corrupt database.
>>
>> In the future, we might have a similar problem in FTS. FTS offers a
>> home-grown tokenizer named "unicode61" that folds case in the same
>> unicode-aware way as nunicode. If the unicode standard changes to define
>> more pairs of case equivalent characters, we will not be able simply
>> upgrade "unicode61". For the same reasons - modifying the comparison
>> function creates an incompatible system. Instead, we would name it
>> "unicode62" or similar, to be sure that databases created using the old
>> version continue to use it.
>>
>>
>> Dan.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> Thanks everyone for your comments. IIUC, the correct way of going about
> what I want to do is to use BINARY collation on the column I'm interested
> in and when I want to do unicode-aware case-insensitive lookups, they
> should look something like SELECT * FROM table WHERE LOWER(col_name) =
> LOWER(key), correct? It seems like with ICU support, LOWER( ) will call
> u_foldCase under the covers, which is what I want.
>
> Alex
>
>
Actually, it looks like the ICU extension doesn't provide what I want here
and the preferred way forward is to define my own collation sequence, as
described in http://www.sqlite.org/datatype3.html and use direct calls to
ICU caseCompare (which does case folding underneath).

I would suggest that a new collation sequence is added to sqlite with ICU
extension enabled (e.g. ICU_NOCASE) - will be very useful IMHO. Is there a
proper way of filing such feature requests?

Thanks!
Alex

-- 
Alex Loukissas

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

Re: [sqlite] Using ICU case folding support

[Alex Loukissas]

On Wed, Mar 19, 2014 at 8:32 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 03/19/2014 09:44 PM, Aleksey Tulinov wrote:
>
>>
>> I've created test database:
>>
>>
>> sqlite> CREATE TABLE test (x COLLATE NOCASE);
>> sqlite> INSERT INTO test VALUES ('s');
>> sqlite> INSERT INTO test VALUES ('S');
>> sqlite> INSERT INTO test VALUES ('ё'); -- Russian e with diacritic
>> sqlite> INSERT INTO test VALUES ('Ё'); -- Russian E with diacritic
>>
>> Then created index in ICU-disabled SQLite version:
>>
>> sqlite> SELECT 'ё' LIKE 'Ё';
>> 0
>> sqlite> .schema
>>
>> CREATE TABLE test (x COLLATE NOCASE);
>> sqlite> CREATE INDEX idx_x ON test (x);
>>
>> Then tried it in ICU-enabled SQLite version:
>>
>
> ICU-enabled or nunicode-enabled?
>
> ICU does not modify the behaviour of existing collation sequences. So
> there is no problem there (apart from the original problem - that the ICU
> extension does not provide anything that can be used to create a
> case-independent collation sequence).
>
> An index is a sorted list. And queries like this:
>
>
>  sqlite> SELECT * FROM test WHERE x = 'ё';
>>
>
> do a binary search of that list to find keys equal to 'ё'. But to do a
> binary search of an ordered list, you need to be using a comparison
> function compatible with that used to sort the list in the first place. Say
> I have the following list, sorted using a unicode aware NOCASE collation:
>
>   (Ä, ä, Ë, ë, f)
>
> Also assume that all characters in the list have umlauts adorning them.
>
> Then I open the db using regular SQLite and try searching for "ä".
> Obviously the binary search fails - the first comparison compares the seek
> key "ä" with "Ë", incorrectly concludes that the key "ä" is larger than "Ë"
> and goes on to search the right-hand side of the index. The search fails.
>
> Then say this search is part of a delete operation to remove a row from
> the database. The table row itself might be removed correctly, but the
> corresponding index key is not - because a search fails to find it. At that
> point you have an inconsistent table and index. A corrupt database.
>
> In the future, we might have a similar problem in FTS. FTS offers a
> home-grown tokenizer named "unicode61" that folds case in the same
> unicode-aware way as nunicode. If the unicode standard changes to define
> more pairs of case equivalent characters, we will not be able simply
> upgrade "unicode61". For the same reasons - modifying the comparison
> function creates an incompatible system. Instead, we would name it
> "unicode62" or similar, to be sure that databases created using the old
> version continue to use it.
>
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Thanks everyone for your comments. IIUC, the correct way of going about
what I want to do is to use BINARY collation on the column I'm interested
in and when I want to do unicode-aware case-insensitive lookups, they
should look something like SELECT * FROM table WHERE LOWER(col_name) =
LOWER(key), correct? It seems like with ICU support, LOWER( ) will call
u_foldCase under the covers, which is what I want.

Alex


-- 
Alex Loukissas

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

Re: [sqlite] Using ICU case folding support

[Alex Loukissas]

On Tue, Mar 18, 2014 at 12:39 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 03/19/2014 01:22 AM, Alex Loukissas wrote:
>
>> Hello,
>>
>> I'm trying to use the ICU extension and it looks like what I want to do is
>> not very clear from the documentation. I would like to switch from my
>> current usage, where my column declaration is as follows:
>>
>> CREATE TABLE demo("name text not null COLLATE NOCASE");
>>
>> As noted in the documentation, this is not Unicode-aware, which is why I
>> turned to the ICU extension. What should be the equivalent usage where
>> sqlite does case folding using ICU?
>>
>
> I don't think there is a way to do that with the current SQLite ICU
> extension.
>
> Dan.
>

I suppose I can declare the column as BINARY and use LOWER( ) in my select
statements. Browsing through the code though, I do see uses of u_foldCase
in certain places, which leads me to believe that what I want may be
already there. I'll try to unit test this.


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



-- 
Alex Loukissas

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

[sqlite] Using ICU case folding support

[Alex Loukissas]

Hello,

I'm trying to use the ICU extension and it looks like what I want to do is
not very clear from the documentation. I would like to switch from my
current usage, where my column declaration is as follows:

CREATE TABLE demo("name text not null COLLATE NOCASE");

As noted in the documentation, this is not Unicode-aware, which is why I
turned to the ICU extension. What should be the equivalent usage where
sqlite does case folding using ICU?

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

[sqlite] failure to build static library with -DSQLITE_ENABLE_ICU

[Alex Loukissas]

2014-03-14 0:30 GMT+01:00 Alex Loukissas :
> I have verified that adding the said include fixes the issue (1-line
patch):
>
> diff --git sqlite3.c sqlite3.c
> index 7c6d3e2..a9399e4 100644
> --- sqlite3.c
> +++ sqlite3.c
> @@ -145656,6 +145656,7 @@ SQLITE_API int sqlite3_rtree_init(
>  #include 
>  #include 
>  #include 
> +#include 
>
>  /* #include  */
>
> Hope this helps others that run into this issue.

> This sounds strange to me (I compiled the SQLite ICU extension
> for Cygwin, and didn't run into this issue).

> According to the ICU documentation,  (which is
> just above the ) includes , which
> in turn includes :
 ><http://icu.sourcearchive.com/documentation/4.2/utypes_8h.html>
> So, the current order of includes should just work fine.
>
> What ICU version are you using? I used ICU 4.8 and 5.1, neither
> of them have the problem you describe. Sorry.
>
> Regards,
>  Jan Nijtmans

Hi,

I've been using icu4c, ver 52.1 (most current), which I build from source
into a static library, using the following options (copying from my bash
build script):

CONFIG_OPTS="--enable-static --disable-shared --disable-strict \
--disable-extras --disable-samples --disable-tests"
EXTRA_FLAGS="-DU_HAVE_NL_LANGINFO_CODESET=0 \
-DU_USING_ICU_NAMESPACE=0 \
-DU_CHARSET_IS_UTF8=1 \
-DU_NO_DEFAULT_INCLUDE_UTF_HEADERS=1 \
-DUCONFIG_NO_LEGACY_CONVERSION=1 \
-DUCONFIG_NO_TRANSLITERATION=0 \
-D__STDC_INT64__ \
-DU_TIMEZONE=0"
GCC_FLAGS="-fno-short-wchar -fno-short-enums"

I'll try to see whether the pointer you gave is still valid in 52.1 and
perhaps one of my macros is blocking an include with an ifdef.

Thanks!
Alex

-- 
Alex Loukissas

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

[sqlite] failure to build static library with -DSQLITE_ENABLE_ICU

[Alex Loukissas]

Hello,

I have been trying to build sqlite 3.8.4.1 using the ICU extension and have
seem to have hit an apparent bug in the sqlite code. After much debugging,
it looks like the header  is missing, which causes warnings
like the following to appear:

sqlite3.c: In function 'icuLikeCompare':
sqlite3.c:145704:5: warning: implicit declaration of function
'U8_NEXT_UNSAFE' [-Wimplicit-function-declaration]
 U8_NEXT_UNSAFE(zPattern, iPattern, uPattern);
 ^
sqlite3.c:145725:11: warning: implicit declaration of function
'U8_FWD_1_UNSAFE' [-Wimplicit-function-declaration]
   U8_FWD_1_UNSAFE(zString, iString);
   ^
sqlite3.c: In function 'icuLikeFunc':
sqlite3.c:145804:5: warning: implicit declaration of function 'U8_NEXT'
[-Wimplicit-function-declaration]
 U8_NEXT(zE, i, nE, uEsc);
 ^

This, in turn, manifests as undefined symbol errors in the linker:

./.libs/libsqlite3.a(sqlite3.o): In function `icuLikeCompare':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145704:
undefined reference to `U8_NEXT_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145743:
undefined reference to `U8_FWD_1_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145752:
undefined reference to `U8_NEXT_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145725:
undefined reference to `U8_FWD_1_UNSAFE'
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145736:
undefined reference to `U8_FWD_1_UNSAFE'
./.libs/libsqlite3.a(sqlite3.o): In function `icuOpen':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:146257:
undefined reference to `U8_NEXT'
./.libs/libsqlite3.a(sqlite3.o): In function `icuLikeFunc':
/home/alex/src/vulcan/build/workdir/sqlite-autoconf-3080401/sqlite3.c:145804:
undefined reference to `U8_NEXT'
collect2: error: ld returned 1 exit status
make[1]: *** [sqlite3] Error 1
make: *** [sqlite] Error 2


I have verified that adding the said include fixes the issue (1-line patch):

diff --git sqlite3.c sqlite3.c
index 7c6d3e2..a9399e4 100644
--- sqlite3.c
+++ sqlite3.c
@@ -145656,6 +145656,7 @@ SQLITE_API int sqlite3_rtree_init(
 #include 
 #include 
 #include 
+#include 

 /* #include  */

Hope this helps others that run into this issue.

-- 
Alex Loukissas

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

Re: [sqlite] Direct PostgreSQL to SQLite connection?

[Alex Mandel]

http://www.gaia-gis.it/OpenLite/
ogr2ogr from gdal.org can probably do it on the command line
Spatialite I think has a Virtual Postgres table tool too.
(You can probably ignore that these are mostly for spatial data, should
work fine on regular tables)

Honestly a small python script to read from sqlite and insert to
Postgres is quite simple, especially if the schema in Postgres already
matches.

Open Sqlite connection
Open Postgres connection
Query Sqlite table
Prepare statement insert to postgres
Close Connections

I would encourage you to keep your working copy in Postgres and use your
SQLite files as archives.

Enjoy,
Alex


On 09/25/2013 11:44 PM, Vivien Malerba wrote:
> Hi!
> 
> Using Libgda (http://www.gnome-db.org) you can create virtual connections
> which "aggregate" several other connections (meaning all the tables from
> all the connections can be used in single SQL queries and you could copy
> data using an "INSERT INTO XXX SELECT ... FROM YYY" query), in your case
> you could aggregate a PostgreSQL and a SQLite connection.
> 
> Hope it helps,
> Regards
> Vivien
> 
> 
> On 25 September 2013 18:45, joe.fis...@tanguaylab.com <
> joe.fis...@tanguaylab.com> wrote:
> 
>> Looking for a solution to connect directly from a PostgreSQL database to a
>> SQLite database. We need PostgreSQL for it's Window/Analytic functions and
>> other features that SQLite doesn't have. Yet, SQLite is great for
>> collecting data.
>>
>> Every one of our experiments adds 300MB to the SQLite database. Rather
>> than exporting to CSV and then importing to PostgreSQL, we'd like to
>> directly access SQLite from PostgreSQL.
>>
>> Does anyone have good experience using 'Foreign Data Wrapper for sqlite'
>> or another method?
>> https://github.com/gleu/**sqlite_fdw <https://github.com/gleu/sqlite_fdw>
>>
>>
>> Joe Fisher
>> Oregon State University
>>
>> __**_
>> 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
> 

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

> State why you don't
> like it and move on. Don't contribute any code that might address the idea.
> The rest? It is not useful.

It is useful.  It help stops people who don't understand the concept of 
relational, screwing up the system.

You just disagree with that.


On 7 Jul 2013, at 22:24, Scott Robison  wrote:

> On Sun, Jul 7, 2013 at 11:34 AM, Keith Medcalf  wrote:
> 
>> 
>> Despite the long diatribes you have not indicated a single case in which
>> the set ordinal of the row would be of any use whatsoever.
>> 
>> For 40 years we have gotten on without it, so yes, it is only for you
>> newbies that somehow think there is a use for it, and for 40 years no one
>> has ever come upon a real need to have set ordinals generated by the
>> database engine as part of the cursor processing (for various definitions
>> of database engine).
>> 
>> Kind of makes you wonder what you might have overlooked or are assuming
>> incorrectly doesn't it?
>> 
>> 
> If you wish to posit that a row number function is not useful, that's fine.
> You don't have to like it or support its inclusion. To claim that there has
> not been a single case described where it would be of any use whatsoever
> clearly demonstrates that you have not bothered reading the messages you're
> replying to! The cases have been described, you simply disagree with them.
> There *is* utility in being able to have a row or rank number function. For
> example, lets say you are Billboard Magazine and you keep track of record
> sales. On a weekly basis you generate a list of the top 40 selling records.
> One of your customers wants that list in descending order. There are
> certainly ways you can accomplish this without a rank function, but a rank
> function can make the SQL clearer, easier to maintain.
> 
> At my last job I used Microsoft SQL Server (as that was technology selected
> before I was hired) and used a rank number capability provided in T/SQL as
> part of a query to generate a list of files in a particular order. The
> order was important in my use case (prioritization of a set of files
> comprising multiple terabytes of data that needed to be synchronized, but
> where not all data was equally important). I didn't need to send all the
> sort criteria to each remote site, I just needed to ensure the order was
> maintained. Could I have done it in a different way? Yes. But as mentioned
> on multiple occasions, the idea of the rank or row number function is not
> that there is no other way to accomplish the task, it is about convenience.
> 
> Now, you are free to dislike my approach to solving the problem. I'm sure
> there are better ways to accomplish what needed to be done. Regardless of
> that, what I did was convenient and got the job done. Had that capability
> not been available to me, I could have done it in another way. No one is
> arguing that there isn't another way to accomplish the stated task.
> 
> Regardless of *ANY OF THE ABOVE*: How about exercising some common courtesy
> and not resort to denigration of another person's knowledge or apparent
> skills or lack thereof. You don't like the idea, fine. State why you don't
> like it and move on. Don't contribute any code that might address the idea.
> The rest? It is not useful.
> 
> SDR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

The SQL standard has always been a moving feast, chasing the field 
implementations, perfectly capable of going back on it's earlier mistakes,  the 
main purpose of which, on a good day, is to promote standardisation of SQL 
implementations and try and keep to the Relational Theory model where practical 
considerations allow.

So, if the SQL standard has drifted toward requiring "… in the order in which 
they are defined in the table definition…"  to be meaningful,   then this is an 
oversight that would likely be corrected when somebody has an in the field SQL 
database which, correctly, enforces no such concept.

People should not be encouraged to become more dependent on the use of such 
temporary misfeatures.

In context, the particular focus of your objection to the relational approach,  
seems irrelevant.

>> "sort by *" would imply that the order of the columns returned by '*' is
>> meaningful, which it is not.  "sort by the arbitrary order produced by
>> 'select *'" isn't even deterministic.  
> 
>  In SQL column order *is* deterministic, so the sort order would also
>  be deterministic.  Likely meaningless, but still deterministic.


Sort order isn't necessarily deterministic even if we know the column order.  
So the possibility that we may not know it, makes life no worse.
 

On 2 Jul 2013, at 05:30, Jay A. Kreibich  wrote:

> On Mon, Jul 01, 2013 at 10:52:20PM -0400, James K. Lowden scratched on the 
> wall:
> 
>> "select *" is shorthand for "all columns". You'll note that what's
>> returned isn't some kind of special '*' column, but all columns.  The
>> order in which the columns are returned isn't meaningful because the
>> colums have labels -- names -- to tell you which is which.  Rearranging
>> the column order doesn't change the answer.  
> 
>  That's not quite true.
> 
>  What you say is more or less true in pure Relational Theory.  Under
>  Relational Theory, relational attributes (columns) are a proper set.
>  The columns have no defined order (just as rows have no defined
>  order), and can only be definitively reference by name.
> 
>  In SQL, columns are *not* a set.  The order of the columns in any SQL
>  query or operation is strictly defined.  Columns cannot be referenced
>  by name, because SQL allows name-less columns (SELECT 1, 1, 1;) and
>  multiple columns with the same name (SELECT 1 A, 1 A, 1 A;).  SQL
>  doesn't even strictly define the column name for a calculated column
>  (SELECT avg( 1 )) and allows the DB to make up its own names.  SQLite
>  used to have several PRAGMAs to control short and long column names.
> 
>  Rather, in SQL, a column is definitively defined by its positional
>  index in the table or result set.  This is also why so many SQL APIs
>  allow you to fetch column values by index, rather than by name (which
>  would be a totally broken and dangerous API if columns could move
>  around).  It gets pretty messy...  The SQL standard goes to some
>  length to define a specific column order for stuff like JOIN operations,
>  including edge-case details like NATURAL JOINs where the number of
>  columns is reduced and somewhat ambiguously named.
> 
>  While rearranging the column order may not functionally change the
>  answer, a database is not given that flexibility in SQL.  For
>  example, "SELECT *" *must* return the columns in the order they are
>  defined in the table definition.  It isn't that most databases just
>  happen to do this-- the column order is actually predicated by the
>  standard.
> 
>> "sort by *" would imply that the order of the columns returned by '*' is
>> meaningful, which it is not.  "sort by the arbitrary order produced by
>> 'select *'" isn't even deterministic.  
> 
>  In SQL column order *is* deterministic, so the sort order would also
>  be deterministic.  Likely meaningless, but still deterministic.
> 
> 
>   -j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
> but showing it to the wrong people has the tendency to make them
> feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)

If you approach the government france and explain that you're not really very 
good at French,  but if they made the following list of Chinese language 
inclusions into French, then it would make it easier for you to write your 
"thank you letter" to your auntie Marie,  than I'm sure they would oblige.

And even if they didn't it wouldn't make you wrong.

You are proposing changes to a relational database management system that 
damage its relationality, on the basis that you don't understand how to use 
relational databases properly, and it would make your life easier.

In defending maintaining the relationality, I don't really need to be right.  
The onus is on you to justify the change that you propose.


On 1 Jul 2013, at 16:36, Tony Papadimitriou <to...@acm.org> wrote:

> OK, you don't agree.  Your opinion!  (That doesn't make you right, though!)
> 
>> I'm sure there will be a SQL engine somewhere that will do it for you.
> 
> We're talking about SQLite here, aren't we?  If some other database can do 
> it, then you should also consider that it may also be able to do what this 
> 'row' function.  (e.g. MySQL).  So, your point it moot.
> 
>>> So, why make it sound like I don't know what I'm talking about?
>> I think you beat me to it.
> 
> No comment!
> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 6:17 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)

[Alex Bowden]

> Please!  Just because you can select something doesn't mean you have to be 
> able to sort by it.

There are a small number of exceptions,  each of which is a bodge.

But some bodges are worth the impact.

>  Can you sort by *

* is a very useful and largely harmless bodge.

There is fundamentally no reason why you shouldn't be able to to sort by *.  
But people usually care about the order of field in a sort.  

> (select * by table sort by *)?  

I'm sure there will be a SQL engine somewhere that will do it for you.

It would require no additional documentation or code.  In fact it might require 
extra, to prevent it.  It's pointless,  but benign.

> So, why make it sound like I don't know what I'm talking about?

I think you beat me to it.

> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 2:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOTthe rowid)
> 
>> This would not be something you would sort by.
> 
> And what if I do?
> 
>> It should be assigned a value only during final 'display' of the query after 
>> all 'sorts' of operations are done with.
> 
> Oh great.  So the user is supposed to understand the implementation, in order 
> to understand what the results will be.
> 
> 
> This would be just another nail in the coffin of relationality and 
> simplicity, on a minor whim.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

> This would not be something you would sort by.  

And what if I do?

> It should be assigned a value only during final 'display' of the query after 
> all 'sorts' of operations are done with.

Oh great.  So the user is supposed to understand the implementation, in order 
to understand what the results will be.


This would be just another nail in the coffin of relationality and simplicity, 
on a minor whim.

On 1 Jul 2013, at 11:01, Tony Papadimitriou <to...@acm.org> wrote:

> 
> -Original Message- From: Alex Bowden
> Sent: Monday, July 01, 2013 12:46 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there a way to return the row number? (NOT therowid)
> 
> 
> I can't wait to try
> 
> order by row_number desc
> 
> 
> On 1 Jul 2013, at 10:33, Tony Papadimitriou <to...@acm.org> wrote:
> 
>> Is there a function (or method), e.g., row(), to return the sequence number 
>> of the selected row?  This is not the same as ROWID.  row() should give a 
>> sequence number always starting from 1 up the to the number of rows returned 
>> by the view/select etc.
>> 
>> If not, then please add to the wish list.
>> 
>> TIA
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to return the row number? (NOT the rowid)

[Alex Bowden]

I can't wait to try

order by row_number desc


On 1 Jul 2013, at 10:33, Tony Papadimitriou  wrote:

> Is there a function (or method), e.g., row(), to return the sequence number 
> of the selected row?  This is not the same as ROWID.  row() should give a 
> sequence number always starting from 1 up the to the number of rows returned 
> by the view/select etc.
> 
> If not, then please add to the wish list.
> 
> TIA 
> ___
> 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] Load a Sqlite Database on Disk as an In-memory Database

[Alex Cheng]

Hi guys,

I want to write a program to load a sqlite database on disk as an in-memory
database, for sake of reducing number of disk I/O operations. Do you know
how to achieve it?

Thank you in advance.

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

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

[Alex Bowden]

It's not your problem.

If the idiot user wants a slow machine,  that's his choice.

It certainly isn't your job to turn off sync in order to hide how slow Windows 
is.

On 30 Nov 2012, at 17:41, David de Regt  wrote:

> Hey all.  I've been struggling with a basic perf issue running the same code 
> on Windows vs. iOS and OSX.
> 
> Basic query set:
> CREATE TABLE test (col1 int, col2 text);
> [loop 500 times]: INSERT INTO TEST (col1,col2) VALUES (4,'test4')
> 
> I'm coding this using the default C amalgamation release and using 
> prepare/etc. on all platforms in the exact same way (same very simple 
> DB-access class I made).  I realize that using a transaction around this 
> would vastly improve perf, but given the atomic nature of the app that this 
> test is simulating, it won't work to wrap it into transactions, so my goal is 
> to improve the atomic performance.  These are all being run on the same 
> Macbook Pro, with an SSD, running Windows via boot camp, OSX natively, and 
> iOS via the iOS simulator:
> 
> With defaults (pragma sync = on, default journal_mode):
> Windows: 2500ms
> iOS: 300ms
> OSX: 280ms
> 
> With pragma sync = off, journal_mode = memory:
> Windows: 62ms
> iOS: 25ms
> OSX: 25ms
> 
> Turning off sync doesn't make me feel warm and fuzzy about our lost-power 
> scenario, so with sync on, it seems like something must be fishy for it to be 
> ~8-9x slower than the other platforms.  Is there something ridiculous about 
> the windows file system performance that hoses sqlite's open/read/write/close 
> transaction cycle?  Is there anything I can do, or just accept it and move 
> on?  With how that scales up, we may need to move to something like using 
> embedded MySQL or LocalDB on Windows to get the same performance as we see 
> with SQLite on other platforms, which seems quite ridiculous.
> 
> Thanks!
> -David
> ___
> 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] System.Data.SQLite.dll locks files forever starting fromv1.0.82.0

[Alex Reitbort]

>Alex Reitbort wrote:
>> 
>> When running this sample with System.Data.SQLite.dll v1.0.81 everything
>> works ok.
>> 
>> When running this sample with System.Data.SQLite.dll v1.0.82, it fails on
>> File.Delete line with IOException: The process cannot access the file
>> 'alex.db' because it is being used by another process.
>> 
> 
>As of 1.0.82.0, the command also needs to be disposed before the connection
>will be fully closed.
>
>--
>Joe Mistachkin

Thank you.

Alex Reitbort
Software Developer
Skyline Software Systems Inc.
www.skylineglobe.com


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

[sqlite] Assertion fault with nested select statement

[Alex Busenius]

Hello,


I think I've found a bug in SQLite3, I get an assertion fault (or 
segmentation fault in release build) when running a somewhat complicated 
nested select statement:



% sqlite3 -init sqlite3-bug.sql bla.sqlite3
-- Loading resources from sqlite3-bug.sql
sqlite3: sqlite3.c:65666: sqlite3VdbeExec: Assertion `u.an.pC!=0' failed.
zsh: abort  sqlite3 -init sqlite3-bug.sql bla.sqlite3


It is 100% reproduceable. I stripped down my statement and schema I used 
to create a small example. See bottom of this mail for the content of 
sqlite3-bug.sql.


I'm running 64-bit linux.


Regards,

Alex




GDB backtrace:
Program received signal SIGABRT, Aborted.
0x77535c15 in raise () from /lib64/libc.so.6
(gdb) bt
#0  0x77535c15 in raise () from /lib64/libc.so.6
#1  0x7753708b in abort () from /lib64/libc.so.6
#2  0x7752ec7e in __assert_fail_base () from /lib64/libc.so.6
#3  0x7752ed22 in __assert_fail () from /lib64/libc.so.6
#4  0x77b997e1 in sqlite3VdbeExec () from /usr/lib64/libsqlite3.so.0
#5  0x77b70927 in sqlite3_step () from /usr/lib64/libsqlite3.so.0
#6  0x004052c4 in shell_exec.constprop.7 ()
#7  0x00405d49 in process_input ()
#8  0x00402783 in main ()



Compiled using:
./configure --prefix=/usr --build=x86_64-pc-linux-gnu 
--host=x86_64-pc-linux-gnu --mandir=/usr/share/man 
--infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc 
--localstatedir=/var/lib --libdir=/usr/lib64 
--disable-dependency-tracking --disable-static 
--enable-dynamic-extensions --enable-readline --enable-threadsafe




% sqlite3 -version
3.7.14.1 2012-10-04 19:37:12 091570e46d04e84b67228e0bdbcd6e1fb60c6bdb



% gcc --version
gcc (Gentoo 4.6.3 p1.6, pie-0.5.2) 4.6.3



% uname -a
Linux inspiron 3.4.11-tuxonice #1 SMP PREEMPT Sat Sep 29 18:58:19 CEST 
2012 x86_64 Intel(R) Core(TM)2 Duo CPU P7450 @ 2.13GHz GenuineIntel 
GNU/Linux




% cat ~/.sqliterc
-- SQLite3 command line configuration

.timer   ON
.headers ON
.modecolumn
.width   0 0 0 0 0  0 0 0 0 0  0 0 0 0 0  0 0 0 0 0

-- vim: ft=sql



% cat sqlite3-bug.sql
-- schema
CREATE TABLE AAA (
   aaa_id   INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE RRR (
rrr_id  INTEGER PRIMARY KEY AUTOINCREMENT,
rrr_dateINTEGER NOT NULL,
rrr_aaa INTEGER
);
CREATE TABLE TTT (
ttt_id  INTEGER PRIMARY KEY AUTOINCREMENT,
target_aaa  INTEGER NOT NULL,
source_aaa  INTEGER NOT NULL
);


-- insert
insert into AAA (aaa_id)
values (2);


insert into TTT (ttt_id, target_aaa, source_aaa)
values (4469, 2, 2);
insert into TTT (ttt_id, target_aaa, source_aaa)
values (4476, 2, 1);


insert into RRR (rrr_id, rrr_date, rrr_aaa)
values (0, 0, NULL);
insert into RRR (rrr_id, rrr_date, rrr_aaa)
values (2, 4312, 2);


-- segfault
SELECT i.aaa_id,
   (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END)
FROM TTT t
   ) AS segfault
FROM (SELECT curr.rrr_aaa as aaa_id
  FROM RRR curr
   -- you also can comment out the next line -- it causes 
segfault to happen after one row is outputted

   INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id)
   LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < 
curr.rrr_date)

  GROUP BY curr.rrr_id
  HAVING r.rrr_date IS NULL
) i;

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

[sqlite] System.Data.SQLite.dll locks files forever starting from v1.0.82.0

[Alex Reitbort]

Hello,

 

I recently updated my project to use the latest version of
System.Data.SQLite (1.0.82.0). But it seems that there is a bug in this
version. After I open an sqllite database and perform any query, the
database stays locked until the process ends.

 

Here is a C# code sample to reproduce the problem:

 

 

using (var conn = new SQLiteConnection("Data Source=alex.db"))

{

conn.Open();

var cmd = conn.CreateCommand();

cmd.CommandText = "select 1;";

cmd.ExecuteScalar();

conn.Close();

}

GC.Collect(2,GCCollectionMode.Forced);

File.Delete("alex.db");

 

When running this sample with System.Data.SQLite.dll v1.0.81 everything
works ok.

When running this sample with System.Data.SQLite.dll v1.0.82, it fails on
File.Delete line with IOException: The process cannot access the file
'alex.db' because it is being used by another process.

 

Can you help me to fix this problem?

 

Alex Reitbort

Software Developer

Skyline Software Systems Inc.

www.skylineglobe.com

 

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

Re: [sqlite] Parsing sqlitedb file.

[Alex Caithness]

Actually, you can recover deleted data from SQLite databases - we've been doing 
it for a while (a number of years). There's at least one product to do it for 
you (http://ccl-forensics.com/epilog)  and there have been a number of 
presentations I have given and been at which discussed the subject.



On 11 Jul 2012, at 7:32am, rsharnagate  wrote:

> Actually I want to recover deleted data from sqlitedb file. Deleted data
> means data from freelist page and unallocated space.
> That's why I want to parse it.

Sorry, but it's not possible to parse unused areas of pages because you lack 
framing information.  The data may have been written to that page when the page 
was used for something else.  Once that data was deleted, the page would have 
been reused for some other purpose, but not all the bits inside the page would 
have been overwritten, so unused area of the page might contain all sorts of 
remnants from pages of other types, written in any order, truncated strangely, 
and completely unparsable.

Simon.
-
CCL-Forensics Ltd
Payton House
Packwood Court
Guild Street
Stratford-upon-Avon
CV37 6RP
Registered in England No: 5314495

Legally privileged/Confidential Information may be contained in this message. 
If you are not the addressee(s) legally indicated in this 
message (or responsible for delivery of the message to such person), you may 
not copy or deliver this message to anyone. In such case, 
you should destroy this message, and notify us immediately.

If you or your employer does not consent to Internet e-mail messages of this 
kind, please advise us immediately. Opinions, conclusions 
and other information expressed in this message are not given or endorsed by my 
firm or employer unless otherwise indicated by an 
authorised representative independent of this message.

Please note that neither my employer nor I accept any responsibility for 
viruses and it is your responsibility to scan attachments (if any). 
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique indexes apparently not working

[Alex Queiroz]

Hello,

On Mon, Mar 12, 2012 at 11:52 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 12 Mar 2012, at 10:11pm, Alex Queiroz <asand...@gmail.com> wrote:
>
>> - If I try to reindex the table, it fails;
>> - If I drop the index and try to add it again, it fails.
>
> For both the above ...
>
> What command are you issuing, and what result are you getting from SQLite 
> when it fails ?  (i.e. what is it doing instead of working ?)
>

sqlite> reindex 'user_identity';
Error: indexed columns are not unique

sqlite> drop index userIdentityByUcgID;
sqlite> CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON
'user_identity' (shortName ASC, ucgID ASC);
Error: indexed columns are not unique

Cheers,
-- 
-alex
http://www.artisancoder.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unique indexes apparently not working

[Alex Queiroz]

Hello,

On Mon, Mar 12, 2012 at 11:34 PM, Petite Abeille
<petite.abei...@gmail.com> wrote:
>
>
> Hmmm… really? That would be most peculiar...
>

Indeed. I have now run this:

sqlite> PRAGMA integrity_check;
rowid 192697 missing from index userIdentityByUcgID
rowid 192701 missing from index userIdentityByUcgID
rowid 192705 missing from index userIdentityByUcgID
rowid 192710 missing from index userIdentityByUcgID
rowid 192711 missing from index userIdentityByUcgID
rowid 192712 missing from index userIdentityByUcgID
rowid 192716 missing from index userIdentityByUcgID
...
...
...

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

Re: [sqlite] Unique indexes apparently not working

[Alex Queiroz]

Hallo,

On Mon, Mar 12, 2012 at 11:16 PM, Petite Abeille
<petite.abei...@gmail.com> wrote:
>
> On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote:
>
>>       id                      INTEGER PRIMARY KEY,
>> CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID'       ON 
>> 'user_identity' (id ASC);
>
> Not directly related to your problem, but… these two clauses are redundant… a 
> primary key is unique by definition… no point adding another unique index on 
> top of it...

You are right and I know it, this code was there before I started here. :)

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

[sqlite] Unique indexes apparently not working

[Alex Queiroz]

Hello,

I have a problem with a customer database that is very strange. This
is part of the DB's schema:

- BEGIN -

CREATE TABLE IF NOT EXISTS user_identity (
   id  INTEGER PRIMARY KEY,
   shortName   TEXT,
   domainName  TEXT,
   lastUpdated INTEGER,
   ucgID   INTEGER
);


CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID'   ON
'user_identity' (id ASC);
CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON
'user_identity' (shortName ASC, ucgID ASC);

-- END --

The problem is that a customer has a database where (shortName, ucgID)
have duplicates.

- When I try to insert manually a new record, the index is enforced;
- When I try to update a column, the index is enforced;
- If I try to reindex the table, it fails;
- If I drop the index and try to add it again, it fails.

At this point I really have no idea of what could have caused that. If
anyone has seen something similar before, please share. :)

Cheers,
-- 
-alex
http://www.artisancoder.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug or expected behavior?

[Alex Dupre]

Hi All,
is it expected the following behavior or is it a bug?

%sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> CREATE TABLE a (x BLOB);
sqlite> INSERT INTO "a" VALUES(X'30313233');
sqlite> INSERT INTO "a" VALUES('0124');
sqlite> SELECT x, typeof(x) FROM a WHERE x LIKE '012%';
0123|blob
0124|text
sqlite> select * from a where x = '0123'; <=== no record found
sqlite> select * from a where x like '0123';
0123
sqlite> select * from a where x = '0124';
0124

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

Re: [sqlite] does sqlite work with Apple iCloud and IOS 5

[Alex Bowden]

Andy

Simon's answer is totally nonsense.  I suspect that he has misunderstood 
something that he's read.

The only thing that he is right about, is that neither of us can tell you 
anything about iOS 5 until the cloud API is public.

However, what I can tell you, which isn't about iOS 5, is this.

Apple uses sqlite for pretty much all iOS based data storage.  You can use 
other storage mechanisms, but sqlite is the default.

Most code that has been developed for iOS (rather than ported to iOS) does not 
use sqlite directly.  It uses core data which is a persistent object store for 
objective C objects.  The default choice for storing these persistent objects 
is to let core data store them in sqlite.

Core data really is an excellent facility which is tightly integrated with 
Objective C, Xcode, with iOS's User Interface facilities, iOS's undo stack, 
etc..  It is unlikely that Apple would walk away from that as a primary storage 
mechanism.

If you have an iPhone app, then you are probably a registered developer, in 
which case you can watch the video's from the recent WWDC.  Search for any with 
iCloud in their name, and watch them.

Otherwise, why not spend the time from now until release, finding out what core 
data will do.

Alex


On 11 Oct 2011, at 17:34, Simon Slavin wrote:

> 
> On 10 Oct 2011, at 9:55pm, Andy Davidson wrote:
> 
>> I have an iPhone app that uses a sqlite base . Apple's iCloud is very cool
>> It automatically syncs your changes back to the cloud and out to all your
>> other devices. Does anyone know  what I need to do to get sqlite to work
>> with Apple's iCloud.
> 
> There's really no point to integrating them.  They both do similar things.  
> Just one stores data in a file an your hard disk (or networked server) and 
> the other stores data at Apple's server farm.
> 
> I'm afraid I can't give details until the iCloud APIs go public, which is not 
> yet.  The same is true of everything to do with iOS 5.  But basically yes, 
> stuff works, roughly the way you'd expect it to.
> 
> It's worth bearing in mind that your device's communications with the cloud 
> might be going through a poor quality phone connection, at phone connection 
> speeds.  You do not want to store more than a few K in the cloud.  Thirty or 
> forty important locations for your GPS device ?  Fine.  Every place you've 
> ever stopped ?  No.
> 
> 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] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Alex Bowden]

logical?

It seems equally logical to me that one of A or B might be evaluated, and if it 
were false, then the other might not be evaluated.

And it would be logical to choose which of A or B to evaluated on a predicted 
cost and probability of an advantageous false result.

but hay.  Who said their could only be one logical approach.

Alex


On 24 Aug 2011, at 20:12, Carlos Rocha wrote:

> Don't know how SQLite should behave in this case, but seems logical to 
> me that A and B would force that A is always evaluated, and B is 
> evaluated only if A is true.
> I would change the order of the two betweens:
> 
> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND 
> '2011-08-02' AND itemID BETWEEN 100 AND 200
> 
>> Hi all,
>> 
>> I have a table like this:
>> 
>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>> CREATE INDEX createIdx on t(createdAt);
>> 
>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>> current content.
>> 
>> When perfoming a Statement like this:
>> 
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>> 
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
>> 
>> regards
>> gerd
>> ___
>> 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] How to cancel subscription of this mail list?

[Alex Cheng]

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

[sqlite] non utf-8 database names

[Alex Kides]

Hi,

 

I'm not sure if this is the right place to ask or has been covered
already (haven't been able to find any reference to this issue), but
does sqlite3_open_v2 have to take a UTF-8 formatted string for the
filename?  

 

SQLITE_API int sqlite3_open_v2(

  const char *filename,   /* Database filename (UTF-8) */

  sqlite3 **ppDb, /* OUT: SQLite db handle */

  int flags,  /* Flags */

  const char *zVfs/* Name of VFS module to use */

);

 

I'm using sqlite through Qt and the problem I'm having is that in non
utf locales (e.g. ja_JP.PCK on solaris) when given a shift-jis encoded
to utf-8 string to open, the file being created is using the utf-8
characters, i.e. when the file is ls'd the characters are garbled.  If a
full path containing any non-ascii characters is given the file fails to
be found given the different encodings.

 

I've been going through the sqlite code shipped with qt (it's the
amalgamated source so a bit of light reading ;) ) - but does anyone know
off hand if there will be untoward issues with sqlite if I modified the
qt call to sqlite so that a locale encoded string instead of utf-8 is
passed across?   

 

I'm quite happy to modify the code as necessary but I am not sure as to
what things might break or what problems to look out for.

 

Regards,

Alex Kides

 

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

Re: [sqlite] SQlite vs. SQlite3 - a beginners question

[Alex Mandel]

Richard Cooke wrote:
> I'm trying to teach myself PHP and SQLite and I've tried to understand 
> whether SQLite and SQLite3 are the same animal or are they completely 
> different.  I am using WAMP on a PC and the PHP version is 5.31.  If I 
> use sqlite_libversion(); I get a result of 2.8.17.  If I use 
> SQLite3::version() I get 3.6.15.  Does this make sense?
> 
> If they are completely different animals how do you decide on which one 
> to use?
> 
> Thanks,
> 
> RC

Use SQLite3 via PDO. sqlite 2.x is actually quite old at this point and
really shouldn't be used for new development, it there more for
backwards compatibility and maintainability of older apps.

Alex

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

Re: [sqlite] Converting .dbf to SQLite

[Alex Mandel]

Using R might actually be a convenient way to do it all in essentially
one step, and technically batch scriptable.

You'd need the RSQlite add on package, I think dbf reading is built in.

Alex

dave lilley wrote:
> 2009/11/12 Rich Shepard <rshep...@appl-ecosys.com>
> 
>> On Thu, 12 Nov 2009, dave lilley wrote:
>>
>>> Not trying to be silly here but why not write a wee program that reads in
>>> the dbf file and for each row read in write the data into an sql file?
>>   Because I'd have to research the format of the .dbf file and I'd probably
>> be re-inventing the wheel.
>>
> 
> No I mean you use a programming language to read the DBF datafile and write
> out to your new database.
> 
> And as someone else has suggested you use OOo spreadsheet to connect to the
> DBF file and then write it out to a CVS file so you can import into your new
> DB.
> 
> if your not confidant with programming then i strongly suggest you take this
> option as you then only have to import the CVS data into SQLlite.
> 
> Rich
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[Alex Mandel]

DaleEMoore wrote:
> I'd like to SUM(tripSeconds) and format output as
> days.hours:minutes:seconds.hundredths, but have not been able to figure out
> how to do that with sqlite. This didn't seem to come close:
> 
> SELECT 
> STRFTIME('%d', SUM(tripSeconds)) + '.' +
> STRFTIME('%H', SUM(tripSeconds)) + ':' +
> STRFTIME('%M', SUM(tripSeconds)) + ':' +
> STRFTIME('%f', SUM(tripSeconds)) AS Duration, 
> SUM(tripSeconds)
> 
> Any ideas are appreciated,
> Dale E. Moore

What about something like
SELECT SUM(STRFTIME('%s', tripSeconds)) AS Duration
from table
GROUP BY trip

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

Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

[Alex Mandel]

Gilles Ganault wrote:
> On Fri, 23 Oct 2009 12:00:47 +0200, Thibaut Gheysen
> <gheyse...@gmail.com> wrote:
>> You can use sqlite-manager (http://code.google.com/p/sqlite-manager/). 
> 
> Thanks for the tip, but I'd rather a stand-alone rather than a Firefox
> plug-in.
> 

If you have the XUL libraries installed it will run standalone I
believe. Just to check when you are testing these tools, are you also
looking at "Save to CSV" which would be just as good as a copy paste?

There's a reason why a lot of them don't display more than 100-1000
records per view.

You could also try http://www.gaia-gis.it/spatialite/

Alex

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

Re: [sqlite] GUI design & managment tool?

[Alex Mandel]

Allen Fowler wrote:
> Hello,
> 
> Can anyone recommend a Free, or reasonably priced Non-Free, GUI tool for 
> creating and maintaining an SQlite databases that can run on both Windows and 
> Linux?
> 
> (Support for visual relation design would be great, too.)
> 
> I found a list at:
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> But, I was wondering if anyone has personal experience to share...
> 
> Thanks,
> :)
> 

I usually use a UML diagram tool to draw the database to begin with and
run some scripts to convert it to SQL.
I've done it with Dia in the past which has a Dia2Code plugin but have
also heard good things about ArgoUml.

For ongoing management I use Firefox SQLite Manager plugin, and
spatialite-gui.

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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Alex Mandel]

Robert,

1. When you build you want to make sure to override the default
directory settings. ./configure --builddir=/usr/
Check the directions and configure script for options
(make sure to run make clean before you attempt to run make again.)

2. Use Checkinstall, it will build a deb file specific to your system
and install it. Instead of make install

Maybe that will help, FYI I forced the Jaunty deb into Hardy with no
issues, since the dependencies haven't changed.

Alex

Robert Villanoa wrote:
> Thank you very much Dan, because this is exactly the problem.
> I used ldd to check shared library dependencies of the executable file 
> sqlite3 and the result was:
> linux-gate.so.1 =>  (0xb7fc3000)
> libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7f4e000)
> libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7f4a000)
> libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7f31000)
> libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7de2000)
> /lib/ld-linux.so.2 (0xb7fc4000)
> However, the file libsqlite3.so.0 from the sqlite3 3.6.14 package is 
> installed to the /usr/local/lib/ folder. I then deleted libsqlite3.so.0 in 
> /user/lib/ and then copied the one from /usr/local/lib/ to /usr/lib/ and I 
> got the correct version of sqlite3.
> Now I wonder is that enough? I mean whether simply copying libsqlite3.so.0 to 
> /usr/lib/ will let me use the new version completely? Or is there any further 
> modifications I must do for me to use sqlite3 3.6.14 'properly'?
> Thank you so much for your help!
> 
> 
> 
> 
> 
> From: Dan <danielk1...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Monday, May 18, 2009 4:43:53 PM
> Subject: Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?
> 
> 
> On May 18, 2009, at 3:33 PM, Robert Villanoa wrote:
> 
>> Thank you for your answer, Jean-Denis.
>> When I type 'which sqlite3', I get the following location:
>> /usr/local/bin/sqlite3
>> And the value of my PATH variable is:
>> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/ 
>> games
>> So I think the executable file sqlite3 is seen by PATH.
>> Could you please tell me any more suggestions?
> 
> $ ldd /usr/local/bin/sqlite3
> 
> Check it's linking to the correct libsqlite3.so.
> 
> Dan.
> 
> 
>>
>>
>>
>>
>>
>> 
>> From: Jean-Denis Muys <jdm...@kleegroup.com>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> Sent: Monday, May 18, 2009 2:50:38 PM
>> Subject: Re: [sqlite] How to install the latest version of sqlite3  
>> on Ubuntu?
>>
>>
>> On 5/18/09 9:19 AM, "Robert Villanoa" <robertvilla...@yahoo.com>  
>> wrote:
>>
>>> Hi everyone,
>>>
>>> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2.  
>>> Now I want
>>> to upgrade it to the latest version, 3.6.14, but I don't know how  
>>> to do that.
>>>
>>> Here are the steps I have done (after reading another thread about  
>>> this
>>> issue):
>>> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
>>> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package,  
>>> go to the
>>> sqlite3 directory and run:
>>> - ./configure
>>> - make
>>> - sudo make install
>>> Although I did not encounter any error after executing these above  
>>> commands,
>>> it seemed that I did not install sqlite3 3.6.14 successfully.  
>>> Whenever I type
>>> 'sqlite3' into the GNOME terminal, it shows that the version is  
>>> 3.4.2.
>>> What's wrong with me? Please help! Thanks in advanced!
>> PATH problem? When you type "which sqlite3" in the terminal, what is  
>> the
>> result? Does it match your install location?
>>
>> JD


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

Re: [sqlite] SQL logic error or missing database

[Alex Ousherovitch]

>> At the end of the loop, instead of having 4000 rows I have 3976 rows
>> (it's random, sometimes I have 3972 or 3974).
>> sqlite3_exec doesn't returns any error during the INSERT statement,
>> but I have some errors during the BEGIN IMMEDIATE, errors are all:
>> SQL logic error or missing database (printed with sqlite3_errmsg).

> Your use of sqlite3_errmsg is itself very likely a race. Between the 
> time you detect an error and the time you retrieve error message, the 
> other thread could have run some statements that modify the error 
> message. Moreover, between the time you call sqlite3_errmsg and the
time 
> you actually print the string pointed to by the char* pointer the 
> function returns, the string may be modified or even deallocated.

> Igor Tandetnik 

Is it safe to use sqlite3_errmsg() when each thread is using its own
connection handle to access the database and get the error messages for
that connection?


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

Re: [sqlite] Implicit indices on TEMP tables

[Alex Ousherovitch]

Igor, yes, deletes do occur on , but before every insert on 
 I delete all records from mods_log_ and nevertheless 
get duplicates.
I would expect the implicit index on modrowid to be refreshed too, when 
mods_log_ gets emptied.

Thank you,
Alex

-
Date: Tue, 14 Apr 2009 00:08:38 -0400
From: "Igor Tandetnik" <itandet...@mvps.org>
Subject: Re: [sqlite] Implicit indices on TEMP tables
To: sqlite-users@sqlite.org
Message-ID: <gs1287$77...@ger.gmane.org>

"Alex Ousherovitch"
<alex.ousherovi...@opentv.com> wrote in
message
news:5ee1928d06817b4788b64caf1a8517b00332a...@sfo-ex-01.ad.opentv.local
> I am creating a set of the following TEMP tables
>
>CREATE TEMP TABLE IF NOT EXISTS mods_log_ (modrowid
> INTEGER NOT NULL PRIMARY KEY)
>
> for every connection on my database, using the same name.  These TEMP
> tables AND their indices are supposed to be only visible within that
> same database connection.
>
> But it appears that when populated from TEMP triggers, also created
> for each connection -
>
>CREATE TEMP TRIGGER IF NOT EXISTS mods_log__trigger
>AFTER INSERT ON 
>FOR EACH ROW BEGIN
>INSERT INTO mods_log_ VALUES (new.rowid);
>END
>
> -I get SQLITE_CONSTRAINT error for attempting to add non-unique
> elements.

Do you ever delete from ? When you do so, do you also delete
a matching record from mods_log_? It's possible that a ROWID
from a deleted record is reused for new record in , which
would be a duplicate in mods_log_.

Igor Tandetnik


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

[sqlite] Implicit indices on TEMP tables

[Alex Ousherovitch]

Hello list,

 

I am creating a set of the following TEMP tables

CREATE TEMP TABLE IF NOT EXISTS mods_log_ (modrowid
INTEGER NOT NULL PRIMARY KEY)

for every connection on my database, using the same name.  These TEMP
tables AND their indices are supposed to be only visible within that
same database connection.

But it appears that when populated from TEMP triggers, also created for
each connection -

CREATE TEMP TRIGGER IF NOT EXISTS mods_log__trigger

AFTER INSERT ON 

FOR EACH ROW BEGIN

INSERT INTO mods_log_ VALUES (new.rowid);

END

-I get SQLITE_CONSTRAINT error for attempting to add non-unique
elements.

The mods log temp tables get cleared from all records before every
INSERT on , so they do not contain duplicates, so it must be
generated by the index.

So it seems that the index on modrowid on the various
mods_log_ ends up being shared.

Any ideas what is going on?

 

Thank you.

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

[sqlite] [Fwd: [SpatiaLite-Users] spatialite-2.3.0 has been released]

[Alex Mandel]

 Original Message 
Subject: [SpatiaLite-Users] spatialite-2.3.0 has been released
Date: Tue, 07 Apr 2009 20:27:51 +0200
From: Alessandro Furieri 
Reply-To: spatialite-us...@googlegroups.com
To: spatialite-us...@googlegroups.com


Hi list,

I've just released SpatiaLite v.2.3.0 [stable version]

http://www.gaia-gis.it/spatialite-2.3.0

=

- supporting Routing [VirtualNetwork]

- supporting EXIF GPS pictures

- compatibility support for FDO/OGR RFC16 [VirtualFDO]

- intensive and generalize debugging

- AMALGAMATION: all the stuff put in a single source, as
  SQLite already does
  a complete SpatialDBMS engine in just 2 (two) C sources

- clear separation between the LIB and the TOOLs

- libspatialite-2.3.0 now includes libsqlite; linking
  a single library allow to support a full SpatialDBMS
  engine [some 800KB required]

- now builds on M$ Visual Studio .NET as well



enhanced GUI-tool:

- color SQL syntax
- full DB self-initialization during creation
  [using init_spatialite.sql is no longer required]
- introducing MEMORY-DB ... try and see ...
  ... never seen before something comparable ...


bye, Sandro

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google
Groups "SpatiaLite Users" group.
To post to this group, send email to spatialite-us...@googlegroups.com
To unsubscribe from this group, send email to
spatialite-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/spatialite-users?hl=en
-~--~~~~--~~--~--~---

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

[sqlite] Adding a NOT NULL column fails on empty table

[Alex Ousherovitch]

Hello list,

 

The attempts to add a NOT NULL column on an empty  table

ALTER TABLE tbl_name ADD COLUMN col_name blob NOT NULL

 fail with the following error message:

Cannot add a NOT NULL column with default value NULL

 

As I understand, many other systems allow it when the table has no
records. 

Is it a bug or a feature in SQLite?

 

Thank you,

Alex

 

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

Re: [sqlite] designing a db to hold repeating data

[Alex Mandel]

You should talk with Alessandro Furieri a.furi...@lqt.it who is
currently working on adding Raster support to spatialite.
http://www.gaia-gis.it/spatialite/

It may be more efficient to store the data separately in well used
raster formats and attach them to the database as needed for queries.

Alex

P Kishor wrote:
> I have a grid of 1000 x 1000 cells with their own data as well as 20
> years of daily weather data (20 * 365 = 7300 rows) for each contiguous
> group of 50 x 50 cell.
> 
> CREATE TABLE cells (
>   cell_id INTEGER PRIMARY KEY,
>   other cell attributes,
>   lat,
>   lon,
>   met_grid_id INTEGER
> );
> 
> cell_id met_grid_id
> --- 
> 0   0
> 1   0
> ..
> 24990
> 25001
> 25011
> ..
> 49991
> 
> CREATE TABLE met (
>   met_id INTEGER PRIMARY KEY,
>   other met attributes,
>   met_grid_id INTEGER
> );
> 
> met_id met_grid_id
> -- ---
> 0  0
> 1  0
> ..
> 7299   0
> 7300   1
> 7301   1
> ..
> 
> CREATE VIRTUAL TABLE cell_index USING rtree (
>   cell_id INTEGER,
>   minx REAL,
>   maxx REAL,
>   miny REAL,
>   maxy REAL
> )
> 
> The db is about 350 MB with the cell table with 1000,000 rows and the
> met table with 2,920,000 rows and the R*Tree index.
> 
> Is there any other better way that jumps out at any of you?
> 
> 

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

[sqlite] WHERE clause analysis - find referenced columns

[Alex Ousherovitch]

Hello list,

 

I need to implement a security model where certain columns (e.g. named
super_secret_key) will be hidden. 

This needs to be enforced not only for the requested result columns but
also for the columns referenced in the WHERE clause.

Is there a relatively simple way to either get each column referenced in
the WHERE clause or build a query that would check if the WHERE clause
(I could place it in a temporary table) contains the super_secret_key as
a column name, but not as a value.

 

Thank you,

Alex

 

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

Re: [sqlite] WHERE clause analysis - find referenced columns

[Alex Ousherovitch]

Unfortunately, for the SQLITE_SELECT action code
(http://www.sqlite.org/c3ref/c_alter_table.html) the registered auth
callback will not get the column information.
It is only available for SQLITE_READ, which, I guess, is not what I need
- I would like prepare statement to fail if there are "hidden columns"
in the search criteria.
Is this possible?

 

> http://www.sqlite.org/c3ref/set_authorizer.html

> 

> This feature is used by systems such as

> 

>http://www.cvstrac.org/

>http://www.fossil-scm.org/

> 

> to prevent unauthorized users from enters hostile SQL, or from  

> entering SQL that uses sensitive information such as the "password"  

> column of the "user" table.

> 

> D. Richard Hipp

> drh at hwaci.com
 

 

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

[sqlite] WHERE clause analysis - find referenced columns

[Alex Ousherovitch]

Hello list,

 

I need to implement a security model where certain columns (e.g. named
super_secret_key) will be hidden. 

This needs to be enforced not only for the requested result columns but
also for the columns referenced in the WHERE clause.

Is there a relatively simple way to either get each column referenced in
the WHERE clause or build a query that would check if the WHERE clause
(I could place it in a temporary table) contains the super_secret_key as
a column name, but not as a value.

 

Thank you,

Alex

 

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

Re: [sqlite] Best GUI?

[Alex Mandel]

Nuno Magalhães wrote:
>> Do you want this for data entry, or developing and managing the database
>> via SQL. I have yet to see a really good front end for data entry
>> although Dabo shows promise as a tool to make good data entry tools.
> 
> Thanks, i'll look into it. I want it as a means to create the inicial
> schema and change it if necessary. Setting up constraints, data types,
> that sort of thing... For random queries i'd probably use the cli and
> for data entry i'll use php.
> 
> Nuno Magalhães
> LU#484677
> ___

Oh that's a different ballpark. I actually use Dia to draw my database
and then use dia2code to generate sql that can be run.
I've seen other variations of the same idea, UML diagram to SQL to code
is the basic idea. Of course most of them have no idea about the data
types for sqlite so you still end up typing out most of it.

Not really sure what you need a GUI for at that stage unless you want it
to baby step you with check boxes about your options. If that's what
you're looking for the firefox plugin is the closest thing I've seen to
that.

FYI- did you consider asking the plugin developer to add tools you think
are useful?

Alex

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

Re: [sqlite] Best GUI?

[Alex Mandel]

Nuno Magalhães wrote:
>> Here lots of  SQLite management tools
>>
>> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> Keyword being "lots". I've been to that site, which was what prompted
> me to write in the first place. I'm looking for either web-based or
> *nix. I know there are lots, i just want to know which one(s) you
> recomend and why. I've been using a firefox plugin called SQLite
> Manager but am not happy with it, i'd like more options, something
> similar to Oracle's SQLDeveloper (pref without the Java™) or even
> Access.
> 
> Btw, vi and sh do not qualify as GUI, i have my own tools for that, thanks.
> 
> Nuno Magalhães
> LU#484677

Do you want this for data entry, or developing and managing the database
via SQL. I have yet to see a really good front end for data entry
although Dabo shows promise as a tool to make good data entry tools.

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

Re: [sqlite] Any concept of row number in SQLite?

[Alex Mandel]

P Kishor wrote:
> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
> <slgdoug...@optusnet.com.au> wrote:
>> Hi,
>> I am converting a program from Paradox (stop laughing, please) to SQLite.
>> Paradox has a useful feature where you can specify the actual index of a row
>> in the table.  This is handy when the table is displayed in a grid and you
>> want the record corresponding to a row in that grid - you can just specify
>> the index, say 28, of that grid row and it will get the record no 28 from
>> the table.  It spares the need for a SELECT statement, and is a lot more
>> efficient.
>> As a SQLite newbie, the only way I can see to do this is to read the whole
>> table with sqlite3_get_table() and then get the required row from the
>> returned array.  This seems overkill when I just want a single record.
> 
> There is the rowid, but I am not sure what you want to do... are you
> expecting a database table to be a linear list of entries? Generally
> one uses a spreadsheet for that kind of stuff. A SQL database doesn't
> have an internal concept of order. You specify a criteria and the db
> returns a SET of rows or records. You can constrain the SET by
> specifying criteria (the WHERE clause), and you can impose an order on
> the returned rows by specifying an ORDER clause.
> 
> If you want just one specific row, just do a
> 
> SELECT cols FROM table WHERE some_primary_key = ?
> 
> If you don't want to specify and control your own primary key, you can
> use the rowid which is something the db uses internally for its own
> shenanigans.
> 
> 
>> Is there a more compact way of doing this?
>> Thanks in advance etc.
>> Sholto
> 
> 
> 

The behavior you want sounds to me like a table is treated as an object,
and the object has a cursor or specific property for each record. It
would make sense if you want to reference a record in this way that you
would need to load the whole table into some type of array or list and
cursor your way to that object.

My guess is the application you used before abstracted the table to an
object in the way that SQL Alchemy turns tables into objects for python.
This is the object oriented programming approach to the issue, the most
efficient sql way would be to do a SELECT statement which for some
reason you don't want to do.

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

Re: [sqlite] SQLite utilty to import XML

[Alex Mandel]

Salles, Joaquim Campos wrote:
> Hello,
> 
> I'm looking for an SQLite utility (Windows / Linux) to import XML files
> into an SQLite Database (free/open source), so I can use it in a shell
> script to periodicly search a directory and import the XML files.
> 
> I tried to find one in:
> 
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> 
> But I didn't find anything I can use.
> 
> Does anyone have any suggestions?
> 
> Thanks for the help,
> 
>  
> 
>  
> 
> Joaquim Salles
> 

I think the firefox SQLite Manager plugin can do XML.
http://code.google.com/p/sqlite-manager/

Alex

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

Re: [sqlite] Recover deleted records

[Alex Mandel]

John Machin wrote:
> On 21/01/2009 7:03 AM, Alex Mandel wrote:
>> Griggs, Donald wrote:
>>> Hi Alex
>>>
>>> I can't answer your specific questions, but I presume you've already
>>> studied the file format document (most recently mentioned by Roger
>>> Binn's recent post):
>>>
>>> http://www.sqlite.org/fileformat.html
>> Yes I did read through that, but I didn't see what I was looking for.
> 
> Have a look at section 3.3.2
> (http://www.sqlite.org/fileformat.html#record_format) and when you get
> to mentions of "variable length integer" and start to wonder what that
> is, read section 3.3.1.
> 
> 
>> Is there a list of binary codes or other delimiters likely to be
>> encountered between records or rows, or is the magic of length of a
>> given record only stored in some coded journal section or index.
>>
>> So far things are looking good using the unix tr command and trying out
>> various filters to remove unneeded characters. I also vacuumed a copy of
>> the database and did a diff against the tr results from that to narrow
>> down where my missing data is in the file.
>>
>> Now I'm just trying to figure out how to slice it into records, which
>> may dependant on me knowing the data really well, the problem I'm
>> running into is that there's no consistent character between two integer
>> fields so it's hard to split them apart.
> 
> Two levels of problem:
> (1) Finding where each record starts
> (2) Unpacking the record into columns
> You would certainly need to write code to do the record unpacking, and
> probably for the record finding as well.
> 
> HTH,
> John

Well I've gotten closer.
I think I have handle on (2) using the struct library in python I can
easily unpack values given a format specified by the first byte of the
record. What I'm still unsure of is how to isolate 1 record in a free
page or how to even find where in the header the free page is.

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

Re: [sqlite] Recover deleted records

[Alex Mandel]

Griggs, Donald wrote:
> Hi Alex
> 
> I can't answer your specific questions, but I presume you've already
> studied the file format document (most recently mentioned by Roger
> Binn's recent post):
> 
> http://www.sqlite.org/fileformat.html 
> 
Yes I did read through that, but I didn't see what I was looking for.
Is there a list of binary codes or other delimiters likely to be
encountered between records or rows, or is the magic of length of a
given record only stored in some coded journal section or index.

So far things are looking good using the unix tr command and trying out
various filters to remove unneeded characters. I also vacuumed a copy of
the database and did a diff against the tr results from that to narrow
down where my missing data is in the file.

Now I'm just trying to figure out how to slice it into records, which
may dependant on me knowing the data really well, the problem I'm
running into is that there's no consistent character between two integer
fields so it's hard to split them apart.

Thanks,
Alex

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

[sqlite] Recover deleted records

[Alex Mandel]

I was looking through old posts and this one seemed quite similar to my
situation with one exception. I have full knowledge of what data I'm
trying to recover.
http://thread.gmane.org/gmane.comp.db.sqlite.general/35764

The basics, data was deleted from 4 tables via an ODBC connection. No
Vacuum has been done and looking at a copy of the database in a text
editor(Scite) I can see a lot of the data completely intact.

What I don't know is,
1.for the non-ascii characters in the file, what are they and how can I
translate the ones that might have say numeric data in them.(Text
appears to be just text)
2.is there a pattern of line starts/ends or other indicators that would
be useful for me to look for if I'm writing a script to parse the file
back into at least an unformatted text dump?

Is the "deleted" data in the "Free Pages" and is there anything in the
API to interact with data in this area so I could loop over it and
extract pieces one by one?

Any other leads I should be following?

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

[sqlite] sqlite Btree

[Alex Krzos]

It is not specified anywhere, but is the sqlite btree a b+tree.  I noticed
that pages with in the btree source file have the ability to be identified
as either leaf or leafdata does this mean that sqlite uses a b+tree? 

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

[sqlite] Modularity, Joins

[Alex Krzos]

Hi all,

I am doing a project which involves looking through sqlite code and getting
a feel for how the dbms actually works.  I have three questions.

First off, sqlite is supposedly modular.  I am having a tough time
determining the modularity of the components.  How is sqlite modular, it
seems that most of the components are coded to be exactly the way they are.

The b-tree implemented by sqlite, is that a b+tree or b-tree?

Third, where can I find how joins are implemented?  Even better yet, how are
joins parsed then processed?

Thanks.

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

[sqlite] Using Eclipse on Windows XP

[Alex Krzos]

Is there an easy way to develop on sqlite using eclipse for C/C++ on a
Windows XP machine? I have eclipse open on a source directory but can not
compile.  Is there a possible faq or wiki on this?  Thanks.

Alex

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

Re: [sqlite] insert speeds slowing down as database size increases (newb)

[Alex Scotti]

On Oct 29, 2008, at 4:59 AM, Julian Bui wrote:

> Hi everyone,
>
> First off, I'm a database and sqlite newbie.  I'm inserting many many
> records and indexing over one of the double attributes.  I am seeing
> my insert times slowly degrade as the database grows in size until
> it's unacceptable - less than 1 write per millisecond (other databases
> have scaled well).  I'm using a intel core 2 duo with 2 GB of ram and
> an ordinary HDD.
>
> I am trying to figure out why some of the other databases (firebird,
> mysql, berkeley db) have provided constant insert speeds whereas
> sqlite has not.

i can tell you firsthand that berkeley db does not provide anything  
like constant time for random inserts into a btree as it's size grows.



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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

On Sep 24, 2008, at 4:17 PM, Nicolas Williams wrote:


>
> But every commercial SQL RDBMS seems to have syntax for index control.

as "[EMAIL PROTECTED]" was kind enough to post on sept 21 to  
this very mailing list, not all sql rdbms have taken this approach.   
at least one, db2, chose the high road.

On Sep 21, 2008, at 12:59 PM, Stephen Woodbridge wrote:

> Richard,
. edited
>
> DB2:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/ 
> com.ibm.db2.udb.doc/admin/t0005308.htm


read http://www.ibm.com/developerworks/db2/library/tips/dm-0312yip/ 
index.html as well if you could.


i implore you all to take the high road here.



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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

On Sep 24, 2008, at 1:13 AM, Alex Scotti wrote:

>
> On Sep 23, 2008, at 2:35 PM, Jay A. Kreibich wrote:
>
>
>>
>>   It doesn't "blatantly" anything.  Indexes are outside of the
>>   Relational Model and have nothing to do with it.  They're  
>> orthogonal.
>>   From that, anything having to do with creating, using, or
>>   manipulating indexes is outside the model.
>>
>>   Within any actual real-world RDBMS product, however, once you  
>> accept
>>   the need for indexes (on any level) then it shouldn't be hard to
>>   accept the desire to control how those indexes are used.
>
> no.  no no no.  i don't know how to convince you of this deep  
> mistake other than beating you over the head with your own words.
>
>
> as i said, i give up.
>
> at the point where the conversations look this, i begin to worry:
>
> question: "my query runs very slowly!  i don't understand why!?"
> answer: "just append this glob of sqlite specific crud to your  
> query and it runs great!"
>
> question: "this query runs slow also!"
> answer: "just code in raw vdbe instructions, you'll have absolute  
> control over the choice of index usage!"
>
> question: "this query runs slowly still!!"
> answer: you should be using berkeley db.  they give you absolute  
> control over everything.
>
>

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

Re: [sqlite] Specifing which index to use. Was: Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

On Sep 22, 2008, at 11:18 AM, Jay A. Kreibich wrote:

> On Mon, Sep 22, 2008 at 10:07:54AM -0400, D. Richard Hipp scratched  
> on the wall:
>> I am reluctant to add to SQLite the ability to explicitly specify the
>> index for a query.  I agree with Alex Scotti that the whole idea  
>> seems
>> very un-RDBMS like.
>
>   Well it is outside of the Relational Model, that's for sure.
>
>   Then again, the whole concept of indexes are outside of the
>   Relational Model.

this isn't exactly a good argument.  an index surely doesn't break  
the relational model in any way.  it's existence or
absence may or may not effect execution time, but would never yield  
incorrect or different results.  the ubiquitous b-tree index
may not have been so obvious at the time, and who's to say it will  
continue to be a given forever.  the model is just that, a model -
abstracted away from implementation details, no matter how obvious  
they seem at the time to the implementers.

to beat a dead horse, the relational model doesn't discuss anything  
physical at all.  by your line of reasoning using disks would be  
outside.  heaven forbid a buffer pool caching your i/o.

on the other hand we have here a non standard sql extension which  
ties users to sqlite, and blatantly does fly in the face of the  
relational model.

that being said, as richard points out nobody would force anyone to  
use this extension.  i would simply pretend it didn't exist.  my fear  
is more along the lines of what a crutch for query optimization  
problems features like this can become.


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

Re: [sqlite] Performance/bug in multikey 'group by' in 3.6.2

[Alex Scotti]

not surprisingly, the db2 approach is the only one that seems to  
follow the "ideal" of the relational dbms.  looks like you get to  
provide to the engine information about your data, rather than  
describing to the engine what steps it's supposed to take.  hints  
that directly influence query plans seem like a huge step backwards.   
if you want tight control over what the engine does you use a pure  
rules based (rather than cost based) optimizer.  but nobody  
(including sqlite) does that anymore for good reasons.


On Sep 21, 2008, at 12:59 PM, Stephen Woodbridge wrote:

> D. Richard Hipp wrote:
>> On Sep 21, 2008, at 8:51 AM, Russ Leighton wrote:
>>
>>> I am interested in ... a way
>>> to constraint/control index selection on queries.
>>>
>>
>> What other SQL database engines have this capability and what syntax
>> do they use?
>
> Richard,
>
> Hope this are useful:
>
> Oracle:
> http://www.adp-gmbh.ch/ora/sql/hints/index.html
> http://download.oracle.com/docs/cd/E12096_01/books/admintool/ 
> admintool_PhysicalSetup32.html
>
> Postgresql:
> Does not support it unless it is a very recent addition, but its  
> planner
> occasionally/frequently? makes a poor choice and adding support for
> hints ala Oracle and/or discussions like "Why didn't Postgresql use my
> index?" regularly occur on the user list.
>
> DB2:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/ 
> com.ibm.db2.udb.doc/admin/t0005308.htm
>
> MySQL:
> http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
>
> -Steve
> ___
> 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] Table Locked Error Recovery

[Alex Katebi]

Does anyone know what the course of action is for recovering from a table
locked error?

I dropped a table and I got the error code 6 (Table Locked Error). After two
weeks of pulling my hair I guessed that the error was not for my table being
dropped. It was for the sqlite_master table being modified by another
thread. If this is the case then I have two questions:

1) sqlite_master table does not belong to the application (for writing) it
belongs to the sqlite library. So sqlite should take care of this recovery
not the application.  The application should not even get this error because
the application had nothing to do with the sqlite_master table directly.

2) The sqlite3_errmsg(db) should mention which table is locked (if it is
possible). So that the next person does not spend two weeks on this.

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

[sqlite] SQLite in memory database concurrency model

[Alex Katebi]

There is a lot of information about disk file concurrency model.
I have not found much information regarding the in memory database
concurrency model.

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

Re: [sqlite] sqlite3_stmt declaration

[Alex Katebi]

Write a C test.c program that uses the desired type. Then do "gcc -E test.c
> test.txt".
This will expand/flatten all the macros. Open the test.txt file and look for
the type.

On Mon, Aug 25, 2008 at 5:30 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:

> Good afternoon list,
>
> I'm attempting to wrap SQLite with Managed C++ and I'm getting some
> compiler warnings as the compiler/linker is have trouble finding the
> declaration of the structure 'sqlite3_stmt', I've tried looking for it
> manually but I can't find it either all I can find is a typedef on line
> 2569 of slite3.h.  This isn't enough to stop the CLR compiler form
> complaining, I had a similar warning with the structure 'sqlite3' but
> including sqlite3Int.h resolved that warning as the structure is defined
> there, however tracking down the header file that defines 'sqlite3_stmt'
> seems to be leading no where...
>
> Cheers,
>
> Daniel Brown | Software Engineer @ EA Canada
> "The best laid schemes o' mice an' men, gang aft agley"
>
>
> ___
> 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] Table Lock Error Question

[Alex Katebi]

Hi,

  What can cause an in memory database a table lock error besides not
finalizing statements?

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

Re: [sqlite] database and table lock

[Alex Katebi]

Just a note. If you change the #define settings to the below values you also
get table locks

#define MAX_THREAD 100
#define MAX_LOOP 100


Thanks,
-Alex

On Sat, Aug 23, 2008 at 3:35 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I am getting a table lock for no apparent reason in my application.
>
> To reproduce this I have written a test file that behaves the same way with
> the difference that it reports database lock instead of table lock. My
> application database is in memory the test uses a file database. I think it
> is the same locking issue.
>
> Attached is "test.c"
> gcc -Wall test.c -lsqlite3 -o test
> run two test at the same time you will see the problem.
>
> Thanks,
> -Alex
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database and table lock

[Alex Katebi]

Hi,

I am getting a table lock for no apparent reason in my application.

To reproduce this I have written a test file that behaves the same way with
the difference that it reports database lock instead of table lock. My
application database is in memory the test uses a file database. I think it
is the same locking issue.

Attached is "test.c"
gcc -Wall test.c -lsqlite3 -o test
run two test at the same time you will see the problem.

Thanks,
-Alex
// gcc -Wall test.c -lsqlite3

#include 
#include 
#include 
#include 
#include 
#include 

sqlite3* gdb;

int ass(int a, int b)
{
  if(a != b)
{
  printf("Expected %d Got %d\n", a, b);
  printf("%s\n", sqlite3_errmsg(gdb));
  return 0;
}
  return 1;
}


#define MAX_LOOP 1
#define MAX_THREAD 1

void xTrace(void* self, const char* zSql)
{
  printf(" %d %s\n", (int) self, zSql);
}

void init_trace(void* self)
{
  sqlite3_trace(gdb, xTrace, self);
}

void* test(void* val)
{
  int i, id = (int)val;
  for(i = 0 ; i < MAX_LOOP ; i++)
{
  char* zSql;
  sqlite3_stmt* stmt;

  char* tbl = sqlite3_mprintf("tbl_%d_%d", getpid(), id);

  zSql = sqlite3_mprintf("create table %s(a)", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
  sqlite3_free(zSql);
  //printf("create stmt %p\n", stmt);

  zSql = sqlite3_mprintf("insert into %s values(7)", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
  sqlite3_free(zSql);
  //printf("insert stmt %p\n", stmt);

  zSql = sqlite3_mprintf("drop table %s", tbl);
  assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  assert(ass(SQLITE_OK, sqlite3_finalize(stmt))); 
  sqlite3_free(zSql);
  //printf("drop stmt %p\n", stmt);

  //zSql = sqlite3_mprintf("select tbl_name from sqlite_master where 
tbl_name=%Q", tbl);
  //assert(ass(SQLITE_OK, sqlite3_prepare_v2(gdb, zSql, -1, , 0)));
  //assert(ass(SQLITE_DONE, sqlite3_step(stmt)));
  //assert(ass(SQLITE_OK, sqlite3_finalize(stmt)));
  //sqlite3_free(zSql);
  //printf("select stmt %p\n", stmt);

  sqlite3_free(tbl);
}
  return 0;
}

int main()
{
  char* file = "test.db";
  //char* file = "/dev/shm/test.db";
  assert(ass(SQLITE_OK, sqlite3_open(file, )));
  init_trace(0);

  int i;
  pthread_t threads[MAX_THREAD];
  for(i = 0 ; i < MAX_THREAD ; i++)
{
  assert(pthread_create([i], 0, test, (void*) i) == 0);  
}
  for(i = 0 ; i < MAX_THREAD ; i++)
{
  assert(pthread_join(threads[i], 0) == 0);
}

  sqlite3_stmt *pStmt;
  while( (pStmt = sqlite3_next_stmt(gdb, 0))!=0 )
{
  static int count = 0;
  printf("%d: next %p\n", count++, pStmt);
  sqlite3_finalize(pStmt);
}

  assert(ass(SQLITE_OK, sqlite3_close(gdb)));
  return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite freestanding.

[Alex Katebi]

Most people are using it the way you described. I assume they had success
since it is the most widely deployed data base engine.

On Thu, Aug 21, 2008 at 12:07 PM, Ricardo Hawerroth Wiggers - Terceiro <
[EMAIL PROTECTED]> wrote:

> Hello.
>
> Has anyone used sqlite in a freestanding embedded environment? If anyone
> had success with it, how about the footprint? And storage medium, direct
> flash access?
>
> Thanks,
> Ricardo
> ___
> 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] Multiple connection to in-memory database

[Alex Katebi]

   This was the model that I was using. But I found out that I get a table
lock for dropping tables for no reason when you do interleave steps for
different prepares. Any body needs a proof I can create a test case for you.
No I did not forget to do finalize for the prior prepares.

   The better way to do this is:

   attach ':memory:' as db2
   attach ':memory:' as db3

   You can do 10 of these puppies and possibly increase it to 32 or 64
depending on your CPU. Your main database can be ':memory:' as well.

 I am going to change my test script to see if the lock problem is solved.

Thanks,
-Alex


On Tue, Aug 19, 2008 at 9:17 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> You don't need to open a second connection. The sole connection can be used
> from any thread.
>
>
> On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]>wrote:
>
>> Hi,
>>
>> I would like to use transactions from separate threads, each thread having
>> one connection to a single in-memory db.
>>
>> I just read in the sqlite doc that in-memory sqlite databases (using
>> sqlite3_open(":memory:", ...)) are private to a single connection.
>> Is there a way to open a second connection to the same in-memory database
>> (for example from a second thread)? Has someone ever tried to do (or
>> implement) that?
>>
>> Any help appreciated.
>>
>> Thanks
>> Vincent
>>
>>
>>
>>  
>> _
>> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
>> http://mail.yahoo.fr
>> ___
>> 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] Table Lock Error

[Alex Katebi]

Hi,
   For a in memory connection I get table lock error 6.
I have one in memory connection with many prepared statements for different
tables.
Depending how the statements are interleaved I get a table lock error when
dropping a table.
What is causing of this?

I did turn on tracing. I am confused by the tracing output. Sometimes it
shows some zSql statements twice. I see inserts and selects two times. Even
though my code is doing it once. Any help is appreciated.

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

Re: [sqlite] sqlite3_trace

[Alex Katebi]

Boy I was off on that one. The stmt is not going to help my case. The users
context that is already there can help me.
Sorry,
-Alex

On Wed, Aug 20, 2008 at 8:52 PM, Alex Katebi <[EMAIL PROTECTED]> wrote:

> Hi All,
>
> I am using the sqlite3_trace to track down a bug. It is lacking a vital
> information that is needed for tracing. The stmt handle is necessary in
> order to identify related statements. Notice the sqlite3_stmt that I have
> added below.
>
> void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*, 
> sqlite3_stmt*), void*);
>
> Thankd,
> -Alex
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_trace

[Alex Katebi]

Hi All,

I am using the sqlite3_trace to track down a bug. It is lacking a vital
information that is needed for tracing. The stmt handle is necessary in
order to identify related statements. Notice the sqlite3_stmt that I have
added below.

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*,
sqlite3_stmt*), void*);

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

Re: [sqlite] Problems building for VxWorks...

[Alex Katebi]

Write the missing functions with using the equivalent VxWorks functions.

example:

int exit(int a)
{
vxworks_exit(a);
}

On Wed, Aug 20, 2008 at 4:16 AM, Kershaw, Anthony (UK) <
[EMAIL PROTECTED]> wrote:

>
> Hi,
> Have been trying to compile (and run) the amalgamated version of sqlite
> 3.6.1 for VxWorks   5.5,
> which is proving quite trouble-some.
>
> I am currently using these switches
> -DSQLITE_OMIT_BUILTIN_TEST
> -DSQLITE_TEMP_STORE=3
> -DSQLITE_HOMEGROWN_RECURSIVE_MUTEX
> -DSQLITE_OMIT_AUTOVACUUM
> -DSQLITE_OMIT_LOAD_EXTENSION
> -DSQLITE_OMIT_LOCALTIME
> -DSQLITE_OMIT_TCL_VARIABLE
> -DNO_GETTOD
>
> Which will allow it to compile, but there are missing symbols, mainly
> because they don't occur in vxworks, the following functions don't
> exist.
> fctrl
> dup
> fsync
> access
> getpid
>
> Any work arounds people could think of would be greatly appreciated. I
> appreciate this heavily hits the locking mechanism code within sqlite...
>
> Regards,
>  Ant
>
>
>
> 
> This email and any attachments are confidential to the intended
> recipient and may also be privileged. If you are not the intended
> recipient please delete it from your system and notify the sender.
> You should not copy it or use it for any purpose nor disclose or
> distribute its contents to any other person.
> 
> ___
> 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] Multiple connection to in-memory database

[Alex Katebi]

You don't need to open a second connection. The sole connection can be used
from any thread.


On Tue, Aug 19, 2008 at 6:17 PM, vincent cridlig <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I would like to use transactions from separate threads, each thread having
> one connection to a single in-memory db.
>
> I just read in the sqlite doc that in-memory sqlite databases (using
> sqlite3_open(":memory:", ...)) are private to a single connection.
> Is there a way to open a second connection to the same in-memory database
> (for example from a second thread)? Has someone ever tried to do (or
> implement) that?
>
> Any help appreciated.
>
> Thanks
> Vincent
>
>
>
>  _
> Envoyez avec Yahoo! Mail. Une boite mail plus intelligente
> http://mail.yahoo.fr
> ___
> 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] SQLite Test Scripts

[Alex Katebi]

How can I run the SQLite TCL test scripts?

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

Re: [sqlite] regarding internal design of SQLite

[Alex Katebi]

No I don't mean offline use. I mean it would be nice to have links on the
sqlite.org for all documents and resources.

On Tue, Jul 8, 2008 at 1:42 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > Is there any way to get to all of these docs you mentioned from the home
> > page of the sqlite.org?
> > Thanks,
> > -Alex
> You mean, for offline use? If yes, then I'd click on Download, then
> scroll down to Documentation, then I'd clock on sqlite_docs_3_5_9.zip:
> http://sqlite.org/sqlite_docs_3_5_9.zip
> ___
> 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