Re: [sqlite] VFS xFullPathname cannot access URI parameters

[Richard Hipp]

On 2/26/20, Chris Warner  wrote:
> In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_*
> functions inside a VFS xFullPathname function returns junk.  This was not an
> issue in 3.30.1 or 3.31.0.
>

SQLite does not promise that sqlite3_uri_parameter() will work for the
argument passed into xFullPathname.  But it has done so historically,
and (apparently) some software has come to depend on that accidental
behavior.  So I have implemented changes on trunk, and on branch-3.31
to make it work again.

-- 
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] VFS xFullPathname cannot access URI parameters

[Chris Warner]

In 3.31.1, attempting to access URI Parameters via the sqlite3_uri_* functions 
inside a VFS xFullPathname function returns junk.  This was not an issue in 
3.30.1 or 3.31.0.

The internals of those functions use the new databaseName() function, which 
attempts to find the database name from the path passed info sqlite3_open_v2(). 
 According to the function documentation:

> This only works if the filename passed in was obtained from the Pager.

However, the xFullPathname function is called before the Pager has opened the 
file, the parameter passed to sqlite3_uri_* functions aren't prefixed by the 4 
null bytes, thus the databaseName() call steps backward too far.

Minimal reproducer:

--

#include 
#include 

int test_full_path_name(sqlite3_vfs* vfs, const char* zName, int nOut, char* 
zOut)
{
  auto test_param = sqlite3_uri_parameter(zName, "test_param");
  if (!test_param)
  {
    return SQLITE_ERROR;
  }

  return SQLITE_OK;
}

int main()
{
  static sqlite3_vfs uzip_vfs = {
      3,
      0,
      sqlite3_vfs_find(nullptr)->mxPathname,
      nullptr,
      "test-vfs",
      nullptr,
      sqlite3_vfs_find(nullptr)->xOpen,
      sqlite3_vfs_find(nullptr)->xDelete,
      sqlite3_vfs_find(nullptr)->xAccess,
      test_full_path_name,
      sqlite3_vfs_find(nullptr)->xDlOpen,
      sqlite3_vfs_find(nullptr)->xDlError,
      sqlite3_vfs_find(nullptr)->xDlSym,
      sqlite3_vfs_find(nullptr)->xDlClose,
      sqlite3_vfs_find(nullptr)->xRandomness,
      sqlite3_vfs_find(nullptr)->xSleep,
      sqlite3_vfs_find(nullptr)->xCurrentTime,
      sqlite3_vfs_find(nullptr)->xGetLastError,
      sqlite3_vfs_find(nullptr)->xCurrentTimeInt64,
      sqlite3_vfs_find(nullptr)->xSetSystemCall,
      sqlite3_vfs_find(nullptr)->xGetSystemCall,
      sqlite3_vfs_find(nullptr)->xNextSystemCall};

  int rc = sqlite3_vfs_register(&uzip_vfs, 0);
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): vfs registration failed." << std::endl;
  }

  std::string open_path{"file:E:/Temp/test.sqlite?test_param=hello"};

  sqlite3* db{nullptr};
  rc = sqlite3_open_v2(open_path.c_str(), &db, SQLITE_OPEN_READONLY | 
SQLITE_OPEN_URI, "test-vfs");
  if (rc != SQLITE_OK)
  {
    std::cout << "ERROR (" << rc << "): opening the database failed." << 
std::endl;
  }

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

Re: [sqlite] VFS implementation for SoC

[Arun - Siara Logics (cc)]

Thank you.  It appears that the POSIX function fsync of the SoC has some 
problem syncing to the local spiffs, but the whole thing works for an external 
SD filesystem.

Regards
Arun

  On Fri, 12 Apr 2019 18:58:36 +0530 Richard Hipp  wrote 

 > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 > > Hi,
 > >
 > > Please ignore the earlier emails.  The extended error code when trying
 > > CREATE TABLE is 1034 and when trying INSERT is 266.
 > 
 > You can now visit https://www.sqlite.org/rescode.html and search for
 > "1034" and "266" to find out what those errors mean.
 > 
 > 
 > -- 
 > 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

Re: [sqlite] VFS implementation for SoC

[Richard Hipp]

On 4/12/19, Arun - Siara Logics (cc)  wrote:
> Hi,
>
> Please ignore the earlier emails.  The extended error code when trying
> CREATE TABLE is 1034 and when trying INSERT is 266.

You can now visit https://www.sqlite.org/rescode.html and search for
"1034" and "266" to find out what those errors mean.


-- 
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

Re: [sqlite] VFS implementation for SoC

[Arun - Siara Logics (cc)]

Hi,

Please ignore the earlier emails.  The extended error code when trying CREATE 
TABLE is 1034 and when trying INSERT is 266.

I have given below the correct log generated during INSERT.  Thanks.

Regards
Arun

Enter file name: 
/spiffs/test.db
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
Opened database successfully

Welcome to SQLite console!!
---

Database file: /spiffs/test.db

1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file

Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES (shox96_0_2c('This wont get inserted'))
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Open
/spiffs/test.db-journal
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Read:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error


  On Fri, 12 Apr 2019 17:47:21 +0530 Arun - Siara Logics (cc) 
 wrote 
 > I also tried INSERT on an existing database.  This time the extended error 
 > is 266.  I am giving below the log.
 > Also, there are two warnings printed during open:
 > (21) API call with invalid database connection pointer
 > (21) misuse at line 152855 of [fb90e7189a]
 > 
 > Regards
 > Arun
 > 
 > Enter file name: 
 > /spiffs/test.db
 > (21) API call with invalid database connection pointer
 > (21) misuse at line 152855 of [fb90e7189a]
 > fn: FullPathNamefn:Fullpathname:Success
 > fn: Open
 > /spiffs/test.db
 > fn:Open:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > Opened database successfully
 > 
 > Welcome to SQLite console!!
 > ---
 > 
 > Database file: /spiffs/test.db
 > 
 > 1. Open database
 > 2. Execute SQL
 > 3. Execute Multiple SQL
 > 4. Close database
 > 5. List folder contents
 > 6. Rename file
 > 7. Delete file
 > 
 > Enter choice: 2
 > Enter SQL (max 500 characters):
 > INSERT INTO test VALUES ('This wont get inserted')
 > fn: Access
 > fn:Access:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Open
 > /spiffs/test.db-journal
 > Create mode
 > fn:Open:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Write
 > fn: FlushBuffer
 > fn: DirectWrite:
 > fn:DirectWrite:Success
 > fn:FlushBuffer:Success
 > fn:Write:Success
 > fn: Write
 > fn:Write:Success
 > fn: Read
 > fn: FlushBuffer
 > fn: DirectWrite:
 > fn:DirectWrite:Success
 > fn:FlushBuffer:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: FileSize
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn:FileSize:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:FlushBuffer:Success
 > fn: Read
 > fn: FlushBuffer
 > fn:F

Re: [sqlite] VFS implementation for SoC

[Arun - Siara Logics (cc)]

I also tried INSERT on an existing database.  This time the extended error is 
266.  I am giving below the log.
Also, there are two warnings printed during open:
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]

Regards
Arun

Enter file name: 
/spiffs/test.db
(21) API call with invalid database connection pointer
(21) misuse at line 152855 of [fb90e7189a]
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test.db
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
Opened database successfully

Welcome to SQLite console!!
---

Database file: /spiffs/test.db

1. Open database
2. Execute SQL
3. Execute Multiple SQL
4. Close database
5. List folder contents
6. Rename file
7. Delete file

