AW: [sqlite] Function Language

[Michael Ruck]

I am all for it and am very interested in your project as I'm working on
something similar. I've been using JS to create dynamic HTML pages in
combination with SQLite using a JSON wrapper from this list. The only issue
I see here is the treatment of JS objects - there's again the OO and
relation mismatch involved. You may need some kind of OO mapper to map to
SQLite tables/views.

HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 02:43
An: sqlite-users@sqlite.org
Betreff: [sqlite] Function Language

I have been looking around at handy way to implement elaborate functions in
Sqlite.  Implementing PL/SQL came to mind but recently it struck me that
Javascript has data rules very similar to Sqlite and has the useful property
that all executables are just data.

Does anyone have views for or against Javascript as an embedded language for
realizing functions?  I see as a positive its data typing affinity with
Sqlite and its widespread usage and a large base of active programmers.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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

[sqlite] Some questions on hierarchical data (nested set model)

[Jef Driesen]

I want to store a tree in an sqlite database. My first choice was the 
adjacency list model:


CREATE TABLE tree (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT,
   parent_id INTEGER
);

But this method requires multiple queries to display the entire tree (or 
a subtree) in my GUI (a gtk+ treeview). Because childs can only be added 
to the treeview if all its parents are already added.


But then I found some resources on the nested set model [1,2]:

CREATE TABLE tree (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT,
   lft INTEGER,
   rgt INTEGER
);

Retrieving a (sub)tree can be done with only one sql query, at the 
expense of more complex queries to add or remove rows. Because all lft 
and rgt values to the right of the node have to be modified.


[1] http://www.sitepoint.com/article/hierarchical-data-database
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I start to understand this model, but I still have some questions 
(especially Q3):


Q1. Which is more efficient? Two simple queries or one self join?

I have seen two different types of queries to retrieve a tree. The first 
one uses two very simple queries:


SELECT lft, rgt FROM tree WHERE name = @name;
SELECT * FROM tree WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC;

The first query is only required to retrieve the lft and rgt values of 
the node. The other type uses a self join (which I assume is more 
expensive), but no extra query is required:


SELECT node.*
FROM tree AS node, tree AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = @name
ORDER BY node.lft;

Which type of query is more efficient? Retrieving the path to a node is 
very similar:


SELECT * FROM tree WHERE lft <= @lft AND rgt >= @rgt ORDER BY lft ASC;

or

SELECT parent.*
FROM tree AS node, tree AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = @name
ORDER BY parent.lft;

Q2. Which indices should I use to make my queries more efficient?

Q3. How do I move a node (or subtree)?

In the adjacency list model, this is extremely easy by pointing the 
parent_id to another node. But I don't know how to do that in the nested 
set model.


Q4. sqlite parameter binding for multiple queries?

For some operations (like deleting a node) I need multiple queries:

DELETE FROM tree WHERE lft BETWEEN @lft AND @rgt;
UPDATE tree SET rgt = rgt - (@rgt - @lft + 1) WHERE rgt > @rgt;
UPDATE tree SET lft = lft - (@rgt - @lft + 1) WHERE lft > @rgt;

and they all need the same parameters (@lft and @rgt). Do I have to 
prepare each statement separately and bind the parameters every time? Or 
is it possible to bind the parameters only once (because the values 
remain the same) and execute all the queries at once. I think this is 
not possible, but I could be wrong.




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

[sqlite] Currency Formatting within SQLite

[Mitchell Vincent]

I know it's a long shot but is it possible to use the Windows API
GetCurrencyFormat() function to format currency strings?

I need a layer between my database and report generator to properly
format currency (money) strings for various countries.

--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

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

Re: AW: [sqlite] Function Language

[John Stanton]

Thankyou for the thoughtful comments.  It strikes me that a JS object 
and an Sqlite row map nicely.  When I was writing the part of my 
application server which encapsulates Sqlite rows in JSON I was struck 
by how simple the interface was, particularly compared to XML which 
involves a little more attention to create well formed XML and a whole 
lot more involvement to parse and generate on the client side.  Adding 
JSON as an alternative to XML was a good idea.


