Re: [sqlite] Newb-ish performance questions

[Jay A. Kreibich]

On Fri, Feb 20, 2009 at 05:22:33AM +, Kim Boulton scratched on the wall:
> Hello,
> 
> I'm trying out Sqlite3 with an eye to improving the performance of 
> queries on an existing MySQL database.
> 
> I've imported the data into sqlite which is approx. 30 million rows of 
> part numbers each with a price.
> 
> So far, it's approx. four times slower than the MySQL version, and the 
> size of the sqlite database is too big to fit in memory (several GB) 
> whereas I can get the MySQL data down to 900MB if it's compressed and 
> read only.

  Make sure you have indexes on appropriate columns, like part-number or
  whatever your queries are commonly keyed off of.

  Bump up the page-cache size.  No matter what the size of the
  database, by default SQLite will only cache 2000 pages.  The default
  page size is 1K, and a cache slot takes about 1.5K, so adjust
  accordingly (like 10x or 100x) via PRAGMA if you're on a system with
  a comfortable amount of RAM.  If you're randomly pulling out
  individual records, the only thing you really care about is keeping
  the most important index(es) in the cache.
  
  Also, SQLite will not pre-load anything, so your first few queries are
  likely to be slower as it seeds the indexes into the data cache.


  I'm surprised about the size.  If you have a name or description
  column, make sure you're not using fixed-length strings for the import.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newb-ish performance questions

[Jim Dodgen]

was it 4 times slower to load? or 4 times slower to query?

also we need some examples.

On Thu, Feb 19, 2009 at 9:33 PM, Thomas Briggs  wrote:

>   Depending on the nature of the data and queries, increasing the
> block size may help.
>
>   Posting some information about your schema and queries is the only
> way to get truly good advice on this though, I think.  There is no
> "-runfast" switch you can include on the command line to fix things.
> :)  The answers are almost guaranteed to be found in your use of
> SQLite, not in the database itself.
>
>   -T
>
> On Fri, Feb 20, 2009 at 12:22 AM, Kim Boulton  wrote:
> > Hello,
> >
> > I'm trying out Sqlite3 with an eye to improving the performance of
> > queries on an existing MySQL database.
> >
> > I've imported the data into sqlite which is approx. 30 million rows of
> > part numbers each with a price.
> >
> > So far, it's approx. four times slower than the MySQL version, and the
> > size of the sqlite database is too big to fit in memory (several GB)
> > whereas I can get the MySQL data down to 900MB if it's compressed and
> > read only.
> >
> > I would appreciate some tips or pointers on getting sqlite3 performance
> > up and the data size down. I googled but couldn't find much.
> >
> > I don't need concurrency or inserts, it's single user, read only.
> >
> > TIA
> >
> > kimb
> >
> >
> >
> >
> > ___
> > 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
>



-- 
Jim Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newb-ish performance questions

[Thomas Briggs]

   Depending on the nature of the data and queries, increasing the
block size may help.

   Posting some information about your schema and queries is the only
way to get truly good advice on this though, I think.  There is no
"-runfast" switch you can include on the command line to fix things.
:)  The answers are almost guaranteed to be found in your use of
SQLite, not in the database itself.

   -T

On Fri, Feb 20, 2009 at 12:22 AM, Kim Boulton  wrote:
> Hello,
>
> I'm trying out Sqlite3 with an eye to improving the performance of
> queries on an existing MySQL database.
>
> I've imported the data into sqlite which is approx. 30 million rows of
> part numbers each with a price.
>
> So far, it's approx. four times slower than the MySQL version, and the
> size of the sqlite database is too big to fit in memory (several GB)
> whereas I can get the MySQL data down to 900MB if it's compressed and
> read only.
>
> I would appreciate some tips or pointers on getting sqlite3 performance
> up and the data size down. I googled but couldn't find much.
>
> I don't need concurrency or inserts, it's single user, read only.
>
> TIA
>
> kimb
>
>
>
>
> ___
> 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] Newb-ish performance questions

[Kim Boulton]

Hello,

I'm trying out Sqlite3 with an eye to improving the performance of 
queries on an existing MySQL database.

I've imported the data into sqlite which is approx. 30 million rows of 
part numbers each with a price.

So far, it's approx. four times slower than the MySQL version, and the 
size of the sqlite database is too big to fit in memory (several GB) 
whereas I can get the MySQL data down to 900MB if it's compressed and 
read only.

I would appreciate some tips or pointers on getting sqlite3 performance 
up and the data size down. I googled but couldn't find much.

I don't need concurrency or inserts, it's single user, read only.

TIA

kimb




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

Re: [sqlite] Any concept of row number in SQLite?

[His Nerdship]

Thanks Derrell,
I only need the ROWIDs for the duration of a user edit, and I won't be doing
a VACUUM while that is happening, so this sounds exactly what I need.
When I first populate the GUI grid I will make a separate array of ROWID's,
and these will be in the same order as the rows in the grid.  I can then
index into this array and do a SELECT based on that ROWID.

Problem solved.  Thanks a lot, guys.

Sholto
His Nerdship Pty Ltd


Derrell Lipman wrote:
> 
> On Thu, Feb 19, 2009 at 10:45 PM, His Nerdship
> wrote:
> 
>>
>> Thank you Thomas, that was most helpful.
>> I have just found the ROWID information
>> (http://sqlite.org/lang_createtable.html#rowid) - this is what I was
>> looking
>> for!  You have to know something exists before you can look for it
>> I take it this value will not change, even if the contents of its row
>> change?
>>
> 
> Be careful. Yes, that value *can* change if you depend on the implicit
> ROWID. Specifically, if you delete rows and then VACUUM the rows will be
> renumbered. You can avoid that with:
> 
>>
>> I can create my own INTEGER PRIMARY KEY column, which becomes an alias
>> for
>> the ROWID, but is there a way I can get the 'original' (I don't want to
>> add
>> a column at this stage)?
> 
> 
> If you use your own INTEGER PRIMARY KEY then it becomes the ROWID and it
> will not change upon vacuum.
> 
>>
>> If I call sqlite3_get_table(), it returns that ***result array (I'm using
>> C++), but AFAIK that doesn't contain the ROWID.
>>
> 
> Just request it explicitly:
> 
>   SELECT ROWID, * FROM table_name;
> 
> Derrell
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22114498.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Double entry bookkeeping

[John Stanton]

We are talking about different things.  The referennce ID is an ID 
assigned by the originator or the transaction, say an invoice number 
from a vendor or a check number from a bank whereas the cross reference 
is assigned by the application software and represents a key in an index 
on the table.

The points made by another poster are significant - not only is the 
design of an accounting database important from a technical perspective, 
it must also not violate the Doctrines of Accounting(consistency, 
disclosure etc).  Since transactions are a matter of legal record they 
cannot be changed or deleted, only added, and it should be possible mto 
prove that they cannot be changed.  The locally assigned cross reference 
number tagging the transaction set can also be used as an audit tool to 
prove the absence of deletions.


BareFeet wrote:
> Hi John,
> 
>> You still miss the point of the cross reference ID.  It is NOT the
>> reference ID od the document, such as an invoice or check number,  
>> but it
>> more like the row ID used by Sqlire as a unique key for a DB row.
> 
> I thought, from your explanation, that was what you were using the  
> "Reference ID" for, as you said:
> 
>>> reference ID to tag the particular complete transaction
> 
> Anyway, it doesn't matter since I think we're talking about the same  
> thing, but with different column names. If you want to clarify, it  
> would help if you posted a schema of the three tables and the  
> relationships between them.
> 
>> You could normalize out the date and have stored as associated with  
>> the cross reference.  The cost of that would be an extra row lookup  
>> each time you want to get the date of an accounting transaction.  A  
>> performance and code complexity decision.
> 
> It's also an SQL design issue which impacts performance and the  
> ability to extract different reports. De-normalized databases are  
> fundamentally a bad thing. You wouldn't, for instance, design a  
> meeting database where every meeting event explicitly store the name  
> and contact details of the person you were meeting. You'd store it in  
> a People table and refer each event to it. Linking the data through  
> "an extra row lookup" is trivial and optimized within the SQL engine.
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> 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] Any concept of row number in SQLite?

