[sqlite] SIGBUS error with WAL mode on disk full

[Yongil Jang]

Thanks a lot!

It is very helpful for my job.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import a text to sqlite

[Kevin Benson]

On Wed, Mar 14, 2012 at 10:04 PM, YAN HONG YE  wrote:

> On Tue, 13 Mar 2012 02:28:38 +, YAN HONG YE 
> wrote:
>
> >here my code,where is wrong?
> >char mma[250];
> >strcpy(mma,"sqlite3 foods.db \" .separator ',' \" \".import
> dzhhkmysql.txt dzh\"");
> >system(mma);
>
> dot-commands for the sqlite3 command line tool have to be on separate
> lines.
>
> Do the equivalent of:
> printf ".separator ','\n.import dzhhkmysql.txt dzh\n" | sqlite3 foods.db
>
> C:\sqlite\lib>".separator ',' \n .import ddl.txt dzh \n" | sqlite3 foods.db
> The system cannot find the path specified.
> but the two file ddl.txt and foods.db certainty under C:\sqlite\lib\
> ___
>
Quick solution to above error may be to try path with forward slashes:

C:\sqlite\lib>".separator ',' \n .import /sqlite/lib/ddl.txt dzh \n" |
sqlite3 /sqlite/lib/foods.db

and 8.3 path format where needed:

C:\sqlite\lib>".separator ',' \n .import /progra~1/sqlite/lib/ddl.txt dzh
\n" | /progra~1/sqlite/lib/foods.db

--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import a text to sqlite

[Larry Brasfield]

On March 14, YAN HONG YE wrote:

On Tue, 13 Mar 2012 02:28:38 +, YAN HONG YE 
wrote:

>here my code,where is wrong?
>char mma[250];
>strcpy(mma,"sqlite3 foods.db \" .separator ',' \" \".import dzhhkmysql.txt 
dzh\"");
>system(mma);

dot-commands for the sqlite3 command line tool have to be on separate
lines.

Do the equivalent of:
printf ".separator ','\n.import dzhhkmysql.txt dzh\n" | sqlite3 foods.db

C:\sqlite\lib>".separator ',' \n .import ddl.txt dzh \n" | sqlite3 foods.db
The system cannot find the path specified.
but the two file ddl.txt and foods.db certainty under C:\sqlite\lib\


The above text is not usefully interpreted by the brain-dead command 
line shell that comes with operating systems using the weird drive 
letter convention and needlessly different backslash.  I advise getting 
a better shell at http://jpsoft.com/index.php, where a free, light 
version can be obtained.  (See http://jpsoft.com/downloads/v13/tccle.exe 
.)  Once you have that, take a look at the 'text' and 'endtext' 
commands, which allow convenient generation of text containing embedded 
line-ends from a batch file.  Alternatively, if you love cmd.exe for 
some strange reason, use a series of 'echo' commands to get lines into a 
temp file, then redirect that into the sqlite3.exe shell.  (This is 
crude, but can work.)  Another alternative is to get bash.exe from the 
MSYS package within the MinGW project.  That shell is one written by 
people who understand the programmer's task, unlike cmd.exe which is one 
of the sorriest excuses for a shell ever allowed to live so long.


Good luck,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] import a text to sqlite

[YAN HONG YE]

On Tue, 13 Mar 2012 02:28:38 +, YAN HONG YE 
wrote:

>here my code,where is wrong?
>char mma[250];
>strcpy(mma,"sqlite3 foods.db \" .separator ',' \" \".import dzhhkmysql.txt 
>dzh\"");
>system(mma);

dot-commands for the sqlite3 command line tool have to be on separate
lines.

Do the equivalent of:
printf ".separator ','\n.import dzhhkmysql.txt dzh\n" | sqlite3 foods.db

C:\sqlite\lib>".separator ',' \n .import ddl.txt dzh \n" | sqlite3 foods.db
The system cannot find the path specified.
but the two file ddl.txt and foods.db certainty under C:\sqlite\lib\
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Having problem with DBLock

[poorwolf]

Hello. 

I am an elementary programmer in Korea. 

