[sqlite] Facing "SQLite ERROR : unable to open database file" error

[Ashok Pitambar]

Hi All,

I have used sqlite for Database in my client , to speed up the
performance
I used *"begin"(BEGIN;) *and *"end"(END;)* query transactions to
include multiple
transactions in single transaction. This helped in performance by reducing
the time
taken to write all the transactions but when I used this for some of the DB
operations
sqlite throws error "unable to open database file".If I remove the changes
it works
fine.What could be the problem here?

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

[sqlite] links in markdown syntax - local repo vs remote repo (edited)

[Chris Peachment]

Hello:

I'm resending a slightly modified version of this email because my
sample remote url listed below did not include the repository name:

  http://www/host.com/site/library.cgi/doc/tip/overview.mkd

should have been:

  http://www/host.com/site/library.cgi/repo-name/doc/tip/overview.mkd

--- begin original message with edit ---

I have started using the markdown feature for embedded documentation
files. I'm using Fossil from trunk, version [9d657c3be5] 2013-01-15,
compiled myself on the two machines of interest to me. My local
machine is running Debian 7 on x86 32 bit and the remote server is
running a modified version of CentOs 5 on x86 64 bit.

The compile instructions on both machines are:

  ./configure --markdown
  make

The Admin-Settings-markdown flag is set ON for both the local and
remote repositories.

The local repository is accessed using the command line instruction:

  fossil ui

The remote repository is accessed via the browser with a url of the
form:

  http://www.host.com/site/library.cgi/repo-name

where library.cgi uses the documented script method of invoking
Fossil:

  #!/path-to/fossil
  directory: /path-to-repo/fossils
  notfound: http://url-to-go-to-if-repo-not-found/ 

The 'site' sub-directory in the remote url should not impact on the
problem here.

The repository home page consists of a few lines of text and a
number of links to other embedded documentation pages.

I have two versions of the home page:

1. index.wiki with links of the form [/doc/tip/overview.mkd | Overview]

2. index.mkd with links of the form [Overview](/doc/tip/overview.mkd)

By changing the Admin-Configuration-Index Page string, I can choose
the active version of the home page.

The wiki version of the home page operates correctly with the links
transformed to include the correct url on both local and remote
machines.

The markdown version operates correctly on the local machine where
the links use the pattern:

  http://localhost:8080/doc/tip/overview.mkd

However, markdown version on the remote machine has an incomplete
link:

  http://www/host.com/doc/tip/overview.mkd

when it should be:

  http://www/host.com/site/library.cgi/repo-name/doc/tip/overview.mkd

As a result, the desired page is not found when the link is clicked.
If I enter the correct url into the browser address bar then the
page is displayed as expected.

Since the embedded documentation pages are written on my local
machine and then committed to the repository, with subsequent sync
to the remote machine, the identical markdown link urls should
operate correctly in both situations. It does not make sense to
include an absolute path url for the remote repository access.

Is there any configuration change I can make to overcome this
problem, or am I detecting a weakness in the markdown interface
for Fossil?

Thanks,
Chris Peachment


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

Re: [sqlite] sqlite3_initialize() before sqlite3_randomness()

[Eric Sink]

Yeah, I did read that page, but not well enough.

I saw this:

"Workstation applications using SQLite normally do not need to invoke 
either of these routines."


And this (all-caps emphasis mine):

"The sqlite3_initialize() routine is called internally by MANY other 
SQLite interfaces so that an application usually does not need to invoke 
sqlite3_initialize() directly."


But I missed this:

"it is recommended that applications always invoke sqlite3_initialize() 
directly prior to using any other SQLite interface"


So I ended up thinking maybe the current behavior was unknown or unintended.

Anyway.  Nothing to see here...

--
E




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

Re: [sqlite] sqlite3_initialize() before sqlite3_randomness()

[Kees Nuyt]

On Tue, 15 Jan 2013 16:07:16 -0600, Eric Sink 
wrote:

>
>So it appears that if the very first SQLite function you call is 
>sqlite3_randomness(), it crashes.
>
>And if you call sqlite3_initialize() first, it does not crash.
>
>Just thought I should let somebody know...

It's documented:

http://sqlite.org/c3ref/initialize.html

