[sqlite] how to cite SQLite

[Roman Fleysher]

Thank you Simon and Richard. I will use this form.

What I also meant is that software often impements unique algorithms, which I 
think SQLite does. These algorithms may be presented at conferences/journals. 
In turn, these publications cite the URL. Thus, I was looking for such a 
presentation, if it exists. Obviously, software may also impement other 
algorithms developed by other people, not directly the software developers.  I 
would expect the "main" SQLite publication to mention them.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, July 13, 2015 2:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to cite SQLite

On 7/13/15, Simon Slavin  wrote:
>
> On 13 Jul 2015, at 4:51pm, Roman Fleysher 
> wrote:
>
>> I am writing a scientific paper to describe our research. To manage data,
>> we use SQLite. I would like to acknowledge SQLite and cite it properly in
>> the paper. Is there a suggested way of doing it? A conference
>> presentation? A paper, a book? In the simplest form I will use URL.
>
> Roman,
>
> SQLite must be cited as software rather than an academic presentation.  I'm
> afraid that the correct citation form for software will depend on the style
> guide of your publication.  I see you're working in medicine in the USA.
> The closest citation forms I know of for US medicine are as follows.  The
> long form conforms to the one used by the APA which, for SQLite, would be
> something like
>
> Hipp, R, et. al. (2015). SQLite (Version 3.8.10.2) [Computer software].
>   SQLite Development Team.  Retrieved June 15, 2015.
>   Available from 

Hipp, D. R., Kennedy, D., Mistachkin, J., (2015) SQLite (Version 3.8.10.2)
[Computer software].  SQLite Development Team.  Retrieved 2015-06-15.
Available from 

Note that the "download.html" link is volatile and will change (to
version 3.8.11) in about 3 weeks, whereas the /src/info/2ef4f link is
persistent and is intended to be accessible for at least 35 years.

>
> You should substitute your own version and dates.  The other is the short
> form the AMA uses which, for SQLite, would be
>
> Hipp R et. al.. SQLite. North Carolina: SQLite Development Team; 2015.
>
> If you find the form of this which suits your publication better, please
> post it to this thread and I'll add it to the list.
>
> Richard, the 'author' requirements require the name of at least one specific
> person.  I don't know who else is on the development team so I've taken the
> liberty of using "Richard Hipp et. al." and "SQLite Development Team" which
> I guessed were closest to what you'd all want.  If you don't like them
> please don't hesitate to correct them.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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] Fwd: Usenix paper: Korean researchers invent sqlite WALDIO mode to circumvent redundant journaling by EXT4 on eMMC

[Donald Griggs]

There's an interesting paper at

https://www.usenix.org/system/files/conference/atc15/atc15-paper-lee-wongun.pdf

I don't know enough to evaluate it, but if I'm understanding correctly:
 -- They have modified sqlite so as to work directly with the EXT4
filesystem to prevent redundant journaling (i.e., otherwise, both sqlite
and ext4 will journal all data changes)
 -- They claim sqlite writes account for a significant portion of mobile
device data writes.
 -- They claim huge reductions in data writes in some configurations --
e.g. down to as low as one sixth of unmodified systems.
 -- They call their new sqlite mode "WALDIO" for WAL Direct-I/O.
 -- They make several changes to obtain the claimed efficiency, such as
preallocation and initialization of db sectors, modifiying and aligning
headers, commands to EXT4, etc.
 -- One mode does require that power not be removed abruptly from the eMMC
controller (but they still claim durability even in the face of a kernel
panic)
 -- The flash system should not ignore the DISCARD command nor return the
old data if a read is later attempted.
 -- Their prototype is on a Samsung Galaxy S5
 -- I don't notice where their code is available.  I  guess it's
proprietary (?)


*Abstract *
This work is dedicated to resolve the Journaling of Journal Anomaly in
Android IO stack. We orchestrate SQLite and EXT4 filesystem so that
SQLite?s file-backed journaling activity can dispense with the expensive
filesystem intervention, the journaling, without compromising the file
integrity under unexpected filesystem failure. In storing the logs, we
exploit the direct IO to suppress the filesystem interference.


