date:20101207


On 7 Dec 2010, at 10:31am, CDN Mark wrote:

 newbie question, what I'd like to do is improve/add columns for a sadly 
 lacking database for a commercial proramme.  What I'd like to know is it 
 possible to add columns to an existing database without causing problems, add 
 extra info into these columns, and then somehow create a viewer of the 
 database.. It woud have to only be a straight viewer, no editing features 
 needed.

Yes.  For these things you need a programmer.  If you're not one yourself, you 
might want to hire one.

If you think that your changes would be useful to some good percentage of 
current users of the program, why not contact the company that produces the 
program ?  It may be happy to include your changes in the next version of the 
program.

 I did add a column, I think successfuly, but it went before the primary key 
 column, does this matter?

Does it matter to what ?

It's possible that the application that mainly uses the database will be 
terribly confused by this, since one common way to insert new data relies on 
there being a specific number of columns in a specific order.  Introducing a 
new column without changing the program to match may cause the program to fail 
in interesting ways.

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

Re: [sqlite] need help on escaping the ']' character in the identifier

On Tue, Dec 7, 2010 at 3:49 AM, 곽현미 jinsoch...@gmail.com wrote:

 Anyone please give me an advise,

 when i try the query below,

 CREATE TABLE Test] (no INTEGER),

 sqlite gives me the 'unrecognized token: ] ' result.


It works when I try it.  What version of SQLite are you using?  Are you
using the sqlite3.exe command-line shell, or some third-party product?




 Is there a way to escape the ']' character in the identifier?

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




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

[sqlite] SQLite - sorting and case-insensitive comparison of Unicode characters

2010-12-07 Thread Vladimir Ljepoja

Hi Guys,

 