" The sqlite3_initialize() routine is called internally by many other
SQLite interfaces so that an application usually does not need to invoke
sqlite3_initialize() directly. For example, sqlite3_open() calls
sqlite3_initialize() so the SQLite library will be automatically
initialized when sqlite3_open() is called if it has not be initialized
already. However, if SQLite is compiled with the SQLITE_OMIT_AUTOINIT
compile-time option, then the automatic calls to sqlite3_initialize()
are omitted and the application must call sqlite3_initialize() directly
prior to using any other SQLite interface. For maximum portability, it
is recommended that applications always invoke sqlite3_initialize()
directly prior to using any other SQLite interface. Future releases of
SQLite may require this. In other words, the behavior exhibited when
SQLite is compiled with SQLITE_OMIT_AUTOINIT might become the default
behavior in some future release of SQLite. "

Usually the first thing an application does is call sqlite3_open*() and
by default SQLITE_OMIT_AUTOINIT is not defined (yet).

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] sqlite3_initialize() before sqlite3_randomness()

[Eric Sink]

So it appears that if the very first SQLite function you call is 
sqlite3_randomness(), it crashes.


And if you call sqlite3_initialize() first, it does not crash.

Just thought I should let somebody know...

--
E

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

Re: [sqlite] links in markdown syntax - local repo vs remote repo

[Stephan Beal]

Hi! Wrong mailing list - the maintainer of the fossil markdown features can
be found on the fossil-users mailing list.

(sent from a mobile device - please excuse brevity and typos)
- stephan beal
http://wanderinghorse.net
On Jan 15, 2013 9:16 PM, "Chris Peachment"  wrote:

> Hello:
>
> I have started using the markdown feature for embedded documentation
> files. I'm using Fossil from trunk, version [9d657c3be5] 2013-01-15,
> compiled myself on the two machines of interest to me. My local
> machine is running Debian 7 on x86 32 bit and the remote server is
> running a modified version of CentOs 5 on x86 64 bit.
>
> The compile instructions on both machines are:
>
>   ./configure --markdown
>   make
>
> The Admin-Settings-markdown flag is set ON for both the local and
> remote repositories.
>
> The local repository is accessed using the command line instruction:
>
>   fossil ui
>
> The remote repository is accessed via the browser with a url of the
> form:
>
>   http://www.host.com/site/library.cgi/repo-name
>
> where library.cgi uses the documented script method of invoking
> Fossil:
>
>   #!/path-to/fossil
>   directory: /path-to-repo/fossils
>   notfound: http://url-to-go-to-if-repo-not-found/
>
> The 'site' sub-directory in the remote url should not impact on the
> problem here.
>
> The repository home page consists of a few lines of text and a
> number of links to other embedded documentation pages.
>
> I have two versions of the home page:
>
> 1. index.wiki with links of the form [/doc/tip/overview.mkd | Overview]
>
> 2. index.mkd with links of the form [Overview](/doc/tip/overview.mkd)
>
> By changing the Admin-Configuration-Index Page string, I can choose
> the active version of the home page.
>
> The wiki version of the home page operates correctly with the links
> transformed to include the correct url on both local and remote
> machines.
>
> The markdown version operates correctly on the local machine where
> the links use the pattern:
>
>   http://localhost:8080/doc/tip/overview.mkd
>
> However, markdown version on the remote machine has an incomplete
> link:
>
>   http://www/host.com/doc/tip/overview.mkd
>
> when it should be:
>
>   http://www/host.com/site/library.cgi/doc/tip/overview.mkd
>
> As a result, the desired page is not found when the link is clicked.
> If I enter the correct url into the browser address bar then the
> page is displayed as expected.
>
> Since the embedded documentation pages are written on my local
> machine and then committed to the repository, with subsequent sync
> to the remote machine, the identical markdown link urls should
> operate correctly in both situations. It does not make sense to
> include an absolute path url for the remote repository access.
>
> Is there any configuration change I can make to overcome this
> problem, or am I detecting a weakness in the markdown interface
> for Fossil?
>
> Thanks,
> Chris Peachment
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] links in markdown syntax - local repo vs remote repo

[Chris Peachment]

Hello:

I have started using the markdown feature for embedded documentation
files. I'm using Fossil from trunk, version [9d657c3be5] 2013-01-15,
compiled myself on the two machines of interest to me. My local
machine is running Debian 7 on x86 32 bit and the remote server is
running a modified version of CentOs 5 on x86 64 bit.

The compile instructions on both machines are:

  ./configure --markdown
  make

The Admin-Settings-markdown flag is set ON for both the local and
remote repositories.

The local repository is accessed using the command line instruction:

  fossil ui