This work consists of three key ingredients:
   (i) Preallocation with Explicit Journaling,
   (ii) Header Embedding, and
   (iii) Group Synchronization.

Preallocation with Explicit Journaling eliminates the filesystem journaling
properly protecting the file metadata against the unexpected system crash.
We redesign the SQLite B-tree structure with Header Embedding to make it
direct IO compatible and block IO friendly. With Group Synch, we minimize
the synchronization overhead of direct IO and make the SQLite operation
NAND Flash friendly. Combining the three technical ingredients, we develop
a new journal mode in SQLite, the WALDIO. We implement it on the
commercially available smartphone. WALDIO mode achieves 5.1? performance
(insert/sec) against WAL mode which is the fastest journaling mode in
SQLite. It yields 2.7? performance (inserts/sec) against the LS-MVBT, the
fastest SQLite journaling mode known to public. WALDIO mode achieves 7.4?
performance (insert/sec) against WAL mode when it is relieved from the
overhead of explicitly synchronizing individual log-commit operations.
WALDIO mode reduces the IO volume to 1/6 compared against the WAL mode.

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 08:00 PM, Hayden Livingston wrote:
> Does your code also map object hierarchies in json?

Yes, but thankfully I don't have much of them.  Essentially the top
level of the object has a unique id (SQLite allocated), and then other
tables are used to join zero or more child objects to the top level.

> What general format do you use?

Ultimately I use Python dictionaries which are supersets of JSON
objects.  Some (ugly) code can convert both ways

> Each object type gets stored in a separate table?

In my case yes but that is because the underlying data represents
known entities and was actually originally in Postgres and then
exported to hideous inconsistent XML which I then convert/denormalise
back into JSON.

Do remember that SQLite does not require you to specify a type for
each column, nor does it care about the values in a column being
different types between rows.  That means I don't have to worry about
types, only the big picture top level of something being an object, a
list, or a scalar.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkg0MACgkQmOOfHg372QRTbQCfRobbh4uU1Eqf0SD9LJxABFYj
iv8AoKUvXNQEgGVvmPiZ/tQgHyU7Q0yL
=S7AM
-END PGP SIGNATURE-

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

We use SQLite as a library and we don't want to migrate to a server situation.

So it seems using the month-to-month approach you outline is
reasonable and not outrageous. Good to know.

Thanks for the tip on schema modifications which get automatically
upgraded on read. This is a great feature. I think I might try that
out for my application.

Does your code also map object hierarchies in json? What general
format do you use? Each object type gets stored in a separate table?

On Mon, Jul 13, 2015 at 7:03 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/13/2015 05:43 PM, Hayden Livingston wrote:
>> Is there a concept of a schema-less JSON SQLite DB?
>
> What exactly is it you want?  A schema-less database?  A JSON
> database? Using SQLite to store JSON?  (It is unclear if you mean
> something like SQLite, or SQLite itself).  Something small and light?
>  A library not a server?
>
> Be aware that you can dynamically update the SQLite schema, and that
> it takes no time to do so.  Another database I won't name locks the
> database and rewrites each record.  This can take hours or even days.
>  SQLite just updates the schema table, and fills in defaults for
> records written before the schema change, on reads.  I wrote some code
> the other day that takes JSON and does the schema manipulation,
> although it is annoying.  (It would be really great if SQLite
> supported arrays like Postgres does.)
>
> You can also store arbitrary JSON in a column.  You won't be able to
> query it effectively, but you can duplicate values into columns you
> can do queries and indexes on.
>
>> My reason is simple: versioning. We have lot of business metrics
>> that get updated let's say once a month, and we need to be agile to
>> get them. Right now, we just put the version in the SQLite file,
>> and then make sure no queries cross the boundaries.
>
> You can also use multiple databases, and attach them.  For example if
> you operate on a month by month basis, then you can put each month's
> data in a separate SQLite file, then attach last month's as
> 'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo >
> lastmonth.bar.foo)
>
>> Do others have experiences and requirements similar to this?
>
> All the time.  I use SQLite when I don't need networked access, a
> running database server, and need less things that can go wrong.
> Mapping JSON into this is painful but possible.
>
> When I can use a database server, I prefer Mongodb as it is very good
> at arbitrary JSON in, the same arbitrary JSON back out.  It is
> especially pleasant that the query syntax has the same shape as the
> underlying JSON data.  Also JSON maps trivially to Python which I use
> the most.  (Note however that Mongodb does have some issues, but so
> does much other software out there.  Production use does require
> effort & planning as does other software.)
>
> In the longer term Postgres is getting increasingly better JSON
> support.  Hopefully it will give Mongodb a run for its money soon.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE
> qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex
> =9jcT
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Schema-less JSON SQLite DB?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/13/2015 05:43 PM, Hayden Livingston wrote:
> Is there a concept of a schema-less JSON SQLite DB?