[Derrell Lipman]

On Thu, Feb 19, 2009 at 10:45 PM, His Nerdship
wrote:

>
> Thank you Thomas, that was most helpful.
> I have just found the ROWID information
> (http://sqlite.org/lang_createtable.html#rowid) - this is what I was
> looking
> for!  You have to know something exists before you can look for it
> I take it this value will not change, even if the contents of its row
> change?
>

Be careful. Yes, that value *can* change if you depend on the implicit
ROWID. Specifically, if you delete rows and then VACUUM the rows will be
renumbered. You can avoid that with:

>
> I can create my own INTEGER PRIMARY KEY column, which becomes an alias for
> the ROWID, but is there a way I can get the 'original' (I don't want to add
> a column at this stage)?


If you use your own INTEGER PRIMARY KEY then it becomes the ROWID and it
will not change upon vacuum.

>
> If I call sqlite3_get_table(), it returns that ***result array (I'm using
> C++), but AFAIK that doesn't contain the ROWID.
>

Just request it explicitly:

  SELECT ROWID, * FROM table_name;

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

Re: [sqlite] Any concept of row number in SQLite?

[His Nerdship]

Thank you Thomas, that was most helpful.
I have just found the ROWID information
(http://sqlite.org/lang_createtable.html#rowid) - this is what I was looking
for!  You have to know something exists before you can look for it
I take it this value will not change, even if the contents of its row
change?

One more question though (there always is) - I see a ROWID is automatically
assigned to each row in the table.  How does one access this value?  I know
I can create my own INTEGER PRIMARY KEY column, which becomes an alias for
the ROWID, but is there a way I can get the 'original' (I don't want to add
a column at this stage)?
If I call sqlite3_get_table(), it returns that ***result array (I'm using
C++), but AFAIK that doesn't contain the ROWID.

As Mañuel would say, ¿qué?

Sholto


Thomas Briggs-2 wrote:
> 
>"It won't be too big..." famous last words.
> 
>I think the rowid is probably safe for what you're trying to do,
> despite the well-intentioned advice others have given you against it.
> 
>Also, if you think the underlying data may change, then I'm not
> sure what good reading the whole table will give you... if the
> underlying data changes, row #28 the first time around may not be row
> #28 the second time around.  Even if you use an ORDER BY clause, which
> you'll absolutely have to do if you go the read-whole-table route.
> 
>What you might want to do is build a map between row number in your
> grid and rowid in the database... that way you aren't relying on the
> rowids being set a certain way, nor relying on the rowids being
> returned to your app in a particular order.
> 
>-T
> 
> On Thu, Feb 19, 2009 at 9:09 PM, His Nerdship
>  wrote:
>>
>> OK, thanks for the info.
>> I will just do what I said before, namely read the whole table (it won't
>> be
>> too big) and extract the required row from the returned array.
>> The reason I wanted a row ID was that all the fields in the display grid
>> can
>> be edited, so by the time I come to process it, any of them might have
>> changed from the original in the database so I can't use them in a WHERE
>> clause.
>> At least I know now
>> Thanks again
>>
>>
>> P Kishor-3 wrote:
>>>
>>> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
>>>  wrote:

 Hi,
 I am converting a program from Paradox (stop laughing, please) to
 SQLite.
 Paradox has a useful feature where you can specify the actual index of
 a
 row
 in the table.  This is handy when the table is displayed in a grid and
 you
 want the record corresponding to a row in that grid - you can just
 specify
 the index, say 28, of that grid row and it will get the record no 28
 from
 the table.  It spares the need for a SELECT statement, and is a lot
 more
 efficient.
 As a SQLite newbie, the only way I can see to do this is to read the
 whole
 table with sqlite3_get_table() and then get the required row from the
 returned array.  This seems overkill when I just want a single record.
>>>
>>> There is the rowid, but I am not sure what you want to do... are you
>>> expecting a database table to be a linear list of entries? Generally
>>> one uses a spreadsheet for that kind of stuff. A SQL database doesn't
>>> have an internal concept of order. You specify a criteria and the db
>>> returns a SET of rows or records. You can constrain the SET by
>>> specifying criteria (the WHERE clause), and you can impose an order on
>>> the returned rows by specifying an ORDER clause.
>>>
>>> If you want just one specific row, just do a
>>>
>>> SELECT cols FROM table WHERE some_primary_key = ?
>>>
>>> If you don't want to specify and control your own primary key, you can
>>> use the rowid which is something the db uses internally for its own
>>> shenanigans.
>>>
>>>
 Is there a more compact way of doing this?
 Thanks in advance etc.
 Sholto
>>>
>>>
>>>
>>> --
>>> Puneet Kishor http://www.punkish.org/
>>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>>> Sent from: Madison Wisconsin United States.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 

Re: [sqlite] Any concept of row number in SQLite?

[John Stanton]

Use the Sqlite row id.

His Nerdship wrote:
> Hi,
> I am converting a program from Paradox (stop laughing, please) to SQLite. 
> Paradox has a useful feature where you can specify the actual index of a row
> in the table.  This is handy when the table is displayed in a grid and you
> want the record corresponding to a row in that grid - you can just specify
> the index, say 28, of that grid row and it will get the record no 28 from
> the table.  It spares the need for a SELECT statement, and is a lot more
> efficient.
> As a SQLite newbie, the only way I can see to do this is to read the whole
> table with sqlite3_get_table() and then get the required row from the
> returned array.  This seems overkill when I just want a single record.
> Is there a more compact way of doing this?
> Thanks in advance etc.
> Sholto

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

Re: [sqlite] Any concept of row number in SQLite?

[Billy Gray]

Might I suggest using MD5 (or other) hashing algorithms in your application
as a way to check whether data in your application's memory is different
from what's in the database?

There are also locking mechanisms you can use to avoid (or create!)
concurrency problems.  If you're not worried about two processes operating
on the same row(s), this all shouldn't really be much of an issue for you.
In any event, I think you're looking to implement some means for your
application to tell whether the data is dirty or not, and ROWNUM or a
similar feature would not be more helpful than a primary key.

For the record, you can do a lot of convenient things using ROWNUM in Oracle
:-)

Cheers,
Billy

On Thu, Feb 19, 2009 at 9:09 PM, His Nerdship wrote:

>
> OK, thanks for the info.
> I will just do what I said before, namely read the whole table (it won't be
> too big) and extract the required row from the returned array.
> The reason I wanted a row ID was that all the fields in the display grid
> can
> be edited, so by the time I come to process it, any of them might have
> changed from the original in the database so I can't use them in a WHERE
> clause.
> At least I know now
> Thanks again
>
>
> P Kishor-3 wrote:
> >
> > On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
> >  wrote:
> >>
> >> Hi,
> >> I am converting a program from Paradox (stop laughing, please) to
> SQLite.
> >> Paradox has a useful feature where you can specify the actual index of a
> >> row
> >> in the table.  This is handy when the table is displayed in a grid and
> >> you
> >> want the record corresponding to a row in that grid - you can just
> >> specify
> >> the index, say 28, of that grid row and it will get the record no 28
> from
> >> the table.  It spares the need for a SELECT statement, and is a lot more
> >> efficient.
> >> As a SQLite newbie, the only way I can see to do this is to read the
> >> whole
> >> table with sqlite3_get_table() and then get the required row from the
> >> returned array.  This seems overkill when I just want a single record.
> >
> > There is the rowid, but I am not sure what you want to do... are you
> > expecting a database table to be a linear list of entries? Generally
> > one uses a spreadsheet for that kind of stuff. A SQL database doesn't
> > have an internal concept of order. You specify a criteria and the db
> > returns a SET of rows or records. You can constrain the SET by
> > specifying criteria (the WHERE clause), and you can impose an order on
> > the returned rows by specifying an ORDER clause.
> >
> > If you want just one specific row, just do a
> >
> > SELECT cols FROM table WHERE some_primary_key = ?
> >
> > If you don't want to specify and control your own primary key, you can
> > use the rowid which is something the db uses internally for its own
> > shenanigans.
> >
> >
> >> Is there a more compact way of doing this?
> >> Thanks in advance etc.
> >> Sholto
> >
> >
> >
> > --
> > Puneet Kishor http://www.punkish.org/
> > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> > Sent from: Madison Wisconsin United States.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.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
>



-- 
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any concept of row number in SQLite?

[Thomas Briggs]

   "It won't be too big..." famous last words.

   I think the rowid is probably safe for what you're trying to do,
despite the well-intentioned advice others have given you against it.

   Also, if you think the underlying data may change, then I'm not
sure what good reading the whole table will give you... if the
underlying data changes, row #28 the first time around may not be row
#28 the second time around.  Even if you use an ORDER BY clause, which
you'll absolutely have to do if you go the read-whole-table route.

   What you might want to do is build a map between row number in your
grid and rowid in the database... that way you aren't relying on the
rowids being set a certain way, nor relying on the rowids being
returned to your app in a particular order.

   -T

On Thu, Feb 19, 2009 at 9:09 PM, His Nerdship
 wrote:
>
> OK, thanks for the info.
> I will just do what I said before, namely read the whole table (it won't be
> too big) and extract the required row from the returned array.
> The reason I wanted a row ID was that all the fields in the display grid can
> be edited, so by the time I come to process it, any of them might have
> changed from the original in the database so I can't use them in a WHERE
> clause.
> At least I know now
> Thanks again
>
>
> P Kishor-3 wrote:
>>
>> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
>>  wrote:
>>>
>>> Hi,
>>> I am converting a program from Paradox (stop laughing, please) to SQLite.
>>> Paradox has a useful feature where you can specify the actual index of a
>>> row
>>> in the table.  This is handy when the table is displayed in a grid and
>>> you
>>> want the record corresponding to a row in that grid - you can just
>>> specify
>>> the index, say 28, of that grid row and it will get the record no 28 from
>>> the table.  It spares the need for a SELECT statement, and is a lot more
>>> efficient.
>>> As a SQLite newbie, the only way I can see to do this is to read the
>>> whole
>>> table with sqlite3_get_table() and then get the required row from the
>>> returned array.  This seems overkill when I just want a single record.
>>
>> There is the rowid, but I am not sure what you want to do... are you
>> expecting a database table to be a linear list of entries? Generally
>> one uses a spreadsheet for that kind of stuff. A SQL database doesn't
>> have an internal concept of order. You specify a criteria and the db
>> returns a SET of rows or records. You can constrain the SET by
>> specifying criteria (the WHERE clause), and you can impose an order on
>> the returned rows by specifying an ORDER clause.
>>
>> If you want just one specific row, just do a
>>
>> SELECT cols FROM table WHERE some_primary_key = ?
>>
>> If you don't want to specify and control your own primary key, you can
>> use the rowid which is something the db uses internally for its own
>> shenanigans.
>>
>>
>>> Is there a more compact way of doing this?
>>> Thanks in advance etc.
>>> Sholto
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org/
>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
>> Sent from: Madison Wisconsin United States.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
> View this message in context: 
> http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any concept of row number in SQLite?

[Rich Shepard]

On Thu, 19 Feb 2009, His Nerdship wrote:

> I will just do what I said before, namely read the whole table (it won't
> be too big) and extract the required row from the returned array. The
> reason I wanted a row ID was that all the fields in the display grid can
> be edited, so by the time I come to process it, any of them might have
> changed from the original in the database so I can't use them in a WHERE
> clause.

   I've no idea what language you're using for your application, but what you
describe is not always the case. I've an application written in python that
uses the pysqlite2 middleware. I can fetch a single row, examine it to
determine if it meets the criteria for selection, then fetch the next
record. Records meeting selection criteria are added to a list and displayed
in the appropriate widgets.

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

Re: [sqlite] Any concept of row number in SQLite?

[His Nerdship]

OK, thanks for the info.
I will just do what I said before, namely read the whole table (it won't be
too big) and extract the required row from the returned array.
The reason I wanted a row ID was that all the fields in the display grid can
be edited, so by the time I come to process it, any of them might have
changed from the original in the database so I can't use them in a WHERE
clause.
At least I know now
Thanks again


P Kishor-3 wrote:
> 
> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
>  wrote:
>>
>> Hi,
>> I am converting a program from Paradox (stop laughing, please) to SQLite.
>> Paradox has a useful feature where you can specify the actual index of a
>> row
>> in the table.  This is handy when the table is displayed in a grid and
>> you
>> want the record corresponding to a row in that grid - you can just
>> specify
>> the index, say 28, of that grid row and it will get the record no 28 from
>> the table.  It spares the need for a SELECT statement, and is a lot more
>> efficient.
>> As a SQLite newbie, the only way I can see to do this is to read the
>> whole
>> table with sqlite3_get_table() and then get the required row from the
>> returned array.  This seems overkill when I just want a single record.
> 
> There is the rowid, but I am not sure what you want to do... are you
> expecting a database table to be a linear list of entries? Generally
> one uses a spreadsheet for that kind of stuff. A SQL database doesn't
> have an internal concept of order. You specify a criteria and the db
> returns a SET of rows or records. You can constrain the SET by
> specifying criteria (the WHERE clause), and you can impose an order on
> the returned rows by specifying an ORDER clause.
> 
> If you want just one specific row, just do a
> 
> SELECT cols FROM table WHERE some_primary_key = ?
> 
> If you don't want to specify and control your own primary key, you can
> use the rowid which is something the db uses internally for its own
> shenanigans.
> 
> 
>> Is there a more compact way of doing this?
>> Thanks in advance etc.
>> Sholto
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> Sent from: Madison Wisconsin United States.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John (Machin),

Thanks for the discussion.

>> I understand that double entry bookkeeping traditionally uses the  
>> redundancy as an error check, but that seems more appropriate for  
>> manual paper systems, since computer systems can validate entries  
>> automatically.
>
> That's just fine and dandy at data entry time. However do please  
> consider that one ill-considered action by a support programmer can  
> cause a whole lot more damage a whole lot faster than a book-keeper  
> with a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets  
> you detect errors. With sufficient redundancy you may even be able  
> to correct errors.

True, but redundancy within an SQL schema is a bad thing. There are  
other ways to cater for redundancy, such as backups, and other ways to  
prevent entry errors such as triggers and contraints. The SQL schema  
should be a tight normalized model.

Having said that, however, I think that storing the Amount of each  
Entry within a Transaction is inevitable from a design perspective. I  
just wondered if anyone had come up with a design that is fully  
normalized.

>> All Accounting Entries in the same Transaction should sum their  
>> Amounts to be zero.
>
> Oh. I thought you wanted to store only n-1 amounts and  
> "deduce" (i.e. guess) the last one.

It was more of a wonder than a want ;-) Since every stored double  
entry transaction will sum its Amounts to zero, we can deduce the nth  
Amount by knowing the other Amounts, which technically violates  
normalization (Third Normal Form). So I wondered if anyone had come up  
with a schema that practically implemented this. I presume not, so  
will continue with the n Amounts stored for each Transaction.