I do not try to create dynamic HTML pages using JS and use a much 
simpler and more efficent application specific language compiled to byte 
code (somewhat analogous to Java or VDBE bytecode).  At that level JS is 
merged into the page in such a way that the JS is matched to the browser 
and locale to remove redundancy.


The sophistication in the otherwise simple application specific language 
is an inference engine to resolve a knowledge base of rules stored in 
the Sqlite database and an event driven capability linked to the 
activity of the database.


The same capability which creates dynamic HTML/Javascript will also 
generate PostScript to deliver PDF and no doubt other formats which may 
show up in the future.


Michael Ruck wrote:

I am all for it and am very interested in your project as I'm working on
something similar. I've been using JS to create dynamic HTML pages in
combination with SQLite using a JSON wrapper from this list. The only issue
I see here is the treatment of JS objects - there's again the OO and
relation mismatch involved. You may need some kind of OO mapper to map to
SQLite tables/views.

HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 02:43

An: sqlite-users@sqlite.org
Betreff: [sqlite] Function Language

I have been looking around at handy way to implement elaborate functions in
Sqlite.  Implementing PL/SQL came to mind but recently it struck me that
Javascript has data rules very similar to Sqlite and has the useful property
that all executables are just data.

Does anyone have views for or against Javascript as an embedded language for
realizing functions?  I see as a positive its data typing affinity with
Sqlite and its widespread usage and a large base of active programmers.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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




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

[sqlite] Re: Currency Formatting within SQLite

[Igor Tandetnik]

Mitchell Vincent <[EMAIL PROTECTED]> wrote:

I know it's a long shot but is it possible to use the Windows API
GetCurrencyFormat() function to format currency strings?

I need a layer between my database and report generator to properly
format currency (money) strings for various countries.


I assume you want to have SQLite format values for you (though you are 
talking about a layer above the database which seems to suggest 
otherwise). You can install a custom function - see 
sqlite3_create_function[16]. This function can do whatever you want, 
including calling GetCurrencyFormat. Then you can do something like


select FormatCurrency(currencyValue) from ...;

Igor Tandetnik 



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

Re: [sqlite] Re: Currency Formatting within SQLite

[Joe Wilson]

--- Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it. Currency
> formatting is quite complicated (much more than just storing a
> currency "symbol") and Windows has already done the leg work - I just
> need to see gain access to the API function for it.

You gain access to the API function by writing code to call it.

On 4/6/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> > otherwise). You can install a custom function - see
> > sqlite3_create_function[16]. This function can do whatever you want,
> > including calling GetCurrencyFormat.


 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

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

RE: [sqlite] Re: Currency Formatting within SQLite

[Samuel R. Neff]

 
> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it. 

You don't need to write a totally custom function, just something that
bridges the two API's.  Create a function that implements the expected
SQLite call spec and then call out to Windows to get the results.

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell
Vincent
Sent: Friday, April 06, 2007 11:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Currency Formatting within SQLite

Yes, I know about creating a function but I'm wondering if I can hook
into the already-existing Windows API function for it. Currency
formatting is quite complicated (much more than just storing a
currency "symbol") and Windows has already done the leg work - I just
need to see gain access to the API function for it.

The reasons are complicated but suffice it to say that currency
formatting is one area that ReportMan (reportman.sourceforge.net) is
lacking in. It's really not the job of a database to do this stuff but
I'm left with either getting SQLite to do it on the fly or adding
another "formatted" text field for each currency field already in the
database.

Thanks Igor!



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

Re: [sqlite] Re: Currency Formatting within SQLite

[Mitchell Vincent]

It sounds so easy when you say it like that! :-)

Thanks Mr, Neff! I'll get to reading!

On 4/6/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:


> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it.

You don't need to write a totally custom function, just something that
bridges the two API's.  Create a function that implements the expected
SQLite call spec and then call out to Windows to get the results.

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell
Vincent
Sent: Friday, April 06, 2007 11:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Currency Formatting within SQLite

Yes, I know about creating a function but I'm wondering if I can hook
into the already-existing Windows API function for it. Currency
formatting is quite complicated (much more than just storing a
currency "symbol") and Windows has already done the leg work - I just
need to see gain access to the API function for it.

The reasons are complicated but suffice it to say that currency
formatting is one area that ReportMan (reportman.sourceforge.net) is
lacking in. It's really not the job of a database to do this stuff but
I'm left with either getting SQLite to do it on the fly or adding
another "formatted" text field for each currency field already in the
database.

Thanks Igor!



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





--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

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

[sqlite] sqlite3.exe .import command

[jaime]

I've configured SQLite with RonR and mongrel for use as a development 
environment on an XP system.  I'm replacing an exiting applicaiton and need 
to migrate data into the new database.  All seemed well until I tried to use 
the .import command in sqlite3.exe. 

I get a message that 13 fields were expected but only one found.  The file I 
am importing is comma delimited text with one line per record.  I could find 
no documentation on format expectations of the .import command. 

I would appreciate help on this particular problem and also a URL for any 
documentation of the sqlite3.exe program. 

Thank you very much! 


jaime

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

RE: [sqlite] Re: Currency Formatting within SQLite

[Samuel R. Neff]

I don't know exactly how to do it in C but in .NET it's pretty
straightforward..

[SQLiteFunction(Name = "FormatCurrency", Arguments = -1, FuncType =
FunctionType.Scalar)]
public class SQLiteCurrency : SQLiteFunction 
{
public override object Invoke(object[] args)
{
try
{
return Convert.ToDecimal(args[0]).ToString("C");
}
catch(InvalidCastException)
{
return null;
}
}
}

Best regards,

Sam 


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell
Vincent
Sent: Friday, April 06, 2007 11:54 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: Currency Formatting within SQLite

It sounds so easy when you say it like that! :-)

Thanks Mr, Neff! I'll get to reading!

On 4/6/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote:
>
> > Yes, I know about creating a function but I'm wondering if I can hook
> > into the already-existing Windows API function for it.
>
> You don't need to write a totally custom function, just something that
> bridges the two API's.  Create a function that implements the expected
> SQLite call spec and then call out to Windows to get the results.
>
> HTH,
>
> Sam


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

Re: [sqlite] Re: Currency Formatting within SQLite

[John Stanton]

You can call the Windows API from a custom function.

Mitchell Vincent wrote:

Yes, I know about creating a function but I'm wondering if I can hook
into the already-existing Windows API function for it. Currency
formatting is quite complicated (much more than just storing a
currency "symbol") and Windows has already done the leg work - I just
need to see gain access to the API function for it.

The reasons are complicated but suffice it to say that currency
formatting is one area that ReportMan (reportman.sourceforge.net) is
lacking in. It's really not the job of a database to do this stuff but
I'm left with either getting SQLite to do it on the fly or adding
another "formatted" text field for each currency field already in the
database.

Thanks Igor!

On 4/6/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> I know it's a long shot but is it possible to use the Windows API
> GetCurrencyFormat() function to format currency strings?
>
> I need a layer between my database and report generator to properly
> format currency (money) strings for various countries.

I assume you want to have SQLite format values for you (though you are
talking about a layer above the database which seems to suggest
otherwise). You can install a custom function - see
sqlite3_create_function[16]. This function can do whatever you want,
including calling GetCurrencyFormat. Then you can do something like

select FormatCurrency(currencyValue) from ...;

Igor Tandetnik


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 










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

Re: [sqlite] SQLite and nested transactions

[rhurst2]

 Darren Duncan <[EMAIL PROTECTED]> wrote: 