My company uses SQLite for some kinds of project. We use it without any 
problems. but recently we got a problem, 
so I searched solutions from internet. but I couldn't find it. so I need helps 
and contact you. 

In my situation, our project is many people can SELECT some data from database 
file by using multi-threads. As I 
know, SQLite can perform the request after former request finished. The request 
for 'SELECT data' locks 
database file and after doing all request, it unlocks the file. 
We don't have any problem on Window server, but we get a problem on Unix 
server. When we use our project on 
Unix server, if more than 3 requests per second occur, database file don't 
unlock and we get 
"SQLite_BUSY". then the server is down.

so in this situation, can I unlock the 'LOCK' forcibly by using command? If I 
can do that, how can I do it? 

or we only use select query from SQLite, so can we make database file never 
unlock? 

and If Using SQLite has differences between on Window server and Unix server, 
Please let me know it. 


Thank you for read my letter. 

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

[sqlite] sqlite as cache database

[Sebastian Bermudez]

i'm working on a small ecommerce site for latin america..

http://comercialibre.com

this website is using sqlite3 for store website pages as cache
there are some burst of 60 concurrent users and sqlite is working great for 
this app layout..


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

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[Igor Tandetnik]

On 3/14/2012 10:13 AM, FengTao DING wrote:

UPDATE game LEFT JOIN updatemag.game AS idcgame ON idcgame.id=game.id SET
game.name=idcgame.name,game.launcher=idcgame.laucher,game.icon=game.directory+idcgame.launcher,
game.info_version=idcgame.information_version,game.modify_version=game.modify_version+1
WHERE game.game_version=idcgame.version AND
game.info_version<>idcgame.information_version


insert or replace into game(
  id, name, launcher, icon, info_version, modify_version, AllOtherFields)
select g.id, idc.name, idc.launcher, g.directory + idc.launcher,
  idc.information_version, g.modify_version + 1, g.AllOtherFields
from game g join updatemag.game idc on (
  g.id = idc.id and g.game_version = idc.version and
  g.info_version != idc.information_version);

If you also explain what your INSERT statement does, it might be 
possible to roll both INSERT and UPDATE into a single INSERT OR REPLACE.

--
Igor Tandetnik

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

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[Kit]

2012/3/14, FengTao DING :
> Because in my UPDATE case, the new values that will be updated come from
> another different table, and the UPDATE's WHERE condition also involves
> columns from more than one tables(actually 3),

UPDATE tab1 SET val1=(SELECT val2 FROM tab2 WHERE ...)
   WHERE val3 in (SELECT val4 FROM tab4 JOIN tab5 ON ... WHERE ...);
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite compile options for multithreaded application

[rahul dev]

--- On Wed, 14/3/12, Dan Kennedy  wrote:

> From: Dan Kennedy 
> Subject: Re: [sqlite] Sqlite compile options for multithreaded application
> To: sqlite-users@sqlite.org
> Date: Wednesday, 14 March, 2012, 5:20 PM
> On 03/14/2012 06:09 PM, rahul dev
> wrote:
> > Guys,
> >
> >     I am using sqlite version
> 3.7.10. My application is multi-threaded and each thread
> opens a separate connection to the the same database file.
> > I want my database operations to be thread-safe and as
> concurrent as possible. Can you please tell me what sqlite
> compile flags should I use ?
> >
> > I am passing THREADSAFE=2. Is that right ?
> > Or should I use THREADSAFE=1 ? But, in that case even
> the concurrent reads will be serialized. So, THREADSAFE=2
> seems to be a better option ?
> 
> With THREADSAFE=1, calls made on a single database handle
> are serialized. But calls made on separate handles may
> still
> run concurrently. THREADSAFE=2 just allows you to avoid the
> overhead imposed by the mutex used to serialize the calls
> made on a single database handle.
> 
> You can also change the threading model using
> sqlite3_config()
> at runtime.
> 
> >
> > Secondly, I am running the application on a
> propreitary
> filesystem/operating-system that does *NOT* support unix
> like file
> locking semantics. However, flock() is supported on my
> system. What
> option should I pass so that "dotlockLockingStyle" or
> "unix-flock" may
> be used ?
> >
> 
> Pass something like "unix-flock" as the fourth argument to
> sqlite3_open_v2() when opening the database connection.
> 
> > If I pass 'SQLITE_ENABLE_LOCKING_STYLE', my compilation
> fails with
> > error error: storage size of 'fsInfo' isn't known cc1:
> warnings being
> > treated as errors /src/sqlite3.c:30079: error:
> implicit declaration of function 'fstatfs'
> >
> > Is this a known problem ? What should I do to take care
> of the above
> compilation problem ?
> 
Thanks a lot, Dan for your help.
I just found that "fcntl" locks are also supported on my system. So, I do not 
need to pass 'SQLITE_ENABLE_LOCING_STYLE'.
I believe that if I do not pass 'SQLITE_ENABLE_LOCING_STYLE', then the default 
locking style (fcntl) will be used. Is that correct ?