Enter choice: 2
Enter SQL (max 500 characters):
INSERT INTO test VALUES ('This wont get inserted')
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Open
/spiffs/test.db-journal
Create mode
fn:Open:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn:Write:Success
fn: Write
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Write:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Write
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: 266 disk I/O error
Time taken:310381 us


  On Fri, 12 Apr 2019 17:30:14 +0530 Arun - Siara Logics (cc) 
 wrote 
 > Hi, Thank you for the suggestion.  The sqlite3_extended_errcode() is 1034 
 > disk I/O error.
 > Regards
 > Arun
 > 
 >   On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp  
 > wrote 
 >  > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 >  > > fn:DirectWrite:Success
 >  > > fn:FlushBuffer:Success
 >  > > fn:Sync:Success
 >  > > SQL error: disk I/O error
 >  > >
 >  > > At the end, there are two files on disk: vfs_test.db (0 bytes) and
 >  > > vfs_test.db-journal (512 bytes).  There is no problem reading a 
 > database.
 >  > > But when CREATE or INSERT is involved, it gives disk I/O error.
 >  > >
 >  > > Any idea why it is throwing disk I/O error, inspite of the previous sync
 >  > > success?  Any suggestions on how I could figure it out?
 >  > 
 >  > Please tell us the sqlite3_extended_errcode().  Also, consider
 >  > enabling the error and warning log
 >  > (https://www.sqlite.org/errlog.html)
 >  > 
 >  > 
 >  > >
 >  > > Regards
 >  > > Arun
 >  > >
 >  > >
 >  > > ___
 >  > > sqlite-users mailing list
 >  > > sqlite-users@mailinglists.sqlite.org
 >  > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 >  > >
 >  > 
 >  > 
 >  > -- 
 >  > 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

Re: [sqlite] VFS implementation for SoC

[Arun - Siara Logics (cc)]

Hi, Thank you for the suggestion.  The sqlite3_extended_errcode() is 1034 disk 
I/O error.
Regards
Arun

  On Fri, 12 Apr 2019 17:06:00 +0530 Richard Hipp  wrote 

 > On 4/12/19, Arun - Siara Logics (cc)  wrote:
 > > fn:DirectWrite:Success
 > > fn:FlushBuffer:Success
 > > fn:Sync:Success
 > > SQL error: disk I/O error
 > >
 > > At the end, there are two files on disk: vfs_test.db (0 bytes) and
 > > vfs_test.db-journal (512 bytes).  There is no problem reading a database.
 > > But when CREATE or INSERT is involved, it gives disk I/O error.
 > >
 > > Any idea why it is throwing disk I/O error, inspite of the previous sync
 > > success?  Any suggestions on how I could figure it out?
 > 
 > Please tell us the sqlite3_extended_errcode().  Also, consider
 > enabling the error and warning log
 > (https://www.sqlite.org/errlog.html)
 > 
 > 
 > >
 > > Regards
 > > Arun
 > >
 > >
 > > ___
 > > sqlite-users mailing list
 > > sqlite-users@mailinglists.sqlite.org
 > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 > >
 > 
 > 
 > -- 
 > 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

Re: [sqlite] VFS implementation for SoC

[Richard Hipp]

On 4/12/19, Arun - Siara Logics (cc)  wrote:
> fn:DirectWrite:Success
> fn:FlushBuffer:Success
> fn:Sync:Success
> SQL error: disk I/O error
>
> At the end, there are two files on disk: vfs_test.db (0 bytes) and
> vfs_test.db-journal (512 bytes).  There is no problem reading a database.
> But when CREATE or INSERT is involved, it gives disk I/O error.
>
> Any idea why it is throwing disk I/O error, inspite of the previous sync
> success?  Any suggestions on how I could figure it out?

Please tell us the sqlite3_extended_errcode().  Also, consider
enabling the error and warning log
(https://www.sqlite.org/errlog.html)


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


-- 
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] VFS implementation for SoC

[Arun - Siara Logics (cc)]

I am trying to implement the demo VFS provided at 
https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c for a System on Chip 
(ESP32) that has its own inbuilt filesystem (spiffs),

No changes have been made to the above code except, I had to add following code 
in the demoOpen method, just before call to "open" POSIX method:

if ( flags&SQLITE_OPEN_READWRITE || flags&SQLITE_OPEN_MAIN_JOURNAL ) {
struct stat st;
memset(&st, 0, sizeof(struct stat));
int rc = stat( zName, &st );
Serial.println(zName);
if (rc == -1) {
  int fd = open(zName, (O_CREAT | O_EXCL), S_IRUSR | S_IWUSR);
  close(fd);
  //oflags |= (O_CREAT | O_RDWR);
  Serial.println("Create mode");
}
}

since it was not creating the file and giving error at Open.

I have given below the log of VFS function calls when calling sqlite3_open() 
and sqlite3_exec() for simple table creation CREATE TABLE t1 (c1):

Enter file name: 
/spiffs/test_vfs.db
fn: FullPathNamefn:Fullpathname:Success
fn: Open
/spiffs/test_vfs.db
Create mode
fn:Open:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
Opened database successfully

Enter SQL (max 500 characters):
CREATE TABLE t1 (c1)
fn: Access
fn:Access:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Open
/spiffs/test_vfs.db-journal
Create mode
fn:Open:Success
fn: Write
fn:Write:Success
fn: Read
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn: Sync
fn: FlushBuffer
fn:FlushBuffer:Success
fn:Sync:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: FileSize
fn: FlushBuffer
fn:FlushBuffer:Success
fn:FileSize:Success
fn: Read
fn: FlushBuffer
fn:FlushBuffer:Success
fn: Write
fn:Write:Success
fn: Sync
fn: FlushBuffer
fn: DirectWrite:
fn:DirectWrite:Success
fn:FlushBuffer:Success
fn:Sync:Success
SQL error: disk I/O error

At the end, there are two files on disk: vfs_test.db (0 bytes) and 
vfs_test.db-journal (512 bytes).  There is no problem reading a database.  But 
when CREATE or INSERT is involved, it gives disk I/O error.

Any idea why it is throwing disk I/O error, inspite of the previous sync 
success?  Any suggestions on how I could figure it out?

Regards
Arun


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

Re: [sqlite] VFS FCNTL question

[Cezary H. Noweta]

Hello,

On 2017-12-03 17:41, J Decker wrote:

https://sqlite.org/c3ref/c_fcntl_busyhandler.html

#define SQLITE_FCNTL_PDB 30

SQLITE_FCNTL_PDB has no documentation as to what it's for.
The only place, where this FCNTL is used, is btree.c:sqlite3BtreeOpen(). 
The FCNTL informs a VFS driver on sqlite3 * connection using BTree file.


It is useful when your VFS driver uses time-consuming I/O operations 
(for example, over a network) and asynchronous sqlite3_interrupt() has 
been invoked. Your driver can periodically check a status of sqlite3's 
isInterrupted flag and immediately stop time-consuming I/O without 
wasting a time for retrieving/writing a data which would be finally 
discarded due to an interrupt.


-- best regards

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

[sqlite] VFS FCNTL question

[J Decker]

https://sqlite.org/c3ref/c_fcntl_busyhandler.html

#define SQLITE_FCNTL_PDB 30

SQLITE_FCNTL_PDB has no documentation as to what it's for.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vfs question: how to pass binary/blob arg to xCreate()?

[Darko Volaric]

You could try Unicode binary encoding to increase the efficiency when passing 
binary as strings: https://qntm.org/unicodings 


> On Oct 20, 2017, at 7:20 PM, Liam Staskawicz  wrote:
> 
> Hi,
> 
> I'm just getting started exploring the vfs capabilities of sqlite, and
> am interested in providing a binary blob argument  to xCreate(), as that
> blob  includes a schema description used to generate the CREATE TABLE
> statement. The existing argument interface is string based, so I'm
> wondering if there's a way to avoid doing something like base64 encoding
> the blob as a string, or something along those lines.
> Thanks for any tips.
> 
> Liam
> ___
> 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

Re: [sqlite] vfs question: how to pass binary/blob arg to xCreate()?

[Richard Hipp]

On 10/20/17, Liam Staskawicz  wrote:
> Hi,
>
> I'm just getting started exploring the vfs capabilities of sqlite, and
> am interested in providing a binary blob argument  to xCreate(),

No.  The arguments to xCreate() are copies of the arguments to the
CREATE VIRTUAL TABLE statement that creates the virtual table, and the
SQL language is written in text, so the arguments to xCreate must also
be text.
-- 
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] vfs question: how to pass binary/blob arg to xCreate()?

[Liam Staskawicz]

Hi,

I'm just getting started exploring the vfs capabilities of sqlite, and
am interested in providing a binary blob argument  to xCreate(), as that
blob  includes a schema description used to generate the CREATE TABLE
statement. The existing argument interface is string based, so I'm
wondering if there's a way to avoid doing something like base64 encoding
the blob as a string, or something along those lines.
Thanks for any tips.

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

[sqlite] VFS for an MCU internal flash

[Scott Doctor]

Below is a link to files for the Atmel SAM4S processor that 
implements a simple FAT file system. The Atmel part is an ARM M4 
core which I used in a recent design. the link takes you to the 
C files. YOu can also easily navigate to the various 
documentation for the code.

http://asf.atmel.com/docs/latest/common.services.fs.fatfs.access_example.sam4s_xplained/html/files.html


Scott Doctor
scott at scottdoctor.com
--

On 7/13/2015 12:29 PM, Jim Callahan wrote:
> At a minimum SQLite needs a C complier (or cross-compiler) for the CPU.
>
> The storage device manufacturer usually publishes some specs (and sample
> code -- such as assembly language routines callable from C) if  a third
> party has a primitive file system (a term I prefer to VFS which could refer
> to virtualizing a network file system) the device manufacturer should know
> about it.
>
> The problem consists of closing the gap, by building or buying a software
> layer (primitive file system?) between the routines or sample code provided
> by the storage device manufacturer and the calls made by SQLite.
>
> Virtual usually refers to a layer up the stack (more abstraction) he is
> trying to go a layer down the stack (closer to the hardware) which is more
> primitive without all the fancy stuff of the Unix civilization.
>
> Here is an article (SIGMOD 2013) on trying to get MS SQL Server to run on
> an SSD with an ARM chip.
> http://pages.cs.wisc.edu/~jignesh/publ/SmartSSD.pdf
>
>
> Here is a discussion of solid state drive (SSD) firmware.
> http://www.openssd-project.org/wiki/The_OpenSSD_Project
>
> As the controller chip on the SDD drive becomes a more powerful ARM chip,
> it may be feasible to have SQLite in the SDD itself.
>
> Here is a discussion from 2008
> http://sqlite.1065341.n5.nabble.com/Porting-into-a-microcontroller-minimum-requirements-td37469.html
>
> Many handheld consumer devices follow the mobile phone tablet model and
> have a Unix or Linux derived operating system iOS (based BSD Unix), Android
> (based on Linux) or Windows (based on Windows) or Ubuntu (a Linux
> distribution) and most of these already have SQLite.
>
> Jim
>
>
>
> On Mon, Jul 13, 2015 at 11:52 AM, Richard Hipp  wrote:
>
>> On 7/13/15, Jim Callahan  wrote:
>>> SQLite expects a file system.
>>>
>> Not necessarily.  Out-of-the-box SQLite does need a filesystem, but
>> embedded system designers can substitute an alternative VFS
>> implementation that writes directly to hardware.  This has been done
>> before.  There are consumer gadgets that you can buy off-the-shelf
>> today (in blister packs) that contain an instance of SQLite that talks
>> directly to flash memory - essentially using SQLite as the filesystem.
>>
>> I think Shuhrat is just trying to do this again.
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> 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
>
>

[sqlite] VFS for an MCU internal flash

[Jim Callahan]

At a minimum SQLite needs a C complier (or cross-compiler) for the CPU.

The storage device manufacturer usually publishes some specs (and sample
code -- such as assembly language routines callable from C) if  a third
party has a primitive file system (a term I prefer to VFS which could refer
to virtualizing a network file system) the device manufacturer should know
about it.

The problem consists of closing the gap, by building or buying a software
layer (primitive file system?) between the routines or sample code provided
by the storage device manufacturer and the calls made by SQLite.

Virtual usually refers to a layer up the stack (more abstraction) he is
trying to go a layer down the stack (closer to the hardware) which is more
primitive without all the fancy stuff of the Unix civilization.

Here is an article (SIGMOD 2013) on trying to get MS SQL Server to run on
an SSD with an ARM chip.
http://pages.cs.wisc.edu/~jignesh/publ/SmartSSD.pdf


Here is a discussion of solid state drive (SSD) firmware.
http://www.openssd-project.org/wiki/The_OpenSSD_Project

As the controller chip on the SDD drive becomes a more powerful ARM chip,
it may be feasible to have SQLite in the SDD itself.

Here is a discussion from 2008
http://sqlite.1065341.n5.nabble.com/Porting-into-a-microcontroller-minimum-requirements-td37469.html

Many handheld consumer devices follow the mobile phone tablet model and
have a Unix or Linux derived operating system iOS (based BSD Unix), Android
(based on Linux) or Windows (based on Windows) or Ubuntu (a Linux
distribution) and most of these already have SQLite.

Jim



On Mon, Jul 13, 2015 at 11:52 AM, Richard Hipp  wrote:

> On 7/13/15, Jim Callahan  wrote:
> > SQLite expects a file system.
> >
>
> Not necessarily.  Out-of-the-box SQLite does need a filesystem, but
> embedded system designers can substitute an alternative VFS
> implementation that writes directly to hardware.  This has been done
> before.  There are consumer gadgets that you can buy off-the-shelf
> today (in blister packs) that contain an instance of SQLite that talks
> directly to flash memory - essentially using SQLite as the filesystem.
>
> I think Shuhrat is just trying to do this again.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] VFS for an MCU internal flash

[Shuhrat Rahimov]

Hello,
I was trying to run SQLite on ARM Cortex M4 CPU. What I need is a minimal
SQLite without the most features. I have no file system, so I wanted to
save a single file db on the internal MCU flash. I have successfully cross
compiled SQLite for ARM Cortex M4. I have set among others compile time
options SQLITE_OS_OTHER=1 and SQLITE_THREADSAFE=0.  Now I wanted to
implement a VFS. I want to save db tables directly in internal flash
without a file system. I have difficulties to understand test_onefile.c
implementation provided by SQLite. How could I edit that file in order to
be able to write file content on the internal MCU flash? Please, could
someone explain it to me or give some links where it is explained?
Kind Regards

[sqlite] VFS for an MCU internal flash

[Richard Hipp]

On 7/13/15, Jim Callahan  wrote:
> SQLite expects a file system.
>

Not necessarily.  Out-of-the-box SQLite does need a filesystem, but
embedded system designers can substitute an alternative VFS
implementation that writes directly to hardware.  This has been done
before.  There are consumer gadgets that you can buy off-the-shelf
today (in blister packs) that contain an instance of SQLite that talks
directly to flash memory - essentially using SQLite as the filesystem.

I think Shuhrat is just trying to do this again.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] VFS for an MCU internal flash

[Jim Callahan]

Here is a link to a more recent (2013) discussion with a more recent
(3.x.xx vs 2.x.xx) version of SQLite:

http://comments.gmane.org/gmane.comp.db.sqlite.general/83038

Jim