> At 4:38 PM -0700 4/5/07, Darren Duncan wrote:
> >To get this to work would basically involve having additional 
> >journal files, with the original one being for the parent-most 
> >transaction, and with an additional one for each transaction level, 
> >or some such arrangement; the extra ones could have file names like 
> >the original but numeric suffixes indicating the transaction level.
> >
> >Note that to maintain backwards compatability, the original journal 
> >file will still need all pre-change pages written to it too, but 
> >intermediate-level files don't necessarily need this, or it can be 
> >done, as the implementer wishes.
> 
> Actually, I will clarify that any "additional" journal files do not 
> need to be on disk ... said pages could just be in RAM, unless there 
> are too many of them and they need to spill to disk ... only the 
> parent-most transaction actually needs a journal file on disk, under 
> the same circumstances that the current journal needs to be on disk 
> ... before writes to the main SQLite db file are being made.
> 
> My more general point is that to support child transactions, the 
> pager layer would need to be updated to represent N ordered layers of 
> state for changed pages, where N is the number of nested transactions 
> (an ordinary SQL statement counting as 1), rather than just 
> representing 2 layers, current and original.
> 
> I believe that this can be done fairly easily, and should be done, 
> assuming the pager is already well designed and doesn't make certain 
> assumptions.
> 
> Moreover, I don't believe that the addition of this feature should 
> make the SQLite code base much larger, and it shouldn't cause much of 
> a performance hit, if any at all.
> 
> -- Darren Duncan
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 

It sounds like this feature can be added under a compile time switch.
Do we have any volunteers out there?
Ray

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

[sqlite] Re: sqlite3.exe .import command

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

I get a message that 13 fields were expected but only one found.  The
file I am importing is comma delimited text with one line per record. 
I
could find no documentation on format expectations of the .import 
command.


I believe it expects TABs by default, but you can change it with

.separator ,


I would appreciate help on this particular problem and also a URL for
any documentation of the sqlite3.exe program.


There's a built-in help accessible with ".help" command. Not very 
extensive, but usually sufficient.


Igor Tandetnik 



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

Re: AW: AW: [sqlite] Function Language

[John Stanton]

By using making the connection from browser to server an RPC model I 
have mapped the interface to the database instead of trying to map the 
database to the Javascript objects.  That also minimizes the network 
traffic.  My application server design has a criterion that network 
traffic should be minimized.  It is a great way to increase effective 
bandwidth.


The generated HTML/Javascript pages have no redundancy and are 
compressed if they are above a threshold size.  The RPC between the 
browser and the server uses sparse messages and not a great deal of 
redundancy.  It also minimizes the extent of execution of the slow, 
interpreted Javascript by partitioning as much basic housekeeping to the 
more efficient server side processing.


The server holds each database open as exclusive and shares it between 
users and multiple user connections, minimizing database open and close 
actions, keeping local cacheing and avoiding file locking.


Currently I am dreaming up ways of implementing the Javascript function 
level in Sqlite.  Creating the JS VM when the DB opens and having one VM 
per open DB instance seems to be a way of avoiding contentions and 
getting fairly efficient execution and a reasonable route for an initial 
prototype.  Garbage collection should take care of stale objects.  I can 
store user defined functions as text items in a dedicated table defining 
aggregate and scalar functions in a database.  A syntax directed editor 
linked to JSLint and RCS can maintain syntactically correct code with 
version control.


Javascript would not be a good way to implement simple functions in 
Sqlite, the current custome function interface to native code is far 
prefereable for that but it is appropriate for implementing larger 
functions and one which require frequent user alterations.


Michael Ruck wrote:

How do you treat objects containing other objects? JS has the capabilities
to build powerful object models with hashes etc. Objects, such as these
don't map nicely to the relational model.

I'm going a different way - I'm using static HTML, which requests JSON from
the server and uses this to update the UI on the client side. However
mapping JSON to SQL is still somewhat of an issue. I've tackled it by
including metadata in JSON, but that's not very clean and I'm not really
happy with it (yet.)

Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 18:22