SQLite has problems related to the sorting and case-insensitive comparison
of Unicode characters. It solved this problem by enabling ICU extension.
But, I couldn't find detailed information about its enabling within ADO.NET
2.0 Provider for SQLite (http://sqlite.phxsoftware.com/). I found some
threads in forums such as
http://sqlite.phxsoftware.com/forums/p/2349/9359.aspx#9359 and
http://sqlite.phxsoftware.com/forums/t/2351.aspx but it seems those threads
didn't help guys to make regular sorting and case-insensitive comparison of
Unicode characters solution.

Please, would you provide me some detailed description how to include the
ICU extension in ADO.NET 2.0 Provider for SQLite?

 

Best Regards

Vladimir Ljepoja

 

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

Re: [sqlite] SQLite 3 and FK

2010-12-07 Thread Wodka40[Google]



On 6 Dic, 14:43, Jay A. Kreibich j...@kreibi.ch wrote:
 On Mon, Dec 06, 2010 at 06:20:13PM +0600, Dagdamor scratched on the wall:
zac
Yes ...my error...i want translate name from italian
I resolve error!
Table models is auto generate from csv import file...and...not have a
PK !!! i dont see this macro error!

Correct SQL:
Anagrafica (Owner)
CREATE TABLE [Anagrafica] (
  [Idscheda] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [Nominativo] NVARCHAR(100) NOT NULL,
  [CF] NVARCHAR(16) NOT NULL,
  [Indirizzo] NVARCHAR(100) NOT NULL,
  [Citta] NVARCHAR(50) NOT NULL,
  [Prov] NCHAR(2),
  [CAP] NCHAR(5),
  [telefono] NVARCHAR(30),
  [fax] NVARCHAR(30),
  [email] NVARCHAR(40),
  [web] NVARCHAR(55),
  [datareg] TIMESTAMP NOT NULL DEFAULT
('datetime(''now'',''localtime'')'),
  [Mostra] BOOLEAN DEFAULT ('1'));

Modelli(Models)
CREATE TABLE Modelli (
  [IDschedamodello] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [Marca] NCHAR(25) NOT NULL,
  [Modello] NCHAR(45) NOT NULL);

and.
Vetture (Cars)

CREATE TABLE [Vetture] (
  [IDvettura] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [IDmodello] INTEGER NOT NULL CONSTRAINT [FKmodello] REFERENCES
[Modelli]([IDschedamodello]),
  [IDproprietario] INTEGER NOT NULL CONSTRAINT [FKproprietario]
REFERENCES [Anagrafica]([Idscheda]),
  [DataImmatricolazione] DATE,
  [Targa] NVARCHAR(20) NOT NULL);

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

[sqlite] SQLITE_CORRUPT when PRAGMA max_page_count is reached

2010-12-07 Thread uncle.f

Could Anybody please help?

We are running SQLite 3.7.3 on an embedded device and using C API to
interact with the DB. One of our goals is to ensure that the database
never grows past certain size (which is very small for this embedded box).

We open DB connection once and would like to keep it open for the
whole duration of C application.
The following PRAGMAs are used to open the database:

 page_size=1024
 max_page_count=5120
 count_changes=OFF
 journal_mode=OFF
 temp_store=MEMORY

When we hit the limit with the INSERT statement we get back
SQLITE_FULL, which is fine and is expected at some point. However, all
subsequent SELECTs or, in fact, any other DB interactions return
SQLITE_CORRUPT. That is until we close and re-open the same database
again, we can then SELECT,DELETE and UPDATE without a problem.

Is this intended behaviour?
Are we doing something wrong?

Thanks in advance,

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

[sqlite] Problem with disableTerm() and virtual tables

2010-12-07 Thread Hick Gunter

I am running SQLite 3.6.22 (debugging code enabled) with extensive use of 
virtual tables that behave as if their structures were as follows (unused 
fields have been omitted):

CREATE TABLE one (
aunsigned,
bunsigned,
cunsigned,
dunsigned,
stext);

CREATE INDEX one_pk ON one (a, b, c, d);

CREATE TABLE two (
one_rowidunsigned,
bunsigned,
eunsigned,
funsigned,
gunsigned);

CREATE INDEX two_pk ON two (one_rowid);

CREATE TABLE three (
aunsigned,
bunsigned,
eunsigned,
hunsigned);

CREATE INDEX three_pk ON three (a, b, e, h);

When I attempt to perform a certain join (shown below), the disableTerm 
function fails in the ALWAYS assertion, because the wtFlags field already has 
the TERM_CODED bit set. As far as I can tell, it is looking at the first 
constraint in the ON clause of the LEFT JOIN, possibly for the second time.

The problem goes away on any of the following conditions:

-  native tables are used as opposed to virtual tables
-  the first constraint of the WHERE clause (one.a = 3) is omitted
-  the constant from the WHERE clause is repeated in the ON clause 
(three.a = 3)

I suspect there is a subtle difference in parsing and/or code generation 
between native and virtual tables the leads to this effect

SELECT
   one.s,
   two.b,
   two.e,
   two.f
FROM  one
join  two   ON two.one_rowid = one.rowid
left join three ON three.a   = one.a and
   three.b   = two.b and
   three.e   = two.e and
   three.h   = two.f
where  one.a = 3 and
   two.g = 1;




Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 - 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How Execute Joint Queries In Sqlite?

2010-12-07 Thread Arunkumar T

I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
Supported. What Should I Do To Do This

Can You Help Me?

Regards

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

Re: [sqlite] SQLITE_CORRUPT when PRAGMA max_page_count is reached

On Tue, Dec 7, 2010 at 12:10 AM, uncle.f ad...@sboxx.org wrote:

 Could Anybody please help?

 We are running SQLite 3.7.3 on an embedded device and using C API to
 interact with the DB. One of our goals is to ensure that the database
 never grows past certain size (which is very small for this embedded box).

 We open DB connection once and would like to keep it open for the
 whole duration of C application.
 The following PRAGMAs are used to open the database:

  page_size=1024
  max_page_count=5120
  count_changes=OFF
  journal_mode=OFF
  temp_store=MEMORY

 When we hit the limit with the INSERT statement we get back
 SQLITE_FULL, which is fine and is expected at some point. However, all
 subsequent SELECTs or, in fact, any other DB interactions return
 SQLITE_CORRUPT. That is until we close and re-open the same database
 again, we can then SELECT,DELETE and UPDATE without a problem.

 Is this intended behaviour?
 Are we doing something wrong?


When you set journal_mode=OFF, then SQLite is unable to recover from any
kind of disk I/O error, either internal or external, because it is unable to
ROLLBACK the transaction in progress.  That means when it reaches the
max_page_count, the database will go corrupt, since running out of disk
space is a kind of internal disk I/O error.  There isn't really anything
that can be done about this since it is not possible to know the size of a
transaction in advance.  The journal_mode=OFF parameter is not a recommended
setting for applications that care about data preservation.



 Thanks in advance,

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




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

Re: [sqlite] How Execute Joint Queries In Sqlite?

2010-12-07 Thread Pavel Ivanov

 I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
 Supported. What Should I Do To Do This

You should re-think once more: do you really need a full outer join?
Maybe you can change your schema so that it was more clear and didn't
require full outer join for querying.


Pavel

On Tue, Dec 7, 2010 at 7:20 AM, Arunkumar T arunkumar.kol...@gmail.com wrote:
 I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
 Supported. What Should I Do To Do This

 Can You Help Me?

 Regards

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

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

Re: [sqlite] How Execute Joint Queries In Sqlite?

Arunkumar T arunkumar.kol...@gmail.com wrote:
 I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
 Supported. What Should I Do To Do This

You should rewrite your query in a way that doesn't require full outer joins (I 
personally don't recall ever having the need to use one). Or else, use some 
database management system that doesn't have lite in its name.
-- 
Igor Tandetnik

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

Re: [sqlite] How Execute Joint Queries In Sqlite?


On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote:

 I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
 Supported. What Should I Do To Do This
 
 You should re-think once more: do you really need a full outer join?
 Maybe you can change your schema so that it was more clear and didn't
 require full outer join for querying.

Just a note that it is probably very easy to do this.  You may even be able to 
phrase your SELECT with an INNER JOIN without changing the schema.  Pavel is 
being helpful, not insulting you.

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

Re: [sqlite] how to know the list of pragma that are database related or connection related

Vander Clock Stephane svandercl...@yahoo.fr wrote:
 Some pragma are set be connection,
 some by database (and all the connection to this database) and
 some by the engine (all database and all connections)

Could you give an example of this last category? I don't see by what possible 
mechanism could a PRAGMA issued in one process connected to one database file, 
affect a separate process connected to a different database file. Einstein's 
spooky action at a distance?

 the doc http://www.sqlite.org/pragma.html don't say anything about this

Most pragmas are per connection. Those that affect the format of the database 
file are explicitly called out. For example:

PRAGMA auto_vacuum = ...;

... auto-vacuuming must be turned on before any tables are created...

PRAGMA encoding = ...;

The second and subsequent forms of this pragma are only useful if the main 
database has not already been created.

PRAGMA page_size = bytes;

The page size may only be set if the database has not yet been created.

 so how to distinguish the pragma that must be call on every connection
 or just set one time after the DLL initialization ?

I'm not aware of any pragmas that could be set once and somehow magically take 
effect on all subsequent connections. How would you even set such a pragma? You 
need a connection to run PRAGMA statements.

 for exemple did i need to call the pragma read_uncommitted on every
 connection

Yes.

 i just
 open thought sqlite3_open_V2 or simply can i open a connection,
 execute the pragma read_uncommitted and close the connection and this
 will stay
 for all the future connection on all database ?

No.
-- 
Igor Tandetnik

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

[sqlite] Backup-restore behaviour

Hi,
we are using SQLite3 as an embeded database in our application and we'd like to 
select the proper
backup-restore mechanism. Based on the documentation available we've already 
decided to use
the CLI either .backup-.restore or .dump. However we have some questions we 
couldn't find in the
docs, ie.: how these operations work/effect a running application? Namely:

[1] .backup online?
Is it safe to run a .backup operation online that is without stopping the 
application?

[2] .backup non blocking?
Is the backup operation a non blocking or a blocking one that is: will it block 
normal transactions or
not?

[3] .restore online?
Can someone run a .restore operation online? The only effect we could see so 
far is the following.
After a successful restore the client (Ruby+Sequel) detects the schema change 
and fails for the
first time, however it refreshes itself and works for subsequent calls...

[4] Is the .dump operation like normal SQL operations? Is it safe to run it 
online? will it block other
SQL operations?

Cheers,
Gyula


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

Re: [sqlite] how to know the list of pragma that are database related or connection related

2010-12-07 Thread Vander Clock Stephane


 Some pragma are set be connection,
 some by database (and all the connection to this database) and
 some by the engine (all database and all connections)
 Could you give an example of this last category? I don't see by what possible 
 mechanism could a PRAGMA issued in one process connected to one database 
 file, affect a separate process connected to a different database file. 
 Einstein's spooky action at a distance?

for exemple page_size, encoding, auto_vaccum, legacy_file_format or also

 From the doc :
*PRAGMA secure_delete;
*When there are attached databases and no database is specified in the 
pragma, all databases have their secure-delete setting altered
The secure-delete setting for newly attached databases is the setting of 
the main database at the time the ATTACH command is evaluated

so doing this pragma will change the behavior of all the database and by 
the way all the connections ...


 so how to distinguish the pragma that must be call on every connection
 or just set one time after the DLL initialization ?
 I'm not aware of any pragmas that could be set once and somehow magically 
 take effect on all subsequent connections. How would you even set such a 
 pragma? You need a connection to run PRAGMA statements.

like page_size, encoding, auto_vaccum, legacy_file_format, secure_delete 
for exemple ? but i can say about this because the doc speak about it, 
but most of the time the doc say nothing :( for exemple what about 
cache_size when we use the Share_Cache mode ?

 for exemple did i need to call the pragma read_uncommitted on every
 connection
 Yes.

ok, that is clair :)

so can you confirm me that what i do is good :

at the initialization :
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
sqlite3_initialize;
sqlite3_enable_shared_cache(1);

after before creating any database i do :

PRAGMA page_size = 512
PRAGMA encoding = UTF-8
PRAGMA legacy_file_format = 0
PRAGMA auto_vacuum = NONE

after for EVERY new connection on the database(s) i do :

PRAGMA cache_size = 2000
PRAGMA count_changes = 0
PRAGMA journal_mode = MEMORY
PRAGMA journal_size_limit = -1
PRAGMA locking_mode = NORMAL
PRAGMA read_uncommitted = 1
PRAGMA secure_delete = 0
PRAGMA synchronous = OFF
PRAGMA temp_store = MEMORY


is it an good way to do ?

thanks you by advance
stephane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backup-restore behaviour


On 7 Dec 2010, at 1:03pm, Csom Gyula wrote:

 Based on the documentation available we've already decided to use
 the CLI either .backup-.restore or .dump.

Possibly simplest to answer some of it here.  

The dump/read pair use standard SQL commands like SELECT and INSERT.  The 
normal SQL locking system is used.  Since no PRAGMAs are used unless you 
yourself specify them these commands are by default network-safe, and 
multi-process safe.

In contrast the backup/restore operations use the Backup API as described here:

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

It copies the information as pages, not as individual data elements.  It does 
not lock the source file.  The process constantly monitors the source file and 
if it notices any change it automatically restarts from the beginning.  
Consequently, if you use this system with a database which is constantly being 
changed it will never finish an entire backup run.  However, this system is 
also network-safe and multi-process safe.

 However we have some questions we couldn't find in the
 docs, ie.: how these operations work/effect a running application? Namely:
 
 [1] .backup online?
 Is it safe to run a .backup operation online that is without stopping the 
 application?
 
 [2] .backup non blocking?
 Is the backup operation a non blocking or a blocking one that is: will it 
 block normal transactions or
 not?
 
 [3] .restore online?
 Can someone run a .restore operation online? The only effect we could see so 
 far is the following.
 After a successful restore the client (Ruby+Sequel) detects the schema change 
 and fails for the
 first time, however it refreshes itself and works for subsequent calls...
 
 [4] Is the .dump operation like normal SQL operations? Is it safe to run it 
 online? will it block other
 SQL operations?

[1] yes [2] non-blocking, restarting [3] yes, see below [4] yes, yes, and yes

Supplementary note for both '.read' and '.restore': you shouldn't use either of 
these while an application is accessing the file.  They both start off by 
deleting all the data in the database -- .restore does it for you and .read 
requires you to do it.  Until they're complete, data will be missing from the 
file.  You wouldn't want to leave an application running while you restored the 
data it was trying to access.

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

[sqlite] Date/Time query help

2010-12-07 Thread Marco Bambini

I have a table like:
CREATE TABLE foo (id integer PRIMARY KEY AUTOINCREMENT,connection_date text);

and I insert data into this table using the syntax:
INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));
INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));