> Some people would go into severe shock-horror mode at the suggestion  
> of
> "updating" the transaction date after it was entered. They would  
> prefer
> to reverse out [not delete!] the presumably erroneous transaction and
> write a correct one.

Do you mean, like an audit trail of changes? That's fine. I am just  
boiling the schema down to the bare essentials here to focus  
discussion. Catering for logging of changes is a great idea and  
practice, but just outside the scope of the basic schema to which I'm  
referring. Whether an update initiates an overwrite of data or a  
"reverse out", it would still have to be done for each Date  
redundantly, if the Date was stored for each Entry within a  
transaction. Since by definition all Entries in a Transaction occur on  
the same Date, it would be best to store it once once for that  
Transaction.

> Concerns about "wasted storage" were understandable when a DASD held
> (say) 50Mb and was the size of a washing machine. That's a long time  
> ago.

Again, it's not a case of "we have space so let's waste it". That  
would be like storing a person's contact details in every event in am  
Event table. It doesn't matter if you have a terabyte of space, it's  
just bad design, creates many problems and defeats the whole purpose   
of a relational database.

For reference to Normalization, see here:


>> I found this:
>> Modeling Business Rules: Data Driven Business Rules
>> http://www.tdan.com/view-articles/5227
>> which, seems similar to the route I was taking. If I'm interpreting  
>> it correctly, the TDAN article suggests a relationship between  
>> Accounting Entry and Accounting Transaction:
>>
>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;


