Re: [sqlite] Do people think of SQLite as a file or as a database

[Nicolas Williams]

On Wed, Jul 15, 2009 at 07:11:51PM -0700, CadMapper wrote:
> 
> This is not a technical question about SQLite.  I want to you how people in
> general think about SQLite.  Is that a file or a database?  When you talk
> about it, do you refer to it as file or database?

It's a database.  It happens to be stored in a file (or, if you use
ATTACH, several files).

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

Re: [sqlite] Do people think of SQLite as a file or as a database

[Wes Freeman]

It's possible that the question was referring to this statement in the
About page on sqlite.org:
"SQLite is an embedded SQL database engine. Unlike most other SQL
databases, SQLite does not have a separate server process. SQLite
reads and writes directly to ordinary disk files. A complete SQL
database with multiple tables, indices, triggers, and views, is
contained in a single disk file. The database file format is
cross-platform - you can freely copy a database between 32-bit and
64-bit systems or between big-endian and little-endian architectures.
These features make SQLite a popular choice as an Application File
Format. Think of SQLite not as a replacement for Oracle but as a
replacement for fopen()"

I usually refer to the sqlite database files as databases.

Wes

On Wed, Jul 15, 2009 at 10:38 PM, Rich Shepard wrote:
> On Wed, 15 Jul 2009, CadMapper wrote:
>
>> This is not a technical question about SQLite. I want to you how people
>> in general think about SQLite. Is that a file or a database?  When you
>> talk about it, do you refer to it as file or database?
>
>   Neither. It is a database management system used to create, maintain, and
> query multiple databases. Each database is physically stored on the hard
> drive as a single file that contains the DDL, tables, data, indices, and so
> on.
>
>   A file is a storage unit. A database is a set of defined tables,
> attributes, rows, indices, relations, transactions, stored procedures, etc.
> all related to a common purpose.
>
>   Your question suggests a lack of understanding of database systems, SQL,
> and the separation of logical organization from physical storage on a
> device. I suggest that you do a bit of research on the Web about database
> management systems (DBMSs).
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.               |  Integrity            Credibility
> Applied Ecosystem Services, Inc.        |            Innovation
>      Voice: 503-667-4517      Fax: 503-667-8863
> ___
> 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] Do people think of SQLite as a file or as a database

[Rich Shepard]

On Wed, 15 Jul 2009, CadMapper wrote:

> This is not a technical question about SQLite. I want to you how people
> in general think about SQLite. Is that a file or a database?  When you
> talk about it, do you refer to it as file or database?

   Neither. It is a database management system used to create, maintain, and
query multiple databases. Each database is physically stored on the hard
drive as a single file that contains the DDL, tables, data, indices, and so
on.

   A file is a storage unit. A database is a set of defined tables,
attributes, rows, indices, relations, transactions, stored procedures, etc.
all related to a common purpose.

   Your question suggests a lack of understanding of database systems, SQL,
and the separation of logical organization from physical storage on a
device. I suggest that you do a bit of research on the Web about database
management systems (DBMSs).

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Do people think of SQLite as a file or as a database

[CadMapper]

This is not a technical question about SQLite.  I want to you how people in
general think about SQLite.  Is that a file or a database?  When you talk
about it, do you refer to it as file or database?

Thanks for your input in advance!
-- 
View this message in context: 
http://www.nabble.com/Do-people-think-of-SQLite-as-a-file-or-as-a-database-tp24508914p24508914.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Load a database stored into memory?

[Shinu]

Hi, I'm using  http://www.wischik.com/lu/programmer/zip_utils.html this class 
to unzip a database, but there is an option to store it directly into memory
(Example 3). Can I access it using the function "sqlite3_open"?
-- 
View this message in context: 
http://www.nabble.com/Load-a-database-stored-into-memory--tp24507516p24507516.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] FTS3 *_content tables (do I really need them?)

[Martin Pfeifle]

Dear all,
 