I really need to use localtime and after the two simple INSERT above my table 
contains rows like:
id  connection_date
1   2010-12-07 14:39:43
2   2010-12-07 14:39:59

I need to create a query that is able to retrieve all the id(s) from foo where 
connection_date is older than 5 minutes starting from now (in localtime).

Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com






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

Re: [sqlite] how to know the list of pragma that are database related or connection related

Vander Clock Stephane svandercl...@yahoo.fr wrote:
 Some pragma are set be connection,
 some by database (and all the connection to this database) and
 some by the engine (all database and all connections)
 Could you give an example of this last category? I don't see by what 
 possible mechanism could a PRAGMA issued in one process
 connected to one database file, affect a separate process connected to a 
 different database file. Einstein's spooky action at a
 distance?  
 
 for exemple page_size, encoding, auto_vaccum, legacy_file_format

Those are of the second category - they affect one database file that is 
created by the connection on which the pragma is issued. If you then create 
another connection and another file, it won't be affected by these pragmas 
(unless you issue them on the new connection, too).

 From the doc :
 *PRAGMA secure_delete;
 *When there are attached databases and no database is specified in the
 pragma, all databases have their secure-delete setting altered
 The secure-delete setting for newly attached databases is the setting of
 the main database at the time the ATTACH command is evaluated
 
 so doing this pragma will change the behavior of all the database

All the databases ATTACHed to the current connection. Not all the databases in 
existence. This setting is per connection. Even if you open another connection 
to the same database, it won't have the same behavior.

 and by
 the way all the connections ...

Where do you get that from?

 so how to distinguish the pragma that must be call on every connection
 or just set one time after the DLL initialization ?

 I'm not aware of any pragmas that could be set once and somehow magically 
 take effect on all subsequent connections. How would
 you even set such a pragma? You need a connection to run PRAGMA statements. 
 
 like page_size, encoding, auto_vaccum, legacy_file_format, secure_delete
 for exemple ?

Those don't behave the way you seem to think they do. In any case, the 
documentation describes their behavior very carefully and precisely.

 for exemple what about
 cache_size when we use the Share_Cache mode ?

This does seem to be an omission in the documentation. It's not entirely clear 
how cache_size interacts with shared cache.

 so can you confirm me that what i do is good :
 
 at the initialization :
 sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
 sqlite3_initialize;
 sqlite3_enable_shared_cache(1);
 
 after before creating any database i do :
 
 PRAGMA page_size = 512
 PRAGMA encoding = UTF-8
 PRAGMA legacy_file_format = 0
 PRAGMA auto_vacuum = NONE
 
 after for EVERY new connection on the database(s) i do :
 
 PRAGMA cache_size = 2000
 PRAGMA count_changes = 0
 PRAGMA journal_mode = MEMORY
 PRAGMA journal_size_limit = -1
 PRAGMA locking_mode = NORMAL
 PRAGMA read_uncommitted = 1
 PRAGMA secure_delete = 0
 PRAGMA synchronous = OFF
 PRAGMA temp_store = MEMORY

Looks right to me.
-- 
Igor Tandetnik

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

Re: [sqlite] Date/Time query help

Marco Bambini ma...@sqlabs.net wrote:
 CREATE TABLE foo (id integer PRIMARY KEY AUTOINCREMENT,connection_date text);
 
 and I insert data into this table using the syntax:
 INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));
 INSERT INTO foo (connection_date) VALUES (datetime('now','localtime'));
 
 I need to create a query that is able to retrieve all the id(s) from foo 
 where connection_date is older than 5 minutes starting
 from now (in localtime). 

select id from foo where connection_date  datetime('now', '-5 minutes', 
'localtime');

Be careful on those days when the time switches between standard and daylight 
savings.
-- 
Igor Tandetnik

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

Re: [sqlite] WAL file size

2010-12-07 Thread Yoni Londner

Hi,

Yes, in this scheme the checksum is based on salt values and own frame 
content.a

Note that the current design solve a potential DB corruption bug in 
sqlite. current WAL design is base on the fact that once sqlite writes 
pages successfully to the WAL, they will never get corrupted. but this 
assumption is not true. take for example the following situation:

H 1 1 1 2 2 2 3 3 3 3

We have here 10 pages in 3 transactions. lets say that sqlite stated a 
checkpoint, succesfully checkpointed transaction 1 and 2, and started 
copy transaction 3 to the DB. while copying the first pages of 
transaction 3, pages from transaction 4 are written to the WAL.
now, since the pages most likely are not aligned to the sector size, the 
OS might read part of last page of transaction 3, and write it along 
with the first page of transaction 4.
If a power failure occur at this point, then the first pages of 
transactions 3 already copied to the DB, while last page of transaction 
3 is corrupted, so when recovering, sqlite will not complete copying 
transaction 3 to the DB, and DB we stay corrupted.

In my design, I used a padding to avoid this situation.

while this problem can occur on any device, it is more likely to happen 
on devices which use flash memory (mostly mobile devices), since the 
size of a sector of flash memory tend to be larger than on non flash memory.

Yoni.

On 3/12/2010 7:33 AM, Dan Kennedy wrote:

 In the current WAL format, the checksum for each frame is based on
 the contents of the frame, the salt-values in the wal header and the
 checksum of the previous frame.

 In this scheme is each frame checksum independent? i.e. each frame
 checksum is computed based only on the salt values in the WAL header
 and the frames own contents?


 Dan.



 On 12/02/2010 11:04 PM, Yoni Londner wrote:
 Hi,

 I will start out by stating that I am not deeply familiar with the
 sqlite   WAL file layout, but I will try anyway, from my current
 understanding:

 The general problem that needs to be solved is to allow SQLITE to be
 constantly used over time (with no 'idle' time where no sql operations
 are done, and it is not inside any transaction).

 The current WAL design does not allow this, since if there are all the
 time open read transactions and/or write transactions, the WAL will
 continue to grow forever.
 I copy/paste below a tiny C program that re-creates this with write
 transactions.

 Remember also that in the WAL file we 'gather' up a lot of work. If we
 do it in the background, this work-load can be smoothed up to run in
 parallel to the regular system work, but if we must make the SQL 'idle'
 (close all transactions) in order to execute the checkpoint, on a heavy
 load system this can mean halting the system for a long period of time
 (in our case, typically for 30 seconds!).

 This needs to be solved by two features to be added to WAL:

 1) 'incremental' checkpoint.

 2) WAL file recycling (this item can also be solved by two WAL files,
 but I think its better to make the WAL file format a little bit more
 complex than start having to handle in the code management of multiple
 files).

 Incremental checkpointing means that checkpointing can be done up until
 the first open transaction (mxFrame). This means both copying the WAL
 pages up-until mxFrame of the first open transaction to the DB file, and
 then MARKING those frames as 'DONE' (I will talk later on how to do the
 'DONE' marking).

 This means that from that point onwards - accessing those pages will be
 to the DB file, not the WAL file.

 WAL recycling will be done by writing pages to the beginning of the WAL
 file when a certain amount of pages from the beginning of the WAL file
 are 'checkpointed' (marked as DONE). This can also happen in the middle
 of a transaction.

 Example:

 Legend:
 H - header.
 1, 2, 3.. - page of transaction 1, 2, 3..
 C - commit marker.
 BOF1: beginning of WAL-1. EOF1: end of WAL-1
 BOF2: beginning of WAL-2. EOF2: end of WAL-2
 P: 64K of padding (junk data)

 WAL file with transactions 1 2 and 3 committed, and transaction 4 open:
 H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4
  +-- BOF1 +--- EOF1

 We continue to add to transaction 4:
 H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
  +-- BOF1 +--- EOF1

 In the meantime, we checkpointed transactions 1 and 2, because there is
 a read transaction working on transaction 3:
 H 1 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
  +-- BOF1 +--- EOF1
  +- checkpointed up to here

 No we decided we want to recycle. Since there is no read transaction
 open on transaction 1 and 2 (cannot be, since if you need a page from
 transaction 1, you will find it in the DB), we can reuse them:
 H 4 1 1C 2 2 2 2C 3 3 3 3C 4 4 4 4 4
  +-- BOF2+-- BOF1 +--- EOF1
  +-- EOF2+ checkpointed up to here
  +-- the 6th page of transaction 4

 And now we close transaction 4:
 H 4 4C 1C

Re: [sqlite] SQLite Introspection

2010-12-07 Thread Jay A. Kreibich