I am using THREADSAFE=2.

I want to know if there are any other sqlite compile flags that I should use 
which may be useful. (for better speed efficiency) ? In the older sqlite 
version, I used to pass 'SQLITE_OMIT_FAULTINJECTOR' flag. This flag is not 
available in the latest version of sqlite. Is there any flag equivalent to 
'SQLITE_OMIT_FAULTINJECTOR' in newest version of sqlite ?
Any other flag that you guys feel should be passed during compilation ?

On a debug build I am passing 'SQLITE_LOCK_TRACE' to ensure that locks are 
working fine. But, I get following error on compilation, 
/src/sqlite3.c: In function 'lockTrace':
/src/sqlite3.c:25521: error: implicit declaration of function 
'sqlite3DebugPrintf'
/src/sqlite3.c: In function 'openDirectory':
/src/sqlite3.c:28250: error: passing argument 3 of 'lockTrace' makes pointer 
from integer without a cast

Any idea what should I do to resolve above compilation error.

thanks a lot,
rahul
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[Marc L. Allen]

I think INSERT OR REPLACE...

INSERT OR REPLACE INTO Game
SELECT ug.xxx, g.xxx
FROM Game g
JOIN UpdateMag.Game ug ON ...
WHERE ...

Since you need to provide all fields to the INSERT, you need all the g.xxx 
fields not being updated, including (and most importantly) the g.id field, 
since that's what will generate the REPLACE.

Note that this will cause each updated record to first  be deleted and then 
reinserted, so if there are any foreign keys looking into the game table, this 
will probably not work.

Another option is to do the select and for each record returned, generate an 
independent UPDATE statement for it.

