Re: [sqlite] Maintaining data and foreign keys question

Sorry for the double-post, I didn't see the first one appear for a bit and I assumed it didn't go out. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupted database

On Wed, Oct 14, 2009 at 6:06 PM, Dan Kennedy wrote: > >> The problem I think may be people using TerminateThread();  that's how >> you hard kill a thread.  It seems that can interrupt an I/O >> operation -- >> ie an operation that writes more than one cluster at a time.  

Re: [sqlite] Corrupted database

Some threads were hard killed (using the .NET "Thread.IsBackgroundThread" mechanism, which uses TerminateThread AFAIK) in that version during normal operation, but none of them do database writes. They could have been doing database reads though. The whole application has been hard-killed few

[sqlite] group_concat optimization

I make use of group_concat aggregate function and I found it very slow, especially when there are thousands of lines per group. This is because it reallocates memory on each processed row. I changed just one line in sqlite3StrAccumAppend(): szNew += N + 1 to something like this: do{

[sqlite] Database in tmpfs

Hello, I am creating a db in tmpfs with following PRAGMA settings: PRAGMA encoding = "UTF-8"; PRAGMA default_cache_size = 4000; PRAGMA synchronous = OFF; PRAGMA temp_store = MEMORY; PRAGMA journal_mode = OFF; I am creating four tables on the run and inserting 8000 records in two of these

Re: [sqlite] Tackling the Date Scan SQL

Tried that. It doesn't work. For example, when I used 09/01 as my start and 04/01 as my ending, what I got back was: 1988 1 4 1988 1 5 1988 1 6 ... ... Instead of: 1988 9 1 1988 9 2 ... ... 1989 3 30 1989 3 31 1989 4 1 1989 9 1 1989 9 2 ... ... 1990 3 31 1990 4 1 ... ... Etc.

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

Worked cleanly now. Thanks again. Mel On Wed, Oct 14, 2009 at 7:02 PM, William Kyngesburye wrote: > Odd, works fine here. ... ah, make sure you use the --disable- > dependency-tracking option. The default dependency tracking does not > work with multiple architectures.

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

Odd, works fine here. ... ah, make sure you use the --disable- dependency-tracking option. The default dependency tracking does not work with multiple architectures. It's just a compilation process and not using it has no effect on the generated code. On Oct 14, 2009, at 7:42 PM, Melton

Re: [sqlite] Tackling the Date Scan SQL

Try to change this: > "ORDER BY Year, ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay > & ") + 1300) % 1300" to this: > "ORDER BY Year, Month, Day" Pavel On Wed, Oct 14, 2009 at 8:08 PM, Rick Ratchford wrote: > With Igor's help, I have this SQL

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

I got a compile error when the 64bit flag was include. Worked fine if I just use the 32bit flag. Thanks for your help. Mel On Wed, Oct 14, 2009 at 6:01 PM, William Kyngesburye wrote: > Add this to your configure command (applies to most all configures): > > CFLAGS="-arch

[sqlite] Tackling the Date Scan SQL

With Igor's help, I have this SQL statement that pulls out records based on a start and stop date. "SELECT Year, Month, Day, Open, High, Low, Close FROM [" & gsTableName & "] " & _ "WHERE ((Month - " & lngStartMth & ")*100 + (Day - " & lngStartDay & ") + 1300) % 1300 <= ((" & _ lngEndMth -

Re: [sqlite] Mac OS Snow Leopard 32bit vs 64bit

Add this to your configure command (applies to most all configures): CFLAGS="-arch i386 -arch x86_64" On Oct 14, 2009, at 4:46 PM, Melton Low wrote: > I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard). > > Version of Sqlite I want to build is 3.6.19. When running 'configure >

Re: [sqlite] readers and writer

On 14 Oct 2009, at 11:19pm, Roger Binns wrote: > Simon Slavin wrote: >> On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote: >> >>> sqlite> .timeout 1 >> >> What is it that that command does ? I looked for a PRAGMA but didn't >> find one. Or does it correspond to a check-and-retry loop which the

[sqlite] Mac OS Snow Leopard 32bit vs 64bit

I am on a Mac laptop running Mac OS X 10.6.1 (Snow Leopard). Version of Sqlite I want to build is 3.6.19. When running 'configure --help' I didn't find an option to set the build to 32bit. Is sqlite automatically build as a 32bit app with 32bit libraries? Did I miss an option? Mel

Re: [sqlite] readers and writer

On 14 Oct 2009, at 7:45pm, Pavel Ivanov wrote: > sqlite> .timeout 1 What is it that that command does ? I looked for a PRAGMA but didn't find one. Or does it correspond to a check-and-retry loop which the programmer has to do yourself in her or his own code ? Simon.

[sqlite] Conflict with SQLite ODBC Driver and MS Office 2007 ?

Hello! Not sure where to ask this question, so I apologize if this is not appropriate. I want to use SQLite as the basis for a new project where I work. I'm new to SQLite - this would be my first use of it beyond some simple test applications. Plan to use REBOL to develop a GUI frontend. For