On Mon, Dec 06, 2010 at 05:25:43PM -0700, Tom Krehbiel scratched on the wall:

 The documentation for the analyze
 command says they can do a *select *on table *sqlite_stat1 *but when I do a
 select I get an error with 'no such table: sqlite_stat1'.

  Like the table sqlite_sequence (used for AUTOINCREMENT), the
  sqlite_statN tables are not created until they're needed.  You need to
  actually run the ANALYZE command at least once to create the
  sqlite_statN tables.

  Also, there can be more than one.  If SQLite is compiled with the
  SQLITE_ENABLE_STAT2 option, there will also be an sqlite_stat2 table,
  to go along with the sqlite_stat1 table.  In the future there may be
  more.

 I haven't been able to find anything in the documentation that
 indicates how to get at the trigger definition.

  They are in the sqlite_master table, just like everything else.  As
  far as I know, there is no trigger equivilant of PRAGMA table_info()
  or PRAGMA index_info().

   -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

Re: [sqlite] WAL file size

2010-12-07 Thread Dan Kennedy

On 12/07/2010 09:49 PM, Yoni Londner wrote:
 Hi,

 Yes, in this scheme the checksum is based on salt values and own frame
 content.a

 Note that the current design solve a potential DB corruption bug in
 sqlite. current WAL design is base on the fact that once sqlite writes
 pages successfully to the WAL, they will never get corrupted. but this
 assumption is not true. take for example the following situation:

 H 1 1 1 2 2 2 3 3 3 3

 We have here 10 pages in 3 transactions. lets say that sqlite stated a
 checkpoint, succesfully checkpointed transaction 1 and 2, and started
 copy transaction 3 to the DB. while copying the first pages of
 transaction 3, pages from transaction 4 are written to the WAL.
 now, since the pages most likely are not aligned to the sector size, the
 OS might read part of last page of transaction 3, and write it along
 with the first page of transaction 4.
 If a power failure occur at this point, then the first pages of
 transactions 3 already copied to the DB, while last page of transaction
 3 is corrupted, so when recovering, sqlite will not complete copying
 transaction 3 to the DB, and DB we stay corrupted.

In synchronous=full mode, we add extra copies of the last frame of
each transaction (the one with the commit flag set) to beat this.
So the WAL log would look like this:

H 1 1 1a 1b 2 2 2a 2b 3 3 3...

Frame 1b is a copy of 1a, and 2b is a copy of 2a. So although a power 
failure while writing the first frame of transaction 3 can damage
frame 2b, this doesn't matter as it is just a duplicate.

There may be more than one duplicate inserted if the device has very
large sectors.

 while this problem can occur on any device, it is more likely to happen
 on devices which use flash memory (mostly mobile devices), since the
 size of a sector of flash memory tend to be larger than on non flash memory.

It's a real problem and it does come up in practice. You are right
to include a solution in your plan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Introspection

2010-12-07 Thread BareFeetWare

On 07/12/2010, at 11:25 AM, Tom Krehbiel wrote:

 I haven't been able to find anything in the documentation that indicates how 
 to get at the trigger definition.

You can, of course, get the definition of any entity from the SQLite_Master 
table, such as:

select Name, SQL from SQLite_Master where Type = 'trigger' and Tbl_Name = 'My 
Table';

to get the Name and SQL of all triggers for a particular table (or view).

But there's no provided way to parse the trigger definition into its parameters 
(eg instead of|before|after, update of|update|delete|insert, steps).

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

Re: [sqlite] How Execute Joint Queries In Sqlite?

2010-12-07 Thread Jim Morris

A union of a left and right joins should do it.

On 12/7/2010 4:50 AM, Simon Slavin wrote:
 On 7 Dec 2010, at 12:37pm, Pavel Ivanov wrote:

 I Writed A FULL OUTER JOIN Query In Sqlite But I Got A Message Like Not
 Supported. What Should I Do To Do This
 You should re-think once more: do you really need a full outer join?
 Maybe you can change your schema so that it was more clear and didn't
 require full outer join for querying.
 Just a note that it is probably very easy to do this.  You may even be able 
 to phrase your SELECT with an INNER JOIN without changing the schema.  Pavel 
 is being helpful, not insulting you.

 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] Backup-restore behaviour

Thanks for your reply! 

It clarified the situation, that is backup-restore seems to be the best 
choice:) Just one more question. As you put backup-restore is based upon data 
pages (that could be binary a format I guess) not on plain SQL/data records. 
After all: Is the data page/backup format platform indenpendent? For instance 
can I restore a database on Windows from a backup created on a Linux box?

Cheers,
Gyula

Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; 
meghatalmaz#243;: Simon Slavin [slav...@bigfraud.org]
Küldve: 2010. december 7. 14:48
Címzett: General Discussion of SQLite Database
Tárgy: Re: [sqlite] Backup-restore behaviour

On 7 Dec 2010, at 1:03pm, Csom Gyula wrote:

 Based on the documentation available we've already decided to use
 the CLI either .backup-.restore or .dump.

Possibly simplest to answer some of it here.

The dump/read pair use standard SQL commands like SELECT and INSERT.  The 
normal SQL locking system is used.  Since no PRAGMAs are used unless you 
yourself specify them these commands are by default network-safe, and 
multi-process safe.

In contrast the backup/restore operations use the Backup API as described here:

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

It copies the information as pages, not as individual data elements.  It does 
not lock the source file.  The process constantly monitors the source file and 
if it notices any change it automatically restarts from the beginning.  
Consequently, if you use this system with a database which is constantly being 
changed it will never finish an entire backup run.  However, this system is 
also network-safe and multi-process safe.

 However we have some questions we couldn't find in the
 docs, ie.: how these operations work/effect a running application? Namely:

 [1] .backup online?
 Is it safe to run a .backup operation online that is without stopping the 
 application?

 [2] .backup non blocking?
 Is the backup operation a non blocking or a blocking one that is: will it 
 block normal transactions or
 not?

 [3] .restore online?
 Can someone run a .restore operation online? The only effect we could see so 
 far is the following.
 After a successful restore the client (Ruby+Sequel) detects the schema change 
 and fails for the
 first time, however it refreshes itself and works for subsequent calls...

 [4] Is the .dump operation like normal SQL operations? Is it safe to run it 
 online? will it block other
 SQL operations?

[1] yes [2] non-blocking, restarting [3] yes, see below [4] yes, yes, and yes

Supplementary note for both '.read' and '.restore': you shouldn't use either of 
these while an application is accessing the file.  They both start off by 
deleting all the data in the database -- .restore does it for you and .read 
requires you to do it.  Until they're complete, data will be missing from the 
file.  You wouldn't want to leave an application running while you restored the 
data it was trying to access.

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] Accessing multiple rows at once via a select statement

2010-12-07 Thread Jonathan R. Haws

 This isn't an SQLite problem; it's a C problem.  You need to make a
 C-style closure: function plus pointer to structure of persistent
 data.  Define:

struct write_closure {
mystructure *next;
};

 or whatever, then put a struct write_closure on the stack and pass a
 pointer to that.  Then you can mutate the members of the closure
 structure (in this case, have each callback invocation increment the
 next-pointer).

