Re: [sqlite] Understanding SELECT statement

[Andy Ling]

> > > Then I get help and this code:
> > > INSERT INTO SchoolYearTeachingDays
> > >   SELECT aDate FROM TeachingSaturdaysInSchoolYear T WHERE T.aDate
> NOT
> > > IN (SELECT S.aDate FROM SchoolYearTeachingDays S)
> >
> > This says "insert all dates that aren't already present", which of course
> avoids UNIQUE constraint.
> 
> Just do not understand what are the 'T' and 'S' means out there, after
> FROM clause.
> Are they CTEs? Are they virtual tables?
> 

They are aliases for the table names. Another way of writing this is

SELECT aDate FROM TeachingSaturdaysInSchoolYear AS T WHERE T.aDate

The AS is optional, but makes it slightly clearer that you are saying use
the table TeachingSaturdaysInSchoolYear and call it T. In this case
it is a way of saving typing.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] This is driving me nuts

[Andy Ling]

> While that makes sense Clemens it again fails to explain why it does no
> caching when desc is stepped through first. I am at least satisfied that it’s 
> not
> something I should dwell on or, more the point, it isn’t down to a bug in my
> code which is why I got embroiled in it in the first place.
> 

My understanding from what Clemens said, which might clarify.

When ascending pages are read sequentially, so sequential mode is used and
the pages get saved in the cache, but when descending, pages are read in
reverse order which makes the cache think it's random so it doesn't keep them.

So if you do ascending first the cache gets filled and eats up memory.
Then when you do descending it gradually deletes all the pages from the cache
and frees up the memory. If you do it the other way round, descending
uses one page worth of memory then ascending eats up enough for all the pages.

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] [External] Re: Always call a value-quoting routine

[Andy Ling]

> On Sun, May 6, 2018 at 11:34 PM, Rowan Worth <row...@dug.com> wrote:
> > Its omission is interesting though. Does it indicate an incompetent
> > attacker, or is companieshouse.gov.uk using some bespoke approach like
> > "delete all single quotes" instead of actually quoting strings?
>
> It could just indicate someone with a sense of humor who crafted a
> name that looks like an injection attack for their company.
>

More about the reason for the name

https://pizzey.me/blog/no-i-didnt-try-to-break-companies-house/

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Andy Ling]

Whoops, your right. I should have tested. I am more used to MySQL and assumed 
field was more "standard".

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Fri 09 March 2018 13:40
To: SQLite mailing list
Subject: [External] Re: [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Don’t think there is a FIELD function in sqlite Andy (MySQL has one).



with cte(ID) as (values (3),(1),(2))

select * from cte inner join Array using (ID);



will return them in the required order. That is to say, if you have a table 
where the records are ordered 3, 1, 2 then you can get the required result. As 
to whether that’s any use to you depends on how you’re creating the (3, 1, 2) 
list.




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Andy Ling <andy.l...@s-a-m.com>
Sent: Friday, March 9, 2018 1:30:25 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] How to get the entry from sqlite db without primary key 
or rowid order?

First, you cannot rely on the order of the rows unless you specify it. So it is 
"just luck" that they are in ID order.

To get want you want you must specify an order and something like this will do 
what you want..

SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2);

So you need to put the same IDs in the FIELD as you have in the IN.

HTH

Andy Ling

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: sqlite-users@mailinglists.sqlite.org
Subject: [External] [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

[Andy Ling]

First, you cannot rely on the order of the rows unless you specify it. So it is 
"just luck" that they are in ID order.

To get want you want you must specify an order and something like this will do 
what you want..

SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2) ORDER BY FIELD (ID, 3, 1, 2);

So you need to put the same IDs in the FIELD as you have in the IN.

HTH

Andy Ling

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hegde, Deepakakumar (D.)
Sent: Fri 09 March 2018 13:15
To: sqlite-users@mailinglists.sqlite.org
Subject: [External] [sqlite] How to get the entry from sqlite db without 
primary key or rowid order?

This Message originated outside your organization.

Hi All,


We have a problem as below:


we have created a table as below:

CREATE TABLE NEWFOLDER(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL) ;

We have inserted 5 entry to this table, and ID will be from 1 to 5 as below

ID   NAME
1 ABC

2 AAA

3 CBA

4 BAC

5 BBB


We execute following select statetment:


SELECT * FROM NEWFOLDER WHERE ID IN (3,1,2);


output for above is:


ID   NAME

1 ABC

2 AAA

3 CBA


It seems by default sqlite is getting the entry in the order of primary key or 
rowid.


So for us expected output is:

ID   NAME

3 CBA

1 ABC

2 AAA


Is there anyway to do this without adding a new column? with the same table?

we need a way where by we can get the entry as we given in "where" "in" clause


Thanks and Regards

Deepak


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

[sqlite] Building 3.22.0 for vxWorks fails

[Andy Ling]

I decided it was about time we updated our version 3.8 sqlite to something a 
bit newer.
So I have just tried building the 3.22.0 amalgamation for VxWorks and it fails 
to compile with the following error

sqlite3.c:31215: error: 'geteuid' undeclared here (not in a function)

It looks like the geteuid function is only called in robustFchown and only if 
HAVE_FCHOWN is defined.
So changing the few lines around 31215 so they look like the following seems to 
fix things.

#if defined(HAVE_FCHOWN)
  { "fchown",   (sqlite3_syscall_ptr)fchown,  0 },
  { "geteuid",  (sqlite3_syscall_ptr)geteuid, 0 },
#else
  { "fchown",   (sqlite3_syscall_ptr)0,   0 },
  { "geteuid",  (sqlite3_syscall_ptr)0,   0 },
#endif
#define osFchown((int(*)(int,uid_t,gid_t))aSyscall[20].pCurrent)
#define osGeteuid   ((uid_t(*)(void))aSyscall[21].pCurrent)

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

[Andy Ling]

Isn't this really a repeat of this thread...

http://sqlite.1065341.n5.nabble.com/how-into-insert-row-into-middle-of-table-with-integer-primary-key-td98629.html

The result of which was, don't try and use the table row order to sort your 
data. Add a column that defines your sort order
and do the sorting on output, not input.

I rather liked Jens solution to use a string to define the sort order. (top of 
second page of thread)

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Eric Grange
Sent: Tue 09 January 2018 10:26
To: SQLite mailing list
Subject: [External] Re: [sqlite] Efficient ways to maintaining order rank / 
row_number() in a rather large set ?

You mean using limit / offset instead ?

Even with an index on the VALUE column, queries like

select * from ranked
order by value
limit 10 offset xxx

become very slow when xxx is great, while

select * from ranked
order by rank
where rank between xxx and xxx+9

are fast regardless of the value of xxx

Similarly finding the rank of a key becomes sluggish for keys that are not
in the top without

So the order by is used to control the insertion order, so that the RANK
autoinc primary key ends up with natural rank order



On Tue, Jan 9, 2018 at 10:59 AM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 9 Jan 2018, at 9:50am, Eric Grange <zar...@gmail.com> wrote:
>
> > then I fill that table with something like
> >
> >   INSERT INTO RANKED
> >  SELECT key, value
> >  FROM ...something rather complex and big...
> >  ORDER BY value desc
> >
> > This works well enough, but as the amount of values to be ranked
> increases,
> > this feels wasteful to delete everything and then re-insert
> > everything just to adjust the RANK column, also I am running into memory
> > issues as the ORDER BY requires a temporary b-tree
> > which runs into the gigabyte range in some instances.
>
> The ORDER BY clause serves no useful value here.  Leave it out.  Do your
> sorting when you query the table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Andy Ling]

Try changing the "DELETE FROM table WHERE " to "SELECT COUNT(1) FROM table 
WHERE " and see if you get a number bigger than 0. If not, then your WHERE 
isn't matching the rows you think it should.

Regards

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fiona
Sent: Fri 13 October 2017 02:19
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with 
large db file(>280GB)

CAUTION - EXTERNAL EMAIL

>> This suggests INSERT works but UPDATE and DELETE does not.  Is this
>> correct ?

Thanks a lot! Yes, that's the situation. I'm using WHERE clause in my
UPDATE/DELETE sentences, and I'm pretty sure the syntax and my shell are not
the problem, because there has no retrun of error, and I also test the same
UPDATE/DELETE sentences with the same shell tool but in a small db file, it
works correctly.

I assume the problem is caused by some kind of *db file consistency check* I
don't know? Or sqlite is doing some *index rebuilding* for my UPDATE/DELETE
operation?  What I really can't understand is why INSERT still works in the
mean time.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] XOR operator

[Andy Ling]

On 6 Oct 2017, at 9:12am, Rowan Worth <row...@dug.com> wrote:

> On 6 October 2017 at 15:42, <no...@null.net> wrote:
>
>> On Fri Oct 06, 2017 at 09:28:08AM +0200, Clemens Ladisch wrote:
>>>
>>> For boolean values, "a XOR b" = "a <> b".
>>
>> Is the <> operator documented somewhere? I can't find it in either of
>> these places:
>
> <> is SQL for "not equal to" (shout out to all the BASIC fans). It’s 
> documented here:
> https://sqlite.org/lang_expr.html#binaryops

>That page says that '<>' means 'non-equals'.  This is not the same as the 
>binary operation 'XOR' since 'non-equals' can yield only two values: true >and 
>false.

Clemens said "For boolean values" So if a and b are limited to the values 0 & 1 
not equals is the same as XOR.

sqlite> SELECT 0 <> 0;
0
sqlite> SELECT 0 <> 1;
1
sqlite> SELECT 1 <> 0;
1
sqlite> SELECT 1 <> 1;
0

Andy Ling

 
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Sharing data between desktop and Android

[Andy Ling]

> Cecil Westerhof wrote:
> > I am thinking about writing some Android applications. I would like to
> > share data between the phone (or tablet) and de desktop. What is the best
> > way to do this? In a way that would also be convenient for other people.
>
> I use an Android app that does this. It has a companion PC app that lets
> you backup and modify the underlying sqlite database. It transfers the
> database between Android and PC using a web link. There is a "sync
> to PC" menu on the app that opens the connection.
>

​> Can you share information about it?

It's not my app, I just use it. It's called MobileSheetsPro. There's more 
information
here 

http://www.zubersoft.com/mobilesheets/

Mike, the guy that wrote it, is extremely helpful. So if you contact him I'm 
sure
he'll help you out.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Sharing data between desktop and Android

[Andy Ling]

Cecil Westerhof wrote:
> I am thinking about writing some Android applications. I would like to
> share data between the phone (or tablet) and de desktop. What is the best
> way to do this? In a way that would also be convenient for other people.

I use an Android app that does this. It has a companion PC app that lets
you backup and modify the underlying sqlite database. It transfers the
database between Android and PC using a web link. There is a "sync
to PC" menu on the app that opens the connection.

HTH

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Issue with updating database content (C++)

[Andy Ling]

You're not deleting any rows, you're just changing the value of the data in the 
row.

What you probably want is something like

DELETE FROM BC WHERE PK = '%q';

HTH

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ali Dorri
Sent: Wed 30 August 2017 12:19
To: SQLite mailing list
Subject: Re: [sqlite] Issue with updating database content (C++)

Hi,

Thanks, now it works and removes all except for the PK. How can I remove
the PK then? i.e. what is the correct way of doing the following?
   UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;

Another issue I have is that when I remove these entries, the size of the
database does not decrease. I do the VACUUM after the program, but it does
not work and the size of the database does not decrease while the data are
removed.
Can anyone help me in this regard?

Thanks

On Wed, Aug 30, 2017 at 8:49 PM, Clemens Ladisch <clem...@ladisch.de> wrote:

> Ali Dorri wrote:
> > I encode the PKs to base64
> > [...]
> > The PK is a BLOB type, i.e.,
> >sql = "CREATE TABLE BC("  \
> > "PKBLOB," \
>
> Why do you store a text value in a blob field?
>
> > "UPDATE BC set Signature = null  and PK = null   where PK = '%q' ;
>
> That does not update the PK column.
>
>UPDATE BC set Signature = null ,PK = null   where PK = '%q' ;
>   
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Database version

[Andy Ling]

>Let's say I made some database files 2 years ago.
>Now I want the current SQLite code to open them and performs some queries
>from the C interface.

I would ask why do you care? Sqlite will read old databases without any problem.

What you may be interested in is what schema version and that's up to you to 
code.
There is a PRAGMA user_version you might want to use or you can create your
own table with stuff like schema_version in it.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Andy Ling]

Whilst this might make writing your application easier, when you think about 
what has to happen
"under the hood" it can't really be any quicker. The database still has to read 
all the rows
that satisfy your WHERE clause and store them somewhere while it sorts them 
based on
your ORDER BY clause, then count through to row "rowNumber" to give you the row 
you asked for.

Not much different to the suggestion already made to create a temporary table.

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 14:21
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows

Hi Ron,

 >  there is no system in existence that will do
I was working a lot with Valentina-DB and they have a cursor class:

var cursor=database.SqlSelect( "SELECT... WHERE... ORDER...");

then you can just get any the ListView wants, forward and backwards, very fast:

cursor.Position = rownumber;

I'm quiet new to SQLite and was surprised, that its so difficult to write this 
kind of cursor.

 > Another way is to Query to a temporary table with an automatic incremented
 > This is extremely fast, only the initial query will take some time.
yeah, this might work, but imagine how much time and memory this would cost 
for 10mio records...

Tom


Am 24.05.2017 um 13:20 schrieb R Smith:
> You are asking the DB to give you all the 8000...+ results, sort them and 
> then 
> you opt to only look at some of them, there is no way this can ever be fast 
> in 
> any system, you need to rethink how you ask for information.
> 
> First things first, you should never be using the sqlite (or any other 
> database's) STEP to support user scrolling, you should be using it to load 
> the 
> results you want to see, and then in a different method show those results to 
> the user. What if the user wants to move up by one line? You can't un-step in 
> a database.
> 
> There are many ways this can be overcome, first with dynamic listviews:
> 
> The way to set up a dynamic listview is to get a query of the ID's of the 
> entire list of possible values, sorted and so on, that you might want to 
> display into your own list object or array. Then populate the listview with 
> the ID's only and determine which are visible, for the visible ones, load the 
> data from a query using only those ID's, perhaps something like:
> 
> SELECT v1, v2 FROM MyMainTable WHERE ID IN (7001, 7002, 7003...for all 
> IDs 
> visible...);
> 
> and set them tot he screen. If the user scrolls loads, you update only when 
> needed, perhaps using a time difference function or such, and when the view 
> "settles" load those results that are visible. Almost all programming systems 
> with visual components like "Listview" has a function or callback that can 
> tell you the current visible items AND whether the visible index/count 
> changed 
> or not. It is often enough to catch this and simply update the visible items 
> when such a change happens.
> 
> Another way is to Query to a temporary table with an automatic incremented 
> primary key, and simply read from that table the paginated values, i.e. if 
> your listview scrolls to line 50013 you can query the temp table like 
> this:
> 
> SELECT * FROM TempResults WHERE ID BETWEEN ?1 AND ?2;
> 
> where ?1 = current_idx (such as 50013) and ?2 = current_idx + 
> page_items_count as defined in your software;
> 
> This is extremely fast, only the initial query will take some time.
> 
> What you can't do is query an insane amount of rows EVERY time the user moves 
> the cursor or scrolls the page, there is no system in existence that will do 
> that quick, ever.
> 
> Good luck!
> Ryan
> 
> 
> On 2017/05/24 11:53 AM, Thomas Flemming wrote:
>> Yes, but this would still be slow, because lastValue is lets say page 50 in 
>> the telephone directory, but I need to go to page 800.
>> So this query would still return all pages from 50 to 800, which I dont need.
>>
>>
>>
>> Am 24.05.2017 um 10:45 schrieb Andy Ling:
>>> Then when you detect a jump you'll need to use a new search to "jump" to 
>>> the page you want. Something like
>>>
>>> SELECT   WHERE sortedColumn > lastValue ORDER BY sortedColumn
>>>
>>> And make sure you have indexes on all the columns that you can sort by.
>>>
>>> Andy Ling
>>>
>>>
>>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>>> Behalf Of Thomas Flemming
>>> Sent: Wed 24 May 2017 10:37
>>> To: sql

Re: [sqlite] SQLite3.Step fast forward / skipping rows

[Andy Ling]

Then when you detect a jump you'll need to use a new search to "jump" to the 
page you want. Something like

SELECT   WHERE sortedColumn > lastValue ORDER BY sortedColumn

And make sure you have indexes on all the columns that you can sort by.

Andy Ling


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Thomas Flemming
Sent: Wed 24 May 2017 10:37
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQLite3.Step fast forward / skipping rows

Almost, but when you compare to a telephone directory, then the use case of
fast scrolling down in a listbox would be going directly to page 800 and not
going to  "Smithson".

And yes, there is a unique key, but this doesn't help, because the list can 
also be sorted to various columns.


Am 24.05.2017 um 10:27 schrieb Keith Medcalf:
> You need to write your application like a telephone directory.  To get to
> the page with the "Smithson" entry on it, you do not read all the entries
> starting from the begining until you get there -- you turn directly to the
> page you want by doing a search.
> 
> Surely you have a unique key for the list?
> 
> -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>> -Original Message- From: sqlite-users
>> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas
>> Flemming Sent: Wednesday, 24 May, 2017 02:09 
>> To:sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite3.Step
>> fast forward / skipping rows
>> 
>> Hi SQLite Users,
>> 
>> 
>> I have a SELECT query, which returns some 10 records and is
>> displayed in a scrollable ListView.
>> 
>> When the user scrolls down the list, each new row is loaded with 
>> SQLite3.Step().
>> 
>> The problem is, when the user scrolls fast with the scroll-slider, lots
>> of rows are skipped, but SQLite still needs to load them all with 
>> SQLite3.Step until it reaches the row which is actually needed. This is
>> very slow.
>> 
>> Is there a way to skip all these unnecessary rows? For example going 
>> directly from row 1000 to row 10 ? I tried SELECT ... OFFSET 10
>> but this is also very slow the more down we go.
>> 
>> Thanks Tom
>> 
>> 

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

-- 
/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Andy Ling]

Now you know a bit more about what you are looking for, perhaps you
should revisit the links Warren Young gave a few days ago...

>> You’re looking for prepared statements with parameters:

>>https://sqlite.org/c3ref/stmt.html
>>https://sqlite.org/lang_expr.html#varparam


Regards

Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thu 26 January 2017 15:41
To: SQLite mailing list
Subject: Re: [sqlite] Using SQLite, how can I search for chars that include a 
', similar to OLE DB .Parameters?

Not finding much.

I will try searching for a single word:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || ?1 || '%') ";

When I tried entering:

  sqlite3_bind_

the compiler starts complaining.

On Thu, Jan 26, 2017 at 9:18 AM, Clyde Eisenbeis <cte...@gmail.com> wrote:
> I'm searching the internet for sqlite3_bind_* () examples.
>
> On Thu, Jan 26, 2017 at 9:14 AM, Andy Ling <andy.l...@s-a-m.com> wrote:
>> I think the point is, you need to use a prepared statement and bind the 
>> parameters to it.
>> The bind process handles the special characters.
>>
>> So you will need to create a command string with question mark operators in 
>> like
>>
>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>> LIKE ('%' || ?2 || '%')"
>>
>> Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
>> liststLikeFieldValue" strings.
>>
>> HTH
>>
>> Andy
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
>> Behalf Of Clyde Eisenbeis
>> Sent: Thu 26 January 2017 15:04
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Using SQLite, how can I search for chars that include 
>> a ', similar to OLE DB .Parameters?
>>
>> I've also tried:
>>
>>   string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE ('%' || " +
>> liststLikeFieldValue[0] + " || '%')";
>>
>> which does not work.
>>
>> On Thu, Jan 26, 2017 at 8:14 AM, heribert <herib...@scharnagl.com> wrote:
>>> There are some missing spaces i think:
>>>
>>> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>> liststLikeFieldValue[0] + "||'%'";
>>>
>>> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>>>
>>>> I tried replacing this:
>>>>
>>>>
>>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>>>> "%'";
>>>>
>>>> with this:
>>>>
>>>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>>>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>>>> liststLikeFieldValue[0] + "||'%'";
>>>>
>>>> Does not work.
>>>>
>>>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp <d...@sqlite.org> wrote:
>>>>>
>>>>> On 1/25/17, Warren Young <war...@etr-usa.com> wrote:
>>>>>>
>>>>>> stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;
>>>>>>
>>>>>> Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
>>>>>> the
>>>>>> string.
>>>>>
>>>>> Not quite.  You cannot have parameters embedded in the middle of
>>>>> strings.  The whole string is replaced by a parameter.
>>>>>
>>>>> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>>>>>
>>>>> Then the application has to prepend and append the "%" on the strings
>>>>> before binding.  Or, if your application does not want to do that:
>>>>>
>>>>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>>>>> LIKE ('%' || ?2 || '%')"
>>>>>
>>>>> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
>>&

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

[Andy Ling]

I think the point is, you need to use a prepared statement and bind the 
parameters to it.
The bind process handles the special characters.

So you will need to create a command string with question mark operators in like

stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
LIKE ('%' || ?2 || '%')"

Then use the sqlite3_bind_* () calls to replace the ?n markers with the " 
liststLikeFieldValue" strings.

HTH

Andy

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clyde Eisenbeis
Sent: Thu 26 January 2017 15:04
To: SQLite mailing list
Subject: Re: [sqlite] Using SQLite, how can I search for chars that include a 
', similar to OLE DB .Parameters?

I've also tried:

  string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
+ " WHERE " + stLikeFieldName + " LIKE ('%' || " +
liststLikeFieldValue[0] + " || '%')";

which does not work.

On Thu, Jan 26, 2017 at 8:14 AM, heribert  wrote:
> There are some missing spaces i think:
>
> string stCmdString = "SELECT " + stFieldNames + " FROM " + stTableName
> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
> liststLikeFieldValue[0] + "||'%'";
>
> Am 26.01.17 um 15:04 schrieb Clyde Eisenbeis:
>>
>> I tried replacing this:
>>
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] +
>> "%'";
>>
>> with this:
>>
>>string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName
>> + " WHERE " + stLikeFieldName + " LIKE '%'||" +
>> liststLikeFieldValue[0] + "||'%'";
>>
>> Does not work.
>>
>> On Wed, Jan 25, 2017 at 11:53 AM, Richard Hipp  wrote:
>>>
>>> On 1/25/17, Warren Young  wrote:

 stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”;

 Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into
 the
 string.
>>>
>>> Not quite.  You cannot have parameters embedded in the middle of
>>> strings.  The whole string is replaced by a parameter.
>>>
>>> stCmdString += " AND fstInfo LIKE ?1 AND fstInfo LIKE ?2"
>>>
>>> Then the application has to prepend and append the "%" on the strings
>>> before binding.  Or, if your application does not want to do that:
>>>
>>> stCmdString += " AND fstInfo LIKE ('%' || ?1 || '%') AND fstInfo
>>> LIKE ('%' || ?2 || '%')"
>>>
>>> Then you can bind the search patterns directly to ?1 and ?2.  (Aside:
>>> || is the string concatenation operator in SQL.)
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

Interesting. I am using an old version, 5.5.34

The database engine may also make a difference. I was using MyISAM. INNODB may 
be different.

A quick scan of the MySQL documentation suggests INNODB is different. For 
example, it doesn't
store the next value in the database.


If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle 
in the InnoDB data dictionary contains a special counter called the 
auto-increment counter that is used in assigning new values for the column. 
This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes 
the equivalent of the following statement on the first insert into a table 
containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

Simon's requested table is likely to get quite complicated :^)

I suspect all you can really say about auto increment is that it will create a 
unique number.


Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Tue 01 November 2016 13:09
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:
++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin
Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote:

> It remembers..

Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.

I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in

<https://www.sqlite.org/nulls.html>

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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

>> It remembers..

>Ah, neat.  Thanks for the testing.

As a slight aside. It also never resets the value. We had a problem where the 
number of inserts
had incremented the AUTO INCREMENT value to MAXINT (it took a few years). It 
then stops.
We fixed it by changing id to a BIGINT, but you can also reset the auto 
increment value via SQL.

Regards

Andy
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

It remembers..

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> update tt set id=10 where v="one";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("two");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=40 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=30 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("three");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++---+
| id | v |
++---+
| 10 | one   |
| 30 | two   |
| 41 | three |
++---+
3 rows in set (0.00 sec)

After doing this fiddling, show create table shows the auto increment value

mysql> show create table tt;
+---+-
| Table | Create Table
+---+-
| tt| CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 |
+---+-
1 row in set (0.00 sec)





-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tue 01 November 2016 11:42
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:14am, Andy Ling <andy.l...@s-a-m.com> wrote:

> MySQL lets you fiddle.

I don't have MySQL.  To satisfy my curiosity, could you try this:

create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;

You got this far already (though I changed the details a little).

Now add this:

update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;

I'm trying to find out whether it remembers that there once was a value that 
was 40, even if the highest value present when you do the insert is 30.  My 
guess is that it'll assign an id of 41 to row three, but I might be wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

> Which, according to GB, is what some other SQL engines do: attempts to change 
> a value
>  in that column using UPDATE always generate an error.  I didn't know that.  
> I looked it up.
>   Apparently Microsoft's SQLSERVER blocks it, but I was unable to find 
> anything mentioning
>  how any of the other big SQL engines handles it.