What exactly is it you want?  A schema-less database?  A JSON
database? Using SQLite to store JSON?  (It is unclear if you mean
something like SQLite, or SQLite itself).  Something small and light?
 A library not a server?

Be aware that you can dynamically update the SQLite schema, and that
it takes no time to do so.  Another database I won't name locks the
database and rewrites each record.  This can take hours or even days.
 SQLite just updates the schema table, and fills in defaults for
records written before the schema change, on reads.  I wrote some code
the other day that takes JSON and does the schema manipulation,
although it is annoying.  (It would be really great if SQLite
supported arrays like Postgres does.)

You can also store arbitrary JSON in a column.  You won't be able to
query it effectively, but you can duplicate values into columns you
can do queries and indexes on.

> My reason is simple: versioning. We have lot of business metrics
> that get updated let's say once a month, and we need to be agile to
> get them. Right now, we just put the version in the SQLite file,
> and then make sure no queries cross the boundaries.

You can also use multiple databases, and attach them.  For example if
you operate on a month by month basis, then you can put each month's
data in a separate SQLite file, then attach last month's as
'lastmonth', and use in queries (eg SELECT blah blah WHERE bar.foo >
lastmonth.bar.foo)

> Do others have experiences and requirements similar to this?

All the time.  I use SQLite when I don't need networked access, a
running database server, and need less things that can go wrong.
Mapping JSON into this is painful but possible.

When I can use a database server, I prefer Mongodb as it is very good
at arbitrary JSON in, the same arbitrary JSON back out.  It is
especially pleasant that the query syntax has the same shape as the
underlying JSON data.  Also JSON maps trivially to Python which I use
the most.  (Note however that Mongodb does have some issues, but so
does much other software out there.  Production use does require
effort & planning as does other software.)

In the longer term Postgres is getting increasingly better JSON
support.  Hopefully it will give Mongodb a run for its money soon.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWkbfsACgkQmOOfHg372QTFiwCgzzf5fEzfJdWn84bk0e1fusGE
qeAAnAhR+sHb39Gsha2Owq3SXsdZVRex
=9jcT
-END PGP SIGNATURE-

[sqlite] how to cite SQLite

[Simon Slavin]

On 13 Jul 2015, at 4:51pm, Roman Fleysher  
wrote:

> I am writing a scientific paper to describe our research. To manage data, we 
> use SQLite. I would like to acknowledge SQLite and cite it properly in the 
> paper. Is there a suggested way of doing it? A conference presentation? A 
> paper, a book? In the simplest form I will use URL.

Roman,

SQLite must be cited as software rather than an academic presentation.  I'm 
afraid that the correct citation form for software will depend on the style 
guide of your publication.  I see you're working in medicine in the USA.  The 
closest citation forms I know of for US medicine are as follows.  The long form 
conforms to the one used by the APA which, for SQLite, would be something like

Hipp, R, et. al. (2015). SQLite (Version 3.8.10.2) [Computer software].
SQLite Development Team.  Retrieved June 15, 2015.
Available from 

You should substitute your own version and dates.  The other is the short form 
the AMA uses which, for SQLite, would be

Hipp R et. al.. SQLite. North Carolina: SQLite Development Team; 2015.