This closure defintion solved my problem (I actually put a void* in the 
closure to handle multiple structure definitions with a single closure.

I agree, this is a C problem from the beginning, but since it was 
directly related to pulling data from an SQLite database, I thought I 
would ask here and get quick responses.

Thanks to all for the help!

Jonathan


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

[sqlite] database size (again)

2010-12-07 Thread Laszlo Nemeth

Hi,

I have a database (6 in fact) of high-frequency data

create table eurusd (tick integer not null, bid float not null, ask  
float not null);

with 80M records currently and growing, freshly inserted, no deletions  
will ever take place, the schema will never change, and neither of the  
fields can be null. The size is already 3.6G (I put an index on it  
after bulk insert), which is a bit too much for me considering that  
the existing infrastructure (ie binary file 12bytes per record) is  
800M (which fits into memory and I don't even need to index).

Having checked older posts on sqlite-users, I noticed that this issue  
comes up frequently and normally the problem is either wrong choice of  
datatypes (ie text instead of integer), or unnecessary indices. None  
of which applies here.

Question: is it possible to recompile sqlite to force the  
representation of integers to be 4 bytes, and that of floats to be  
also 4 bytes. I would like to have no observable change in the  
behaviour of sqlite.

I
(1) am quite comfortable with the hacking,
(2) understand that the database will no longer be platform  
independent, nor compatible with anything else,
(3) tried to run the analyser to see if there is something fishy with  
half full pages, but it wants tcl8.6 and haven't gotten around to  
install it (will do),
(4) also checked the file format document, but that didn't give me any  
immediate hint how to achieve what  I'd like, though I only skimmed it  
through.

The point of doing this is that I get a smaller db, and I still get  
all the beautiful machinery built for sqlite.

Any suggestions, a complete solution, or  a no  that's not possible,  
because... will be much appreciated.
Thanks, Z

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

Re: [sqlite] Backup-restore behaviour


On 7 Dec 2010, at 3:49pm, Csom Gyula wrote:

 Just one more question. As you put backup-restore is based upon data pages 
 (that could be binary a format I guess) not on plain SQL/data records. After 
 all: Is the data page/backup format platform indenpendent? For instance can I 
 restore a database on Windows from a backup created on a Linux box?

You are correct.  Although this format is documented, it should be treated as 
an impenetrable black box in most circumstances.  Howver, the file format is 
identical on all platforms that run SQLite version 3.  You can, for example, 
take any SQLite database that was created on an iPhone, and read it on a 
Windows computer, or vice versa.

There are some minor format differences between different versions of SQLite.  
For instance, if you use the ALTER TABLE ADD COLUMN command introduced in 
3.2.0, versions before that will not be able to read the file.  However, 
changes like these are very rare (it's happen just twice and we're up to 3.7 
now) and if you keep to relatively similar version numbers on your two 
platforms you're unlikely to have problems.

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

Re: [sqlite] database size (again)

2010-12-07 Thread Jim Wilcoxson

A lot of the SQLite overhead is going to be in the stuff surrounding your
actual data; I'd be surprised if you saved much space by using fixed-size
ints vs the varints used by SQLite.  You didn't mention about indexes; if
you have any, they will take a lot of space because your row size is so
small.

Maybe write your own VFS for SQLite?

Definitely run sqlite3_analyzer before deciding anything.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup


On Tue, Dec 7, 2010 at 10:57 AM, Laszlo Nemeth lnem...@cs.bilgi.edu.trwrote:

 Hi,

 I have a database (6 in fact) of high-frequency data

 create table eurusd (tick integer not null, bid float not null, ask
 float not null);

 with 80M records currently and growing, freshly inserted, no deletions
 will ever take place, the schema will never change, and neither of the
 fields can be null. The size is already 3.6G (I put an index on it
 after bulk insert), which is a bit too much for me considering that
 the existing infrastructure (ie binary file 12bytes per record) is
 800M (which fits into memory and I don't even need to index).

 Having checked older posts on sqlite-users, I noticed that this issue
 comes up frequently and normally the problem is either wrong choice of
 datatypes (ie text instead of integer), or unnecessary indices. None
 of which applies here.

 Question: is it possible to recompile sqlite to force the
 representation of integers to be 4 bytes, and that of floats to be
 also 4 bytes. I would like to have no observable change in the
 behaviour of sqlite.

 I
 (1) am quite comfortable with the hacking,
 (2) understand that the database will no longer be platform
 independent, nor compatible with anything else,
 (3) tried to run the analyser to see if there is something fishy with
 half full pages, but it wants tcl8.6 and haven't gotten around to
 install it (will do),
 (4) also checked the file format document, but that didn't give me any
 immediate hint how to achieve what  I'd like, though I only skimmed it
 through.

 The point of doing this is that I get a smaller db, and I still get
 all the beautiful machinery built for sqlite.

 Any suggestions, a complete solution, or  a no  that's not possible,
 because... will be much appreciated.
 Thanks, Z

 ___
 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] WAL file size

2010-12-07 Thread Yoni Londner

Hi,

It's clear that for the WAL to be a general purpose solution, which can 
be used in systems which have constant stream of reads/writes from 
sqlite, it should not grow limitlessly under all circumstances (assuming 
the user run a checkpoint every once in a while).

I think my design can work (or at lease is a good start - and should be 
refined more), but I can't implement it, since I do not have a deep and 
profound enough knowledge in sqlite code and specifically in WAL format 
and behavior.

I will try to implement a much simpler solution, which have many 
limitations (e.g. might work only with single process system), but solve 
the problem to me. the idea is to keep the last transaction in memory, 
and not writing it to the wal, until the commit. this will increase the 
chances that the checkpoint in the background can finish copy all the 
pages to the DB, so next write will be at the start of the file.
This is good enough for me since:
1. I do not have logical transactions. I only use them to make writes 
faster, and I commit the transactions when sqlite cache is stressed, so 
they wont be very large.
1. I do not care about loosing the last transactions (in case of a crash).
3. My application is single process (multithreaded).

If it will be interesting for someone, I will be happy to post a patch 
of this simpler solution.

I hope to see a progress in this issue, and I am sure it will make 
sqlite more robust and thus more usable in a lot of situation.

Yoni.

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

Re: [sqlite] Backup-restore behaviour

Again, thanks for your response:)

Cheers,
Gyula

Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; 
meghatalmaz#243;: Simon Slavin [slav...@bigfraud.org]
Küldve: 2010. december 7. 17:00
Címzett: General Discussion of SQLite Database
Tárgy: Re: [sqlite] Backup-restore behaviour

On 7 Dec 2010, at 3:49pm, Csom Gyula wrote:

 Just one more question. As you put backup-restore is based upon data pages 
 (that could be binary a format I guess) not on plain SQL/data records. After 
 all: Is the data page/backup format platform indenpendent? For instance can I 
 restore a database on Windows from a backup created on a Linux box?

You are correct.  Although this format is documented, it should be treated as 
an impenetrable black box in most circumstances.  Howver, the file format is 
identical on all platforms that run SQLite version 3.  You can, for example, 
take any SQLite database that was created on an iPhone, and read it on a 
Windows computer, or vice versa.

There are some minor format differences between different versions of SQLite.  
For instance, if you use the ALTER TABLE ADD COLUMN command introduced in 
3.2.0, versions before that will not be able to read the file.  However, 
changes like these are very rare (it's happen just twice and we're up to 3.7 
now) and if you keep to relatively similar version numbers on your two 
platforms you're unlikely to have problems.

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] Backup-restore behaviour

2010-12-07 Thread Doug Currie


On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote:

 It clarified the situation, that is backup-restore seems to be the best 
 choice:) Just one more question. As you put backup-restore is based upon data 
 pages (that could be binary a format I guess) not on plain SQL/data records. 
 After all: Is the data page/backup format platform indenpendent? For instance 
 can I restore a database on Windows from a backup created on a Linux box?

If your Linux is on ARM, you should pay attention to the 
SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows 
(or x86 Linux for that matter).

e

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

[sqlite] enums

2010-12-07 Thread john darnell

Is there any way to build an SQLite table that recognizes enums?  It's a lot 
easier to understand at data that looks like this:

kHard
kSoft
kAlt
kSoft
kSoft
kHard

than this:

1
2
3
2
2
1

R,
John A.M. Darnell
Senior Programmer
Walsworth Publishing Company
Brookfield, MO
John may also be reached at 
johnamdarn...@gmail.commailto:johnamdarn...@gmail.com

Trivia SF question:  In the movie, THE MATRIX, just before Neo and Trinity take 
a harrowing ride up an elevator shaft holding on to an elevator cable, Neo 
mutters a single phrase. What is that phrase?



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

Re: [sqlite] database size (again)

2010-12-07 Thread Max Vlasov

On Tue, Dec 7, 2010 at 6:57 PM, Laszlo Nemeth lnem...@cs.bilgi.edu.trwrote:

 Hi,

 Question: is it possible to recompile sqlite to force the
 representation of integers to be 4 bytes, and that of floats to be
 also 4 bytes. I would like to have no observable change in the
 behaviour of sqlite.



Since integers are effectively packed in sqlite and I suppose you can not
map you tick to rowid (I guessed it from the name), the only possible
improvement could be floats. You could borrow this idea:
http://stackoverflow.com/questions/2775854/map-a-32-bit-float-to-a-32-bit-integerto
map it in your reading/writing code to save space for floats. In worst
case every 8 bytes float will be 4(5)-byte integer (5 possible due to the
internal packed format of sqlite), so maybe you will save 10-15 bytes per
record (I added also indexes)

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

Re: [sqlite] Backup-restore behaviour

Thanks for your response! 

We are currently running our app on a 64 bit machine (btw OS is Debian 
GNU/Linux). 