MySQL lets you fiddle.

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
|  1 | one  |
++--+
1 row in set (0.00 sec)

mysql> update tt set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
++--+
1 row in set (0.00 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | one  |
++--+
2 rows in set (0.00 sec)
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Pivot & concat SELECT?

[Andy Ling]

To add to the inefficiency :^)...

select unit, (sum(1 << bit_position) & 0xff) AS byte1, ((sum(1 << bit_position) 
>> 8) & 0xff) AS byte2 from table1 where val group by unit;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Daniel Polski
Sent: Mon 17 October 2016 15:47
To: SQLite mailing list
Subject: Re: [sqlite] Pivot & concat SELECT?


> select unit, sum(1 << bit_position) from table1 where val group by unit;
>

To make it more complex.. Is it possible to select into "different 
bytes" depending on bit_position? (For example that bit_position 0-7 
represent byte 1, bit_position 8-15 represent another)

To get a second "byte" I can tweak your initial idea to:

select unit, sum(1 << (bit_position-9)) from table1 where val and 
bit_position between 8 and 15 group by unit;

But that would need to get merged into the initial solution some way on 
the same select row result.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Pivot & concat SELECT?

[Andy Ling]

On 17 Oct 2016, at 3:03pm, Igor Tandetnik  wrote:

> select unit, sum(1 << bit_position) from table1 where val group by unit;

Beat me to it. I was going to say

select unit, sum(val << bit_position) as byte from table1 group by unit;

But yours is slightly more efficient not summing the zeros (if there were any)

Andy
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] 64-bit SQLite3.exe

[Andy Ling]

You still seem to have missed the point that several have made.

There are lots of people still using 32bit Windows. They cannot use a 64bit 
SQLite3.exe.
Therefore the 32bit one has to stay. The 32bit one will work on a 64bit OS, 
with restrictions.
The number of people who hit those restrictions is quite small. You are the 
only person
I can remember posting about hitting one. Most that do can build their own 
64bit exe.

So I guess currently the team have decided it isn't worth the effort to provide 
both exes
to support those few people. Maybe this will change if the number increase.

Regards

Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Rousselot, Richard A
Sent: Wed 10 August 2016 15:43
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe

First of all thanks for the discussion.  Special thanks out to DD. ;)

I will just summarize my main view.
* CTEs can easily exceed memory limits of the 32-bit SQLite3.exe.  So I have a 
valid need and am not on a crusade to kill 32-bits.  Telling me that other 
people don't have my problems is irrelevant, to me. :)

Response to all other banter; in good humor.
* The rest of the arguments about Microsoft currently shipping software that is 
32-bit, is a red herring.  I bet if you tried hard enough, you could find code 
from Windows 3.1 in Windows 10.  That wasn’t a properly thought out design 
decision.  That is laziness and frugality (possibly stupidity) on Microsoft's 
part.  I once read that MS would hire the developer of ReactOS so they could 
explain how their own operating system works.   (I digress)
* If I did figure out how to build the 64-bit executable, I guarantee, that it 
would be a catastrophe.  I can't even figure out how to post on Nabble for 
freaks sake. ;)
* I can still buy a VW bug designed in the 30s; does that mean that all new 
engines developed should have to fit in the back of that thing?
* I acquiesce on all other points, I am weary.

Finally, I just want to say I really appreciate the work that is put in to 
SQLite.  It is one of many tools I use on a day to day basis but it is the only 
one where I actively watch the discussions on a mailing list.  The regulars 
here are thoughtful, intelligent and infinitely patient.  I learn new things 
every day watching you guys and for that I thank you.

Richard

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Wednesday, August 10, 2016 7:03 AM
To: SQLite mailing list
Subject: Re: [sqlite] 64-bit SQLite3.exe


> Even on a 64-bit processor, there’s usually no reason to run 64-bit
> Windows unless you have more than 4 GB of RAM, a threshold we didn’t
> pass very long ago.

Yes, please remember to keep the "addressable memory limits" linkage to 
"processor bitted-ness" is a Microsoft Only phenomenon.  You do NOT need 64-bit 
processors or 64-bit Operating systems to be able to address more than 4 GB of 
physical RAM.  In fact that there are 32-bit versions of Windows (NT 3.5, 4.0, 
2000, XP, 2003 etc) which have been compiled without this artificially imposed 
limitation.  You pay more for "properly written and compiled" software however 
because, well, it is easy to do stupid things and impose stupid limits for no 
reason and you need higher wattage (therefore more expensive people) if you 
want software that is not bounded by a crapload of inane if not brain-dead) 
design decisions.  It is also quite profitable to claim that your prior 
incompetencies were to blame on "something else" and if everyone would just 
spend a boatload of money and replace all the defective crap we sold them with 
a new boatload of defective crap (every six months), a very few of the 
deliberately imposed limitations will be removed (plus we make yet another 
boatload of money by ripping off the customer).

> Or maybe you’d like to look to a less legacy-bound company?  Say,
> Google, who ships Chrome still built as 32-bit, originally for
> compatibility with 32-bit NSAPI plugins.  Since they dropped that, I
> can only guess why they’re still building 32-bit binaries, and that
> guess is that with the tab-per-process isolation, no single tab needs more 
> than 4 GB of VM space.

Or they are using defective compilers (primary supplier in that field is 
Microsoft) that cannot switch memory models without re-writing the code.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
This communication is the property of CenturyLink and may contain confidential 
or privileged information. Unauthorized use of this communication is strictly 
prohibited and may be unlawful. If you have received this communication in 
error, please immediately notify the sender by reply e-mail and destroy all 
copies of the communication and any 

[sqlite] How to check if connection to main database is still open or closed before new query ?

[Andy Ling]

I don't think this will work. sqlite3_open and sqlite3_close return a status 
which, if they worked, is SQLITE_OK.

A simpler solution might be to set ppDB to NULL in your code every time you 
close the database.

So add something like

sqlite3_close(ppDB);
ppDB = NULL ;

Then the original test Simon suggested will work.

Regards

Andy Ling

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of a a
Sent: Tue 01 March 2016 17:13
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to main database is still open 
or closed before new query ?

Let me explain it better cause i got confused and i am a  newbie also.

I open a connection like this 

sqlite3 *ppDB;
int dbcon;
 dbcon = sqlite3_open(databasename, );
/// i do my code i query the database i do some stuff and i close the database 
like this
dbcon = sqlite3_close(ppDB);

I want to check after a while if the connection is allready closed or not for 
the simple reason not to reopen the database but if is open to run a query or 
if it is closed 
to reopen the database and then run the query.

so i think the dbcon is the one i have to check not the ppDB :/

it works now maby i didnt understand what ppDB you were reffering to

if(dbcon){
//is open
}else{
//is closed
}

:) thanks though it is tested and runs ok


From: sqlite-users-bounces at mailinglists.sqlite.org  on behalf of Simon Slavin <slav...@bigfraud.org>
Sent: Tuesday, March 1, 2016 7:01 PM
To: SQLite mailing list
Subject: Re: [sqlite] How to check if connection to maindatabase
is  still   open or closed before new query ?

On 1 Mar 2016, at 4:59pm, a a  wrote:

> the sqlite3_close() <--- needs one argument if i pass sqlite3_close(NULL) it 
> actually closes again a closed connection ?

No.  If you pass it null it realises that the connection is closed and does 
nothing.

Simon.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] I don't know how to cast ‘const unsigned char*’ to a 'const std::string'

[Andy Ling]

> Actualy (maybe I wrong) but it leads to the same result in that case
> that using `reinterpret_cast`
> 
> my problem about doing that is I don't know if it's safe...
> I mean, should I not get some mess with some char code
> (UTF8)?
> 

You could create a new unsigned string type

typedef std::basic_string  ustring;

ustring s = sqlite3_column_text(..);

Maybe that would do what you want and preserve any
odd UTF8 characters.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Andy Ling]

> > That's a common technique with LEFT JOIN
> 
> Thanks, Igor.
>

A bit MySQL specific, but shows some options

http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Compiling sqlite as as library

[Andy Ling]

> I tried with the following flags :
> 
> gcc -arch x86_64 -o sqlite3.o sqlite3.c shell.c
> 
> 
>   and
> 
> 
> > gcc -m32  -o sqlite3.o sqlite3.c shell.c
> 
> 
> but both results in the same error.
> 
> Can you please help me solve this ?
> 

It's getting outside my level of expertise (and not really sqlite specific).
Try googling, there are lots of results. Maybe something like this will help

https://github.com/tpoechtrager/osxcross/issues/11

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Compiling sqlite as as library

[Andy Ling]

> I get the following error while trying to compile after linking with the
> library
> 
> ld: warning: ignoring file
> > /Users/prakash-2282/Downloads/sqlite-amalgamation-
> 201506301510/libsqlite3.a,
> > file was built for archive which is not the architecture being linked
> > (x86_64):
> > /Users/prakash-2282/Downloads/sqlite-amalgamation-
> 201506301510/libsqlite3.a
> 

I'm guessing, but it looks like your compiling for 64bit and linking to a 32bit 
system
or vice versa.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Compiling sqlite as as library

[Andy Ling]

> Thanks a ton Andy for your reply.
> 
> can you specify how I should specify the include flags (-I).
> The kore build complais that "sqlite3.h" header file is not found .
> 

Sounds like you need to read a few more manuals :^)

Normally there are compiler options that let you tell it which directories
to look in. For header files it is normally -I for include directories
and for libraries it is -L. So just use these to add the directories that
contain your sqlite3.h and libsqlite3.a files.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Compiling sqlite as as library

[Andy Ling]

> Can you please tell me how to compile it as a library on Mac OSX and Linux
> platforms ?
> 

Use your favourite compiler to generate sqlite3.o then use ar to wrap
that up as a .a file. Give that file a name something like
libsqlite3.a and you will be able to add -Lsqlite3 to your LDFLAGS.

The ar command will be something like

ar -rus libsqlite3.a sqlite3.o

Check the man page for more information

HTH

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] PRAGMA Synchronous safety

[Andy Ling]

> >So would it be possible to run that command each time you open the config
> database and after any change to it ?  That would give us a perfect way to
> find out which commands were causing your problems.<
> 
> Not really possible. The average update rare is low, but there are times when
> hundreds of settings are written, depending on which changes the user
> makes in preferences etc. Users can update settings from custom scripts,
> which may mean one update per session or hundreds per minute. Running a
> 5 second integrity check after each write would bring down performance
> badly.
> 

I had got the impression (and probably Simon had too) that the preferences 
database
was a lot smaller, so the integrity check would be a lot quicker.

> I now also run an integrity_check when closing the settings database during
> application shut-down and will seek to find a way to notify the user to retain
> the log file - in the hope that it contains more info. My users are no IT 
> folks,
> just average users, moms & pops. Displaying scary error messages about
> damaged databases and asking to send log files will cause a lot of additional
> support and probably bad reviews in social media. Database damage is a very
> sensitive area.
> 

I wonder whether you could provide those that have suffered a corrupt database
with "special" code with extra logging and checks. You could warn them about 
scary
messages and longer delays. Explaining this is part of your investigations. 
These
people already know there is a problem, so are unlikely to spread bad reviews.
They may also be more likely to suffer another corruption if it relates to a 
particular
workflow.

As a user of your application I would be happy to help, unfortunately (not for 
me :^)
I've never had a corrupted database.

Regards

Andy Ling

Re: [sqlite] Autoincrement with rollback

[Andy Ling]

> --- Begin ---
> 
> sqlite> drop table if exists demo;
> sqlite> create table demo (id integer primary key autoincrement, value
> text);
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> sqlite> delete from demo where id = 1;
> sqlite> rollback;
> sqlite> begin transaction;
> sqlite> insert into demo (value) VALUES ('value');
> sqlite> select last_insert_rowid();
> 1
> 
> --- End ---
> 
> Using a delete statement before the rollback
> doesn't help.
> 
> I really want sqlite to only issue an id once.
> 

Surely the whole point of rollback is that it puts the database back to the
state it was in before the transaction started. So everything should be
just as if no inserts (or deletes or anything else)had happened, including
any changes to autoincrement values.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> > > Please try the patch at
> > >
> http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> > > 3 and
> > > see if that fixes the problem.
> > >
> >
> > I think that fixes unixDelete. Running on the vxWorks dosFs disk
> > everything works as before.
> >
> > If I use the host filing system, then I think the delete of the
> > non-existent file works, but it then fails in unixSync followed by a fail
> > in unixDelete
> >
> > os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
> >
> 
> 
> Error code 35 is ENOTSUP - fsync is apparently not supported on your
> filesystem.
> 

I have asked WindRiver about the various issues we have seen and their initial 
response was...

 An errno of EACCES is set by the hostFS and unfortunately it’s not 
aligned
 with POSIX errno. I have suggested to our developers to update that 