If you find the form of this which suits your publication better, please post 
it to this thread and I'll add it to the list.

Richard, the 'author' requirements require the name of at least one specific 
person.  I don't know who else is on the development team so I've taken the 
liberty of using "Richard Hipp et. al." and "SQLite Development Team" which I 
guessed were closest to what you'd all want.  If you don't like them please 
don't hesitate to correct them.

Simon.

[sqlite] How to correctly a add string to a Mem?

[Sairam Gaddam]

I want to make some changes to the result set and I need to add an extra
column.

On Mon, Jul 13, 2015 at 12:33 PM, Clemens Ladisch 
wrote:

> Sairam Gaddam wrote:
> > I have tried a method to create a Mem and add a string to it, which is as
> > below
> >
> >  sqlite3VdbeMemSetStr(&(p->custom_aMem[0]), zColumn ,
> strlen(zColumn)*sizeof(char), SQLITE_UTF8, SQLITE_STATIC);
>
> This is an internal function that applications are not supposed to know
> about.  There is no right way to use it because the interface can change
> at any time.
>
> Why are you trying to do this?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Schema-less JSON SQLite DB?

[Hayden Livingston]

 Is there a concept of a schema-less JSON SQLite DB?

 My reason is simple: versioning. We have lot of business metrics that
 get updated let's say once a month, and we need to be agile to get
 them. Right now, we just put the version in the SQLite file, and then
 make sure no queries cross the boundaries.

 Secondly, we have requirements for slightly hierarchal data, i.e.
 mostly row form, but then some guy wants to put an object.

 What's the SQLite community heading towards if at all?

 Do others have experiences and requirements similar to this?

[sqlite] how to cite SQLite

[Roman Fleysher]

Thank you, Richard. I will.

Our research has nothing to do with databases or computer science. We use 
SQLite as pure users, to help manage our data analysis. Nevertheless, it made a 
huge difference for us already and more to come as we replace text-file 
based/manual management. I am very grateful to all SQLiters for making SQLite 
available.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, July 13, 2015 11:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to cite SQLite

On 7/13/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am writing a scientific paper to describe our research. To manage data, we
> use SQLite. I would like to acknowledge SQLite and cite it properly in the
> paper. Is there a suggested way of doing it? A conference presentation? A
> paper, a book? In the simplest form I will use URL.
>

I think just the URL:  https://www.sqlite.org/

If possible, please provide a link to your paper when it becomes available.  :-)

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

[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] how to cite SQLite

[Roman Fleysher]

Dear SQLiters,

I am writing a scientific paper to describe our research. To manage data, we 
use SQLite. I would like to acknowledge SQLite and cite it properly in the 
paper. Is there a suggested way of doing it? A conference presentation? A 
paper, a book? In the simplest form I will use URL.


Thank you,

Roman

[sqlite] SQLite transaction time limit

[Iryna Atamanova]

Hi guys,

We have found the next feature of sqllite - it transaction takes more that
5 seconds, the database has been locked for some time. Could you please
help us and let me know how we can increase this time - 5 seconds to 30 for
example (what parameter / attribute can be used?)  from source code .

Our project is implemented on .Net CF and we use SQLite version 3.8.

Best regards,
Ira Atamanova

[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] How to correctly a add string to a Mem?

[Clemens Ladisch]

Sairam Gaddam wrote:
> I want to make some changes to the result set

The SQLite API does not allow making changes to the data in a sqlite3_stmt.

> and I need to add an extra column.

This can be done in SQL:

  SELECT Column1, ColumnA, 'some value' FROM ...;


Regards,
Clemens

[sqlite] how to cite SQLite

[Richard Hipp]