> Do you mean Amount is always non-negative and Operator tells you  
> whether it is DR or CR?

That schema was my interpretation of the design discussion page above.  
There, yes, they use Operator as plus or minus with always a positive  
Amount. I think that's unnecessary and confusing, so I would instead  
opt for an Amount that can be positive or negative, eliminating the  
Operator column. The data can still be presented to the user in Debit  
and Credit columns that only have positive values, but the storage can  
be signed. Any thoughts/agreement/disagreement on that?

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any concept of row number in SQLite?

[Alex Mandel]

P Kishor wrote:
> On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
>  wrote:
>> Hi,
>> I am converting a program from Paradox (stop laughing, please) to SQLite.
>> Paradox has a useful feature where you can specify the actual index of a row
>> in the table.  This is handy when the table is displayed in a grid and you
>> want the record corresponding to a row in that grid - you can just specify
>> the index, say 28, of that grid row and it will get the record no 28 from
>> the table.  It spares the need for a SELECT statement, and is a lot more
>> efficient.
>> As a SQLite newbie, the only way I can see to do this is to read the whole
>> table with sqlite3_get_table() and then get the required row from the
>> returned array.  This seems overkill when I just want a single record.
> 
> There is the rowid, but I am not sure what you want to do... are you
> expecting a database table to be a linear list of entries? Generally
> one uses a spreadsheet for that kind of stuff. A SQL database doesn't
> have an internal concept of order. You specify a criteria and the db
> returns a SET of rows or records. You can constrain the SET by
> specifying criteria (the WHERE clause), and you can impose an order on
> the returned rows by specifying an ORDER clause.
> 
> If you want just one specific row, just do a
> 
> SELECT cols FROM table WHERE some_primary_key = ?
> 
> If you don't want to specify and control your own primary key, you can
> use the rowid which is something the db uses internally for its own
> shenanigans.
> 
> 
>> Is there a more compact way of doing this?
>> Thanks in advance etc.
>> Sholto
> 
> 
> 

The behavior you want sounds to me like a table is treated as an object,
and the object has a cursor or specific property for each record. It
would make sense if you want to reference a record in this way that you
would need to load the whole table into some type of array or list and
cursor your way to that object.

My guess is the application you used before abstracted the table to an
object in the way that SQL Alchemy turns tables into objects for python.
This is the object oriented programming approach to the issue, the most
efficient sql way would be to do a SELECT statement which for some
reason you don't want to do.

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

Re: [sqlite] Any concept of row number in SQLite?

[Rich Shepard]

On Thu, 19 Feb 2009, P Kishor wrote:

> There is the rowid, but I am not sure what you want to do... are you
> expecting a database table to be a linear list of entries? Generally one
> uses a spreadsheet for that kind of stuff. A SQL database doesn't have an
> internal concept of order. You specify a criteria and the db returns a SET
> of rows or records. You can constrain the SET by specifying criteria (the
> WHERE clause), and you can impose an order on the returned rows by
> specifying an ORDER clause.

   And, there is no particular order of rows in the table. They can be
re-ordered behind the scenes when the db engine determines benefits to the
change. That's why it's never a good idea to use row IDs.

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

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

> You still miss the point of the cross reference ID.  It is NOT the
> reference ID od the document, such as an invoice or check number,  
> but it
> more like the row ID used by Sqlire as a unique key for a DB row.