An: sqlite-users@sqlite.org
Betreff: Re: AW: [sqlite] Function Language

Thankyou for the thoughtful comments.  It strikes me that a JS object and an
Sqlite row map nicely.  When I was writing the part of my application server
which encapsulates Sqlite rows in JSON I was struck by how simple the
interface was, particularly compared to XML which involves a little more
attention to create well formed XML and a whole lot more involvement to
parse and generate on the client side.  Adding JSON as an alternative to XML
was a good idea.

I do not try to create dynamic HTML pages using JS and use a much simpler
and more efficent application specific language compiled to byte code
(somewhat analogous to Java or VDBE bytecode).  At that level JS is merged
into the page in such a way that the JS is matched to the browser and locale
to remove redundancy.

The sophistication in the otherwise simple application specific language is
an inference engine to resolve a knowledge base of rules stored in the
Sqlite database and an event driven capability linked to the activity of the
database.

The same capability which creates dynamic HTML/Javascript will also generate
PostScript to deliver PDF and no doubt other formats which may show up in
the future.

Michael Ruck wrote:

I am all for it and am very interested in your project as I'm working 
on something similar. I've been using JS to create dynamic HTML pages 
in combination with SQLite using a JSON wrapper from this list. The 
only issue I see here is the treatment of JS objects - there's again 
the OO and relation mismatch involved. You may need some kind of OO 
mapper to map to SQLite tables/views.


HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 6. April 2007 02:43
An: sqlite-users@sqlite.org
Betreff: [sqlite] Function Language

I have been looking around at handy way to implement elaborate 
functions in Sqlite.  Implementing PL/SQL came to mind but recently it 
struck me that Javascript has data rules very similar to Sqlite and 
has the useful property that all executables are just data.


Does anyone have views for or against Javascript as an embedded 
language for realizing functions?  I see as a positive its data typing 
affinity with Sqlite and its widespread usage and a large base of active


programmers.


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

Re: [sqlite] Re: Re: Currency Formatting within SQLite

[Mitchell Vincent]

Hi Igor!

The only problem is my lack of understanding on how implementing
custom functions in SQLite works. I'm sure your idea is sound I just
don't know the details of implementing it. For instance, is this
something that has to be compiled into the DLL, or is it database file
specific?

Now that I know it's possible I'll I'm sure I'll have all my questions
answered as I read the documentation.

Thanks again!

On 4/6/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it. Currency
> formatting is quite complicated (much more than just storing a
> currency "symbol") and Windows has already done the leg work - I just
> need to see gain access to the API function for it.

I don't understand. You can install a custom function into SQLite
engine - the function that you implement. This function can then be used
in any SQL statement. Within implementation of that function, you can
happily use GetCurrencyFormat or any other API. What again seems to be
the problem?

Igor Tandetnik


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





--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

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

RE: [sqlite] sqlite3.exe .import command

[Griggs, Donald]

Re: "...The file I am importing is comma delimited text ..."

I believe the default separator is the vertical bar (virgule, "|"), but
as Igor wrote, you can easily change it with:
 .separator ,

I *don't* think you can quote commas which might appear in your text.
E.g. if you are importing a list of names, you can just surround the
name with quote chars, such as:
"John Smith, Jr.",Anytown,USA

So if your text contains commas, you might want to use a different
separator.

Also, I don't think a trailing separator character is expected at the
end of your records.  If your originating application insists on
including one, you can just define an extra field on your import table.


[opinions are my own, not those of my company]

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

Re: [sqlite] Re: Re: Currency Formatting within SQLite

[John Stanton]

Just look at the Sqlite source and use the function code there as a 
template.


For a custome function look up the Sqlite API documentation on sqlite.org.

Mitchell Vincent wrote:

Hi Igor!