The remote repository is accessed via the browser with a url of the
form:

  http://www.host.com/site/library.cgi/repo-name

where library.cgi uses the documented script method of invoking
Fossil:

  #!/path-to/fossil
  directory: /path-to-repo/fossils
  notfound: http://url-to-go-to-if-repo-not-found/ 

The 'site' sub-directory in the remote url should not impact on the
problem here.

The repository home page consists of a few lines of text and a
number of links to other embedded documentation pages.

I have two versions of the home page:

1. index.wiki with links of the form [/doc/tip/overview.mkd | Overview]

2. index.mkd with links of the form [Overview](/doc/tip/overview.mkd)

By changing the Admin-Configuration-Index Page string, I can choose
the active version of the home page.

The wiki version of the home page operates correctly with the links
transformed to include the correct url on both local and remote
machines.

The markdown version operates correctly on the local machine where
the links use the pattern:

  http://localhost:8080/doc/tip/overview.mkd

However, markdown version on the remote machine has an incomplete
link:

  http://www/host.com/doc/tip/overview.mkd

when it should be:

  http://www/host.com/site/library.cgi/doc/tip/overview.mkd

As a result, the desired page is not found when the link is clicked.
If I enter the correct url into the browser address bar then the
page is displayed as expected.

Since the embedded documentation pages are written on my local
machine and then committed to the repository, with subsequent sync
to the remote machine, the identical markdown link urls should
operate correctly in both situations. It does not make sense to
include an absolute path url for the remote repository access.

Is there any configuration change I can make to overcome this
problem, or am I detecting a weakness in the markdown interface
for Fossil?

Thanks,
Chris Peachment

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

Re: [sqlite] Need help with query

[Michael Black]

You're structure is bad for future growth (i.e. multiple languages) as the
query gets really nasty really quickly.

You should normalize this data and your query will never change.


pragma foreign_keys=ON;
CREATE TABLE buttons (
ID integer primary key autoincrement,
Key1 varchar not null,
Key2 varchar not null
);
insert into buttons(key1,key2) values('FORM1','SAVE_BUTTON');
insert into buttons(key1,key2) values('FORM1','HELP_BUTTON');

CREATE TABLE masterlanguages(
ID integer primary key autoincrement,
ISOCode varchar not null
);
insert into masterlanguages (ISOCode) values ('ENG');
insert into masterlanguages(ISOCode) values ('DEU');

CREATE TABLE buttontext (
ID integer primary key autoincrement,
Description varchar not null,
masterlanguage integer,
button integer,
foreign key(masterlanguage) references masterlanguages(id),
foreign key(button) references buttons(id)
);

insert into buttontext (Description,masterlanguage,button)
values('Save',1,1);
insert into buttontext (Description,masterlanguage,button)
values('Help',1,2);
insert into buttontext (Description,masterlanguage,button)
values('Speichern',2,1);
insert into buttontext (Description,masterlanguage,button)
values('Hilfe',2,2);

All you need to do0 is specify what language you want in the query.

sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='ENG');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|1|Save|1|1
2|FORM1|HELP_BUTTON|2|Help|1|2
sqlite> select * from buttons b1 join buttontext b2 on b1.id=b2.button and
b2.masterlanguage=(select id from masterlanguages where ISOCode='DEU');
ID|Key1|Key2|ID|Description|masterlanguage|button
1|FORM1|SAVE_BUTTON|3|Speichern|2|1
2|FORM1|HELP_BUTTON|4|Hilfe|2|2

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kai Peters
Sent: Monday, January 14, 2013 11:50 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Need help with query

Hi all,

given

CREATE TABLE masterlanguages (
  ID  integer primary key autoincrement,
  Key1varchar not null, 
  Key2varchar not null,
  ISOCode varchar not null,
  Description varchar not null,
  MaxCharsinteger default 0
);


insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
'Hilfe', 0);



In addition to the data from 

SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU' 

I also need the Description field for the corresponding record (based on
Key1 + Key2) in English so 
that I can display the original English description as well as its German
translation.


How can I achieve this?

TIA,
Kai
___
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] order by "a huge number" does not work, but "random" well why ?

[Simon Slavin]

On 15 Jan 2013, at 1:58pm, Ryan Johnson  wrote:

> On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:
>> i would like to "seed random" a request, so i could do use some pagination
>> system with a order by random(), is this possible sqlite ? when a tried to
>> use "order by 1234567892" it always return the same order