I thought, from your explanation, that was what you were using the  
"Reference ID" for, as you said:

>> reference ID to tag the particular complete transaction

Anyway, it doesn't matter since I think we're talking about the same  
thing, but with different column names. If you want to clarify, it  
would help if you posted a schema of the three tables and the  
relationships between them.

> You could normalize out the date and have stored as associated with  
> the cross reference.  The cost of that would be an extra row lookup  
> each time you want to get the date of an accounting transaction.  A  
> performance and code complexity decision.

It's also an SQL design issue which impacts performance and the  
ability to extract different reports. De-normalized databases are  
fundamentally a bad thing. You wouldn't, for instance, design a  
meeting database where every meeting event explicitly store the name  
and contact details of the person you were meeting. You'd store it in  
a People table and refer each event to it. Linking the data through  
"an extra row lookup" is trivial and optimized within the SQL engine.

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any concept of row number in SQLite?

[P Kishor]

On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship
 wrote:
>
> Hi,
> I am converting a program from Paradox (stop laughing, please) to SQLite.
> Paradox has a useful feature where you can specify the actual index of a row
> in the table.  This is handy when the table is displayed in a grid and you
> want the record corresponding to a row in that grid - you can just specify
> the index, say 28, of that grid row and it will get the record no 28 from
> the table.  It spares the need for a SELECT statement, and is a lot more
> efficient.
> As a SQLite newbie, the only way I can see to do this is to read the whole
> table with sqlite3_get_table() and then get the required row from the
> returned array.  This seems overkill when I just want a single record.

There is the rowid, but I am not sure what you want to do... are you
expecting a database table to be a linear list of entries? Generally
one uses a spreadsheet for that kind of stuff. A SQL database doesn't
have an internal concept of order. You specify a criteria and the db
returns a SET of rows or records. You can constrain the SET by
specifying criteria (the WHERE clause), and you can impose an order on
the returned rows by specifying an ORDER clause.

If you want just one specific row, just do a

SELECT cols FROM table WHERE some_primary_key = ?

If you don't want to specify and control your own primary key, you can
use the rowid which is something the db uses internally for its own
shenanigans.


> Is there a more compact way of doing this?
> Thanks in advance etc.
> Sholto



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison Wisconsin United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Any concept of row number in SQLite?

[His Nerdship]

Hi,
I am converting a program from Paradox (stop laughing, please) to SQLite. 
Paradox has a useful feature where you can specify the actual index of a row
in the table.  This is handy when the table is displayed in a grid and you
want the record corresponding to a row in that grid - you can just specify
the index, say 28, of that grid row and it will get the record no 28 from
the table.  It spares the need for a SELECT statement, and is a lot more
efficient.
As a SQLite newbie, the only way I can see to do this is to read the whole
table with sqlite3_get_table() and then get the required row from the
returned array.  This seems overkill when I just want a single record.
Is there a more compact way of doing this?
Thanks in advance etc.
Sholto
-- 
View this message in context: 
http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22112862.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Double entry bookkeeping

[John Stanton]

You still miss the point of the cross reference ID.  It is NOT the 
reference ID od the document, such as an invoice or check number, but it 
more like the row ID used by Sqlire as a unique key for a DB row.

You could normalize out the date and have stored as associated with the 
cross reference.  The cost of that would be an extra row lookup each 
time you want to get the date of an accounting transaction.  A 
performance and code complexity decision.  As they say "you pays your 
money and you takes your choice".

BareFeet wrote:
> Hi John,
> 
> Thanks again for your discussion.
> 
>> "Double Entry" book keeping is actually a misnomer.  A transaction  
>> is very likely to have more than two entries.
> 
> Yes, I realize that, though most Transactions generally have just two.  
> In any case, if there are n entries in a Transaction, we only need n-1  
> of the Entry amounts in order to deduce the remaining amount. SQL (or  
> more generally, set theory) normalization suggests that explicitly  
> storing a redundant record (in this case, the nth amount) is not  
> ideal. I understand that double entry bookkeeping traditionally uses  
> the redundancy as an error check, but that seems more appropriate for  
> manual paper systems, since computer systems can validate entries  
> automatically. So I wondered if there's a normalized approach to  
> double entry bookkeeping.
> 
>> The "transaction ID" I mentioned is a locally generated reference ID  
>> to tag the particular complete transaction, which might include  
>> postings to a bank account, sales, cost of sales, inventory etc.  It  
>> is not conventionally used, but confers great advantages when it is  
>> implemented.  It lets you locate the balancing entries for any  
>> accounting transaction.  In other words given any element of an  
>> accounting transaction you can complete the T Account.
> 
> I think you actually called this the "Cross Reference" and I called it  
> "Transaction ID". But, yes, I understand using it in each Accounting  
> Entry that is part of the same Transaction, in order to group them  
> together. All Accounting Entries in the same Transaction should sum  
> their Amounts to be zero.
> 
>>> You seem to suggest storing the date for each entry within a  
>>> transaction. Would it not be better to store it once only for the  
>>> transaction as a whole, since each entry will share that same date?
> 
>> Dating each accounting transaction aids in creating a unique ID.
> 
> I understand the need to date a Transaction as a whole (therefore  
> storing the Date in the Transaction table), but it seems redundant and  
> denormalized to store the same date in each constituent Accounting  
> Entry. It doesn't make any of the Accounting Entries more unique  
> (since they all by nature have the same date).
> 
>> Normalizing it out is nor necessarily an advantage.
> 
> I can see disadvantages of requiring multiple updating (changing the  
> date in one Accounting Entry requires updating the Date for all other  
> Accounting Entries that are part of the same Transaction), wasted  
> storage etc. I can't see any advantages.
> 
> Or perhaps you actually also meant to imply that Date should be stored  
> once for the Transaction (not for each Entry within it)?
> 
>> It has been my observation that accounting data is generally stored  
>> in poorly thought-out data structures which then require layer upon  
>> layer of logic to overcome the deficiencies.
> 
> Thanks then for your insight into the matter :-) I want to get it  
> right from the outset. In my experience poor normalization  
> necessitates layers of fixes down the road.
> 
> Here is the basic schema from before. I welcome any direct suggestions  
> about the schema:
> 
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus
>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> 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] Double entry bookkeeping

[John Machin]

On 20/02/2009 9:23 AM, BareFeet wrote:
> Hi John,
> 
> Thanks again for your discussion.
> 
>> "Double Entry" book keeping is actually a misnomer.  A transaction  
>> is very likely to have more than two entries.
> 
> Yes, I realize that, though most Transactions generally have just two.  
> In any case, if there are n entries in a Transaction, we only need n-1  
> of the Entry amounts in order to deduce the remaining amount. SQL (or  
> more generally, set theory) normalization suggests that explicitly  
> storing a redundant record (in this case, the nth amount) is not  
> ideal. I understand that double entry bookkeeping traditionally uses  
> the redundancy as an error check, but that seems more appropriate for  
> manual paper systems, since computer systems can validate entries  
> automatically.