On 7/13/15, Simon Slavin  wrote:
>
> On 13 Jul 2015, at 4:51pm, Roman Fleysher 
> wrote:
>
>> I am writing a scientific paper to describe our research. To manage data,
>> we use SQLite. I would like to acknowledge SQLite and cite it properly in
>> the paper. Is there a suggested way of doing it? A conference
>> presentation? A paper, a book? In the simplest form I will use URL.
>
> Roman,
>
> SQLite must be cited as software rather than an academic presentation.  I'm
> afraid that the correct citation form for software will depend on the style
> guide of your publication.  I see you're working in medicine in the USA.
> The closest citation forms I know of for US medicine are as follows.  The
> long form conforms to the one used by the APA which, for SQLite, would be
> something like
>
> Hipp, R, et. al. (2015). SQLite (Version 3.8.10.2) [Computer software].
>   SQLite Development Team.  Retrieved June 15, 2015.
>   Available from 

Hipp, D. R., Kennedy, D., Mistachkin, J., (2015) SQLite (Version 3.8.10.2)
[Computer software].  SQLite Development Team.  Retrieved 2015-06-15.
Available from 

Note that the "download.html" link is volatile and will change (to
version 3.8.11) in about 3 weeks, whereas the /src/info/2ef4f link is
persistent and is intended to be accessible for at least 35 years.

>
> You should substitute your own version and dates.  The other is the short
> form the AMA uses which, for SQLite, would be
>
> Hipp R et. al.. SQLite. North Carolina: SQLite Development Team; 2015.
>
> If you find the form of this which suits your publication better, please
> post it to this thread and I'll add it to the list.
>
> Richard, the 'author' requirements require the name of at least one specific
> person.  I don't know who else is on the development team so I've taken the
> liberty of using "Richard Hipp et. al." and "SQLite Development Team" which
> I guessed were closest to what you'd all want.  If you don't like them
> please don't hesitate to correct them.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How to correctly a add string to a Mem?

[Sairam Gaddam]

I have tried a method to create a Mem and add a string to it, which is as
below

 sqlite3VdbeMemInit(&(p->custom_aMem[0]), p->db, MEM_Null);

 memAboutToChange(p,&(p->custom_aMem[0]));

 sqlite3VdbeMemSetStr(&(p->custom_aMem[0]), zColumn ,
strlen(zColumn)*sizeof(char), SQLITE_UTF8, SQLITE_STATIC);

First I allocated space to Mem and initialized it using sqlite3VdbeMemInit(
) function.
Then I changed the Mem and set the value of the string to the Mem
using memAboutToChange(
) and sqlite3VdbeMemSetStr( ) respectively. Where zColumn is the string to
be added.

Is this the correct way to add data to Mem ???

Because when I add data using this method I experienced a memory leak when
executing large number of queries.

Can anyone kindly tell what is the correct way to add data to Mem ?

[sqlite] how to cite SQLite

[Richard Hipp]

On 7/13/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am writing a scientific paper to describe our research. To manage data, we
> use SQLite. I would like to acknowledge SQLite and cite it properly in the
> paper. Is there a suggested way of doing it? A conference presentation? A
> paper, a book? In the simplest form I will use URL.
>

I think just the URL:  https://www.sqlite.org/

If possible, please provide a link to your paper when it becomes available.  :-)

-- 
D. Richard Hipp
drh at sqlite.org

[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] How to correctly a add string to a Mem?

[Clemens Ladisch]

Sairam Gaddam wrote:
> I have tried a method to create a Mem and add a string to it, which is as
> below
>
>  sqlite3VdbeMemSetStr(&(p->custom_aMem[0]), zColumn , 
> strlen(zColumn)*sizeof(char), SQLITE_UTF8, SQLITE_STATIC);

This is an internal function that applications are not supposed to know
about.  There is no right way to use it because the interface can change
at any time.

Why are you trying to do this?


Regards,
Clemens

[sqlite] Suggestions for Fast Set Logic?

[Hick Gunter]

Consider creating a bitmapped index on the fields you expect to be queried the 
most. Bitmaps may be stored and manipulated (combined by logical operations) 
very efficiently as long as the field values conform to certain criteria 
(usually "managable cardinality of distinct values").

Bitmap indices are stored per column as opposed to regular database data which 
is stored per row. So a bitmap index on a field (a) with 4 discrete values 
(say, NULL, 1, 7 and 31) would be stored as 4 bitmaps (corresponding to the 
clauses "IS NULL", "= 1", "= 7" and "=31"). Or a bitmap index for defined 
keywords in a text (t) is stored as 1 bitmap for each keyword (corresponding to 
"LIKE '%key%'" for each value of key).