Marc

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of FengTao DING
> Sent: Wednesday, March 14, 2012 10:13 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Is there any option that can enable me to do
> INSERT or UPDATE while SELECT
> 
> Thanks, Igor, Nico, and anyone pay attention to this post.
> Here are the schemas:
> 
> In Server.db3
> ---
> Table [game]
> Fields: 37
> [id]: TEXT
> [name]: TEXT
> [category2_id]: INTEGER
> [icon]: TEXT
> [hot_level]: TEXT
> [no_update]: INTEGER
> [size]: INTEGER
> [launcher]: TEXT
> [directory]: TEXT
> [client_path]: TEXT
> [update_time]: TEXT
> [game_version]: INTEGER
> [info_version]: INTEGER
> [modify_version]: INTEGER
> [save_config]: INTEGER
> [save_type]: INTEGER
> [save_path]: TEXT
> [env_parameter]: TEXT
> [env_copy]: TEXT
> [env_resolution]: TEXT
> [env_color]: TEXT
> [env_registry]: TEXT
> [env_lib]: TEXT
> [env_virtual_cdrom]: TEXT
> [client_not_run]: TEXT
> [client_local_run]: TEXT
> [client_remote_run]: TEXT
> [cleanup]: INTEGER
> [cleanup_exclude]: TEXT
> [auto_index]: INTEGER
> [md5_verify]: INTEGER
> [file_count]: INTEGER
> [client_cleanup]: INTEGER
> [save_path_version]: INTEGER
> [env_version]: INTEGER
> [modify_game_version]: INTEGER
> [resource_version]: INTEGER
> Indexes: 1
> [game_id_unique] UNIQUE
> [id]
> Triggers: 0
> Unique constraints: 0
> Check constraints: 0
> 
> In Updatemag.db3
> --
> Table [game]
> Fields: 47
> [id]: TEXT
> [name]: TEXT
> [version]: INTEGER
> [sort]: INTEGER
> [hot]: INTEGER
> [game_id]: TEXT
> [directory]: TEXT
> [download_path]: TEXT
> [launcher]: TEXT
> [origin_launcher]: TEXT
> [site]: TEXT
> [exclude]: TEXT
> [formal_only]: INTEGER
> [delete_flag]: INTEGER
> [category2_id]: TEXT
> [recommend_site]: TEXT
> [description]: TEXT
> [save_path]: TEXT
> [operate_id]: INTEGER
> [charge_id]: INTEGER
> [hot_level]: TEXT
> [other_id1]: TEXT
> [other_id2]: TEXT
> [other_id3]: TEXT
> [other_id4]: TEXT
> [other_id5]: TEXT
> [other_id6]: TEXT
> [random_file1]: TEXT
> [random_file2]: TEXT
> [random_file3]: TEXT
> [env_parameter]: TEXT
> [env_copy]: TEXT
> [env_resolution]: TEXT
> [env_color]: TEXT
> [env_registry]: TEXT
> [env_lib]: TEXT
> [update_time]: TEXT
> [pack_result]: TEXT
> [run_time]: TEXT
> [file_number]: INTEGER
> [size]: INTEGER
> [seed_id]: TEXT
> [information_version]: INTEGER DEFAULT '1'
> [save_type]: INTEGER DEFAULT '1'
> [save_path_version]: INTEGER DEFAULT '1'
> [env_version]: INTEGER DEFAULT '1'
> [resource_version]: INTEGER DEFAULT '1'
> Indexes: 1
> [game_id_unique] UNIQUE
> [id]
> Triggers: 0
> Unique constraints: 0
> Check constraints: 0
> 
> Server.db3 is main db, Updatemag.db3 is attached with alias updatemag.
> Both dbs are in the same folder, of local disk.
> Here is one of the UPDATE statements I want, the statement is not
> written in correct syntax, but just describe my intent:
> 
> UPDATE game LEFT JOIN updatemag.game AS idcgame ON idcgame.id=game.id
> SET
> game.name=idcgame.name,game.launcher=idcgame.laucher,game.icon=game.dir
> ectory+idcgame.launcher,
> game.info_version=idcgame.information_version,game.modify_version=game.
> modify_version+1
> WHERE game.game_version=idcgame.version AND
> game.info_version<>idcgame.information_version
> 
> Then how to do that in a right way?
> 
> -
> 
> On Wed, Mar 14, 2012 at 9:08 PM, Igor Tandetnik 
> wrote:
> 
> > FengTao DING  wrote:
> > > I prefer to one connection rather than WAL if it 

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[FengTao DING]

Thanks, Igor, Nico, and anyone pay attention to this post.
Here are the schemas:

In Server.db3
---
Table [game]
Fields: 37
[id]: TEXT
[name]: TEXT
[category2_id]: INTEGER
[icon]: TEXT
[hot_level]: TEXT
[no_update]: INTEGER
[size]: INTEGER
[launcher]: TEXT
[directory]: TEXT
[client_path]: TEXT
[update_time]: TEXT
[game_version]: INTEGER
[info_version]: INTEGER
[modify_version]: INTEGER
[save_config]: INTEGER
[save_type]: INTEGER
[save_path]: TEXT
[env_parameter]: TEXT
[env_copy]: TEXT
[env_resolution]: TEXT
[env_color]: TEXT
[env_registry]: TEXT
[env_lib]: TEXT
[env_virtual_cdrom]: TEXT
[client_not_run]: TEXT
[client_local_run]: TEXT
[client_remote_run]: TEXT
[cleanup]: INTEGER
[cleanup_exclude]: TEXT
[auto_index]: INTEGER
[md5_verify]: INTEGER
[file_count]: INTEGER
[client_cleanup]: INTEGER
[save_path_version]: INTEGER
[env_version]: INTEGER
[modify_game_version]: INTEGER
[resource_version]: INTEGER
Indexes: 1
[game_id_unique] UNIQUE
[id]
Triggers: 0
Unique constraints: 0
Check constraints: 0