sqlite> CREATE TABLE myTable (ax TEXT, myOrder INTEGER);
sqlite> INSERT INTO myTable (ax) VALUES ('fred');
sqlite> INSERT INTO myTable (ax) VALUES ('wilma');
sqlite> INSERT INTO myTable (ax) VALUES ('barney');
sqlite> INSERT INTO myTable (ax) VALUES ('betty');
sqlite> INSERT INTO myTable (ax) VALUES ('pebbles');
sqlite> INSERT INTO myTable (ax) VALUES ('bamm bamm');
sqlite> SELECT * FROM myTable;
fred|
wilma|
barney|
betty|
pebbles|
bamm bamm|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
wilma|
bamm bamm|
fred|
barney|
pebbles|
sqlite> SELECT * FROM myTable ORDER BY random();
betty|
pebbles|
fred|
wilma|
barney|
bamm bamm|

> That's because you told sqlite that the distinguishing feature of a tuple is 
> the constant '1234567892' . The sorting routine will see "t1 < t2 = false" 
> and "t1 > t2 = false" (implying t1 = t2) no matter which two tuples it 
> examines. If sqlite uses a stable sorting algorithm, that will leave the 
> input untouched. A very expensive no-op, in other words.
> 
> With random(), t1 < t2 is undefined (because it turns out different every 
> time it's evaluated), and you end up with tuples in a random order (assuming 
> the sorting routine doesn't crash; code usually assumes values don't change 
> while being sorted). Note that shuffling directly is more efficient than 
> "sorting" in this way, though sqlite lacks the necessary "shuffle" operator 
> to do it in-database.

If you're going to search and seek you actually have to have the random values 
stored, because you need to get consistent results when you look for/at a 
value.  One way to get rows in a random order is to put an extra column in your 
table, with an INTEGER affinity and random values.  When you want to shuffle 
the order just update the values in the table.  Then you can do an "ORDER BY 
myOrder" and everything comes out consistently:

sqlite> UPDATE myTable SET myOrder=random();
sqlite> SELECT * FROM myTable ORDER BY myOrder;
barney|-8843778138112958223
bamm bamm|-7611867930025301398
pebbles|-5851529134925141201
wilma|-2528762072213537603
betty|853939383612226875
fred|8581781187410411890

There's no problem with ordering by these 'huge numbers'.

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

Re: [sqlite] Intermittent "malformed database schema"

[Simon Slavin]

On 15 Jan 2013, at 1:56pm, Marcus Ilgner  wrote:

> [answers]

Okay, you reached the limit of what I know, but I hope the information you 
supplied lets someone else figure out the cause of what you see.

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[François-xavier Jacobs]

Indeed shuffle was my goal

i solve it by doing order by "((rowid * "+seed.longValue()+" ) %
"+BIG_PRIME_NUMBER+")";

as long ass the seed doe not change result is the same, so i can shuffle
with LIMT

Regards


2013/1/15 Ryan Johnson 

> On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:
>
>> Hi everyone
>>
>> i would like to "seed random" a request, so i could do use some pagination
>> system with a order by random(), is this possible sqlite ? when a tried to
>> use "order by 1234567892" it always return the same order
>>
> That's because you told sqlite that the distinguishing feature of a tuple
> is the constant '1234567892' . The sorting routine will see "t1 < t2 =
> false" and "t1 > t2 = false" (implying t1 = t2) no matter which two tuples
> it examines. If sqlite uses a stable sorting algorithm, that will leave the
> input untouched. A very expensive no-op, in other words.
>
> With random(), t1 < t2 is undefined (because it turns out different every
> time it's evaluated), and you end up with tuples in a random order
> (assuming the sorting routine doesn't crash; code usually assumes values
> don't change while being sorted). Note that shuffling directly is more
> efficient than "sorting" in this way, though sqlite lacks the necessary
> "shuffle" operator to do it in-database.
>
> Ryan
>
> __**_
> 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] order by "a huge number" does not work, but "random" well why ?

[Clemens Ladisch]

François-xavier Jacobs wrote:
> when a tried to use "order by 1234567892" it always return the same order

Because this is the *same* value for all records.

> i would like to "seed random" a request, so i could do use some pagination
> system with a order by random(), is this possible sqlite ?

Calls to random() are never repeatable.

I'd suggest to
1) read all IDs of the table into memory;
2) shuffle these IDs randomly; and
3) explicitly load the records for one 'page' of ID values.