a few weeks ago, I asked the question below but did not get any response on it.
A few minutes ago,  I found a remark
(cf.  http://osdir.com/ml/freedesktop.tracker/2008-07/msg00085.html)
 that it might be possible to avoid the "redundant" storage of document 
information in the
fts *_content tables. 
 
Has anyone an implementation available for fts3 which does not store data in 
the fts3 *_content tables but only uses the *_segdir and *_segments tables
 
 
Best Martin


Dear all,     we plan to use FTS in embedded devices for address search.  One 
requirement is to save storage.     Assume I create a table FTS_addresses 
(Field1,Field,2,..Fieldn),  where Field1 is an identifier for my addresses.  If 
now field1 would be used as document id, and if every fts query returns only   
the field1 value,  we would not need to store the contenttable at all, which 
would save space.     Can we get such a functionality from fts3, or do we have 
to implement this on   our own.Best Martin  


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

Re: [sqlite] Database corrupt.

[Kees Nuyt]

On Wed, 15 Jul 2009 08:29:15 +0200, Denis Gottardello
 wrote:

>I have many cases of database corruption. 
>May depend on what? 

It shouldn't happen if you use it correctly.
This page is worth reading:
http://www.sqlite.org/atomiccommit.html

Please tell us the sqlite version, operation system, wrapper
(if any), and all the compiler options and PRAGMAs you have
applied.

>omeone using 
>sqlite in multi thread environment?
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing insert or replace speed

[Adam DeVita]

Also, very good.


No index on Delta File:
3 seconds.

Index on SN:
4 seconds.

Index on MasterList (write_out_ok, MFGID, TypeID, SN);
4 seconds.

Time is to the nearest second in my test program, so I can't distinguish
between the two.

In summary:

/*FAST */
insert or replace into main.masterlist select d.*  from delta.masterlist d
left outer join main.masterlist M on d.sn = M.sn
   where d.write_out_ok=0 and d.record_updatetime >=
ifnull(M.record_updatetime, '')

/* just as FAST*/
insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
not exists (select 1 from main.masterlist M  where M.sn = d.sn and
M.record_updatetime > d.record_updatetime);

/* very SLOW*/
insert or replace into main.masterlist select * from delta.masterlist d
where d.write_out_ok=0 and
d.sn not in(select M.sn from main.masterlist M where M.record_updatetime >
d.record_updatetime)



On Wed, Jul 15, 2009 at 7:33 AM, Igor Tandetnik  wrote:

> Adam DeVita wrote:
> > I've identified the following query as a bottle neck in a utility I've
> > written.
> >
> > insert or replace into main.masterlist select * from delta.masterlist
> > d where d.write_out_ok=0 and
> > d.sn not in(select M.sn from main.masterlist M where
> > M.record_updatetime > d.record_updatetime)
>
> Try this:
>
> insert or replace into main.masterlist
> select * from delta.masterlist d
> where d.write_out_ok=0 and
> not exists (select 1 from main.masterlist M
>  where M.sn = d.sn and M.record_updatetime > d.record_updatetime);
>
> It appears that your query doesn't use an index on M(sn), while mine
> does.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing insert or replace speed

[Igor Tandetnik]

Adam DeVita wrote:
> I've identified the following query as a bottle neck in a utility I've
> written.
>
> insert or replace into main.masterlist select * from delta.masterlist
> d where d.write_out_ok=0 and
> d.sn not in(select M.sn from main.masterlist M where
> M.record_updatetime > d.record_updatetime)

Try this:

insert or replace into main.masterlist
select * from delta.masterlist d
where d.write_out_ok=0 and
not exists (select 1 from main.masterlist M
  where M.sn = d.sn and M.record_updatetime > d.record_updatetime);

It appears that your query doesn't use an index on M(sn), while mine
does.

Igor Tandetnik 



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

[sqlite] Feature request: GiST index

[Alexey Pechnikov]

Hello!

There are a lot of tasks where GiST index is needed.
Does exists any plans to implement this?

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a table that has a field that can hold a 64-bit interger

[Simon Davies]

2009/7/15 Dieter Dasberg :
> Hello,
>
.
.
.
>
> This
>
> create table test (Afield INTEGER)
>
> leads always to a data-size of 4 byte,

What makes you think this?

See http://www.sqlite.org/datatype3.html

> what I need is a data-size of 8 byte.
>
> What can I do?
>
> Best regards,
>
> Dieter

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>  create table tst( data integer );
sqlite> insert into tst( data ) values( 1 );
sqlite> insert into tst( data ) values( 9223372036854775807 );
sqlite> insert into tst( data ) values( 9223372036854775808 );
sqlite>
sqlite>
sqlite> select data, typeof( data ) from tst;
1|integer
9223372036854775807|integer
9.22337203685478e+18|real
sqlite>

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

Re: [sqlite] Announce of the new "Versioning" extension

[Alexey Pechnikov]

Hello!

On Wednesday 15 July 2009 09:56:28 Neville Franks wrote:
> Hi Alexey,
> Thank you for this extension which could be quite interesting to many
> SQLite users. Is there any documentation on this available, possibly
> in your new book? I couldn't find any with the source at
> http://mobigroup.ru/files/sqlite-ext/ 

I did add README file.
 
> Also you have used the GNU License which means we cannot use this in
> any commercial applications. It also goes against the Public Domain
> license used by SQLite itself - see
> http://www.sqlite.org/copyright.html It would be great if this could
> be changed.

I did change license to Public Domain same as SQLite core and my other 
extensions.

P.S. Added indexes in new "Versioning" version.

 README =
"Versioning" SQLite extension

Copyright 2009 Alexey Pechnikov 
The code is public domain.


The extension may be used for table versioning and replication.

Functions:
versioning_table(SOURCE) - add versioning support for SOURCE table by
1. drop if exists previously created _versioning_SOURCE table
2. add _versioning_SOURCE table to store versions
4. copy current state of SOURCE table
3. create triggers on SOURCE table

unversioning_table(SOURCE) - remove versioning support for SOURCE table. 
Doesn't drop _versioning_SOURCE table but only remove SOURCE triggers!

The _versioning_SOURCE table consists all fields of SOURCE table without any 
checks or constraints and some additional fields
_date REAL, _action TEXT, _rowid INTEGER
to store date of perform action on SOURCE row, action name ('I' - insert, 'U' - 
update, 'D' - delete) and original record rowid.


Add versioning example:
CREATE TABLE key (name text not null);
select versioning_table('key');
.schema
CREATE TABLE _undo(sql TEXT, status TEXT);
CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid 
INTEGER);
CREATE TABLE key (name text not null);
CREATE INDEX versioning_key_date_idx on _versioning_key(_date);
CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date);
CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT INTO 
_versioning_key (_date, _action, _rowid) values (julianday('now'), 'D', 
old.rowid);END;
CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT INTO 
_versioning_key SELECT *, julianday('now') as _date, 'I' as _action, new.rowid 
as _rowid FROM key WHERE rowid=new.rowid;END;
CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT INTO 
_versioning_key SELECT *, julianday('now') as _date, 'U' as _action, new.rowid 
as _rowid FROM key WHERE rowid=new.rowid;END;