On Mon, Jul 13, 2015 at 11:22 AM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> SQLite expects a file system.
> "SQLite reads and writes directly to ordinary disk files."
> https://www.sqlite.org/about.html
>
> You need a minimal file system.
>
> A file system does not have to be large.
> CP/M ran on an 8 bit Z-80 with less than 64k of RAM  (but the original
> CP/M was written in Z-80 assembly language -- not C).
>
> Here is an article on a stripped down Linux for M3/M4: uCLinux
>
> http://electronicdesign.com/embedded/practical-advice-running-uclinux-cortex-m3m4
>
> And here is website for SQLite for uClinux.
> http://www.menie.org/georges/uClinux/sqlite.html
>
>
> The other option would be to emulate the Linux system file calls.
> You could start from scratch or download the Linux source code (assuming
> you have installed and know how to use git source code management system):
>
> # the Linux kernel (approx. 640MB download):
> $ git clone git://git.kernel.org/pub/scm/linux/kernel/git/torvalds/linux.git
>
>
>
> https://www.kernel.org/pub/software/scm/git/docs/user-manual.html#how-to-get-a-git-repository
>
> Hope this helps.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Mon, Jul 13, 2015 at 5:52 AM, Shuhrat Rahimov 
> wrote:
>
>> Hello,
>> I was trying to run SQLite on ARM Cortex M4 CPU. What I need is a minimal
>> SQLite without the most features. I have no file system, so I wanted to
>> save a single file db on the internal MCU flash. I have successfully cross
>> compiled SQLite for ARM Cortex M4. I have set among others compile time
>> options SQLITE_OS_OTHER=1 and SQLITE_THREADSAFE=0.  Now I wanted to
>> implement a VFS. I want to save db tables directly in internal flash
>> without a file system. I have difficulties to understand test_onefile.c
>> implementation provided by SQLite. How could I edit that file in order to
>> be able to write file content on the internal MCU flash? Please, could
>> someone explain it to me or give some links where it is explained?
>> Kind Regards
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] VFS for an MCU internal flash

[Jim Callahan]

SQLite expects a file system.
"SQLite reads and writes directly to ordinary disk files."
https://www.sqlite.org/about.html

You need a minimal file system.

A file system does not have to be large.
CP/M ran on an 8 bit Z-80 with less than 64k of RAM  (but the original CP/M
was written in Z-80 assembly language -- not C).

Here is an article on a stripped down Linux for M3/M4: uCLinux
http://electronicdesign.com/embedded/practical-advice-running-uclinux-cortex-m3m4

And here is website for SQLite for uClinux.
http://www.menie.org/georges/uClinux/sqlite.html


The other option would be to emulate the Linux system file calls.
You could start from scratch or download the Linux source code (assuming
you have installed and know how to use git source code management system):

# the Linux kernel (approx. 640MB download):
$ git clone git://git.kernel.org/pub/scm/linux/kernel/git/torvalds/linux.git


https://www.kernel.org/pub/software/scm/git/docs/user-manual.html#how-to-get-a-git-repository

Hope this helps.

Jim Callahan
Orlando, FL



On Mon, Jul 13, 2015 at 5:52 AM, Shuhrat Rahimov 
wrote:

> Hello,
> I was trying to run SQLite on ARM Cortex M4 CPU. What I need is a minimal
> SQLite without the most features. I have no file system, so I wanted to
> save a single file db on the internal MCU flash. I have successfully cross
> compiled SQLite for ARM Cortex M4. I have set among others compile time
> options SQLITE_OS_OTHER=1 and SQLITE_THREADSAFE=0.  Now I wanted to
> implement a VFS. I want to save db tables directly in internal flash
> without a file system. I have difficulties to understand test_onefile.c
> implementation provided by SQLite. How could I edit that file in order to
> be able to write file content on the internal MCU flash? Please, could
> someone explain it to me or give some links where it is explained?
> Kind Regards
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite vfs and wal mode

[J Decker]

I was trying to figure out what to do about this WAL journal_mode option...
1) my vfs does not (and cannot) have a shared memory interface... so those
routines are NULL (so I would think WAL logic should prohibit flagging the
database as wal...)
2) I tried to return error on the pragma, but returning "SQLITE_NOTFOUND"
results in a recordset with 'delete' as the first field... (used to be the
journal mode such as 'WAL' )... returning SQLITE_ERROR results in a parsing
error that also does not indicate my result error of something like
'journal mode not supported' so when I started doing this, sqlite was
still attemping to delete the -wal 3 times at startup and then a lot of
tests if it exists... but it never does... (and never has? isn't journal
mode recorded in the database?)

I'm using a 3.8.7.4 amalgamation.

The database seemed to be working before with much fewer operations; was
just annoyed that it was constantly trying to find a file it never
created... (2? times per statement)
And actually even without ever issuing a wal journal mode it keeps testing
for existence...

[sqlite] sqlite vfs and wal mode

[Dan Kennedy]

On 02/23/2015 12:42 PM, J Decker wrote:
> I see; the first part...
>
> " result of the pragma or the error message if the pragma fails"  I missed
> the or part... or rather missed the part before the or.
>
> On Sun, Feb 22, 2015 at 8:13 PM, J Decker  wrote:
>
>> So now that I have the added complexity of my own vfs in which to make a
>> sqlite vfs... I'm having some issues.
>>
>> Hmm...
>> One of the first things I do is 'pragma journal_mode=WAL'
>> I return sqlite_ok; but the result has 0 columns, whereas my command line
>> tool returns a column 'journal_mode' and a row 'WAL'..
>>
>> is WAL somehow dependant on the VFS?

Only in that sqlite3_io_methods.iVersion needs to be 2 or greater. i.e. 
you need to implement the xShmXXX() methods.

>>
>> I was just going to note, that although I'm setting wall, and it does 3
>> probes to see if the wal file exists... and probes a lot actually - though
>> I guess it's assuming other processes are going to be manipulating the
>> file? (is there a compile option to disable that, and assume it's the only
>> one with access to the database?)

If you set "PRAGMA locking_mode=EXCLUSIVE" it won't do that. Of course, 
then you're limited to at most one sqlite3* handle.

Or, it will stop probing if it is actually in wal mode.

Dan.

[sqlite] sqlite vfs and wal mode

[J Decker]

I see; the first part...

" result of the pragma or the error message if the pragma fails"  I missed
the or part... or rather missed the part before the or.

On Sun, Feb 22, 2015 at 8:13 PM, J Decker  wrote:

> So now that I have the added complexity of my own vfs in which to make a
> sqlite vfs... I'm having some issues.
>
> Hmm...
> One of the first things I do is 'pragma journal_mode=WAL'
> I return sqlite_ok; but the result has 0 columns, whereas my command line
> tool returns a column 'journal_mode' and a row 'WAL'..
>
> is WAL somehow dependant on the VFS?
>
> I was just going to note, that although I'm setting wall, and it does 3
> probes to see if the wal file exists... and probes a lot actually - though
> I guess it's assuming other processes are going to be manipulating the
> file? (is there a compile option to disable that, and assume it's the only
> one with access to the database?)
>
> It never created a -wal file... it does use a temp file once when I
> start... and I guess NULL to the open is expecting a unique file every time
> the open method of the vfs is called?
>
> it does create a -journal file.. (and deletes) so I guess it's normal mode
> is working
>
>
>

[sqlite] sqlite vfs and wal mode

[J Decker]

So now that I have the added complexity of my own vfs in which to make a
sqlite vfs... I'm having some issues.

Hmm...
One of the first things I do is 'pragma journal_mode=WAL'
I return sqlite_ok; but the result has 0 columns, whereas my command line
tool returns a column 'journal_mode' and a row 'WAL'..

is WAL somehow dependant on the VFS?

I was just going to note, that although I'm setting wall, and it does 3
probes to see if the wal file exists... and probes a lot actually - though
I guess it's assuming other processes are going to be manipulating the
file? (is there a compile option to disable that, and assume it's the only
one with access to the database?)

It never created a -wal file... it does use a temp file once when I
start... and I guess NULL to the open is expecting a unique file every time
the open method of the vfs is called?

it does create a -journal file.. (and deletes) so I guess it's normal mode
is working

Re: [sqlite] SQLite VFS for Chan FatFS

[Andrew Beal]

Richard,

I appreciate the reference. I am trying to replace a method based on the 
implementation you suggested since we discovered that the SQLite DB doesn't 
take up even a full 5 MB on our system so we are trying to reclaim the extra 
space on the flash chip for other features. 

Andrew Beal

Email: ab...@whoi.edu

-Original Message-
From: Richard Hipp [mailto:d...@sqlite.org] 
Sent: Monday, April 07, 2014 10:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite VFS for Chan FatFS

On Mon, Apr 7, 2014 at 9:52 AM, Andrew Beal  wrote:

> Hey All,
>
> Does anyone have an example of a VFS for use with Chan FatFS or Petit 
> FatFS?
>
> Ref: http://elm-chan.org/fsw/ff/00index_e.html
>
>
Just glaceing at the interface spec, it appears that Petit FatFS only allows a 
single file to be open at once. That isn't sufficient for SQLite (unless you 
specify PRAGMA journal_mode=OFF or journal_mode=MEMORY) so I don't think that 
will work for you.  But an interface to Chan FatFS seems easily doable.

But have you considered using the "test_onefile.c" VFS (
http://www.sqlite.org/src/artifact/0396f220561f3b4e) that writes directly to 
persistent media, without any intervening filesystem?  Would that VFS 
accomplish what you want?

--
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] SQLite VFS for Chan FatFS

[Richard Hipp]

On Mon, Apr 7, 2014 at 9:52 AM, Andrew Beal  wrote:

> Hey All,
>
> Does anyone have an example of a VFS for use with Chan FatFS or Petit
> FatFS?
>
> Ref: http://elm-chan.org/fsw/ff/00index_e.html
>
>
Just glaceing at the interface spec, it appears that Petit FatFS only
allows a single file to be open at once. That isn't sufficient for SQLite
(unless you specify PRAGMA journal_mode=OFF or journal_mode=MEMORY) so I
don't think that will work for you.  But an interface to Chan FatFS seems
easily doable.

But have you considered using the "test_onefile.c" VFS (
http://www.sqlite.org/src/artifact/0396f220561f3b4e) that writes directly
to persistent media, without any intervening filesystem?  Would that VFS
accomplish what you want?

-- 
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] SQLite VFS for Chan FatFS

[Andrew Beal]

Hey All,

Does anyone have an example of a VFS for use with Chan FatFS or Petit FatFS?

Ref: http://elm-chan.org/fsw/ff/00index_e.html



F. Andrew Beal

Woods Hole Oceanographic Institution
266 Woods Hole Road MS#18
Woods Hole, MA 02543

Office: 508-289-2970
Email: ab...@whoi.edu



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

Re: [sqlite] vfs?

[Philip Bennefall]

In that case you want to specify a NULL pointer for that argument. Then 
SqLite will pick an appropriate vfs to use for whatever platform it's being 
run on.


Kind regards,

Philip Bennefall
- Original Message - 
From: "Arbol One" 

To: "General Discussion of SQLite Database" 
Sent: Sunday, June 17, 2012 8:46 PM
Subject: Re: [sqlite] vfs?


Thanks for your prompt respnse.
Well, no, really. I don't know where my application will be used. Maybe 
someone will use it under linux or OS2, all I know is that now I am coding 
in Win32.


- Original Message -
From: Igor Tandetnik
Sent: 06/17/12 02:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] vfs?

Arbol One  wrote: > Using C/C++, is there a way to 
extract which file system is SQLite using? You already know that - it's the 
one you've specified in a call to sqlite3_open_v2. What problem are you 
trying to solve? -- Igor Tandetnik 
___ sqlite-users mailing list 
sqlite-users@sqlite.org 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] vfs?

[Arbol One]

Thanks for your prompt respnse.
 Well, no, really. I don't know where my application will be used. Maybe 
someone will use it under linux or OS2, all I know is that now I am coding in 
Win32.

- Original Message -
From: Igor Tandetnik
Sent: 06/17/12 02:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] vfs?

 Arbol One  wrote: > Using C/C++, is there a way to 
extract which file system is SQLite using? You already know that - it's the one 
you've specified in a call to sqlite3_open_v2. What problem are you trying to 
solve? -- Igor Tandetnik ___ 
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] vfs?

[Igor Tandetnik]

Arbol One  wrote:
> Using C/C++, is there a way to extract which file system is SQLite using?

You already know that - it's the one you've specified in a call to 
sqlite3_open_v2.

What problem are you trying to solve?
-- 
Igor Tandetnik

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

[sqlite] vfs?

[Arbol One]