Alterntively to step 3, you could save the indexes of the shuffled
array entries back into a new field in the table so that you have
a column that you can do an ORDER BY on.


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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Igor Tandetnik]

On 1/14/2013 9:48 AM, François-xavier Jacobs wrote:

i would like to "seed random" a request, so i could do use some pagination
system with a order by random(), is this possible sqlite ? when a tried to
use "order by 1234567892" it always return the same order


random() returns a different value on every call. So the rows for which 
it just happens to return a smaller value get sorted before rows for 
which it happens to return a larger value.


When you do "order by 1234567892", each row gets associated with the 
same value, so the ORDER BY clause doesn't induce any particular order. 
It doesn't matter whether the value is large or small. I'm not sure what 
good you expected such a clause to achieve.


I'm also really curious as to what line of reasoning has led you to 
conclude that using a large constant value would do something different 
than using a small constant value. What kind of magic do you believe 
"huge numbers" possess that small numbers don't?

--
Igor Tandetnik

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Marc L. Allen]

If you literally used "ORDER BY 1234567892" then there's nothing in the record 
being sorted.

I can't recall is SQLite allows order by aliases, but something like..

Select ..., random() as X
Order by X

Might work, as long as random() is executed for each row.

(Sorry.. don't have a quick SQLite engine available to test)

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of François-xavier Jacobs
Sent: Monday, January 14, 2013 9:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] order by "a huge number" does not work, but "random" well why 
?

Hi everyone

i would like to "seed random" a request, so i could do use some pagination 
system with a order by random(), is this possible sqlite ? when a tried to use 
"order by 1234567892" it always return the same order 
___

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] order by "a huge number" does not work, but "random" well why ?

[Ryan Johnson]

On 14/01/2013 9:48 AM, François-xavier Jacobs wrote:

Hi everyone

i would like to "seed random" a request, so i could do use some pagination
system with a order by random(), is this possible sqlite ? when a tried to
use "order by 1234567892" it always return the same order
That's because you told sqlite that the distinguishing feature of a 
tuple is the constant '1234567892' . The sorting routine will see "t1 < 
t2 = false" and "t1 > t2 = false" (implying t1 = t2) no matter which two 
tuples it examines. If sqlite uses a stable sorting algorithm, that will 
leave the input untouched. A very expensive no-op, in other words.