That's just fine and dandy at data entry time. However do please 
consider that one ill-considered action by a support programmer can 
cause a whole lot more damage a whole lot faster than a book-keeper with 
a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets you 
detect errors. With sufficient redundancy you may even be able to 
correct errors.

> So I wondered if there's a normalized approach to  
> double entry bookkeeping.
> 
>> The "transaction ID" I mentioned is a locally generated reference ID  
>> to tag the particular complete transaction, which might include  
>> postings to a bank account, sales, cost of sales, inventory etc.  It  
>> is not conventionally used, but confers great advantages when it is  
>> implemented.  It lets you locate the balancing entries for any  
>> accounting transaction.  In other words given any element of an  
>> accounting transaction you can complete the T Account.
> 
> I think you actually called this the "Cross Reference" and I called it  
> "Transaction ID". But, yes, I understand using it in each Accounting  
> Entry that is part of the same Transaction, in order to group them  
> together. All Accounting Entries in the same Transaction should sum  
> their Amounts to be zero.

Oh. I thought you wanted to store only n-1 amounts and "deduce" (i.e. 
guess) the last one.

> 
>>> You seem to suggest storing the date for each entry within a  
>>> transaction. Would it not be better to store it once only for the  
>>> transaction as a whole, since each entry will share that same date?
> 
>> Dating each accounting transaction aids in creating a unique ID.
> 
> I understand the need to date a Transaction as a whole (therefore  
> storing the Date in the Transaction table), but it seems redundant and  
> denormalized to store the same date in each constituent Accounting  
> Entry. It doesn't make any of the Accounting Entries more unique  
> (since they all by nature have the same date).
> 
>> Normalizing it out is nor necessarily an advantage.
> 
> I can see disadvantages of requiring multiple updating (changing the  
> date in one Accounting Entry requires updating the Date for all other  
> Accounting Entries that are part of the same Transaction), wasted  
> storage etc. I can't see any advantages.

Some people would go into severe shock-horror mode at the suggestion of 
"updating" the transaction date after it was entered. They would prefer 
to reverse out [not delete!] the presumably erroneous transaction and 
write a correct one.

Concerns about "wasted storage" were understandable when a DASD held 
(say) 50Mb and was the size of a washing machine. That's a long time ago.

> 
> Or perhaps you actually also meant to imply that Date should be stored  
> once for the Transaction (not for each Entry within it)?
> 
>> It has been my observation that accounting data is generally stored  
>> in poorly thought-out data structures which then require layer upon  
>> layer of logic to overcome the deficiencies.
> 
> Thanks then for your insight into the matter :-) I want to get it  
> right from the outset. In my experience poor normalization  
> necessitates layers of fixes down the road.
> 
> Here is the basic schema from before. I welcome any direct suggestions  
> about the schema:
> 
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus

Do you mean Amount is always non-negative and Operator tells you whether 
it is DR or CR?


>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

___
sqlite-users mailing 

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

Thanks again for your discussion.

> "Double Entry" book keeping is actually a misnomer.  A transaction  
> is very likely to have more than two entries.

Yes, I realize that, though most Transactions generally have just two.  
In any case, if there are n entries in a Transaction, we only need n-1  
of the Entry amounts in order to deduce the remaining amount. SQL (or  
more generally, set theory) normalization suggests that explicitly  
storing a redundant record (in this case, the nth amount) is not  
ideal. I understand that double entry bookkeeping traditionally uses  
the redundancy as an error check, but that seems more appropriate for  
manual paper systems, since computer systems can validate entries  
automatically. So I wondered if there's a normalized approach to  
double entry bookkeeping.

> The "transaction ID" I mentioned is a locally generated reference ID  
> to tag the particular complete transaction, which might include  
> postings to a bank account, sales, cost of sales, inventory etc.  It  
> is not conventionally used, but confers great advantages when it is  
> implemented.  It lets you locate the balancing entries for any  
> accounting transaction.  In other words given any element of an  
> accounting transaction you can complete the T Account.

I think you actually called this the "Cross Reference" and I called it  
"Transaction ID". But, yes, I understand using it in each Accounting  
Entry that is part of the same Transaction, in order to group them  
together. All Accounting Entries in the same Transaction should sum  
their Amounts to be zero.

>> You seem to suggest storing the date for each entry within a  
>> transaction. Would it not be better to store it once only for the  
>> transaction as a whole, since each entry will share that same date?

> Dating each accounting transaction aids in creating a unique ID.

I understand the need to date a Transaction as a whole (therefore  
storing the Date in the Transaction table), but it seems redundant and  
denormalized to store the same date in each constituent Accounting  
Entry. It doesn't make any of the Accounting Entries more unique  
(since they all by nature have the same date).

> Normalizing it out is nor necessarily an advantage.

I can see disadvantages of requiring multiple updating (changing the  
date in one Accounting Entry requires updating the Date for all other  
Accounting Entries that are part of the same Transaction), wasted  
storage etc. I can't see any advantages.

Or perhaps you actually also meant to imply that Date should be stored  
once for the Transaction (not for each Entry within it)?

> It has been my observation that accounting data is generally stored  
> in poorly thought-out data structures which then require layer upon  
> layer of logic to overcome the deficiencies.

Thanks then for your insight into the matter :-) I want to get it  
right from the outset. In my experience poor normalization  
necessitates layers of fixes down the road.

Here is the basic schema from before. I welcome any direct suggestions  
about the schema:

>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;
>> create table "Accounting Transaction"
>> (
>>ID
>>  , Date
>>  , Description
>> )
>> ;
>> create table Account
>> (
>>ID
>>  , Description
>> )
>> ;

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

"Double Entry" book keeping is actually a misnomer.  A transaction is 
very likely to have more than two entries.  The "transaction ID" I 
mentioned is a locally generated reference ID to tag the particular 
complete transaction, which might include postings to a bank account, 
sales, cost of sales, inventory etc.  It is not conventionally used, but 
confers great advantages when it is implemented.  It lets you locate the 
balancing entries for any accounting transaction.  In other words given 
any element of an accounting transaction you can complete the T Account.

Dating each accounting transaction aids in creating a unique ID. 
Normalizing it out is nor necessarily an advantage.

It has been my observation that accounting data is generally stored in 
poorly thought-out data structures which then require layer upon layer 
of logic to overcome the deficiencies.