and it’s
 tracked internally as VXW6-83401 but the request will be considered an
 enhancement and the decision will be taken later in time when the 
product management
 team will decide to implement it.

 The fsync is indeed not supported on hostFS so the error is expected. 
Because the target
 server connection is mostly used for debugging sessions implementing 
POSIX API is not in plan.

I did query whether fsync is just "unnecessary" and whether it could be made a 
no-op that
just returned OK. So far I haven't had a response.

I did wonder whether the ENOTSUP error could be ignored for vxWorks. It seems 
vaguely reasonable
that if fsync is not supported it isn't needed so SQLite could just ignore the 
error.

I did complain to WindRiver that this mess of differences between filing 
systems makes writing
portable code very difficult. Again, I haven't had a reply.

Regards

Andy Ling




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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of ???
> Sent: 02 September 2014 13:41
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has some problem
> 
> 
> 
> firstly :
> Even if I used
> #if OS_VXWORKS
> || osAccess(zPath,0) != 0
> #endif
> 
> 
> or I used
> #if OS_VXWORKS
> }else if(errno == 0x380003 || errno == 13 )
>   rc  = SQLITE_IOERR_DELETE_NOENT:
> #endif
> 
> 
> Without the SQLITE_ENABLE_LOCKING_STYLE compile option, I tried the
> above two method, but  it still told me the error : disk I/o error.
> 

If you read what I wrote in the email you are answering, you will see that the 
problem is that the host file system does not support fsync.

So the short answer is that you cannot use SQLite with a database that is 
stored on your host PC. You must use a file system that is part of the target 
system.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: 01 September 2014 15:30
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has some problem
> > > Please try the patch at
> > >
> http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> > > 3 and
> > > see if that fixes the problem.
> > >
> >
> > I think that fixes unixDelete. Running on the vxWorks dosFs disk
> > everything works as before.
> >
> > If I use the host filing system, then I think the delete of the
> > non-existent file works, but it then fails in unixSync followed by a fail
> > in unixDelete
> >
> > os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
> >
> 
> 
> Error code 35 is ENOTSUP - fsync is apparently not supported on your
> filesystem.
> 

OK. So it sounds like the answer to Wang Qinggang at the moment is that the 
host filing system is not supported.

I'm slightly surprised fsync isn't supported, but a quick test here proves that 
it isn't. I will try and raise it with WindRiver and see what they say. It may 
be a general problem with any network filing system under vxWorks.

I'm happy that dosFs works. That's all I need for the time being.

Regards

Andy Ling


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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: 01 September 2014 14:39
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> Please try the patch at
> http://www.sqlite.org/src/info/b0f6b91f36b503d8ba8d5257bb194f8c1afb483
> 3 and
> see if that fixes the problem.
> 

I think that fixes unixDelete. Running on the vxWorks dosFs disk everything 
works as before.

If I use the host filing system, then I think the delete of the non-existent 
file works, but it then fails in unixSync followed by a fail in unixDelete

os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)
Error deleting file /tgtsvr/testdb.sql-journal errno 13
os_unix.c:30167: (13) unlink(/tgtsvr/testdb.sql-journal) -  (2570)
Error executing statement CREATE TABLE t (id INTEGER PRIMARY KEY ASC) - disk 
I/O error
Error message disk I/O error

Regards

Andy Ling


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

Re: [sqlite] HELP sqlite3 used in vxworks has some problem

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jan Nijtmans
> Sent: 01 September 2014 12:29
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> 2014-09-01 13:04 GMT+02:00 Andy Ling <andy.l...@quantel.com>:
> > Although this doesn't feel right having to keep extending the list of error
> codes that are checked.
> > I'm not sure what else can be done though. Maybe some compile options
> that let you choose.
> 
> Here is a list of possible error-codes I found:
> 
> <http://www.vxdev.com/docs/vx55man/vxworks/errno/errnoNumList.html
> >

That list is from an older version of vxWorks (5.5). So some things have 
changed slightly.

> 
> The value 13 is listed here as EACCES, I really doubt this one
> should be added: If there really is an access problem, I
> think that shouldn't be masked.
> 

That code is still as listed. I suspect the problem is vxWorks can't tell the 
difference between
a file not there and not being able to access the file. Although see later...

> There are two entries that end with _FILE_NOT_FOUND, the other one is:
> 1310723  0x140003  S_rt11FsLib_FILE_NOT_FOUND
> This suggests that for VxWorks an additional check for 0x140003
> might work if you use the rt11 filesystem (whatever that is .)
> 

rt11 is not supported any longer. It harks back to the days of the LSI11 and 
DEC. rt11 was the
OS DEC used then. It was a bit like a cut down version of VMS.

> Other possible candidates:
> 2686989  0x29000d  S_netDrv_NO_SUCH_FILE_OR_DIR
> 2949130  0x2d000a  S_nfsDrv_NO_SUCH_FILE_OR_DIR
> 7602186  0x74000a  S_cdromFsLib_NO_SUCH_FILE_OR_DIRECTORY
> 

They are still current.

vxWorks now includes the TrueFlashFilingSystem which has the error code

13303810  0xcb0002  S_tffsLib_FILE_NOT_FOUND

I have done some more investigation accessing the host file system.
First, running unlink from the command line. Calling unlink for a file that 
exists works correctly. It deletes the file and returns OK. Calling unlink for 
a file that doesn't exist returns error and sets errno to EACCES. So this may 
be a bug or vxWorks may not be able to tell when talking over a network. If I 
get time I will put in a call to WindRiver to see what the official line is.

So I added the check in unixDelete for errno 13 to see what happens.

The open does create a database file on my host PC. The create table still 
fails with a disk I/O error, but it gets a bit further.
I've added a print in unixLogError and it prints the following...

os_unix.c:27830: (35) full_fsync(/tgtsvr/testdb.sql-journal) -  (1034)

The 1034 on the end is errno.

So it is failing in unixSync. It has created the testdb.sql-journal file and it 
is 512 bytes long. So I suspect things are working, but the error checks don't 
think they are. At this point I probably need more help to save me wasting too 
much time.

Personally I'm not sure how much effort needs to be put into this if it is only 
the host file system that is a problem. Most vxWorks systems would only be 
using the host filing system during development.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> -Original Message-
> From: Andy Ling
> Sent: 29 August 2014 16:15
> To: '???'; sqlite-users@sqlite.org
> Subject: RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> I would add a printf statement in unixDelete rather than rely on a break point
> working.
> 


I finally had 5 minutes to try this out. I added the following to unixDelete

}else{
  printf ("Error deleting file %s error %d\n", zPath, errno) ;
  rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
}

I then ran the following

func call _open "/tgtsvr/testdb.sql" &
func call _execute "CREATE TABLE t (id INTEGER PRIMARY KEY ASC)" &

Where /tgtsvr is a mount of the file system on my Windows PC.  This generates 
the following

Error deleting file /tgtsvr/testdb.sql-wal error 13
Error executing statement CREATE TABLE t (id INTEGER PRIMARY KEY ASC) - disk 
I/O error
Error message disk I/O error

So as suspected, the host file system is returning yet another error code when 
deleting a file that doesn't exist.

This can be fixed by changing the patch to something like...

#if OS_VXWORKS
}else if( errno==0x380003  || errno == 13){ /* == S_dosFsLib_FILE_NOT_FOUND 
*/
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif

Although this doesn't feel right having to keep extending the list of error 
codes that are checked.
I'm not sure what else can be done though. Maybe some compile options that let 
you choose.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

I would add a printf statement in unixDelete rather than rely on a break point 
working.

Of course, it may not be unixDelete that is the problem, we just need to be 
sure.

Do you have a disk on your target system? If you do, perhaps you could try 
creating the database file on that and see if that works. 

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com] 
Sent: 29 August 2014 15:49
To: Andy Ling; sqlite-users@sqlite.org
Subject: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？

hi, Andy Ling
     I have removed the SQLITE_ENABLE_LOCKING_STYLE compile option, so it tell 
me the error : disk I/o error.
       but when I set breakpoint in unixdelete funtion , I found it not enter 
the funtion. I do not know why.
      
     You are right! What OS your host system is running (Windows in my case).
        I want to get some information from the unixdelete funtion.

     Regards
     Wang Qinggang


At 2014-08-29 05:30:23, "Andy Ling" <andy.l...@quantel.com> wrote:

Have you removed the SQLITE_ENABLE_LOCKING_STYLE compile option?
 
On my vxWorks system, if I open a new database and create a table it always 
calls unixDelete trying to delete a “database name”-wal file. This doesn’t 
exist so it has to handle the error.
 
This patch that was added ….
 
#if OS_VXWORKS
    }else if( errno==0x380003 ){ /* == S_dosFsLib_FILE_NOT_FOUND */
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif
 
to handle the error code returned by a dosFs filing system. Before adding this 
patch I got the disk I/O error.
 
Because you are using the host filing system I would guess the error code 
returned will be something different.
 
Looking at the WindRiver documentation for the tsfs driver it says….
 
“The routines in this library return the VxWorks error codes that most closely 
match the errnos generated by the corresponding host function.”
 
So it looks like it will depend on what OS your host system is running (Windows 
I assume in your case).
 
Regards
 
Andy Ling
 
 
From: 163 [mailto:2004wqg2...@163.com] 
Sent: 29 August 2014 03:34
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
 
i find it not enter the unixdelete.

发自我的 iPhone

在 2014年8月28日，22:42，Andy Ling <andy.l...@quantel.com> 写道：
Sorry, I meant unixDelete
 
My guess is that because you are using the host filing system vxWorks will be 
setting yet another error code for a file that doesn’t exist. So it will need 
another check adding to unixDelete
 
Regards
 
Andy Ling
 
 
From: 王庆刚 [mailto:2004wqg2...@163.com] 
Sent: 28 August 2014 15:01
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？
 
 
Is unixUnlink  an function? If it is , but I can not find the funtion in 
sqlite3.c.
 
Regards
 
Wang Qinggang
 



 

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Have you removed the SQLITE_ENABLE_LOCKING_STYLE compile option?

On my vxWorks system, if I open a new database and create a table it always 
calls unixDelete trying to delete a “database name”-wal file. This doesn’t 
exist so it has to handle the error.

This patch that was added ….

#if OS_VXWORKS
}else if( errno==0x380003 ){ /* == S_dosFsLib_FILE_NOT_FOUND */
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif

to handle the error code returned by a dosFs filing system. Before adding this 
patch I got the disk I/O error.

Because you are using the host filing system I would guess the error code 
returned will be something different.

Looking at the WindRiver documentation for the tsfs driver it says….

“The routines in this library return the VxWorks error codes that most closely 
match the errnos generated by the corresponding host function.”

So it looks like it will depend on what OS your host system is running (Windows 
I assume in your case).

Regards

Andy Ling


From: 163 [mailto:2004wqg2...@163.com]
Sent: 29 August 2014 03:34
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

i find it not enter the unixdelete.

发自我的 iPhone

在 2014年8月28日，22:42，Andy Ling 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> 写道：
Sorry, I meant unixDelete

My guess is that because you are using the host filing system vxWorks will be 
setting yet another error code for a file that doesn’t exist. So it will need 
another check adding to unixDelete

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com]
Sent: 28 August 2014 15:01
To: Andy Ling
Cc: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Jan Nijtmans
Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？


Is unixUnlink  an function? If it is , but I can not find the funtion in 
sqlite3.c.

Regards

Wang Qinggang






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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Sorry, I meant unixDelete

My guess is that because you are using the host filing system vxWorks will be 
setting yet another error code for a file that doesn’t exist. So it will need 
another check adding to unixDelete

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com]
Sent: 28 August 2014 15:01
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？


Is unixUnlink  an function? If it is , but I can not find the funtion in 
sqlite3.c.

Regards

Wang Qinggang





At 2014-08-28 00:03:23, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:

So you are using a host file system. You should be able to make the open work 
by using

rc = sqlite3_open("host:D:/WindRiver/SqliteOne.db",);

You have to use a path and file name that works in the vxWorks domain.

It shouldn’t be necessary to use �CDSQLITE_ENABLE_LOCKING_STYLE=1. In fact it 
has been said this will probably not work in other areas. So it would be good 
to find out what is causing the disk I/O error. Did you try putting some debug 
in unixUnlink?

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 27 August 2014 16:17
To: Andy Ling
Cc: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Jan Nijtmans
Subject: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？



Firstly:

I  fixed the disk I/O error by adding -DSQLITE_ENABLE_LOCKING_STYLE=1 to the 
build macro.

Secondly:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
I change it to the following
cd("host:D:/WindRiver");
rc = sqlite3_open("SqliteOne.db",);
can fix the problem : unable to open the database.