With random(), t1 < t2 is undefined (because it turns out different 
every time it's evaluated), and you end up with tuples in a random order 
(assuming the sorting routine doesn't crash; code usually assumes values 
don't change while being sorted). Note that shuffling directly is more 
efficient than "sorting" in this way, though sqlite lacks the necessary 
"shuffle" operator to do it in-database.


Ryan

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

Re: [sqlite] Intermittent "malformed database schema"

[Marcus Ilgner]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi Simon,

thank you for the quick reply!

On Dienstag, 15. Januar 2013 12:14:20, Simon Slavin wrote:
>
> On 15 Jan 2013, at 9:50am, Marcus Ilgner  wrote:
>
>> every now and then - very hard to reproduce - I get the SQLite error
>> "malformed database schema" (error code 1). This happens when running
>> lots of schema-changing statements after another and seems to occur
>> only on systems with a very fast hard-disk (SSD).
>> Fortunately, the database seems to be ok, i.e. everything works fine
>> after closing and re-opening the file and re-issuing the statement.
>> This is with in-memory temp database, WAL and PRAGMA synchronous=NORMAL.
>> The statements (schema migrations) are grouped into blocks where each
>> starts with a BEGIN EXCLUSIVE TRANSACTION and ends with committing the
>> transaction.
>
> Which version of SQLite are you using ?  You can tell us the result of
>
> SELECT sqlite_version()
>
> if that's convenient.
>
Sorry for that. The version string is "3.7.15.1".

> Are you using any PRAGMAs besides "PRAGMA synchronous=NORMAL" ?

The complete list of PRAGMAs/setup statements:
PRAGMA temp_store = 2
PRAGMA journal_mode = WAL
PRAGMA synchronous = 1
PRAGMA foreign_keys = ON

> Is the disk database you're using on a local drive or accessed across a 
> network ?

The file resides on a local drive. Also it isn't accessed by any other
processes.

> You say that this happens only when using an SSD drive, but you also say that 
> this is with an in-memory temporary database.  This sounds a little strange.  
> What are you doing to tell SQLite you want an in-memory temporary database ?

Oops. I meant memory temp store, not database.

> Can you try the same thing with just "BEGIN" instead of "BEGIN EXCLUSIVE" ?

It first appeared with deferred transactions and I then changed it to
exclusive mode, just to make sure that the lock was acquired as soon as
possible.

Best regards
Marcus
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJQ9WAEAAoJELuOTdgGQnyZTBgIAJ6XLIAcL9O0fVwx1P/JoOCy
AzPhL/C4TKAInzFgdxTXjpghclfVKlGq1eqUYecLCOTSNElrlg7/qOEVL5T3orta
2j5EARRimtenRdPG5c3rCBzUf1jgCZ/MLNmExkcGrsJNt4L6Q4Z2MABrEQbzBHJ4
JzHbQBKnpGWRjN3plVejk0KogW+SU2Csv21fZieCz8wvMDCwA2XRlFxGu6036OZe
g6fK8heEXnPnN49QqzWT2i4TKDsXFwvrWcdbVXhDhRcXFG9mipofGGGzof2ob7Lf
itcXgM0c9k938AzxUaQkmlF6JgdfsS/IzOLeTLbZ4gk7xa38cH2aa+gONvnDUXg=
=GKwV
-END PGP SIGNATURE-

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

[sqlite] order by "a huge number" does not work, but "random" well why ?

[François-xavier Jacobs]

Hi everyone

i would like to "seed random" a request, so i could do use some pagination
system with a order by random(), is this possible sqlite ? when a tried to
use "order by 1234567892" it always return the same order
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intermittent "malformed database schema"

[Simon Slavin]

On 15 Jan 2013, at 9:50am, Marcus Ilgner  wrote:

> every now and then - very hard to reproduce - I get the SQLite error
> "malformed database schema" (error code 1). This happens when running
> lots of schema-changing statements after another and seems to occur
> only on systems with a very fast hard-disk (SSD).
> Fortunately, the database seems to be ok, i.e. everything works fine
> after closing and re-opening the file and re-issuing the statement.
> This is with in-memory temp database, WAL and PRAGMA synchronous=NORMAL.
> The statements (schema migrations) are grouped into blocks where each
> starts with a BEGIN EXCLUSIVE TRANSACTION and ends with committing the
> transaction.

Which version of SQLite are you using ?  You can tell us the result of

SELECT sqlite_version()

if that's convenient.

Are you using any PRAGMAs besides "PRAGMA synchronous=NORMAL" ?

Is the disk database you're using on a local drive or accessed across a network 
?

You say that this happens only when using an SSD drive, but you also say that 
this is with an in-memory temporary database.  This sounds a little strange.  
What are you doing to tell SQLite you want an in-memory temporary database ?

Can you try the same thing with just "BEGIN" instead of "BEGIN EXCLUSIVE" ?

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

[sqlite] Intermittent "malformed database schema"

[Marcus Ilgner]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,

every now and then - very hard to reproduce - I get the SQLite error
"malformed database schema" (error code 1). This happens when running
lots of schema-changing statements after another and seems to occur
only on systems with a very fast hard-disk (SSD).
Fortunately, the database seems to be ok, i.e. everything works fine
after closing and re-opening the file and re-issuing the statement.
This is with in-memory temp database, WAL and PRAGMA synchronous=NORMAL.
The statements (schema migrations) are grouped into blocks where each
starts with a BEGIN EXCLUSIVE TRANSACTION and ends with committing the
transaction.

Maybe someone else had this problem or has an idea how it may be caused?

All the best
Marcus
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJQ9SZaAAoJELuOTdgGQnyZIh4H/RJtoJl9p1GEz3BP31qvF0eF
w8jBxNoIhjAcGGA5reVF0BtLyn2mklQqCIkZJG2F5/yKTxepRo7jcsJiPi2imfw+
r+AWO9rOREhxiXq35F3FxS4kFavXX1BCzelY4WnP1knZbvZiCKSBRSqJmn4cM09W
8LLb6PY62WWyd9smTGmNtkV8JlmPHvrNkzTIs3KuICDIB/yM/epM9f1Huu/nNrhS
+Go17P1oSqPaD9MlzErdvY6eOT9T8l+cRHYvOfJYhcJ71C3O6qpMsoAJVZ/eVsa0
Qwbph6z+j4Bw5GwnHYmpIsCb6T3ouYncURIjuY4WOn4hE8bjmK47ya4hr8iYO6s=
=I7Bc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users