Versioning example:
insert into key (name) values ('test key 1');
insert into key (name) values ('test key 1');
delete from key;
.header on
select * from _versioning_key;
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
test key 1|2455027.87582772|I|2
|2455027.87709961|D|1
|2455027.87709961|D|2


Now you can select versions of SOURCE row by rowid:
.header on
select * from _versioning_key where _rowid=1;
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
|2455027.87709961|D|1


For replication can be selected versions of all records by current time which 
were changed after previous syncronization (1 hour ago, as example):
.header on
select * from _versioning_key where _date>julianday('now','-1 hour');
name|_date|_action|_rowid
test key 1|2455027.87582762|I|1
test key 1|2455027.87582772|I|2
|2455027.87709961|D|1
|2455027.87709961|D|2

These records may be synced by sql dump or by other ways.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to create a table that has a field that can hold a 64-bit interger

[Dieter Dasberg]

Hello,

 

I am using the SQLite3.dll with Delphi 2007 for development.

 

I try to create a table with an integer field, that can hold 64-bit integer
values.

I read and searched a lot of the archives, also googled around a good time,
but I did not find a hint.

 

This 

 

create table test (Afield INTEGER)

 

leads always to a data-size of 4 byte, what I need is a data-size of 8 byte.

 

What can I do?

 