At 2014-08-26 08:55:31, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
As I said before, that doesn’t look like a vxWorks path to a file. Are you sure 
D:\\WindRiver really exists? In general vxWorks uses the forward slash (/) as a 
path separator. Is this a remote mounted host file system you are trying to use?

What is your current directory when it works the second time? i.e. what is the 
output from the “pwd” command? Can you use that directory as part of a full 
pathname?

I’m glad you have fixed the disk I/O problem. What did you have to do? Are 
there any more changes that need feeding back into the source?

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 26 August 2014 13:44
To: Andy Ling
Cc: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Jan Nijtmans
Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

hi, Andy Ling:
The error disk I / o error I have resolved ;
but when I used the following code:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me  can not find the data base;

when I used the following code:
rc = sqlite3_open("SqliteOne.db",);
It is OK


How could I resolve the prolblem?

At 2014-08-25 11:29:57, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
That file name in the first error doesn't look like a vxWorks file. What 
devices have you got mounted. You need to specify a file path that points to 
one of the vxWorks file IO devices. By just specifying the file name in your 
second example it is being created in the current directory.

The second disk I / O error is what I had before applying the patch to 
unixUnlink. The error returned by vxWorks when deleting a file that doesn't 
exist depends on the underlying file system. If it is a POSIX file system it 
should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in 
vxWorks 6.9 is 0x380003

So if you are using yet another file system, maybe you are getting a different 
error code being set. start by adding a printf to unixUnlink to find out if 
that is your problem.

Regards

Andy Ling
____
From: 王庆刚 [2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 25 August 2014 13:13
To: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Andy Ling; Jan 
Nijtmans
Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
hi
  I modified the code sqlite3.c according to you method, as follow

 
<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
  or   <http://fossil-scm.org/index.html/info/c2d4bd7365>

I test you method in workbench3.2(vxworks6.8) , the build macros which I used 
in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0  
-DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME

but it still have some problems.
1.  if I do as follows:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me the error  can not open the database.


2.  if I do as follows:
rc = sqlite3_open("SqliteOne.db",);this will be ok .
but

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Sorry, I meant unixDelete

My guess is that because you are using the host filing system vxWorks will be 
setting yet another error code for a file that doesn’t exist. So it will need 
another check adding to unixDelete

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com]
Sent: 28 August 2014 15:01
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re:RE: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？


Is unixUnlink  an function? If it is , but I can not find the funtion in 
sqlite3.c.

Regards

Wang Qinggang





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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

So you are using a host file system. You should be able to make the open work 
by using

rc = sqlite3_open("host:D:/WindRiver/SqliteOne.db",);

You have to use a path and file name that works in the vxWorks domain.

It shouldn’t be necessary to use �CDSQLITE_ENABLE_LOCKING_STYLE=1. In fact it 
has been said this will probably not work in other areas. So it would be good 
to find out what is causing the disk I/O error. Did you try putting some debug 
in unixUnlink?

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com]
Sent: 27 August 2014 16:17
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re:RE: [sqlite] HELP sqlite3 used in vxworks has someproblem？



Firstly:

I  fixed the disk I/O error by adding -DSQLITE_ENABLE_LOCKING_STYLE=1 to the 
build macro.

Secondly:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
I change it to the following
cd("host:D:/WindRiver");
rc = sqlite3_open("SqliteOne.db",);
can fix the problem : unable to open the database.


At 2014-08-26 08:55:31, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:

As I said before, that doesn’t look like a vxWorks path to a file. Are you sure 
D:\\WindRiver really exists? In general vxWorks uses the forward slash (/) as a 
path separator. Is this a remote mounted host file system you are trying to use?

What is your current directory when it works the second time? i.e. what is the 
output from the “pwd” command? Can you use that directory as part of a full 
pathname?

I’m glad you have fixed the disk I/O problem. What did you have to do? Are 
there any more changes that need feeding back into the source?

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 26 August 2014 13:44
To: Andy Ling
Cc: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Jan Nijtmans
Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

hi, Andy Ling:
The error disk I / o error I have resolved ;
but when I used the following code:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me  can not find the data base;

when I used the following code:
rc = sqlite3_open("SqliteOne.db",);
It is OK


How could I resolve the prolblem?


At 2014-08-25 11:29:57, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
That file name in the first error doesn't look like a vxWorks file. What 
devices have you got mounted. You need to specify a file path that points to 
one of the vxWorks file IO devices. By just specifying the file name in your 
second example it is being created in the current directory.

The second disk I / O error is what I had before applying the patch to 
unixUnlink. The error returned by vxWorks when deleting a file that doesn't 
exist depends on the underlying file system. If it is a POSIX file system it 
should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in 
vxWorks 6.9 is 0x380003

So if you are using yet another file system, maybe you are getting a different 
error code being set. start by adding a printf to unixUnlink to find out if 
that is your problem.

Regards

Andy Ling

From: 王庆刚 [2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 25 August 2014 13:13
To: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Andy Ling; Jan 
Nijtmans
Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
hi
  I modified the code sqlite3.c according to you method, as follow

 
<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
  or   <http://fossil-scm.org/index.html/info/c2d4bd7365>

I test you method in workbench3.2(vxworks6.8) , the build macros which I used 
in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0  
-DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME

but it still have some problems.
1.  if I do as follows:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me the error  can not open the database.


2.  if I do as follows:
rc = sqlite3_open("SqliteOne.db",);this will be ok .
but when I do the following thing
sql = " create table stu(i int, name text);";
rc = sqlite3_exec(db,sql,NULL,NULL,);
it tell me the error  : disk I / O  error.



在 2014-08-12 08:10:13，"Jan Nijtmans" 
<jan.nijtm...@gmail.com<mailto:jan.nijtm...@gmail.com>> 写道：
2014-08-03 9:56 GMT+02:00 Jan Nijtmans 
<jan.nijtm...@gmail.com<mailto:jan.nijtm...@gmail.com>>:
2014-08-02 16:00 GMT+02:00 王庆刚 
<2004wqg2...@163.com<mailto:2004wqg2...@163.com>>:
> hi ， Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for 
> Vxworks6.8 ?
> When I  compile them , there have so many problems .
You can find the necessary changes here:

<http://fossil-scm.org/index.html/vp

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

As I said before, that doesn’t look like a vxWorks path to a file. Are you sure 
D:\\WindRiver really exists? In general vxWorks uses the forward slash (/) as a 
path separator. Is this a remote mounted host file system you are trying to use?

What is your current directory when it works the second time? i.e. what is the 
output from the “pwd” command? Can you use that directory as part of a full 
pathname?

I’m glad you have fixed the disk I/O problem. What did you have to do? Are 
there any more changes that need feeding back into the source?

Regards

Andy Ling


From: 王庆刚 [mailto:2004wqg2...@163.com]
Sent: 26 August 2014 13:44
To: Andy Ling
Cc: sqlite-users@sqlite.org; Jan Nijtmans
Subject: Re:RE: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

hi, Andy Ling:
The error disk I / o error I have resolved ;
but when I used the following code:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me  can not find the data base;

when I used the following code:
rc = sqlite3_open("SqliteOne.db",);
It is OK


How could I resolve the prolblem?



At 2014-08-25 11:29:57, "Andy Ling" 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:

That file name in the first error doesn't look like a vxWorks file. What 
devices have you got mounted. You need to specify a file path that points to 
one of the vxWorks file IO devices. By just specifying the file name in your 
second example it is being created in the current directory.

The second disk I / O error is what I had before applying the patch to 
unixUnlink. The error returned by vxWorks when deleting a file that doesn't 
exist depends on the underlying file system. If it is a POSIX file system it 
should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in 
vxWorks 6.9 is 0x380003

So if you are using yet another file system, maybe you are getting a different 
error code being set. start by adding a printf to unixUnlink to find out if 
that is your problem.

Regards

Andy Ling


From: 王庆刚 [2004wqg2...@163.com<mailto:2004wqg2...@163.com>]
Sent: 25 August 2014 13:13
To: sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>; Andy Ling; Jan 
Nijtmans
Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
hi
  I modified the code sqlite3.c according to you method, as follow

 
<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
  or   <http://fossil-scm.org/index.html/info/c2d4bd7365>

I test you method in workbench3.2(vxworks6.8) , the build macros which I used 
in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0  
-DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME

but it still have some problems.
1.  if I do as follows:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me the error  can not open the database.


2.  if I do as follows:
rc = sqlite3_open("SqliteOne.db",);this will be ok .
but when I do the following thing
sql = " create table stu(i int, name text);";
rc = sqlite3_exec(db,sql,NULL,NULL,);
it tell me the error  : disk I / O  error.




在 2014-08-12 08:10:13，"Jan Nijtmans" 
<jan.nijtm...@gmail.com<mailto:jan.nijtm...@gmail.com>> 写道：

2014-08-03 9:56 GMT+02:00 Jan Nijtmans 
<jan.nijtm...@gmail.com<mailto:jan.nijtm...@gmail.com>>:
2014-08-02 16:00 GMT+02:00 王庆刚 
<2004wqg2...@163.com<mailto:2004wqg2...@163.com>>:
> hi ， Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for 
> Vxworks6.8 ?
> When I  compile them , there have so many problems .
You can find the necessary changes here:

<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
thanks to Andy Ling.

Still has to be reviewed by the SQLite developers for inclusion in 3.8.6
(not tested yet on other platforms than vxworks, win32/64 and Linux,
there it works fine)

New attempt here, base on current SQLite trunk:
  <http://fossil-scm.org/index.html/info/c2d4bd7365>
Regards,
  Jan Nijtmans


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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

That file name in the first error doesn't look like a vxWorks file. What 
devices have you got mounted. You need to specify a file path that points to 
one of the vxWorks file IO devices. By just specifying the file name in your 
second example it is being created in the current directory.

The second disk I / O error is what I had before applying the patch to 
unixUnlink. The error returned by vxWorks when deleting a file that doesn't 
exist depends on the underlying file system. If it is a POSIX file system it 
should return ENOENT. For dosFS it returns S_dosFsLib_FILE_NOT_FOUND, which in 
vxWorks 6.9 is 0x380003

So if you are using yet another file system, maybe you are getting a different 
error code being set. start by adding a printf to unixUnlink to find out if 
that is your problem.

Regards

Andy Ling



From: 王庆刚 [2004wqg2...@163.com]
Sent: 25 August 2014 13:13
To: sqlite-users@sqlite.org; Andy Ling; Jan Nijtmans
Subject: Re:Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

hi
  I modified the code sqlite3.c according to you method, as follow

 
<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
  or   <http://fossil-scm.org/index.html/info/c2d4bd7365>

I test you method in workbench3.2(vxworks6.8) , the build macros which I used 
in build properties is : -DOS_VXWORKS=1 -DSQLITE_THREADSAFE=0  
-DSQLITE_OMIT_LOAD_EXTENSION -DHAVE_UTIME

but it still have some problems.
1.  if I do as follows:
rc = sqlite3_open("D:\\WindRiver\\SqliteOne.db",);
it tell me the error  can not open the database.


2.  if I do as follows:
rc = sqlite3_open("SqliteOne.db",);this will be ok .
but when I do the following thing
sql = " create table stu(i int, name text);";
rc = sqlite3_exec(db,sql,NULL,NULL,);
it tell me the error  : disk I / O  error.





在 2014-08-12 08:10:13，"Jan Nijtmans" <jan.nijtm...@gmail.com> 写道：
2014-08-03 9:56 GMT+02:00 Jan Nijtmans 
<jan.nijtm...@gmail.com<mailto:jan.nijtm...@gmail.com>>:
2014-08-02 16:00 GMT+02:00 王庆刚 
<2004wqg2...@163.com<mailto:2004wqg2...@163.com>>:
> hi ， Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for 
> Vxworks6.8 ?
> When I  compile them , there have so many problems .

You can find the necessary changes here:

<http://fossil-scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a1>
thanks to Andy Ling.

Still has to be reviewed by the SQLite developers for inclusion in 3.8.6
(not tested yet on other platforms than vxworks, win32/64 and Linux,
there it works fine)

New attempt here, base on current SQLite trunk:
  <http://fossil-scm.org/index.html/info/c2d4bd7365>

Regards,
  Jan Nijtmans



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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

That looks like a makefile generated by the vxWorks eclipse based workbench IDE 
tool. The bit that would be based on "user input" is that list of OPTs (the 
source files would be read automatically by adding to the project).

I use a custom build, so don't use these automatically generated files.

As to the options, Richard has said SQLITE_EABLE_LOCKING_STYLE should only be 
needed for Apple users.

So far I have done so little with SQLite that I don't know if I need any of the 
more advanced features. The compile flags I am using are -DHAVE_UTIME 
-DSQLITE_OMIT_LOAD_EXTENSION. This certainly compiles and runs to the point of 
letting me create tables and add and read data. As to locking and threading 
issues, I will find out over the next few months.

Regards

Andy Ling


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jan Nijtmans [jan.nijtm...@gmail.com]
Sent: 14 August 2014 08:19
To: General Discussion of SQLite Database
Subject: Re: [sqlite]   HELP sqlite3 used in vxworks has someproblem？

2014-08-13 14:31 GMT+02:00 Andy Ling <andy.l...@quantel.com>:

> > From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of
> Richard Hipp
> > OK, I did find some cases where unlink() is called on files that do not
> exist.
>


> > Anyway, a patch has now been checked in.
>
> Great, thank


Congratulations to Andy and Richard for getting VxWorks
support back into SQLite. Just one more question:

What is the status of the Makefile.vxworks file in
the SQLite source code? Especially line 68
puzzles me, what is SQLITE_ENABLE_LOCKING_STYLE=1
needed for? (User "chw", whoever that is, should be able
to answer that question, since he/she committed this Makefile.vxworks)

<http://www.sqlite.org/src/artifact/034289efa9?ln=68>

Regards,
   Jan Nijtmans
___
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] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard 
> Hipp
> Sent: 13 August 2014 12:40
> To: Andy Ling
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

