[sqlite] Northwind example database

2010-03-27 Thread GeoffW

Hi

Just for educational purposes I have been experimenting a little with the
Northwind Sqlite database contained on the sqlite official site. 
Download link: http://download.vive.net/Northwind.zip.

Am I misunderstanding here or are the dates in the wrong format for sqlite
within this converted database ?

Assuming it is wrong and not my understsanding, are there any easy ways to
get the dates reversed and corrected to sqlite order and written back out to
the database ?

Thanks

Geoff
-- 
View this message in context: 
http://old.nabble.com/Northwind-example-database-tp28051470p28051470.html
Sent from the SQLite mailing list archive at Nabble.com.

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


[sqlite] Porting Sqlite to MQX OS: Question 3

2010-03-22 Thread GeoffW

Hello

I am still working away at this porting exercise, I have a couple of general
questions please that I would value some comments on.

The absence of a file locking mechanism in MQX means the best I can do is a
"dot lock" type locking strategy. I have implemented this now, but what is
concerning me is the amount of extra file opening and closing it is creating
in addition to the actual accessing of the database. In my embedded system I
am using NOR flash which has a limited write cycle life. I dont really have
a good feel as to what amount of extra flash write overhead I am causing, is
there an easy way to roughly estimate this ?

If this really is an issue then I have no option other than to have no file
locking. This might be acceptable in our typical application, I just need to
make sure I understand the limitations.

Lets say for the sake of an example, I have an Application made up of 10
tasks. Task 1 and only task 1 can access a customer.db database file. Task 2
and only task 2 can access say a fault logging database fault.db. 

Are these 2 statement correct for the above situation ?

1) This will work fine with no file locking.

2) I must make sure that I have implemented the mutex system to allow me to
define SQLITE_THREADSAFE.  

Just wanted to sanity check my understanding of the concepts here.

Thanks Geoff









-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-OS%3A-Question-3-tp27985475p27985475.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-16 Thread GeoffW

Hello Dan