The only problem is my lack of understanding on how implementing
custom functions in SQLite works. I'm sure your idea is sound I just
don't know the details of implementing it. For instance, is this
something that has to be compiled into the DLL, or is it database file
specific?

Now that I know it's possible I'll I'm sure I'll have all my questions
answered as I read the documentation.

Thanks again!

On 4/6/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> Yes, I know about creating a function but I'm wondering if I can hook
> into the already-existing Windows API function for it. Currency
> formatting is quite complicated (much more than just storing a
> currency "symbol") and Windows has already done the leg work - I just
> need to see gain access to the API function for it.

I don't understand. You can install a custom function into SQLite
engine - the function that you implement. This function can then be used
in any SQL statement. Within implementation of that function, you can
happily use GetCurrencyFormat or any other API. What again seems to be
the problem?

Igor Tandetnik


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 










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

RE: [sqlite] sqlite3.exe .import command

[Griggs, Donald]

Ouch!   I of course meant to write "can't" instead of "can" in the
(corrected) sentence below.

E.g. if you are importing a list of names, you *can't* just surround the
name with quote chars, such as:
"John Smith, Jr.",Anytown,USA

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

[sqlite] Re: sqlite3 extensions; was: Re: Bug#404242: closed by Laszlo Boszormenyi (GCS) <[EMAIL PROTECTED]> (Bug#404242: fixed in sqlite3 3.3.14-1)

[Liam Healy]

As far as I know, they've always been experimental, so I'm not sure
what changed between 3.3.8 and 3.3.14.  And yes, I really need it.
I have never compiled sqlite, only used the Debian compiled version, so
I am not sure what needs to be done here.  I am ccing the sqlite mailing
list in hopes that someone can provide this information.

Liam


On 4/6/07, Laszlo Boszormenyi <[EMAIL PROTECTED]> wrote:


Hi Liam!

On Thu, 2007-04-05 at 17:04 -0400, Liam Healy wrote:
> There is now in 3.3.14 a new and more serious problem related to the
> extensions: while the
> header file is present, the functions have disappeared from the .so
> library:
> nm -D  /usr/lib/libsqlite3.so.0.8.6 | grep extension
> now yields nothing.  In 3.3.8, this library had
> 00028e10 T sqlite3_auto_extension
> 00028cb0 T sqlite3_enable_load_extension
> 00028cd0 T sqlite3_load_extension
> 00028dd0 T sqlite3_reset_auto_extension
Well, yes. These are marked experimental and not enabled by default
anymore. There is no configure options for them even. I can hack and
enable it by default; but do you really need a just added and
experimental function set?

Regards,
Laszlo/GCS

AW: AW: AW: [sqlite] Function Language

[Michael Ruck]

If you come up with something, please share it.

Mike 