> OK, I did find some cases where unlink() is called on files that do not exist.

Some of those very very rare cases :^)

Good to know there aren't more issues to track down.

> Anyway, a patch has now been checked in.

Great, thanks

Andy Ling


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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Andy Ling
> Sent: 13 August 2014 10:06
> To: 'Richard Hipp'
> Cc: General Discussion of SQLite Database
> Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？
> 
> Then I try and create a table and get an error from unixDelete saying it can't
> delete /ata0:3/testdb.sql-wal. It actually fails twice.
> 

And before someone asks. I did try and create a file called 
/ata0:3/testdb.sql-wal and vxWorks is quite happy to create one.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

>  If your build is frequently trying to unlink a file that does not exist, 
> then something is wrong.  We need to figure out what that is and fix it.

I have added some debug.

First I create a brand new database called /ata0:3/testdb.sql

Then I try and create a table and get an error from unixDelete saying it can't 
delete /ata0:3/testdb.sql-wal. It actually fails twice.

My shell output is shown below

Regards

Andy Ling


[quentina]# ls -l
drwxrwxrwx  1 0   032768 Aug 13 09:55 logs/
drwxrwxrwx  1 0   032768 Aug 13 09:55 db/
drwxrwxrwx  1 0   032768 Dec 19  2013 dataLogs/
drwxrwxrwx  1 0   032768 Oct 29  2013 csv/
[quentina]#
[quentina]#
[quentina]# pwd
/ata0:3
[quentina]#
[quentina]# func call _open "/ata0:3/testdb.sql" &
[quentina]# 2014/08/13 10:02:39.00326[stdout] sql_open opening database 
/ata0:3/testdb.sql
2014/08/13 10:02:39.00326[stdout] Database /ata0:3/testdb.sql opened - pDb 
= 0x3ee9c48

[quentina]# ls -l
drwxrwxrwx  1 0   032768 Aug 13 09:55 logs/
drwxrwxrwx  1 0   032768 Aug 13 09:55 db/
drwxrwxrwx  1 0   032768 Dec 19  2013 dataLogs/
drwxrwxrwx  1 0   032768 Oct 29  2013 csv/
-rwxrwxrwx  1 0   00 Aug 13 10:02 testdb.sql
[quentina]# func call _execute "CREATE TABLE t (id INTEGER PRIMARY KEY 
ASC)" &
[quentina]# 2014/08/13 10:03:03.00897[stdout] Spawning sqlExec
2014/08/13 10:03:03.00898[stdout] unixDelete error 0x380003 for file 
/ata0:3/testdb.sql-wal
2014/08/13 10:03:03.00898[stdout] unixDelete error 0x380003 for file 
/ata0:3/testdb.sql-wal
2014/08/13 10:03:03.00913[stdout] Statement CREATE TABLE t (id INTEGER 
PRIMARY KEY ASC) executed

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

OK, thanks.

It isn't limited to 8+3. The dosFs name refers to the FAT nature of the file 
system, not any filename limitations. In fact files don't have to have a 
.extension.

Regards

Andy Ling


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: 12 August 2014 20:59
To: General Discussion of SQLite Database
Subject: Re: [sqlite]   HELP sqlite3 used in vxworks has someproblem？

On Tue, Aug 12, 2014 at 3:54 PM, Andy Ling <andy.l...@quantel.com> wrote:

>
> I did wonder if SQLite was making any assumptions about current
> directories or the makeup of a file name.
>

No.  Any filename will do.

You have to specify -DSQLITE_ENABLE_8_3_NAMES if your filesystem is limited
to 8+3 filenames, however.

--
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Our code is already creating lots of files in /ata0:3 without any problem. This 
is quite a mature product that is working well and is exercising the hardware 
and drivers extensively. The database is a new feature to initially provide 
persistence of some data. So I am confident there is nothing wrong with the 
disk or its drivers.

I did wonder if SQLite was making any assumptions about current directories or 
the makeup of a file name. /ata0:3 is an unusual format for a directory name.

Anyway, a simple printf in unixDelete tomorrow will reveal all.

Regards

Andy Ling


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: 12 August 2014 20:16
To: General Discussion of SQLite Database
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

On 12 Aug 2014, at 7:46pm, Andy Ling <andy.l...@quantel.com> wrote:

> I can tell you the name of the database file is /ata0:3/testdb.sql and it 
> gets created.

Can you add some commands to one of your apps using the library which create 
and delete a file in '/ata0:3/' and see they work ?  In other words, duplicate 
what SQLite is trying to do in your own code and see if your code has the same 
problem.

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] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

I don't know. The standard debug doesn't print the name, so I will have to add 
something tomorrow.

I can tell you the name of the database file is /ata0:3/testdb.sql and it gets 
created.

It is possible things are working. It is just that the sqlite3_exec is not 
returning SQLITE_OK, so I give up and print an error.

I wonder if before the check was added to unixDelete all unlink errors were 
ignored. So it would have worked just fine and you wouldn't have known it was 
trying to delete files that didn't exist.

I will investigate further tomorrow and let you know the file names.

Regards

Andy Ling


From: drhsql...@gmail.com [drhsql...@gmail.com] on behalf of Richard Hipp 
[d...@sqlite.org]
Sent: 12 August 2014 19:15
To: Andy Ling
Cc: General Discussion of SQLite Database; 王庆刚
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

What is the name of the file that SQLite is trying to delete but which does not 
exist?  And what is the name of the corresponding database file?  The name of 
the file that fails unlink() will give us a big clue about what is going wrong.


On Tue, Aug 12, 2014 at 2:13 PM, Richard Hipp 
<d...@sqlite.org<mailto:d...@sqlite.org>> wrote:



On Tue, Aug 12, 2014 at 2:00 PM, Andy Ling 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
Because the file doesn't exist. I assume because this is a brand new database 
the file hasn't been created yet.

I did debug this originally, but I don't remember the file it is trying to 
delete. It definitely didn't exist though.

To some extent it doesn't really matter. The unixDelete function on vxWorks 
with dosFs is broken for files that don't exist, so some change is needed.

That check to ignore the error when trying to delete a file that does not exist 
- that check was only added less than 2 years ago, 2012-11-10.  So for the 
first 8 years of its history, billions of instances of SQLite3 got along fine 
without that check.  This is not surprising since an attempt to delete a file 
that does not exist should only come up in very rare circumstances.

So we can update the unixDelete routine for that.

But, the fact that your build of SQLite does not work *at all* without such a 
change suggests that there are other problems - problems that are being masked, 
but not resolved, by the unixDelete change.  I'm trying figure out what those 
other problems are.



--
D. Richard Hipp
d...@sqlite.org<mailto:d...@sqlite.org>



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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Because the file doesn't exist. I assume because this is a brand new database 
the file hasn't been created yet. 

I did debug this originally, but I don't remember the file it is trying to 
delete. It definitely didn't exist though. 

To some extent it doesn't really matter. The unixDelete function on vxWorks 
with dosFs is broken for files that don't exist, so some change is needed. The 
patch I sent is what Jan and I came up with. 

Regards 
 
Andy Ling  

From: drhsql...@gmail.com [drhsql...@gmail.com] on behalf of Richard Hipp 
[d...@sqlite.org]
Sent: 12 August 2014 18:01
To: Andy Ling
Cc: General Discussion of SQLite Database; 王庆刚
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

On Tue, Aug 12, 2014 at 12:45 PM, Andy Ling 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
Doing a create table generates a disk I/O error

I open / create a new database with sqlite3_open_v2

  int err = sqlite3_open_v2 (file, , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, "unix-namedsem") ;

Then call sqlite3_exec with “CREATE TABLE t (id INTEGER PRIMARY KEY ASC)”

This generates a disk I/O error. I assume it is trying to remove a temporary 
file.


It might be trying to unlink the rollback journal to commit the transaction.  
But why is that generating an error?  The file exists, so it shouldn't be 
generating a FILE_NOT_FOUND error...


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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

Doing a create table generates a disk I/O error

I open / create a new database with sqlite3_open_v2

  int err = sqlite3_open_v2 (file, , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, "unix-namedsem") ;

Then call sqlite3_exec with “CREATE TABLE t (id INTEGER PRIMARY KEY ASC)”

This generates a disk I/O error. I assume it is trying to remove a temporary 
file.

Regards

Andy Ling


From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp
Sent: 12 August 2014 17:24
To: Andy Ling
Cc: General Discussion of SQLite Database; 王庆刚
Subject: Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？



On Tue, Aug 12, 2014 at 11:40 AM, Andy Ling 
<andy.l...@quantel.com<mailto:andy.l...@quantel.com>> wrote:
> From: drhsql...@gmail.com<mailto:drhsql...@gmail.com> 
> [mailto:drhsql...@gmail.com<mailto:drhsql...@gmail.com>] On Behalf Of Richard 
> Hipp
> Sent: 12 August 2014 15:46

> I put a new snapshot on the download page.  Please try it, *without* 
> SQLITE_ENABLE_LOCKING_MODE.
OK, it builds, but doesn't run.

What are you doing that is causing an unlink() call to fail?  That should be a 
very obscure and infrequent occurrence.  How is this preventing you from 
running simple tests?  What problems does it display?




It is missing the patch to unixDelete. Whilst vxWorks is POSIX compliant, for 
file I/O it is only
compliant if the underlying file system is. We are using dosFs, which isn't. 
This means the error
codes don't match. So we added the following.