In Updatemag.db3
--
Table [game]
Fields: 47
[id]: TEXT
[name]: TEXT
[version]: INTEGER
[sort]: INTEGER
[hot]: INTEGER
[game_id]: TEXT
[directory]: TEXT
[download_path]: TEXT
[launcher]: TEXT
[origin_launcher]: TEXT
[site]: TEXT
[exclude]: TEXT
[formal_only]: INTEGER
[delete_flag]: INTEGER
[category2_id]: TEXT
[recommend_site]: TEXT
[description]: TEXT
[save_path]: TEXT
[operate_id]: INTEGER
[charge_id]: INTEGER
[hot_level]: TEXT
[other_id1]: TEXT
[other_id2]: TEXT
[other_id3]: TEXT
[other_id4]: TEXT
[other_id5]: TEXT
[other_id6]: TEXT
[random_file1]: TEXT
[random_file2]: TEXT
[random_file3]: TEXT
[env_parameter]: TEXT
[env_copy]: TEXT
[env_resolution]: TEXT
[env_color]: TEXT
[env_registry]: TEXT
[env_lib]: TEXT
[update_time]: TEXT
[pack_result]: TEXT
[run_time]: TEXT
[file_number]: INTEGER
[size]: INTEGER
[seed_id]: TEXT
[information_version]: INTEGER DEFAULT '1'
[save_type]: INTEGER DEFAULT '1'
[save_path_version]: INTEGER DEFAULT '1'
[env_version]: INTEGER DEFAULT '1'
[resource_version]: INTEGER DEFAULT '1'
Indexes: 1
[game_id_unique] UNIQUE
[id]
Triggers: 0
Unique constraints: 0
Check constraints: 0

Server.db3 is main db, Updatemag.db3 is attached with alias updatemag. Both
dbs are in the same folder, of local disk.
Here is one of the UPDATE statements I want, the statement is not written
in correct syntax, but just describe my intent:

UPDATE game LEFT JOIN updatemag.game AS idcgame ON idcgame.id=game.id SET
game.name=idcgame.name,game.launcher=idcgame.laucher,game.icon=game.directory+idcgame.launcher,
game.info_version=idcgame.information_version,game.modify_version=game.modify_version+1
WHERE game.game_version=idcgame.version AND
game.info_version<>idcgame.information_version

Then how to do that in a right way?

-

On Wed, Mar 14, 2012 at 9:08 PM, Igor Tandetnik  wrote:

> FengTao DING  wrote:
> > I prefer to one connection rather than WAL if it can accomplish the job.
> >
> > But I found that, INSERT can be done in one connection, but UPDATE can
> not.
> >
> > Because in my UPDATE case, the new values that will be updated come from
> > another different table, and the UPDATE's WHERE condition also involves
> > columns from more than one tables(actually 3),
>
> How does this prevent you from running an UPDATE statement on the first
> connection, while still enabling you to do this on the second? I don't
> understand the nature of the problem.
>
> > UPDATE statement doesn't support JOIN, so I didn't find a way to do this
> in
> > one connection(one query).
>
> Where there's a will, there's a way. Show your database schema, describe
> exactly what kind of change you want to make.
>
> > Currently, the only way I can work out is that,
> > save all SELECT result in a heavy c++ array ( include values that WHERE
> > needs ), then use the same connection to do the UPDATE, but this should
> not
> > be a smart way. Could anyone point out me another method?
>
> Not without some more details.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards, 

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[Igor Tandetnik]

FengTao DING  wrote:
> I prefer to one connection rather than WAL if it can accomplish the job.
> 
> But I found that, INSERT can be done in one connection, but UPDATE can not.
> 
> Because in my UPDATE case, the new values that will be updated come from
> another different table, and the UPDATE's WHERE condition also involves
> columns from more than one tables(actually 3),