Using C/C++, is there a way to extract which file system is SQLite using?
 TIA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS minor issue with xDelete method return value

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 31/10/11 09:25, Всеволод Новиков wrote:
> I am using sqlite 3.7.8.
> 
> My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when
> the file to be deleted was not found, and this worked fine for older
> versions (probably prior to 3.7.x where the write ahead log was
> introduced), particularly for 3.6.10.

This was a backwards incompatible change made in SQLite 3.7.8.  I did
report the issue to the developers before its release, but they took no
action and chose not to document it.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6wj8gACgkQmOOfHg372QR0QgCgkXpLgUMZ2lQIY68VJSKwJTbc
EdUAnjE26wC4sYNKIWTm2jtYngmmUr1D
=GsrC
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VFS minor issue with xDelete method return value

[Всеволод Новиков]

I am using sqlite 3.7.8.

My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when the 
file to be deleted was not found, and this worked fine for older 
versions (probably prior to 3.7.x where the write ahead log was 
introduced), particularly for 3.6.10.


But now the sqlite core system asks a VFS to delete the .db-wal 
file at the start of the opening the database. When the VFS returns 
SQLITE_NOTFOUND error, the core system refuses opening the database.


I've patched my custom VFS to return SQLITE_OK in this case, but IMHO 
this is a bug, because returning SQLITE_NOTFOUND error is a normal way 
to report about this particular case - the file requested to be deleted 
is not found. I've looked briefly into previous logs and noticed that 
older sqlite subsystems used to check access to the file using xAccess 
method before trying to delete it. I think this is a most proper way to 
delete a file also in case of .db-wal file.


I think that xDelete should be explained deeply in the documentation. I 
didn't find any description for xDelete-specific return values in the 
documentation, and examples are the only source introducing them.


Regards,
Vsevolod Novikov

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

Re: [sqlite] VFS

[Robert Hairgrove]

It appears that Qt (or more accurately, WebKit) had defined
SQLITE_OMIT_LOAD_EXTENSION and a couple of other symbols at compile
time, and therefore when Qt opens a database, it uses a slightly
different VFS than the default VFS contained in sqlite3.c (i.e., no xDl*
members are set).

I believe now that I should recompile Qt and make SQLite a plug-in ...
or else build it separately and let Qt use the system's SQLite instead
of their version.

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

Re: [sqlite] VFS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/2011 02:39 PM, Robert Hairgrove wrote:
> On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote:
>> What you think you are seeing is not happening.  The documentation is 
>> correct.
> 
> OK ... but what about that which the GDB debugger is seeing?

There are two possibilities:

1 - The SQLite code deliberately (against documented behaviour) goes around
copying VFS structures, and sneaks in behind the scenes to zero out some
members all the while craftily hiding how this is done so that searching the
source code won't find it.  It also hides the previous values so that
extension loading still works.

2 - When layers of third party code are compiled, linked, some static, some
dynamic, and combined in a process, you and/or gdb get confused.

For Linux the only way the default VFS ends up with zero for the dl
functions is if it is compiled with SQLITE_OMIT_LOAD_EXTENSION (which
incidentally has often been the default for many maintainers).

Other things I have seen are multiple copies of SQLite getting loaded into
the same process.  This is especially an issue on Mac where CoreData picks
up the system copy.  The copies all coexist but will confuse you.

I have been caught out by shared library unloading and then reloading.
Because Linux uses ASLR the reload happens at a different address but any
registrations made during the first load point to the wrong addresses.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1khwwACgkQmOOfHg372QRvkACgvIRRBiQV3k9suHC38EhYEKQ8
UmsAnRmXUyam/B2FfiTiS3/cEGyLv/rH
=1gl3
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS

[Robert Hairgrove]

On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote:
> On 02/22/2011 05:29 AM, Robert Hairgrove wrote:
> > I'm trying to understand how the VFS implementation works. 
> 
> What you think you are seeing is not happening.  The documentation is correct.

OK ... but what about that which the GDB debugger is seeing?

> > However, if I open a database and inspect the VFS contained in the sqlite3*,
> 
> That should just point to the VFS used.  There is no copying or modification.

Or so says the documentation... 

Maybe some more details will help here:

The OS is Linux Ubuntu 10.04 LTS, AKA "Lucid Lynx" running on a Dell
Inspiron 1420N notebook (and Dell supplies the drivers etc. for this
distro of Linux). GCC version is 4.4.3.

SQLite version is 3.6.19 (I know it's not the latest and greatest, but
this is what is supplied together with the Qt sources... these are
version 4.7.1 -- which is pretty much up-to-date, but not cutting edge).

I compiled the Qt libraries from source myself and built SQLite
statically into Qt, as opposed to a plug-in.  The SQLite sources are all
in the amalgamation file "sqlite.c".

I created a little console project in QtCreator to test the SQLite VFS
functionality. I had to add "sqlite3.c" to the project because I was
getting linker errors when trying to call some of the SQLite API
functions, such as "sqlite3_vfs_find" ... (hmmm...seems that these
symbols aren't exported from the Qt shared libraries).

Obviously, SOMETHING is resetting these pointers. Here is a snippet from
the test code I wrote. It should compile and run OK if you have a newer
version of Qt installed on your system. Here is my Qt .pro file:

// my Qt project file: ===
QT += core sql
INCLUDEPATH += \
  {wherever the Qt sources are...}/src/3rdparty/sqlite
SOURCES += main.cpp \
  {wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.c
HEADERS += \
  {wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.h
// end of Qt project file ===

And here is the source code of my test app in "main.cpp":

// BEGIN CODE: 
#include 
#include 
#include 
#include 

void showVFS(const sqlite3_vfs * const &);

int main(int argc, char *argv[])
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","default_db");
sqlite3 * pSqliteDb = 0;
//
// Using an on-disk database seems to give the exact same results:
// db.setDatabaseName("/home/bob/code/SQLite_Test_VFS/test.db");
//
db.setDatabaseName(":memory:");
if (db.open()) {
//---
// Some of the following lines of code are copied and
// pasted from the Qt docs:
//---
QVariant v = db.driver()->handle();
if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
// v.data() returns a pointer to the handle...
//
// Actually, it seems to return either int
// or void* ... (see below):
//
sqlite3 *pSqliteDb = *static_cast(v.data());
if (pSqliteDb) {
//
// Interesting that using static_cast<> does not work below;
// probably because QSqlDriver::handle() returns void* (???)
// although the Qt docs AND the sources say differently:
//
sqlite3_vfs * pvfs = *reinterpret_cast(pSqliteDb);

//
// The line below gives a VFS with non-NULL values
// for the struct members under discussion:

//
// sqlite3_vfs * pvfs = sqlite3_vfs_find(0);

//
if (pvfs) {
showVFS(pvfs);
}
} else {
std::cout << "No VFS handle!" << std::endl;
}
}
}
return 0;
}

void showVFS(const sqlite3_vfs * const &pVfs)
{
if (pVfs->zName) {
std::cout << "=\n";
std::cout << "Name of VFS:\t&

Re: [sqlite] VFS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/22/2011 05:29 AM, Robert Hairgrove wrote:
> I'm trying to understand how the VFS implementation works. 

What you think you are seeing is not happening.  The documentation is correct.

> However, if I open a database and inspect the VFS contained in the sqlite3*,

That should just point to the VFS used.  There is no copying or modification.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1j6FkACgkQmOOfHg372QTR8gCgsX+dYtQFkeYRZDskwKoQRXdc
eGUAoJqhFJYdkOp40Usuujws3KSFMT2m
=kYzg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VFS

[Robert Hairgrove]

I'm trying to understand how the VFS implementation works. If I fetch
the default VFS with sqlite3_vfs_find(NULL), these members:

  xDlOpen
  xDlError
  xDlSym
  xDlClose

all have non-NULL values. However, if I open a database and inspect the
VFS contained in the sqlite3*, these four members are all NULL. Which
function resets them, and why? I have no extensions loaded, but
SQLITE_OMIT_EXTENSIONS is also not defined.

Thanks.

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

[sqlite] VFS and hot journals

[Max Vlasov]

Hi,

I recently discovered that for vfs implemented writable formats that
actually change the data of the sqlite base (encryption, compression), there
should be a complex logic behind the restoration from the hot journal, at
least some versions ago. For correct password handling initially I relied on
the test read after the first open to detect wrong password and reopen the
db with a new one, but if a hot file is present  one can lose the data. It
looked like between no-error open (that usually always the case for any db)
and my test read, there was also a restoration attempt and at least sqlite
3.6.10 deleted -journal if this attempt failed. I implemented a more complex
logic when first open also tried to read data from header and reported
SQLITE_NOTADB  if it could not. This worked for 3.6.10

I tried also 3.7.4 and it looks like starting some version sqlite don't
delete -journal if vfs failed to deliver the correct data. But if I disable
my complex logic during open, after the correct password is supplied and vfs
starts to return correct data, I see some reading/file size requests and
something is done, but the final file is malformed. If I repeat the
procedure with 3.6.10 and tricky open the restoration process correctly
returns the original db (I compared them bit to bit). 3.7.4 with this
complex open also returns the correct db.

I know that it's deep about internals and I still can rely on my complex
logic, but what was that change between 3.6.10 and 3.7.4 that fixed -journal
deleting, but introduced something new that sometimes prevents the program
from working correctly?

Thanks,

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

Re: [sqlite] VFS Layer for to split databaseinto several files?

[Jay A. Kreibich]

On Mon, Jul 19, 2010 at 12:47:18PM -0700, James Croall scratched on the wall:
> Hi Michael,
> 
> I agree -- while in theory it shouldn't be difficult, the testing and
> verification would be painful! And it's unclear to me from glancing through
> the code how locking should be handled -- probably just on the first data
> file.
> 
> I saw some historic posts on here where people were discussing writing just
> such a layer, thought there might be something reusable :-)
> 
> I will work on bringing the customer into the 20th century instead.



  I assume using multiple database files to each hold one table (or a
  subset of tables) is not an option?  Will the 2GB limit may still
  cause problems?  You could just open one database and ATTACH the
  others.  As long as the table names are unique, you wouldn't even
  have to rewrite the queries.

  It is a tad bit ugly, but less so than modifying the default VFS.


  I'd be very concerned about locking under NFSv2 as well.  You might
  look into dot-locking instead.  NFSv2 has no locking.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS Layer for to split database into several files?

[Black, Michael (IS)]

What "modern" system doesn't support NFSV3?  They might have that turned on by 
default for their file export to legacy systems perhaps.  The two systems 
should default to the highest version supported by both so who's holding the 
show?
 
Are you running your application on another Linux box?
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of James Croall
Sent: Mon 7/19/2010 2:42 PM
To: j...@kreibi.ch; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] EXTERNAL:Re: VFS Layer for to split database into several 
files?



Hi All,

This is a very modern Linux64 system and the only limiting factor I can find
here is the NFSv2 filesystem. Our process gets killed with a SIGXFSZ when we
exceed 2GB. The user is not keen on changing their environment to support my
application.

Looking through os_unix.c it looks like it could be reasonably easy to turn
one db file into, say, a series of four each with a maximum size of 2GB, and
decide which fd to use based on the offset requested. Plus enough logic to
keep pages in the same partition.

Thanks,

- James

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jay A. Kreibich
> Sent: Monday, July 19, 2010 12:30 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] EXTERNAL:Re: VFS Layer for to split database into
> several files?
>
> On Mon, Jul 19, 2010 at 01:48:56PM -0500, Black, Michael (IS) scratched
> on the wall:
> > NFSV2 is something that limits filesize.
>
>   From the phrasing of the rest of your email, I assume you meant for
>   this to be "NFSv2 *isn't* something...".
>
>   And technically that is true.  NFS won't limit the filesize.
>
>   However, NFSv2 has a very well known limitation, in that network
>   clients could only access the first 2GB worth of a file.  That
>   effectively limits the usable filesize to 2GB.
>
> http://en.wikipedia.org/wiki/Network_File_System_%28protocol%29#NFSv2
>
>   There are still many systems (especially embedded ones) out there
>   that only support NFSv2.  Like TFTP, it is a dead simple protocol
>   that requires almost no state, a very simple networking stack,
>   and is very easy to cram into a very limited code space
>
>-j
>
> >
> > Generally what limits filesize is the operating system and associated
> > compilation flags like Pavel mentioned.
> >
> > What makes you or your "user" think their system is limited to 2GB?
> > What OS are they using and what OS are you using?  If it's anything
> > more recent than about 10 years ago it should support >2GB as long as
> > you have the disk space.
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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


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