static int unixDelete(
  sqlite3_vfs *NotUsed, /* VFS containing this as the xDelete method */
  const char *zPath,/* Name of file to be deleted */
  int dirSync   /* If true, fsync() directory after deleting file */
){
  int rc = SQLITE_OK;
  UNUSED_PARAMETER(NotUsed);
  SimulateIOError(return SQLITE_IOERR_DELETE);
  if( osUnlink(zPath)==(-1) ){
if( errno==ENOENT ){
  rc = SQLITE_IOERR_DELETE_NOENT;
#if OS_VXWORKS
}else if( errno==0x380003 ){ /* == S_dosFsLib_FILE_NOT_FOUND */
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif
}else{
  rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
}
With this patch I have run a few simple commands. Created a table, added a few 
rows and listed them.

The compile options I'm using are -DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION

Regards

Andy Ling


--
D. Richard Hipp
d...@sqlite.org<mailto:d...@sqlite.org>



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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard 
> Hipp
> Sent: 12 August 2014 15:46

> I put a new snapshot on the download page.  Please try it, *without* 
> SQLITE_ENABLE_LOCKING_MODE.

OK, it builds, but doesn't run.

It is missing the patch to unixDelete. Whilst vxWorks is POSIX compliant, for 
file I/O it is only
compliant if the underlying file system is. We are using dosFs, which isn't. 
This means the error
codes don't match. So we added the following.

static int unixDelete(
  sqlite3_vfs *NotUsed, /* VFS containing this as the xDelete method */
  const char *zPath,/* Name of file to be deleted */
  int dirSync   /* If true, fsync() directory after deleting file */
){
  int rc = SQLITE_OK;
  UNUSED_PARAMETER(NotUsed);
  SimulateIOError(return SQLITE_IOERR_DELETE);
  if( osUnlink(zPath)==(-1) ){
if( errno==ENOENT ){
  rc = SQLITE_IOERR_DELETE_NOENT;
#if OS_VXWORKS
}else if( errno==0x380003 ){ /* == S_dosFsLib_FILE_NOT_FOUND */
  rc = SQLITE_IOERR_DELETE_NOENT;
#endif
}else{
  rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
}

With this patch I have run a few simple commands. Created a table, added a few 
rows and listed them.

The compile options I'm using are -DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION

Regards

Andy Ling


-- 
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] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard 
> Hipp
> Sent: 12 August 2014 14:28
> To: Andy Ling
> On Tue, Aug 12, 2014 at 9:23 AM, Andy Ling <andy.l...@quantel.com> wrote:


>> I have been testing
>> the changes Jan has made and at the moment I am happy.


> But we cannot use Jan's changes directly because he lives in a jurisdiction 
> that does not
> recognize the ability of an author to contribute their work into the public 
> domain.  We can
> only use Jan's changes as a guideline for implementing our own changes.
> Do you have any issues with the current code on the tip of trunk?

The patches I applied to 3.8.5 are working OK.
I've lost track a bit of what code I'm running and what I need to download.
So I've just downloaded the latest pre-release 
sqlite-amalgamation-201408081749.zip
and applied the patches below. This builds, but I haven't had a chance to do any
testing yet. Hopefully this matches the changes Jan suggested.

Regards

Andy Ling


*** sqlite3.c   2014-08-08 13:50:06.0 +0100
--- sqlite3vxw.c2014-08-12 15:21:22.0 +0100
***
*** 24161,24167 
  #endif


! #if SQLITE_ENABLE_LOCKING_STYLE
  # include 
  # if OS_VXWORKS
  #  include 
--- 24161,24167 
  #endif


! #if SQLITE_ENABLE_LOCKING_STYLE || OS_VXWORKS
  # include 
  # if OS_VXWORKS
  #  include 
***
*** 24589,24595 
--- 24589,24599 
  ** we are not running as root.
  */
  static int posixFchown(int fd, uid_t uid, gid_t gid){
+ #if OS_VXWORKS
+   return 0;
+ #else
return geteuid() ? 0 : fchown(fd,uid,gid);
+ #endif
  }

  /* Forward reference */
***
*** 24645,24651 
{ "read", (sqlite3_syscall_ptr)read,   0  },
  #define osRead  ((ssize_t(*)(int,void*,size_t))aSyscall[8].pCurrent)

! #if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE
{ "pread",(sqlite3_syscall_ptr)pread,  0  },
  #else
{ "pread",(sqlite3_syscall_ptr)0,  0  },
--- 24649,24655 
{ "read", (sqlite3_syscall_ptr)read,   0  },
  #define osRead  ((ssize_t(*)(int,void*,size_t))aSyscall[8].pCurrent)

! #if defined(USE_PREAD) || (SQLITE_ENABLE_LOCKING_STYLE && !OS_VXWORKS)
{ "pread",(sqlite3_syscall_ptr)pread,  0  },
  #else
{ "pread",(sqlite3_syscall_ptr)0,  0  },
***
*** 24662,24668 
{ "write",(sqlite3_syscall_ptr)write,  0  },
  #define osWrite ((ssize_t(*)(int,const 
void*,size_t))aSyscall[11].pCurrent)

! #if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE
{ "pwrite",   (sqlite3_syscall_ptr)pwrite, 0  },
  #else
{ "pwrite",   (sqlite3_syscall_ptr)0,  0  },
--- 24666,24672 
{ "write",(sqlite3_syscall_ptr)write,  0  },
  #define osWrite ((ssize_t(*)(int,const 
void*,size_t))aSyscall[11].pCurrent)

! #if defined(USE_PREAD) || (SQLITE_ENABLE_LOCKING_STYLE && !OS_VXWORKS)
{ "pwrite",   (sqlite3_syscall_ptr)pwrite, 0  },
  #else
{ "pwrite",   (sqlite3_syscall_ptr)0,  0  },
***
*** 25564,25572 
--- 25568,25582 
  ** Return TRUE if pFile has been renamed or unlinked since it was first 
opened.
  */
  static int fileHasMoved(unixFile *pFile){
+ #if OS_VXWORKS
+   return pFile->pInode!=0 &&
+
+  pFile->pId!=pFile->pInode->fileId.pId;
+ #else
struct stat buf;
return pFile->pInode!=0 &&
   (osStat(pFile->zPath, )!=0 || 
buf.st_ino!=pFile->pInode->fileId.ino);
+ #endif
  }