BareFeet wrote:
> Hi John,
> 
> Thanks for the input.
> 
>> In general you need for one transaction -
>> General Ledger account
>> Date
>> Reference ID
>> Cross Reference
>> Amount
>> Optional narrative
>>
>> The Chart of Accounts defines full set of accounts and the details of
>> each account.
> 
> OK, that seems very similar to the schema I offered before (appended  
> below), with some different names:
> 
> General Ledger account  = Account ID
> Date= Date
> Reference ID
> Cross Reference = Transaction ID
> Amount  = Amount
> Optional narrative  = Description
> 
> I assume that your Reference ID is for references such as cheque  
> number, invoice number etc, but linked outside of this basic schema.
> 
> You seem to suggest storing the date for each entry within a  
> transaction. Would it not be better to store it once only for the  
> transaction as a whole, since each entry will share that same date?
> 
>> If each set of entries is an atomic transaction which balances  
>> debits and credits then the ledger is always balanced.
> 
> Yes, I had concluded the same, since it's kind of the definition of  
> double entry bookkeeping. I had wondered, though, since most  
> transactions involve two entries for the same amount but for two  
> different accounts, whether storing that same amount twice would not  
> only be redundant but actually de-normalized from an SQL point of  
> view. Is it preferable to have a schema where the amount is only  
> stored once in those circumstances?
> 
>> Having a separate index linking balancing transactions makes  
>> displaying
>> accounting activity much clearer.  You could call the cross  
>> reference a
>> "journal ID" or a similar name, since it has some functional  
>> similarity
>> to the traditional bookkeeper's general journal.
> 
> Your cross reference or "Journal ID" seems the same as the  
> "Transaction ID" I included in my earlier schema.
> 
>> Make sure that you insert each set of accounting transactions as one
>> Sqlite transaction which rolls back should it fail to successfully
>> complete, thus maintaining the balance of the ledger.
> 
> Good tip :-)
> 
>>> I found this:
>>> Modeling Business Rules: Data Driven Business Rules
>>> http://www.tdan.com/view-articles/5227
>>> which, seems similar to the route I was taking. If I'm interpreting  
>>> it
>>> correctly, the TDAN article suggests a relationship between  
>>> Accounting
>>> Entry and Accounting Transaction:
>>>
>>> create table "Accounting Entry"
>>> (
>>>   ID
>>> , "Transaction ID"  --> "Accounting Transaction".ID
>>> , "Account ID"  --> Account.ID
>>> , Amount
>>> , Operator  -- plus or minus
>>> )
>>> ;
>>> create table "Accounting Transaction"
>>> (
>>>   ID
>>> , Date
>>> , Description
>>> )
>>> ;
>>> create table Account
>>> (
>>>   ID
>>> , Description
>>> )
>>> ;
>>>
> 
> Thanks,
> Tom
> BareFeet
> 
>   --
> Comparison of SQLite GUI tools:
> http://www.tandb.com.au/sqlite/compare/?ml
> ___
> 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] Mixing SQlite DLL versions

[D. Richard Hipp]

On Feb 18, 2009, at 10:00 PM, Jay A. Kreibich wrote:

> On Wed, Feb 18, 2009 at 05:33:50PM -0500, Igor Tandetnik scratched  
> on the wall:
>> jose isaias cabrera 
>> wrote:
>>> Greetings and salutations.
>>>
>>> Can one SQLite db be UPDATEd and used by folks using different DLL
>>> versions?
>>
>> All 3.* versions use the same file format, and can happily coexist.
>
>  Not exactly.  The file format changed with 3.3.0.  Files made with
>  3.3.0 and later cannot be read by earlier 3.x versions.  Older files
>  can be read by newer versions, however.  That was over three years
>  ago, however.


Version 3.0.0 will read and write database files created with all  
later versions of SQLite provided that the database file does not  
contain features that were added after 3.0.0.  For example, the  
CURRENT_DATE keyword was added in 3.1.0.  So if your schema contains a  
default value of CURRENT_DATE, it won't be readable by 3.0.8 or  
earlier.  For another example, the INDEXED BY clause was added in  
version 3.6.4.  If your schema contains a view that uses INDEXED BY,  
then your database will not be readable by 3.6.3 or earlier.

Version 3.3.0 introduced support for descending indices.  Descending  
indices are turned off by default, for backwards compatibility.  You  
have to enable them using PRAGMA legacy_file_format=OFF.  Assuming you  
leave descending indices turned off, a database file created by 3.6.11  
should be readable and writable by 3.0.0.

Versions 3.3.0 through 3.3.6 had descending indices turned on by  
default. Version 3.3.7 through the current have descending indices  
turned off for compatibility.  (Versions 3.3.0 through 3.3.6 created a  
lot of problems - we learned our lession.)  Note, however, that I am  
itching to turn descending indices on by default.  As soon as it seems  
like all pre-3.3.0 versions of SQLite have been upgraded, I will  
probably do so.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Double entry bookkeeping

[BareFeet]

Hi John,

Thanks for the input.

> In general you need for one transaction -
> General Ledger account
> Date
> Reference ID
> Cross Reference
> Amount
> Optional narrative
>
> The Chart of Accounts defines full set of accounts and the details of
> each account.

OK, that seems very similar to the schema I offered before (appended  
below), with some different names:

General Ledger account  = Account ID
Date= Date
Reference ID
Cross Reference = Transaction ID
Amount  = Amount
Optional narrative  = Description

I assume that your Reference ID is for references such as cheque  
number, invoice number etc, but linked outside of this basic schema.

You seem to suggest storing the date for each entry within a  
transaction. Would it not be better to store it once only for the  
transaction as a whole, since each entry will share that same date?

> If each set of entries is an atomic transaction which balances  
> debits and credits then the ledger is always balanced.

Yes, I had concluded the same, since it's kind of the definition of  
double entry bookkeeping. I had wondered, though, since most  
transactions involve two entries for the same amount but for two  
different accounts, whether storing that same amount twice would not  
only be redundant but actually de-normalized from an SQL point of  
view. Is it preferable to have a schema where the amount is only  
stored once in those circumstances?

> Having a separate index linking balancing transactions makes  
> displaying
> accounting activity much clearer.  You could call the cross  
> reference a
> "journal ID" or a similar name, since it has some functional  
> similarity
> to the traditional bookkeeper's general journal.

Your cross reference or "Journal ID" seems the same as the  
"Transaction ID" I included in my earlier schema.

> Make sure that you insert each set of accounting transactions as one
> Sqlite transaction which rolls back should it fail to successfully
> complete, thus maintaining the balance of the ledger.

Good tip :-)

>> I found this:
>> Modeling Business Rules: Data Driven Business Rules
>> http://www.tdan.com/view-articles/5227
>> which, seems similar to the route I was taking. If I'm interpreting  
>> it
>> correctly, the TDAN article suggests a relationship between  
>> Accounting
>> Entry and Accounting Transaction:
>>
>> create table "Accounting Entry"
>> (
>>ID
>>  , "Transaction ID"  --> "Accounting Transaction".ID
>>  , "Account ID"  --> Account.ID
>>  , Amount
>>  , Operator  -- plus or minus
>> )
>> ;
>> create table "Accounting Transaction"
>> (
>>ID
>>  , Date
>>  , Description
>> )
>> ;
>> create table Account
>> (
>>ID
>>  , Description
>> )
>> ;
>>

Thanks,
Tom
BareFeet

  --
Comparison of SQLite GUI tools:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible problems in SQLite code

[Ionut Subasu]

Hi,