>Do the tasks share a heap? And do you have multiple connections
>to the database (multiple calls to sqlite3_open() or sqlite3_open_v2().

I would like to keep the implementation as general purpose as possible so we
can use sqlite in the future in differient Applications. Therefore I will
say I need to have multiple connections to the database. Each of the tasks
in the Application, have their own task stack, but mallocs() for example
will all take blocks from a common memory pool. Not too sure of how this
effects my locking strategy implementation.

To be honest, despite having read the locking bits of osUnix.c numerous
times, I am still pretty much lost.

Regards Geoff









Dan Kennedy-4 wrote:
> 
> 
> On Mar 16, 2010, at 5:22 AM, GeoffW wrote:
> 
>>
>> Hello Dan
>>
>> Thanks for your useful input. To answer your questions.
>>
>>> Do you have any file-locking primitives provided by the OS?
>> There are no file locking OS Primitives at all that I can use.
>>
>>> Do you have clients connecting to the database from multiple
>>> processes? Or only multiple threads within the same process?
>>
>> My Application doesnt have a concept of processes and threads as you  
>> might
>> find in Windows.
>> Think of it as a  small data collection embedded Application. The  
>> platform
>> has essentially 1 fixed Application running, made up of say 10  
>> different
>> concurrent tasks.
> 
> Do the tasks share a heap? And do you have multiple connections
> to the database (multiple calls to sqlite3_open() or sqlite3_open_v2().
> 
> If you only have one connection, then you don't need any locking. Have
> xCheckReservedLock() set its result variable to 0 for all calls.
> 
> If all tasks share a heap, maybe you can implement locking in-memory
> using global variables in the VFS layer. Or, if you strictly use
> shared-cache mode, you will not need any locking.
> 
> Dan.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-OS%3A-Question-2-tp27874124p27918689.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread GeoffW

Hello Dan

Thanks for your useful input. To answer your questions.

>Do you have any file-locking primitives provided by the OS?
There are no file locking OS Primitives at all that I can use.

>Do you have clients connecting to the database from multiple
>processes? Or only multiple threads within the same process?

My Application doesnt have a concept of processes and threads as you might
find in Windows.
Think of it as a  small data collection embedded Application. The platform
has essentially 1 fixed Application running, made up of say 10 different
concurrent tasks. I would like to structure the program  so that any of the
tasks can safely access the database. The frequency of reading or writing to
the database will be fairly low, so performance isnt a prime consideration.
However integrity of the database is vital, I dont want to risk corruption
due to a broken or misunderstood file locking implementation.

Your message on first reading seemed to imply that the internal lockstate
variable  (NONE ,PENDING ,RESERVED, EXCLUSIVE etc) that Sqlite keeps on each
db file access is enough to make the locking safe, without needing an OS
lock/unlock primitive? I suspect I am mis-interpreting your comments here
though ?

Thanks for any further clarification on how to implement this.

Regards Geoff





Dan Kennedy-4 wrote:
> 
> 
> On Mar 15, 2010, at 10:04 PM, GeoffW wrote:
> 
>>
>> Hi Kees
>>
>> Thanks for the response and giving me a clue to look at dot locking,  
>> I had
>> missed the dot lock mechanism as I had concentrated mainly on the  
>> osWin.c
>> file. I cant find any documentation other than the source code on  
>> how this
>> low level file locking stuff is implemented.
>>
>> I am currently reading through the code to see if I can figure it  
>> out. The
>> unix.c module is horrendously complicated so I am struggling to make  
>> sense
>> of it.
> 
> It is more complicated. But the comments in it are often better than
> those in os_win.c. See the comments above unixLock(), at or around line
> 1208 of os_unix.c. Also here:
> 
>http://www.sqlite.org/lockingv3.html
> 
>> In simple terms I was thinking that when the db file is opened for  
>> writing,
>> I could use the filename with ".lock" appended, and open that new  
>> dummy
>> file, which is then used to determine if I have a lock on the original
>> database file or not ?
> 
> You can implement locking that way. But it would be a last
> resort. And is the sort of trick that could be prone to race
> conditions.
> 
> Do you have any file-locking primitives provided by the OS?
> 
> Do you have clients connecting to the database from multiple
> processes? Or only multiple threads within the same process?
> 
>> As the code says for dot locking there is really only a 2 state lock,
>> exclusive or no lock. I got lost by the complexity of the code, for  
>> vxworks
>> it seems to keep linked lists of filenames, not sure why I would  
>> need to do
>> that, also it does lots of other stuff that I didnt really  
>> understand for
>> example updating the file timestamp on the ".lock" file.
> 
> 2 locking states are all SQLite needs to work. An exclusive lock
> for writers and a shared lock for readers. The other locks are
> only required to increase concurrencly between readers and the
> single writer.
> 
> If you handle RESERVED and PENDING locks in the same way as EXCLUSIVE
> locks, things will work fine, just you lose a little concurrency.
> 
> 
> 
> 
>> I was hoping Dr Hipp could manage to find a few minutes to write  
>> some notes
>> explaining what needs to be implemented re: dot locking and what can  
>> be
>> simplified and ignored.
>>
>> Thanks if anyone can fill in some details here.
>>
>> Geoff
>>
>>
>>
>> Kees Nuyt wrote:
>>>
>>> On Fri, 12 Mar 2010 02:22:31 -0800 (PST), GeoffW
>>> <geoffw...@hotmail.com> wrote:
>>>
>>>>
>>>> Hi
>>>>
>>>> Now I have got a half ported version of Sqlite up and running on  
>>>> MQX I
>> cant
>>>> put off any longer the question of file locking and how I tackle  
>>>> that ?
>> For
>>>> the moment i had made the xLock() and xUnlock() the same as in  
>>>> osWin.c
>>>> version but with the Windows lock() and unlock() function calls  
>>>> commented
>>>> out.
>>>>
>>>> The problem I have is that MQX's file system is just ANSI C, and  
>>>> it lacks
>>>> any lock/unloc

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread GeoffW

Hi Kees

Thanks for the response and giving me a clue to look at dot locking, I had
missed the dot lock mechanism as I had concentrated mainly on the osWin.c
file. I cant find any documentation other than the source code on how this
low level file locking stuff is implemented.

I am currently reading through the code to see if I can figure it out. The
unix.c module is horrendously complicated so I am struggling to make sense
of it. 

In simple terms I was thinking that when the db file is opened for writing,
I could use the filename with ".lock" appended, and open that new dummy
file, which is then used to determine if I have a lock on the original
database file or not ?
As the code says for dot locking there is really only a 2 state lock,
exclusive or no lock. I got lost by the complexity of the code, for vxworks
it seems to keep linked lists of filenames, not sure why I would need to do
that, also it does lots of other stuff that I didnt really understand for
example updating the file timestamp on the ".lock" file.

I was hoping Dr Hipp could manage to find a few minutes to write some notes
explaining what needs to be implemented re: dot locking and what can be
simplified and ignored.

Thanks if anyone can fill in some details here.

Geoff



Kees Nuyt wrote:
> 
> On Fri, 12 Mar 2010 02:22:31 -0800 (PST), GeoffW
> <geoffw...@hotmail.com> wrote:
> 
>>
>>Hi
>>
>>Now I have got a half ported version of Sqlite up and running on MQX I
cant
>>put off any longer the question of file locking and how I tackle that ?
For
>>the moment i had made the xLock() and xUnlock() the same as in osWin.c
>>version but with the Windows lock() and unlock() function calls commented
>>out.
>>
>>The problem I have is that MQX's file system is just ANSI C, and it lacks
>>any lock/unlock functions.
>>
>>As Sqlite has been ported to so many diverse operating systems I am hoping
>>that this question of what to do for an ANSI only File system has already
>>been hit and a workaround defined ?
>>
>>I am pretty hazy on this topic of file locking so I would appreciate some
>>basic discussion and guidance. It might help if I explain my application a
>>little. It is an embedded system where we are just running one
application,
>>but it is multithreaded. For my initial porting work I have made
>>SQLITE_THREADSAFE = 0 (so I probably dont even need a file lock in the
>>configuration I have at present ?)
>>However I would of course like to make SQLITE_THREADSAFE = 1 so that
>>multiple tasks can safely access a database.
> 
> (Disclaimer: this is not my specialism, I hope it helps
> nevertheless)
> 
> SQLITE_THREADSAFE has effect on threads within the same
> process (=task), especially when they use the same
> connection. You need file locks for synchronisation between
> different processes. If the filesystem doesn't provide them,
> you could use the dot lockfile paradigm, which seems to be
> associated with SQLITE_ENABLE_LOCKING_STYLE.
> 
>>Thanks for any guidance
>>
>>Regards Geoff
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-OS%3A-Question-2-tp27874124p27905578.html
Sent from the SQLite mailing list archive at Nabble.com.

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


[sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-12 Thread GeoffW

Hi

Now I have got a half ported version of Sqlite up and running on MQX I cant
put off any longer the question of file locking and how I tackle that ? For
the moment i had made the xLock() and xUnlock() the same as in osWin.c
version but with the Windows lock() and unlock() function calls commented
out.

The problem I have is that MQX's file system is just ANSI C, and it lacks
any lock/unlock functions.

As Sqlite has been ported to so many diverse operating systems I am hoping
that this question of what to do for an ANSI only File system has already
been hit and a workaround defined ?

I am pretty hazy on this topic of file locking so I would appreciate some
basic discussion and guidance. It might help if I explain my application a
little. It is an embedded system where we are just running one application,
but it is multithreaded. For my initial porting work I have made
SQLITE_THREADSAFE = 0 (so I probably dont even need a file lock in the
configuration I have at present ?)
However I would of course like to make SQLITE_THREADSAFE = 1 so that
multiple tasks can safely access a database.

Thanks for any guidance

Regards Geoff




-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-OS%3A-Question-2-tp27874124p27874124.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Porting Sqlite to MQX Operating system

2010-03-12 Thread GeoffW

hello 

I thought I had better update this and confess to my sins. Good job no one
is reading this thread as it is an embarassingly stupid mistake :rules:

I cant believe I did this and then took so long to spot it.

int sqlite3_os_init(void)
{
static sqlite3_vfs mqxVfs = {
1, /* iVersion */
0,   /* szOsFile ->ARRRGGGH <*/
MAX_PATH,  /* mxPathname */
etc 

I started creating the ported file from osWin.c, I had quite a struggle
getting it to compile initially using CodeWarrior, so I was making several
temporary hacks to allow it to compile, which I then revisited later to
correct. I had replaced the sizeof(winFile) with a zero, and then forgot to
change it to sizeof(mqxFile).
This caused a fun crash down in the bowels of the paging code.

Once I corrected this one liner it fixed the crash and sqlite is basically
up and running now. I still have a fair bit of work left to implement some
interface functions which are just dummy stubs at present, but I am making
progress now.

Regards Geoff




-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p27874110.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Porting Sqlite to MQX Operating system

2010-03-08 Thread GeoffW

Hi 

I wasted another entire day on this without really being able to grasp what
the problem is.

The call sequence is for the sqlite3OsClose() function

Working Windows version

pager_unlock() pPager->jfpd where pMethods =0
releaseAllSavepoints() pPager->sjfpd where pMethods =0
above 2 lines repeat 3 times 

then

sqlite3PagerClose() pPager->fd where pMethods  <> 0  hence a real file close
of the test.db file occurs


MQX faulty version

pager_unlock() pPager->jfpd where pMethods = 0
pager_unlock() pPager->jfpd where pMethods = 0

releaseAllSavepoints() pPager->sjfpd where pMethods <> 0

This call to releaseAllSavepoints has the pMethods pointer pointing at the
sqlite3_io_methods mqxIoMethod structure, so a  call to mqxClose() occurs
and a the db file is erroneously closed.

Any ideas on this would be greatly appreciated. Thanks

Geoff







GeoffW wrote:
> 
> hello Dan 
> 
> Thanks for the very speedy reply. I have just been experimenting with the
> working Windows version.
> 
> I only get one hit of the winOpen() function and that is with a filename
> parameter of the full path to my test.db file
> 
> So maybe it is not a temporary journal file or maybe I am misunderstanding
> what you a saying ?
> 
> 
> The only other thing I have noticed since my initial post is that even for
> the working Windows version I am getting 7 calls to the sqlite3OsClose()
> function.  The actual close of the file is prevented by the line if(
> pId->pMethods ). As that is NULL, it doesnt close the file except for the
> very last 7th time it is called where pMethods is not NULL.   I didnt
> expect it to do this on the windows version, seems odd to me.
> 
> On MQX the 2nd call to sqlite3OsClose() has pId->pMethods != NULL so an
> actual close occurs.
> 
> Geoff
> 
> 
> 
> 
> 
> Dan Kennedy-4 wrote:
>> 
>>>
>>> http://old.nabble.com/file/p27792715/stack.jpg
>>>
>>> I know its a long shot but does Dr Hipp or anyone have any possible  
>>> ideas
>>> what could give rise to a premature file close ?  Sorry my message  
>>> was so
>>> long. Thanks for any ideas
>> 
>> SQLite thinks it is closing the statement journal here, not
>> the db file. This file should have been opened with the pathname
>> argument to the xOpen call set to NULL.
>> 
>> Dan.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p27823835.html
Sent from the SQLite mailing list archive at Nabble.com.

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


Re: [sqlite] Porting Sqlite to MQX Operating system

2010-03-05 Thread GeoffW

hello Dan 

Thanks for the very speedy reply. I have just been experimenting with the
working Windows version.

I only get one hit of the winOpen() function and that is with a filename
parameter of the full path to my test.db file

So maybe it is not a temporary journal file or maybe I am misunderstanding
what you a saying ?


The only other thing I have noticed since my initial post is that even for
the working Windows version I am getting 7 calls to the sqlite3OsClose()
function.  The actual close of the file is prevented by the line if(
pId->pMethods ). As that is NULL, it doesnt close the file except for the
very last 7th time it is called where pMethods is not NULL.   I didnt expect
it to do this on the windows version, seems odd to me.

On MQX the 2nd call to sqlite3OsClose() has pId->pMethods != NULL so an
actual close occurs.

Geoff





Dan Kennedy-4 wrote:
> 
>>
>> http://old.nabble.com/file/p27792715/stack.jpg
>>
>> I know its a long shot but does Dr Hipp or anyone have any possible  
>> ideas
>> what could give rise to a premature file close ?  Sorry my message  
>> was so
>> long. Thanks for any ideas
> 
> SQLite thinks it is closing the statement journal here, not
> the db file. This file should have been opened with the pathname
> argument to the xOpen call set to NULL.
> 
> Dan.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p27793173.html
Sent from the SQLite mailing list archive at Nabble.com.

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


[sqlite] Porting Sqlite to MQX Operating system

2010-03-05 Thread GeoffW

hello

MQX is an Operating system from Freescale available for a number of their
Mid range processors. I have started the task of porting Sqlite to run on a
Coldfire V4. MQX is not as full featured as the Windows, Unix etc ports, so
it is going to be a challenge. If I ever manage to get this to work, I will
put the code in the public domain and write up some notes on how I did it
and problems I hit. The Sqlite doumentation web pages are great for running
code on a Windows OS, but are a bit light on detail for folks that are
trying to port to other OS's particularly what I call the mid-range OS's E.G 
ThreadX, Nucleus, MQX etc

I have so far implemented the memory handling functions for MQX, and the
majority of the file operation functions so I thought I would try some
initial debugging with a simple externally created database running
sqlite3_exec() and "SELECT * from tbl1"  (non threadsafe is defined to
simplify the problem)

Not totally unexpectedly but it is currently crashing when I run it, and I
cant see what is wrong.

I have added some debug printfs to both my Windows version and my MQX
version
Windows version gives me

File opened OK
Read 100  Got 100
sqlite3_exec called
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0
Read 1024  Got 1024
sqlite3_exec called
sqlite3_step returns 100  callbackinit = 0
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0
sqlite3_step returns 100  callbackinit = 1
sqlite3_step returns 101  callbackinit = 1
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0
Read 16  Got 16
Read 1024  Got 1024
sqlite3_step returns 100  callbackinit = 0
col1_Title = Two
col2_Title = 2
customer = John

sqlite3_step returns 100  callbackinit = 1
col1_Title = One
col2_Title = 1
customer = Bill

sqlite3_step returns 100  callbackinit = 1
col1_Title = Three
col2_Title = 3
customer = Albert

sqlite3_step returns 101  callbackinit = 1
File closed OK

This is the working version, the callback function prints out the 3 columns 
of data and all 3 rows from my test database. Also the test.db file is
opened right at the start and closed again at the end



MQX  Non working version shown below
File c:\test.db opened OK
Read 100  Got 100
sqlite3_exec called
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0
Read 1024  Got 1024
sqlite3_exec called
sqlite3_step returns 100  callbackinit = 0
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0
sqlite3_step returns 100  callbackinit = 1
File c:\test.db closed OK
sqlite3_step returns 101  callbackinit = 1
sqlite3_exec called
sqlite3_step returns 101  callbackinit = 0

*** UNHANDLED INTERRUPT ***

Several things to note here, there are 2 reads of 100 bytes then 1024, but
the subsequent ones in the window log dont get called. (Expecting 16 bytes
read as next read call)

None of my lower layer MQX calls are printing out any error lines, so I
think they are working OK

There is a premature call to close the database file, mid way through the
statement execution, so that is possibly why it crashes a few lines farther
on.

The code is very complex around where it is closing the db file and I have
not been able to figure it out so far. Here is the stack trace where it
calls the premature close

http://old.nabble.com/file/p27792715/stack.jpg 

I know its a long shot but does Dr Hipp or anyone have any possible ideas
what could give rise to a premature file close ?  Sorry my message was so
long. Thanks for any ideas

Geoff
-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p27792715.html
Sent from the SQLite mailing list archive at Nabble.com.

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