***
*** 26709,26715 
/* Otherwise see if some other process holds it. */
if( !reserved ){
  sem_t *pSem = pFile->pInode->pSem;
- struct stat statBuf;

  if( sem_trywait(pSem)==-1 ){
int tErrno = errno;
--- 26719,26724 
***
*** 26762,26768 
  */
  static int semLock(sqlite3_file *id, int eFileLock) {
unixFile *pFile = (unixFile*)id;
-   int fd;
sem_t *pSem = pFile->pInode->pSem;
int rc = SQLITE_OK;

--- 26771,26776 
***
*** 29893,29902 
int isCreate = (flags & SQLITE_OPEN_CREATE);
int isReadonly   = (flags & SQLITE_OPEN_READONLY);
int isReadWrite  = (flags & SQLITE_OPEN_READWRITE);
! #if SQLITE_ENABLE_LOCKING_STYLE
int isAutoProxy  = (flags & SQLITE_OPEN_AUTOPROXY);
  #endif
! #if defined(__APPLE__) || SQLITE_ENABLE_LOCKING_STYLE
struct statfs fsInfo;
  #endif

--- 29901,29910 
int isCreate = (flags & SQLITE_OPEN_CREATE);
int isReadonly   = (flags & SQLITE_OPEN_READONLY);
int isReadWrite  = (flags & SQLITE_OPEN_READWRITE);
! #if SQLITE_ENABLE_LOCKING_STYLE && !OS_VXWORKS
int isAutoProxy  = (flags & SQLITE_OPEN_AU

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp
Sent: 12 August 2014 13:27

> This is very awkward working through a proxy.  If Mr. Ling (or anybody else 
> with access
>  to a VxWorks development system) wants VxWorks support in SQLite, he can 
> come to
>  this mailing list himself and submit suggestions here, where we can ask 
> questions. 
>  No more VxWorks changes without direct communication with actual VxWorks 
> developers.

I am on the list and that was where I made my first requests. As vxWorks has a 
fairly limited
audience, Jan suggested we take it off-list to finalise the required patches. I 
have been testing
the changes Jan has made and at the moment I am happy.

> The first question I have:  Why do VxWorks developers think they also need
>  SQLITE_ENABLE_LOCKING_STYLE support?

I don't. At some point I think someone suggested turning it on to see if it 
helped fix my
compilation problems (one of the errors I had related to semaphores). So I 
reported the
errors I got with it turned on.

If I have any more problems I'll be sure to ask on the list, although responses 
to vxWorks
issues are pretty small :^)

Thanks for all the help so far

Regards

Andy Ling


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

Re: [sqlite] SQLite version 3.8.6 coming soon

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: 06 August 2014 01:58
> To: General Discussion of SQLite Database; sqlite-dev
> Subject: [sqlite] SQLite version 3.8.6 coming soon
> 
> Version 3.8.6 was originally scheduled for September.  But the change log (
> http://www.sqlite.org/draft/releaselog/current.html) is getting rather long
> and there are a number of important bug fixes.  So we might try to get
> 3.8.6 out the door sooner rather than later.
> 
> Please test!  Recent amalgamations are available on the download page (
> http://www.sqlite.org/download.html).  Please compile your applications
> use
> the latest tip of trunk and let us know if you encounter any problems.
> 

The amalgamation I've just downloaded (201408060029) doesn't appear to have the 
changes
I did with Jan to make it compile under vxWorks.

Regards

Andy Ling

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

Re: [sqlite] HELP sqlite3 used in vxworks has someproblem？

[Andy Ling]

> -Original Message-
> 
> 2014-08-02 16:00 GMT+02:00 王庆刚 <2004wqg2...@163.com>:
> > hi ， Can Sqlite3.c and sqlite.h be compiled in Workbench3.2 for
> Vxworks6.8 ?
> > When I  compile them , there have so many problems .
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> You can find the necessary changes here:
> <http://fossil-
> scm.org/index.html/vpatch?from=dd5743a8239d1ce9=b68f65bb69a098a
> 1>
> thanks to Andy Ling.
> 

Yes, I build it in Workbench 3.3 and vxWorks 6.9 as part of a RTP. I use the 
compile flags

-DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION

You can probably make the load extension work if you need it. It requires 
linking to the dynamic linking library.

Regards

Andy Ling


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

Re: [sqlite] Building for vxWorks

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Eduardo Morras
> Sent: 08 July 2014 10:19


> On Mon, 7 Jul 2014 12:44:54 +0000
> Andy Ling <andy.l...@quantel.com> wrote:
> 
> > Building the original file with the flags.
> >
> > -DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION
> > -DSQLITE_ENABLE_LOCKING_STYLE
> >
> > I get the following errors.
> >
> > sqlite3.c: In function 'posixFchown':
> > sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
> > sqlite3.c:24249: warning: implicit declaration of function 'fchown'
> > sqlite3.c: At top level:
> > sqlite3.c:24306: error: 'pread' undeclared here (not in a function)
> 
> 
> Are you trying to use -DSQLITE_OMIT* with the amalgamation sqlite3.c or
> with the full sqlite3 src? Most of -DSQLITE_OMIT* won't work if you use the
> amalgamation.
> 

I'm using the amalgamation. I seemed to need the  SQLITE_OMIT_LOAD_EXTENSION to 
get close to a build that works. I've just tried removing all compile options 
and with the patched amalgamation I get..

sqlite3.c: In function 'posixFchown':
sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
sqlite3.c:24249: warning: implicit declaration of function 'fchown'
sqlite3.c: At top level:
sqlite3.c:24996: error: expected specifier-qualifier-list before 'sem_t'
sqlite3.c: In function 'semCheckReservedLock':
sqlite3.c:26377: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26377: error: (Each undeclared identifier is reported only once
sqlite3.c:26377: error: for each function it appears in.)
sqlite3.c:26377: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26377: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:26379: warning: implicit declaration of function 'sem_trywait'
sqlite3.c:26390: warning: implicit declaration of function 'sem_post'
sqlite3.c: In function 'semLock':
sqlite3.c:26430: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26430: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26430: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c: In function 'semUnlock':
sqlite3.c:26463: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26463: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26463: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c: In function 'fillInUnixFile':
sqlite3.c:29263: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29264: error: 'unixInodeInfo' has no member named 'aSemName'
sqlite3.c:29270: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29270: warning: implicit declaration of function 'sem_open'
sqlite3.c:29271: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29271: error: 'SEM_FAILED' undeclared (first use in this function)
sqlite3.c:29273: error: 'unixInodeInfo' has no member named 'aSemName'

It may be I needed the OMIT to get the link to work, once the above has been 
fixed I'll let you know.

Most of this can be fixed by adding a #include . So by making the 
change..

#if SQLITE_ENABLE_LOCKING_STYLE
# include 
# if OS_VXWORKS
#  include 
#  include 
# else
#  include 
#  include 
# endif
#else
# if OS_VXWORKS
#  include 
# endif
#endif /* SQLITE_ENABLE_LOCKING_STYLE */

I now just get

sqlite3.c: In function 'posixFchown':
sqlite3.c:24253: warning: implicit declaration of function 'geteuid'
sqlite3.c:24253: warning: implicit declaration of function 'fchown'

Trying to link this I get the following unresolved symbols...

In function `posixFchown':
undefined reference to `geteuid'
In function `dotlockLock':
undefined reference to `utimes'
In function `unixDlError':
undefined reference to `dlerror'
In function `unixDlSym':
undefined reference to `dlsym'
In function `posixFchown':
undefined reference to `fchown'
In function `unixDlClose':
undefined reference to `dlclose'
In function `unixDlOpen':
undefined reference to `dlopen'

Adding the -DHAVE_UTIME option fixes the utimes reference
Adding the -DSQLITE_OMIT_LOAD_EXTENSION fixes the dl* references, which just 
leaves geteuid & fchown.

Regards

Andy Ling

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

Re: [sqlite] Building for vxWorks

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jan Nijtmans
> Sent: 07 July 2014 20:14


> 2014-07-07 14:44 GMT+02:00 Andy Ling <andy.l...@quantel.com>:
> > Removing the SQLITE_ENABLE_LOCKING_STYLE I get
> ...
> > sqlite3.c: In function 'fileHasMoved':
> > sqlite3.c:25236: error: 'struct unixFileId' has no member named 'ino'
> > sqlite3.c: In function 'semCheckReservedLock':
> ...
> > sqlite3.c:26372: warning: unused variable 'statBuf'
> > sqlite3.c: In function 'semLock':
> ...
> > sqlite3.c:26425: warning: unused variable 'fd'
> 
> Below my suggested patch (derived from todays trunk)
> which should solve the above 2 warnings and the
> error. All other warnings/errors seems
> to derive from the missing  and maybe
> other header files ( ???)
> 

OK, I've made those changes and with

-DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION

I get 

sqlite3.c: In function 'posixFchown':
sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
sqlite3.c:24249: warning: implicit declaration of function 'fchown'
sqlite3.c: At top level:
sqlite3.c:24996: error: expected specifier-qualifier-list before 'sem_t'
sqlite3.c: In function 'semCheckReservedLock':
sqlite3.c:26377: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26377: error: (Each undeclared identifier is reported only once
sqlite3.c:26377: error: for each function it appears in.)
sqlite3.c:26377: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26377: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:26379: warning: implicit declaration of function 'sem_trywait'
sqlite3.c:26390: warning: implicit declaration of function 'sem_post'
sqlite3.c: In function 'semLock':
sqlite3.c:26430: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26430: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26430: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c: In function 'semUnlock':
sqlite3.c:26463: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26463: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26463: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c: In function 'fillInUnixFile':
sqlite3.c:29263: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29264: error: 'unixInodeInfo' has no member named 'aSemName'
sqlite3.c:29270: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29270: warning: implicit declaration of function 'sem_open'
sqlite3.c:29271: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29271: error: 'SEM_FAILED' undeclared (first use in this function)
sqlite3.c:29273: error: 'unixInodeInfo' has no member named 'aSemName'

If I add -DSQLITE_ENABLE_LOCKING_STYLE I get

sqlite3.c: In function 'posixFchown':
sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
sqlite3.c:24249: warning: implicit declaration of function 'fchown'
sqlite3.c: At top level:
sqlite3.c:24306: error: 'pread' undeclared here (not in a function)
sqlite3.c:24323: error: 'pwrite' undeclared here (not in a function)
sqlite3.c: In function 'unixOpen':
sqlite3.c:29561: error: storage size of 'fsInfo' isn't known
sqlite3.c:29721: warning: implicit declaration of function 'fstatfs'
sqlite3.c:29726: error: request for member 'f_fstypename' in something not a 
structure or union
sqlite3.c:29726: warning: passing argument 2 of 'strncmp' from incompatible 
pointer type
sqlite3.c:29751: warning: implicit declaration of function 'statfs'
sqlite3.c:29764: error: request for member 'f_flags' in something not a 
structure or union
sqlite3.c:29764: error: 'MNT_LOCAL' undeclared (first use in this function)
sqlite3.c:29764: error: (Each undeclared identifier is reported only once
sqlite3.c:29764: error: for each function it appears in.)
sqlite3.c:29764: error: invalid operands to binary & (have 'struct unix_syscall 
*' and 'struct unix_syscall *')
sqlite3.c:29769: warning: implicit declaration of function 
'proxyTransformUnixFile'
sqlite3.c:29561: warning: unused variable 'fsInfo'

This is only the compile. Once this is sorted there is linking to fix.

> I don't have VxWorks, just tested this on Linux using
> -DOS_VXWORKS=1, as suggested here:
> 
> 

I'm happy to try any patches. I obviously do have vxWorks.

As a slight aside. Some of the compile options seem necessary to get a basic 
build. I wonder if these should be set by default for vxWorks.

Regards

Andy Ling

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

Re: [sqlite] Building for vxWorks

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jan Nijtmans
> Sent: 07 July 2014 12:24
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Building for vxWorks
> 
> 2014-07-04 11:52 GMT+02:00 Andy Ling <andy.l...@quantel.com>:
> > I now have sqlite running under vxWorks. As nobody offered any
> suggestions, I thought I would share my experience in the hope it will help
> others and maybe feed some changes back into the main code.
> 
> > semaphone.h is included if SQLITE_ENABLE_LOCKING_STYLE is defined, but
> I
> > couldn't get it to build with that set.
> 
> The first logical question is: Why you couldn't get it to build with
> that set? What exact error-message did you get? I think that's
> the first thing that should be fixed before trying other
> refinements.
> 

Building the original file with the flags.

-DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_ENABLE_LOCKING_STYLE

I get the following errors.

sqlite3.c: In function 'posixFchown':
sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
sqlite3.c:24249: warning: implicit declaration of function 'fchown'
sqlite3.c: At top level:
sqlite3.c:24306: error: 'pread' undeclared here (not in a function)
sqlite3.c:24323: error: 'pwrite' undeclared here (not in a function)
sqlite3.c: In function 'fileHasMoved':
sqlite3.c:25236: error: 'struct unixFileId' has no member named 'ino'
sqlite3.c:25236: warning: comparison between pointer and integer
sqlite3.c: In function 'semCheckReservedLock':
sqlite3.c:26372: warning: unused variable 'statBuf'
sqlite3.c: In function 'semLock':
sqlite3.c:26425: warning: unused variable 'fd'
sqlite3.c: In function 'unixOpen':
sqlite3.c:29557: error: storage size of 'fsInfo' isn't known
sqlite3.c:29717: warning: implicit declaration of function 'fstatfs'
sqlite3.c:29722: error: request for member 'f_fstypename' in something not a 
structure or union
sqlite3.c:29722: warning: passing argument 2 of 'strncmp' from incompatible 
pointer type
sqlite3.c:29747: warning: implicit declaration of function 'statfs'
sqlite3.c:29760: error: request for member 'f_flags' in something not a 
structure or union
sqlite3.c:29760: error: 'MNT_LOCAL' undeclared (first use in this function)
sqlite3.c:29760: error: (Each undeclared identifier is reported only once
sqlite3.c:29760: error: for each function it appears in.)
sqlite3.c:29760: error: invalid operands to binary & (have 'struct unix_syscall 
*' and 'struct unix_syscall *')
sqlite3.c:29765: warning: implicit declaration of function 
'proxyTransformUnixFile'
sqlite3.c:29557: warning: unused variable 'fsInfo'

Removing the SQLITE_ENABLE_LOCKING_STYLE I get

sqlite3.c: In function 'posixFchown':
sqlite3.c:24249: warning: implicit declaration of function 'geteuid'
sqlite3.c:24249: warning: implicit declaration of function 'fchown'
sqlite3.c: At top level:
sqlite3.c:24996: error: expected specifier-qualifier-list before 'sem_t'
sqlite3.c: In function 'fileHasMoved':
sqlite3.c:25236: error: 'struct unixFileId' has no member named 'ino'
sqlite3.c: In function 'semCheckReservedLock':
sqlite3.c:26371: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26371: error: (Each undeclared identifier is reported only once
sqlite3.c:26371: error: for each function it appears in.)
sqlite3.c:26371: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26371: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:26374: warning: implicit declaration of function 'sem_trywait'
sqlite3.c:26385: warning: implicit declaration of function 'sem_post'
sqlite3.c:26372: warning: unused variable 'statBuf'
sqlite3.c: In function 'semLock':
sqlite3.c:26426: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26426: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26426: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:26425: warning: unused variable 'fd'
sqlite3.c: In function 'semUnlock':
sqlite3.c:26459: error: 'sem_t' undeclared (first use in this function)
sqlite3.c:26459: error: 'pSem' undeclared (first use in this function)
sqlite3.c:26459: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c: In function 'fillInUnixFile':
sqlite3.c:29259: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29260: error: 'unixInodeInfo' has no member named 'aSemName'
sqlite3.c:29266: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29266: warning: implicit declaration of function 'sem_open'
sqlite3.c:29267: error: 'unixInodeInfo' has no member named 'pSem'
sqlite3.c:29267: error: 'SEM_FAILED' undeclared (first use in this function)
sqlite3.c:29269: error: 'unixInodeInfo' has no member named 'aSemName'

Regards

Andy Ling

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

Re: [sqlite] Building for vxWorks

[Andy Ling]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Andy Ling
> Sent: 27 June 2014 10:27
> To: 'sqlite-users@sqlite.org'
> Subject: [sqlite] Building for vxWorks
> 

I now have sqlite running under vxWorks. As nobody offered any suggestions, I 
thought I would share my experience in the hope it will help others and maybe 
feed some changes back into the main code.

> I am trying to build sqlite 3.8.5 for vxWorks 6.9 and I'm after a bit of help.
> 
> To be more precise, I'm building an RTP for 64bit NEHALEM on vxWorks 6.9.3
> 
> Straight out of the box it won't build, I get the following errors
> 
> sqlite3.c:24997: error: expected specifier-qualifier-list before 'sem_t'
> sqlite3.c: In function 'fileHasMoved':
> sqlite3.c:25237: error: 'struct unixFileId' has no member named 'ino'
> sqlite3.c: In function 'semCheckReservedLock':
> 
> The first I have fixed by adding a #include 

semaphone.h is included if SQLITE_ENABLE_LOCKING_STYLE is defined, but I 
couldn't get it to build with that set and it is required for the unixInodeInfo 
structure so just before this structure definition I added

#if OS_VXWORKS
#  include 
#endif

> and the second
> I've fixed by bodging fileHasMoved to always return false.

I'm not sure what the "right" way is to fix this, but in our particular system 
I don't think any files will get moved. So I have changed the function to look 
like

static int fileHasMoved(unixFile *pFile){
 #ifdef OS_VXWORKS
  return 0 ;
#else
  struct stat buf;
  return pFile->pInode!=0 &&
 (osStat(pFile->zPath, )!=0 || 
buf.st_ino!=pFile->pInode->fileId.ino);
#endif
}

> It then builds, but has a few unresolved symbols. These I have fixed by
> adding the compile options
> 
> -DHAVE_UTIME
> -DSQLITE_OMIT_LOAD_EXTENSION
> 

I also had to bodge posixFchown. vxWorks doesn't support geteuid so I changed 
this function to..

static int posixFchown(int fd, uid_t uid, gid_t gid){
#if OS_VXWORKS
  return 0 ;
#else
  return geteuid() ? 0 : fchown(fd,uid,gid);
#endif
}

> It now runs, but anything that tries to modify a database file generates a 
> Disk
> I/O error.
> 

After a bit of faffing I eventually found this was a problem with unlink. 
Sqlite was trying to delete a temporary file using unlink. If the error from 
unlink was ENOENT it ignored it assuming the file had been deleted (or not 
created).

vxWorks only supports the POSIX error codes if you are using a POSIX compliant 
filing system. We're using dosFs, which isn't. So the error returned by unlink 
wasn't ENOENT.

I have fixed this by adding a new compile option "USING_DOSFS" and changed 
delete to..

static int unixDelete(
  sqlite3_vfs *NotUsed, /* VFS containing this as the xDelete method */
  const char *zPath,/* Name of file to be deleted */
  int dirSync   /* If true, fsync() directory after deleting file */
){
  int rc = SQLITE_OK;
  UNUSED_PARAMETER(NotUsed);
  SimulateIOError(return SQLITE_IOERR_DELETE);
  if( osUnlink(zPath)==(-1) ){
#if OS_VXWORKS && USING_DOSFS
if ( errno == S_dosFsLib_FILE_NOT_FOUND )
#else
if( errno==ENOENT )
#endif
  {
  rc = SQLITE_IOERR_DELETE_NOENT;
}else{

This also requires the dosFsLib.h file being included and there seems to be 
some incompatibilities somewhere which mean it didn't quite work. I didn't have 
the time to investigate properly so added this bodge..

#if OS_VXWORKS && USING_DOSFS
/* copied from vwModNum.h */
#  define M_dosFsLib  (56 << 16)
#  define IMPORT extern
#  include 
#endif

My compile options are now

-DHAVE_UTIME -DSQLITE_OMIT_LOAD_EXTENSION -DUSING_DOSFS

The next problem I encountered was running sqlite commands from the vxWorks 
shell. Invariably they crashed in some way. This was because the stack is too 
small in the shell. So making the commands spawn a thread with a bigger stack 
(64K was enough) fixed this.

So I now have enough running to start doing some real work.

Regards

Andy Ling

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

[sqlite] Building for vxWorks

[Andy Ling]

Hi,

I am trying to build sqlite 3.8.5 for vxWorks 6.9 and I'm after a bit of help.

To be more precise, I'm building an RTP for 64bit NEHALEM on vxWorks 6.9.3

Straight out of the box it won't build, I get the following errors

sqlite3.c:24997: error: expected specifier-qualifier-list before 'sem_t'
sqlite3.c: In function 'fileHasMoved':
sqlite3.c:25237: error: 'struct unixFileId' has no member named 'ino'
sqlite3.c: In function 'semCheckReservedLock':

The first I have fixed by adding a #include  and the second I've 
fixed by bodging fileHasMoved to always return false.
Is there a compile option I should have used that would have fixed these?

It then builds, but has a few unresolved symbols. These I have fixed by adding 
the compile options

-DHAVE_UTIME
-DSQLITE_OMIT_LOAD_EXTENSION

It now runs, but anything that tries to modify a database file generates a Disk 
I/O error.

It lets me create a database and a file gets created on disk. I can do simple 
stuff like

SELECT date('now')

And that returns sensible stuff. So lots of it is working. But as soon as I try 
to create a table I get a disk I/O error. So I can't get much further.

Does anyone have any ideas about what I need to do to make this work.

Thanks

Andy Ling


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