I was just wondering how portable is the backup format... (well according to 
the backup API - as far as I see the backup format is nothing but the database 
file format).

Gyula

Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; 
meghatalmaz#243;: Doug Currie [doug.cur...@gmail.com]
Küldve: 2010. december 7. 18:40
Címzett: General Discussion of SQLite Database
Tárgy: Re: [sqlite] Backup-restore behaviour

On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote:

 It clarified the situation, that is backup-restore seems to be the best 
 choice:) Just one more question. As you put backup-restore is based upon data 
 pages (that could be binary a format I guess) not on plain SQL/data records. 
 After all: Is the data page/backup format platform indenpendent? For instance 
 can I restore a database on Windows from a backup created on a Linux box?

If your Linux is on ARM, you should pay attention to the 
SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows 
(or x86 Linux for that matter).

e

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

Re: [sqlite] Backup-restore behaviour


On 7 Dec 2010, at 5:49pm, Csom Gyula wrote:

 I was just wondering how portable is the backup format... (well according to 
 the backup API - as far as I see the backup format is nothing but the 
 database file format).

Yes, it's just a copy of the source file.  No changes.

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

Re: [sqlite] enums

2010-12-07 Thread Max Vlasov

On Tue, Dec 7, 2010 at 8:38 PM, john darnell john.darn...@walsworth.comwrote:

 Is there any way to build an SQLite table that recognizes enums?


Hmm, I always thought that this is better to be implemented by a separate
table and lookup join.  Can you name a reason to do this internally by
sqlite?

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

Re: [sqlite] enums

2010-12-07 Thread john darnell

I have no better reason than that I'm used to it in my dealings with MySQL and 
C++.

It could save developer time and disk space, however, if it were efficiently 
implemented.

From the sense of your comment, I get that the answer is no...Oh well.  At 
least I learned something today.

Thanks for the information, Max.

R,
John

 -Original Message-
 From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
 On Behalf Of Max Vlasov
 Sent: Tuesday, December 07, 2010 12:15 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] enums
 
 On Tue, Dec 7, 2010 at 8:38 PM, john darnell
 john.darn...@walsworth.comwrote:
 
  Is there any way to build an SQLite table that recognizes enums?
 
 
 Hmm, I always thought that this is better to be implemented by a separate
 table and lookup join.  Can you name a reason to do this internally by
 sqlite?
 
 Max Vlasov
 ___
 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] enums

2010-12-07 Thread Dagdamor

john darnell john.darn...@walsworth.com писал(а) в своём письме Wed, 08  
Dec 2010 00:22:54 +0600:

 I have no better reason than that I'm used to it in my dealings with  
 MySQL and C++.

 It could save developer time and disk space, however, if it were  
 efficiently implemented.

 From the sense of your comment, I get that the answer is no...Oh well.   
 At least I learned something today.

 Thanks for the information, Max.

 R,
 John

 -Original Message-
 From: sqlite-users-boun...@sqlite.org  
 [mailto:sqlite-users-boun...@sqlite.org]
 On Behalf Of Max Vlasov
 Sent: Tuesday, December 07, 2010 12:15 PM
 To: General Discussion of SQLite Database
 Subject: Re: [sqlite] enums

 On Tue, Dec 7, 2010 at 8:38 PM, john darnell
 john.darn...@walsworth.comwrote:

  Is there any way to build an SQLite table that recognizes enums?
 

 Hmm, I always thought that this is better to be implemented by a  
 separate
 table and lookup join.  Can you name a reason to do this internally by
 sqlite?

 Max Vlasov

Enums are useful for keeping your data correct - i.e. you declate
a field as enum('alpha','beta','gamma') and you can be sure that that
field will be holding only one of those values, and nothing more, never.

Internally these values held as integers (0, 1, 2), one byte per field,
so they won't waste too much space :)

But SQLite has a principle of free-typing (or manifest typing), so I'm
not sure how enums idea conflicts with that principle...

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

[sqlite] Lemon behavior

2010-12-07 Thread Begelman, Jamie

I'm using Lemon for a non-sqlite related project and it is exiting with an 
assertion failure that I would like to understand. I have extracted the 
following small set of productions from a larger grammar. The list production 
happens to be the start symbol in the larger grammar.

list::= list DELIMITER command.
list::= command.
command ::= TERMINAL1.
command ::= TERMINAL2.

When I place these in a .y file by themselves and build the file, Lemon fails 
with:

Assertion failed: apx-type==SH_RESOLVED || apx-type==RD_RESOLVED || 
apx-type==SSCONFLICT || apx-type==SRCONFLICT || apx-type==RRCONFLICT || 
apy-type==SH_RESOLVED || apy-type==RD_RESOLVED || apy-type==SSCONFLICT || 
apy-type==SRCONFLICT || apy-type==RRCONFLICT, file lemon.c, line 1065

The odd thing is I use this pattern to parse lists of things elsewhere in the 
grammar without issue. Any insight appreciated.

TIA,
Jamie


The information contained in this message may be confidential and legally 
protected under applicable law. The message is intended solely for the 
addressee(s). If you are not the intended recipient, you are hereby notified 
that any use, forwarding, dissemination, or reproduction of this message is 
strictly prohibited and may be unlawful. If you are not the intended recipient, 
please contact the sender by return e-mail and destroy all copies of the 
original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite in WinCE Emulator - Disk I/O Error and change of pragma don´t work

2010-12-07 Thread Horacio Rabelo Pereira

Hi, all


I'm using SQLite version 3.7.2 ported to Windows CE, in a application developed 
using Lazarus


I got the compiled SQLITE3.DLL for Windows CE in the site www.parmaja.com


For now, I am  using  one emulator to run the native Windows CE application in 
a desktop computer running Windows 2000 SP4.


That emulator is the Microsoft Device Emulator V3.


Well, considering that is the first time I work in the environment, I  made a 
little application in Lazarus that load text files to the SQLite database. 


The process was done well, until the moment that  will close execute the 
command “commit”. I this moment the application crashes showing the message 
“Disk I/O Error”.


During the execution, the journal file for database is created, and after the 
crash it is removed and the database file remains without any modifications.


Searching in the Web, I found a note describing a workaround, that consist in 
change the pragma “jornal_mode” to value “truncate”.


For my surprise, all my tries to change the value of pragma “journal_mode” 
don't work. I try change via SQL DDL script, via SQL Expert and via 
application. 


In all the cases, when I access the database after the changes, the values of 
pragma “journal_mode” it ever “delete”, not the value that I change. I try 
“truncate”, “memory”, etc, without any success.


In resume, I have two problems

1)The “Disk  I/O Error” when I try to commit the transaction (running in a 
Windows CE Emulator)

2)The change of value of pragma that don't work.   


Thanks in advance

Horacio Pereira
Belo Horizonte – MG – Brasil
hora...@bysat.com.br
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite in WinCE Emulator - Disk I/O Error and change of pragma don??t work

2010-12-07 Thread Jay A. Kreibich

On Tue, Dec 07, 2010 at 06:08:28PM -0200, Horacio Rabelo Pereira scratched on 
the wall:


 Searching in the Web, I found a note describing a workaround, that
 consist in change the pragma ???jornal_mode??? to value ???truncate???.
 
 For my surprise, all my tries to change the value of pragma 
 ???journal_mode??? don't work. I try change
 via SQL DDL script, via SQL Expert and via application. 

  This PRAGMA is a property of the active database connection, not the
  database file.  You need to re-set it every time you call
  sqlite3_open_v2().

-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

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dagdamor
Sent: Tuesday, December 07, 2010 12:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] enums

john darnell john.darn...@walsworth.com писал(а) в своём письме Wed, 08
Dec 2010 00:22:54 +0600:

I have no better reason than that I'm used to it in my dealings with
MySQL and C++.

It could save developer time and disk space, however, if it were
efficiently implemented.

From the sense of your comment, I get that the answer is no...Oh well.
At least I learned something today.

Thanks for the information, Max.

R,
John

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Max Vlasov
Sent: Tuesday, December 07, 2010 12:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] enums

On Tue, Dec 7, 2010 at 8:38 PM, john darnell
john.darn...@walsworth.comwrote:

Is there any way to build an SQLite table that recognizes enums?

Hmm, I always thought that this is better to be implemented by a
separate
table and lookup join. Can you name a reason to do this internally by
sqlite?

Max Vlasov