-Ursprüngliche Nachricht-
Von: John Stanton [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 6. April 2007 20:49
An: sqlite-users@sqlite.org
Betreff: Re: AW: AW: [sqlite] Function Language

By using making the connection from browser to server an RPC model I have
mapped the interface to the database instead of trying to map the database
to the Javascript objects.  That also minimizes the network traffic.  My
application server design has a criterion that network traffic should be
minimized.  It is a great way to increase effective bandwidth.

The generated HTML/Javascript pages have no redundancy and are compressed if
they are above a threshold size.  The RPC between the browser and the server
uses sparse messages and not a great deal of redundancy.  It also minimizes
the extent of execution of the slow, interpreted Javascript by partitioning
as much basic housekeeping to the more efficient server side processing.

The server holds each database open as exclusive and shares it between users
and multiple user connections, minimizing database open and close actions,
keeping local cacheing and avoiding file locking.

Currently I am dreaming up ways of implementing the Javascript function
level in Sqlite.  Creating the JS VM when the DB opens and having one VM per
open DB instance seems to be a way of avoiding contentions and getting
fairly efficient execution and a reasonable route for an initial prototype.
Garbage collection should take care of stale objects.  I can store user
defined functions as text items in a dedicated table defining aggregate and
scalar functions in a database.  A syntax directed editor linked to JSLint
and RCS can maintain syntactically correct code with version control.

Javascript would not be a good way to implement simple functions in Sqlite,
the current custome function interface to native code is far prefereable for
that but it is appropriate for implementing larger functions and one which
require frequent user alterations.

Michael Ruck wrote:
> How do you treat objects containing other objects? JS has the 
> capabilities to build powerful object models with hashes etc. Objects, 
> such as these don't map nicely to the relational model.
> 
> I'm going a different way - I'm using static HTML, which requests JSON 
> from the server and uses this to update the UI on the client side. 
> However mapping JSON to SQL is still somewhat of an issue. I've 
> tackled it by including metadata in JSON, but that's not very clean 
> and I'm not really happy with it (yet.)
> 
> Mike
> 
> -Ursprüngliche Nachricht-
> Von: John Stanton [mailto:[EMAIL PROTECTED]
> Gesendet: Freitag, 6. April 2007 18:22
> An: sqlite-users@sqlite.org
> Betreff: Re: AW: [sqlite] Function Language
> 
> Thankyou for the thoughtful comments.  It strikes me that a JS object 
> and an Sqlite row map nicely.  When I was writing the part of my 
> application server which encapsulates Sqlite rows in JSON I was struck 
> by how simple the interface was, particularly compared to XML which 
> involves a little more attention to create well formed XML and a whole 
> lot more involvement to parse and generate on the client side.  Adding 
> JSON as an alternative to XML was a good idea.
> 
> I do not try to create dynamic HTML pages using JS and use a much 
> simpler and more efficent application specific language compiled to 
> byte code (somewhat analogous to Java or VDBE bytecode).  At that 
> level JS is merged into the page in such a way that the JS is matched 
> to the browser and locale to remove redundancy.
> 
> The sophistication in the otherwise simple application specific 
> language is an inference engine to resolve a knowledge base of rules 
> stored in the Sqlite database and an event driven capability linked to 
> the activity of the database.
> 
> The same capability which creates dynamic HTML/Javascript will also 
> generate PostScript to deliver PDF and no doubt other formats which 
> may show up in the future.
> 
> Michael Ruck wrote:
> 
>>I am all for it and am very interested in your project as I'm working 
>>on something similar. I've been using JS to create dynamic HTML pages 
>>in combination with SQLite using a JSON wrapper from this list. The 
>>only issue I see here is the treatment of JS objects - there's again 
>>the OO and relation mismatch involved. You may need some kind of OO 
>>mapper to map to SQLite tables/views.
>>
>>HTH,
>>Mike
>>
>>-Ursprüngliche Nachricht-
>>Von: John Stanton [mailto:[EMAIL PROTECTED]
>>Gesendet: Freitag, 6. April 2007 02:43
>>An: sqlite-users@sqlite.org
>>Betreff: [sqlite] Function Language
>>
>>I have been looking around at handy way to implement elaborate 
>>functions in Sqlite.  Implementing PL/SQL came to mind but recently it 
>>struck me that Javascript has data rules very similar to Sqlite and 
>>has the useful property that all executables are just data.
>>
>>Does anyone have views for or 

[sqlite] Re: Re: Re: Currency Formatting within SQLite

[Igor Tandetnik]

Mitchell Vincent <[EMAIL PROTECTED]> wrote:

The only problem is my lack of understanding on how implementing
custom functions in SQLite works. I'm sure your idea is sound I just
don't know the details of implementing it. For instance, is this
something that has to be compiled into the DLL, or is it database file
specific?


It can be compiled into any piece of code that can call 
sqlite3_create_function[16] API. Custom functions are not permanent, 
they exist within a particular database connection, the one whose handle 
you pass as the first parameter to sqlite3_create_function.


Igor Tandetnik 



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

RE: [sqlite] Implementing type find on a large result set

[Samuel R. Neff]

Instead of using LIKE use '<' to get a count of records before the one your
targeting.  Something like this would work (names are from my schema):

SELECT PermissionRef 
FROM LU_Permissions 
LIMIT 10 
OFFSET (
SELECT COUNT(*) 
FROM LU_Permissions 
WHERE PermissionRef < 'Sc');

HTH,

Sam
 


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


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

[sqlite] in memory databases

[Mike Johnston]

Hi,
Is it possible to have memory databases located at a specific memory 
address? I have a battery backed memory I'd like to store specific 
information apart from the main database.

Thanks,
Mike
 
 
-
No need to miss a message. Get email on-the-go 
with Yahoo! Mail for Mobile. Get started.

Re: [sqlite] Some questions on hierarchical data (nested set model)

[P Kishor]

I am very interested in hearing on this as well. A recommendation has
been Joe Celko's book "Trees and Hierarchies in SQL for Smarties"
(.
I have tons of options for post-processing. A cursory search in CPAN
reveals the entire Graph and Tree hierarchy
(
is particularly interesting). But, I would like to learn more about
the backend, the storage option itself. Or, is it simply better to
just store the data in the simplest format possible and then slurp it
all in and work on it in-memory using our programming language of
choice, assuming that SQL would be not as powerful as a full-fledged
programming language.

On 4/6/07, Jef Driesen <[EMAIL PROTECTED]> wrote:

I want to store a tree in an sqlite database. My first choice was the
adjacency list model:

CREATE TABLE tree (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
parent_id INTEGER
);

But this method requires multiple queries to display the entire tree (or
a subtree) in my GUI (a gtk+ treeview). Because childs can only be added
to the treeview if all its parents are already added.

But then I found some resources on the nested set model [1,2]:

CREATE TABLE tree (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
lft INTEGER,
rgt INTEGER
);

Retrieving a (sub)tree can be done with only one sql query, at the
expense of more complex queries to add or remove rows. Because all lft
and rgt values to the right of the node have to be modified.

[1] http://www.sitepoint.com/article/hierarchical-data-database
[2] http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I start to understand this model, but I still have some questions
(especially Q3):

Q1. Which is more efficient? Two simple queries or one self join?

I have seen two different types of queries to retrieve a tree. The first
one uses two very simple queries:

SELECT lft, rgt FROM tree WHERE name = @name;
SELECT * FROM tree WHERE lft BETWEEN @lft AND @rgt ORDER BY lft ASC;

The first query is only required to retrieve the lft and rgt values of
the node. The other type uses a self join (which I assume is more
expensive), but no extra query is required:

SELECT node.*
FROM tree AS node, tree AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = @name
ORDER BY node.lft;

Which type of query is more efficient? Retrieving the path to a node is
very similar:

SELECT * FROM tree WHERE lft <= @lft AND rgt >= @rgt ORDER BY lft ASC;

or

SELECT parent.*
FROM tree AS node, tree AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = @name
ORDER BY parent.lft;

Q2. Which indices should I use to make my queries more efficient?

Q3. How do I move a node (or subtree)?

In the adjacency list model, this is extremely easy by pointing the
parent_id to another node. But I don't know how to do that in the nested
set model.

Q4. sqlite parameter binding for multiple queries?

For some operations (like deleting a node) I need multiple queries:

DELETE FROM tree WHERE lft BETWEEN @lft AND @rgt;
UPDATE tree SET rgt = rgt - (@rgt - @lft + 1) WHERE rgt > @rgt;
UPDATE tree SET lft = lft - (@rgt - @lft + 1) WHERE lft > @rgt;

and they all need the same parameters (@lft and @rgt). Do I have to
prepare each statement separately and bind the parameters every time? Or
is it possible to bind the parameters only once (because the values
remain the same) and execute all the queries at once. I think this is
not possible, but I could be wrong.



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





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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