How does this prevent you from running an UPDATE statement on the first 
connection, while still enabling you to do this on the second? I don't 
understand the nature of the problem.

> UPDATE statement doesn't support JOIN, so I didn't find a way to do this in
> one connection(one query).

Where there's a will, there's a way. Show your database schema, describe 
exactly what kind of change you want to make.

> Currently, the only way I can work out is that,
> save all SELECT result in a heavy c++ array ( include values that WHERE
> needs ), then use the same connection to do the UPDATE, but this should not
> be a smart way. Could anyone point out me another method?

Not without some more details.
-- 
Igor Tandetnik

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

Re: [sqlite] No error message generated by sqlite_exec()

[Rob Richardson]

Thank you, Dr. Hipp.  I understand.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, March 13, 2012 7:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] No error message generated by sqlite_exec()

SQLite3_exec() should always generate an error message, but with two
exceptions:

(1) When malloc() fails trying to allocate space to hold the error message
(2) In an SQLITE_MISUSE situation because the malloc() subsystem might not even 
be initialized.

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

Re: [sqlite] Sqlite compile options for multithreaded application

[Dan Kennedy]

On 03/14/2012 06:09 PM, rahul dev wrote:

Guys,

I am using sqlite version 3.7.10. My application is multi-threaded and each 
thread opens a separate connection to the the same database file.
I want my database operations to be thread-safe and as concurrent as possible. 
Can you please tell me what sqlite compile flags should I use ?

I am passing THREADSAFE=2. Is that right ?
Or should I use THREADSAFE=1 ? But, in that case even the concurrent reads will 
be serialized. So, THREADSAFE=2 seems to be a better option ?


With THREADSAFE=1, calls made on a single database handle
are serialized. But calls made on separate handles may still
run concurrently. THREADSAFE=2 just allows you to avoid the
overhead imposed by the mutex used to serialize the calls
made on a single database handle.

You can also change the threading model using sqlite3_config()
at runtime.



Secondly, I am running the application on a propreitary

filesystem/operating-system that does *NOT* support unix like file
locking semantics. However, flock() is supported on my system. What
option should I pass so that "dotlockLockingStyle" or "unix-flock" may
be used ?




Pass something like "unix-flock" as the fourth argument to
sqlite3_open_v2() when opening the database connection.


If I pass 'SQLITE_ENABLE_LOCKING_STYLE', my compilation fails with
error error: storage size of 'fsInfo' isn't known cc1: warnings being
treated as errors /src/sqlite3.c:30079: error:

implicit declaration of function 'fstatfs'


Is this a known problem ? What should I do to take care of the above

compilation problem ?

SQLITE_ENABLE_LOCKING_STYLE only works on osx.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite compile options for multithreaded application

[rahul dev]

Guys,

   I am using sqlite version 3.7.10. My application is multi-threaded and each 
thread opens a separate connection to the the same database file. 
I want my database operations to be thread-safe and as concurrent as possible. 
Can you please tell me what sqlite compile flags should I use ?

I am passing THREADSAFE=2. Is that right ? 
Or should I use THREADSAFE=1 ? But, in that case even the concurrent reads will 
be serialized. So, THREADSAFE=2 seems to be a better option ?

Secondly, I am running the application on a propreitary 
filesystem/operating-system that does *NOT* support unix like file locking 
semantics. However, flock() is supported on my system. What option should I 
pass so that "dotlockLockingStyle" or "unix-flock" may be used ?

If I pass 'SQLITE_ENABLE_LOCKING_STYLE', my compilation fails with error 
error: storage size of 'fsInfo' isn't known
cc1: warnings being treated as errors /src/sqlite3.c:30079: error: implicit 
declaration of function 'fstatfs'

Is this a known problem ? What should I do to take care of the above 
compilation problem ?

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

Re: [sqlite] Is there any option that can enable me to do INSERT or UPDATE while SELECT

[FengTao DING]

I prefer to one connection rather than WAL if it can accomplish the job.

But I found that, INSERT can be done in one connection, but UPDATE can not.