The combination of clauses (e.g. "a NOT NULL AND t LIKE '%key%' ") is 
blindingly fast, as the result can be determined for 32 (or even 64) rows at a 
time by simple bit arithmetics. And often even faster for bitmaps that compress 
sequences of "all 1" and "all 0" (because "all 1" AND A = A, "all 0" AND A = 
"all 0",  "all 0" OR B = B and "all 1" OR B = "all 1")

Bitmapped indices are best suited for large collections of mostly static data 
that are acquired once, rarely changed and queried a lot (there is a "fastbit" 
package developed specifically for searching through high energy physics 
experiment results).

-Urspr?ngliche Nachricht-
Von: Scott Robison [mailto:scott at casaderobison.com]
Gesendet: Montag, 13. Juli 2015 07:53
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Suggestions for Fast Set Logic?

On Sun, Jul 12, 2015 at 9:46 AM, James K. Lowden 
wrote:

> On Sat, 11 Jul 2015 19:02:59 -0600
> Scott Robison  wrote:
>
> > > I don't follow you.  A complex query is an assemblage of clauses.
> > > Whether or not broken down "to individual sub queries", the search
> > > arguments are the same.  They are few in kind, even though they may
> > > be large in number.  The analysis for what indexes to create will
> > > be the same either way.  In fact, Keith already proposed a
> > > plausible design.
> >
> > I'm just saying that knowing which index to create in advance is
> > impossible if the user can specify arbitrarily complex where clauses.
>
> In that case, I can make your job easier, starting today.  You don't
> need to consider query complexity to design your indexes.  :-)
>

{snipped}

You're making someone else's job easier (the OP), not mine. I'm certain I
could (if I cared to spend the time) find a case where the OP scenario
could be more efficiently performed with a set of individual queries that
are only filtered at the end. I don't know how large the data set would
need to be, or how many columns it would require, or how complex the where
clause would be. Just saying that it (almost certainly) exists. Also saying
that the OP scenario of individual queries isn't absolutely a *bad* way to
use SQLite, but there are many ways to skin this particular cat.

Note that *I* am not trying to provide this type of functionality. I'm
perfectly content, when using SQLite, to craft specific queries to get the
job done that can rely on one (or at most a few) specific CREATE INDEX
statements. Often the size of data I'm dealing with is small enough that
the difference between full scan and indexed access is irrelevant.

In any case, certainly the OP has been given plenty to think about and can
probably come up with an efficient way of using SQLite for this particular
use case. Perhaps some of the other suggestions have put the problem in a
different light, in which case monolithic queries can be constructed that
are sufficiently performant.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Question about getting size of stored data

[Hick Gunter]

AFAIKT you are attempting to determine the "size" of one row by the difference 
in the file size. This must fail, because SQLite allocates and writes the 
database file in units of "database pages".

Does your definition of "size" include the index entries pertaining to a row? 
Does it include the overhead of storing it in a b* tree? Does it include the 
overhead of storing blob data ?

The net size of a row (that does not contain large string and/or blob fields 
and excluding all overhead) is about

1 (the size of the header length varint) +
n (one one-byte varint per field) +
sum of the size of the varints for integer fields with an actual value other 
than NULL, 0 or 1 +
sum of the lengths of the actuial values of string/blob fields

longer string/blob values may add one or more bytes to the corresponding 
per-field varint, enough of those may add one or more bytes to the header 
length varint

So a record of 20 fields of all NULL/0/1  takes up at least 21 bytes (in 
INTEGER PRIMAY KEY or WITHOUT ROWID tables and a rowid of 0 or 1) NET but may 
use up to 201 bytes NET if all the fields contain sufficiently large integers.

-Urspr?ngliche Nachricht-
Von: m.g.001 [mailto:m.g.001 at web.de]
Gesendet: Dienstag, 30. Juni 2015 14:44
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Question about getting size of stored data