Re: [sqlite] VFS Layer for to split databaseinto several files?

[James Croall]

Hi Michael,

I agree -- while in theory it shouldn't be difficult, the testing and
verification would be painful! And it's unclear to me from glancing through
the code how locking should be handled -- probably just on the first data
file.

I saw some historic posts on here where people were discussing writing just
such a layer, thought there might be something reusable :-)

I will work on bringing the customer into the 20th century instead.

Thanks!

- James

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Monday, July 19, 2010 12:42 PM
> To: j...@kreibi.ch; General Discussion of SQLite Database; General
> Discussion of SQLite Database
> Subject: Re: [sqlite] VFS Layer for to split databaseinto several
> files?
> 
> Oops -- my bad...I guess I never ran into that problem in the "old"
> days.  I know I used to write 2Gig+ files on 32-bit Linux before it was
> popular.
> 
> So...perhaps the next logical question is...can this guy's user put
> NFSV3 or such on?  Would be a lot easier than writing your own split-
> VFS which I think is asking for trouble.
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of Jay A. Kreibich
> Sent: Mon 7/19/2010 2:29 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] EXTERNAL:Re: VFS Layer for to split databaseinto
> several files?
> 
> 
> 
> On Mon, Jul 19, 2010 at 01:48:56PM -0500, Black, Michael (IS) scratched
> on the wall:
> > NFSV2 is something that limits filesize.
> 
>   From the phrasing of the rest of your email, I assume you meant for
>   this to be "NFSv2 *isn't* something...".
> 
>   And technically that is true.  NFS won't limit the filesize.
> 
>   However, NFSv2 has a very well known limitation, in that network
>   clients could only access the first 2GB worth of a file.  That
>   effectively limits the usable filesize to 2GB.
> 
> http://en.wikipedia.org/wiki/Network_File_System_%28protocol%29#NFSv2
> 
>   There are still many systems (especially embedded ones) out there
>   that only support NFSv2.  Like TFTP, it is a dead simple protocol
>   that requires almost no state, a very simple networking stack,
>   and is very easy to cram into a very limited code space
> 
>-j
> 
> >
> > Generally what limits filesize is the operating system and associated
> > compilation flags like Pavel mentioned.
> >
> > What makes you or your "user" think their system is limited to 2GB?
> > What OS are they using and what OS are you using?  If it's anything
> > more recent than about 10 years ago it should support >2GB as long as
> > you have the disk space.
> 
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] VFS Layer for to split databaseinto several files?

[Black, Michael (IS)]

Oops -- my bad...I guess I never ran into that problem in the "old" days.  I 
know I used to write 2Gig+ files on 32-bit Linux before it was popular.
 
So...perhaps the next logical question is...can this guy's user put NFSV3 or 
such on?  Would be a lot easier than writing your own split-VFS which I think 
is asking for trouble.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Jay A. Kreibich
Sent: Mon 7/19/2010 2:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXTERNAL:Re: VFS Layer for to split databaseinto several 
files?



On Mon, Jul 19, 2010 at 01:48:56PM -0500, Black, Michael (IS) scratched on the 
wall:
> NFSV2 is something that limits filesize.

  From the phrasing of the rest of your email, I assume you meant for
  this to be "NFSv2 *isn't* something...".

  And technically that is true.  NFS won't limit the filesize.

  However, NFSv2 has a very well known limitation, in that network
  clients could only access the first 2GB worth of a file.  That
  effectively limits the usable filesize to 2GB.

http://en.wikipedia.org/wiki/Network_File_System_%28protocol%29#NFSv2

  There are still many systems (especially embedded ones) out there
  that only support NFSv2.  Like TFTP, it is a dead simple protocol
  that requires almost no state, a very simple networking stack,
  and is very easy to cram into a very limited code space
 
   -j

> 
> Generally what limits filesize is the operating system and associated
> compilation flags like Pavel mentioned.
> 
> What makes you or your "user" think their system is limited to 2GB?
> What OS are they using and what OS are you using?  If it's anything
> more recent than about 10 years ago it should support >2GB as long as
> you have the disk space.

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
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] VFS Layer for to split database into several files?

[James Croall]

Hi Michael,

I'm trying to support a user running on an NFSv2 file server, which is
limited to a maximum file size of 2GB.

Thanks,

- James

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Monday, July 19, 2010 10:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VFS Layer for to split database into several
> files?
> 
> Also...would you care to elucidate what environment you're in that
> limits you to 2GB?
> 
> I've heard numerous people before who think they're limited when, in
> actuality, they're not.
> 
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of James Croall
> Sent: Mon 7/19/2010 12:26 PM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] VFS Layer for to split database into several
> files?
> 
> 
> 
> Hello All,
> 
> 
> 
> I'm stuck with an environment with a 2GB file size limit.
> 
> 
> 
> I have seen several posts over the last few years about working with
> these limits, and several proposals discussed. Some involve creating a
> VFS layer that splits the database file into chunks, and some discuss
> using a DISKIO feature that I'm frankly not familiar with.
> 
> 
> 
> Has anyone come up with a solution that they would be willing to share?
> I am in a time crunch here and anything that would save me some time
> reaching a solution would be *much* appreciated.
> 
> 
> 
> Thanks!
> 
> 
> 
> - James
> 
> ___
> 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] VFS Layer for to split database into several files?

[Black, Michael (IS)]

Also...would you care to elucidate what environment you're in that limits you 
to 2GB?
 
I've heard numerous people before who think they're limited when, in actuality, 
they're not.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of James Croall
Sent: Mon 7/19/2010 12:26 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] VFS Layer for to split database into several files?



Hello All,



I'm stuck with an environment with a 2GB file size limit.



I have seen several posts over the last few years about working with these
limits, and several proposals discussed. Some involve creating a VFS layer
that splits the database file into chunks, and some discuss using a DISKIO
feature that I'm frankly not familiar with.



Has anyone come up with a solution that they would be willing to share? I am
in a time crunch here and anything that would save me some time reaching a
solution would be *much* appreciated.



Thanks!



- James

___
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] VFS Layer for to split database into several files?

[Pavel Ivanov]

> I'm stuck with an environment with a 2GB file size limit.

What the exact problem do you see and how do you compile SQLite? All
modern compilers have macros _LARGEFILE_SOURCE or _FILE_OFFSET_BITS=64
defined by default and with that SQLite works perfectly with any files
with more than 2GB in size. So you could probably define those macros
yourself and see if it makes things better.


Pavel

On Mon, Jul 19, 2010 at 1:26 PM, James Croall  wrote:
> Hello All,
>
>
>
> I'm stuck with an environment with a 2GB file size limit.
>
>
>
> I have seen several posts over the last few years about working with these
> limits, and several proposals discussed. Some involve creating a VFS layer
> that splits the database file into chunks, and some discuss using a DISKIO
> feature that I'm frankly not familiar with.
>
>
>
> Has anyone come up with a solution that they would be willing to share? I am
> in a time crunch here and anything that would save me some time reaching a
> solution would be *much* appreciated.
>
>
>
> Thanks!
>
>
>
> - James
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VFS Layer for to split database into several files?

[James Croall]

Hello All,

 

I'm stuck with an environment with a 2GB file size limit.

 

I have seen several posts over the last few years about working with these
limits, and several proposals discussed. Some involve creating a VFS layer
that splits the database file into chunks, and some discuss using a DISKIO
feature that I'm frankly not familiar with.

 

Has anyone come up with a solution that they would be willing to share? I am
in a time crunch here and anything that would save me some time reaching a
solution would be *much* appreciated.

 

Thanks!

 

- James

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

Re: [sqlite] VFS implementation guidance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> Is it possible to change the VFS SQLite is using while SQLite is
> running? 

You can call sqlite3_vfs_register/unregister at any point.  Note that
SQLite does no inuse tracking so it will happily let you unregister a
VFS currently in use.

There can only be one default but you can change it at any time.  You
can specify the vfs to use on each open - see sqlite3_open_v2.

> I'm looking at creating two different VFS implementations and
> it would be great to be able to switch between implementations as
> required, I'd be looking to switch VFS during program start-up and
> before any databases are loaded or used.

That is trivial to do.  You can just register them all and specify as
appropriate in sqlite3_open_v2.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEUEARECAAYFAkkQ1W4ACgkQmOOfHg372QS5HwCYnWw4GTP55EEYv1rYfkw54E4Y
awCfSiVcPzXsgRmhEVKFy0wbJoC5T2w=
=Dh20
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS implementation guidance

[Mark Spiegel]

I would start with an existing VFS implementation and modify it to use 
the primitives available to you.  I started with the OS_WIN and modified 
it.  That will probably be easier than starting from scratch.

You can switch VFSs with each open.  You can use a different one for 
each DB open if you like.  (I do that now.)  I don't think you can 
switch the VFS for a given DB handle once it is opened.  You will 
probably have to close it and reopen.

Note that you can also, via #define values, exclude the pre-defined VFS 
implementations and just use your own.

Brown, Daniel wrote:
> Thanks for the pointers Roger and the example tests.  
>
> Is it possible to change the VFS SQLite is using while SQLite is
> running?  I'm looking at creating two different VFS implementations and
> it would be great to be able to switch between implementations as
> required, I'd be looking to switch VFS during program start-up and
> before any databases are loaded or used.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
> Sent: Tuesday, November 04, 2008 12:39 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VFS implementation guidance
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Brown, Daniel wrote:
>   
>> Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite?
>> 
> A
>   
>> good practices guide would be as useful.   I already have an existing
>> file system API/library for the target system so I guess it is mostly
>> just matching up the API with the VFS implementation via some wrapper
>> functions?
>> 
>
> Pretty much just implement the functions as documented.  One gotcha is
> that xRandomness is only called once and is only called on the default
> VFS which makes testing it fun.
>
> Another is that xGetLastError is never called so you don't need to
> implement it.  http://www.sqlite.org/cvstrac/tktview?tn=3337
>
> You then need to run queries that exercise all parts of the VFS.  If you
> want some guidance, this is what I use:
>
> http://code.google.com/p/apsw/source/browse/apsw/trunk/tests.py#4759
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkkQssUACgkQmOOfHg372QTuLwCgygTWzPSW3CCHnQONXiEcKXf7
> 5XEAnR7DYzMf+hvXCORi/I/hpWgWF/t3
> =3tEY
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS implementation guidance

[Brown, Daniel]

Thanks for the pointers Roger and the example tests.  

Is it possible to change the VFS SQLite is using while SQLite is
running?  I'm looking at creating two different VFS implementations and
it would be great to be able to switch between implementations as
required, I'd be looking to switch VFS during program start-up and
before any databases are loaded or used.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Roger Binns
Sent: Tuesday, November 04, 2008 12:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VFS implementation guidance

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite?
A
> good practices guide would be as useful.   I already have an existing
> file system API/library for the target system so I guess it is mostly
> just matching up the API with the VFS implementation via some wrapper
> functions?

Pretty much just implement the functions as documented.  One gotcha is
that xRandomness is only called once and is only called on the default
VFS which makes testing it fun.

Another is that xGetLastError is never called so you don't need to
implement it.  http://www.sqlite.org/cvstrac/tktview?tn=3337

You then need to run queries that exercise all parts of the VFS.  If you
want some guidance, this is what I use:

http://code.google.com/p/apsw/source/browse/apsw/trunk/tests.py#4759

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkQssUACgkQmOOfHg372QTuLwCgygTWzPSW3CCHnQONXiEcKXf7
5XEAnR7DYzMf+hvXCORi/I/hpWgWF/t3
=3tEY
-END PGP SIGNATURE-
___
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] VFS implementation guidance

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Brown, Daniel wrote:
> Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite?  A
> good practices guide would be as useful.   I already have an existing
> file system API/library for the target system so I guess it is mostly
> just matching up the API with the VFS implementation via some wrapper
> functions?

Pretty much just implement the functions as documented.  One gotcha is
that xRandomness is only called once and is only called on the default
VFS which makes testing it fun.

Another is that xGetLastError is never called so you don't need to
implement it.  http://www.sqlite.org/cvstrac/tktview?tn=3337

You then need to run queries that exercise all parts of the VFS.  If you
want some guidance, this is what I use:

http://code.google.com/p/apsw/source/browse/apsw/trunk/tests.py#4759

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkQssUACgkQmOOfHg372QTuLwCgygTWzPSW3CCHnQONXiEcKXf7
5XEAnR7DYzMf+hvXCORi/I/hpWgWF/t3
=3tEY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] VFS implementation guidance

[Brown, Daniel]

Good morning list,

Are there any guides to implementing a VFS (sqlite3_vfs) for SQLite?  A
good practices guide would be as useful.   I already have an existing
file system API/library for the target system so I guess it is mostly
just matching up the API with the VFS implementation via some wrapper
functions?

Cheers,

Daniel Brown | Software Engineer
"The best laid schemes o' mice an' men, gang aft agley"


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

Re: [sqlite] vfs implementation question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jeffrey Becker wrote:
> Cool.  I'm actually working on extending Robert Simson's ADO.Net
> provider to allow vfs implementations to be written in managed code.

I'm doing the same in my Python wrapper for SQLite (APSW).  One feature
I have implemented is that the VFS can inherit from an existing one.
That makes it easier for the developer to do things like a VFS that is
like an existing one, but with only specified changes.  For example you
could easily make one that obfuscates the file by xoring the data with a
constant by writing about 10 lines of code.  To make testing easier you
can make an xWrite that will do I/O errors on demand to verify your own
code can handle SQLite telling you there was an I/O error.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIq2zZmOOfHg372QQRApzOAJ9W2sGJe7iwk3B9XlzuWEJol/FQXgCg48qu
ExBykKM3fDTqrUtwgBSU7zc=
=zx60
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vfs implementation question

[Jeffrey Becker]

Cool.  I'm actually working on extending Robert Simson's ADO.Net
provider to allow vfs implementations to be written in managed code.
I hope to get at least two custom vfs implementations into his
codebase, a custom memory backed vfs and a true client-server vfs.

On Tue, Aug 19, 2008 at 5:45 PM, Richard Klein
<[EMAIL PROTECTED]> wrote:
> D. Richard Hipp wrote:
>> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>>
>>
>>> Should the xLock member of sqlite3_io_methods object block until the
>>> lock can be acquired?
>>
>> xLock does not block on any of the built-in VFSes.  But if you want to
>> make your own custom VFS that blocks on locks, I don't  know of any
>> reason why that wouldn't work.
> I wrote a custom VFS that blocks on locks, and it works just fine.
>
> I also implemented a subset of the five locking levels of SQLite:
> just UNLOCKED and EXCLUSIVE.  This means that as soon as a transaction
> asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which
> locks out all other transactions until the first one commits.
>
> This works fine in an embedded application where there are only a
> few threads, whose transactions execute quickly.
>
> - Richard Klein
>
> ___
> 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] vfs implementation question

[Richard Klein]

D. Richard Hipp wrote:
> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>
>   
>> Should the xLock member of sqlite3_io_methods object block until the
>> lock can be acquired?
>
> xLock does not block on any of the built-in VFSes.  But if you want to  
> make your own custom VFS that blocks on locks, I don't  know of any  
> reason why that wouldn't work.
I wrote a custom VFS that blocks on locks, and it works just fine.

I also implemented a subset of the five locking levels of SQLite:
just UNLOCKED and EXCLUSIVE.  This means that as soon as a transaction
asks for a SHARED lock, it actually gets an EXCLUSIVE lock, which
locks out all other transactions until the first one commits.

This works fine in an embedded application where there are only a
few threads, whose transactions execute quickly.

- Richard Klein

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

Re: [sqlite] vfs implementation question

[D. Richard Hipp]

On Aug 19, 2008, at 9:37 AM, Jeffrey Becker wrote:

> So in the cases where the lock cant be acquired, the built in vfs
> implementations return SQLITE_BUSY?
>

Yes

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] vfs implementation question

[Jeffrey Becker]

So in the cases where the lock cant be acquired, the built in vfs
implementations return SQLITE_BUSY?

On Tue, Aug 19, 2008 at 9:16 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:
>
>> Should the xLock member of sqlite3_io_methods object block until the
>> lock can be acquired?
>
> xLock does not block on any of the built-in VFSes.  But if you want to
> make your own custom VFS that blocks on locks, I don't  know of any
> reason why that wouldn't work.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] vfs implementation question

[D. Richard Hipp]

On Aug 19, 2008, at 9:02 AM, Jeffrey Becker wrote:

> Should the xLock member of sqlite3_io_methods object block until the
> lock can be acquired?

xLock does not block on any of the built-in VFSes.  But if you want to  
make your own custom VFS that blocks on locks, I don't  know of any  
reason why that wouldn't work.

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] vfs implementation question

[Jeffrey Becker]

Should the xLock member of sqlite3_io_methods object block until the
lock can be acquired?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

D. Richard Hipp wrote:
> On May 3, 2008, at 12:30 AM, Roger Binns wrote:
>> I would like a #define of the current version number.
> 
> http://www.sqlite.org/c3ref/c_version.html

I meant the version number of the VFS implementation :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIHXY0mOOfHg372QQRAtSNAJwLsq0a2nl0ltxcwIEourfHa+7RCACg2tXZ
Eaxrj1agjIhvOvFxnlK/Vqc=
=1lNc
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[D. Richard Hipp]

On May 3, 2008, at 12:30 AM, Roger Binns wrote:
>
> I would like a #define of the current version number.

http://www.sqlite.org/c3ref/c_version.html

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[Shawn Wilsher]