Because in my UPDATE case, the new values that will be updated come from
another different table, and the UPDATE's WHERE condition also involves
columns from more than one tables(actually 3),

UPDATE statement doesn't support JOIN, so I didn't find a way to do this in
one connection(one query). Currently, the only way I can work out is that,
save all SELECT result in a heavy c++ array ( include values that WHERE
needs ), then use the same connection to do the UPDATE, but this should not
be a smart way. Could anyone point out me another method?

PS: INSERT also faces the across-table problem but INSERT...SELECT... can
deal with that.

Thx

On Wed, Mar 14, 2012 at 11:07 AM, Igor Tandetnik wrote:

> On 3/13/2012 10:38 PM, FengTao DING wrote:
>
>> Hi all,
>>
>> I wrote a piece of code, that open two sqlite3 connections, use
>> connection1
>> to do a SELECT query, while stepping in this query,
>>
>> when some condition matches, it uses connection2 to do a INSERT query;
>> when another condition matches, it uses connection2 to do a UPDATE query.
>>
>> As common result, SQLITE_BUSY or SQLITE_LOCKED(when using
>> SQLITE_OPEN_SHAREDCACHE) will return when do INSERT or UPDATE.
>>
>
> Just run your INSERT and UPDATE statements on connection1, don't bother
> with the second connection. I assume you are not planning to modify the
> same data you are iterating over.
>
> Another possibility is opening your database in WAL mode:
> http://sqlite.org/wal.html . In this mode, readers don't block writers.
> The writer will be able to modify the same data the reader is reading (but
> the reader won't see those changes).
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 12.03.2012 12:40, schrieb Kit:

2012/3/12, Christoph P.U. Kukulies:

INSERT OR IGNORE INTO resource ...
INSERT OR REPLACE INTO instance ...

Thanks a lot. What is the idea behind the INSERT OR REPLACE in your
solution?
Christoph

If you edit a test for some version of your software, md5sum is
changed too. You must replace row in `instance`.

Thanks. I have the structure now in a shape I think it would be useful:

CREATE TABLE instance (
   mandant TEXT ,
   md5sum TEXT,
   basename TEXT,
   prob_ref TEXT,
   suite TEXT ,
   tag TEXT ,
   size INT,
   creation_date TEXT,
  last_write_time TEXT,
 PRIMARY KEY (mandant,basename,prob_ref,suite,tag)
 );

CREATE TABLE resource ( md5sum TEXT PRIMARY KEY, data BLOB);

The FS tree contained about a million files and a corresponding 
tar-archive comprises about 70 GB.

The database with gzipped BLOBS now is 1.7GB in size.

Each files' location is made of \mandant\prob_ref\tag\suite\basename.

My only question now is:

when I collect the files and INSERT or IGNORE the resource, I have to 
read in the file and build the BLOB,
independently on whether the INSERT really has to take place. A quick 
beforehand decision whether
an INSERT has to be made, could possibly speed up  the file collection 
process.


I was using a try clause on the INSERT and if that wouldn't fail I was 
doing an UPDATE on just the BLOB.


Other ideas?

--
Christoph



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

Re: [sqlite] C# delete primary key used in another table

[Simon Slavin]

On 14 Mar 2012, at 7:11am, Pawl  wrote:

> I have this tables:
> 
> #1:
> 
> CREATE TABLE [mat] (
>  [ID_MAT] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
>  [kod] TEXT, 
>  [popis] TEXT UNIQUE, 
>  [dilu_aktual] INTEGER, 
>  [dilu_aktualmax] INTEGER);
> 
> #2:
> CREATE TABLE [mat_spoj] (
>  [ID_VYROBEK] INTEGER NOT NULL REFERENCES [vyroba]([ID_VYROBA]), 
>  [ID_MAT] INTEGER NOT NULL REFERENCES [mat]([ID_MAT]), 
>  [dilu_max] INTEGER, 
>  [sarze] TEXT, 
>  [dilu_stitek] INTEGER, 
>  [POZICE] TEXT, 
>  [ghp_stav] INTEGER);
> 
> I use SQL Little Express Personal - when I try to delete used row in table
> "mat" i get error message 
> Abort due to constraint violation. - Helped by command DELETE from mat WHERE
> ID_MAT=3

This is correct.  Every mat_spo belongs to a mat.  You are trying to delete a 
mat which still has at least one mat_spo.

> When I try to make in my application 
> 
> cmd = new SQLiteCommand("DELETE from mat WHERE ID_MAT=3",parent.conn);
> 
> cmd.ExecuteNonQuery(); 
> =>SQL Delete this row! without any problem. 

Your own application is faulty.  It is using a database with FOREIGN KEYs 
without enabling foreign key support.  Please see



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

[sqlite] C# delete primary key used in another table

[Pawl]

I have this tables:

#1:

CREATE TABLE [mat] (
  [ID_MAT] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [kod] TEXT, 
  [popis] TEXT UNIQUE, 
  [dilu_aktual] INTEGER, 
  [dilu_aktualmax] INTEGER);

#2:
CREATE TABLE [mat_spoj] (
  [ID_VYROBEK] INTEGER NOT NULL REFERENCES [vyroba]([ID_VYROBA]), 
  [ID_MAT] INTEGER NOT NULL REFERENCES [mat]([ID_MAT]), 
  [dilu_max] INTEGER, 
  [sarze] TEXT, 
  [dilu_stitek] INTEGER, 
  [POZICE] TEXT, 
  [ghp_stav] INTEGER);

I use SQL Little Express Personal - when I try to delete used row in table
"mat" i get error message 
Abort due to constraint violation. - Helped by command DELETE from mat WHERE
ID_MAT=3

When I try to make in my application 

cmd = new SQLiteCommand("DELETE from mat WHERE ID_MAT=3",parent.conn);  
  
cmd.ExecuteNonQuery(); 
=>SQL Delete this row! without any problem. 

Whay? How to solve it?

Pavel
-- 
View this message in context: 
http://old.nabble.com/C--delete-primary-key-used-in-another-table-tp33499843p33499843.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] xcopy deployment using System.Data.SQLite for Windows x86, x64, and Mono/Unix?

[Joe Mistachkin]

Andrew Rondeau wrote:
> 
> Hmmm, part of the point of Mono is that a pure CLR .dll or .exe can work
on
> any platform without recompilation.
> 

The standard build of System.Data.SQLite looks for a native assembly (DLL)
named
"SQLite.Interop.dll" and this has always been the case.  It is possible to
build
a System.Data.SQLite managed-only assembly that looks for a native assembly
(DLL)
named "sqlite3" by compiling the System.Data.SQLite sources, setting the
UseInteropDll build property to false and the UseSqliteStandard build
property
to true, just as described on the build procedures wiki page.  Mono on Unix
is
documented to automatically transform the reference from "sqlite3" to
something
like "libsqlite3".

Unfortunately, there is no pre-compiled binary package currently available
for
this build configuration.  This is primarily because:

1. There are already a huge number of build configurations available and
adding
another one would likely just increase confusion with little benefit to the
community.

2. It is relatively easy to build System.Data.SQLite for Mono on any Windows
machine, with minimal effort, using only freely available tools (MSBuild and
the
.NET Framework).  Visual Studio is not required to build the
System.Data.SQLite
assembly for use with Mono.

> 
> How can I have a System.Data.SQLite that will work on Windows x86, x64,
and
> the various Unixes without recompiling System.Data.SQLite? PHX's older
> version of System.Data.SQLite does this, except for Windows x64.
> 

Fairly easily, as long as you have a Windows XP or later machine with the
.NET
Framework 3.5.  Also, x64 should not have anything to do with it since you
are
asking about the managed-only assembly which runs unmodified on all
processor
architectures.

The exact steps to follow for obtaining and building the System.Data.SQLite
source code are listed on the following two wiki pages:

Obtaining the source code:

http://system.data.sqlite.org/index.html/doc/trunk/www/source.wiki

Building the source code (see the "Mono" section):

http://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki

Please let me know if you have any specific questions not addressed on one
of
these pages.

--
Joe Mistachkin

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