I have compiled the sqlite with the SQLITE_OMIT_DISKIO option, and I get the
following warnings(see below), I think that an IFDEF si wrongly set, and
maybe someone from the developers can take a look.
Regards,
Ionut
sqlite3.c:8714: warning: 'sqlite3PagerOpen' used but never defined
sqlite3.c:8715: warning: 'sqlite3PagerClose' used but never defined
sqlite3.c:8716: warning: 'sqlite3PagerReadFileheader' used but never defined
sqlite3.c:8719: warning: 'sqlite3PagerSetBusyhandler' used but never defined
sqlite3.c:8720: warning: 'sqlite3PagerSetReiniter' used but never defined
sqlite3.c:8721: warning: 'sqlite3PagerSetPagesize' used but never defined
sqlite3.c:8722: warning: 'sqlite3PagerMaxPageCount' used but never defined
sqlite3.c:8723: warning: 'sqlite3PagerSetCachesize' used but never defined
sqlite3.c:8724: warning: 'sqlite3PagerSetSafetyLevel' used but never defined
sqlite3.c:8725: warning: 'sqlite3PagerLockingMode' used but never defined
sqlite3.c:8726: warning: 'sqlite3PagerJournalMode' used but never defined
sqlite3.c:8727: warning: 'sqlite3PagerJournalSizeLimit' used but never
defined
sqlite3.c:8731: warning: 'sqlite3PagerAcquire' used but never defined
sqlite3.c:8733: warning: 'sqlite3PagerLookup' used but never defined
sqlite3.c:8734: warning: 'sqlite3PagerRef' used but never defined
sqlite3.c:8735: warning: 'sqlite3PagerUnref' used but never defined
sqlite3.c:8738: warning: 'sqlite3PagerWrite' used but never defined
sqlite3.c:8739: warning: 'sqlite3PagerDontWrite' used but never defined
sqlite3.c:8740: warning: 'sqlite3PagerMovepage' used but never defined
sqlite3.c:8741: warning: 'sqlite3PagerPageRefcount' used but never defined
sqlite3.c:8742: warning: 'sqlite3PagerGetData' used but never defined
sqlite3.c:8743: warning: 'sqlite3PagerGetExtra' used but never defined
sqlite3.c:8746: warning: 'sqlite3PagerPagecount' used but never defined
sqlite3.c:8747: warning: 'sqlite3PagerBegin' used but never defined
sqlite3.c:8748: warning: 'sqlite3PagerCommitPhaseOne' used but never defined
sqlite3.c:8749: warning: 'sqlite3PagerSync' used but never defined
sqlite3.c:8750: warning: 'sqlite3PagerCommitPhaseTwo' used but never defined
sqlite3.c:8751: warning: 'sqlite3PagerRollback' used but never defined
sqlite3.c:8752: warning: 'sqlite3PagerOpenSavepoint' used but never defined
sqlite3.c:8753: warning: 'sqlite3PagerSavepoint' used but never defined
sqlite3.c:8756: warning: 'sqlite3PagerIsreadonly' used but never defined
sqlite3.c:8757: warning: 'sqlite3PagerRefcount' used but never defined
sqlite3.c:8758: warning: 'sqlite3PagerFilename' used but never defined
sqlite3.c:8760: warning: 'sqlite3PagerFile' used but never defined
sqlite3.c:8761: warning: 'sqlite3PagerJournalname' used but never defined
sqlite3.c:8762: warning: 'sqlite3PagerNosync' used but never defined
sqlite3.c:8763: warning: 'sqlite3PagerTempSpace' used but never defined
sqlite3.c:8764: warning: 'sqlite3PagerIsMemdb' used but never defined
sqlite3.c:8767: warning: 'sqlite3PagerTruncateImage' used but never defined
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Develop SQLite with Eclipse

[Ionut Subasu]

Problem solved,

I have created an Eclipse project and added the whole CVS source files there
and it worked.

Regards, Ionut

On Thu, Feb 19, 2009 at 10:07 AM, Ionut Subasu wrote:

> Hi everybody,
>
> I am trying to do some changes to sqlite and I use as IDE Eclipse and as OS
> Linux.
> Due to the large size of the file, the environment is verry slow on
> editing.
>
> I have checked out the indication on the SQLite web site (
> http://www.sqlite.org/cvstrac/wiki?p=HowToCompile)
> but I think that some steps are missing :).
>
> Thanks
> Ionut
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Double entry bookkeeping

[John Stanton]

In general you need for one transaction -
General Ledger account
Date
Reference ID
Cross Reference
Amount
Optional narrative

The Chart of Accounts defines full set of accounts and the details of 
each account.

The G/L A/C indicates whether it is an asset or liability account (plus 
or minus) and how it is grouped in reports, the a/c, date amd reference 
ID are a unique ID and the "cross reference" can be a key on an index 
which ties together balancing entries in each "T Account".  If each set 
of entries is an atomic transaction which balances debits and credits 
then the ledger is always balanced.

Having a separate index linking balancing transactions makes displaying 
accounting activity much clearer.  You could call the cross reference a 
"journal ID" or a similar name, since it has some functional similarity 
to the traditional bookkeeper's general journal.

Make sure that you insert each set of accounting transactions as one 
Sqlite transaction which rolls back should it fail to successfully 
complete, thus maintaining the balance of the ledger.

BareFeet wrote:
> Hi Rich,
> 
>>> I'm trying to design a schema for double entry book-keeping. Is there
>>> already a schema available that provides this functionality?
>>   Well, if you're going to re-invent the wheel, take a look at the  
>> source
>> code for gnucash or, better yet, SQL-Ledger.
> 
> Thanks for your reply. I can't find the actual schema for either of  
> those packages. But what I have seen of them indicates that they cater  
> for a much broader solution than what I'm after.
> 
> I'm just looking for the general schema or data model for storing  
> double entry accounting transactions.
> 
> I found this:
> Modeling Business Rules: Data Driven Business Rules
> http://www.tdan.com/view-articles/5227
> which, seems similar to the route I was taking. If I'm interpreting it  
> correctly, the TDAN article suggests a relationship between Accounting  
> Entry and Accounting Transaction:
> 
> create table "Accounting Entry"
> (
> ID
>   , "Transaction ID"  --> "Accounting Transaction".ID
>   , "Account ID"  --> Account.ID
>   , Amount
>   , Operator  -- plus or minus
> )
> ;
> create table "Accounting Transaction"
> (
> ID
>   , Date
>   , Description
> )
> ;
> create table Account
> (
> ID
>   , Description
> )
> ;
> 
> Is tat the general model others have used, experienced, seen,  
> implemented?
> 
> Thanks,
> Tom
> BareFeet
> 
> ___
> 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] Develop SQLite with Eclipse

[Ionut Subasu]

Hi everybody,

I am trying to do some changes to sqlite and I use as IDE Eclipse and as OS
Linux.
Due to the large size of the file, the environment is verry slow on editing.

I have checked out the indication on the SQLite web site (
http://www.sqlite.org/cvstrac/wiki?p=HowToCompile)
but I think that some steps are missing :).

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