On Sat, May 3, 2008 at 12:30 AM, Roger Binns <[EMAIL PROTECTED]> wrote:
> I'd also prefer the documentation to be in the wiki so people who use it
> can update it as they discover various issues.  The doc is currently
> duplicated in http://www.sqlite.org/34to35.html and
> http://www.sqlite.org/c3ref/vfs.html
Additionally, there are some inconsistencies between those documents
(method headers aren't the same), and the vfs.html page doesn't give
as detailed (or doesn't even talk about) some of the methods that the
34to35.html page does.

Cheers,

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

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

D. Richard Hipp wrote:
> suggest API or functionality changes, now would be a good to to do so,

I have a few in no particular order, based on currently implementing vfs
in my Python SQLite wrapper.

I would like a #define of the current version number.  This allows me to
cause compile time failure if it is different than my code expects.
(Under the hood I allow "inheritance" from existing vfs so I runtime
detect if the versions mismatch but would prefer to catch it earlier.)

There is no way to pass back error text, only error codes.  Some method
for also setting error text would be nice and more user friendly.

Although you encourage never deleting a vfs, but rather replacing with
one of the same name, I would like some safety checks since from a
dynamic language environment that can only be achieved by leaking
memory.  For example I currently add something to my normal
sqlite3_open_v2 wrapper to catch which vfs is used and if it is one of
mine then increment reference counts.  That way I can know for sure if a
vfs is in use and not call unregister.  There is still a theoretical
race condition between unregistering at the same time as someone tries
to use it, but that won't happen in Python due to the global interpreter
lock.

I'd like the xFileControl method to also take a parameter saying how big
the pArg is.  That makes it possible to use from a non-C environment,
and would add extra safety in a C environment.  (For example in a
wrapper I can't return anything since I have no idea how big the area is.)

I'd like a "not implemented" error code.  You could then fallback to the
default vfs if necessary.  For example a NULL for xDlOpen is treated as
not implemented, but if I supply a callback into Python, that code can't
respond based on runtime determination that it isn't implemented.

I'd also prefer the documentation to be in the wiki so people who use it
can update it as they discover various issues.  The doc is currently
duplicated in http://www.sqlite.org/34to35.html and
http://www.sqlite.org/c3ref/vfs.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIG+ptmOOfHg372QQRAgSXAKDQVphZCHfWe1smV/+OjgbTQD8rNQCeK8Ya
7HDM/jDhgEPexr803lJi+AM=
=lZqJ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[D. Richard Hipp]

On May 2, 2008, at 2:07 PM, Ken wrote:

> I use the VFS to disable journal file creation and writing.
>
> Any possibility to make this part of the sqlite3_open_v2 mode  
> parameter?
>
> That way I could drop my custom VFS.
>

http://www.sqlite.org/pragma.html#pragma_journal_mode

Before you rush out and try this on 3.5.8, please note that it is  
currently only available in CVS HEAD.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Proposed incompatible changes to the SQLite VFS layer

[Ken]

I use the VFS to disable journal file creation and writing.

Any possibility to make this part of the sqlite3_open_v2 mode parameter? 

That way I could drop my custom VFS.

Thanks,
Ken


"D. Richard Hipp" <[EMAIL PROTECTED]> wrote: The Virtual File System (VFS) 
layer for SQLite was introduced in  
version 3.5.0 on 2007-09-04.  The VFS interface has made SQLite much  
easier to port to unusual systems.

However, as we have gained experience with the VFS, we have come to  
realize that the original VFS design has some warts.  In particular,  
not all of the methods in the VFS interface are able to return the  
errors that some systems want to return.  For example, the xAccess()  
method is used to determine whether or not a file exists.  On Unix and  
win32, the system calls to determine whether or not a file exists  
cannot fail as long as the call is well-formed (i.e. you give it a  
valid file descriptor.) The operating system will always be able to  
give you a straight yes/no answer about whether or not a file exists.  
And so we made no provisions in the VFS design for the xAccess()  
method to return an error code.  Since then, we have found that some  
embedded platforms do not work this way and that it is sometimes  
necessary for xAccess() to fail and say "I don't know".  This can  
happen, for example, when the  system is unable to allocate memory for  
an IPC buffer.

To resolve minor problems like this, we are proposing to make  
incompatible changes to the SQLite VFS layer for version 3.6.0.  The  
changes will be relatively minor (such as allowing xAccess to return  
an error code in addition to a result) but they are changes all the  
same and will require VFS implementations targeting 3.5.x to be revised.

If you use SQLite exclusively on workstations (unix, O/S2, and/or  
win32) then you will not notice any changes at all since the VFS  
layers for these systems are supplied by default.  All you have to do  
is recompile.  However, if you have your own custom VFS layer written  
for an embedded platform, the change to version 3.6.0 may require you  
to make adjustments to your application code.  Note, however, that the  
adjustments will not be nearly as complex as the migration from 3.4.2  
to 3.5.0.

Our plan is to release version 3.5.9 prior to starting work on 3.6.0.

If the proposed incompatible changes to the VFS layer might cause you  
hardship, now is the time to make your concerns known.  If you have  
had trouble using the VFS layer and what to suggest API or  
functionality changes, now would be a good to to do so, prior to us  
beginning work on version 3.6.0.

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Proposed incompatible changes to the SQLite VFS layer

[Derrell Lipman]

On Fri, May 2, 2008 at 11:56 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> However, as we have gained experience with the VFS, we have come to
> realize that the original VFS design has some warts.  In particular,
> not all of the methods in the VFS interface are able to return the
> errors that some systems want to return.  For example, the xAccess()
> method is used to determine whether or not a file exists.  On Unix and
> win32, the system calls to determine whether or not a file exists
> cannot fail as long as the call is well-formed (i.e. you give it a
> valid file descriptor.) The operating system will always be able to
> give you a straight yes/no answer about whether or not a file exists.
> And so we made no provisions in the VFS design for the xAccess()
> method to return an error code.  Since then, we have found that some
> embedded platforms do not work this way and that it is sometimes
> necessary for xAccess() to fail and say "I don't know".  This can
> happen, for example, when the  system is unable to allocate memory for
> an IPC buffer.


If you're going to be making changes in this area anyway...

The use of access() at all is really quite dangerous.  It causes a race
condition.  It is quite possible that at the time that access() is called, a
file can not exist, and when the file is then opened for creation, it can
already have been created by a different process (or vice versa).  It is
much safer to never use access() to determine whether you "can" do
something, but rather to actually try to "do" the operation, and test for
success or failure.  This avoid the possible race.

Cheers,

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

[sqlite] Proposed incompatible changes to the SQLite VFS layer

[D. Richard Hipp]

The Virtual File System (VFS) layer for SQLite was introduced in  
version 3.5.0 on 2007-09-04.  The VFS interface has made SQLite much  
easier to port to unusual systems.

However, as we have gained experience with the VFS, we have come to  
realize that the original VFS design has some warts.  In particular,  
not all of the methods in the VFS interface are able to return the  
errors that some systems want to return.  For example, the xAccess()  
method is used to determine whether or not a file exists.  On Unix and  
win32, the system calls to determine whether or not a file exists  
cannot fail as long as the call is well-formed (i.e. you give it a  
valid file descriptor.) The operating system will always be able to  
give you a straight yes/no answer about whether or not a file exists.  
And so we made no provisions in the VFS design for the xAccess()  
method to return an error code.  Since then, we have found that some  
embedded platforms do not work this way and that it is sometimes  
necessary for xAccess() to fail and say "I don't know".  This can  
happen, for example, when the  system is unable to allocate memory for  
an IPC buffer.

To resolve minor problems like this, we are proposing to make  
incompatible changes to the SQLite VFS layer for version 3.6.0.  The  
changes will be relatively minor (such as allowing xAccess to return  
an error code in addition to a result) but they are changes all the  
same and will require VFS implementations targeting 3.5.x to be revised.

If you use SQLite exclusively on workstations (unix, O/S2, and/or  
win32) then you will not notice any changes at all since the VFS  
layers for these systems are supplied by default.  All you have to do  
is recompile.  However, if you have your own custom VFS layer written  
for an embedded platform, the change to version 3.6.0 may require you  
to make adjustments to your application code.  Note, however, that the  
adjustments will not be nearly as complex as the migration from 3.4.2  
to 3.5.0.

Our plan is to release version 3.5.9 prior to starting work on 3.6.0.

If the proposed incompatible changes to the VFS layer might cause you  
hardship, now is the time to make your concerns known.  If you have  
had trouble using the VFS layer and what to suggest API or  
functionality changes, now would be a good to to do so, prior to us  
beginning work on version 3.6.0.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Florian Weimer]

* Dennis Cote:

> The OP said they were measuring an excess of lock calls. That would 
> imply that SQLite is locking files it has already locked. Is that 
> possible with the POSIX APIs?

SQLite uses fcntl-based locks, which keep a separate lock for each byte
in a file (or, more precisely, any byte that you can address with
off_t).  You can lock and unlock ranges, and if the sizes do not match
(which isn't a requirement), the number of calls do not need to match.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VFS memory leak : During lock / unlock operations

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > 
> > There are no lock leaks.  But on the other hand, there is not
> > a one-to-one mapping of lock to unlock calls.  unlock is called
> > more often than lock and there are often attempts to unlock files
> > that have never been locked, irrc.  This is harmless on windows
> > and unix.
> > 
> 
> Richard,
> 
> The OP said they were measuring an excess of lock calls. That would 
> imply that SQLite is locking files it has already locked. Is that 
> possible with the POSIX APIs?
> 

That might also be possible.  It has been a while since I looked
into this, but I think SQLite might be acquiring (for example)
multiple read locks then releasing them all with a single unlock.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> 
> There are no lock leaks.  But on the other hand, there is not
> a one-to-one mapping of lock to unlock calls.  unlock is called
> more often than lock and there are often attempts to unlock files
> that have never been locked, irrc.  This is harmless on windows
> and unix.
> 

Richard,

The OP said they were measuring an excess of lock calls. That would 
imply that SQLite is locking files it has already locked. Is that 
possible with the POSIX APIs?

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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[drh]

<[EMAIL PROTECTED]> wrote:
> Hi..
>  
>we are using sqlite3.3.4 with Integrity OS. we are facing a problem
> where in the VFS memory is getting exhausted due
>to large lock/unlock calls made by sqlite. Integrity support team
> said that,  for each file lock call made by sqlite,  a definite amount
>of memory is allocated, this memory is released only after the unlock
> or when the file is closed. And they are claiming that
>number of file unlock calls are not same as the number of file lock
> calls. they have put traces and identified that for 1000 lock calls
>there are only 950 unlock calls, which is a shortage of 50 unlock
> calls. This will leak considerable amount of memory if the
>system is left for long hours, with continuous sqlite operations
> being made. 
>  
> can any one throw some light on this problem, is there any known
> issue like this. Any information on this will be very helpful

The os_unix.c backend to SQLite makes no attempt to match lock/unlock
calls, because posix does not requires such.  If you are running
on an operating system that does require matching lock/unlock calls,
you will probably need to modify the os_unix.c layer in order for it
to work properly on your system.


>  
> thanks
> murthy
> 
> Please do not print this email unless it is absolutely necessary. Spread 
> environmental awareness.
> 
> The information contained in this electronic message and any attachments to 
> this message are intended for the exclusive use of the addressee(s) and may 
> contain proprietary, confidential or privileged information. If you are not 
> the intended recipient, you should not disseminate, distribute or copy this 
> e-mail. Please notify the sender immediately and destroy all copies of this 
> message and any attachments. 
> 
> WARNING: Computer viruses can be transmitted via email. The recipient should 
> check this email and any attachments for the presence of viruses. The company 
> accepts no liability for any damage caused by any virus transmitted by this 
> email. 
> 
> www.wipro.com
> ___
> 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] VFS memory leak : During lock / unlock operations

[drh]

Brad House <[EMAIL PROTECTED]> wrote:
> >> they have put traces and identified that for 1000 lock calls
> >> there are only 950 unlock calls, which is a shortage of 50 unlock
> >> calls. 
> > The os_unix.c backend to SQLite makes no attempt to match lock/unlock
> > calls, because posix does not requires such.  If you are running
> > on an operating system that does require matching lock/unlock calls,
> > you will probably need to modify the os_unix.c layer in order for it
> > to work properly on your system.
> 
> I'm not sure what you're getting at here.  Are you saying that it's
> possible that some mutexes will _never_ be unlocked?  In what circumstance
> does that occur? Is there a case where a mutex could be destroyed while
> a lock is held?  I know my linux 'man pthread_mutex_destroy' says
> "Attempting to destroy a locked mutex results in undefined behavior".
> 
> Under what circumstances does this "lock leakage" occur exactly?

There are no lock leaks.  But on the other hand, there is not
a one-to-one mapping of lock to unlock calls.  unlock is called
more often than lock and there are often attempts to unlock files
that have never been locked, irrc.  This is harmless on windows
and unix.


--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Brad House]

>> they have put traces and identified that for 1000 lock calls
>> there are only 950 unlock calls, which is a shortage of 50 unlock
>> calls. 
> The os_unix.c backend to SQLite makes no attempt to match lock/unlock
> calls, because posix does not requires such.  If you are running
> on an operating system that does require matching lock/unlock calls,
> you will probably need to modify the os_unix.c layer in order for it
> to work properly on your system.

I'm not sure what you're getting at here.  Are you saying that it's
possible that some mutexes will _never_ be unlocked?  In what circumstance
does that occur? Is there a case where a mutex could be destroyed while
a lock is held?  I know my linux 'man pthread_mutex_destroy' says
"Attempting to destroy a locked mutex results in undefined behavior".

Under what circumstances does this "lock leakage" occur exactly?

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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Sreedhar.a]

Hi,

We are working on different operating system other than unix and windows.
We are using the os_win.c for porting, we have ported the code by making
some changes with respect to the FAT32 file system apis in the code.

We have not tested much, my worry is whether we also need to take care of
such in the os_win.c
In our OS also.

Also, is this the OS dependent?

Best Regards,
A. Sreedhar.


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Friday, February 08, 2008 7:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VFS memory leak : During lock / unlock operations

<[EMAIL PROTECTED]> wrote:
> Hi..
>  
>we are using sqlite3.3.4 with Integrity OS. we are facing a problem 
> where in the VFS memory is getting exhausted due
>to large lock/unlock calls made by sqlite. Integrity support team 
> said that,  for each file lock call made by sqlite,  a definite amount
>of memory is allocated, this memory is released only after the 
> unlock or when the file is closed. And they are claiming that
>number of file unlock calls are not same as the number of file lock 
> calls. they have put traces and identified that for 1000 lock calls
>there are only 950 unlock calls, which is a shortage of 50 unlock 
> calls. This will leak considerable amount of memory if the
>system is left for long hours, with continuous sqlite operations 
> being made.
>  
> can any one throw some light on this problem, is there any known 
> issue like this. Any information on this will be very helpful

The os_unix.c backend to SQLite makes no attempt to match lock/unlock calls,
because posix does not requires such.  If you are running on an operating
system that does require matching lock/unlock calls, you will probably need
to modify the os_unix.c layer in order for it to work properly on your
system.


>  
> thanks
> murthy
> 
> Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
> 
> The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s) and
may contain proprietary, confidential or privileged information. If you are
not the intended recipient, you should not disseminate, distribute or copy
this e-mail. Please notify the sender immediately and destroy all copies of
this message and any attachments. 
> 
> WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses. The
company accepts no liability for any damage caused by any virus transmitted
by this email. 
> 
> www.wipro.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> .


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


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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[murthy.girigowda]

 



From: Murthy Girigowda (WT01 - TES-Access Networks) 
Sent: Friday, February 08, 2008 12:25 PM
To: 'sqlite-users@sqlite.org'
Subject: VFS memory leak : During lock / unlock operations


Hi..
 
   we are using sqlite3.3.4 with Integrity OS. we are facing a problem
where in the VFS memory is getting exhausted due
   to large lock/unlock calls made by sqlite. Integrity support team
said that,  for each file lock call made by sqlite,  a definite amount
   of memory is allocated, this memory is released only after the unlock
or when the file is closed. And they are claiming that
   number of file unlock calls are not same as the number of file lock
calls. they have put traces and identified that for 1000 lock calls
   there are only 950 unlock calls, which is a shortage of 50 unlock
calls. This will leak considerable amount of memory if the
   system is left for long hours, with continuous sqlite operations
being made. 
 
can any one throw some light on this problem, is there any known
issue like this. Any information on this will be very helpful
 
thanks
murthy

Please do not print this email unless it is absolutely necessary. Spread 
environmental awareness.

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

[sqlite] VFS memory leak : During lock / unlock operations

[murthy.girigowda]

Hi..
 
   we are using sqlite3.3.4 with Integrity OS. we are facing a problem
where in the VFS memory is getting exhausted due
   to large lock/unlock calls made by sqlite. Integrity support team
said that,  for each file lock call made by sqlite,  a definite amount
   of memory is allocated, this memory is released only after the unlock
or when the file is closed. And they are claiming that
   number of file unlock calls are not same as the number of file lock
calls. they have put traces and identified that for 1000 lock calls
   there are only 950 unlock calls, which is a shortage of 50 unlock
calls. This will leak considerable amount of memory if the
   system is left for long hours, with continuous sqlite operations
being made. 
 
can any one throw some light on this problem, is there any known
issue like this. Any information on this will be very helpful
 