Re: [sqlite] Corrupted database

On Oct 14, 2009, at 2:59 PM, McClellen, Chris wrote: > I think the issue is : > > Thread 1 does exitprocess/terminateprocess (or process.kill, or > anything like that) > Thread 2 does write() -- the write I believe can be interrupted when > partially complete in these cases (only part of the

Re: [sqlite] Corrupted database

I think the issue is : Thread 1 does exitprocess/terminateprocess (or process.kill, or anything like that) Thread 2 does write() -- the write I believe can be interrupted when partially complete in these cases (only part of the blocks have been written to disk, the others are not even

Re: [sqlite] Corrupted database

Hello Chris, Customer PC's right? I've never had a corrupt DB3 here and that includes me jumping out of the debugger mid-transaction but, I do have customers who get corrupted DB's even with sync set to full. For some customers, deleting the DB3's, running once to let them get created and then

Re: [sqlite] Corrupted database

On Oct 14, 2009, at 2:51 PM, Reusche, Andrew wrote: > We get a "2" returned when we check synchronous. I think that's the > enum for "full". We do not explicitely kill any threads, but when we > decide to reboot or shutdown, we call "ExitProcess(0)" without > stopping > any DB threads, and

Re: [sqlite] Corrupted database

We get a "2" returned when we check synchronous. I think that's the enum for "full". We do not explicitely kill any threads, but when we decide to reboot or shutdown, we call "ExitProcess(0)" without stopping any DB threads, and I'm sure this isn't very healthy. Andrew This communication

Re: [sqlite] Cannot ATTACH a database that contains a VIEW

> Is there any workaround for this? Maybe there is a trick to define the > view in a different way in order to make clear that it relates to the > database it's located in, and not to any database whichever is "main" > currently? Are you referencing "main" in your view explicitly? If so then

Re: [sqlite] readers and writer

> If there is a timeout and it is set to 0 by default then that it is not > very useful. It's indeed so and it's useful in some cases. And you know, everything can be tested pretty easily. Just make some test database and execute in one terminal: sqlite> create table t (a); sqlite> begin;

[sqlite] Cannot ATTACH a database that contains a VIEW

Hi, it looks like I cannot ATTACH a database that contains a VIEW: sqlite> ATTACH DATABASE 'G:\Project\ASK_ORA\ask.db' AS dbsrc; SQL error: malformed database schema (ask_art) - view ask_art cannot reference objects in database main sqlite> Obviously SQLite tries to apply the view to tables in

Re: [sqlite] readers and writer

Simon Slavin wrote: > On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote: > > >> Yes, I understood that, but the impression I got is that SELECT will >> place shared lock on the database. While INSERT or UPDATE will first >> place PENDING lock indicating that it wants to write. >> > > Okay, I

Re: [sqlite] Is it possible to combine a collation and a search

Andy, >Hwever, what I want to do is seach for all of these varients too, i.e. so >that if I search for e that I get all of the e and accented e' etc, is >this >possble using something like the collation, or do I need to specify all of >them individually? I'm about to release the beta of an

Re: [sqlite] readers and writer

On 14 Oct 2009, at 5:39am, Dmitri Priimak wrote: > Yes, I understood that, but the impression I got is that SELECT will > place shared lock on the database. While INSERT or UPDATE will first > place PENDING lock indicating that it wants to write. Okay, I see what you mean. I don't know how

Re: [sqlite] Corrupted database

Yes, I agree. What I am now trying to find out is if things like running a service or .NET service causes terminatethread to be called behind the scenes as some kind of cleanup. The testing was to show that this can be a problem, to characterize why some dbs can get corrupted on "normal exits"

Re: [sqlite] how to represent a tree in SQL

just to throw in my two bits: I have done a lot of work with trees in SQL, and IMHO, the best method BY FAR is the one described in the link below (mysql article), mainly due to its capability to handle siblings and descendants. for example, the self-join, parent_node method described elsewhere

Re: [sqlite] Corrupted database

Hello Chris, It's always a mistake to use TerminateThread. Even Microsoft warns against it. >From MSDN: - If the target thread owns a critical section, the critical section will not be released. - If the target thread is allocating memory from the heap, the heap lock will not be released. -

Re: [sqlite] Corrupted database

> The problem I think may be people using TerminateThread(); that's how > you hard kill a thread. It seems that can interrupt an I/O > operation -- > ie an operation that writes more than one cluster at a time. Meaning, > synch = full may have nothing to do with it. If you have to say write

Re: [sqlite] Corrupted database

Yes, if we are in the middle of a lot of updates/inserts and just terminate the thread, pragma integrity_check from the sqlite3 command line tool will report corruption at times. Normally, when we hard kill a thread in the middle of these ops, a journal is left behind. I think we only see

Re: [sqlite] how to represent a tree in SQL

Thanks all! It will take me a while to learn the materials in your posts. One thing I care most is DYNAMICS of a tree. I am not sure it is covered in the mentioned references before I study them. Thanks all again! On Wed, Oct 14, 2009 at 9:18 AM, O'Neill, Owen wrote: >

Re: [sqlite] how to represent a tree in SQL

On Wed, 14 Oct 2009, Michael Chen wrote: > I am developing a numerical application, where a single rooted dynamic > tree is the main data structure. I intended to use SQLite for this purpose > and also for other data as well. However I have no reference how to > represent a tree using tables. I

Re: [sqlite] how to represent a tree in SQL

look this: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html is for mysql but aplies to every sql DB - Mensaje original De: Michael Chen Para: sqlite-users@sqlite.org Enviado: mié, octubre 14, 2009 10:40:45 AM Asunto: [sqlite] how to

[sqlite] SQLite version 3.6.19

SQLite version 3.6.19 is now available on the SQLite website http://www.sqlite.org/ Version 3.6.19 adds support for enforcing foreign key constraints, including support for deferred constraints and cascading deletes and updates. Foreign key constraint enforcement is turned off by

Re: [sqlite] how to represent a tree in SQL

Isn't most of the complexity in the software? Representing a tree is fairly simple, it just requires a foreign key in the table referencing the primary key of the same table... Create table tree_node ( node_id integer primary key, distance_from_root integer not null,-- 0

[sqlite] how to represent a tree in SQL

Dear there, I am developing a numerical application, where a single rooted dynamic tree is the main data structure. I intended to use SQLite for this purpose and also for other data as well. However I have no reference how to represent a tree using tables. I need these functionalities: (1) basic

Re: [sqlite] sqlite3 issue in sqlite3VdbeReset

> Why do sqlite3VdbeReset() always produces problem; What problem? Why 'always' if only you complain and it works in other's applications? > Could you please provide any help in this issue. Run valgrind on your application and see where memory is getting corrupted or double-freed. Pavel On

Re: [sqlite] when to create temporary disk file for group by and order by?

AFAIK, SQLite's optimizer doesn't unfold inner statements to get one query that wouldn't require to store temporary data. So in your query SQLite needs temporary space to store results of 2 inner queries. Then it joins these results and most probably it does it not so effectively as you might

Re: [sqlite] Hi, new member here (and also my first question)

Do you initialize your sqlite3_stmt* pointer in constructor? Is there any corrupting memory code in other parts of your application? You know, it's pretty hard to read and debug your application without seeing it. But believe us there's nothing wrong with SQLite and sqlite3MemFree(), something

Re: [sqlite] Corrupted database

On Mon, Oct 12, 2009 at 8:56 PM, McClellen, Chris wrote: > What is your synchronous set to?  Full?  FYI If you are using .NET data > providers, it is set to "Normal" by default. Normal or Off, but no power failure was involved. (Yes, System.Data.SQLite is used) > If it

Re: [sqlite] readers and writer

--- prii...@stanford.edu wrote: > > Simon Slavin wrote: >> On 14 Oct 2009, at 1:21am, priimak wrote: >> >> >>> I am heaving small problem with sqlite. I have a webapp which connects >>> to the database using sqlite-jdbc and performs SELECTs to response to >>> different GET requests, while

Re: [sqlite] Segfault on a specific input

On Oct 14, 2009, at 4:30 AM, Wentao Han wrote: > Hi there, > > When I type the following statements of SQL into sqlite3, it crashed > with > segmentation fault. > CREATE TABLE todo ( > id integer primary key, > title text, > created timestamp default (now()), > done boolean default 'f' >

Re: [sqlite] Select * from table where field = "value" does not work when "value" is also a field....

2009/10/13 Hillebrand Snip : > I have a database with the following fields: > 1) Status (it will hold values like "Open", "Closed", "Submitted"... etc...) > 2) Closed (boolean field which contains 1 or 0) > > If i enter a query like:  Select * from Issues where Status !=

[sqlite] Segfault on a specific input

Hi there, When I type the following statements of SQL into sqlite3, it crashed with segmentation fault. CREATE TABLE todo ( id integer primary key, title text, created timestamp default (now()), done boolean default 'f' ); INSERT INTO todo (title) VALUES ('Learn web.py'); I tried this

[sqlite] Select * from table where field = "value" does not work when "value" is also a field....

I have a database with the following fields: 1) Status (it will hold values like "Open", "Closed", "Submitted"... etc...) 2) Closed (boolean field which contains 1 or 0) If i enter a query like: Select * from Issues where Status != "Closed" i get all records (even the ones with Status=Closed).

[sqlite] when to create temporary disk file for group by and order by?

sqlite perform group by and order by using transient index, and if there isn't exist such an index, sqlite will create the index and store it in its a temporary file. So I think the following SQL statement won't create temporary file in disk. create table tbl(a,b,c);

Re: [sqlite] New open source data synchronization tool

It is part of Data Integration and Business Intelligence. ETL is database-related. Jay A. Kreibich-2 wrote: > > On Wed, Oct 07, 2009 at 10:49:55AM -0500, Beau Wilkinson scratched on the > wall: >> >Hello, >> > >> >We are trying to find an ETL tool open source. Basically, we need our >>