Hello,

I am new to this mailinglist. I think I have to send my question here.   My
Name is Michael and I come from Germany. At the moment I develope a few android 
apps for my study.
Is it possbile to get the size of one stored row inside my sqlite database?



I tried the following, but it returns different sizes every time. But my test 
data is the same so I think it should have the same size.



public long getObjectSize(MyData data)

{

if(getSize() == 0)
//because i don't want to divide by zero

{

insertOneFakeData(data);

}

long vorvorherdatasize = getDataSize();

long checkdatasize;

do
//this is for writing till the end of the current block size

{

insertOneFakeData(data);

checkdatasize = getDataSize();

}while(checkdatasize == vorvorherdatasize);

//dataSize changed, so size-1 should be the number ob rows stored in 
the old size.

long vorhersize = getSize()-1;
//number of elements before next write

long vorherdatasize = getDataSize(); //Size of the file before next 
write

do

{

insertOneFakeData(data);

checkdatasize = getDataSize();

}while(checkdatasize == vorherdatasize);

long nachherdatasize = checkdatasize;

long nachhersize = getSize()-1;
//number of rows before new block was reserved

long size = nachhersize - vorhersize; //number of elements in the last 
block

long datasize = vorherdatasize - vorvorherdatasize; //datasize oft he 
last block

long objectSize = datasize / size; //my
try to get the size of 1 row

return objectSize;

}



Only for completeness my other used methods:
public long getDataSize()//returns size of file in which my database is
stored

  {

  SQLiteDatabase db = this.getWritableDatabase();

  db.getPath();

  long size = new File(db.getPath()).length();

  return size;

  }



public long getSize() //returns number of rows in my database

{

  Log.i("ESmartDataManager","getSize");

SQLiteDatabase db = this.getWritableDatabase();

Cursor cursor = db.rawQuery("SELECT "

  + _ID + ", "

  + CANID + ", "

  + RTR +", "

  + IDE + ", "

  + DLC + ", "

  + TIMESTAMP + ", "

  + DATA

  + " FROM "

  + FAKETABLE_NAME , null);

  return cursor.getCount();

}







Can you help me to get the size of 1 row? Is it possible?



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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Suggestions for Fast Set Logic?

[Scott Robison]

On Sun, Jul 12, 2015 at 9:46 AM, James K. Lowden 
wrote:

> On Sat, 11 Jul 2015 19:02:59 -0600
> Scott Robison  wrote:
>
> > > I don't follow you.  A complex query is an assemblage of clauses.
> > > Whether or not broken down "to individual sub queries", the search
> > > arguments are the same.  They are few in kind, even though they may
> > > be large in number.  The analysis for what indexes to create will
> > > be the same either way.  In fact, Keith already proposed a
> > > plausible design.
> >
> > I'm just saying that knowing which index to create in advance is
> > impossible if the user can specify arbitrarily complex where clauses.
>
> In that case, I can make your job easier, starting today.  You don't
> need to consider query complexity to design your indexes.  :-)
>

{snipped}

You're making someone else's job easier (the OP), not mine. I'm certain I
could (if I cared to spend the time) find a case where the OP scenario
could be more efficiently performed with a set of individual queries that
are only filtered at the end. I don't know how large the data set would
need to be, or how many columns it would require, or how complex the where
clause would be. Just saying that it (almost certainly) exists. Also saying
that the OP scenario of individual queries isn't absolutely a *bad* way to
use SQLite, but there are many ways to skin this particular cat.

Note that *I* am not trying to provide this type of functionality. I'm
perfectly content, when using SQLite, to craft specific queries to get the
job done that can rely on one (or at most a few) specific CREATE INDEX
statements. Often the size of data I'm dealing with is small enough that
the difference between full scan and indexed access is irrelevant.

In any case, certainly the OP has been given plenty to think about and can
probably come up with an efficient way of using SQLite for this particular
use case. Perhaps some of the other suggestions have put the problem in a
different light, in which case monolithic queries can be constructed that
are sufficiently performant.

-- 
Scott Robison