thanks
murthy

Please do not print this email unless it is absolutely necessary. Spread 
environmental awareness.

The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments. 

WARNING: Computer viruses can be transmitted via email. The recipient should 
check this email and any attachments for the presence of viruses. The company 
accepts no liability for any damage caused by any virus transmitted by this 
email. 

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

RE: [sqlite] VFS in upcoming 3.5.0

[Virgilio Alexandre Fornazin]

You can create a 'shared memory VFS' to share a memory database against
other thread / processes, and you can also 'copy' the RAW bytes of your
memory with memcpy from/to another storage to accomplish the serialize /
load you want to wire transfer SQLite memory databases. But this is not a
simple code, may be after some development, it could be give to public
domain and merged into SQLite if it prove to be robust and safe enough.

-Original Message-
From: Olaf Schmidt [mailto:[EMAIL PROTECTED] 
Sent: sábado, 1 de setembro de 2007 12:15
To: sqlite-users@sqlite.org
Subject: [sqlite] VFS in upcoming 3.5.0


Hi,

first - congrats to the planned changes in the new, upcoming 
version of SQLite. The new shared-cache behaviour sounds
promising and also the new VFS-option.

A few questions to VFS.
1. As known, an InMemory-DB is currently not (much) faster
   than working against a File.
   With the new VFS I think, that much faster InMemory-
   DB-Handling should be possible, is that right?

2. If so, is it planned, to automatically instantiate an appropriate
   (already built in) InMemory-VFS, if one sets the Filename-Param 
   to ':memory:' in an Open-Call, so that InMemory DBs work against 
   this implicite created MemVFS - meaning that the "Default-SQLite-
   engine" already implements such an "InMemory-VFS" for us "Wrapper-
   developers" (because you know best, how to do it in the fastest 
   possible way and because of my following "feature-request" below)? ;-)

3. If you plan something like this, it would be very nice, if
   you could include (now that many new interfaces are coming in
   either way) an additional API-enhancement, wich would allow,  
   to get the current "Byte-Content" of an InMemory-DB, wich
   makes use of this new (built in) InMemory-VFS?

The background for these questions is, that we use SQLite
behind an Appserver wich is currently able, to get Resultset-
Objects at the serverside (done over our wrapper) and after 
retrieving such an Resultset, to serialize its "Query-Content" 
into a ByteArray, wich is then transferred over sockets back 
to the client.

At the clientside we are able, to deserialize the Bytes
and "materialize" a new Resultset-Object appropriately.

With a built in InMemory-VFS (and its new "Dump-Interface")
we could achieve many nice things in only one roundtrip.
At the serverside we could attach an empty InMemory-DB 
(implicitely using the new MemVFS) to an already pooled 
SQLite-Connection and perform a bunch of "Insert Into-Queries",
to create a small snapshot of e.g. a midsized "Master-Detail-
Scenario".
Now we could dump not only a "single-query-content" (as
with our Resultsets currently), but could write a complete, 
related scenario (containing the prepared "InMemory-Tables") 
to a ByteArray and transfer *this* to the client.

Now it would be great, if we could use the new MemVFS-
interface, to create *and* initialize a new InMemory-DB at 
the clientside with the received ByteContent.
This way, we could perform related queries (Joins, Filters,
etc.) against the InMemory-DB (containing the midsize
Master-Detail-Set) without doing any extra-roundtrips 
over the server.

An already builtin InMemory-VFS would ease the burden
of all wrapper-developers, to implement such kind of animal
themselfes - and maybe such an implementation would
help to cleanup (and speedup) the already contained 
InMemory-DB-Handling of the sqlite-engine too.

What's your opinion on this feature-request?

Best regards,

Olaf Schmidt
(developer of dhSQLite and dhRPCServer)


-- 
View this message in context:
http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170
Sent from the SQLite mailing list archive at Nabble.com.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] VFS in upcoming 3.5.0

[Olaf Schmidt]

>> 1. As known, an InMemory-DB is currently not (much) faster
>>than working against a File.
>>With the new VFS I think, that much faster InMemory-
>>DB-Handling should be possible, is that right?

> I don't think so. The vfs is really an interface for moving 
> data (pages) in and out of the pager cache. 
Yep, so I understood them.

> The way in-memory databases currently work, pages are 
> just kept in the page-cache all the time and never written 
> out to whatever system implements the vfs interface.
Ah, ok. 
Somehow thought, that the InMemory-Handling was implemented,
working against the b-trees, but then going *through* 
a "non-sized" pager-cache against "something behind it", 
wich was implemented using standard-fileio-apis, working 
against memory-mapped-files (so being system-dependent
and not so performant like a "real selfwritten Filesystem"). 
>From my Insert-Tests (50,000 records on 6 mixed Columns):
Filebased (sync=off, pagesize 4kB): ca. 0.7sec
InMemory (pagesize 4kB): ca. 0.55sec
(all done using direct bindings on the same Insert-statement)

So it seems, that *not* seeing a huge performance-boost
is more, because most of the "insert-time" is spent populating
the B-tree, right?

Anyway, an easy to use, already built in "Dump-Interface"
for InMemory-DBs (able to be initialized with a given Byte-
Content) would be nice. ;-)
(Yes, I saw patches for this ask, but some time ago, not
knowing if those work with the current pager. Simply thought,
that this is a good time, to ask for an "officially supported"
interface-enhancement regarding this functionality, mainly
because of the new VFS and because I thought, you would
use its capabilities in either way for InMemory-DBs).


Regards,

Olaf Schmidt
-- 
View this message in context: 
http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12442415
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] VFS in upcoming 3.5.0

[Dan Kennedy]

On Sat, 2007-09-01 at 08:15 -0700, Olaf Schmidt wrote:
> Hi,
> 
> first - congrats to the planned changes in the new, upcoming 
> version of SQLite. The new shared-cache behaviour sounds
> promising and also the new VFS-option.
> 
> A few questions to VFS.
> 1. As known, an InMemory-DB is currently not (much) faster
>than working against a File.
>With the new VFS I think, that much faster InMemory-
>DB-Handling should be possible, is that right?

I don't think so. The vfs is really an interface for moving data 
(pages) in and out of the pager cache. The way in-memory databases 
currently work, pages are just kept in the page-cache all the time
and never written out to whatever system implements the vfs interface.

So there's not a lot you can do with the vfs interface to speed
up in-memory databases.

Years ago (sqlite v2) there was different data structure used 
for in-memory databases - "red-black balanced trees" or 
something instead of b-trees. But IIRC it wasn't all 
that much faster than the current approach - maybe 15-20% faster
or thereabouts. And it didn't do nearly as good a job of using 
memory efficiently.

Dan.



> 2. If so, is it planned, to automatically instantiate an appropriate
>(already built in) InMemory-VFS, if one sets the Filename-Param 
>to ':memory:' in an Open-Call, so that InMemory DBs work against 
>this implicite created MemVFS - meaning that the "Default-SQLite-
>engine" already implements such an "InMemory-VFS" for us "Wrapper-
>developers" (because you know best, how to do it in the fastest 
>possible way and because of my following "feature-request" below)? ;-)
> 
> 3. If you plan something like this, it would be very nice, if
>you could include (now that many new interfaces are coming in
>either way) an additional API-enhancement, wich would allow,  
>to get the current "Byte-Content" of an InMemory-DB, wich
>makes use of this new (built in) InMemory-VFS?
> 
> The background for these questions is, that we use SQLite
> behind an Appserver wich is currently able, to get Resultset-
> Objects at the serverside (done over our wrapper) and after 
> retrieving such an Resultset, to serialize its "Query-Content" 
> into a ByteArray, wich is then transferred over sockets back 
> to the client.
> 
> At the clientside we are able, to deserialize the Bytes
> and "materialize" a new Resultset-Object appropriately.
> 
> With a built in InMemory-VFS (and its new "Dump-Interface")
> we could achieve many nice things in only one roundtrip.
> At the serverside we could attach an empty InMemory-DB 
> (implicitely using the new MemVFS) to an already pooled 
> SQLite-Connection and perform a bunch of "Insert Into-Queries",
> to create a small snapshot of e.g. a midsized "Master-Detail-
> Scenario".
> Now we could dump not only a "single-query-content" (as
> with our Resultsets currently), but could write a complete, 
> related scenario (containing the prepared "InMemory-Tables") 
> to a ByteArray and transfer *this* to the client.
> 
> Now it would be great, if we could use the new MemVFS-
> interface, to create *and* initialize a new InMemory-DB at 
> the clientside with the received ByteContent.
> This way, we could perform related queries (Joins, Filters,
> etc.) against the InMemory-DB (containing the midsize
> Master-Detail-Set) without doing any extra-roundtrips 
> over the server.
> 
> An already builtin InMemory-VFS would ease the burden
> of all wrapper-developers, to implement such kind of animal
> themselfes - and maybe such an implementation would
> help to cleanup (and speedup) the already contained 
> InMemory-DB-Handling of the sqlite-engine too.
> 
> What's your opinion on this feature-request?
> 
> Best regards,
> 
> Olaf Schmidt
> (developer of dhSQLite and dhRPCServer)
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] VFS in upcoming 3.5.0

[Olaf Schmidt]

Hi,

first - congrats to the planned changes in the new, upcoming 
version of SQLite. The new shared-cache behaviour sounds
promising and also the new VFS-option.

A few questions to VFS.
1. As known, an InMemory-DB is currently not (much) faster
   than working against a File.
   With the new VFS I think, that much faster InMemory-
   DB-Handling should be possible, is that right?

2. If so, is it planned, to automatically instantiate an appropriate
   (already built in) InMemory-VFS, if one sets the Filename-Param 
   to ':memory:' in an Open-Call, so that InMemory DBs work against 
   this implicite created MemVFS - meaning that the "Default-SQLite-
   engine" already implements such an "InMemory-VFS" for us "Wrapper-
   developers" (because you know best, how to do it in the fastest 
   possible way and because of my following "feature-request" below)? ;-)

3. If you plan something like this, it would be very nice, if
   you could include (now that many new interfaces are coming in
   either way) an additional API-enhancement, wich would allow,  
   to get the current "Byte-Content" of an InMemory-DB, wich
   makes use of this new (built in) InMemory-VFS?

The background for these questions is, that we use SQLite
behind an Appserver wich is currently able, to get Resultset-
Objects at the serverside (done over our wrapper) and after 
retrieving such an Resultset, to serialize its "Query-Content" 
into a ByteArray, wich is then transferred over sockets back 
to the client.

At the clientside we are able, to deserialize the Bytes
and "materialize" a new Resultset-Object appropriately.

With a built in InMemory-VFS (and its new "Dump-Interface")
we could achieve many nice things in only one roundtrip.
At the serverside we could attach an empty InMemory-DB 
(implicitely using the new MemVFS) to an already pooled 
SQLite-Connection and perform a bunch of "Insert Into-Queries",
to create a small snapshot of e.g. a midsized "Master-Detail-
Scenario".
Now we could dump not only a "single-query-content" (as
with our Resultsets currently), but could write a complete, 
related scenario (containing the prepared "InMemory-Tables") 
to a ByteArray and transfer *this* to the client.

Now it would be great, if we could use the new MemVFS-
interface, to create *and* initialize a new InMemory-DB at 
the clientside with the received ByteContent.
This way, we could perform related queries (Joins, Filters,
etc.) against the InMemory-DB (containing the midsize
Master-Detail-Set) without doing any extra-roundtrips 
over the server.

An already builtin InMemory-VFS would ease the burden
of all wrapper-developers, to implement such kind of animal
themselfes - and maybe such an implementation would
help to cleanup (and speedup) the already contained 
InMemory-DB-Handling of the sqlite-engine too.

What's your opinion on this feature-request?

Best regards,

Olaf Schmidt
(developer of dhSQLite and dhRPCServer)


-- 
View this message in context: 
http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-