Best regards,

 

Dieter

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

Re: [sqlite] Announce of the new "Versioning" extension

[Neville Franks]

Hi Alexey,
Thank you for this extension which could be quite interesting to many
SQLite users. Is there any documentation on this available, possibly
in your new book? I couldn't find any with the source at
http://mobigroup.ru/files/sqlite-ext/ 

Also you have used the GNU License which means we cannot use this in
any commercial applications. It also goes against the Public Domain
license used by SQLite itself - see
http://www.sqlite.org/copyright.html It would be great if this could
be changed.


Tuesday, July 14, 2009, 10:17:18 PM, you wrote:

AP> Hello!

AP> This may be used for table versioning and replication.

AP> Source code is available here
AP> http://mobigroup.ru/files/sqlite-ext/

AP> You can get from the debian repository the SQLite build with some extra 
extensions:
AP> deb http://mobigroup.ru/debian/ lenny main contrib non-free
AP> deb-src http://mobigroup.ru/debian/ lenny main contrib non-free

AP> =Test script==
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
AP> insert into sessions (key,value) values ('test key 0','test value 0');

AP> select versioning_table('sessions');
AP> .schema
AP> select * from _versioning_sessions;

AP> insert into sessions (key,value) values ('test key 1','test value 1');
AP> insert into sessions (key,value) values ('test key 2','test value 2');
AP> select * from _versioning_sessions;

AP> delete from sessions;
AP> select * from _versioning_sessions;

AP> select unversioning_table('sessions');
AP> .schema

AP> ==Test script results=
sqlite>> CREATE TABLE sessions (   
AP>...> key text not null,
AP>...> value text not null
AP>...> );
sqlite>> insert into sessions (key,value) values ('test key 0','test value 0');
sqlite>>
sqlite>> select versioning_table('sessions');

sqlite>> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_sessions(key text,value text, _date
AP> REAL, _action TEXT, _rowid INTEGER);
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
AP> CREATE TRIGGER _versioning_sessions_d AFTER DELETE ON sessions
AP> BEGIN INSERT INTO _versioning_sessions (_date, _action, _rowid)
AP> values (julianday('now'), 'D', old.rowid);END;
AP> CREATE TRIGGER _versioning_sessions_i AFTER INSERT ON sessions
AP> BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now')
AP> as _date, 'I' as _action, new.rowid as _rowid FROM sessions WHERE 
rowid=new.rowid;END;
AP> CREATE TRIGGER _versioning_sessions_u AFTER UPDATE ON sessions
AP> BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now')
AP> as _date, 'U' as _action, new.rowid as _rowid FROM sessions WHERE 
rowid=new.rowid;END;
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
sqlite>>
sqlite>> insert into sessions (key,value) values ('test key 1','test value 1');
sqlite>> insert into sessions (key,value) values ('test key 2','test value 2');
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
sqlite>>
sqlite>> delete from sessions;
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
AP> ||2455027.00753382|D|1
AP> ||2455027.00753382|D|2
AP> ||2455027.00753382|D|3
sqlite>>
sqlite>> select unversioning_table('sessions');

sqlite>> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_sessions(key text,value text, _date
AP> REAL, _action TEXT, _rowid INTEGER);
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
AP> ||2455027.00753382|D|1
AP> ||2455027.00753382|D|2
AP> ||2455027.00753382|D|3
AP> ==

AP> Best regards, Alexey Pechnikov.
AP> http://pechnikov.tel/
AP> ___
AP> sqlite-users mailing list
AP> sqlite-users@sqlite.org
AP> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

[sqlite] Database corrupt.

[Denis Gottardello]

I have many cases of database corruption. May depend on what? Someone using 
sqlite in multi thread environment?
-- 
Denis Gottardello
Sintesi S.r.l.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users