Enums are useful for keeping your data correct - i.e. you declate
a field as enum('alpha','beta','gamma') and you can be sure that that
field will be holding only one of those values, and nothing more, never.

Internally these values held as integers (0, 1, 2), one byte per field,
so they won't waste too much space :)

But SQLite has a principle of free-typing (or manifest typing), so I'm
not sure how enums idea conflicts with that principle...

--
Regards,
Serge Igitov

I cannot find a reference to enum in any SQLite documentation. Hmm. I
suppose I could write a quick little function and use it as the Default...

On a completely off-topic subject, Mr. Igitov, your moniker is Dagdamor...for a
moment there I thought you might be Irish Celt (Dagda meaning the good father
and was a principle Irish god in olden times), but Igitov kinda makes me
think otherwise. Chat with me offline (meaning, outside the list) if you are
interested in pursuing this wholly trivial but still interesting topic.

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

[sqlite] FTS3 bug?

2010-12-07 Thread Iker Arizmendi

The function that opens a cursor for the simple tokenizer,
simpleOpen, does not set the pTokenizer member of the
returned cursor. Ie, it appears the following line is
missing:

c-base.pTokenizer = pTokenizer;

which causes problems in simpleNext . Possible bug?

Regards,
Iker

-- 
Iker Arizmendi
ATT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516

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

Re: [sqlite] SQLite in WinCE Emulator - Disk I/O Erro r and change of pragma don´t work


On 7 Dec 2010, at 8:08pm, Horacio Rabelo Pereira wrote:

 During the execution, the journal file for database is created, and after the 
 crash it is removed and the database file remains without any modifications.

it is important that the journal file is /not/ removed by anything except 
SQLite.  In the journal file is stored information used when SQLite makes 
contact with the database again.  Removing the journal file can lead to a 
corrupt database.

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

[sqlite] sqlite3_analyzer issue on MacOS X 10.6.5

2010-12-07 Thread Marco Bambini

Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue:

dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib
 Referenced from: /Users/marco/Desktop/sqlite3_analyzer
 Reason: image not found
Trace/BPT trap

Seems like a broken binary to me.
Any idea?
--
Marco Bambini
http://www.sqlabs.com






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

Re: [sqlite] sqlite3_analyzer issue on MacOS X 10.6.5

On Tue, Dec 7, 2010 at 6:22 PM, Marco Bambini ma...@sqlabs.net wrote:
 Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue:

 dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib
  Referenced from: /Users/marco/Desktop/sqlite3_analyzer
  Reason: image not found
 Trace/BPT trap

 Seems like a broken binary to me.
 Any idea?

I don't know how to statically link the TCL libraries on a Mac.  I
tried every combination of options I could think of and none of them
seem to work.

I think you just have to install TCL on your Mac in order to use
sqlite3_analyzer there.  Bummer.


 --
 Marco Bambini
 http://www.sqlabs.com






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




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

Re: [sqlite] sqlite3_analyzer issue on MacOS X 10.6.5


On 8 Dec 2010, at 12:15am, Richard Hipp wrote:

 I don't know how to statically link the TCL libraries on a Mac.  I
 tried every combination of options I could think of and none of them
 seem to work.
 
 I think you just have to install TCL on your Mac in order to use
 sqlite3_analyzer there.  Bummer.

It works fine on my Mac running 10.6.5.  I haven't knowingly installed TCL (or 
MacPorts, or anything of the sort), but I do have Apple's Developer Tools 
installed.

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

[sqlite] occasional SQLITE_PROTOCOL on synchronous=normal with WAL

2010-12-07 Thread Michael Barton

We've been testing switching our app to use WAL journaling mode.
We're using a snapshot of trunk from last week sometime.

We have a sort of weird occasional problem where we get
SQLITE_PROTOCOL when setting pragma synchronous=normal.  Our app has
a lot of concurrent connections to the database (within the same
process and by other processes), but I can't figure out anything else
that would be touching, much less locking, the db file.

Does anyone have suggestions for digging a little deeper on this problem?

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

Re: [sqlite] occasional SQLITE_PROTOCOL on synchronous=normal with WAL

On Tue, Dec 7, 2010 at 7:30 PM, Michael Barton m...@weirdlooking.com wrote:
 We've been testing switching our app to use WAL journaling mode.
 We're using a snapshot of trunk from last week sometime.

 We have a sort of weird occasional problem where we get
 SQLITE_PROTOCOL when setting pragma synchronous=normal.  Our app has
 a lot of concurrent connections to the database (within the same
 process and by other processes), but I can't figure out anything else
 that would be touching, much less locking, the db file.

 Does anyone have suggestions for digging a little deeper on this problem?

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




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

Re: [sqlite] occasional SQLITE_PROTOCOL on synchronous=normal with WAL

On Tue, Dec 7, 2010 at 7:30 PM, Michael Barton m...@weirdlooking.com wrote:
 We've been testing switching our app to use WAL journaling mode.
 We're using a snapshot of trunk from last week sometime.

 We have a sort of weird occasional problem where we get
 SQLITE_PROTOCOL when setting pragma synchronous=normal.  Our app has
 a lot of concurrent connections to the database (within the same
 process and by other processes), but I can't figure out anything else
 that would be touching, much less locking, the db file.

 Does anyone have suggestions for digging a little deeper on this problem?

This is due to a race condition that doesn't go away like we thought
it would.  SQLite tries and tries to get a lock it needs, and which
should only be transiently held, but gives up after 100 attempts.
There is a 1 microsecond delay between each attempt following the 5th.

This was first seen on Android a couple of weeks ago, and then late
last week on a Mac.  Dan was experimenting with some fixes just this
morning, and he was able to make the situation much harder to reach,
but was not able to eliminate it.  If you are willing to hack the
code, I believe Dan saw that the incidences of SQLITE_PROTOCOL were
reduced but not completely eliminated if you add a call to
sched_yield() right before or right after the call to usleep() in the
unixSleep() function.

You have to have multiple processes really hammering away at the
database in order to get this to happen, which is apparently what you
are doing, huh.  And we've only seen this on variations of unix, not
on windows.  Does this jive with what you are seeing?



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




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

Re: [sqlite] sqlite3_analyzer issue on MacOS X 10.6.5

In Tue, Dec 7, 2010 at 7:15 PM, Richard Hipp d...@sqlite.org wrote:
 On Tue, Dec 7, 2010 at 6:22 PM, Marco Bambini ma...@sqlabs.net wrote:
 Running sqlite3_analyzer on a MacOS X 10.6.5 results in the following issue:

 dyld: Library not loaded: /usr/local/lib/libtcl8.6.dylib
  Referenced from: /Users/marco/Desktop/sqlite3_analyzer
  Reason: image not found
 Trace/BPT trap

 Seems like a broken binary to me.
 Any idea?

 I don't know how to statically link the TCL libraries on a Mac.  I
 tried every combination of options I could think of and none of them
 seem to work.

 I think you just have to install TCL on your Mac in order to use
 sqlite3_analyzer there.  Bummer.

I finally figured out how to statically link TCL on a Mac (you have to
add -framework CoreFoundation to the compiler command-line)  I
rebuilt using this recipe and put up a new image.  Please download the
latest and try again.




 --
 Marco Bambini
 http://www.sqlabs.com






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




 --
 D. Richard Hipp
 d...@sqlite.org




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

[sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Rich Shepard

   What happened to the souce tarball of the amalgamation? I'm wondering if
the change to the autoconf version will break the Slackbuild script I use.

   Was there something wrong with the tarballs of previous versions?

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

Re: [sqlite] SQLite-3.7.4 Amalgamation?

On Tue, Dec 7, 2010 at 8:17 PM, Rich Shepard rshep...@appl-ecosys.com wrote:
   What happened to the souce tarball of the amalgamation? I'm wondering if
 the change to the autoconf version will break the Slackbuild script I use.

   Was there something wrong with the tarballs of previous versions?

I changed to a more consistent naming scheme for all of the build products:

 sqlite-PRODUCT-OS-ARCH-VERSION.zip

with the OS and ARCH being omitted for source-code products.  In your
case, you probably are looking for

http://www.sqlite.org/sqlite-amalgamation-3070400.zip

which is the very first build product at the top of the page at

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

Or maybe you want

http://www.sqlite.org/sqlite-autoconf-3070400.tar.gz

which is the second build product from the top.  The -amalgamation-
product is just the sqlite3.c source file and a few others.  The
-autoconf- product contains sqlite3.c together with a configure
script, ready to build on your unix-like machine.


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




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

Re: [sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Rich Shepard

On Tue, 7 Dec 2010, Richard Hipp wrote:

 I changed to a more consistent naming scheme for all of the build products:

 sqlite-PRODUCT-OS-ARCH-VERSION.zip

 with the OS and ARCH being omitted for source-code products.  In your
 case, you probably are looking for

Richard,

   That's how it's been for a while.

http://www.sqlite.org/sqlite-amalgamation-3070400.zip

   I will change the script so it unzips rather than untars, and it looks for
zeros rather than periods in the version number.

Thanks,

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

Re: [sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Ladislav Bodnar

On Wednesday 08 December 2010, Richard Hipp wrote:
 I changed to a more consistent naming scheme for all of the build
 products:
 
  sqlite-PRODUCT-OS-ARCH-VERSION.zip
 
 with the OS and ARCH being omitted for source-code products.  In your
 case, you probably are looking for
 
 http://www.sqlite.org/sqlite-amalgamation-3070400.zip

So the version number is no longer 3.7.4, but 3070400?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 bug?

2010-12-07 Thread Dan Kennedy

On 12/08/2010 04:18 AM, Iker Arizmendi wrote:
 The function that opens a cursor for the simple tokenizer,
 simpleOpen, does not set the pTokenizer member of the
 returned cursor. Ie, it appears the following line is
 missing:

  c-base.pTokenizer = pTokenizer;

 which causes problems in simpleNext . Possible bug?

How do we reproduce the problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Darren Duncan

Richard Hipp wrote:
 I changed to a more consistent naming scheme for all of the build products:
 
  sqlite-PRODUCT-OS-ARCH-VERSION.zip
 
 with the OS and ARCH being omitted for source-code products.  In your
 case, you probably are looking for
 
 http://www.sqlite.org/sqlite-amalgamation-3070400.zip
 
 which is the very first build product at the top of the page at
 
 http://www.sqlite.org/download.html
 
 Or maybe you want
 
 http://www.sqlite.org/sqlite-autoconf-3070400.tar.gz
 
 which is the second build product from the top.  The -amalgamation-
 product is just the sqlite3.c source file and a few others.  The
 -autoconf- product contains sqlite3.c together with a configure
 script, ready to build on your unix-like machine.

I am also working with automated scripts, which now have to be updated to use 
either the new style or old style depending on the user-requested SQLite 
version.  (DBD::SQLite bundles a SQLite version, and includes a script users 
can 
use to pull in a different, albeit typically newer, SQLite version to use with 
DBD::SQLite instead.)

With respect to the two files:

   sqlite-amalgamation-3070400.zip

   sqlite-autoconf-3070400.tar.gz

A few questions:

1.  Why does the file sqlite3ext.h differ between the 2 of them?  The one in 
-amalgamation had added some declarations from 
sqlite-amalgamation-3.7.3.tar.gz, 
but the one in -autoconf is the same as for 3.7.3; I would expect -autoconf to 
be a proper superset.

2.  Why does -amalgamation unzip to the folder name 
sqlite-amalgamation-3070400 but -autoconf untars to the folder name 
sqlite-3.7.4?  Why the inconsistent use of version formats?

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

Re: [sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/07/2010 08:45 PM, Darren Duncan wrote:
 I am also working with automated scripts, which now have to be updated to use 
 either the new style or old style depending on the user-requested SQLite 
 version.  (DBD::SQLite bundles a SQLite version, and includes a script users 
 can 
 use to pull in a different, albeit typically newer, SQLite version to use 
 with 
 DBD::SQLite instead.)

And my python stuff does the same thing and is also now has to cope with
different naming styles.  It also broke the other python SQLite wrapper.

It would have been nice if there had been a least little forewarning and
consultation.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkz/EuUACgkQmOOfHg372QSQ6wCgh2UNn2KQk5FWLXw62aEnBMiF
jA0An3wbKeP1y7FUQOf0AdDlUgD95ARM
=1FeE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite-3.7.4 Amalgamation?

2010-12-07 Thread Darren Duncan

Roger Binns wrote:
 On 12/07/2010 08:45 PM, Darren Duncan wrote:
 I am also working with automated scripts, which now have to be updated to 
 use 
 either the new style or old style depending on the user-requested SQLite 
 version.  (DBD::SQLite bundles a SQLite version, and includes a script users 
 can 
 use to pull in a different, albeit typically newer, SQLite version to use 
 with 
 DBD::SQLite instead.)
 
 And my python stuff does the same thing and is also now has to cope with
 different naming styles.  It also broke the other python SQLite wrapper.
 
 It would have been nice if there had been a least little forewarning and
 consultation.

For my part, I have already committed an update to the DBD::SQLite script so 
that it now works with the old and new SQLite dist versions.

Moreover, the script now lets users specify a SQLite version in either the old 
or new format for any version, and will normalize as appropriate, so the users 
at least don't even have to know that there was a change.

If anyone else can benefit from my solution to speed their own similar updates, 
see 
https://fisheye2.atlassian.com/browse/cpan/trunk/DBD-SQLite/util/getsqlite.pl#r13338
 
and click on raw.

I expect it will receive third-party testing before being released though it 
works for me.

That said, I will like to know soon if any further changes will be made, before 
this DBD::SQLite update is pushed to CPAN and users try self-updating with it.

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

Re: [sqlite] Problem with disableTerm() and virtual tables

2010-12-07 Thread Dan Kennedy



 When I attempt to perform a certain join (shown below), the disableTerm 
 function fails in the ALWAYS assertion, because the wtFlags field already has 
 the TERM_CODED bit set. As far as I can tell, it is looking at the first 
 constraint in the ON clause of the LEFT JOIN, possibly for the second time.

 The problem goes away on any of the following conditions:

 -  native tables are used as opposed to virtual tables
 -  the first constraint of the WHERE clause (one.a = 3) is omitted
 -  the constant from the WHERE clause is repeated in the ON clause 
 (three.a = 3)

 I suspect there is a subtle difference in parsing and/or code generation 
 between native and virtual tables the leads to this effect

I couldn't immediately reproduce this using the echo virtual
table module. It could have something to do with the scanning
costs your virtual tables are returning to SQLite.

Does this happen in 3.7.4?

Can you provide us with code for virtual tables that cause the bug
to occur?

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

[sqlite] Concern over runtime memory growth?

2010-12-07 Thread Lynton Grice

Hi there,

 

I have implemented a queue using SQLIte in WAL mode and it seems to be
working well..

 

Now that I am testing and send thousands of messages to the queue I am
watching the memory growth of the application grow and grow. 

 

I have make a queue shared lib / dll and have seen that it is the SQLIte
calls I am making that are causing memory to grow and grow as I have
commented out other stuff and all is fine. So basically I have commented out
the queue stuff and the application runs smooththe moment I add in the
queue lib again the memory grows ;-(

 

I have the following set:

 

PRAGMA journal_mode=wal

PRAGMA wal_checkpoint

PRAGMA synchronous=normal

PRAGMA temp_store=memory

 

And when I open the queue I use:

 

rc = sqlite3_open_v2(queueName,handle, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE | SQLITE_OPEN_CREATE, NULL);

 

So as an example I have a function peekByID

 

So the steps I follow are:

 

sqlite3_prepare_v2(handle,peekText,-1,stmt,0 );

..

rc = sqlite3_step(stmt);

...

sqlite3_finalize(stmt);

 

But memory just grows and grows with this call.

 

I have tried to open the queue and close the queue after each call and that
seems to help, but surely that is not the answer?

 

I have let the application run and it was still climbing at 500 MB
memory.;-(

 

I have noticed that when I comment out 

 

rc = sqlite3_step(stmt);

 

it is much better

 

Is their anything I can do to clean up the memory better? Or just get things
neater?

 

Thanks

 

Lynton

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

Re: [sqlite] Concern over runtime memory growth?

2010-12-07 Thread Max Vlasov

On Wed, Dec 8, 2010 at 8:58 AM, Lynton Grice lynton.gr...@logosworld.comwrote:

 Hi there,


 I have implemented a queue using SQLIte in WAL mode and it seems to be
 working well..


 Now that I am testing and send thousands of messages to the queue I am
 watching the memory growth of the application grow and grow.


Lynton , please report your OS and sqlite version

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

Re: [sqlite] Concern over runtime memory growth?